XSSF Workbook批量设置单元格时程序卡顿的原因及优化方案咨询

让我来拆解下你遇到的这个卡顿问题，以及对应的优化思路——你已经精准定位到row.createCell(i)是瓶颈，核心原因和解决办法都围绕POI处理大数据量Excel的机制展开：

为什么row.createCell(i)会越来越慢？ #

你用的XSSFWorkbook是POI中处理.xlsx的标准实现，它会把整个Excel文档的结构（包括所有行、单元格）都加载在内存中维护成DOM树。当你写入几万行、每行70列的数据时，每创建一个单元格都要更新这个内存中的DOM结构，数据量越大，每次操作的内存开销就会指数级上升——到2万行之后，内存里的DOM已经非常庞大，每次修改都要遍历、更新大量节点，自然就越来越卡。

另外还有几个次要的开销点：

• getCellContentAsString里每次都新建DataFormatter，这个对象是线程安全且可复用的，重复创建会额外消耗资源
• 循环里每次都调用worksheet.getLastRowNum()，频繁查询工作簿状态也会增加不必要的开销
• 每一行都执行System.out.println，控制台输出的IO开销在大数据量下也会被放大

优化步骤（按优先级排序） #

1. 改用SXSSFWorkbook实现流式写入

这是解决卡顿最核心的方案。SXSSFWorkbook是POI专门为大数据量Excel设计的流式处理类，它会把超出内存限制的行自动写入临时文件，只在内存中保留最近的N行（可配置），彻底避免内存DOM膨胀的问题。

修改工作簿创建逻辑：

// 直接在内存创建SXSSFWorkbook，设置内存保留100行，超出的写入临时文件
SXSSFWorkbook output_wb = new SXSSFWorkbook(100);
// 直接创建需要的Sheet，无需先写空文件到磁盘
output_wb.createSheet("Removed records");
output_wb.createSheet("Added records");
output_wb.createSheet("Updated records");

同时删掉原来的createOutputXLSFile函数，不需要先创建空文件再读入，直接在内存构建工作簿即可。

2. 复用DataFormatter对象

把DataFormatter改成类级别的静态常量，避免每次调用getCellContentAsString都新建对象：

// 全局复用的DataFormatter，线程安全
private static final DataFormatter DATA_FORMATTER = new DataFormatter();

private static String getCellContentAsString(Cell cell) {
    String data = "";
    CellType cellType = cell.getCellType();
    switch (cellType) {
        case STRING:
            data = cell.getStringCellValue();
            break;
        case NUMERIC:
        case BOOLEAN:
            data = DATA_FORMATTER.formatCellValue(cell);
            break;
        case ERROR:
            data = String.valueOf(cell.getErrorCellValue());
            break;
        case BLANK:
        case _NONE:
            data = "";
            break;
    }
    return data;
}

3. 优化循环内的冗余操作

• 提前获取初始行号，避免每次循环都查询getLastRowNum()
• 减少控制台打印频率，比如每1000行打印一次，降低IO开销

修改buildRowsByListOfRows函数：

private static void buildRowsByListOfRows(int sheetNumber, ArrayList<Row> sheet, Workbook wb) {
    Sheet worksheet = wb.getSheetAt(sheetNumber);
    int currentRowNum = worksheet.getLastRowNum(); // 提前获取初始行号
    int x = 0;
    System.out.println("Size of array list: " + sheet.size());

    for (Row my_row : sheet) {
        try {
            currentRowNum++;
            Row row = worksheet.createRow(currentRowNum);
            int lastCellNum = my_row.getLastCellNum();
            
            for (int i = 0; i < lastCellNum; i++) {
                Cell sourceCell = my_row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                String cell_value = getCellContentAsString(sourceCell);
                Cell cell = row.createCell(i);
                cell.setCellValue(cell_value);
            }
            
            // 每1000行打印一次进度，避免频繁IO
            if (x % 1000 == 0) {
                System.out.println("Processed row #: " + x);
            }
            x++;
        } catch (Exception e) {
            System.err.println("Error at row: " + x);
            e.printStackTrace();
        }
    }

    // 如果是SXSSFWorkbook，最后清理临时文件
    if (wb instanceof SXSSFWorkbook) {
        ((SXSSFWorkbook) wb).dispose();
    }
}

4. 优化工作簿保存逻辑

用try-with-resources确保资源正确关闭，同时适配SXSSF：

// 最后保存工作簿到文件
try (FileOutputStream fout = new FileOutputStream(output_filename_path)) {
    output_wb.write(fout);
} catch (IOException e) {
    e.printStackTrace();
} finally {
    try {
        output_wb.close();
        if (output_wb instanceof SXSSFWorkbook) {
            ((SXSSFWorkbook) output_wb).dispose(); // 清理临时文件
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

效果验证 #

改用SXSSF之后，内存占用会大幅降低，写入速度也会保持稳定，不会出现越到后面越慢的情况，7万多行的数据应该能在几分钟内完成写入。

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