jxl匯出excel

原文地址：http://www.cnblogs.com/forlina/archive/2011/06/15/2081153.html

首先先在自己工程中匯入jxl的jar包；

瘋狂google後找到一段別人的匯出excel方法，先備份於下面：

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Vector;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExportData {

    /** *//**
     * 匯出資料為XLS格式
     * @param fileName        檔案的名稱，可以設為絕對路徑，也可以設為相對路徑
     * @param content        資料的內容
     */
    public static void exportExcel(String fileName, Vector<Person> content) {
        WritableWorkbook wwb;
        FileOutputStream fos;
        try {    
            fos = new FileOutputStream(fileName);
            wwb = Workbook.createWorkbook(fos);
            WritableSheet ws = wwb.createSheet("三國志武將列表", 10);        // 建立一個工作表

            //    設定儲存格的文字格式
            WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
            wcf.setAlignment(Alignment.CENTRE);
            ws.setRowView(1, 500);

            //    填充資料的內容
            Person[] p = new Person[content.size()];
            for (int i = 0; i < content.size(); i++){
                p[i] = (Person)content.get(i);
                ws.addCell(new Label(1, i + 1, p[i].getName(), wcf));
                ws.addCell(new Label(2, i + 1, p[i].getNickname(), wcf));
                ws.addCell(new Label(3, i + 1, p[i].getPower(), wcf));
                ws.addCell(new Label(4, i + 1, p[i].getWit(), wcf));
                ws.addCell(new Label(5, i + 1, p[i].getPolity(), wcf));
                ws.addCell(new Label(6, i + 1, p[i].getCharm(), wcf));
                ws.addCell(new Label(7, i + 1, p[i].getStory(), wcf));
                if(i == 0)
                    wcf = new WritableCellFormat();
            }

            wwb.write();
            wwb.close();

        } catch (IOException e){
        } catch (RowsExceededException e){
        } catch (WriteException e){}
    }

    /** *//**
     * 從Excel檔案裡讀取資料儲存到Vector裡
     * @param fileName        Excel檔案的名稱
     * @return                Vector對象,裡麵包含從Excel檔案裡擷取到的資料
     */
    public static Vector<Person> importExcel(String fileName){
        Vector<Person> v = new Vector<Person>();
        try {
            Workbook book = Workbook.getWorkbook(new File(fileName));
            Sheet sheet = book.getSheet(0);        // 獲得第一個工作表對象
            int rows = sheet.getRows();
           
            for(int i = 0; i < rows; i++) {
                Cell [] cell = sheet.getRow(i);
                if(cell.length == 0)
                    continue;
               
                Person p = new Person();
                p.setName(sheet.getCell(1, i).getContents());
                p.setNickname(sheet.getCell(2, i).getContents());
                p.setPower(sheet.getCell(3, i).getContents());
                p.setWit(sheet.getCell(4, i).getContents());
                p.setPolity(sheet.getCell(5, i).getContents());
                p.setCharm(sheet.getCell(6, i).getContents());
                p.setStory(sheet.getCell(7, i).getContents());
                v.add(p);
            }

            book.close();
        }catch(Exception e) {}
        return v;
    }

    public static void main(String [] args){
        String fileName = "C:\\test.xls";
        String fileNameNew = "C:\\testNew.xls";
       
        Person p0 = new Person("姓名","字","武力","智力","政治","魅力","英雄事迹");
        Person p1 = new Person("趙雲","子龍","98","84","83","87","單騎救主!!!");
        Person p2 = new Person("馬超","孟起","98","62","40","88","殺得曹操割須棄袍!!!");
        Person p3 = new Person("諸葛亮","孔明","55","100","92","93","死後木偶退兵，錦囊殺魏延!!!");

        Vector<Person> v = new Vector<Person>();
        v.add(p0);
        v.add(p1);
        v.add(p2);
        v.add(p3);
       
        exportExcel(fileName, v);
        System.out.println("成功匯出資料到Excel檔案(" + fileName + ")了!!!");
       
//        Vector<Person> vector = importExcel(fileName);
//        System.out.println("成功從Excel檔案(" + fileName + ")匯入資料!!!");
//       
//        exportExcel(fileNameNew, vector);
//        System.out.println("成功將" + fileName + "裡的資料手複製到" + fileNameNew + "中!!!");
    }
}

上面的方法很好，匯出我試過了，很好用，但它只實現了自動產生了excel檔案；但是我們項目要求的是：彈出儲存/開啟excel的對話方塊，然後直接開啟或輸入路徑再儲存；

import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Vector;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExportExcel {

public static void exportExcel( Object objIn,Object objOut) {
         try {
         //取得response HttpServletResponse
         HashMap hmOut = (HashMap)objOut;
         HashMap hmIn = (HashMap)objIn;
         HttpServletResponse response=(HttpServletResponse)hmIn.get("response");
         //設定table列名
         String excelName =(String)hmIn.get("excelName");
         String[] excelNameArray = excelName.split(",");
        
        
         //取得key
         String[] excelKeyArray = (String[])hmOut.get("excelKey");
        
         OutputStream os = response.getOutputStream();// 取得輸出資料流  

             response.reset();// 清空輸出資料流  

             response.setHeader("Content-disposition", "attachment; filename="+new String("Book1".getBytes("GB2312"),"8859_1")+".xls");// 設定輸出檔案頭  

             response.setContentType("application/msexcel");// 定義輸出類型
            
             WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel檔案  
        
             WritableSheet ws = wwb.createSheet("Sheet1", 10);        // 建立一個工作表

             //    設定儲存格的文字格式
             WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
                     UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
             WritableCellFormat wcf = new WritableCellFormat(wf);
             wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
             wcf.setAlignment(Alignment.CENTRE);
             ws.setRowView(0, 500);

             //    填充資料的內容
             int len=((String[])hmOut.get(excelKeyArray[0])).length;
             //設定列頭名
         for (int j=0;j<excelKeyArray.length;j++){
            ws.addCell(new Label(j, 0, excelNameArray[j], wcf));
         }
         //設定內容
         wcf = new WritableCellFormat();
             for (int i = 0; i <len; i++){
             for (int j=0;j<excelKeyArray.length;j++){
                ws.addCell(new Label(j, i+1, ((String[])hmOut.get(excelKeyArray[j]))[i], wcf));
             }
             }

             wwb.write();
             wwb.close();

         } catch (IOException e){
         } catch (RowsExceededException e){
         } catch (WriteException e){}
     }

}

註：現在彈出對話方塊，就是說要用io流的方式，io流就要從response中取；這裡要匯入tomcat裡的一個jar：servlet.jar