EasyExcel处理大数据量分段导出

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); // 设置字体大小为12
            font.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); // 设置字体大小为11
            contentStyle.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<>();

    @Override
    protected 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;

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //标题头设置两种方式 head和class
        List<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;
    }
}