Java中匯入、匯出Excel

標籤：3.3   學習   com   form   blog   拷貝   匯出   資料   工作表   

一、介紹

　　當前B/S模式已成為應用開發的主流，而在企業辦公系統中，常常有客戶這樣子要求：你要把我們的報表直接用Excel開啟(電信系統、銀行系統)。或者是：我們已經習慣用Excel列印。這樣在我們實際的開發中，很多時候需要實現匯入、匯出Excel的應用。

　　目前，比較常用的實現Java匯入、匯出Excel的技術有兩種Jakarta POI和Java Excel

　　下面我就分別講解一下如何使用這兩個技術實現匯入、匯出Excel

二、使用Jakarta POI匯入、匯出Excel

　　Jakarta POI 是一套用於訪問微軟格式文檔的Java API。Jakarta POI有很多組件組成，其中有用於操作Excel格式檔案的HSSF和用於操作Word的HWPF，在各種組件中目前只有用於操作Excel的HSSF相對成熟。官方首頁http://poi.apache.org/index.html，API文檔http://poi.apache.org/apidocs/index.html

2.1 環境配置　　2.1.1下載jar

　　官方下載：http://poi.apache.org/download.html這裡可以下載到它的最新版本和文檔，目前最新版本是3.7，這裡使用比較穩定的3.6版。

　　2.1.2加入jar包

　　將根目錄下的poi-3.6-20091214.jar和Lib目錄下三個通用包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar拷貝到項目的Lib下

2.2 Jakarta POI HSSF API組件

　　HSSF（用於操作Excel的組件）提供給使用者使用的對象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel對象，樣式和格式，還有輔助操作。有以下幾種對象： 

常用組件：

HSSFWorkbook                      excel的文檔對象

HSSFSheet                         excel的表單

HSSFRow                           excel的行

HSSFCell                          excel的格子單元

HSSFFont                          excel字型

HSSFDataFormat                    日期格式

HSSFHeader                        sheet頭

HSSFFooter                        sheet尾（只有列印的時候才能看到效果）

樣式：

HSSFCellStyle                       cell樣式

輔助操作包括：

HSSFDateUtil                        日期

HSSFPrintSetup                      列印

HSSFErrorConstants                  錯誤資訊表

2.3 基本操作步驟

　　首先，理解一下一個Excel的檔案的組織形式，一個Excel檔案對應於一個workbook(HSSFWorkbook)，一個workbook可以有多個sheet（HSSFSheet）組成，一個sheet是由多個row（HSSFRow）組成，一個row是由多個cell（HSSFCell）組成。

　　基本操作步驟：

1、用HSSFWorkbook開啟或者建立“Excel檔案對象”

2、用HSSFWorkbook對象返回或者建立Sheet對象

3、用Sheet對象返回行對象，用行對象得到Cell對象

4、對Cell對象讀寫。

　　下面來看一個動態產生Excel檔案的例子：

//建立HSSFWorkbook對象HSSFWorkbook wb = new HSSFWorkbook();//建立HSSFSheet對象HSSFSheet sheet = wb.createSheet("sheet0");//建立HSSFRow對象HSSFRow row = sheet.createRow(0);//建立HSSFCell對象HSSFCell cell=row.createCell(0);//設定儲存格的值cell.setCellValue("儲存格中的中文");//輸出Excel檔案FileOutputStream output=new FileOutputStream("d:\\workbook.xls");wkb.write(output);output.flush();

　　HSSF讀取檔案同樣還是使用這幾個對象，只是把相應的createXXX方法變成了getXXX方法即可。可見只要理解了其中原理，不管是讀還是寫亦或是特定格式都可以輕鬆實現，正所謂知其然更要知其所以然。

2.4 匯出Excel應用執行個體

　　 在2.3中我們寥寥幾行代碼實際上就已經就是實現了匯出Excel一個簡單樣本，下面我們在看如何?匯出的Excel表格？

　　代碼如下：（實際開發中應封裝到業務層組件中，然後在控制層中調用。這裡直接寫在控制層組件，如Servlet的doGet/doPost方法或Struts架構的execute方法中）

//建立HSSFWorkbook對象(excel的文檔對象)      HSSFWorkbook wb = new HSSFWorkbook();//建立新的sheet對象（excel的表單）HSSFSheet sheet=wkb.createSheet("成績表");//在sheet裡建立第一行，參數為行索引(excel的行)，可以是0～65535之間的任何一個HSSFRow row1=sheet.createRow(0);//建立儲存格（excel的儲存格，參數為列索引，可以是0～255之間的任何一個HSSFCell cell=row1.createCell(0);      //設定儲存格內容cell.setCellValue("學員考試成績一覽表");//合併儲存格CellRangeAddress構造參數依次表示起始行，截至行，起始列， 截至列sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));//在sheet裡建立第二行HSSFRow row2=sheet.createRow(1);          //建立儲存格並設定儲存格內容      row2.createCell(0).setCellValue("姓名");      row2.createCell(1).setCellValue("班級");          row2.createCell(2).setCellValue("筆試成績");row2.createCell(3).setCellValue("機試成績");          //在sheet裡建立第三行      HSSFRow row3=sheet.createRow(2);      row3.createCell(0).setCellValue("李明");      row3.createCell(1).setCellValue("As178");      row3.createCell(2).setCellValue(87);          row3.createCell(3).setCellValue(78);      //.....省略部分代碼//輸出Excel檔案    OutputStream output=response.getOutputStream();    response.reset();    response.setHeader("Content-disposition", "attachment; filename=details.xls");    response.setContentType("application/msexcel");            wkb.write(output);    output.close();retrun null;

　　加底線這部分代碼是B/S模式中採用的輸出方式，而不是輸出到本地指定的磁碟目錄。該代碼錶示將details.xls的Excel檔案通過應答實體（response）輸出給請求的用戶端瀏覽器，用戶端可儲存或直接開啟。

2.5 樣式設定

　　在實際應用中匯出的Excel檔案往往需要閱讀和列印的，這就需要對輸出的Excel文檔進行排版和樣式的設定，主要操作有合併儲存格、設定儲存格樣式、設定字型樣式等。

　　2.5.1儲存格合并

    使用HSSFSheet的addMergedRegion()方法

public int addMergedRegion(CellRangeAddress region)

    參數CellRangeAddress 表示合并的地區，構造方法如下：

CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

　　構造參數依次表示起始行，截至行，起始列， 截至列。範例程式碼參照2.4部分

2.5.2設定儲存格的行高、列寬

HSSFSheet sheet=wb.createSheet();sheet.setDefaultRowHeightInPoints(10);//設定預設列高sheet.setDefaultColumnWidth(20);//設定預設列寬//設定指定列的列寬，256 * 50這種寫法是因為width參數單位是單個字元的256分之一sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50);

　　2.5.2儲存格樣式

　　1、建立HSSFCellStyle

 HSSFCellStyle cellStyle=wkb.createCellStyle();

　　2、設定樣式

 // 設定儲存格的橫向和縱向對齊，具體參數就不列了，參考HSSFCellStyle  cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);  cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  /* 設定儲存格的填充方式，以及前景顏色和背景顏色   三點注意：   1.如果需要前景顏色或背景顏色，一定要指定填充方式，兩者順序無所謂；   2.如果同時存在前景顏色和背景顏色，前景顏色的設定要寫在前面；   3.前景顏色不是字型顏色。  */  //設定填充方式(填充圖案)  cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);  //設定前景色彩  cellStyle.setFillForegroundColor(HSSFColor.RED.index);  //設定背景顏色  cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);  // 設定儲存格底部的邊框及其樣式和顏色  // 這裡僅設定了底邊邊框，左邊框、右邊框和頂邊框同理可設  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);  cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);  //設定日期型資料的顯示樣式  cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));  

　　3、將樣式應用於儲存格

  cell.setCellStyle(cellStyle);  //將樣式應用到行，但有些樣式只對儲存格起作用  row.setRowStyle(cellStyle);

　　2.5.2設定字型樣式

　　1、建立HSSFFont對象（調用HSSFWorkbook 的createFont方法）

HSSFWorkbook wb=new HSSFWorkbook();HSSFFont  fontStyle=wb.createFont();HSSFWorkbook wb=new HSSFWorkbook ();

　　2、設定字型各種樣式

  //設定字型樣式  fontStyle.setFontName("宋體");    //設定字型高度  fontStyle.setFontHeightInPoints((short)20);    //設定字型顏色  font.setColor(HSSFColor.BLUE.index);  //設定粗體  fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  //設定斜體font.setItalic(true);//設定底線font.setUnderline(HSSFFont.U_SINGLE);

　　3、將字型設定到儲存格樣式

//字型也是儲存格格式的一部分，所以從屬於HSSFCellStyle// 將字型對象賦值給儲存格樣式對象cellStyle.setFont(font);// 將儲存格樣式應用於儲存格cell.setCellStyle(cellStyle);

2.6 匯入Excel應用執行個體

　　實現將已存在的Excel檔案中的資料匯入到系統中的基本步驟同匯出十分的類似，關鍵在於要瞭解要匯入Excel檔案的結構，比如資料有哪些列、讀取資料起始位置（有效資料從第幾行幾列開始）等。在實際項目中由於這些資料（Excel檔案）往往來自於日常辦公人員或其他系統平台產生的業務資料，因此這些Excel檔案的資料格式要有統一的要求，並提供提供者（指訪問途徑），這樣在所需資料的系統中就可通過提供這個提供者調用方法，從而獲得資料。解決方案採用Web Service是不錯的選擇。這裡，我們就以匯入2..4所產生的excel表為例，重點掌握如何編寫匯入Excel代碼

public List<ScoreInfo> loadScoreInfo(String xlsPath) throws IOException{    List temp = new ArrayList();FileInputStream fileIn = new FileInputStream(xlsPath);//根據指定的檔案輸入資料流匯入Excel從而產生Workbook對象Workbook wb0 = new HSSFWorkbook(fileIn);//擷取Excel文檔中的第一個表單Sheet sht0 = wb0.getSheetAt(0);//對Sheet中的每一行進行迭代        for (Row r : sht0) {        //如果當前行的行號（從0開始）未達到2（第三行）則從新迴圈If(r.getRowNum()<1){continue;}//建立實體類ScoreInfo info=new ScoreInfo();//取出當前行第1個儲存格資料，並封裝在info實體stuName屬性上info.setStuName(r.getCell(0).getStringCellValue());info.setClassName(r.getCell(1).getStringCellValue());info.setRscore(r.getCell(2).getNumericCellValue());            info.setLscore(r.getCell(3).getNumericCellValue());temp.add(info);        }        fileIn.close();            return temp;        }

三、使用java Excel操作Excel檔案

 　　Java Excel是一開放源碼項目，通過它Java開發人員可以讀取Excel檔案的內容、建立新的Excel檔案、更新已經存在的Excel檔案。jxl 由於其小巧 易用的特點, 逐漸已經取代了 POI-excel的地位, 成為了越來越多的java開發人員產生excel檔案的首選。Java  Excel的特徵：

● 支援Excel 95-2000的所有版本 
         ● 產生Excel 2000標準格式 
         ● 支援字型、數字、日期格式化操作 
         ● 支援對儲存格加陰影和加色彩；

● 修改存在的工作表；
         ● 支援映像和圖表

● 日誌記錄可以定製

● 更小更快更省記憶體

　　應該說以上功能已經能夠大致滿足我們的需要。最關鍵的是這套API是純Java的，並不依賴Windows系統，即使運行在Linux下，它同樣能夠正確 的處理Excel檔案。另外需要說明的是，這套API對圖形和圖表的支援很有限，而且僅僅識別PNG格式。 線上協助文檔http://jexcelapi.sourceforge.net/resources/javadocs/2_6_10/docs/index.html

　　在這裡我們將通過一些執行個體，學習掌握讀取、建立、更新，其中也包括常見格式的設定:字型、顏色、背景、合併儲存格等操作，有這些其實已經基本足夠應付大部分問題了。

3.1環境配置　　3.1.1下載

　　    http://www.andykhan.com/jexcelapi/

　　3.1.2 加入jar包

　　將jxl.jar拷貝到項目的Lib下

3.2 使用Java Excel Api 匯出 Excel檔案

　　下面我們在看如何使用Java Excel實現匯出Excel表格？

　　代碼如下：（實際開發中應封裝到業務層組件中，然後在控制層中調用。這裡直接寫在控制層組件，如Servlet的doGet/doPost方法或Struts架構的execute方法中）

 

//獲得輸出資料流，該輸出資料流的輸出介質是用戶端瀏覽器  OutputStream output=response.getOutputStream();  response.reset();  response.setHeader("Content-disposition","attachment;           filename=temp.xls");  response.setContentType("application/msexcel");  //建立可寫入的Excel工作薄，且內容將寫入到輸出資料流，並通過輸出資料流輸出給用戶端瀏覽
  WritableWorkbook wk=Workbook.createWorkbook(output);///建立可寫入的Excel工作表    WritableSheet sheet=wk.createSheet("成績表", 0);//把儲存格（column, row）到儲存格（column1, row1）進行合并。//mergeCells(column, row, column1, row1);  sheet.mergeCells(0,0, 4,0);//儲存格合并方法//建立WritableFont 字型對象，參數依次表示黑體、字型大小12、粗體、非斜體、不帶底線、亮藍色WritableFont titleFont=new WritableFont(WritableFont.createFont("黑體"),12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.LIGHT_BLUE);//建立WritableCellFormat對象，將該對象應用於儲存格從而設定儲存格的樣式WritableCellFormat titleFormat=new WritableCellFormat();//設定字型格式titleFormat.setFont(titleFont);//設定文本水平置中對齊titleFormat.setAlignment(Alignment.CENTRE);//設定文本垂直置中對齊titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//設定背景顏色titleFormat.setBackground(Colour.GRAY_25);//設定自動換行titleFormat.setWrap(true);//添加Label對象，參數依次表示在第一列，第一行，內容，使用的格式Label lab_00=new Label(0,0,"學員考試成績一覽表",titleFormat);//將定義好的Label對象添加到工作表上，這樣工作表的第一列第一行的內容為‘學員考試成績一覽表’並應用了titleFormat定義的樣式sheet.addCell(lab_00);WritableCellFormat cloumnTitleFormat=new WritableCellFormat();cloumnTitleFormat.setFont(new WritableFont(WritableFont.createFont("宋體"),10,WritableFont.BOLD,false));cloumnTitleFormat.setAlignment(Alignment.CENTRE);Label lab_01=new Label(0,1,"姓名",cloumnTitleFormat);Label lab_11=new Label(1,1,"班級",cloumnTitleFormat);Label lab_21=new Label(2,1,"筆試成績",cloumnTitleFormat);
Label lab_31=new Label(3,1,"上機成績",cloumnTitleFormat);Label lab_41=new Label(4,1,"考試日期",cloumnTitleFormat);  sheet.addCell(lab_01);  sheet.addCell(lab_11);  sheet.addCell(lab_21);  sheet.addCell(lab_31);  sheet.addCell(lab_41);  sheet.addCell(new Label(0,2,"李明"));  sheet.addCell(new Label(1,2,"As178"));//定義數字格式NumberFormat nf=new NumberFormat("0.00");WritableCellFormat wcf=new WritableCellFormat(nf);//類似於Label對象，區別Label表示文本資料，Number表示數值型資料Number numlab_22=new Number(2,2,78,wcf);sheet.addCell(numlab_22);sheet.addCell(newNumber(3,2,87,new WritableCellFormat(new NumberFormat("#.##") )));//定義日期格式DateFormat df=new DateFormat("yyyy-MM-dd hh:mm:ss");//建立WritableCellFormat對象WritableCellFormat datewcf=new WritableCellFormat(df);//類似於Label對象，區別Label表示文本資料，DateTime表示日期型資料DateTime dtLab_42=new DateTime(4,2,new Date(),datewcf);sheet.addCell(dtLab_42);   //將定義的工作表輸出到之前指定的介質中（這裡是用戶端瀏覽器）wk.write();//操作完成時，關閉對象，釋放佔用的記憶體空間   wk.close();

　　加底線這部分代碼是B/S模式中採用的輸出方式，而不是輸出到本地指定的磁碟目錄。該代碼錶示將temp.xls的Excel檔案通過應答實體（response）輸出給請求的用戶端瀏覽器，下載到用戶端本地（儲存或直接開啟）。若要直接輸出到磁碟檔案可採用下列代碼替換加底線這部分代碼

File file=new File("D://temp.xls");WritableWorkbook wwb = Workbook.createWorkbook(file); 

3.3進階操作　　3.3.1資料格式化

　　在Excel中不涉及複雜的資料類型，能夠比較好的處理字串、數字和日期已經能夠滿足一般的應用即可。

　　資料的格式化涉及到的是字型、粗細、字型大小等元素，這些功能主要由 WritableFont和WritableCellFormat類來負責。例如：

① WritableFont font=new WritableFont(WritableFont.createFont("宋體"),12,WritableFont.NO_BOLD );

② WritableCellFormat format1=new WritableCellFormat(font);

③ Label label=new Label(0,0,”data 4 test”,format1);

　　其中

I.指定了字串格式：字型為宋體，字型大小16，加粗顯示。WritableFont有非常豐富的構造子，供不同情況下使用，jExcelAPI的java-doc中有詳細列表，這裡不再列出。

II. 處代碼使用了WritableCellFormat類，這個類非常重要，通過它可以指定儲存格的各種屬性，如上例代碼所示。

III. 處使用了Label類的構造子，指定了顯示的位置，常值內容，字串被賦予的格式。

與Label類似的Number、DateTime，區別Label表示文本資料；Number表示數值資料，可使NumberFormat格式化資料；用DateTime表示日期型資料，可應用DateFormat格式化資料。

　　3.3.2儲存格操作

　　Excel中很重要的一部分是對儲存格的操作，比如行高、列寬、儲存格合并等，所幸jExcelAPI提供了這些支援。這些操作相對比較簡單，下面只介紹一下相關的API。

　　1、 合併儲存格

WritableSheet.mergeCells(int m,int n,int p,int q);//作用是從(m,n)到(p,q)的儲存格全部合并，比如：WritableSheet sheet=book.createSheet(“第一頁”,0);//合并第一列第一行到第六列第一行的所有儲存格sheet.mergeCells(0,0,5,0);//合并既可以是橫向的，也可以是縱向的。合并後的儲存格不能再次進行合并，否則會觸發異常。

　　2、 行高和列寬

 writableSheet.setRowView(int i,int height);//作用是指定第i+1行的高度，比如：// 將第一行的高度設為200sheet.setRowView(0,200);WritableSheet.setColumnView(int i,int width);//作用是指定第i+1列的寬度，比如：//將第一列的寬度設為30sheet.setColumnView(0,30); 

3.4 　從Excel檔案讀取資料表

　　我們就以匯入3.2所產生的excel表為例，掌握如何編寫匯入Excel代碼(該代碼封裝在業務層方法)

public List<ScoreInfo> loadScoreInfo(String xlsPath) throws IOException, BiffException{//匯入已存在的Excel檔案，獲得唯讀工作薄對象        FileInputStream fis=new FileInputStream(xlsPath);        Workbook wk=Workbook.getWorkbook(fis);        //擷取第一張Sheet表         Sheet sheet=wk.getSheet(0);        //擷取總行數int rowNum=sheet.getRows();//從資料行開始迭代每一行        for(int i=2;i<rowNum;i++){ScoreInfo info=new ScoreInfo();        //getCell(column,row)，表示取得指定列指定行的儲存格（Cell）//getContents()擷取儲存格的內容，返回字串資料。適用於字元型資料的儲存格//使用實體類封裝儲存格資料info.setStuName(sheet.getCell(0, i).getContents());            info.setClassName(sheet.getCell(1, i).getContents());            //判斷儲存格的類型，儲存格主要類型LABEL、NUMBER、DATE                    if(sheet.getCell(2,i).getType==CellType.NUMBER){//轉化為數值型儲存格NumberCell numCell=(NumberCell)sheet.getCell(2,i);//NumberCell的getValue()方法取得儲存格的數值型資料info.setRscore(numCell.getValue());}if(sheet.getCell(3,i).getType==CellType.NUMBER){NumberCell numCell=(NumberCell)sheet.getCell(3,i);info.setRscore(numCell.getValue);}if(sheet.getCell(4,i).getType==CellType.DATE){DateCell dateCell=(DateCell)sheet.getCell(4,i);//DateCell的getDate()方法取得儲存格的日期型資料info.setDate(dateCell.getDate());}        }        fis.close();        wk.close();}

3.4 　更新已存在的Excel檔案

　　將3.2所產生的excel表（temp.xls）的第一條記錄（excel檔案的指第三行）的班級名稱改為As179，代碼如下：

File file=new File("d://temp.xls");//匯入已存在的Excel檔案，獲得唯讀工作薄對象Workbook wk=Workbook.getWorkbook(file);//根據唯讀工作薄對象（wk）建立可寫入的Excel工作薄對象         WritableWorkbook wwb=Workbook.createWorkbook(file, wk);//讀取第一張工作表         WritableSheet sheet=wwb.getSheet(0);///獲得要編輯的儲存格對象         WritableCell cell=sheet.getWritableCell(1, 2);        //判斷儲存格的類型, 做出相應的轉化 if(cell.getType()==CellType.LABEL){            Label lable=(Label)cell;            //修改儲存格的內容lable.setString("As179");        }    wwb.write();     wwb.close();     wk.close(); 

　　對於更新已存在的Excel檔案實際上就是擷取已有工作薄對象（但是唯讀），然後將擷取的唯讀工作薄對象轉化為可寫入的Excel工作薄對象（WritableWorkbook ），其他部分就是通過可寫入WritableSheet 對象和可寫入WritableCell 對象進行編輯。

Java中匯入、匯出Excel