在 Delphi 中使用 HotXLS 处理动态数组溢出公式

HotXLS 在 Delphi 和 C++Builder 中原生评估 Excel 365 动态数组溢出公式：您向其提供一个单元格包含 XLOOKUP 或 FILTER 的工作簿，其公式引擎会计算结果集并将其溢出到锚定的输出矩形中，这与 Excel 生成的值相同。HotXLS Delphi Excel 组件将溢出公式建模为一个拥有计算的所有权的根单元格，加上一组从中读回的依赖单元格，这就是您在编写代码前需要了解的全部图景

该模型很重要，因为日常任务并不是抽象的。客户发送一个在 Excel 365 中构建的 .xlsx，其工作表充满了 =FILTER(...) 和 =XLOOKUP(...)，而您的服务必须在没有安装 Excel 的机器上以无头模式重现完全相同的数字，然后读取溢出的值或自行写入新的溢出区域。动态数组将计算约定从“一个公式，一个单元格”改变为“一个公式，一个单元格矩形”，HotXLS 引擎遵循这一约定，而不是用预先展开的网格来伪造它

如何在 Delphi 中评估 XLOOKUP 和 FILTER？

HotXLS 通过 lxCalc.pas 中的单个求值器入口点 CalcDynArrayFunc 分发每个动态数组函数，因此整个系列共享同一个溢出代码路径。支持的集合是 XLOOKUP 和 FILTER（最初的两个），加上 XMATCH、SORT、UNIQUE 和 SEQUENCE。每个函数都返回一个二维变体数组而不是标量：SEQUENCE(3;2;1;1) 产生一个 3 行 2 列的网格，SORT 按任何排序列的升序或降序重新排序行，UNIQUE 折叠重复行保留第一次出现，而 XMATCH 则报告与 XLOOKUP 相同精确、通配符、次小和次大匹配模式下值的基于 1 的位置。与统计分布函数每调用一次返回一个数字不同，这些函数传回的是一个完整的形状

要计算一个已经携带了这些公式的工作簿，请将其打开，调用一次 Recalculate，然后读取溢出落入的单元格。TXLSXWorkbook.Recalculate 遍历依赖图并按拓扑顺序评估每个脏公式，因此溢出根仅计算一次，其元素将直接写入成员单元格中

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // 评估每个公式，包括溢出
    Sheet := Book.Sheets[1];          // Sheets 是基于 1 的
    // 读回 XLOOKUP / FILTER 溢出到的数据块，例如 D2:F9
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

如果工作簿需要引擎未附带的函数，相同的求值器允许您通过自定义工作表函数钩子插入您自己的逻辑，并且自定义函数可以自由返回变体数组，从而像内置函数一样进行溢出

使用 SetArrayFormula 锚定溢出范围

HotXLS 从不猜测溢出应该有多大：您指定输出矩形，然后 TXLSRange.SetArrayFormula 将公式锚定到它。该方法编译一次公式，将语法树连同锚定范围一起存储在左上角的根单元格上，并为矩形中的每个其他单元格赋予一个弱引用根的轻量级公式。在重新计算时，根评估一次，其矩阵元素直接广播到每个成员单元格，这也是溢出根在渐进式重新计算依赖图中显示为单个节点的方式。当任务是将溢出区域写回文件而不是读取一个时，这就是要使用的路径

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // 将矩形大小调整为结果的大小：一个 3 行 2 列的网格
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // 沿着行方向用 1..6 填充 A1:B3
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

显式锚定的后果是值得明确说明的边界：HotXLS 不会像交互式 Excel 那样自动调整溢出的大小。Excel 会随着其输入的变化而增长和收缩溢出区域，并在目标单元格被占用时引发 #SPILL!。在 HotXLS 中，您传递给 SetArrayFormula 的矩形就是您得到的大小，因此您需要根据预期的结果来调整它的大小，如果函数产生的行数超过了您锚定的行数，多余的部分将无处落脚

操作符跨数组的逐元素广播

当操作的任何一侧是数组时，HotXLS 会逐个元素地应用算术操作符。辅助函数 ApplyArrayBinaryOp 涵盖了一维和二维变体数组上的 +、-、*、/ 和 ^：标量操作数将广播到每个元素，两个数组形状必须匹配，否则操作将被拒绝，并且除以零将作为错误传播而不会崩溃。标量算术路径不受影响，因此这仅在至少有一个操作数确实是数组时才会激活，例如溢出范围或矩阵函数结果。这意味着像 =D2:D13*1.1 这样锚定在列上的公式将依次乘以每个元素，而 =D2:D13*E2:E13 则将两个相同形状的列逐位置相乘

// 标量广播：锚定的每个单元格都得到 D(n) * 1.1
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// 两个相同形状的数组逐个元素地相乘
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

@ 操作符与传统 CSE 数组有什么区别？

HotXLS 将显式的 @ 读取为交叉引用操作符，而不是当前 Excel 中提取行的隐式交叉。在引擎中，A1:A3 @ B1:B3 返回两个范围交叉的单个单元格，并且该操作符的绑定比 ^ 更紧，比 % 更松。这里需要诚实地说明一点：隐式的空格分隔交叉形式尚未被分词处理，因为词法分析器仍会跳过空格，所以您只能通过显式的 @ 符号获得交叉效果

更深层次的区别在于传统的 CSE 数组公式与现代动态数组，而在 HotXLS 中，两者都流经相同的锚定机制。经典的数组公式是通过对预先大小调整的选区输入 Ctrl+Shift+Enter 得到的 {=...} 块，每个单元格共享同一个编译好的公式。动态数组是一个其结果决定其自身形状的单个公式。HotXLS 将它们统一在 SetArrayFormula 之下：无论公式是旧式矩阵表达式还是新的 SORT，您都需要指定矩形，由根拥有编译好的树，并且成员引用它。您永远不会得到的是 Excel 的自动波纹效果（即区域默默地重新增长），记住这一条可以防止这两个心智模型模糊不清

动态数组支持的终点

了解边缘情况可以节省一下午的调试时间。SORT 遵循每个键的方向，默认升序，当排序参数为负数时为降序；比较方向是一个值得拿您自己的键列与 Excel 进行测试的地方，因为反向比较只会静默地返回相反顺序的行，而不会失败。UNIQUE 保留每个不同行的第一次出现，引擎通过扫描前面的行来确认这一点，而不是信任原始的重复计数。TABLE 函数（模拟分析数据表）是被识别的，因此它可以在文件中进行往返，但它求值后为一个占位符，因为实际的替换结果是 Excel 已经存储的缓存值，HotXLS 不会重新运行模拟分析网格

LET 是要指出的最明确的局限。HotXLS 注册了 LET 并带有求值路径，但解析器会拒绝不是已经定义的名字的裸名称，因此 =LET(x;10;x) 在解析时就会失败，甚至还没有到达求值器。在解析器对其绑定的名字获得作用域感知之前，请将 LET 视为不受支持的。一个小小的移植性提示：这些示例中的公式字符串使用引擎的列表分隔符（分号），因此在组装公式文本时请匹配您的构建所期望的分隔符。这里涵盖的动态数组函数（从 XLOOKUP 和 FILTER 到 SORT、UNIQUE、SEQUENCE 和 XMATCH）包含在 HotXLS Delphi Excel Component 中，其公式参考中列出了完整的函数目录和每个函数支持的参数模式