LlosLab Software Development Blog面向 PDF、Delphi 和文档软件开发者的工程笔记

技术文章

Delphi 中的已定义名称和跨工作表公式（HotXLS）

HotXLS 组件

已定义名称是代表常量、单元格范围或公式表达式的标签，它在工作簿中仅存储一次，并在需要它的任何地方被符号化地引用；在公式中写入 TaxRate，引擎就会将其解析为该名称定义所包含的任何内容，无论那是字面量 0.08 还是范围 Data!$A$2:$D$100；跨工作表引用是另一个概念：Data!D2 通过使用工作表名称限定地址来访问另一个工作表上的单元格；将这两者结合起来，汇总表就可以通过一个从未提及字面地址的名称对明细表求和，这正是您在由生成器组装并由会计师审计的工作簿中所想要的

HotXLS 是 losLab 专为 XLS 和 XLSX 文件打造的原生 Delphi 库，它公开了这两种格式的名称表，支持创建、查找和删除访问，并配有一个能在进程内解析名称和跨工作表引用的公式引擎；这两种格式保持着独立的类层次结构，它们名称 API 之间的差异是将代码从一种格式移植到另一种格式时容易出错的部分

两个不共享接口的名称存储

在 XLS 方面，TXLSWorkbook.GetNames 返回一个 IXLSNames 集合，其 Add(Name, RefersTo, Visible) 重载将名称写入 BIFF 名称表；各个条目作为 IXLSName 对象返回，带有 Name、RefersTo、解析后的 RefersToRange 和 Delete 方法；在 XLSX 方面，TXLSXWorkbook.DefinedNames 是一个具有 Add、FindByName 和 DeleteByName 的 TXLSXDefinedNames 集合

查找约定的分歧在移植期间而非编译时浮现；XLS 集合的默认 Item 属性接受 Variant，因此 Names[0] 和 Names['TaxRate'] 都可以对照它进行解析；XLSX 集合没有这样的默认属性，您需要调用 FindByName('TaxRate')，这在名称不存在时返回 nil；为一种接口编写的代码仅能凑巧在另一种接口上编译，而且失败往往表现为运行时的 nil 访问，而不是 IDE 中的红色波浪线

作用域是第一项决策，而不是稍后添加的标志

已定义名称要么是工作簿作用域的（对每个工作表上的公式可见），要么是工作表作用域的（仅对拥有它的工作表上的公式可见）；在 XLSX API 中，这一区别仅是一个可选参数：DefinedNames.Add(AName, AFormula) 创建工作簿级别的名称，而 Add(AName, AFormula, ASheetIndex) 将其绑定到某一个工作表上；读回它时，TXLSXDefinedName.SheetIndex 在为工作簿作用域时返回 -1，否则返回基于 0 的工作表索引

作用域同时也是您的冲突策略，这就是为什么在写入第一个名称之前先确定它的原因；Excel 允许在每个工作表上有一个本地作用域的 Total，加上一个工作簿级别的 Total，并且给定工作表上的公式会优先解析本地那个；生成的工作簿应该刻意地依靠这一点；由多个工作表消耗的业务假设（例如税率、外汇汇率和报告期）属于工作簿作用域；仅由一个工作表的公式引用的辅助范围在工作表作用域下更安全，在这一作用域下，它们不会被任何内容遮蔽，也不会遮蔽任何其他内容

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... fill Data!A2:D100 with detail rows ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // workbook scope, a constant
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // workbook scope, a range
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // scoped to sheet index 1 only

    // XLSX formulas take no leading '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

已定义名称不一定非要指向范围；上面的 TaxRate 指向纯常量 0.08，这是发布业务假设最干净的方式；它在 Excel 的名称管理器中仅出现一次，每个公式都符号化地引用它，下个季度的税率更改只是对生成器进行的一行编辑，而不是在 14 个组装好的公式字符串中进行搜索

仅属于一侧的等号

公式输入通道是移植后的代码最常崩溃的地方，因为这两个接口在等号的使用上存在分歧；XLS 单元格通过带前导 = 的 Value 接收公式，XLSX 单元格有一个专用的 Formula 属性，该属性接受不带前缀的表达式；如果将 '=SUM(A1:A10)' 写入 TXLSXCell.Formula，等号会变成存储的表达式文本的一部分而不是标记，文件就不会像相同字符串在 XLS 侧表现得那样工作了

var
  Book: IXLSWorkbook;   // interface-counted: do not Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // assume a sheet named 'Data' already holds the detail rows
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = hidden from the Name Manager

  // XLS formulas go through Value, with the '=' prefix
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

这段代码展示了 XLS 侧的另外两个奇特之处：工作表集合基于 1，因此 Sheets[1] 是第一个工作表（与基于 0 的 XLSX Sheets[0] 相反）；此外，第三个 Add 参数创建了一个隐藏的名称 —— 存在于文件中并可供公式使用，但在 Excel 的名称管理器中不可见；隐藏名称是生成器内部管道的合适工具，终端用户绝不应该意外编辑或删除它们

跨工作表引用，以及行移动时会发生什么

两个公式引擎都接受标准的跨工作表语法；普通的工作表名称直接限定为 Data!A1，带空格或标点符号的名称需要单引号（例如 'Sheet With Space'!A1）；在名称的 RefersTo 文本内，几乎每次都需要使用绝对引用（例如 Data!$A$2:$D$100）；已定义名称内部的相对引用是相对于使用它的单元格进行解析的，这是 Excel 特意设计的功能，但如果意外触发，也会成为混淆的源头

结构编辑是跨工作表记账体现价值的地方，而 XLSX 侧在这些编辑中保持名称一致；InsertRows 和 DeleteRows 会平移已定义名称的范围，以及单元格、合并、超链接和图表锚点，因此在生成器在 Data 上方打开一个缺口后，指向 Data!$A$2:$D$100 的名称仍覆盖数据块；关于公式有一个记录在案的注意事项：行插入仅调整以正在编辑的工作表为目标的引用；当行进入 Data 时，引用 Data!D2:D100 的 Summary 公式会被重写，这通常是您想要的；验证它而不是假设它，因为引擎会很容易地告诉您：

// the calculation engine resolves names and cross-sheet references in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate 在不保存任何内容的情况下，对照当前工作簿状态评估任意表达式，这使其成为生成器测试的自然断言基元；在 Pascal 中根据源数据计算预期的聚合，评估工作簿自身的公式，并对比两者；公式引擎文章涵盖了引擎评估的内容、时间，以及如何使用自定义函数对其进行扩展

属性层拥有的 _xlnm 名称

在低级检查器中打开生成文件的名称表，您会发现一些您从未写过的条目：_xlnm.Print_Area、_xlnm.Print_Titles 及其相关内容；这些是 OOXML（ECMA-376 / ISO 29500）存储打印区域和重复标题行的方式（作为带有保留标识符的已定义名称）；HotXLS 通过专用的工作表属性管理它们，因此设置 PrintArea 或 PrintTitleRows 会为您写入对应的 _xlnm.* 条目

陷阱在于手动伸入该保留命名空间；在设置 PrintArea 属性的同时通过 DefinedNames.Add 添加 _xlnm.Print_Area 条目，工作簿就会为一个保留名称携带两个冲突的定义，Excel 解决这种状态的方式是任何产品都不应该依赖的；将每个以 _xlnm. 开头的标识符都视为属于属性层；要检查打印设置，读取属性，而不是名称表；保护和页面设置文章在上下文中介绍了打印区域属性

在提交设计前值得了解的两个边界

已定义名称无法通过便捷的 XLS 到 XLSX 桥接进行传递；SaveXLSWorkbookAsXLSX 会复制单元格内容和基本格式，且名称表并不在其记录的复制列表中，因此依赖其名称的工作簿在过桥后会丢失它们；在转换后通过 DefinedNames.Add 重新创建名称；这一步并没有听起来那么繁琐，因为它给您提供了一个时间来规范它们的作用域，而不是直接沿用 XLS 文件恰好拥有的任何作用域

另一个边界是公式字符串与工作表名称之间的漂移；在交互式重命名期间，Excel 会重写公式和名称内的工作表引用，因此用户在 Excel 中编辑的文件可以保持自身的一致性；暴露的是生成器侧：当 Pascal 代码从工作表名称字面量组装公式字符串时，在一个地方重命名工作表而忘记在另一个地方修改，会产生对不存在的工作表的引用；将工作表名称保存在单个 Delphi 常量中，并将其同时提供给 Sheets.Add 和您的公式组装，这样两者就绝不会不一致；这与主张命名报表的输出单元格而不是硬编码地址是相同的直觉：当设计师在总计单元格上方插入三行后，已命名总计单元格的模板仍能继续工作，而写入字面量 B17 的生成器则会静默地将数字存放在错误的地方；模板报表生成文章正好基于该模式构建

这两种格式的完整已定义名称 API 以及公式引擎参考随 HotXLS Component 一起发售