使用Java POI处理大XLSX文件生成数据透视表时遇OOM问题求助

你的问题核心在于XSSFWorkbook会将整个Excel文件加载到内存中，100MB的XLSX展开后在内存中会占用远大于100MB的空间，这直接导致了OOM。下面给你几个可行的解决方案，从根本上解决内存占用问题，同时满足生成数据透视表的需求：

方案一：使用POI Event API（SAX解析）读取原文件 + SXSSF流式写入构建新工作簿 #

这是最推荐的方案，既避免了一次性加载整个文件到内存，又能在新工作簿中生成数据透视表。

步骤详解： #

1. 用SAX解析原大文件：POI的Event API基于SAX，会逐行读取Excel内容，不会将整个文件存入内存。你需要实现XSSFSheetXMLHandler.SheetContentsHandler来处理每一行的数据。
2. 用SXSSF流式写入数据到新工作簿：SXSSF是POI的流式写入实现，它会将大部分数据写入临时文件，内存中仅保留最近的N行（默认100行），大幅降低内存占用。
3. 在SXSSF工作簿中创建数据透视表：从POI 4.1.0版本开始，SXSSF对数据透视表的支持已经比较完善，可以直接基于SXSSFSheet创建透视表。

代码示例（核心部分）： #

1. SAX读取原文件的工具类

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

public class LargeXlsxReader {
    public static void readSheet(String filePath, int sheetIndex, XSSFSheetXMLHandler.SheetContentsHandler handler) throws Exception {
        OPCPackage pkg = OPCPackage.open(filePath);
        XSSFReader reader = new XSSFReader(pkg);
        SharedStringsTable sst = reader.getSharedStringsTable();
        
        XMLReader parser = XMLReaderFactory.createXMLReader();
        parser.setContentHandler(new XSSFSheetXMLHandler(sst, null, handler, false));
        
        // 获取指定索引的工作表流
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
        int currentIndex = 0;
        while (sheetIterator.hasNext()) {
            InputSource sheetSource = new InputSource(sheetIterator.next());
            if (currentIndex == sheetIndex) {
                parser.parse(sheetSource);
                break;
            }
            currentIndex++;
        }
        pkg.close();
    }
}

2. 读取数据并写入SXSSF，同时创建透视表

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;

public class PivotGenerator {
    public static void main(String[] args) throws Exception {
        // 创建SXSSF工作簿，设置内存中保留100行，超出的写入临时文件
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        // 开启临时文件压缩，节省磁盘空间
        wb.setCompressTempFiles(true);

        // 读取原文件的第一个工作表，写入到新工作簿的Sheet1
        Sheet sheet1 = wb.createSheet("数据源1");
        LargeXlsxReader.readSheet("workbook.xlsx", 0, new XSSFSheetXMLHandler.SheetContentsHandler() {
            private Row currentRow;
            @Override
            public void startRow(int rowNum) {
                currentRow = sheet1.createRow(rowNum);
            }
            @Override
            public void cell(String cellReference, String formattedValue) {
                Cell cell = currentRow.createCell(CellReference.convertColStringToIndex(cellReference.substring(0, 1)));
                cell.setCellValue(formattedValue);
            }
            @Override
            public void endRow(int rowNum) {}
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {}
        });

        // 读取原文件的第二个工作表，写入到新工作簿的Sheet2
        Sheet sheet2 = wb.createSheet("数据源2");
        LargeXlsxReader.readSheet("workbook.xlsx", 1, new XSSFSheetXMLHandler.SheetContentsHandler() {
            private Row currentRow;
            @Override
            public void startRow(int rowNum) {
                currentRow = sheet2.createRow(rowNum);
            }
            @Override
            public void cell(String cellReference, String formattedValue) {
                Cell cell = currentRow.createCell(CellReference.convertColStringToIndex(cellReference.substring(0, 1)));
                cell.setCellValue(formattedValue);
            }
            @Override
            public void endRow(int rowNum) {}
            @Override
            public void headerFooter(String text, boolean isHeader, String tagName) {}
        });

        // 为Sheet1创建数据透视表（示例：A列作为行标签，B列作为值字段统计数量）
        AreaReference sourceArea1 = new AreaReference(
            new CellReference(0, 0), 
            new CellReference(sheet1.getLastRowNum(), sheet1.getRow(0).getLastCellNum()-1), 
            SpreadsheetVersion.EXCEL2007
        );
        Sheet pivotSheet1 = wb.createSheet("透视表1");
        CellReference pivotCell1 = new CellReference(0, 0);
        PivotTable pivotTable1 = pivotSheet1.createPivotTable(sourceArea1, pivotCell1, sheet1);
        // 设置行标签（假设A列是分类字段）
        pivotTable1.addRowLabel(0);
        // 设置值字段（统计B列的数量）
        pivotTable1.addColumnLabel(DataConsolidateFunction.COUNT, 1);

        // 为Sheet2创建数据透视表（类似逻辑）
        AreaReference sourceArea2 = new AreaReference(
            new CellReference(0, 0), 
            new CellReference(sheet2.getLastRowNum(), sheet2.getRow(0).getLastCellNum()-1), 
            SpreadsheetVersion.EXCEL2007
        );
        Sheet pivotSheet2 = wb.createSheet("透视表2");
        CellReference pivotCell2 = new CellReference(0, 0);
        PivotTable pivotTable2 = pivotSheet2.createPivotTable(sourceArea2, pivotCell2, sheet2);
        pivotTable2.addRowLabel(0);
        pivotTable2.addColumnLabel(DataConsolidateFunction.COUNT, 1);

        // 写入最终文件
        try (FileOutputStream fos = new FileOutputStream("final_pivot.xlsx")) {
            wb.write(fos);
        }
        // 清理SXSSF的临时文件，避免磁盘残留
        wb.dispose();
    }
}

注意事项： #

• POI版本：请使用POI 4.1.0及以上版本，确保SXSSF对数据透视表的支持正常。
• 样式处理：如果原文件有复杂的单元格样式，你需要在SAX解析时收集样式信息，然后在写入SXSSF时应用，这部分需要额外处理。
• 临时文件：SXSSF会生成临时文件，默认存储在系统临时目录，你可以通过wb.setTempFileCreationStrategy()指定自定义目录，避免磁盘空间不足。

方案二：调整JVM堆内存（临时应急方案） #

如果你的场景只是偶尔处理100MB的文件，也可以尝试调整JVM启动参数，比如-Xmx512m或-Xmx1g，增加堆内存上限。但这种方案治标不治本，当文件更大时（比如500MB）还是会OOM，不推荐作为长期解决方案。

方案三：拆分原文件后合并透视表 #

你提到的拆分文件思路是可行的，但不需要用XSSF加载整个文件，而是用SAX解析逐行拆分：

1. 用SAX解析原文件，将两个工作表分别保存为两个独立的小XLSX文件（用SXSSF写入）。
2. 再创建一个新的SXSSF工作簿，将两个小文件的工作表导入进来，然后生成透视表。
   不过这个方案多了一步拆分和导入，效率不如方案一，所以优先推荐方案一。

最后，你担心的“数据透视表需通过筛选快速查看数据及统计数量”的需求，方案一生成的SXSSF工作簿完全可以满足，因为最终的XLSX文件和普通XLSX没有区别，透视表的筛选、统计功能都正常。

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