Apache POI修改Excel单元格后无法获取M14更新值求助

问题描述 #

我使用Apache POI通过setCellValue方法修改Excel单元格，尝试获取依赖该修改单元格的M14单元格的值。手动打开Excel时，M14的值能正常更新，但通过代码System.out.println("Updated Value at M14: " + cellM14.getNumericCellValue());输出时，得到的仍是M14的旧值。由于存在命名相关Bug，无法使用Formula Evaluator，求实现正确打印M14的更新值。

原代码 #

String filePath = "file";
try (FileInputStream fileInputStream = new FileInputStream(new File(filePath));
     XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)) {
    Sheet sheet = workbook.getSheet("input");
    workbook.setForceFormulaRecalculation(true);

    // 检查第13行是否存在，打印M14原始值
    Row row13 = sheet.getRow(13);
    if (row13 != null) {
        Cell cellM14 = row13.getCell(11);
        if (cellM14 != null) {
            System.out.println("Original Value at M14: " + cellM14.getNumericCellValue());
        } else {
            System.out.println("Cell M14 is null.");
        }
    } else {
        System.out.println("Row 13 is null.");
    }

    // 修改H5单元格（行索引4，列索引7）的值
    Row row4 = sheet.getRow(4);
    if (row4 == null) {
        row4 = sheet.createRow(4);
    }
    Cell sub = row4.getCell(7);
    if (sub == null) {
        sub = row4.createCell(7);
    }
    sub.setCellValue("nanoparticle");

    // 保存工作簿
    try (FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath))) {
        workbook.write(fileOutputStream);
    }

} catch (Exception e) {
    e.printStackTrace();
}
try (FileInputStream fileInputStream = new FileInputStream(new File(filePath));
     XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)) {
    workbook.setForceFormulaRecalculation(true);
    try (FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath))) {
        workbook.write(fileOutputStream);
    }
    Sheet sheet = workbook.getSheet("input");

    // 检查第13行是否存在，打印M14更新后的值
    Row row13 = sheet.getRow(13);
    if (row13 != null) {
        Cell cellM14 = row13.getCell(11);
        if (cellM14 != null) {
            System.out.println("Updated Value at M14: " + cellM14.getNumericCellValue());
        } else {
            System.out.println("Cell M14 is null.");
        }
    } else {
        System.out.println("Row 13 is null.");
    }
} catch (Exception e) {
    e.printStackTrace();
}

解决方案 #

方案1：手动实现公式逻辑（推荐，跨平台且不依赖POI公式计算） #

如果已知M14单元格的具体公式逻辑，可以直接在代码中根据修改后的H5值计算出M14的结果，完全绕过POI的公式计算模块。

比如假设M14的公式是=IF(H5="nanoparticle", 100, 0)，可以在代码中添加如下计算逻辑：

// 在修改H5后，直接计算M14的值
String h5Value = sub.getStringCellValue();
double m14Value = "nanoparticle".equals(h5Value) ? 100 : 0;
System.out.println("Calculated Value at M14: " + m14Value);

这种方式完全避免了使用Formula Evaluator，也不受POI公式解析Bug的影响。

方案2：借助外部Excel进程触发重算（依赖环境） #

如果无法手动实现公式逻辑，可以通过调用本地Excel程序的命令行参数来触发重算并保存，之后再读取文件获取更新后的值。

以Windows环境为例，添加如下代码片段（需要确保Excel路径正确）：

// 保存文件后调用Excel重算
String excelPath = "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE";
ProcessBuilder pb = new ProcessBuilder(excelPath, "/e", "/x", "/r", filePath);
pb.start().waitFor();

这段代码会启动Excel打开目标文件，触发自动重算后退出，之后再读取文件就能获取更新后的M14值。注意该方案仅适用于安装了Microsoft Excel的Windows环境，跨平台场景不适用。

方案3：绕过Formula Evaluator的Bug（针对性修复） #

如果Bug仅影响特定命名范围的公式解析，可以尝试修改Excel中的公式写法，避免使用触发Bug的语法（比如避免整行/整列的 dotted range 表达式），之后再使用Formula Evaluator计算：

// 修改公式写法后，尝试使用Formula Evaluator
XSSFFormulaEvaluator evaluator = XSSFFormulaEvaluator.create(workbook, null, null);
evaluator.evaluateFormulaCell(cellM14);
System.out.println("Updated Value at M14: " + cellM14.getNumericCellValue());

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