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

posted @ 2020-01-21 01:28  光头才能强  阅读(4635)  评论(0编辑  收藏  举报