如何在使用Apache POI StreamingReader读取大Excel时高效同步写入？

针对大Excel文件的处理，核心思路是用流式读取逐行解析原文件，同时用流式写入逐行生成新文件，既不加载整个文件到内存（解决OOM问题），也不用缓存全量数据（解决写入耗时问题）。以下是两种可行方案：

方案一：第三方StreamingReader + POI SXSSFWorkbook #

利用你已经在使用的StreamingReader做流式读，搭配POI官方的SXSSFWorkbook做流式写，代码简洁易维护：

// 初始化流式写对象：内存中保留100行数据，超过则刷到临时文件
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);
// 初始化流式读对象：配置缓存和缓冲区大小，可按需调整
StreamingReader reader = StreamingReader.builder()
        .rowCacheSize(100)
        .bufferSize(4096)
        .open(new FileInputStream("你的输入文件路径.xlsx"));

// 创建和原表同名的目标工作表
SXSSFSheet targetSheet = sxssfWorkbook.createSheet(reader.getSheetName());

int rowIndex = 0;
for (Row sourceRow : reader) {
    SXSSFRow targetRow = targetSheet.createRow(rowIndex);
    int sourceCellCount = sourceRow.getLastCellNum();

    // 复制原行的所有单元格内容
    for (int colIndex = 0; colIndex < sourceCellCount; colIndex++) {
        Cell sourceCell = sourceRow.getCell(colIndex);
        SXSSFCell targetCell = targetRow.createCell(colIndex);
        if (sourceCell != null) {
            // 简单复制单元格值，如需保留样式可额外处理（注意SXSSF样式复用）
            targetCell.setCellValue(sourceCell.toString());
        }
    }

    // 执行你的数据验证逻辑，获取错误信息
    String errorMsg = validateRowData(sourceRow);
    // 追加"Error Message"列
    SXSSFCell errorCell = targetRow.createCell(sourceCellCount);
    errorCell.setCellValue(errorMsg != null ? errorMsg : "");

    rowIndex++;
    // 每处理1000行手动刷盘，进一步降低内存占用
    if (rowIndex % 1000 == 0) {
        ((SXSSFSheet) targetSheet).flushRows();
    }
}

// 写出到目标输出流（比如HTTP响应流或本地文件）
sxssfWorkbook.write(new FileOutputStream("你的输出文件路径.xlsx"));
// 清理SXSSF的临时文件，避免磁盘占用
sxssfWorkbook.dispose();
reader.close();

注意事项 #

• 样式处理：如果原Excel有复杂样式，直接复制CellStyle可能导致内存溢出，建议复用SXSSF的样式对象，避免重复创建。
• 公式处理：StreamingReader默认读取公式计算后的结果，如需保留原公式，需在读取时额外配置。

方案二：POI原生SAX解析 + SXSSFWorkbook #

如果不想依赖第三方库，用POI原生的SAX事件模型做流式读，配合SXSSF写，原理和方案一一致，但代码更繁琐：

SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);
SXSSFSheet targetSheet = sxssfWorkbook.createSheet("Sheet1");

// 打开原Excel包
OPCPackage pkg = OPCPackage.open(new File("你的输入文件路径.xlsx"));
XSSFReader xssfReader = new XSSFReader(pkg);
SharedStringsTable sst = xssfReader.getSharedStringsTable();

// 配置SAX解析器
XMLReader parser = XMLReaderFactory.createXMLReader();
parser.setContentHandler(new XSSFSheetXMLHandler(sst, new XSSFSheetXMLHandler.SheetContentsHandler() {
    private int currentRowNum = -1;
    private SXSSFRow currentTargetRow;
    private List<String> currentRowValues = new ArrayList<>();

    @Override
    public void startRow(int rowNum) {
        currentRowNum = rowNum;
        currentRowValues.clear();
        currentTargetRow = targetSheet.createRow(rowNum);
    }

    @Override
    public void cell(String cellRef, String cellValue, XSSFComment comment) {
        currentRowValues.add(cellValue);
        // 写入原单元格值
        int colNum = CellReference.convertColStringToIndex(cellRef.substring(0, 1));
        SXSSFCell targetCell = currentTargetRow.createCell(colNum);
        targetCell.setCellValue(cellValue);
    }

    @Override
    public void endRow(int rowNum) {
        // 执行数据验证
        String errorMsg = validateRowData(currentRowValues);
        // 追加错误列
        SXSSFCell errorCell = currentTargetRow.createCell(currentRowValues.size());
        errorCell.setCellValue(errorMsg != null ? errorMsg : "");

        // 定期刷盘
        if (rowNum % 1000 == 0) {
            ((SXSSFSheet) targetSheet).flushRows();
        }
    }
}, null, false));

// 开始解析工作表
InputStream sheetIn = xssfReader.getSheetsData().next();
parser.parse(new InputSource(sheetIn));

// 写出并清理资源
sxssfWorkbook.write(new FileOutputStream("你的输出文件路径.xlsx"));
sxssfWorkbook.dispose();
pkg.close();
sheetIn.close();

方案对比 #

• 方案一：代码简洁，依赖第三方库但封装完善，开发效率高，适合大多数场景。
• 方案二：纯原生POI实现，无额外依赖，代码复杂度高，适合对依赖有严格要求的场景。

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