springboot应用大批量导出excel产生oom处理措施实践（适用于poi&easyexcel）

一、背景：

在某些信息管理场景中，存在大批量导出需求，例如一次性导出10~100w行excel数据，如果不做特殊的处理，很容易导致Out Of Memory，特别是堆内存溢出。

oom复现

例如修改IDEA运行配置，VM参数初始堆为256m，最大堆为1G，垃圾回收器为G1

-Xms256m -Xmx1G -XX:+UseG1GC

Apply之后重启应用，重启之后打开Java VisualVM，连接刚启动的进程，关注监控tab页面的内存使用情况

使用poi导出，单批次为10w条

等待一段时间，触发了oom异常：

此时其它业务操作也会受到影响，例如此处分页查询已经失效，只能重启临时解决。

二、分批处理实践

思路：每次查询并写入指定数量的excel行，文本设置为1w。

service代码：

package com.tgh.securitydemo.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.tgh.securitydemo.entity.CommonWorkOrderLog;
import com.tgh.securitydemo.entity.excel.WorkOrderLogExportVO;
import com.tgh.securitydemo.entity.excel.WorkOrderLogSearchVO;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author PineTree
 * @description: 导出服务
 * @date 2025/4/27 17:51
 */
@Service
public class ExportService {
    @Autowired
    private ICommonWorkOrderLogService commonWorkOrderLogService;
    private static final int batchSize = 10000; // 每批次大小

    public void exportExcel(WorkOrderLogSearchVO searchVO, HttpServletResponse response) throws IOException {
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=work_order_logs.xlsx");
        if (searchVO.isEasy()) {
            if (searchVO.isBatch()) {
                handleEasyExcelBatch(searchVO, response);
            } else {
                handleEasyExcel(searchVO, response);
            }
        } else {
            if (searchVO.isBatch()) {
                handlePoiBatch(searchVO, response);
            } else {
                handlePoiExcel(searchVO, response);
            }
        }
    }

    private void handlePoiBatch(WorkOrderLogSearchVO searchVO, HttpServletResponse response) throws IOException {
        // 创建SXSSFWorkbook，设置行访问窗口大小为100
        SXSSFWorkbook workbook = new SXSSFWorkbook(100); // 保留3500行在内存中
        Sheet sheet = workbook.createSheet("日志数据-poi分批");
        boolean hasNext = true;
        try {
            int currentPage = 1;
            int rowNum = 0;
            Row row = sheet.createRow(rowNum++);
            generatePoiHead(row);
            while (hasNext) {
                // 分页查询数据
                IPage<CommonWorkOrderLog> pageSearch = new Page<>(currentPage, batchSize);
                IPage<CommonWorkOrderLog> pageData = commonWorkOrderLogService.getOrderPage(pageSearch, new CommonWorkOrderLog());
                List<CommonWorkOrderLog> records = pageData.getRecords();

                if (records.isEmpty()) {
                    break; // 没有数据了，退出循环
                }
                for (CommonWorkOrderLog record : records) {
                    Row poiRow = sheet.createRow(rowNum++);
                    generatePoiDateRow(record, poiRow);
                }
                // 刷新已写入的行
                ((SXSSFSheet)sheet).flushRows(batchSize);
                // 判断是否还有下一页
                hasNext = pageData.getCurrent() * batchSize < searchVO.getExportCount();
                currentPage++;
            }
            workbook.write(response.getOutputStream());
        } finally {
            workbook.dispose();
            workbook.close();
        }
    }

    private void handleEasyExcelBatch(WorkOrderLogSearchVO searchVO, HttpServletResponse response) throws IOException {
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), WorkOrderLogExportVO.class)
                .build();

        try {
            WriteSheet writeSheet = EasyExcel.writerSheet("日志数据-easy分批").build();
            int currentPage = 1;
            boolean hasNext = true;

            while (hasNext) {
                // 分页查询数据
                IPage<CommonWorkOrderLog> pageSearch = new Page<>(currentPage, batchSize);
                IPage<CommonWorkOrderLog> pageData = commonWorkOrderLogService.getOrderPage(pageSearch, new CommonWorkOrderLog());
                List<CommonWorkOrderLog> records = pageData.getRecords();
                List<WorkOrderLogExportVO> exportData = records.stream()
                        .map(WorkOrderLogExportVO::fromEntity)
                        .collect(Collectors.toList());
                // 写入当前批次数据
                excelWriter.write(exportData, writeSheet);
                // 判断是否还有下一页
                hasNext = pageData.getCurrent() * batchSize < searchVO.getExportCount();
                currentPage++;
            }
        } finally {
            // 关闭ExcelWriter
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    private void handleEasyExcel(WorkOrderLogSearchVO searchVO, HttpServletResponse response) throws IOException {
        IPage<CommonWorkOrderLog> page = new Page<>(1, searchVO.getExportCount());
        IPage<CommonWorkOrderLog> orderPage = commonWorkOrderLogService.getOrderPage(page, new CommonWorkOrderLog());
        List<CommonWorkOrderLog> records = orderPage.getRecords();
        List<WorkOrderLogExportVO> exportData = records.stream()
                .map(WorkOrderLogExportVO::fromEntity)
                .collect(Collectors.toList());

        // 使用EasyExcel导出
        EasyExcel.write(response.getOutputStream(), WorkOrderLogExportVO.class)
                .sheet("日志数据-easy单批")
                .doWrite(exportData);
    }

    private void handlePoiExcel(WorkOrderLogSearchVO searchVO, HttpServletResponse response) throws IOException {
        IPage<CommonWorkOrderLog> page = new Page<>(1, searchVO.getExportCount());
        IPage<CommonWorkOrderLog> orderPage = commonWorkOrderLogService.getOrderPage(page, new CommonWorkOrderLog());
        List<CommonWorkOrderLog> records = orderPage.getRecords();
        // 创建工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("日志数据-poi单批");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        generatePoiHead(headerRow);

        // 填充数据
        int rowNum = 1;
        for (CommonWorkOrderLog record : records) {
            Row poiRow = sheet.createRow(rowNum++);
            generatePoiDateRow(record, poiRow);
        }

        // 写入响应流
        workbook.write(response.getOutputStream());
        workbook.close();
    }

    private static void generatePoiHead(Row headerRow) {
        headerRow.createCell(0).setCellValue("工单日志ID");
        headerRow.createCell(1).setCellValue("操作类型");
        headerRow.createCell(2).setCellValue("工单ID");
        headerRow.createCell(3).setCellValue("工单名称");
        headerRow.createCell(4).setCellValue("工单类型");
        headerRow.createCell(5).setCellValue("业务位置ID");
        headerRow.createCell(6).setCellValue("计划完成时间");
        headerRow.createCell(7).setCellValue("实际完成时间");
        headerRow.createCell(8).setCellValue("处理人账号");
        headerRow.createCell(9).setCellValue("处理人名称");
        headerRow.createCell(10).setCellValue("创建时间");
        headerRow.createCell(11).setCellValue("创建人名称");
        headerRow.createCell(12).setCellValue("最后更新人名称");
        headerRow.createCell(13).setCellValue("业务属性1");
        headerRow.createCell(14).setCellValue("业务属性2");
    }

    private static void generatePoiDateRow(CommonWorkOrderLog record, Row poiRow) {
        poiRow.createCell(0).setCellValue(record.getWorkOrderLogId());
        poiRow.createCell(1).setCellValue(record.getOperationType());
        poiRow.createCell(2).setCellValue(record.getWorkOrderId());
        poiRow.createCell(3).setCellValue(record.getWorkOrderName());
        poiRow.createCell(4).setCellValue(record.getWorkOrderType());
        poiRow.createCell(5).setCellValue(record.getBizLocationId());
        poiRow.createCell(6).setCellValue(record.getPlannedCompletionTime().toString());
        poiRow.createCell(7).setCellValue(ObjectUtils.isNotEmpty(record.getActualCompletionTime()) ? record.getActualCompletionTime().toString() : "");
        poiRow.createCell(8).setCellValue(record.getHandleUserAccount());
        poiRow.createCell(9).setCellValue(record.getHandleUserName());
        poiRow.createCell(10).setCellValue(record.getCreateTime().toString());
        poiRow.createCell(11).setCellValue(record.getCreateByName());
        poiRow.createCell(12).setCellValue(record.getLastUpdateName());
        poiRow.createCell(13).setCellValue(record.getBizAttribute1());
        poiRow.createCell(14).setCellValue(record.getBizAttribute2());
    }

}

三、测试验证

poi分批导出10w

poi分批导出100w

easyexcel分批导出10w

easyexcel分批导出100w

四、结论

poi和easyexcel大批量导出，均可通过分批思路来处理oom异常，easyexcel导出比poi慢，但是生成的文件小于poi，本文仅仅解决了大批量excel导出从不能导到能导。如果同时存在多用户，高频率，大批量导出同样会出问题，后续讨论…。

代码免费仓获取完整代码：
前端：https://gitee.com/pinetree-cpu/hello_vue3
后端：https://gitee.com/pinetree-cpu/parent-demon