Java Apache POI编辑超10万条大Excel时内存溢出问题求解

你的问题核心在于：先用XSSFWorkbook全量加载10万+行的Excel到内存，即便后续转成SXSSFWorkbook，原始XSSFWorkbook已经占用了大量内存，导致写入时触发GC overhead超限。SXSSF的优势是流式写入减少内存占用，但如果先全量加载整个文档，就完全失去了它的意义。

下面是针对编辑现有大Excel的正确处理方案：

核心思路 #

采用流式读取+流式写入的组合：用XSSFReader逐行读取原始Excel内容，仅将需要编辑的行/单元格加载到内存，其余内容直接复制到SXSSFWorkbook中，避免全量加载整个文档。

具体实现代码 #

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFCell;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

public class LargeExcelEditor {
    public static void main(String[] args) throws Exception {
        String inputPath = "path/to/your/large.xlsx";
        String outputPath = "path/to/edited/output.xlsx";
        
        // 设置SXSSF内存保留行数，超出的写入临时文件（建议设为100-1000）
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);
        sxssfWorkbook.setCompressTempFiles(true); // 压缩临时文件，减少磁盘占用
        
        try (OPCPackage pkg = OPCPackage.open(new FileInputStream(inputPath))) {
            XSSFReader reader = new XSSFReader(pkg);
            StylesTable stylesTable = reader.getStylesTable();
            
            // 获取原始工作表的输入流（这里假设只有一个工作表，多表需遍历sheetsData）
            InputStream sheetInputStream = reader.getSheetsData().next();
            SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet("Sheet1");
            
            // 注：实际生产环境需用SAX解析器（XSSFSheetXMLHandler）处理流，以下为简化逻辑
            int rowNum = 0;
            while (rowNum < 100000) {
                SXSSFRow row = sxssfSheet.createRow(rowNum);
                // 模拟复制原始行的单元格样式和内容（实际需从SAX事件中获取真实数据）
                for (int colNum = 0; colNum < 10; colNum++) {
                    SXSSFCell cell = row.createCell(colNum);
                    // 复制对应单元格的样式（实际需从原始单元格获取样式索引）
                    XSSFCellStyle style = stylesTable.getStyleAt(0);
                    cell.setCellStyle(style);
                    // 设置原始内容
                    cell.setCellValue(new XSSFRichTextString("Original " + rowNum + "-" + colNum));
                    
                    // 编辑指定单元格：示例修改第5行（rowNum=4）第2列（colNum=1）
                    if (rowNum == 4 && colNum == 1) {
                        cell.setCellValue("Edited Content");
                    }
                }
                rowNum++;
                
                // 定期刷新临时文件，主动释放内存
                if (rowNum % 1000 == 0) {
                    sxssfSheet.flushRows();
                }
            }
            
            // 写入输出文件
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                sxssfWorkbook.write(fos);
            }
        } finally {
            // 清理SXSSF生成的临时文件
            sxssfWorkbook.dispose();
        }
    }
}

关键优化点 #

• 流式读取：用XSSFReader（基于SAX）逐行解析原始Excel，彻底避免全量加载到内存。
• SXSSF窗口控制：通过构造器参数设置内存仅保留指定行数，超出部分自动写入临时文件。
• 临时文件压缩：开启setCompressTempFiles(true)降低磁盘占用。
• 主动刷新：定期调用flushRows()将内存中的行写入磁盘，及时释放内存。

额外建议 #

• 若仅需修改少量单元格，可先定位目标行的位置，仅加载该行数据编辑，其余行直接复制，进一步减少内存消耗。
• 可辅助调整JVM参数（如-Xmx4G，根据机器配置调整），但这只是临时方案，核心优化还是依赖流式处理。

内容的提问来源于stack exchange，提问作者junior