用 HotXLS 在 Delphi 中实现跨工作簿实时求值

HotXLS 可以针对内存中已打开的另一个工作簿，对形如 =[Rates.xlsx]FX!B4*C10 这样的公式求值，而不是针对一个过时的缓存数值。你通过 RegisterExternalWorkbook 按名称注册第二个工作簿，此后每次重算都会直接从该对象读取被引用的单元格。取消注册后，求值就会回退到该文件本身携带的外部链接缓存

这填补了一个具体而令人头疼的空白。Excel 工作簿之间彼此引用是家常便饭：一张汇率表、一份会计科目表、一份由其他团队维护的价目表。当这样的文件被某个库而不是被 Excel 打开时，这些引用通常会解析成 Excel 上一次重算时缓存下来的数值，那可能是几个月前的数据，而无论重算多少次都无法修正，因为数据本身根本不在那里

跨工作簿引用在文件内部长什么样？

外部引用并不是按你在编辑栏里看到的文本形式存储的。工作簿内部保存一张辅助工作簿表，每个公式令牌指向该表中的一个索引，而一个独立的缓存值部分则为每个被引用的单元格保存最近一次已知的结果。这正是为什么一个脱离了配套文件单独打开的文件依然能显示数字

解析文本形式本身也有一些微妙之处。词法分析器会把带方括号的工作簿名当作单个标识符的一部分，因此 [Book.xlsx]Sheet!A1 会成为一个整体令牌，而不是一个不配对的方括号后面跟一个工作表引用。名称随后会被归一化以便查找：引号、路径前缀、文件扩展名和数字索引形式，全都会归并成一个大写的基础名，因此 'C:\Rates\[Rates.xlsx]FX'!B4 和 [rates.xlsx]FX!B4 会解析到同一个已注册的工作簿

注册工作簿并重算

注册按名称进行，而这个名称就是发起引用的那份工作簿的公式中实际写出的名称：

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // valuation.xlsx 中写着 [Rates.xlsx]... 的公式，现在会从
    // 这个实时对象读取，而不是从缓存的外部数值读取
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // 修改源数据并再次重算：新值会一路传递下去
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

注册持有的是一个弱引用。HotXLS 不会取得已注册工作簿的所有权，也不会释放它，这意味着生命周期由你自己管理，而顺序也很重要：应在释放源工作簿之前取消注册，或者至少不要在释放它之后再进行重算

工作簿未注册时会发生什么？

求值会回退到文件本身存储的外部链接缓存，这正是一个脱离配套文件单独打开的工作簿的行为。正是这个回退机制，使得取消注册是安全的，而不是破坏性的

取消注册之后的行为值得精确说明一下，因为它常常让人意外。当一个引用既无法从已注册的工作簿解析、也无法从缓存解析时，求值会产生一个错误，而这个错误不会被写回单元格。单元格会保留它上一次的计算值。重新注册源工作簿并重算，数值就会再次更新

这条"失败时保留原值"的规则是刻意设计的。另一种做法——一旦源不可用就立刻把错误值写进每一个受影响的单元格——会让一张暂时缺失的汇率表，把整个工作簿变成一片错误标记，还需要靠后续一次成功的重算去修复。保留上一次的正确值，意味着一次中断的处理流程留下的仍是一份读起来合情合理的文档

脏标记传播止步于工作簿边界

编辑已注册的源工作簿中的一个单元格，并不会把主工作簿中依赖它的单元格标记为脏。增量重算跟踪的是同一个工作簿内部的依赖关系，它并不会跨边界监视。Excel 的行为也是如此，这正是为什么它的外部引用只在打开文件或显式刷新时更新，而不是持续更新

实际影响是，修改一个源数值需要对引用它的工作簿做一次显式重算，就像上面的示例那样。如果你的应用在一个循环里同时编辑两份工作簿，应该在最后统一重算一次，而不是每编辑一次就重算一次；增量重算与依赖图 中所述的增量引擎，届时会在每个工作簿内部只做最少量的工作

这项特性适用的三种场景

合并汇总是最直观的一种。一份母表引用了十二份月度文件；把这十二份全部注册好，重算一次，就能读到合并后的数字，而完全不需要打开 Excel。每一份的注册名都必须与母表公式里实际写的名称一致，这一点最好对照公式文本去核实，而不是想当然地认为它与文件名相同

情景分析是第二种。注册一个源工作簿，把一组假设写进去，重算依赖它的模型，记录输出结果，然后重复。因为源是一个实时对象，两次迭代之间不需要写任何文件，一百个情景付出的代价是一百次重算，而不是一百次保存再重新加载

交付前校验是第三种，也是最不容易想到的一种。打开一份工作簿，注册它的配套文件，重算，然后把结果与文件本身已携带的数值做对比。出现不一致就意味着缓存值已经过期，这是一个值得在客户收到文件之前就发现的缺陷。这种比较与 比较两份 Excel 工作簿的差异 中所述的工作簿差异对比是天然的搭档

依赖它之前先做引用体检

先跑一遍公式分析。它在一次遍历中就能报告缺失的函数、外部引用和循环依赖组件，让你在着手寻找文件之前，就确切知道一份文档需要哪些工作簿名称：

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

另外两个习惯能省下不少时间。让注册名保持规范——用纯文件名注册，让归一化逻辑去处理公式里带引号、带路径前缀的各种变体。并且要把那些引用了你无法提供的文件的工作簿，当作只读产物对待：重算它会保留缓存值，但任何改动了依赖单元格的编辑，都会让文档处于一种部分数值是最新、部分数值是历史值的状态，而文件里没有任何标记能说明哪些是哪些。在工作簿之间复制公式是另一种操作，有它自己的重新绑定规则，见 跨工作簿复制与公式重新绑定，定义名称还会再加一层复杂性，见 定义名称与跨工作表公式

实时跨工作簿求值、增量重算和公式分析，运行在 Delphi 与 C++Builder 同一套引擎之上；完整功能列表见 HotXLS Delphi 电子表格组件页面