在Delphi中用HotXLS处理SUBTOTAL与AGGREGATE的隐藏行

如果一个带隐藏行的工作簿上 SUBTOTAL(109, ...) 和 SUBTOTAL(9, ...) 返回同一个数，那两个里必有一个是错的。这是搭建电子表格计算引擎时最容易踩的陷阱之一，而 HotXLS——面向 Delphi 和 C++Builder 的原生 Excel 电子表格组件——让计算引擎能向工作表询问某一行是否隐藏，从而避开它

这个症状很少以「公式代码对不对」的面目出现，它以对不上的面目出现：服务器上的批处理算出一个总数，用户在 Excel 里带着筛选打开同一份文件，两个数差的那部分恰好是被筛掉行之和。没人会怀疑聚合函数，因为单元格里的公式字符串两处一模一样。差别完全在于求值器被允许看见什么

SUBTOTAL 109 为什么会把隐藏行算进去？

因为多数引擎设计里，求值公式的那一层根本无从得知行的可见性。直观的设计通过单一的取值回调获取单元格值，就像 lxCalc.pas 里的 TXLSGetValue 回调，它只对（表，行，列）三元组回答一个值，别无其他。可见性是存在行记录上的表现属性，如果调用链上没有别的东西一起传下来，引擎最终只剩一条聚合路径，而 SUBTOTAL 函数编号表的上下两半都解析到它。这可不是舍入误差级别的错误：隐藏行策略正是这张表后半部分存在的全部理由。以 ISO/IEC 29500-1 发布的 ECMA-376 Part 1 在公式函数定义（§18.17.7）里定义 SUBTOTAL，其第一个参数同时选择内层聚合和隐藏行策略。代码 1 到 11 映射到 AVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR 和 VARP，并把手工隐藏行上的值算进去；代码 101 到 111 选择同样十一个聚合，却把它们排除。用户敲 109 而不是 9，是在对隐藏数据表态；把这一区分压平的引擎，是在悄悄推翻用户的表态

这些函数编号在引擎内部对应到哪里

HotXLS 在 CalcSubtotalFunc 里解析 SUBTOTAL 第一个参数：把代码 101 到 111 归一化到与 1 到 11 相同的内层函数标识符，然后按聚合本身分发。家族里大多数走增量式 ExcelSum 累加器——处理 SUM、COUNT、COUNTA、MIN、MAX 和 AVERAGE 的那个。五个走不了：STDEV、VAR、STDEVP、VARP 和 PRODUCT 需要对数据做一次闭式遍历，所以 CalcSubtotalFunc 把内层代码 12、46、193、194 和 183 路由到独立的归约器 SubtotalReduceVariance。动手之前，值得先画清楚的就是这个分叉，因为两条独立的聚合路径意味着两个独立的单元格遍历循环，只在其中一个加隐藏行检查会产出最糟的结果：同一区域上 SUBTOTAL(109, ...) 尊重筛选，SUBTOTAL(107, ...) 却不。把 AGGREGATE 算进来，HotXLS 一共有六条，散布在区域求值、普通区域收集和三个独立归约器之间

为什么用一个临时字段，而不是新增六个函数签名

因为为了一个布尔值，把新参数穿过六个单元格遍历函数外加它们的一切调用方，是对热点代码路径的大改动。HotXLS 对替代方案有先例：求值器上的瞬态字段，与 GetRangeInfo 用来记录 3D 引用解析进外部工作簿的草稿字段同一精神。隐藏行门控是第二个：一个回调类型 TXLSIsRowHidden，声明为 (SheetIndex, row) 返回 Boolean 的函数，存进 FIsRowHidden，外加瞬态标志 FIgnoreHiddenRows。函数代码落在 101 到 111 时，标志在 CalcSubtotalFunc 入口武装；AGGREGATE 选中隐藏行排除的选项代码亦然，在 CalcAggregateFunc 入口武装。之后每个单元格遍历循环都查看它，置位时跳过一行，每处只花一行代码

// 六个单元格遍历循环中重复出现的形状
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... 把 Cells[rr, cc] 折入累加器 ...
    end;
  end;

置位这段代码里有两个细节，关系到整套方案的正确性。这个标志是保存后再恢复的，而不是简单地置位再清零，因为一个SUBTOTAL的参数本身可能包含一个表达式，这个表达式会在外层聚合还在调用栈上的时候运行自己的求值过程，而这次嵌套的求值绝不能继承或者破坏外层的这道闸门。而且恢复操作放在一个finally块里，因为CalcSubtotalFunc有好几处针对错误码的提前返回；如果一次错误返回之后这个标志还留在置位状态，就会悄无声息地污染重算顺序中接下来那个毫不相干的公式

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // 对 Item.Child[2] .. Item.Child[ChildCount] 做聚合
  // 下方的每个 Exit 路径都由 finally 覆盖
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Assigned 测试是求值器 API 保持兼容的关键。TXLSCalculator 构造函数接受第三个默认为 nil 的参数，用两参调用构建计算器的代码原样编译，得到的是把隐藏行算进去的行为

隐藏行这一位信息到底是从哪里来的？

从工作表来，而且有两个来源，因为 HotXLS 带两套工作簿引擎。传统 BIFF 侧从 TXLSRowInfoList.GetHidden 应答，经 TXLSWorkbook.GetRowHidden 到达。OOXML 侧从 TXLSXWorksheet.GetRowHidden 应答，经 TXLSXWorkbook.GetCalcRowHidden 到达。两者都在构造时接进计算器，与它们镜像的取值回调并列。行号约定正是这种桥接最容易出错的地方，值得说清楚。计算器交给回调的是 0 基行，与 TXLSGetValue 已在用的坐标一致。XLSX 工作表的行隐藏映射按键是 1 基行号，与 Excel 的行编号一致——公共属性 RowHidden[ARow] 暴露的也是它。所以 XLSX 桥在查找前加一，BIFF 桥不加，因为 TXLSRowInfoList 本来就是 0 基。两个桥都把超出有效范围的表下标或行当作可见处理，越界查询退化为「把隐藏行算进去」的答案，而不是丢数据

对于带筛选的工作簿，什么变了

这正是隐藏行策略最要紧的场景。在 HotXLS 里经 ApplyAutoFilter 应用 AutoFilter 会对列条件求值并隐藏所有不匹配的数据行——用户点筛选下拉时 Excel 做的正是这件事。这些行对用户不可见；因为计算引擎查询同一份行状态，服务器侧的 SUBTOTAL(109, ...) 报告的是筛选后的总数，而不是未筛选的

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // 隐藏不匹配的行

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // 单元格值与 Excel 在同一筛选下显示的一致，
    // VisibleRows 告诉你有多少行参与了计算

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

手工隐藏同理，因为 RowHidden[ARow] := True 写入的正是筛选所写的同一份状态。这个等价在 Excel 里是刻意的，在 HotXLS 里同样成立。有一件事值得写进随生成工作簿一起交付的文档：用代码 109 算出的总数是依赖视图的数字，收件人一清筛选它就变。报表必须给出无视读者视图操作的固定数字时，代码 9 才是正确选择，而且一直是。数据验证、AutoFilter 与表格一文把筛选、验证和表格放在一起讲。隐藏行不碰任何公式，所以它本身也不会弄脏依赖图——如果你依靠脏子图上的增量重算保持大工作簿的响应速度，这点值得知道

AGGREGATE 选项代码

AGGREGATE 就是带第二个策略参数的 SUBTOTAL，HotXLS 在 CalcAggregateFunc 里处理它。选项参数编码了互相独立的开关：范围内的嵌套 SUBTOTAL 和 AGGREGATE 调用是否跳过、隐藏行上的值是否跳过、错误值是被抑制还是传播。HotXLS 为选项代码 2、3、6、7 武装共享的隐藏行门控，为选项代码 4 到 7 抑制错误值。函数编号参数随后按 SUBTOTAL 的方式选择聚合，方差、标准差和乘积同样经各自的归约器路由。真实工作簿几乎总是把 SUBTOTAL 公式放在其他 SUBTOTAL 公式聚合的区域之外；如果你的生成器造出互相重叠的聚合区域，请把内层总计挪出外层区域，而不是指望选项代码替你去重

同一个分发器里的参数个数守卫

同一个分发器还带一个参数个数检查，设计理由与草稿字段相同：把检查放在能只写一次的地方。约 280 个内置函数体，若让每个自己对着 Item.ChildCount 核实参数个数，「参数过多」就没有一致的边界，而且陷阱很容易漏：像 =SIN(1,2) 这样的调用会进入一个只看首参数、无视多余参数的函数体，返回一个看似合理的数，而 Excel 返回 #VALUE!。HotXLS 把每个内置的声明参数个数存进函数注册表，暴露为 THashFunc.ArgsCnt（-1 表示 SUM、IF、CONCAT 这类变参函数），经 TXLSFormula.FuncArgsCntByPtg 属性转发，并在主分发器 GetValueItemFunc 顶部的一道门里检查

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] 是函数节点
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) 得 #VALUE!
    Exit;
  end;
end;

守卫只拒绝参数过多，对参数过少刻意不置一词。VLOOKUP、SUBSTITUTE 等一长串函数在 Excel 里省略末尾可选参数是合法的，对称的检查会为了抓错误的公式而弄坏正确的。未知标识符按变参报告、完全绕过这道门——用户自定义函数因此不受影响；注册自己的函数时，公式引擎与自定义函数指南所述的行为不变。参数过少的情形留给每个函数体自己处理，因为那些函数体各有自己的错误码语义

这里介绍的计算引擎、两套工作簿门面，以及为它们提供数据的AutoFilter与行可见性API，都是HotXLS Delphi电子表格组件的一部分，随附完整源码，适用于Delphi和C++Builder，运行时无需在机器上安装Excel