Spring Boot 入门（十二）：报表导出，对比poi、jxl和esayExcel的效率
本片博客是紧接着Spring Boot 入门（十一）：集成 WebSocket, 实时显示系统日志写的
关于poi、jxl和esayExcel的介绍自行百度。
- jxl最多支持03版excel，所以单个sheet页面最多只能导出65536条数据。
- 我直接将excel导入到浏览器并打开，以下统计导出时长指将数据从数据库查询，并写入到excel的过程。不包括打开excel所消耗的时间
- 为了接近真实场景，我建了一个表，一共有32个字段，其中2个id：一个自增长、一个UUID，10个int型字段，10个String字段，10个datatime字段；导出的excel包含了32个字段
- 我每次导出一个excel后，直接将jvm的内存清空，再进行下一个excel的导出，保证导出excel不受其它线程的影响
- 我只是为了比较性能，所以没有对excel的样式进行过多的渲染
- poi方式，我使用的是刷新硬盘的方式，数据量大于设置的值，就将内存中的数据刷新到硬盘，降低OOM的概率，同时也增加了导出效率
1.pom依赖
以下是poi、jxl和esayExcel的全部依赖
1 <!--begin poi--> 2 <dependency> 3 <groupId>org.apache.poi</groupId> 4 <artifactId>poi</artifactId> 5 <version>${poi.version}</version> 6 </dependency> 7 8 <dependency> 9 <groupId>org.apache.poi</groupId> 10 <artifactId>poi-ooxml</artifactId> 11 <version>${poi.version}</version> 12 </dependency> 13 <!--end poi--> 14 <!--begin jxl--> 15 <dependency> 16 <groupId>net.sourceforge.jexcelapi</groupId> 17 <artifactId>jxl</artifactId> 18 <version>2.6.10</version> 19 </dependency> 20 <!--end jxl--> 21 <!--begin esayExcel--> 22 <dependency> 23 <groupId>com.alibaba</groupId> 24 <artifactId>easyexcel</artifactId> 25 <version>1.1.2-beat1</version> 26 </dependency> 27 <!--end esayExcel-->
2.页面
由于是直接将excel通过response相应的方式写入到内存，然后在浏览器端打开，所以页面部分不能用ajax请求
1 <form class="form-horizontal"> 2 <div class="form-group clearfix"> 3 <button type="button" onclick="report_poi();" class="btn btn-sm btn-warning">poi导出</button> 4 <button type="button" onclick="report_jxl();" class="btn btn-sm btn-danger">jxl导出</button> 5 <button type="button" onclick="report_esay_excel();" class="btn btn-sm btn-primary">esayExcel导出</button> 6 </div> 7 </form>
1 function report_poi() { 2 window.location.href = "/conf/report/reportPoi"; 3 } 4 5 function report_jxl() { 6 window.location.href = "/conf/report/reportJxl"; 7 } 8 9 function report_esay_excel() { 10 window.location.href = "/conf/report/reportEsayExcel"; 11 }
3.后台
在类中定义了一个常量，表示excel的表头
1 // 报表的title 2 private static final String[] title = {"id", "报表id" 3 , "col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8", "col9", "col10" 4 , "col11", "col12", "col13", "col14", "col15", "col16", "col17", "col18", "col19", "col20" 5 , "col21", "col22", "col23", "col24", "col25", "col26", "col27", "col28", "col29", "col30"};
（1）poi相关的后台代码
1 @Log("poi导出报表") 2 @RequestMapping(value = "/reportPoi", method = RequestMethod.GET) 3 @ResponseBody 4 public String reportPoi(HttpServletResponse response) throws Exception { 5 //excel文件名 6 log.info("poi方式开始导出数据"); 7 response.reset();// 清空输出流 8 response.setHeader("Content-Disposition", "attachment;filename=poi.xlsx"); 9 response.setContentType("application/octet-stream;charset=UTF-8"); 10 response.addHeader("Pargam", "no-cache"); 11 response.addHeader("Cache-Control", "no-cache"); 12 //sheet页中的行数,行数数据； 13 List<Report> list = reportService.getAllDate(); 14 long start = System.currentTimeMillis(); 15 // 开始导出excel 16 SXSSFWorkbook wb = new SXSSFWorkbook(1000); 17 SXSSFSheet sheet = wb.createSheet("poi"); 18 CellStyle style = wb.createCellStyle(); 19 style.setWrapText(true); 20 Row row = sheet.createRow(0); 21 Cell cell = null; 22 for (int i = 0; i < title.length; i++) { 23 cell = row.createCell(i); 24 cell.setCellValue(title[i]); 25 cell.setCellStyle(style); 26 } 27 for (int i = 0; i < list.size(); i++) { 28 Report report = list.get(i); 29 row = sheet.createRow(i + 1); 30 row.createCell(0).setCellValue(report.getId()); 31 row.createCell(1).setCellValue(report.getReportId()); 32 row.createCell(2).setCellValue(report.getCol1()); 33 row.createCell(3).setCellValue(report.getCol2()); 34 row.createCell(4).setCellValue(report.getCol3()); 35 row.createCell(5).setCellValue(report.getCol4()); 36 row.createCell(6).setCellValue(report.getCol5()); 37 row.createCell(7).setCellValue(report.getCol6()); 38 row.createCell(8).setCellValue(report.getCol7()); 39 row.createCell(9).setCellValue(report.getCol8()); 40 row.createCell(10).setCellValue(report.getCol9()); 41 row.createCell(11).setCellValue(report.getCol10()); 42 row.createCell(12).setCellValue(report.getCol11()); 43 row.createCell(13).setCellValue(report.getCol12()); 44 row.createCell(14).setCellValue(report.getCol13()); 45 row.createCell(15).setCellValue(report.getCol14()); 46 row.createCell(16).setCellValue(report.getCol15()); 47 row.createCell(17).setCellValue(report.getCol16()); 48 row.createCell(18).setCellValue(report.getCol17()); 49 row.createCell(19).setCellValue(report.getCol18()); 50 row.createCell(20).setCellValue(report.getCol19()); 51 row.createCell(21).setCellValue(report.getCol20()); 52 row.createCell(22).setCellValue(report.getCol21()); 53 row.createCell(23).setCellValue(report.getCol22()); 54 row.createCell(24).setCellValue(report.getCol23()); 55 row.createCell(25).setCellValue(report.getCol24()); 56 row.createCell(26).setCellValue(report.getCol25()); 57 row.createCell(27).setCellValue(report.getCol26()); 58 row.createCell(28).setCellValue(report.getCol27()); 59 row.createCell(29).setCellValue(report.getCol28()); 60 row.createCell(30).setCellValue(report.getCol29()); 61 row.createCell(31).setCellValue(report.getCol30()); 62 63 } 64 long millis = System.currentTimeMillis() - start; 65 OutputStream os = response.getOutputStream(); 66 wb.write(os); 67 os.flush(); 68 os.close(); 69 wb.dispose(); 70 log.info("POI导出报表，数据量：{},时间：{}ms", list.size(), millis); 71 return ""; 72 }
（2）jxl相关后台代码
1 @Log("jxl导出报表") 2 @RequestMapping(value = "/reportJxl") 3 @ResponseBody 4 public String reportJxl(HttpServletResponse response) throws Exception { 5 log.info("jxl方式开始导出数据"); 6 try { 7 long start = System.currentTimeMillis(); 8 OutputStream os = response.getOutputStream();// 取得输出流 9 response.reset();// 清空输出流 10 response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode("jxl", "UTF-8") + "Excel.xlsx");// 设定输出文件头 11 response.setContentType("application/msexcel");// 定义输出类型 12 WritableWorkbook workbook = jxl.Workbook.createWorkbook(os); // 建立excel文件 13 WritableSheet sheet1 = workbook.createSheet("jxl", 0);//第一个sheet名 14 // 通过函数WritableFont（）设置字体样式 15 // 第一个参数表示所选字体 16 // 第二个参数表示字体大小 17 // 第三个参数表示粗体样式，有BOLD和NORMAL两种样式 18 // 第四个参数表示是否斜体 19 // 第五个参数表示下划线样式 20 // 第六个参数表示颜色样式 21 WritableFont wf = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); 22 CellFormat cf = new WritableCellFormat(wf); 23 // 设置表头 24 for (int i = 0; i < title.length; i++) { 25 sheet1.addCell(new Label(i, 0, title[i], cf)); 26 } 27 List<Report> list = reportService.getAllDate(); 28 //根据内容自动设置列宽(内容为英文时) 29 // 生成主体内容 30 for (int i = 0; i < list.size(); i++) { 31 Report report = list.get(i); 32 sheet1.addCell(new Label(0, i + 1, report.getId().toString())); 33 sheet1.addCell(new Label(1, i + 1, report.getReportId())); 34 sheet1.addCell(new Label(2, i + 1, report.getCol1().toString())); 35 sheet1.addCell(new Label(3, i + 1, report.getCol2().toString())); 36 sheet1.addCell(new Label(4, i + 1, report.getCol3().toString())); 37 sheet1.addCell(new Label(5, i + 1, report.getCol4().toString())); 38 sheet1.addCell(new Label(6, i + 1, report.getCol5().toString())); 39 sheet1.addCell(new Label(7, i + 1, report.getCol6().toString())); 40 sheet1.addCell(new Label(8, i + 1, report.getCol7().toString())); 41 sheet1.addCell(new Label(9, i + 1, report.getCol8().toString())); 42 sheet1.addCell(new Label(10, i + 1, report.getCol9().toString())); 43 sheet1.addCell(new Label(11, i + 1, report.getCol10().toString())); 44 sheet1.addCell(new Label(12, i + 1, report.getCol11())); 45 sheet1.addCell(new Label(13, i + 1, report.getCol12())); 46 sheet1.addCell(new Label(14, i + 1, report.getCol13())); 47 sheet1.addCell(new Label(15, i + 1, report.getCol14())); 48 sheet1.addCell(new Label(16, i + 1, report.getCol15())); 49 sheet1.addCell(new Label(17, i + 1, report.getCol16())); 50 sheet1.addCell(new Label(18, i + 1, report.getCol17())); 51 sheet1.addCell(new Label(19, i + 1, report.getCol18())); 52 sheet1.addCell(new Label(20, i + 1, report.getCol19())); 53 sheet1.addCell(new Label(21, i + 1, report.getCol20())); 54 sheet1.addCell(new Label(22, i + 1, report.getCol21().toString())); 55 sheet1.addCell(new Label(23, i + 1, report.getCol22().toString())); 56 sheet1.addCell(new Label(24, i + 1, report.getCol23().toString())); 57 sheet1.addCell(new Label(25, i + 1, report.getCol24().toString())); 58 sheet1.addCell(new Label(26, i + 1, report.getCol25().toString())); 59 sheet1.addCell(new Label(27, i + 1, report.getCol26().toString())); 60 sheet1.addCell(new Label(28, i + 1, report.getCol27().toString())); 61 sheet1.addCell(new Label(29, i + 1, report.getCol28().toString())); 62 sheet1.addCell(new Label(30, i + 1, report.getCol29().toString())); 63 sheet1.addCell(new Label(31, i + 1, report.getCol30().toString())); 64 } 65 workbook.write(); // 写入文件 66 workbook.close(); 67 os.close(); // 关闭流 68 long millis = System.currentTimeMillis() - start; 69 log.info("jxl导出报表，数据量：{},时间：{}ms", list.size(), millis); 70 } catch (Exception e) { 71 log.error("jxl导出报表报错", e); 72 } 73 return ""; 74 }
（3）esayExcel相关后台代码
1 @Log("esayExcel导出报表") 2 @RequestMapping(value = "/reportEsayExcel") 3 @ResponseBody 4 public String reportEsayExcel(HttpServletResponse response) throws Exception { 5 log.info("esayExcel方式开始导出数据"); 6 long start = System.currentTimeMillis(); 7 8 try { 9 ExcelWriter writer = null; 10 OutputStream outputStream = response.getOutputStream(); 11 //添加响应头信息 12 response.setHeader("Content-disposition", "attachment; filename= esayExcel.xlsx"); 13 response.setContentType("application/msexcel;charset=UTF-8");//设置类型 14 response.setHeader("Pragma", "No-cache");//设置头 15 response.setHeader("Cache-Control", "no-cache");//设置头 16 response.setDateHeader("Expires", 0);//设置日期头 17 18 //实例化 ExcelWriter 19 writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true); 20 21 //实例化表单 22 Sheet sheet = new Sheet(1, 0, Report.class); 23 sheet.setSheetName("esayExcel"); 24 25 //获取数据 26 List<Report> list = reportService.getAllDate(); 27 28 //输出 29 writer.write(list, sheet); 30 writer.finish(); 31 outputStream.flush(); 32 long millis = System.currentTimeMillis() - start; 33 log.info("sayExcel导出报表，数据量：{},时间：{}ms", list.size(), millis); 34 } catch (IOException e) { 35 log.error("esayExcel导出excel报错", e); 36 } finally { 37 try { 38 response.getOutputStream().close(); 39 } catch (IOException e) { 40 log.error("esayExcel关闭资源", e); 41 } 42 } 43 return ""; 44 }
1 package com.learn.hello.system.common.listener; 2 3 import com.alibaba.excel.context.AnalysisContext; 4 import com.alibaba.excel.event.AnalysisEventListener; 5 import lombok.extern.slf4j.Slf4j; 6 import org.apache.poi.ss.formula.functions.T; 7 8 import java.util.ArrayList; 9 import java.util.List; 10 11 /** 12 * @ClassName ExcelListener 13 * @Deccription 通过esayExcel的方式导出excel 14 * @Author DZ 15 * @Date 2020/1/20 22:28 16 **/ 17 @Slf4j 18 public class ExcelListener extends AnalysisEventListener<T> { 19 //可以通过实例获取该值 20 private final List<T> rows = new ArrayList<>(); 21 22 @Override 23 public void invoke(T object, AnalysisContext analysisContext) { 24 //数据存储到list，供批量处理，或后续自己业务逻辑处理。 25 rows.add(object); 26 } 27 28 @Override 29 public void doAfterAllAnalysed(AnalysisContext analysisContext) { 30 } 31 32 public List<T> getRows() { 33 return rows; 34 } 35 }
ExcelListener 这个类中还可以做很多工作，比喻在doAfterAllAnalysed中做一些销毁工作，日志记录等。在invoke中做一些业务相关的工作，或者对rows进行遍历处理
实体类:
1 package com.learn.hello.modules.entity; 2 3 import com.alibaba.excel.annotation.ExcelProperty; 4 import com.alibaba.excel.metadata.BaseRowModel; 5 import lombok.Data; 6 7 import javax.persistence.*; 8 import java.util.Date; 9 10 @Data 11 @Table(name = "t_report") 12 public class Report extends BaseRowModel { 13 @ExcelProperty(value = "id", index = 0) 14 @Id 15 @GeneratedValue(strategy = GenerationType.IDENTITY) 16 private Integer id; 17 18 /** 19 * 报表id 20 */ 21 @ExcelProperty(value = "报表id", index = 1) 22 @Column(name = "report_id") 23 private String reportId; 24 25 @ExcelProperty(value = "col1", index = 2) 26 private Integer col1; 27 28 @ExcelProperty(value = "col2", index = 3) 29 private Integer col2; 30 31 @ExcelProperty(value = "col3", index = 4) 32 private Integer col3; 33 34 @ExcelProperty(value = "col4", index = 5) 35 private Integer col4; 36 37 @ExcelProperty(value = "col5", index = 6) 38 private Integer col5; 39 40 @ExcelProperty(value = "col6", index = 7) 41 private Integer col6; 42 43 @ExcelProperty(value = "col7", index = 8) 44 private Integer col7; 45 46 @ExcelProperty(value = "col8", index = 9) 47 private Integer col8; 48 49 @ExcelProperty(value = "col9", index = 10) 50 private Integer col9; 51 52 @ExcelProperty(value = "col10", index = 11) 53 private Integer col10; 54 55 @ExcelProperty(value = "col11", index = 12) 56 private String col11; 57 58 @ExcelProperty(value = "col12", index = 13) 59 private String col12; 60 61 @ExcelProperty(value = "col13", index = 14) 62 private String col13; 63 64 @ExcelProperty(value = "col14", index = 15) 65 private String col14; 66 67 @ExcelProperty(value = "col15", index = 16) 68 private String col15; 69 70 @ExcelProperty(value = "col16", index = 17) 71 private String col16; 72 73 @ExcelProperty(value = "col17", index = 18) 74 private String col17; 75 76 @ExcelProperty(value = "col18", index = 19) 77 private String col18; 78 79 @ExcelProperty(value = "col19", index = 20) 80 private String col19; 81 82 @ExcelProperty(value = "col20", index = 21) 83 private String col20; 84 85 @ExcelProperty(value = "col21", index = 22) 86 private Date col21; 87 88 @ExcelProperty(value = "col22", index = 23) 89 private Date col22; 90 91 @ExcelProperty(value = "col23", index = 24) 92 private Date col23; 93 94 @ExcelProperty(value = "col24", index = 25) 95 private Date col24; 96 97 @ExcelProperty(value = "col25", index = 26) 98 private Date col25; 99 100 @ExcelProperty(value = "col26", index = 27) 101 private Date col26; 102 103 @ExcelProperty(value = "col27", index = 28) 104 private Date col27; 105 106 @ExcelProperty(value = "col28", index = 29) 107 private Date col28; 108 109 @ExcelProperty(value = "col29", index = 30) 110 private Date col29; 111 112 @ExcelProperty(value = "col30", index = 31) 113 private Date col30; 114 115 }
其中@ExcelProperty(value = "col30", index = 14)注解是给esayExcel'使用的，poi和jxl使用这个实体的时候，这行注解可以忽略
4.性能比较
以下是打印的日志：由于jxl最多只能导出65536条数据，所以在70W条数据导出的时候，就没有jxl的相关耗时。此外，在导出第80W条以及以后的数据的时候，我将jvm内存清空了，让jvm以最佳的状态导出，所以60W到80W的时候，耗时并没有增加多少
**************************************************idea打印出的日志************************************************
POI导出报表，数据量：10001,时间：752ms
jxl导出报表，数据量：10001,时间：993ms
sayExcel导出报表，数据量：10001,时间：2189ms
POI导出报表，数据量：20001,时间：1527ms
jxl导出报表，数据量：20001,时间：2447ms
sayExcel导出报表，数据量：20001,时间：3481ms
POI导出报表，数据量：30001,时间：1538ms
jxl导出报表，数据量：30001,时间：2520ms
sayExcel导出报表，数据量：30001,时间：5102ms
POI导出报表，数据量：40001,时间：1892ms
jxl导出报表，数据量：40001,时间：3549ms
sayExcel导出报表，数据量：40001,时间：7523ms
POI导出报表，数据量：50001,时间：2395ms
jxl导出报表，数据量：50001,时间：4714ms
sayExcel导出报表，数据量：50001,时间：8319ms
POI导出报表，数据量：60001,时间：2860ms
jxl导出报表，数据量：60001,时间：5255ms
sayExcel导出报表，数据量：60001,时间：10197ms
POI导出报表，数据量：70001,时间：3693ms
sayExcel导出报表，数据量：70001,时间：11595ms
POI导出报表，数据量：80001,时间：3843ms
sayExcel导出报表，数据量：80001,时间：13928ms
POI导出报表，数据量：90001,时间：4319ms
sayExcel导出报表，数据量：90001,时间：14901ms
POI导出报表，数据量：100001,时间：4943ms
sayExcel导出报表，数据量：100001,时间：15962ms
POI导出报表，数据量：200011,时间：11296ms
sayExcel导出报表，数据量：200011,时间：33037ms
POI导出报表，数据量：300011,时间：14947ms
sayExcel导出报表，数据量：300011,时间：49748ms
POI导出报表，数据量：400011,时间：19626ms
sayExcel导出报表，数据量：400011,时间：66043ms
POI导出报表，数据量：600011,时间：34418ms
sayExcel导出报表，数据量：600011,时间：101819ms
POI导出报表，数据量：800011,时间：38726ms
sayExcel导出报表，数据量：800011,时间：135209ms
POI导出报表，数据量：1000011,时间：47433ms
sayExcel导出报表，数据量：1000011,时间：167676ms
**************************************************idea打印出的日志************************************************
对上面的数据量取整，统计图如下：
第一行为数据量，从3W到100W
第二到四行为导出excel消耗的时间，单位为毫秒
其中纵坐标为导出时间，横轴为导出数量。
结论：
- 从时间上：poi>jxl>esayExcel
- 从代码简洁程度上：esayExce>jxl>poi
- 从jvm内存消耗上，我监控的是最高峰的内存消耗量：3中方式都差不多（网上说esayExcel消耗内存很小，我真的没看出来）
- jxl可以直接设置excel模板，所以对于复杂表头的excel，jxl处理起来很方便（具体可以自行搜索jxl 模板 导出）
- esayExcel目前没有提供较复杂的api，无法导出较复杂的数据（二进制图片，音乐等）
如果对于表头简单，且数据量小于10W条数据的，推荐使用esayExcel该方式代码很简洁，10W以下的导出效率还行
如果小于60W条数据，表头复杂建议使用jxl；表头简单，建立使用poi
如果大于60W条数据，选择poi
poi方式处理代码繁琐点，性能很好，不知道如何选择，就直接使用poi，不会出错
完整的项目和代码见：https://gitee.com/bald_dz/SpringbootLean
