当前位置：首页 > news >正文

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

news 2025/5/6 7:44:41

一、背景：

在某些信息管理场景中，存在大批量导出需求，例如一次性导出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());}}