Java之使用POI教你玩轉(zhuǎn)Excel導(dǎo)入與導(dǎo)出

第Java之使用POI教你玩轉(zhuǎn)Excel導(dǎo)入與導(dǎo)出目錄POI簡(jiǎn)介POI依賴文件POI常用類數(shù)據(jù)導(dǎo)出設(shè)置樣式數(shù)據(jù)導(dǎo)入在平時(shí)開發(fā)項(xiàng)目時(shí)對(duì)數(shù)據(jù)的處理肯定是少不了的。對(duì)于數(shù)據(jù)處理也是很多程序員比較頭疼的問(wèn)題，就比如項(xiàng)目中的數(shù)據(jù)是如何添加進(jìn)去呢？一條一條的錄入？好像又有點(diǎn)不太友好，數(shù)據(jù)多了效率太低了，最關(guān)鍵的是甲方爸爸肯定不會(huì)滿意的。

這時(shí)我們可以使用POI來(lái)操作Excel表格，可以通過(guò)POI來(lái)把Excel中的數(shù)據(jù)批量導(dǎo)入到數(shù)據(jù)庫(kù)中，從而簡(jiǎn)化操作，提高效率。反之我們還可以通過(guò)POI把數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)出到Excel表格中。

POI簡(jiǎn)介

ApachePOI是Apache軟件基金會(huì)的開放源碼函式庫(kù)，POI提供API給Java程序?qū)icrosoftOffice格式檔案讀和寫的功能。

POI依賴文件

dependency

groupIdorg.apache.poi/groupId

artifactIdpoi/artifactId

version3.17/version

/dependency

POI常用類

類名說(shuō)明HSSFWorkbookExcel的文件對(duì)象HSSFSheetExcel的表單HSSFRowExcel的行HSSFCellExcel的格子單元HSSFFontExcel字體HSSFDataFormat格子單元的日期格式HSSFHeaderExcel文檔Sheet的頁(yè)眉HSSFFooterExcel文檔Sheet的頁(yè)腳樣式：HSSFCellStylecell樣式輔助操作包括：HSSFDateUtil日期HSSFPrintSetup打印HSSFErrorConstants錯(cuò)誤信息表

數(shù)據(jù)導(dǎo)出

首先，理解一下一個(gè)Excel的文件的組織形式，一個(gè)Excel文件對(duì)應(yīng)于一個(gè)workbook(HSSFWorkbook)，一個(gè)workbook可以有多個(gè)sheet（HSSFSheet）表單組成，一個(gè)sheet是由多個(gè)row（HSSFRow）行組成，一個(gè)row是由多個(gè)cell（HSSFCell）單元格組成。

所以對(duì)應(yīng)的操作步驟如下：

用HSSFWorkbook打開或者創(chuàng)建Excel文件對(duì)象。用HSSFWorkbook對(duì)象返回或者創(chuàng)建Sheet對(duì)象。用Sheet對(duì)象返回行對(duì)象，用行對(duì)象得到Cell對(duì)象。對(duì)Cell對(duì)象讀寫。

下面來(lái)看一下數(shù)據(jù)導(dǎo)出功能

前臺(tái)頁(yè)面只需要給出一個(gè)跳轉(zhuǎn)到后臺(tái)導(dǎo)出方法的鏈接即可。

button導(dǎo)出/button

后臺(tái)對(duì)應(yīng)導(dǎo)出的方法，代碼如下：

@RequestMapping(value="/export")

@ResponseBody

publicvoidexport(HttpServletRequestrequest,HttpServletResponseresponse)throwsException{

//查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)

ListStaffstaffList=staffService.findAll();

//創(chuàng)建excel的文檔對(duì)象

HSSFWorkbookworkbook=newHSSFWorkbook();

//創(chuàng)建員工信息工作薄，表單

HSSFSheetsheet=workbook.createSheet("員工信息");

//在sheet里創(chuàng)建第一行，參數(shù)為行索引(excel的行)，從0開始

HSSFRowrow1=sheet.createRow(0);

//創(chuàng)建單元格excel的單元格，參數(shù)為列索引，從0開始

HSSFCellcell=row1.createCell(0);

//設(shè)置單元格內(nèi)容，標(biāo)題第一行（可以不設(shè)置，根據(jù)項(xiàng)目需求）

cell.setCellValue("員工信息");

//合并單元格CellRangeAddress構(gòu)造參數(shù)依次表示起始行，截至行，起始列，截至列，一行標(biāo)題合并單元格

sheet.addMergedRegion(newCellRangeAddress(0,0,0,3));

//設(shè)置列名（每列的小標(biāo)題）

String[]fieldnames={"姓名","部門","年齡","工號(hào)"};

//在sheet里創(chuàng)建第二行，第一行設(shè)置了標(biāo)題

HSSFRowrow2=sheet.createRow(1);

for(inti=0;ifieldnames.length;i++){

row2.createCell(i).setCellValue(fieldnames[i]);//設(shè)置列名

//填充導(dǎo)出的數(shù)據(jù)到Excel中

for(inti=0;istaffList.size();i++){

HSSFRowrows=sheet.createRow(i+2);

HSSFCellcellName=rows.createCell(0);

cellName.setCellValue(staffList.get(i).getName());

HSSFCellcell1Dept=rows.createCell(1);

cell1Dept.setCellValue(staffList.get(i).getDept());

HSSFCellcell1Age=rows.createCell(2);

cell1Age.setCellValue(staffList.get(i).getAge());

HSSFCellcell1Number=rows.createCell(3);

cell1Number.setCellValue(staffList.get(i).getNumber());

//輸出Excel文件

OutputStreamoutput=response.getOutputStream();

response.reset();

//filename可以設(shè)置Excel文件的名稱

response.setHeader("Content-disposition","attachment;filename=staff.xls");

response.setContentType("application/msexcel");

workbook.write(output);

output.close();

注釋怎么清楚，就不用多解釋了吧，(#^.^#)。

導(dǎo)出結(jié)果如下：

設(shè)置樣式

上面導(dǎo)出的內(nèi)容大家也看到了，就是最原始的樣式。其實(shí)在導(dǎo)出時(shí)我們也可以對(duì)其Excel設(shè)置相應(yīng)的樣式。

1、合并單元格

使用HSSFSheet類中的addMergedRegion(CellRangeAddressregion)方法，上面導(dǎo)出也用到過(guò)。

參數(shù)CellRangeAddress表示合并的區(qū)域，方法如下：

publicCellRangeAddress(intfirstRow,intlastRow,intfirstCol,intlastCol)

其中參數(shù)依次表示起始行，截至行，起始列，截至列。有興趣的小伙伴可以點(diǎn)進(jìn)封裝類中看一下。

2、設(shè)置單元格的大小

HSSFSheetsheet=workbook.createSheet("員工信息");//創(chuàng)建工作表(Sheet)

HSSFRowrow=sheet.createRow(1);

HSSFCellcell=row.createCell(1);

cell.setCellValue("張三");

sheet.setColumnWidth(1,256*50);//設(shè)置第一列的寬度，為50個(gè)字符寬度

row.setHeightInPoints(50);//設(shè)置一行的高度

setColumnWidth方法和setHeightInPoints方法適合這是部分的樣式，如果需要設(shè)置全部樣式，可以使用HSSFSheet.setDefaultColumnWidth和HSSFSheet.setDefaultRowHeightInPoints方法設(shè)置默認(rèn)的列寬和行高。

3、設(shè)置單元格樣式

單元格樣式是通過(guò)HSSFCellStyle類來(lái)設(shè)置的，所以我們需要先得到HSSFCellStyle類

HSSFCellStylestyle=workbook.createCellStyle()

3.1、設(shè)置水平對(duì)齊方式

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

水平對(duì)齊相關(guān)參數(shù)

如果是左側(cè)對(duì)齊就是HSSFCellStyle.ALIGN_FILL。如果是居中對(duì)齊就是HSSFCellStyle.ALIGN_CENTER。如果是右側(cè)對(duì)齊就是HSSFCellStyle.ALIGN_RIGHT。如果是跨列舉中就是HSSFCellStyle.ALIGN_CENTER_SELECTION。如果是兩端對(duì)齊就是HSSFCellStyle.ALIGN_JUSTIFY。

3.2、設(shè)置垂直對(duì)齊方式

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

垂直對(duì)齊相關(guān)參數(shù)

如果是靠上就是HSSFCellStyle.VERTICAL_TOP。如果是居中就是HSSFCellStyle.VERTICAL_CENTER。如果是靠下就是HSSFCellStyle.VERTICAL_BOTTOM。如果是兩端對(duì)齊就是HSSFCellStyle.VERTICAL_JUSTIFY。

3.3、設(shè)置邊框

style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上邊框

style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下邊框

style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左邊框

style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右邊框

style.setTopBorderColor(HSSFColor.RED.index);//上邊框顏色

style.setBottomBorderColor(HSSFColor.BLUE.index);//下邊框顏色

style.setLeftBorderColor(HSSFColor.GREEN.index);//左邊框顏色

style.setRightBorderColor(HSSFColor.PINK.index);//右邊框顏色

3.4、應(yīng)用樣式

//用在某一個(gè)單元格中

cell.setCellStyle(cellStyle);

//用在一行中，設(shè)置一行的樣式

row.setRowStyle(cellStyle);

4、設(shè)置字體樣式

字體樣式是通過(guò)HSSFFont類來(lái)設(shè)置的，所以我們需要先得到HSSFFont類

HSSFFontfont=workbook.createFont()

設(shè)置樣式

font.setFontName("華文行楷");//設(shè)置字體名稱

font.setFontHeightInPoints((short)28);//設(shè)置字號(hào)

font.setColor(HSSFColor.RED.index);//設(shè)置字體顏色

font.setUnderline(FontFormatting.U_SINGLE);//設(shè)置下劃線

font.setTypeOffset(FontFormatting.SS_SUPER);//設(shè)置上標(biāo)下標(biāo)

font.setStrikeout(true);//設(shè)置刪除線

下劃線選項(xiàng)值：

單下劃線FontFormatting.U_SINGLE雙下劃線FontFormatting.U_DOUBLE會(huì)計(jì)用單下劃線FontFormatting.U_SINGLE_ACCOUNTING會(huì)計(jì)用雙下劃線FontFormatting.U_DOUBLE_ACCOUNTING無(wú)下劃線FontFormatting.U_NONE

上標(biāo)下標(biāo)選項(xiàng)值：

上標(biāo)FontFormatting.SS_SUPER下標(biāo)FontFormatting.SS_SUB普通，默認(rèn)值FontFormatting.SS_NONE

應(yīng)用樣式

//字體也是單元格格式的一部分，所以從屬于HSSFCellStyle

//將字體對(duì)象賦值給單元格樣式對(duì)象

style.setFont(font);

//將單元格樣式應(yīng)用于單元格

cell.setCellStyle(cellStyle);

數(shù)據(jù)導(dǎo)入

導(dǎo)入數(shù)據(jù)時(shí)，頁(yè)面需要給出一個(gè)選擇文件的標(biāo)簽，頁(yè)面就不多說(shuō)了，只要有一個(gè)上傳的標(biāo)簽即可。

這是用layui寫的一個(gè)簡(jiǎn)單的頁(yè)面，代碼如下：

styletype="text/css"

#updateFile{

margin:20px0px20px50px;

/style

formid="importForm"method="post"

div

buttontype="button"name="file"id="updateFile"選擇文件/button

/div

div

div

inputid="upload"value="導(dǎo)入"/

ahref="staff/exportmoban"rel="externalnofollow"下載模板/a

/div

/div

/form

scripttype="text/javascript"

layui.use(['form','upload'],function(){

varupload=layui.upload;

upload.render({

elem:'#updateFile',

url:'staff/excelimport',

auto:false,

accept:'file',

exts:'xls|excel|xlsx',

bindAction:'#upload',

done:function(res){//導(dǎo)出成功后回調(diào)

/script

其中下載模板和導(dǎo)出數(shù)據(jù)基本一樣，只需要?jiǎng)?chuàng)建一個(gè)Excel給出一條樣式數(shù)據(jù)即可，代碼如下：

@RequestMapping(value="exportmoban")

@ResponseBody

publicvoidexportmoban(HttpServletRequestrequest,HttpServletResponseresponse)throwsException{

//創(chuàng)建excel的文檔對(duì)象

HSSFWorkbookworkbook=newHSSFWorkbook();

//創(chuàng)建員工信息工作薄，表單

HSSFSheetsheet=workbook.createSheet("員工信息");

//在sheet里創(chuàng)建第一行，參數(shù)為行索引(excel的行)，從0開始

HSSFRowrow1=sheet.createRow(0);

//創(chuàng)建單元格excel的單元格，參數(shù)為列索引，從0開始

HSSFCellcell=row1.createCell(0);

//設(shè)置單元格內(nèi)容，標(biāo)題第一行（可以不設(shè)置，根據(jù)項(xiàng)目需求）

cell.setCellValue("員工信息");

//合并單元格CellRangeAddress構(gòu)造參數(shù)依次表示起始行，截至行，起始列，截至列，一行標(biāo)題合并單元格

sheet.addMergedRegion(newCellRangeAddress(0,0,0,3));

//設(shè)置列名（每列的小標(biāo)題）

String[]fieldnames={"姓名","部門","年齡","工號(hào)"};

//在sheet里創(chuàng)建第二行，第一行設(shè)置了標(biāo)題

HSSFRowrow2=sheet.createRow(1);

for(inti=0;ifieldnames.length;i++){

row2.createCell(i).setCellValue(fieldnames[i]);//設(shè)置列名

//模板數(shù)據(jù)

HSSFRowrow3=sheet.createRow(2);

row3.createCell(0).setCellValue("王五");

row3.createCell(1).setCellValue("軟件部");

row3.createCell(2).setCellValue(“18“);

row3.createCell(3).setCellValue("003");

//輸出Excel文件

OutputStreamoutput=response.getOutputStream();

response.reset();

//filename可以設(shè)置Excel文件的名稱

response.setHeader("Content-disposition","attachment;filename=staff.xls");

response.setContentType("application/msexcel");

workbook.write(output);

output.close();

}

模板下載如圖。

導(dǎo)入數(shù)據(jù)，我們只需要選擇一個(gè)有數(shù)據(jù)的Excel表格（數(shù)據(jù)格式需要和模板中格式一樣），點(diǎn)擊導(dǎo)入即可。

后臺(tái)對(duì)應(yīng)導(dǎo)入數(shù)據(jù)的excelimport方法，代碼如下：

@RequestMapping(value="/excelimport")

@ResponseBody

publicbooleanexcelimport(MultipartFilefile){

//實(shí)例化工具類

ImportExcelexcelReader=newImportExcel();

try{

InputStreamis=file.getInputStream();

//導(dǎo)入excel

excelReader.readExcelContent(is);

MapInteger,Stringmap=excelReader.readExcelContent(is);

//遍歷數(shù)據(jù)保存

//因?yàn)榈谝恍泻偷诙惺菢?biāo)題，所以從2開始

for(inti=2;i=map.size()+1;i++){

String[]scoreArray=map.get(i).split("-");

if(scoreArray.length0){

Stringname=scoreArray[0];

Stringdept=scoreArray[1];

Integerage=Integer.parseInt(scoreArray[2]);

Stringnumber=scoreArray[3];

Staffstaff=newStaff();

staff.setName(name);

staff.setDept(dept);

staff.setAge(age);

staff.setNumber(number);

//保存

staffService.save(staff);

returntrue;

}catch(Exceptione){

e.printStackTrace();

returnfalse;

}

注：如果對(duì)數(shù)據(jù)需要做一些效驗(yàn)的話可以放在for循環(huán)中處理，這里只是寫一個(gè)小案例，沒有添加任何效驗(yàn)，根據(jù)項(xiàng)目需求自己添加。

其中ImportExcel類是一個(gè)處理導(dǎo)入的Excel數(shù)據(jù)的封裝類。代碼如下：

importorg.apache.poi.hssf.usermodel.*;

importorg.apache.poi.poifs.filesystem.POIFSFileSystem;

importjava.io.FileInputStream;

importjava.io.FileNotFoundException;

importjava.io.IOException;

importjava.io.InputStream;

importjava.text.SimpleDateFormat;

importjava.util.Date;

importjava.util.HashMap;

importjava.util.Map;

publicclassImportExcel{

privatePOIFSFileSystemfs;

privateHSSFWorkbookwb;

privateHSSFSheetsheet;

privateHSSFRowrow;

*讀取Excel表格表頭的內(nèi)容

*@paramis

*@returnString表頭內(nèi)容的數(shù)組

publicString[]readExcelTitle(InputStreamis){

try{

fs=newPOIFSFileSystem(is);

wb=newHSSFWorkbook(fs);

}catch(IOExceptione){

e.printStackTrace();

sheet=wb.getSheetAt(0);

//得到首行的row

row=sheet.getRow(0);

//標(biāo)題總列數(shù)

intcolNum=row.getPhysicalNumberOfCells();

String[]title=newString[colNum];

for(inti=0;icolNum;i++){

title[i]=getCellFormatValue(row.getCell((short)i));

returntitle;

*讀取Excel數(shù)據(jù)內(nèi)容

*@paramis

*@returnMap包含單元格數(shù)據(jù)內(nèi)容的Map對(duì)象

publicMapInteger,StringreadExcelContent(InputStreamis){

MapInteger,Stringcontent=newHashMapInteger,String

Stringstr="";

try{

fs=newPOIFSFileSystem(is);

wb=newHSSFWorkbook(fs);

}catch(IOExceptione){

//e.printStackTrace();

sheet=wb.getSheetAt(0);

//得到總行數(shù)

introwNum=sheet.getLastRowNum();

//由于第0行和第一行已經(jīng)合并了在這里索引從2開始

row=sheet.getRow(2);

intcolNum=row.getPhysicalNumberOfCells();

//正文內(nèi)容應(yīng)該從第二行開始,第一行為表頭的標(biāo)題

for(inti=2;i=rowNum;i++){

row=sheet.getRow(i);

intj=0;

while(jcolNum){

str+=getCellFormatValue(row.getCell((short)j)).trim()+"-";

j++;

content.put(i,str);

str="";

returncontent;

*獲取單元格數(shù)據(jù)內(nèi)容為字符串類型的數(shù)據(jù)

*@paramcellExcel單元格

*@returnString單元格數(shù)據(jù)內(nèi)容

privateStringgetStringCellValue(HSSFCellcell){

StringstrCell="";

switch(cell.getCellType()){

caseHSSFCell.CELL_TYPE_STRING:

strCell=cell.getStringCellValue();

break;

caseHSSFCell.CELL_TYPE_NUMERIC:

strCell=String.valueOf(cell.getNumericCellValue());

break;

caseHSSFCell.CELL_TYPE_BOOLEAN:

strCell=String.valueOf(cell.getBooleanCellValue());

break;

caseHSSFCell.CELL_TYPE_BLANK:

strCell="";

break;

default:

strCell="";

break;

if(strCell.equals("")||strCell==null){

return"";

if(cell==null){

return"";

returnstrCell;

*獲取單元格數(shù)據(jù)內(nèi)容為日期類型的數(shù)據(jù)

*@paramcell

*Excel單元格

*@returnString單元格數(shù)據(jù)內(nèi)容

privateStringgetDateCellValue(HSSFCellcell){

Stringresult="";

try{

intcellType=cell.getCellType();

if(cellType==HSSFCell.CELL_TYPE_NUMERIC){

Datedate=cell.getDateCellValue();

result=(date.getYear()+1900)+"-"+(date.getMonth(