Springboot项目中使用POI操作Excel（详细教程系列2/3）

1、基于模板导出列表数据

1.1、需求

按照以下样式导出excel：

1.2、思路

首先准备一个excel模板，这个模板把复杂的样式和固定的内容先准备好并且放入到项目中，然后读取到模板后向里面放入数据。

1.3、实现

1. 准备一个excel作为导出的模板，模板内容如下

   第一个sheet：
   

   第二个sheet：

2. 把这个模板改一个英文名称比如：userList.xlsx,放入到项目中
   
3. 修改UserController中的方法

@GetMapping(value="/downLoadXlsxByPoiWithTemplate",name="使用POI下载高版本-带模板文件")publicvoiddownLoadXlsxByPoiWithTemplate(HttpServletRequestrequest,HttpServletResponseresponse)throwsException{//带单元格样式导出userService.downLoadXlsxByPoiWithTemplate(request,response);}

4. 修改userService

voiddownLoadXlsxByPoiWithTemplate(HttpServletRequestrequest,HttpServletResponseresponse)throwsException;

5. 修改实现类

@OverridepublicvoiddownLoadXlsxByPoiWithTemplate(HttpServletRequestrequest,HttpServletResponseresponse)throwsException{//1.获取模板// 获取模板的路径FilerootPath=newFile(ResourceUtils.getURL("classpath:").getPath());//SpringBoot项目获取根目录的方式FiletemplatePath=newFile(rootPath.getAbsolutePath(),"/excel_template/userList.xlsx");// 读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄Workbookworkbook=newXSSFWorkbook(templatePath);// 读取工作薄的第一个工作表，向工作表中放数据Sheetsheet=workbook.getSheetAt(0);// 获取第二个的sheet中那个单元格中的单元格样式CellStylecellStyle=workbook.getSheetAt(1).getRow(0).getCell(0).getCellStyle();//2.查询所有用户数据// 处理内容List<User>userList=userMapper.selectList(null);//3.放入到模板中introwIndex=2;Rowrow=null;Cellcell=null;SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-dd");for(Useruser:userList){row=sheet.createRow(rowIndex);row.setHeightInPoints(15);//设置行高cell=row.createCell(0);cell.setCellValue(user.getId());cell.setCellStyle(cellStyle);//设置单元格样式cell=row.createCell(1);cell.setCellValue(user.getUserName());cell.setCellStyle(cellStyle);cell=row.createCell(2);cell.setCellValue(user.getPhone());cell.setCellStyle(cellStyle);cell=row.createCell(3);cell.setCellValue(sdf.format(user.getHireDate()));cell.setCellStyle(cellStyle);cell=row.createCell(4);cell.setCellValue(user.getAddress());cell.setCellStyle(cellStyle);rowIndex++;}//把第二个sheet删除workbook.removeSheetAt(1);workbook.setSheetName(0,"用户列表");//4.导出文件// 导出的文件名称Stringfilename="用户列表数据.xlsx";// 设置文件的打开方式和mime类型ServletOutputStreamoutputStream=response.getOutputStream();response.setHeader("Content-Disposition","attachment;filename="+newString(filename.getBytes(),"ISO8859-1"));response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");workbook.write(outputStream);}

6. 导出结果验证：

2、导出用户详细数据

2.1、 需求

如下，点击用户列表中的下载按钮，下载文件内容如下：

2.2、思路

最简单的方式就是先根据案例制作模板，导出时查询用户数据、读取模板，把数据放入到模板中对应的单元格中，其中我们先处理最基本的数据，稍后再处理图片

1. 制作一个excel导出模板，如下：
   

2. 制作好的模板放入到项目中
   

3. Controller中添加方法

@GetMapping(value="/downLoadUserInfoWithTempalte",name="导出用户详细信息")publicvoiddownLoadUserInfoWithTempalte(Longid,HttpServletRequestrequest,HttpServletResponseresponse)throwsException{userService.downLoadUserInfoWithTempalte(id,request,response);}

4. 在UserService中添加方法

voiddownLoadUserInfoWithTempalte(Longid,HttpServletRequestrequest,HttpServletResponseresponse)throwsException;

5. 实现类的修改

@OverridepublicvoiddownLoadUserInfoWithTempalte(Longid,HttpServletRequestrequest,HttpServletResponseresponse)throwsException{// 获取模板的路径FilerootPath=newFile(ResourceUtils.getURL("classpath:").getPath());//SpringBoot项目获取根目录的方式FiletemplatePath=newFile(rootPath.getAbsolutePath(),"/excel_template/userInfo.xlsx");// 读取模板文件产生workbook对象,这个workbook是一个有内容的工作薄Workbookworkbook=newXSSFWorkbook(templatePath);// 读取工作薄的第一个工作表，向工作表中放数据Sheetsheet=workbook.getSheetAt(0);// 处理内容Useruser=userMapper.selectById(id);// 接下来向模板中单元格中放数据// 用户名 第2行第2列sheet.getRow(1).getCell(1).setCellValue(user.getUserName());// 手机号 第3行第2列sheet.getRow(2).getCell(1).setCellValue(user.getPhone());SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-dd");// 生日 第4行第2列 日期转成字符串sheet.getRow(3).getCell(1).setCellValue(sdf.format(user.getBirthday()));// 工资 第5行第2列sheet.getRow(4).getCell(1).setCellValue(user.getSalary());// 工资 第6行第2列sheet.getRow(5).getCell(1).setCellValue(sdf.format(user.getHireDate()));// 省份 第7行第2列sheet.getRow(6).getCell(1).setCellValue(user.getProvince());// 现住址 第8行第2列sheet.getRow(7).getCell(1).setCellValue(user.getAddress());// 司龄 第6行第4列暂时先不考虑// 城市 第7行第4列sheet.getRow(6).getCell(3).setCellValue(user.getCity());// 导出的文件名称Stringfilename=user.getUserName()+"详细信息数据.xlsx";// 设置文件的打开方式和mime类型ServletOutputStreamoutputStream=response.getOutputStream();response.setHeader("Content-Disposition","attachment;filename="+newString(filename.getBytes(),"ISO8859-1"));response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");workbook.write(outputStream);}

6. 验证结果输出：
   点击页面上的下载按钮，效果如下：
   
   接下来处理一下头像照片和司龄…

3、导出数据带图片、公式

3.1、导出图片

个人信息的导出中包含了头像照片，需要用到POI的导出图片功能，那么POI主要提供了两个类来处理照片，这两个类是Patriarch和ClientAnchor前者负责在表中创建图片，后者负责设置图片的大小位置。

在UserServiceImpl实现类的方法中添加以下代码:

//照片的位置//开始处理图片// 先创建一个字节输出流ByteArrayOutputStreambyteArrayOut=newByteArrayOutputStream();// BufferedImage是一个带缓冲区图像类,主要作用是将一幅图片加载到内存中BufferedImagebufferImg=ImageIO.read(newFile(rootPath+user.getPhoto()));// 把读取到图像放入到输出流中// user.getPhoto()StringextName=user.getPhoto().substring(user.getPhoto().lastIndexOf(".")+1).toUpperCase();ImageIO.write(bufferImg,extName,byteArrayOut);//Patriarch控制图片的写入和ClientAnchor指定图片的位置// 创建一个绘图控制类，负责画图Drawingpatriarch=sheet.createDrawingPatriarch();// 指定把图片放到哪个位置 指定图片的位置 开始列3 开始行2 结束列4 结束行5 偏移的单位:是一个英式公制的单位1厘米=360000ClientAnchoranchor=newXSSFClientAnchor(100000,100000,-100000,-100000,2,1,4,5);// 开始把图片写入到sheet指定的位置intformat=0;switch(extName){case"JPG":{format=XSSFWorkbook.PICTURE_TYPE_JPEG;}case"JPEG":{format=XSSFWorkbook.PICTURE_TYPE_JPEG;}case"PNG":{format=XSSFWorkbook.PICTURE_TYPE_PNG;}}patriarch.createPicture(anchor,workbook.addPicture(byteArrayOut.toByteArray(),format));//结束处理图片

关于XSSFClientAnchor的8个参数说明：

dx1 - the x coordinate within the first cell.//定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0
dy1 - the y coordinate within the first cell.//定义了图片在第一个cell的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0
dx2 - the x coordinate within the second cell.//定义了图片在第二个cell的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0
dy2 - the y coordinate within the second cell.//定义了图片在第二个cell的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0

col1 - the column (0 based) of the first cell.//第一个cell所在列，既图片左上角所在列
row1 - the row (0 based) of the first cell.//图片左上角所在行 col2 - the
column (0 based) of the second cell.//图片右下角所在列 row2 - the row (0
based) of the second cell.//图片右下角所在行

图片输出结果：

3.2、导出公式

应用场景说明，在导出用户详细数据时有一个司龄的显示，这里的司龄就是截止到现在入职到本公司的时间，为了学习POI对公式的操作，我们这里使用POI的公式来做。

计算截止到现在入职到本公司的时间应该用到两个日期相差的函数:DATEDIF函数，这个函数需要3个参数

P1: 一个日期 P2:截止日期 P3: 时间单位 举例：

1. DATEDIF(“2015-10-01”,“2020-10-01”,“y”) 结果是5
2. CONCATENATE(DATEDIF(“2015-10-01”,“2020-10-01”,“y”)),“年”,DATEDIF(“2015-10-01”,“2020-10-01”,“ym”),“个月”) 结果是5年0个月

放到这个用户导出时，第一个参数就是放到相应单元格上数据，第二个参数就是当天时间，

如果直接在excel中操作，如下：

在使用POI导出时使用setCellFormula方法来设置公式：

关于POI支持公式详见官网： https://poi.apache.org/components/spreadsheet/eval-devguide.html

其实在正常开发时应该在模板中直接设置好公式，这样打开直接导出的excel文档时公式会直接运行出我们想要的结果。

“人的一生会经历很多痛苦，但回头想想，都是传奇”。