LlosLab Software Development Blog面向 PDF、Delphi 及文档软件开发者的技术笔记

技术文章

在 Delphi 中逐步追踪 Excel 公式求值过程

HotXLS Component

Excel 将一个小型调试器藏在了显眼之处。选中单元格，打开"公式"选项卡并点击"公式求值"，弹出对话框会显示该公式并在某个子表达式下方划线。按下"求值"后，该子表达式折叠为其计算结果，接着下一个子表达式被加上下划线，如此循环，你可以看到一个冗长的表达式一步步化简为单个数值。这是查找嵌套 IF 中究竟触发了哪个分支、或是哪个引用导致汇总出错的最快手段。HotXLS 通过 TXLSFormulaTracer 精确复现了这一行为，使 Delphi 或 C++Builder 程序能够渲染相同的求值步骤列表，用于审计工作簿、调试生成的公式，或向他人解释某个结果为何如此产生。每条记录的步骤都包含子表达式的文本以及它化简为的值

化简引擎如何遍历表达式

tracer 不会直接介入计算引擎内部。它先对公式进行词法分析，再用递归下降解析器构建语法树，然后以深度优先的方式化简该树，最内层可求值的子表达式最先处理。当某个节点化简为一个值后，该值作为字面量被替换回周围的表达式，引擎再调用真正的计算器对这个已更简单的表达式重新求值。由于每一步都通过工作表的公开 Calculate 方法进行求值，而非走私有捷径，每一步的结果与对该单元格完整重算的结果完全一致。解析器在设计上不具有侵入性，因此可以在任何工作表上运行而不干扰其状态

解析器遵循运算符优先级层次，每个优先级层次对应一个递归层级。从最低绑定到最高绑定依次为：第 0 级比较运算（=、<>、<、>、<=、>=），第 1 级字符串连接（&），第 2 级加减法，第 3 级乘除法，第 4 级乘方，最后是一元正负号。每个层级以上一层级作为操作数来解析，因此更高的层级绑定更紧。这与 Excel 所使用的优先级完全一致，这就是为何 A1*B1+A2*B1 会先化简两个乘积再计算求和：乘法处于第 3 级，加法处于第 2 级，所以乘法在树中更深，最先被化简

追踪公式并遍历步骤

用法与附带的演示程序 Demo/Delphi/FormulaTrace/FormulaTrace.dpr 一致。构建一个工作表（或打开现有工作簿），在该工作表上创建一个 tracer，调用 Trace，然后遍历返回的数组。每个 TXLSFormulaStep 暴露 Depth（用于缩进）、Source（原始子表达式）、Expression（操作数已被替换后的子表达式）和 Value（该步骤的计算结果）

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

单元格引用最先被解析并作为独立步骤出现，然后是乘积化简，接着是带括号的税率因子，最后由最终的乘法收尾。Depth 字段可用于缩进，使最内层的化简在视觉上处于最深处，与 Excel 在外层之前先给最内层项加下划线的行为完全一致

与 locale 无关的字面量陷阱

整个方案中最危险的细节在英文系统上毫无征兆，却会在德文系统上立刻崩溃。当一个计算所得的数值被替换回公式文本时，它必须被写成字符串，再由计算引擎重新解析，而引擎将 . 视为小数点。如果替换时使用了系统 locale，德文的 TFormatSettings 会将税率因子写成 1,08，逗号会被识别为参数分隔符，对 A1*B1*1,08 的重算要么解析成错误的形态，要么直接失败

tracer 通过在构造时锁定一个私有的 TFormatSettings 来规避这一问题，其中 DecimalSeparator 被强制设为 .，ThousandSeparator 设为 #0，从而不会输出任何分组字符。FloatToStr 随后生成的字面量无论操作者的区域设置如何，引擎都能正确读回

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

这类 bug 在作者自己的测试中从不出现，只有当其他 locale 的用户运行相同代码时才会暴露，因此值得明确指出：将一个值来回穿越公式文本是一个序列化问题，而序列化必须与 locale 无关

布尔值化简为 1 和 0

还有一个与替换相关的决策涉及逻辑值。当某个子表达式求值为布尔类型时，tracer 将其写回为 1 或 0，而非 TRUE 或 FALSE。原因在于，化简后的字面量必须能在其所处的任何上下文中被干净地重新解析，而算术运算是最严苛的场景。如果像 A1>A2 这样的比较化简为文本 TRUE，而该文本又落在 TRUE*B1 内部，重算时就要依赖引擎在乘法位置接受一个裸布尔关键字。替换为 1 则完全回避了这个问题，因为 1*B1 在任何算术位置都无歧义。这也与 Excel 自身的强制转换一致，即当期望数值时，TRUE 等效于 1，FALSE 等效于 0

函数调用以原子方式化简

一个简单的步进引擎会先化简函数的各个参数，再对整个函数调用求值。这对 Excel 而言是错误的，tracer 刻意不这样做。函数调用从其原始文本出发，作为一个整体在单步内求值。原因在于短路求值语义。IF、CHOOSE 和 IFERROR 只对其所选的分支求值，若先化简参数则会强迫引擎计算 Excel 从不触及的分支。典型的受害者是防除零的 guard，例如 IF(B1=0,0,A1/B1)：如果 tracer 在求值 IF 之前先化简 A1/B1，guard 就会失效并抛出它本身存在以防止的错误。通过以原子方式对整个调用求值，tracer 保留了使此类 guard 得以工作的惰性求值特性

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

取舍在于，你无法看到函数调用内部的各个独立步骤，但这是正确的行为。将 Excel 从不执行的参数化简展示出来，比将整个调用视为单一求值单元更具误导性

参数分隔符与完整区域引用

还有两处规范化处理保证了重算的正确性。计算引擎的编译器期望 ; 作为函数参数分隔符，因此当 tracer 从解析树重建函数调用时，会用 ; 连接参数，即使用户最初输入的是 ,。写作 SUM(A1,A2,A3) 的公式会被重算为 SUM(A1;A2;A3)，引擎可以接受这一形式。值的替换使重建成为必要，而正确处理分隔符则使重建后的公式能够被解析

区域引用是另一种情况。像 A1:A3 这样的区域不是标量，不能被拆分为三个独立的值，因为使用它的函数期望的是一个区域参数。tracer 将区域以其原始文本保持完整，让外层函数作为整体来化简。在 SUM(A1:A3)*B1 中，区域保持完整，SUM(A1:A3) 在单步内化简为一个数值，随后外层乘法才运行。这与 Excel 在区域操作数和它最终贡献的标量之间所划定的界限完全一致

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

综合以上这些规则，步骤列表成为了 Excel"公式求值"命令的忠实镜像，而非一种近似。化简按照 Excel 执行的顺序发生，替换后的字面量能在任何 locale 下存活，布尔值按 Excel 的方式进行强制转换，惰性函数保持惰性。如果你想进一步扩展引擎以支持自定义函数，公式引擎与自定义函数一文介绍了如何注册它们；若要进行更繁重的数值计算，Delphi 中的统计分布函数一文涵盖了 tracer 所基于的内置函数库。所有这些功能均作为面向 Delphi 和 C++Builder 的 HotXLS 电子表格组件的一部分随附发布，本博客其他文章也涵盖了读取、写入、格式化和计算等相关 API