C#使用NPOI读取Excel公式单元格未编辑状态取值异常问题

差异原因 #

• 原始Excel一般由第三方导出工具生成，未经过微软Excel客户端的公式重算流程，LOOKUP数组公式的缓存值被导出工具错误填充为数值0，CachedFormulaResultType也被标记为数值类型，直接读取缓存就会得到错误的0值
• 手动打开Excel编辑保存时，Excel会自动触发全局公式重算，将LOOKUP返回的字符串结果写入单元格公式缓存，同时更新CachedFormulaResultType为字符串类型，所以场景2读取缓存可以得到正确结果

解决方法 #

无需修改原始Excel文件，只需要在代码中主动触发公式计算，放弃读取文件自带的缓存值即可，修改后的代码如下：

1. 加载工作簿后创建公式计算器，适配XLS/XLSX两种格式

var book = WorkbookFactory.Create(this._fileFullPath);
// 新增公式计算器
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(book);
var sheet = book.GetSheetAt(0);

2. 替换原有公式类型单元格的处理逻辑，主动计算结果

case CellType.Formula:
    // 主动计算公式结果，忽略文件自带的错误缓存
    CellValue calculatedValue = evaluator.Evaluate(cell);
    switch (calculatedValue.CellType)
    {
        case CellType.String:
            value = calculatedValue.StringValue;
            break;
        case CellType.Numeric:
            // 可选：判断是否为日期格式，做对应转换
            if (DateUtil.IsCellDateFormatted(cell))
            {
                value = DateUtil.GetJavaDate(calculatedValue.NumberValue).ToString("yyyy-MM-dd");
            }
            else
            {
                value = calculatedValue.NumberValue.ToString();
            }
            break;
        case CellType.Boolean:
            value = calculatedValue.BooleanValue.ToString();
            break;
        case CellType.Blank:
            break;
        default:
            StringBuilder sb = new StringBuilder();
            sb.AppendLine($"セルタイプ({cell.CellType})ですが、");
            sb.AppendLine($"計算結果のセルタイプ({calculatedValue.CellType})に該当する処理がありません。");
            throw new Exception(sb.ToString());
    }
    break;

3. 可选优化：如果需要批量读取大量公式单元格，可以在加载工作簿后先调用evaluator.EvaluateAll()预计算所有公式，再遍历读取，性能更高。

内容的提问来源于stack exchange，提问作者Angus B.