Apache POI v3.17中FormulaEvaluator修改单元格公式后失效问题

核心问题在于：POI 3.17的FormulaEvaluator无法正确解析带会计格式的字符串类型金额，即便Excel本身能计算，POI的公式引擎也会因类型不匹配返回#VALUE!。以下是针对性修复方案：

方案1：从源头解决——写入数字而非字符串 #

这是最彻底的解决方式，避免后续所有解析问题：

1. 先将带格式的美元字符串（如$19,190.90、($1,234.56)）解析为纯数字
2. 以数字类型写入单元格，再为单元格设置会计格式

示例代码（适配Katalon Studio的Java环境）：

import org.apache.poi.ss.usermodel.*;
import java.math.BigDecimal;

// 假设待写入的金额字符串
String amountStr = "$19,190.90";

// 清理格式：移除$、千分位逗号，处理括号表示的负数
String cleanedStr = amountStr.trim()
    .replaceAll("\\$", "")
    .replaceAll(",", "");
if (cleanedStr.startsWith("(") && cleanedStr.endsWith(")")) {
    cleanedStr = "-" + cleanedStr.substring(1, cleanedStr.length() - 1);
}

// 转换为数字并写入单元格
BigDecimal amount = new BigDecimal(cleanedStr);
Cell targetCell = row.createCell(8); // 对应I列
targetCell.setCellValue(amount.doubleValue());

// 设置会计数字格式
CellStyle accountingStyle = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
// 标准美元会计格式代码：_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
accountingStyle.setDataFormat(format.getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
targetCell.setCellStyle(accountingStyle);

这种方式下，单元格存储的是纯数字，会计格式仅用于显示，POI的公式引擎能直接识别数值，计算时不会出现#VALUE!。

方案2：若必须保留字符串写入，手动转换后再计算 #

如果业务限制必须写入字符串，可在计算前手动将引用单元格的字符串金额转为数字，再让POI计算：

1. 遍历公式引用的所有单元格，自定义方法转换字符串为数字
2. 临时将单元格类型改为数字，再调用FormulaEvaluator计算

示例代码：

import org.apache.poi.ss.usermodel.*;

// 自定义转换方法：处理会计格式字符串为数字
private double parseAccountingString(String str) {
    String cleaned = str.trim()
        .replaceAll("\\$", "")
        .replaceAll(",", "");
    if (cleaned.startsWith("(") && cleaned.endsWith(")")) {
        cleaned = "-" + cleaned.substring(1, cleaned.length() - 1);
    }
    return Double.parseDouble(cleaned);
}

// 处理公式=I34-(J34+K34)的场景
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(33); // 对应第34行

// 获取公式引用的单元格
Cell iCell = row.getCell(8);
Cell jCell = row.getCell(9);
Cell kCell = row.getCell(10);

// 转换为数字并更新单元格
iCell.setCellValue(parseAccountingString(iCell.getStringCellValue()));
jCell.setCellValue(parseAccountingString(jCell.getStringCellValue()));
kCell.setCellValue(parseAccountingString(kCell.getStringCellValue()));

// 执行公式计算
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell netCell = row.getCell(11); // Expected Net Amount所在单元格
CellValue result = evaluator.evaluate(netCell);
double correctValue = result.getNumberValue(); // 得到正确的1919.09

关键提示 #

POI 3.17的公式引擎对字符串转数值的支持远弱于Excel，尤其是带特殊格式的字符串，优先选择方案1能避免后续所有潜在问题。

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