sprmvc与poi结合导出Excel

比眉伴天荒 2022-01-31 05:29 188阅读 0赞

1.  思路

1.  通过response获取OutPutStream
2.  **通过POI创建H****SSF****Wo****rkBook****，根据查询出数据生成表格**
3.  通过workBook.write导出

1.  response设置返回类型、文件名

![20190515191947390.png][]

1.  通过poi创建HSSFWorkBook

<table style="margin-left:18pt;width:396.8pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:396.8pt;"> /** &nbsp;* 生成客户列表 &nbsp;*/ private&nbsp;HSSFWorkbook getWorkbook(List&lt;Customer&gt; customerList) throws&nbsp;IOException { //1.创建表格 HSSFWorkbook workBook&nbsp;= new&nbsp;HSSFWorkbook(); //2.创建工作簿 HSSFSheet sheet&nbsp;= workBook.createSheet("工作簿01"); &nbsp; //样式 HSSFCellStyle cellStyle&nbsp;= workBook.createCellStyle(); HSSFFont font&nbsp;= workBook.createFont(); font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short)16); cellStyle.setFont(font); &nbsp; HSSFRow row&nbsp;= sheet.createRow(0); &nbsp; String[] heads&nbsp;= { "客户编号","客户名称","客户来源","客户类型","业务员","发布人"}; //创建表头 for(int&nbsp;i=0;i&lt;heads.length;i++){ &nbsp; HSSFCell cell&nbsp;= row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(heads[i]); } //设置表格内容 for(int&nbsp;i=0;i&lt;customerList.size();i++){ HSSFRow rowContent&nbsp;= sheet.createRow(i+1); HSSFCell cell0&nbsp;= rowContent.createCell(0); cell0.setCellValue(customerList.get(i).getCusnum()); &nbsp; HSSFCell cell1&nbsp;= rowContent.createCell(1); cell1.setCellValue(customerList.get(i).getCusname()); &nbsp; &nbsp; HSSFCell cell2&nbsp;= rowContent.createCell(2); cell2.setCellValue(customerList.get(i).getOriginate()); &nbsp; HSSFCell cell3&nbsp;= rowContent.createCell(3); cell3.setCellValue(customerList.get(i).getType()); &nbsp; HSSFCell cell4&nbsp;= rowContent.createCell(4); cell4.setCellValue(customerList.get(i).getBusinessname()); &nbsp; HSSFCell cell5&nbsp;= rowContent.createCell(5); cell5.setCellValue(customerList.get(i).getIssuername()); } &nbsp; //6.写入磁盘 workBook.write(new&nbsp;File("D:/helloWorld03.xls")); return&nbsp;workBook; } </td> 
 </tr> 
 </tbody> 
</table>

1.  workBook.write导出

![20190515191958940.png][]

1.  完整代码

<table style="margin-left:18pt;width:396.8pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:396.8pt;"> @RequestMapping("/export/excel") public&nbsp;String contactsList( @RequestParam(defaultValue="1") Integer currentPage, @RequestParam(defaultValue="15") Integer pageSize, String cusnum, HttpServletResponse response) throws&nbsp;IOException{ String dateStr&nbsp;= DateUtil.getDateStr(new&nbsp;Date(),"yyyyMMddHHmmss"); String fileName&nbsp;= URLEncoder.encode("客户信息列表_"+dateStr+".xls", "UTF-8"); //1.设置返回的数据类型 response.setContentType("application/vnd.ms-excel"); //2.设置返回的文件名 response.setHeader("Content-Disposition","attachment;filename*=utf-8'zh_cn'"+fileName); &nbsp; //3.获取输出流 OutputStream outputStream&nbsp;= response.getOutputStream(); &nbsp; //4.查询数据 //根据条件 查询客户分页信息 PageInfo&lt;Customer&gt; pageInfo&nbsp;= customerService.findPageInfo(currentPage,pageSize,cusnum); //4.通过POI创建excel HSSFWorkbook workBook&nbsp;= getWorkbook(pageInfo.getList()); //写出 workBook.write(outputStream); //关闭 workBook.close(); return&nbsp;null; } &nbsp; /** &nbsp;* 生成客户列表 &nbsp;*/ private&nbsp;HSSFWorkbook getWorkbook(List&lt;Customer&gt; customerList) throws&nbsp;IOException { //1.创建表格 HSSFWorkbook workBook&nbsp;= new&nbsp;HSSFWorkbook(); //2.创建工作簿 HSSFSheet sheet&nbsp;= workBook.createSheet("工作簿01"); &nbsp; //样式 HSSFCellStyle cellStyle&nbsp;= workBook.createCellStyle(); HSSFFont font&nbsp;= workBook.createFont(); font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short)16); cellStyle.setFont(font); &nbsp; HSSFRow row&nbsp;= sheet.createRow(0); &nbsp; String[] heads&nbsp;= { "客户编号","客户名称","客户来源","客户类型","业务员","发布人"}; //创建表头 for(int&nbsp;i=0;i&lt;heads.length;i++){ &nbsp; HSSFCell cell&nbsp;= row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(heads[i]); } //设置表格内容 for(int&nbsp;i=0;i&lt;customerList.size();i++){ HSSFRow rowContent&nbsp;= sheet.createRow(i+1); HSSFCell cell0&nbsp;= rowContent.createCell(0); cell0.setCellValue(customerList.get(i).getCusnum()); &nbsp; HSSFCell cell1&nbsp;= rowContent.createCell(1); cell1.setCellValue(customerList.get(i).getCusname()); &nbsp; &nbsp; HSSFCell cell2&nbsp;= rowContent.createCell(2); cell2.setCellValue(customerList.get(i).getOriginate()); &nbsp; HSSFCell cell3&nbsp;= rowContent.createCell(3); cell3.setCellValue(customerList.get(i).getType()); &nbsp; HSSFCell cell4&nbsp;= rowContent.createCell(4); cell4.setCellValue(customerList.get(i).getBusinessname()); &nbsp; HSSFCell cell5&nbsp;= rowContent.createCell(5); cell5.setCellValue(customerList.get(i).getIssuername()); } &nbsp; //6.写入磁盘 workBook.write(new&nbsp;File("D:/helloWorld03.xls")); return&nbsp;workBook; } </td> 
 </tr> 
 </tbody> 
</table>

[20190515191947390.png]: /images/20220131/113712f864464393b578b9d7808eece4.png
[20190515191958940.png]: /images/20220131/db349668160740d6b8e37ae7fab144d4.png