百万级Excel导出方案

布满荆棘的人生 2022-10-28 04:55 275阅读 0赞

**百万数据量excel导出方案**

*  **编写目的**

在实际场景中excel报表导出业务比较常见，随着业务量及数据量递增，报表导出数据量大的情况下容易出现内存溢出，响应耗时太长等问题，现提供百万数据量内的报表导出方案，供大家参考。

*  **适用范围**

Java开发工程师

*  **方案说明**

*  **问题说明**

1.  大批量数据导出容易瞬间打满老年代导致Full GC频繁发生，容易系统卡死。
2.  一次性把目标数据全部查询出来再写到流中的方式，大量被查询的对象驻留在堆内存中，直接打满整个堆，容易堆内存溢出。

![20210205154042248.png][]

*  **核心思路**

1.  底层使用POI Excel SXSSF，Excel  2007一个页签只能放1048576行数据、16384列。这里用alibba开源EsayExcel。
2.  数据批量分页查出，输出到流。如果有自增主键，可采用滚动分页方式，查询效率优化比较明显。

SELECT \* FROM tableX WHERE id > \#\{lastBatchMaxId\} \[其他条件\] ORDER BY id \[ASC|DESC\](这里一般选用ASC排序) LIMIT $\{size\}

*  **参考代码**

Pom引入easyexcel

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.4</version>
    </dependency>

参考代码@

@GetMapping(path = "/exportId")
    public void exportId(@RequestParam("pageSize") int pageSize, @RequestParam("pageNum") int pageNum, HttpServletResponse response) throws Exception {
    
        long startTime = System.currentTimeMillis();
        ExcelWriter writer = null;
        try {
            String fileName = URLEncoder.encode(String.format("%s-(%s).csv", "订单支付数据", UUID.randomUUID().toString()),
                    StandardCharsets.UTF_8.toString());
            response.setContentType("application/force-download");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            writer = new ExcelWriterBuilder()
                    .autoCloseStream(true)
                    .excelType(ExcelTypeEnum.XLSX)
                    .file(response.getOutputStream())
                    .head(OrderRecordExportDO.class)
                    //注册自定义时间格式转换，easyExceL目前支持Date，不支持LocalDateTime,需要自定义时间转换器
                    .registerConverter(new LocalDateTimeConverter())
                    .build();
            // xlsx文件上上限是104W行左右,这里如果超过104W需要分Sheet
            WriteSheet writeSheet = new WriteSheet();
            writeSheet.setSheetName("target");
            long lastBatchMaxId = 0L;
    
            //2、查询原数据表交易
            for (int i = 1; i <= pageNum; i++) {
                long startSelect=System.currentTimeMillis();
                log.debug("i=[{}]", i);
    
                List<OrderRecordExportDO> orders = orderRecordMapper.exportLimitId(lastBatchMaxId,pageSize);
                log.info("[源数据交易数][{}][{}],耗时={}", i, orders.size(),System.currentTimeMillis()-startSelect);
                lastBatchMaxId = orders.stream().map(OrderRecordExportDO::getId).max(Long::compareTo).orElse(Long.MAX_VALUE);

G**测试情况**

总数=800000,pageSize=10000,pageNum=80,耗时=48046ms   10列 130M

![2021020515432213.png][]

表格样式，模板文件导出可以查看esayExcel官网。

https://www.yuque.com/easyexcel/doc/easyexcel

参考地址

https://www.cnblogs.com/throwable/p/13285518.html

[20210205154042248.png]: /images/20221024/2d933af658ad4493912ca9bba0bbf383.png
[2021020515432213.png]: /images/20221024/912e50eafbed489ca6c6bb42165a4304.png