HotXLS：在 Delphi 中使用公式引擎与自定义函数

一个只存储公式字符串的电子表格库，和一个拥有可用公式引擎的库，是两件看起来完全相同、直到你向其中一个要一个数字才显出不同的产品。大多数 Delphi 电子表格代码从不会察觉这道缝隙，因为 Excel 把它糊了起来：把 SUM(B2:B501) 写进一个单元格、保存，Excel 在人打开文件的瞬间就重新算出总数。把人从循环中拿掉，让同一个工作簿跑过一条直接导出成 CSV 的服务器流水线，差异就不再只是学理上的了。CSV 在本该是数字的地方带着字面文本 =SUM(B2:B501)，因为在整个过程中没有任何东西真正求值过这条公式

这正是 HotXLS 站在对的那一边的那条线。它像文件格式那样对待一条公式——存储的文本加上一个可选的缓存结果——所以一次裸 CSV 导出复现的是配方而不是成品。但它也携带一个你可以直接调用的计算引擎，在 XLS 和 XLSX 门面中是同一个引擎，外加一个用于解析引擎从未听过的函数名的钩子。HotXLS 是一个原生 Object Pascal 库，可在不使用 Excel 自动化的情况下从 Delphi 和 C++Builder 读写 XLS 和 XLSX，而它其中一半的计算能力，正是把存储的公式按需变回值的东西

公式是被存储的，而非被急切求值的

把一条公式写进单元格不会计算任何东西。在保存时，工作簿记录下公式文本。在 XLS 侧它还记录由 RecalcOnSave 治理的标志，后者默认为 True 并告诉 Excel 在打开时重新计算。对于注定要给 Excel 用的文件，这个模型是正确的；但对于直接消费单元格值的流水线，无论是 CSV 导出、HTML 导出，还是你自己的代码把单元格读回来，它就是错的。对于这些场景，要用 Calculate 显式求值。它存在于四个入口点：TXLSWorkbook、IXLSWorksheet、TXLSXWorkbook 和 TXLSXWorksheet 都暴露 function Calculate(const Formula: WideString): Variant

// 在进程内求值，然后交付数值而非配方
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV 现在携带数值

交给 Calculate 的表达式是普通的 Excel 公式文本。跨表引用、定义名和嵌套函数都针对当前内存中的工作簿解析，这使该调用远不止用于修补 CSV 导出。把它当作一种断言机制。一个刚刚写完五百行明细的生成器，可以询问工作簿它自己的总计，并把那个数字与它在 Pascal 中独立算出的数字相比较，从而在客户的审计员之前捕获一个 off-by-one 的范围错误

它也为公式密集的输出框定了正确的测试策略。Excel 仍然是公式语言的参考实现，所以对于少数几条承载业务后果的公式，保留一份预期的值由 Excel 自身产出的批准过的夹具文件，并让构建流水线用 Calculate 针对那些夹具求值生成工作簿的公式。差异于是作为 Delphi 中失败的测试浮现，而不是作为客户对比两份报告时发现的差异

用 OnUserFunction 添加业务函数

当引擎遇到一个它不认识的函数名时，它引发一个事件而不是直接失败。在任一工作簿类上赋值 OnUserFunction，你就可以自己解析这次调用：

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args 以 Variant 数组到达
    Handled := True;
  end;
end;

// 接线与使用
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

有三处细节值得关注。第一，只在你确实认出了名字时才设 Handled := True。让它保持 False 允许引擎继续它正常的未知函数处理，所以单个处理程序可以为多个工作簿服务，而不把经过的一切都揽下来。第二，用 SameText 不区分大小写地比较名字，因为公式作者会不分场合地敲 discount( 和 DISCOUNT(。第三，参数到达时已被预先求值：DISCOUNT(A1) 交给你的是 A1 的值，而不是引用，所以一个函数无法得知它的输入来自何处。最后这一点引出了下一节要讲的那项限制

用对待任何外部入口点同样的防御性来对待处理程序体。Args 数组反映的是公式作者键入的任何东西，所以在索引它之前要校验参数计数和类型，并预先决定一次无效调用返回什么：一个 Variant 错误值，还是一个抛出的异常。这个选择很重要，因为在处理程序内部抛出的异常会沿着触发求值的 Calculate 调用传播出去。在一个紧密受控的生成器中这是可接受的，而在一个求值用户编写工作簿的服务中则是粗暴的——那里一条坏公式会放倒整个请求。在这种场景下，在处理程序内部捕获并返回一个周围工作流能识别并记录的哨兵值

位置感知的函数需要 Ex 变体

有些函数合理地依赖于自己在哪里被求值。一个因工作表而异的费率、一个与行相关的查找、一个只在各区域工作表上适用的按区域乘数：这些没有一个能仅凭参数值来回答。普通事件无法表达它，所以引擎提供了 OnUserFunctionEx，除了多一个参数之外完全相同：

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // 同一条公式在不同的区域工作表上产生不同的费率
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext 携带正在求值的单元格的 SheetIndex、Row 和 Col。如果一个函数的结果哪怕只是略微依赖于它的位置，就从一开始接线 Ex 事件。把上下文改造进一个已经有三十条公式在调用的处理程序，比在第一天就选对签名要乱得多，而这两个事件在其他方面如此相似，以至于没什么理由从较窄的那个开始

自定义函数不会随文件进入 Excel

一个自定义函数完全活在你的进程内部。名字 DISCOUNT 只有在你的 Delphi 代码及其事件处理程序正在运行时才有意义。把保存的文件在 Excel 中打开，DISCOUNT 只是一个未被识别的名字；该单元格显示 #NAME?，除非用户机器上碰巧存在一个匹配的 VBA 函数或加载项。这就是把一个演示和一个可交付产品分开的设计事实，它迫使你做出一个必须有意为之而非事后才发觉的选择

逐个单元格地决定你正在交付两种契约中的哪一种。要让用户在 Excel 内部看到重新计算的单元格，必须完全用 Excel 自己的函数词汇表来构建，别无其他。逻辑属于专有的单元格，应该用 Calculate 在进程内求值并以纯值持久化，这样自定义函数的行为就像一条内部计算规则，而不是文件内容。可靠地制造支持工单的失败模式是那个中间地带：持久化一个自定义函数的公式，却指望 Excel 遵从它

"仅值"契约有一个安静的好处：它保护知识产权。一条在你的 Delphi 进程中求值并以数字交付的定价规则，无法像一条可见公式那样从工作簿中被逆向工程出来，而用户也无法通过编辑某个中间单元格来破坏它。发票生成器、佣金对账单和费率卡几乎总是属于这一类。真正需要活公式的情形是交互式的假设分析模型，其中客户被期望改变输入并观看总数移动，而那些必须用 Excel 自己的词汇表加上定义名来构建

计算模式、迭代与 R1C1：XLS 门面的那些旋钮

XLS 门面暴露 Excel 从文件中读取的 BIFF 级计算设置。CalculationMode 接受 xlCalcManual、xlCalcAutomatic（默认）或 xlCalcAutomaticExceptTables，它决定了文件打开后 Excel 的行为方式。一个带有数千条公式的模型工作簿通常以手动模式交付更友好，这样接收者可以自己决定那场重新计算风暴何时发生。EnableIteration（默认 False）连同 MaxIterations（默认 100）和 MaxIterationChange（默认 0.001），解锁了一些金融模型中出现的、迭代收敛型的那种有意为之的循环引用。ReferenceStyle 在 A1 与 R1C1 显示之间切换，而 UseFullPrecision 镜像 Excel 的"按所显示精度"选项

这些属性住在 XLS 门面上，因为它们映射到 BIFF 记录；在生成 .xlsx 时，要让公式不依赖于迭代设置，或者在 Delphi 中算出收敛值并写入结果

数组公式：公开入口在 XLSX

遗留的 CSE 风格数组公式通过 TXLSXRange.SetArrayFormula 创建：

// 一条横跨 A2:A4 的数组公式
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

等效的方法在 XLS 类层级中存在，但位于一个 private 段中，所以没有受支持的方式把新数组公式写入 .xls 文件。已打开文件中已有的那些在往返中完好；你做不到的是创建它们。随之而来的规则足够简单：当数组语义是需求的一部分时，目标是 .xlsx。如果一份遗留的 .xls 交付物确实需要数组行为，务实的路线是在 Delphi 中算出数组结果并把各个值写入单元格

本站还有两篇相关阅读：定义名与跨表公式涵盖引擎执行的名字解析，而CSV 与 TSV 导出文章详述让显式计算变得必要的导出行为。完整的引擎参考（包括受支持的函数集）随 HotXLS Delphi Component 一同发布