Java之使用POI教你玩转Excel导入与导出

第Java之使用POI教你玩转Excel导入与导出目录POI简介POI依赖文件POI常用类数据导出设置样式数据导入在平时开发项目时对数据的处理肯定是少不了的。对于数据处理也是很多程序员比较头疼的问题，就比如项目中的数据是如何添加进去呢？一条一条的录入？好像又有点不太友好，数据多了效率太低了，最关键的是甲方爸爸肯定不会满意的。

这时我们可以使用POI来操作Excel表格，可以通过POI来把Excel中的数据批量导入到数据库中，从而简化操作，提高效率。反之我们还可以通过POI把数据库中的数据导出到Excel表格中。

POI简介

ApachePOI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对MicrosoftOffice格式档案读和写的功能。

POI依赖文件

dependency

groupIdorg.apache.poi/groupId

artifactIdpoi/artifactId

version3.17/version

/dependency

POI常用类

类名说明HSSFWorkbookExcel的文件对象HSSFSheetExcel的表单HSSFRowExcel的行HSSFCellExcel的格子单元HSSFFontExcel字体HSSFDataFormat格子单元的日期格式HSSFHeaderExcel文档Sheet的页眉HSSFFooterExcel文档Sheet的页脚样式：HSSFCellStylecell样式辅助操作包括：HSSFDateUtil日期HSSFPrintSetup打印HSSFErrorConstants错误信息表

数据导出

首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）表单组成，一个sheet是由多个row（HSSFRow）行组成，一个row是由多个cell（HSSFCell）单元格组成。

所以对应的操作步骤如下：

用HSSFWorkbook打开或者创建Excel文件对象。用HSSFWorkbook对象返回或者创建Sheet对象。用Sheet对象返回行对象，用行对象得到Cell对象。对Cell对象读写。

下面来看一下数据导出功能

前台页面只需要给出一个跳转到后台导出方法的链接即可。

button导出/button

后台对应导出的方法，代码如下：

@RequestMapping(value="/export")

@ResponseBody

publicvoidexport(HttpServletRequestrequest,HttpServletResponseresponse)throwsException{

//查询数据库中的数据

ListStaffstaffList=staffService.findAll();

//创建excel的文档对象

HSSFWorkbookworkbook=newHSSFWorkbook();

//创建员工信息工作薄，表单

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

//在sheet里创建第一行，参数为行索引(excel的行)，从0开始

HSSFRowrow1=sheet.createRow(0);

//创建单元格excel的单元格，参数为列索引，从0开始

HSSFCellcell=row1.createCell(0);

//设置单元格内容，标题第一行（可以不设置，根据项目需求）

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

//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列，一行标题合并单元格

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

//设置列名（每列的小标题）

String[]fieldnames={"姓名","部门","年龄","工号"};

//在sheet里创建第二行，第一行设置了标题

HSSFRowrow2=sheet.createRow(1);

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

row2.createCell(i).setCellValue(fieldnames[i]);//设置列名

//填充导出的数据到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可以设置Excel文件的名称

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

response.setContentType("application/msexcel");

workbook.write(output);

output.close();

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

导出结果如下：

设置样式

上面导出的内容大家也看到了，就是最原始的样式。其实在导出时我们也可以对其Excel设置相应的样式。

1、合并单元格

使用HSSFSheet类中的addMergedRegion(CellRangeAddressregion)方法，上面导出也用到过。

参数CellRangeAddress表示合并的区域，方法如下：

publicCellRangeAddress(intfirstRow,intlastRow,intfirstCol,intlastCol)

其中参数依次表示起始行，截至行，起始列，截至列。有兴趣的小伙伴可以点进封装类中看一下。

2、设置单元格的大小

HSSFSheetsheet=workbook.createSheet("员工信息");//创建工作表(Sheet)

HSSFRowrow=sheet.createRow(1);

HSSFCellcell=row.createCell(1);

cell.setCellValue("张三");

sheet.setColumnWidth(1,256*50);//设置第一列的宽度，为50个字符宽度

row.setHeightInPoints(50);//设置一行的高度

setColumnWidth方法和setHeightInPoints方法适合这是部分的样式，如果需要设置全部样式，可以使用HSSFSheet.setDefaultColumnWidth和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽和行高。

3、设置单元格样式

单元格样式是通过HSSFCellStyle类来设置的，所以我们需要先得到HSSFCellStyle类

HSSFCellStylestyle=workbook.createCellStyle()

3.1、设置水平对齐方式

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

水平对齐相关参数

如果是左侧对齐就是HSSFCellStyle.ALIGN_FILL。如果是居中对齐就是HSSFCellStyle.ALIGN_CENTER。如果是右侧对齐就是HSSFCellStyle.ALIGN_RIGHT。如果是跨列举中就是HSSFCellStyle.ALIGN_CENTER_SELECTION。如果是两端对齐就是HSSFCellStyle.ALIGN_JUSTIFY。

3.2、设置垂直对齐方式

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

垂直对齐相关参数

如果是靠上就是HSSFCellStyle.VERTICAL_TOP。如果是居中就是HSSFCellStyle.VERTICAL_CENTER。如果是靠下就是HSSFCellStyle.VERTICAL_BOTTOM。如果是两端对齐就是HSSFCellStyle.VERTICAL_JUSTIFY。

3.3、设置边框

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、应用样式

//用在某一个单元格中

cell.setCellStyle(cellStyle);

//用在一行中，设置一行的样式

row.setRowStyle(cellStyle);

4、设置字体样式

字体样式是通过HSSFFont类来设置的，所以我们需要先得到HSSFFont类

HSSFFontfont=workbook.createFont()

设置样式

font.setFontName("华文行楷");//设置字体名称

font.setFontHeightInPoints((short)28);//设置字号

font.setColor(HSSFColor.RED.index);//设置字体颜色

font.setUnderline(FontFormatting.U_SINGLE);//设置下划线

font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标

font.setStrikeout(true);//设置删除线

下划线选项值：

单下划线FontFormatting.U_SINGLE双下划线FontFormatting.U_DOUBLE会计用单下划线FontFormatting.U_SINGLE_ACCOUNTING会计用双下划线FontFormatting.U_DOUBLE_ACCOUNTING无下划线FontFormatting.U_NONE

上标下标选项值：

上标FontFormatting.SS_SUPER下标FontFormatting.SS_SUB普通，默认值FontFormatting.SS_NONE

应用样式

//字体也是单元格格式的一部分，所以从属于HSSFCellStyle

//将字体对象赋值给单元格样式对象

style.setFont(font);

//将单元格样式应用于单元格

cell.setCellStyle(cellStyle);

数据导入

导入数据时，页面需要给出一个选择文件的标签，页面就不多说了，只要有一个上传的标签即可。

这是用layui写的一个简单的页面，代码如下：

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="导入"/

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){//导出成功后回调

/script

其中下载模板和导出数据基本一样，只需要创建一个Excel给出一条样式数据即可，代码如下：

@RequestMapping(value="exportmoban")

@ResponseBody

publicvoidexportmoban(HttpServletRequestrequest,HttpServletResponseresponse)throwsException{

//创建excel的文档对象

HSSFWorkbookworkbook=newHSSFWorkbook();

//创建员工信息工作薄，表单

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

//在sheet里创建第一行，参数为行索引(excel的行)，从0开始

HSSFRowrow1=sheet.createRow(0);

//创建单元格excel的单元格，参数为列索引，从0开始

HSSFCellcell=row1.createCell(0);

//设置单元格内容，标题第一行（可以不设置，根据项目需求）

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

//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列，一行标题合并单元格

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

//设置列名（每列的小标题）

String[]fieldnames={"姓名","部门","年龄","工号"};

//在sheet里创建第二行，第一行设置了标题

HSSFRowrow2=sheet.createRow(1);

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

row2.createCell(i).setCellValue(fieldnames[i]);//设置列名

//模板数据

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可以设置Excel文件的名称

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

response.setContentType("application/msexcel");

workbook.write(output);

output.close();

}

模板下载如图。

导入数据，我们只需要选择一个有数据的Excel表格（数据格式需要和模板中格式一样），点击导入即可。

后台对应导入数据的excelimport方法，代码如下：

@RequestMapping(value="/excelimport")

@ResponseBody

publicbooleanexcelimport(MultipartFilefile){

//实例化工具类

ImportExcelexcelReader=newImportExcel();

try{

InputStreamis=file.getInputStream();

//导入excel

excelReader.readExcelContent(is);

MapInteger,Stringmap=excelReader.readExcelContent(is);

//遍历数据保存

//因为第一行和第二行是标题，所以从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;

}

注：如果对数据需要做一些效验的话可以放在for循环中处理，这里只是写一个小案例，没有添加任何效验，根据项目需求自己添加。

其中ImportExcel类是一个处理导入的Excel数据的封装类。代码如下：

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表格表头的内容

*@paramis

*@returnString表头内容的数组

publicString[]readExcelTitle(InputStreamis){

try{

fs=newPOIFSFileSystem(is);

wb=newHSSFWorkbook(fs);

}catch(IOExceptione){

e.printStackTrace();

sheet=wb.getSheetAt(0);

//得到首行的row

row=sheet.getRow(0);

//标题总列数

intcolNum=row.getPhysicalNumberOfCells();

String[]title=newString[colNum];

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

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

returntitle;

*读取Excel数据内容

*@paramis

*@returnMap包含单元格数据内容的Map对象

publicMapInteger,StringreadExcelContent(InputStreamis){

MapInteger,Stringcontent=newHashMapInteger,String

Stringstr="";

try{

fs=newPOIFSFileSystem(is);

wb=newHSSFWorkbook(fs);

}catch(IOExceptione){

//e.printStackTrace();

sheet=wb.getSheetAt(0);

//得到总行数

introwNum=sheet.getLastRowNum();

//由于第0行和第一行已经合并了在这里索引从2开始

row=sheet.getRow(2);

intcolNum=row.getPhysicalNumberOfCells();

//正文内容应该从第二行开始,第一行为表头的标题

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;

*获取单元格数据内容为字符串类型的数据

*@paramcellExcel单元格

*@returnString单元格数据内容

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;

*获取单元格数据内容为日期类型的数据

*@paramcell

*Excel单元格

*@returnString单元格数据内容

privateStringgetDateCellValue(HSSFCellcell){

Stringresult="";

try{

intcellType=cell.getCellType();

if(cellType==HSSFCell.CELL_TYPE_NUMERIC){

Datedate=cell.getDateCellValue();

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