技术文章

HotXLS:Delphi 中的 Excel 结构化表引用

一张 Excel 表的 Amount 列存着 =[@Qty]*[@Price],保存的文件里却找不到 [@Qty] 的踪迹。解包工作簿,工作表 XML 里的 <f> 元素显示 Excel 实际写出的东西:冗长的表限定拼写 Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]],因为 ECMA-376 Part 1 §18.17 的公式语法里有 #This Row 关键字而没有 @。下方某个汇总单元格存着 SUM(Lines[Amount])。两串文本里一个单元格地址都没有,所以只会解析 A1 引用的引擎在弄清表坐落何处、哪一行是表头、公式自己住在哪一行之前,没有任何可计算的东西

最后这条信息是最麻烦的。列引用对每张表只需转一次矩形;当前行引用不行——它复制到每一行就指向另一个单元格

为什么结构化引用不只是一个带友好名字的区域?

定义名称把地址冻结,表引用不会。把名称 DataBlock 指向 Sheet1!$A$2:$D$100,它就一直是那个矩形,直到有东西改写它。Sales[Amount] 意思是“Sales 表的 Amount 列”,无论公式求值那一刻表的边界在哪里。给表加二十行,求和就覆盖它们;没有引用需要调整,因为公式从头到尾就没握过地址

HotXLS 在每次编译公式求值时解析这些引用。TXLSXWorkbook 在工作簿的表模型里按名称查表(TXLSXTable.Name 或 DisplayName,不区分大小写比较),把列对上 TXLSXTable.Columns 里存的表头文本,应用项目说明符选出行带,然后把诸如 'Sales'!$D$2:$D$25 这样的绝对区间交给编译器。重写后的文本只为那一次编译存在。单元格保留的公式与你写下的分毫不差,而且因为查找针对重算时刻的 TXLSXTable.Range 现状进行,表长大之前写的公式仍对着长大后的表求值

示意图:HotXLS 在 Delphi 中解析结构化引用 SalesTable[Amount]——按名称找到表、匹配 Amount 列头、应用 #Data 说明符得到具体区域
HotXLS 在解析时针对表模型编译 Table[Column] 引用,并转换为当前的具体区域,公式因此随表增长持续跟随

矩形语法:列、跨列与项目说明符

HotXLS 求值的每种整表形式都产出一个连续块。接受的写法有 [Col] 和它的双括号孪生 [[Col]]、裸项目说明符 [#Data]、[#All]、[#Headers] 和 [#Totals]、组合形式 [[#Data],[Col]]、项目说明符内的跨列如 [[#Data],[Col1]:[Col2]],以及普通跨列 [[Col1]:[Col2]]。跨列可以从右往左写;[[Q2]:[Q1]] 与 [[Q1]:[Q2]] 解析成相同的两列

每个说明符选中的行带由表上的两个标志决定。TXLSXTable.HasHeaderRow 决定 Range 的首行是否表头,TXLSXTable.TotalsRowShown 决定末行是否合计行。普通列引用和 [#Data] 覆盖两者之间的行。[#All] 拿整个 Range,表头合计都算。对没有表头行的表要 [#Headers]、对没有合计行的表要 [#Totals],或者取一个主体没有行的表的列,引用都会变成 #REF!。这与 Excel 对同样请求的显示一致,也让一个不可能的引用不至于悄悄变成求和为零的空区间

含方括号、井号、@ 符号或单引号的列名遵循 Excel 的转义规则:引用内部每个这类字符前面加一个单引号,所以叫 Unit [USD] 的表头写作 Sales[Unit '[USD']]。单列和当前行两种形式在匹配前解码这些转义。跨列和组合的 [#Data],[Col] 形式只对公式所在工作表上的表解析,所以需要在别的表上做跨列的汇总工作表,逐列用普通列引用更合适

HotXLS 在 Delphi 中接受的结构化引用语法地图:#Data、#Headers、#Totals 与 #All 各形式覆盖哪些表区域,以及带括号写法如何映射到相同的矩形
每种整表形式覆盖表的一个连续行带,带括号的变体与其朴素写法解析结果完全一致

下面的示例构建一张 24 行的销售表,并在表旁同一工作表的 F2 到 F6 放五个汇总公式。第 1 行是表头行,第 2 到 25 行是主体,没有合计行,所以 SalesTable[#All] 跨 25 行,而 SalesTable[Amount] 跨 24 行

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ...写入表头行和 24 行数据...

    Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
    Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

这个布局的预期值:F5 是 25,F6 是 4——每个表头单元格计数一次。F2 到 F4 取决于你写入的数据

[@Column] 怎么知道该读哪一行?

当前行引用的行取自正在求值的单元格。Recalculate 走到第 7 行一个含 Lines[@Qty] 的公式时,HotXLS 把它解析成第 7 行的 Qty 列;同一段公式文本在第 8 行读的就是第 8 行。Excel 写盘时的长拼写 Lines[[#This Row],[Qty]] 被当作同一个引用,含空格的表头对应的 Lines[@[Unit Price]] 也是

不带表名前缀的 [@Qty] 需要再多一个事实:它属于哪张表。HotXLS 按位置回答。求值单元格落在同一张表上某个表的主体行与列跨之内时,由那张表供列;在这样的表内,[@[Q1]:[Q2]] 变成横跨这些列的单行区间。其他任何位置,引用保持未解析。公式使用当前行引用的定义名称,其行取自引用该名称的单元格——与公式内联时的行为一致

把当前行公式留在表自己的主体行上,代码生成时带上表名限定。带限定的引用经得起移动,读起来与 Excel 存储的方式一致,而且表自身行上的公式正是 Excel 与文件的其他消费者对 @ 含义达成一致的唯一场景

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Cols.Add('Item');
    Cols.Add('Qty');
    Cols.Add('Price');
    Cols.Add('Amount');
    Sheet.Tables.Add('Lines', 'A1:D13', Cols);
    // ...在第 2..13 行写入表头以及品名、数量、单价...

    // 一份公式文本,十二个不同结果:每个单元格读自己的行
    for Row := 2 to 13 do
      Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';

    // 表下交叉核对:列合计对一个重新计算的乘积
    Sheet.Cells[15, 4].Formula :=
      'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';

    Book.Recalculate;
    // 预期:每个 Amount 单元格都等于 Qty * Price 时 D15 求值为 0
    Book.SaveAs('orders.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

D15 那个核对很便宜,值得在报表构建期间留在生成的文件里。拿错行的逐行公式会让两个合计不一致,差值以非零数字出现在一个人人能读的单元格里

流程图:HotXLS 在 Delphi 中针对求值单元格所在行解析当前行形式 [@Amount],同时公式文本原样往返,保存后 Excel 读到同一个引用
当前行引用逐单元格按公式所在行解析,保存的文件仍携带原始引用文本供 Excel 读取

表格形状发生变化时会怎样

增长是容易的案子,前提是表定义真的长了。往 Range 为 A1:D25 的表下面的第 26 行写值,不会让第 26 行变成表的一部分。自动扩展是 Excel 的编辑特性,靠有人在表旁边打字触发,经 HotXLS 写单元格不会点火。导入之后把 TXLSXTable.Range 设到新边界,或者用 TXLSXWorksheet.InsertRows 把行插进表内部——它会连同工作表上所有其他引用一起平移 Tables[i].Range。两种办法 [#Data] 都会变宽,而公式一条都不用改

收缩才是引用可能断的地方,HotXLS 跟随 Excel 的规则。覆盖表列的 DeleteCols 把每一条点名该列的结构化引用改写成 #REF!——单元格公式、条件格式、数据验证规则、图表系列、定义名称一律如此。删掉表头行的 DeleteRows 连表一起删除并以同样方式作废其引用;删掉合计行的则清除 TotalsRowShown,此后 [#Totals] 再没有行可指。普通 A1 引用经过同样的调整,见插入删除时的公式引用调整

TXLSXTables.DeleteByName 和经 TXLSXTable.Name 的重命名只作用于表模型。提到旧名称的公式保留原文,所以代码重命名表时,同一趟把这些公式也改名

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];  // 工作簿工作表从 1 开始

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table = nil then Exit;

    // ...把 20 行导入数据写进 A26:D45...
    Table.Range := 'A1:D45';  // 主体现在从第 2 行到第 45 行

    Book.Recalculate;  // F2 的 SUM(SalesTable[Amount]) 现在覆盖 44 行

    // 对表主体的 Recordset 式查找,行结果从 1 起,0 = 无
    Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
    while Row > 0 do
    begin
      Log(VarToStr(Sheet.Cells[Row, 4].Value));  // Log 是你自己的例程
      Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
    end;

    Book.SaveAs('sales.xlsx');
  finally
    Book.Free;
  end;
end;

FindFirst 和 FindNext 字符串不区分大小写比较、数值按值比较,且只扫主体行,所以表头文本 Region 永远不会被当作匹配返回

往返一致性

带着 SUM(SalesTable[Amount]) 加载的工作簿,保存时还是 SUM(SalesTable[Amount]);存着 Lines[[#This Row],[Qty]] 的单元格,原样带出去。解析出的地址从不顶替用户写下的东西。如果 HotXLS 存的是 SUM($D$2:$D$25),文件在 Excel 里打开时看着好好的,然后第一次有人扩展表,公式就不再覆盖新行——这是最难追溯回病因的那种坏法

表定义本身——包括无表头表和每表注释——经由数据验证、AutoFilter 与 Excel 表格覆盖的表模型往返。许多单元格共享一个公式模式时,XLSX 可以把它作为共享公式只存一次,这些单元格的展开见共享公式 si 展开。Excel 保存的计算列常常就是这种形态,所以表主体里的结构化引用在求值前要过两条路径:共享公式先展开成逐单元格文本,然后每个单元格解析自己的行

这里用到的表模型、Recalculate 引擎和工作表插入删除调用都是 HotXLS 的一部分,后者无需安装 Excel 即可从 Delphi 和 C++Builder 读写 XLS、XLSX 和 ODS。完整 API 参考在HotXLS Delphi 电子表格组件页面