Java使用POI处理Excel模板时公式无法自动重计算问题求助

问题原因 #

• 你当前代码仅启用了委托Excel自动重计算的逻辑，注释了POI端主动计算的代码。该方案依赖Excel客户端的「自动计算」开关，若用户端Excel默认关闭自动计算，就只会在点击单元格时才触发重算。
• 填充单元格值时统一传入字符串类型，若公式引用的单元格要求数值/日期格式，会导致公式无法识别合法输入，重算逻辑不触发。
• 若使用.xls格式的旧Excel模板，部分POI版本对setForceFormulaRecalculation的HSSF实现存在兼容问题，不会写入对应的强制重算标记。

修复方案 #

方案A：POI端主动计算所有公式（推荐，不依赖Excel客户端配置） #

优先使用POI内置的公式计算器直接计算结果，写入文件后打开就能直接看到计算值，不需要用户操作。
修改后的代码如下：

public static String fillBook(String filename, String outFilename,  int sheetNumber, String[] params){ 
    String result = "";
    try {
        File file = new File(filename);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(sheetNumber);

        for (String param: params)
        {
            String[] paramArray = param.split("\u0001");
            String address = paramArray[0];
            String value = paramArray.length > 1 ? paramArray[1] : "";

            CellReference cellReference = new CellReference(address);
            Row row = sheet.getRow(cellReference.getRow());
            if (row == null) {
                row = sheet.createRow(cellReference.getRow());
            }
            Cell cell = row.getCell(cellReference.getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            
            // 修复点1：根据单元格类型转换值类型，不要统一传入字符串
            CellType cellType = cell.getCellType();
            if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
                try {
                    // 数值格式转数字传入
                    cell.setCellValue(Double.parseDouble(value));
                } catch (NumberFormatException e) {
                    // 转换失败再当字符串处理
                    cell.setCellValue(value);
                }
            } else {
                cell.setCellValue(value);
            }
        }

        // 修复点2：启用POI端公式计算，先清除旧缓存再全量重算
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        evaluator.clearAllCachedResultValues(); // 清除模板原有缓存值
        evaluator.evaluateAll();

        FileOutputStream out = new FileOutputStream(outFilename);
        workbook.write(out);
        out.close();
        workbook.close(); // 补充关闭工作簿避免资源泄漏

    } catch (Exception e) {
        StringWriter sw = new StringWriter();
        e.printStackTrace(new PrintWriter(sw));
        String exceptionAsString = sw.toString();
        result = e.toString() + " " + exceptionAsString;
    }
    return result;
}

如果遇到POI不支持的特殊公式（部分Excel高级函数POI未实现），可以用如下兼容逻辑，计算失败的公式回退到委托Excel端重算：

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
for (Sheet sheet : workbook) {
    for (Row row : sheet) {
        for (Cell cell : row) {
            if (cell.getCellType() == CellType.FORMULA) {
                try {
                    evaluator.evaluateInCell(cell);
                } catch (Exception e) {
                    // 计算失败的公式标记为需要Excel端重算
                    cell.setCellFormula(cell.getCellFormula());
                }
            }
        }
    }
}

方案B：仅依赖Excel端重算的补充配置 #

如果不需要POI端计算，只需要确保打开文件时不管客户端配置都自动重算，除了原有setForceFormulaRecalculation(true)之外，补充如下设置：

workbook.setForceFormulaRecalculation(true);
// 针对xls格式的兼容设置
if (workbook instanceof HSSFWorkbook) {
    HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
    hssfWorkbook.getWorkbook().setRecalcOnSave(true);
}
// 针对xlsx格式的兼容设置
if (workbook instanceof XSSFWorkbook) {
    XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;
    CTWorkbook ctWorkbook = xssfWorkbook.getCTWorkbook();
    if (!ctWorkbook.isSetCalcPr()) ctWorkbook.addNewCalcPr();
    ctWorkbook.getCalcPr().setCalcId(191028); // 对应Excel 2016及以上版本的计算ID，强制全量重算
    ctWorkbook.getCalcPr().setFullCalcOnLoad(true);
}

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