百万级Excel导出方案

百万数据量excel导出方案

 

• 编写目的

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

• 适用范围

    Java开发工程师

• 方案说明

• 问题说明

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

 

• 核心思路

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

 

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

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

 

参考地址

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