資料以excel格式輸出和讀取excel檔案資料

Java中導入、導出Excel

釋出時間：2018-06-16

一、介紹

　　目前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)這裡可以下載下傳到它的最新版本和文檔，目前最新版本是4.1，這裡使用比較穩定的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);

//設定填充方式(填充圖案)

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 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行的高度，比如：  // 将第一行的高度設為200
  sheet.setRowView(0,200);  WritableSheet.setColumnView(int i,int width);  //作用是指定第i+1列的寬度，比如：  //将第一列的寬度設為30
  sheet.setColumnView(0,30); 
           

3.4 　從Excel檔案讀取資料表

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

複制内容到剪貼闆

public List 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();