一、背景：

在某些信息管理場景中，存在大批量導出需求，例如一次性導出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 {@Autowiredprivate 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，設置行訪問窗口大小為100SXSSFWorkbook 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 {// 關閉ExcelWriterif (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