通过SQL查询出来一次性写，在大数据量时存在OOM的隐患
分页查询、分批次写数据，避免导出大数据量时内存消耗陡增
基于mybatis-puls分页查询
example
导出250万用户数据，代码看起来是不是很清爽呢？
@Test
public void pageWrite() {// build excel writerExcelWriter excelWriter = EasyExcel.write("E:/easyexcel/testPageWrite.xlsx", PageWriteData.class).excelType(ExcelTypeEnum.XLSX).build();// page writeEasyExcelUtils.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 1000000int 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 countlong pageCount = (totalCount - 1) / pageSize + 1;long sheetCount = (totalCount - 1) / sheetMaxRow + 1;int currentPage = 0;// page write dataWriteSheet 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 1excelWriter.write(pageQueryService.data(++currentPage, pageSize), sheet);if (currentPage >= pageCount) {break;}}}// close sourceexcelWriter.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;}
}