欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【EasyExcel】封装一个分页写数据的通用方法,有效规避OOM

程序员文章站 2024-03-21 13:01:58
...

通过SQL查询出来一次性写,在大数据量时存在OOM的隐患
分页查询、分批次写数据,避免导出大数据量时内存消耗陡增
基于mybatis-puls分页查询

example

导出250万用户数据,代码看起来是不是很清爽呢?

@Test
public void pageWrite() {
   // build excel writer
   ExcelWriter excelWriter = EasyExcel.write("E:/easyexcel/testPageWrite.xlsx", PageWriteData.class).excelType(ExcelTypeEnum.XLSX).build();
   // page write
   EasyExcelUtils.pageWrite(excelWriter, "数据清单", 2_500_000L,
           (currentPage, pageSize) -> {
       			Page<User> page = new Page<>(currentPage, pageSize);
              	return userService.selectByPage(page).getRecords();
           });
}

源码实现

EasyExcelUtils.java

package com.itplh.common.util;

import com.itplh.base.service.MybatisPlusPageQueryService;
import com.itplh.common.constant.ExcelPageEnum;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;

/**
 * @description:
 * @author: tanpeng
 * @date: 2020-02-28 10:46
 * @version: v1.0.0
 */
public class EasyExcelUtils extends EasyExcel {

    /**
     * @description: 分页查询、分批次写数据,避免导出大数据量时OOM
     * auto close resource
     * @author: tanpeng
     * @date : 2020-02-28 11:42
     * @version: v1.0.0
     * @param excelWriter
     * @param sheetName
     * @param totalCount 数据总数
     * @param pageQueryService
     */
    public static void pageWrite(ExcelWriter excelWriter,
                                  String sheetName,
                                  long totalCount,
                                  MybatisPlusPageQueryService pageQueryService) {

        // default export xlsx, page size 10000, sheet max row 1000000
        int pageSize = ExcelPageEnum.XLSX.getPageSize();
        int sheetMaxRow = ExcelPageEnum.XLSX.getSheetMaxRow();

        ExcelTypeEnum excelType = excelWriter.writeContext().writeWorkbookHolder().getExcelType();
        boolean isXls =  excelType != null && ExcelTypeEnum.XLS.getValue().equals(excelType.getValue());
        if (isXls) {
            pageSize = ExcelPageEnum.XLS.getPageSize();
            sheetMaxRow = ExcelPageEnum.XLS.getSheetMaxRow();
        }

        // compute page count, sheet count
        long pageCount = (totalCount - 1) / pageSize + 1;
        long sheetCount = (totalCount - 1) / sheetMaxRow + 1;
        int currentPage = 0;

        // page write data
        WriteSheet sheet = null;
        for (int i = 0; i < sheetCount; i++) {
            sheet = EasyExcel.writerSheet(i, sheetName + i).build();
            for (int j = 0; j < (sheetMaxRow / pageSize); j++) {
                // must use ++currentPage, mybatis-plus page query current page start 1
                excelWriter.write(pageQueryService.data(++currentPage, pageSize), sheet);
                if (currentPage >= pageCount) {
                    break;
                }
            }
        }

        // close source
        excelWriter.finish();
    }

}

MybatisPlusPageQueryService.java

package com.itplh.base.service;

import java.util.List;

/**
 * @description:
 * @author: tanpeng
 * @date: 2020-02-28 11:20
 * @version: v1.0.0
 */
@FunctionalInterface
public interface MybatisPlusPageQueryService<E> {

    /**
     * @description: 获取分页查询数据
     * 注意:mybatis-plus 的 current 参数是从 1 开始
     * @author: tanpeng
     * @date : 2020-02-28 12:08
     * @version: v1.0.0
     * @param current 当前页,从 1 开始
     * @param size
     */
    List<E> data(int current, int size);

}

ExcelPageEnum.java

package com.itplh.common.constant;

/**
 * @description: excel 分页参数枚举
 * @author: tanpeng
 * @date: 2020-02-28 11:29
 * @version: v1.0.0
 */
public enum ExcelPageEnum {

    XLS(10_000, 60_000),
    XLSX(10_000, 1_000_000);

    private int pageSize;
    private int sheetMaxRow;

    ExcelPageEnum(int pageSize, int sheetMaxRow) {
        this.pageSize = pageSize;
        this.sheetMaxRow = sheetMaxRow;
    }

    public int getPageSize() {
        return pageSize;
    }

    public int getSheetMaxRow() {
        return sheetMaxRow;
    }
}
相关标签: # EasyExcel