【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;
}
}
上一篇: 新增集群时ssh免密登录配置
下一篇: Markdown 接口文档示例