使用SXSSF/XSSF写入Excel遇内存溢出与磁盘重复写入错误求助

核心问题拆解 #

• 使用SXSSFWorkbook包装原生Workbook触发OOM：SXSSF的内存行缓存与原受保护文件已加载的内容叠加，加上200万级数据的额外开销，导致内存溢出。
• 直接用原生XSSFWorkbook写入报错：XSSF会在写入一定行数后将部分行刷入磁盘，后续无法修改已刷盘的行范围，而受保护文件的预设行结构或锁定区域与写入操作冲突。

针对性解决方案 #

1. 优化SXSSFWorkbook内存配置 #

• 缩小行缓存大小：初始化时设置更小的缓存行数，比如new SXSSFWorkbook(workbook, 50)，减少内存驻留行数。
• 主动刷盘释放内存：批量写入后调用sheet.flushRows()，强制将内存行刷入磁盘并释放对应内存。
• 启用临时文件压缩：调用sxssfWorkbook.setCompressTempFiles(true)，降低临时文件的内存占用。

2. 规避已刷盘行的修改限制 #

• 先读取目标文件的行范围：获取受保护文件的最后一行行号，从该行之后开始追加数据，完全避开已刷盘的行区域。
• 临时解除文件保护（若有密码）：如果知道文件保护密码，可先解除保护再写入，完成后重新加密：

  workbook.unprotectSheet("yourPassword");
  // 执行写入逻辑
  workbook.protectSheet("yourPassword");
  

3. 分批次写入+临时文件中转 #

因为目标文件无法清空，可先将大数据写入无保护的临时SXSSF文件，再逐批次追加到目标文件：

// 第一步：写入临时文件，避免内存溢出
SXSSFWorkbook tempWorkbook = new SXSSFWorkbook(100);
// 分批次从数据库读取数据写入tempWorkbook（示例省略数据库分页查询逻辑）
tempWorkbook.write(new FileOutputStream("temp_data.xlsx"));
tempWorkbook.dispose(); // 释放临时文件资源

// 第二步：追加到目标受保护文件
XSSFWorkbook targetWorkbook = new XSSFWorkbook(new FileInputStream("target.xlsx"));
XSSFSheet targetSheet = targetWorkbook.getSheetAt(0);
int startRow = targetSheet.getLastRowNum() + 1;

// 读取临时文件并追加
SXSSFWorkbook tempReadWorkbook = new SXSSFWorkbook(new XSSFWorkbook(new FileInputStream("temp_data.xlsx")));
SXSSFSheet tempSheet = tempReadWorkbook.getSheetAt(0);

for (Row tempRow : tempSheet) {
    XSSFRow targetRow = targetSheet.createRow(startRow++);
    // 复制单元格数据（根据实际数据类型调整逻辑）
    for (Cell tempCell : tempRow) {
        XSSFCell targetCell = targetRow.createCell(tempCell.getColumnIndex());
        switch (tempCell.getCellType()) {
            case STRING:
                targetCell.setCellValue(tempCell.getStringCellValue());
                break;
            case NUMERIC:
                targetCell.setCellValue(tempCell.getNumericCellValue());
                break;
            // 其他数据类型处理
        }
    }
    // 每1000行刷新一次，避免内存堆积
    if (startRow % 1000 == 0) {
        targetSheet.flushRows();
    }
}

targetWorkbook.write(new FileOutputStream("target.xlsx"));
targetWorkbook.close();
tempReadWorkbook.dispose();

4. 替换为轻量级写入库 #

如果POI的机制无法适配，可改用阿里的EasyExcel，它原生支持大数据内存优化，对受保护文件的写入兼容性更好：

• Maven依赖：

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.3.2</version>
  </dependency>
  

• 追加写入示例：

  ExcelWriter writer = EasyExcel.write("target.xlsx")
      .withPassword("yourPassword") // 若文件有保护密码
      .append(true)
      .build();
  // 分批次从数据库读取数据，逐批写入
  writer.write(queryBatchData(), EasyExcel.writerSheet().build());
  writer.finish();
  

关键注意事项 #

• 数据库查询必须分页，避免一次性加载200万数据到内存。
• 所有IO操作完成后，必须关闭流并调用dispose()释放SXSSF的临时资源。
• 写入受保护文件时，优先选择追加模式，避免修改已刷盘的行区域。

内容的提问来源于stack exchange，提问作者Улан Дуйшеналиев