1.场景说明
在实际生产场景中会存在同时有多人操作巨量数据导出，如果是采取的先把所有符合筛选条件的数据查询出来，再挨个写入Excel的方式，可能会产生CPU飙高到100%，内存被占满，系统罢工的情况，因此需要采用其他方案解决此问题。本人采取的方案是分段导出、批量写策略，假设一共50万数据需要导出，每查询到1000条数据就写入Excel，直到查询完50万数据并写入Excel为止。需要注意的是，生成的File文件及其他对象需要及时释放内存，以给其他的操作腾出可用空间。
3.基本实现
3.1添加依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>
3.2 代码示例
package com.jdq.mall.util;import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Objects;import lombok.extern.slf4j.Slf4j;import java.util.*;/*** @author SuperMan* @date 2024年07月31日* @description EasyExcel大数据量批量写、分段写实现*/
@Slf4j
public class BatchExportEasyExcelUtil {public static void batchExportToExcel() {int pageNum = 1;int pageSize = 10;//文件名String fileName = "D:\\Users\\SuperMan\\Desktop\\test\\user.xlsx";//1、模拟数据List<Map> data = simulatedData();//计算模拟数据的总页数int pageTotal = (data.size() % pageSize) > 0 ? (data.size() / pageSize) + 1 : (data.size() / pageSize);ExcelWriter excelWriter = null;// 2、定义动态表头List<List<String>> headList = new ArrayList<>();//字段中文说明//List<String> headCodeList = new ArrayList<>();//字段编码//配置：在实际项目中，可以是枚举类，可以是数据库表里的数据，或者其他数据来源Map<String, String> fieldMap = new HashMap<>();fieldMap.put("idCard", "身份证号码");fieldMap.put("id", "主键");fieldMap.put("name", "姓名");fieldMap.put("address", "所在地址");List<String> finalFieldList = new ArrayList<>(fieldMap.keySet());//中文表头for (String field : finalFieldList) {headList.add(Arrays.asList(fieldMap.get(field)));//headCodeList.add(field);}try {//3、设置样式excelWriter = EasyExcel.write(fileName, Map.class).build();//3.1 表头样式WriteCellStyle headStyle = new WriteCellStyle();WriteFont font = new WriteFont();font.setFontHeightInPoints((short) 12); // 设置字体大小为12font.setBold(false);headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);headStyle.setVerticalAlignment(VerticalAlignment.CENTER);headStyle.setWriteFont(font);headStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//3.2 单元格内容样式WriteCellStyle contentStyle = new WriteCellStyle();WriteFont font2 = new WriteFont();font2.setBold(false);font2.setFontHeightInPoints((short) 11); // 设置字体大小为11contentStyle.setWriteFont(font2);contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);//批量写临时容器List<List<Object>> targetList = new ArrayList<>();//批量写数量，达到配置的数量时则开启写入。可使用Apollo、Nacos、SpringCloudConfig等进行动态配置int batchWriteSize = 2000;//单个工作表允许写的最大数据量，达到配置的数据量时，需要重新开启一个新的Sheet工作表。可使用Apollo、Nacos、SpringCloudConfig等进行动态配置int nextSheetThreshold = 50000;int tempSheetRowCount = 0;//Excel工作表序号int sheetNo = 0;//4、创建工作表，表头和列表是自适应的。WriteSheet writeSheet = EasyExcel.writerSheet("会员信息" + sheetNo).head(headList).registerWriteHandler(new HorizontalCellStyleStrategy(headStyle, contentStyle)).registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/.registerWriteHandler(new FreezeHeaderStrategy())/*冻结固定首行*/.build();long startTime = System.currentTimeMillis();while (true) {System.out.println("数据导出开始，第" + pageNum + "页,总页数：" + pageTotal);// 5、模拟分页查询方法List<Map> pageDataList = queryUsersByPage(pageNum, pageSize, data);if (pageDataList == null || pageDataList.isEmpty()) {// 查询结果为空时，终止循环System.out.println("查询结果为空，终止循环，总用时：" + (System.currentTimeMillis() - startTime) / 1000 + "秒");break;}//6、处理分页查询的数据，将获取的数据集处理成与Excel单元格表头匹配的数据。//即：单元格表头只有一行，而数据有多行，将数据集中的每条数据转换为与单元格表头匹配的单行数据int total = pageDataList.size();for (int i = 0; i < total; i++) {Map map = pageDataList.get(i);List<Object> rowDatas = new ArrayList<>();for (String field : finalFieldList) {String value = "";if (Objects.nonNull(map.get(field))) {value = String.valueOf(map.get(field));}rowDatas.add(value);}targetList.add(rowDatas);tempSheetRowCount++;//batchWriteSize 和nextSheetThreshold要是倍数关系，且nextSheetThreshold要大于等于batchWriteSize //例如：batchWriteSize 每次批量取2000条数据，nextSheetThreshold单个Sheet工作表所允许写的数据阈值为50000，50000/2000=25，是整数倍关系，符合条件设定int sheetRowCountThreShold = nextSheetThreshold < batchWriteSize ? batchWriteSize : nextSheetThreshold;if (targetList.size() == batchWriteSize ) {excelWriter.write(targetList, writeSheet);targetList = new ArrayList<>();}if (tempSheetRowCount == sheetRowCountThreShold) {sheetNo++;log.info("会员信息导出，生成新的工作表，当前序号：{}", sheetNo);writeSheet = EasyExcel.writerSheet("会员信息" + sheetNo).head(headList).registerWriteHandler(new HorizontalCellStyleStrategy(headStyle, contentStyle)).registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/.registerWriteHandler(new FreezeHeaderStrategy())/*冻结固定首行*/.build();tempSheetRowCount = 0;}//是否是最后一页，且还有数据未写完boolean isLastPage = (i == total - 1);if (isLastPage && CollUtil.isNotEmpty(targetList)) {excelWriter.write(targetList, writeSheet);}}// 记录日志：准备查询下一页log.info("第：{}页数据写入完成,总页数：{}",pageNum, pageTotal);pageNum++;}//return new File(filePath);} finally {if (excelWriter != null) {// 关闭Writer释放资源excelWriter.finish();}}}/*** 模拟数据（DB存储）*/private static List<Map> simulatedData() {List<Map> data = new ArrayList<>();for (int i = 1; i <= 100000; i++) {Map userMap = new HashMap();userMap.put("id", i);userMap.put("name", "姓名" + i);userMap.put("address", "所在地址所在地址所在地址" + i);data.add(userMap);}return data;}/*** 模拟分页查询** @param pageNum 页码* @param pageSize 页大小* @param data 数据*/private static List<Map> queryUsersByPage(int pageNum, int pageSize, List<Map> data) {// 分页查询逻辑实现，此处以伪代码表示List<List<Map>> lists = splitList(data, pageSize);if (lists.size() < pageNum) {return null;}// 返回查询结果return lists.get(pageNum - 1);}public static <T> List<List<T>> splitList(List<T> alllist, int groupSize) {int length = alllist.size();// 计算可以分成多少组int num = (length + groupSize - 1) / groupSize;List<List<T>> newList = new ArrayList(num);for (int i = 0; i < num; i++) {// 开始位置int fromIndex = i * groupSize;// 结束位置int toIndex = (i + 1) * groupSize < length ? (i + 1) * groupSize : length;newList.add(alllist.subList(fromIndex, toIndex));}return newList;}public static void main(String[] args) {batchExportToExcel();}}@Data
class DataModel {// @ExcelProperty("用户ID")// @ColumnWidth(15) // 设置列宽为15字符宽度// @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40) // 设置头部样式，比如背景颜色// @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)// @HeadFontStyle(bold = BooleanEnum.TRUE, fontHeightInPoints = 12) // 设置头部字体样式private int id;// @ExcelProperty("姓名")// @ColumnWidth(15) // 设置列宽为15字符宽度// @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)// @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40) // 设置头部样式，比如背景颜色// @HeadFontStyle(bold = BooleanEnum.TRUE, fontHeightInPoints = 12) // 设置头部字体样式private String name;// @ExcelProperty("身份证")// @ColumnWidth(35) // 设置列宽为15字符宽度// @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)// @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40) // 设置头部样式，比如背景颜色// @HeadFontStyle(bold = BooleanEnum.TRUE, fontHeightInPoints = 12) // 设置头部字体样式// private String idCard;// @ExcelProperty("地址")// @ColumnWidth(50) // 设置列宽为15字符宽度// @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)// @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40) // 设置头部样式，比如背景颜色// @HeadFontStyle(bold = BooleanEnum.TRUE, fontHeightInPoints = 12) // 设置头部字体样式private String address;
}/*** 自适应列宽计算策略*/
class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {/*** 工作表缓存*/private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {//若表头、数据不为空，则需要计算列宽boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {//每个页签缓存最大的Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());Integer columnWidth = this.dataLength(cellDataList, cell, isHead);// 单元格文本长度大于32换行if (columnWidth >= 0) {if (columnWidth > 32) {columnWidth = 32;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());//1.首次计算列宽//2.当前计算列宽大于缓存的列宽，及时更新最大列宽，并设置到工作表中if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);Sheet sheet = writeSheetHolder.getSheet();sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}/*** 计算数据长度** @param cellDataList 数据源* @param cell 单元格* @param isHead 是否是表头*/private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData<?> cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:// 换行符（数据需要提前解析好）int index = cellData.getStringValue().indexOf("\n");return index != -1 ?cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}
}/*** 冻结固定首行策略*/
class FreezeHeaderStrategy implements SheetWriteHandler {public int colSplit = 0, leftmostColumn = 0;@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {//标题头设置两种方式 head和classList<List<String>> head = writeSheetHolder.getHead();//标题行Sheet sheet = writeSheetHolder.getSheet();//当head获取的为空时从class获取if (Objects.isNull(head)) {Class clazz = writeSheetHolder.getClazz();if (!Objects.isNull(clazz)) {head = getHead(clazz);}}int row = head.stream().mapToInt(List::size).max().orElse(1);/** 参数1：要拆分的列号。该参数指定了在哪一列之前（从左到右的列数）拆分窗格。例如，如果将其设置为 1，则从第二列之前进行拆分。* 参数2：要拆分的行号。该参数指定了在哪一行之前（从上到下的行数）拆分窗格。例如，如果将其设置为 1，则从第二行之前进行拆分。* 参数3：左侧可见的列数。该参数指定了在拆分窗格时左侧可见的列数。通常将其设置为等于colSplit即可。* 参数4：顶部可见的行数。该参数指定了在拆分窗格时顶部可见的行数。通常将其设置为等于rowSplit即可。*/sheet.createFreezePane(colSplit, row, leftmostColumn, row);}/*** 获取标题头<pre>通过class获取ExcelProperty注解中的标题头</pre>** @param clazz 类* @return 标题头*/private List<List<String>> getHead(Class clazz) {List<List<String>> result = Collections.emptyList();Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {if (field.isAnnotationPresent(ExcelProperty.class)) {ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (!Objects.isNull(annotation)) {result.add(Arrays.asList(annotation.value()));}}}return result;}
}
