文件把 =SUM(A1:B1*{10,100}) 这类公式当普通公式存储时,Excel 365 会在公式里插 @ 并显示 #VALUE!,因为此时 Excel 对每个运算符操作数应用老式隐式交集。HotXLS Delphi Component 按 Excel 365 的方式存储这些数组运算符公式:在 XLSX 里存为单格动态数组公式,在 XLS 里存为单格数组公式
这个症状能活过代码评审。Delphi 服务写出一份工作簿,它的公式引擎在普通公式单元里为 =SUM(A1:B1*{10,100}) 算好缓存 210,客户拿 Excel 16 一开,编辑栏里是 =SUM(@A1:B1*@{10,100}),单元格里是 #VALUE!。文件里没有任何畸形的东西。缺的是告诉 Excel“这条公式按动态数组规则书写”的元数据;没有它,Excel 退回动态数组之前的求值模型
Excel 365 为什么给算得正确的公式加 @?
Excel 365 之所以加 @,是因为按定义,没有动态数组标记的公式就是旧式公式,而旧式公式在运算符期待单个值的每一处,都会把多单元格区域收缩成一个单元格。这个收缩就是隐式交集:Excel 取区域里与公式同行(竖直区域)或同列(水平区域)的那个单元格,找不到就得到 #VALUE!。Excel 365 对旧式公式保留这套语义,并把 @ 显示出来,让收缩可见
把 =SUM(A1:B1*{10,100}) 放进 E5,老式读法立刻现形。A1:B1 是横向区域,公式在 E 列,区域里没有 E 列的单元格,于是 @A1:B1 是 #VALUE!,整个 SUM 继承它。按动态数组规则,同一段文本逐元素相乘,1 × 10 + 2 × 100,得 210。HotXLS 公式引擎按动态数组方式求值,文件格式也必须这么说。A1:B2 放 1、2、3、4 时,下面是探针公式与 Excel 16 的显示:
| 公式 | HotXLS 结果 | Excel 16,按普通公式存储 | HotXLS 的存储方式 |
|---|---|---|---|
=SUM(A1:B1*{10,100}) | 210 | #VALUE! | 动态数组,Excel 显示 210 |
=SUM((A1:B2>2)*1) | 2 | 隐式交集,值错或报错 | 动态数组,Excel 显示 2 |
=SUMPRODUCT((A1:B2>2)*1) | 2 | 隐式交集,值错或报错 | 动态数组,Excel 显示 2 |
=MAX(A1:B2-1) | 3 | 隐式交集,值错或报错 | 动态数组,Excel 显示 3 |
=SUM(A1:B2) | 10 | 10 | 普通公式,原样保留 |
最后一行跟前四行一样重要。SUM(A1:B2) 把区域直接传给一个接受引用的函数参数,没有任何运算符见到多单元格区域,交集也就无从发生。Excel 365 自己就把这个公式存成普通公式,HotXLS 同样如此
HotXLS 如何在 XLSX 与 XLS 里存储数组运算符公式
HotXLS 在 XLSX 里把数组运算符公式写成单单元格动态数组:<c> 元素带上 cm="1",公式是 <f t="array" ref="E5">,包里多出 xl/metadata.xml,其中 XLDAPR 元数据类型的扩展块持有 dynamicArrayProperties fDynamic="1"。cm 属性是从 1 开始的索引,指向该部件的 cellMetadata 块,背后的 XLDAPR 记录才是告诉 Excel「按动态数组规则求值」的那道凭证。你在 Excel 16 里手敲同一公式再保存,写出的就是这套结构——当初的目标布局正是这么定下来的
XLS 里没有元数据部件,所以 HotXLS 用上 BIFF8 为数组求值准备的唯一构造:单单元格数组公式。单元格拿到一条 FORMULA 记录,其 token 流是单个指向自身的 PtgExp,后跟一条 ARRAY 记录($0221),在单单元格范围上装着真正解析后的公式。Excel 365 往 XLS 写动态数组公式用的也是这条路,老版本 Excel 读到文件时看到的就是经典的 Ctrl+Shift+Enter 数组公式
不需要任何新 API。两个引擎里,标记都发生在你通过普通单元格 API 赋公式的那一刻。XLSX 这边就是 TXLSXCell.Formula:
uses
SysUtils, Variants, lxCalc, lxHandle, lxHandleX; // lxOk 声明在 lxCalc 里
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Data');
Sheet.Cells[1, 1].Value := 1;
Sheet.Cells[1, 2].Value := 2;
Sheet.Cells[2, 1].Value := 3;
Sheet.Cells[2, 2].Value := 4;
// 运算符作用在区域或内联数组上:按动态数组存储
Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
// 区域直接传给函数:保持普通 <f>
Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';
if Book.Recalculate = lxOk then
Writeln(VarToStr(Sheet.Cells[5, 5].Value)); // 210
// 数组根单元格回读的文本不带前导 '='
Writeln(Sheet.Cells[5, 5].Formula); // SUM(A1:B1*{10,100})
Book.SaveAs('probe.xlsx'); // E5 和 E6 得到 cm="1" + t="array"
finally
Book.Free;
end;
end;
转换之后,TXLSXCell.Formula 回读的文本不带 =,与 TXLSXRange.SetDynamicArrayFormula 存储的形式一致,所以赋值后再比较公式字符串的代码,应该先把前导 = 归一化
经典引擎通过单单元格上的 IXLSRange.Formula 遵循同一条规则。赋公式的动作在内部被改道到单单元格数组路径,所以保存出的 XLS 里是 FORMULA 加 ARRAY 那一对:
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['A1', 'A1'].Value := 1;
Sh.Range['B1', 'B1'].Value := 2;
Sh.Range['A2', 'A2'].Value := 3;
Sh.Range['B2', 'B2'].Value := 4;
Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})'; // ARRAY 记录
Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)'; // ARRAY 记录
Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)'; // 普通 FORMULA
Writeln(VarToStr(Sh.Range['E5', 'E5'].Value)); // 210
Writeln(VarToStr(Sh.Range['E6', 'E6'].Value)); // 3
Wb.SaveAs('probe.xls');
end;
如果你要落的是多单元格结果而不是标量聚合,显式 API 仍是对的工具:预先量好尺寸的矩形用 SetArrayFormula,见HotXLS 的动态数组溢出公式一文;想在自己定尺寸的区域上要 XLSX 动态数组标记就用 TXLSXRange.SetDynamicArrayFormula。本文的自动路径只覆盖敲进单个单元格的公式
HotXLS 把哪些公式标记成动态数组?
只有当某个运算符的子树里长出能产生数组的操作数时,HotXLS 才做标记。检查在编译后的语法树上进行,一个操作数在三种情况下产生数组:它是多单元格区域、是内联数组常量、或是自身带有这类操作数的另一个运算符表达式。括号是透明的。算数的运算符包括四则与乘方(+ - * / ^)、拼接(&)、六种比较、一元正负号和百分号:
A1:B1*{10,100}、(A1:B2>2)*1、--(B1:B2>0)和A1:B2-1会被标记,不管出现在公式哪个位置,SUMPRODUCT 里也算SUM(A1:B2)和SUMPRODUCT(A1:A2,{1;10})不标记,因为区域和数组直接进了函数实参,没有运算符碰它们A1*2或SUM(A1,B1)*2不标记:对这道检查来说,单单元格引用和函数结果都是标量
三条边界是刻意划的。第一,只在公式经 API 敲入时才标记,即 XLSX 引擎的 TXLSXCell.Formula,经典引擎的单单元格 Formula 或 Value 赋值。从文件加载来的公式原样写回,因为别的生成器产出的旧式公式可能刻意依赖隐式交集。第二,文本里既没有 : 也没有 { 的,不做第二次编译直接跳过。第三,本会溢出的公式,比如单独一句 =A1:B1*2,会标记成锚定在你放置位置上的单单元格动态数组。HotXLS 不替它溢出,Excel 下次重算时会把结果扩展到相邻单元格
这条操作数规则与HotXLS 里定义名称的隐式交集一文讲的实参类别规则是姊妹篇。那篇说的是声明为 value 类别的函数参数;这篇说的是运算符——旧式模型里运算符永远只要值
计算引擎如何对上 Excel 365 的值
存储标记要兑现,前提是公式引擎先就返回 Excel 365 的值——两个引擎都是。SUMPRODUCT 是最显眼的案例:只接受两个以上普通区域的实现,对 SUMPRODUCT((B1:B2>0)*1)、SUMPRODUCT(--(B1:B2>0)) 乃至单参数的 SUMPRODUCT(B1:B2) 都会返回 #N/A。HotXLS 按 Excel 的规则逐元素求值表达式参数:
- 每个实参形状必须完全一致,标量按 1 × 1 计,否则结果是
#VALUE! - 任一实参里的错误值直接作为结果返回
- 文本和逻辑元素按 0 计,所以仍需
(B1:B2>0)*1或--把 TRUE 变成 1 - 实参全是普通区域时保留原先的流式循环,大区域不会被物化成数组
SUM 家族(SUM、COUNT、AVERAGE、MIN、MAX、COUNTA)在参数是区域上的运算符表达式时用同一个逐元素求值器,所以 =SUM((B1:B2>0)*1) 数的是两行而不是只看第一个单元格。空格交集运算符返回两个引用的公共矩形,不相交时得 #NULL!,所以 =SUM(A1:B2 B1:B2) 是 6 而不是 2,结果还能喂给 ROWS、INDEX 这类引用参数。解析器接受 {1,2;3,4} 这样的内联数组常量(逗号分列、分号分行)和 (A1:B2,D4) 这样的引用并集。逐元素比较还让空白元素取对侧的类型——对逻辑值即 FALSE,与HotXLS 里的比较链与空白单元格所述的标量规则一致
var
V: Variant;
begin
// Book 是第一个示例里的 TXLSXWorkbook;
// 其活动表的 A1:B2 为 1, 2, 3, 4
V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)'); // 2
V := Book.Calculate('=SUMPRODUCT(A1:B2)'); // 10,单参数
V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})'); // 31 = 1*1 + 3*10
V := Book.Calculate('=SUM(A1:B2 B1:B2)'); // 6,公共区域 B1:B2
V := Book.Calculate('=SUM((A1:B2,B1:B2))'); // 16,重叠计了两次
V := Book.Calculate('=ROWS({1,2,3;4,5,6})'); // 2
V := Book.Calculate('=TRUE*1'); // 1,TRUE 按 1 计
end;
TXLSXWorkbook.Calculate 对活动表求值公式字符串而不存储它,是快速检验引擎行为的办法。关于 @ 本身要当心一点:HotXLS 接受两个引用之间的 @ 作为二元交集,并按真正的交集语义求值那种形式。而在 Excel 365 里,@ 是一元隐式交集前缀。不要把 @ 写进公式文本还指望它是 Excel 的意思;交集用空格,动态数组语义交给上面的存储规则处理
Excel 打开动态数组输出时要查的三个细节
要让 Excel 接受动态数组标记,归结为三个细节——自读写往返测试一个也抓不到,因为库读自己的输出怎么都是对的。每个细节只在你用 Excel 16 打开输出、一次替换一个变量时才现形:
- 扩展 GUID 必须全小写。
xl/metadata.xml里的ext uri必须恰好是{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}。大小写混着拼,Excel 16 拒绝打开整个包,而不只是那个单元格 - 数组根文本不带前导
=。XLSX 写出器把数组根的存储文本原样写进<f>。转换后的单元格若留着=,元素就成了<f t="array" ref="E5">=SUM(...)</f>,Excel 打开时同样拒收。HotXLS 在转换时剥掉它,所以TXLSXCell.Formula读回时没有它 - Delphi 里
Double(True)是 -1。Variant 转换遵循 COM 约定——TRUE 是全位为 1——而且VarIsNumeric(True)也返回 True。想当然的转换会让=TRUE*1返回 -1,并把逻辑数组元素归类成数字,(B1:B2>0)=TRUE这类比较随之出错。HotXLS 在标量算术、数组算术与数组元素归类里都先测varBoolean再把 Variant 当数字,TRUE 按 1 计
BIFF8 操作数类别:写给格式实现者的字节级细节
BIFF8 里每个操作数 token 都在 token 字节自身里带着操作数类别,而 Excel 信任这个类别胜过公式的结构。[MS-XLS] 把类别定义为 token 第 5、6 位上的两位 PtgDataType 字段:1 是引用,2 是值,3 是数组。低五位命名 token,于是同一个区域引用有三种拼法:
| Token | 引用类别 | 值类别 | 数组类别 |
|---|---|---|---|
PtgRef | $24 | $44 | $64 |
PtgArea | $25 | $45 | $65 |
PtgArray | $20 | $40 | $60 |
BIFF8 写出器有三个容易做错的地方,每一处都会在 Excel 里产出独特的症状,而在写出器自带的阅读器里读回一切正常:
- 引用类的数组常量。按上下文选类的编码器看到 SUM 或 ROWS 参数是引用类,就把
=SUM({1,2})的PtgArray写成$20。Excel 把整条公式显示为=#N/A。数组常量永远不可能是引用,所以凡上下文要引用类的地方,HotXLS 都写数组类$60 PtgIsect与PtgUnion的值类操作数。给二元操作符配值类操作数,对*是对的,对引用操作符是错的。PtgIsect($0F)前面放上$45区域,Excel 把=SUM(A1:B2 B1:B2)读成=SUM(@A1:B2 @B1:B2),返回#VALUE!。HotXLS 把PtgIsect与PtgUnion($10)的操作数写成引用类$25- ARRAY 记录里的值类操作数。操作数为值类时,即使在数组公式内部 Excel 也应用隐式交集。那里放
$45,=SUM(A1:B1*{10,100})的单格数组公式在 Excel 里求值成 10。因此 HotXLS 在 ARRAY 记录的记号流里,把每个值类的引用与数组常量提升为数组类$65与$60——Excel 自己就是这么写的
忽略类别位的读取器对这三种都能愉快往返,所以如果你自己维护 BIFF8 写出器,请把每个操作数 token 的类别位与 Excel 保存的同一公式文件比对,别只比 token 编号
速查
- 普通未标记公式里的运算符收到多格区域或内联数组时,Excel 365 显示
@ - HotXLS 把这类公式存为 XLSX 单格动态数组(
cm="1"、t="array"、XLDAPR元数据)和 XLS 单格数组公式(FORMULA 带PtgExp加 ARRAY$0221) - 只有运算符操作数才算数;直接传给函数参数的区域仍是普通公式
- 只有经
TXLSXCell.Formula或经典引擎单格Formula/Value输入的公式才被标记;从文件加载的公式原样保留 - 转换后的根单元格读回时没有前导
= - 动态数组
ext uriGUID 必须小写,否则 Excel 拒收整个包 - Delphi 里
Double(True)是 -1;数值转换前先测varBoolean - BIFF8:数组常量绝不写引用类,
PtgIsect/PtgUnion操作数用引用类,ARRAY 记录操作数用数组类
HotXLS 在 Delphi 和 C++Builder 上原生读写并计算 XLS 与 XLSX 工作簿,存储数组运算符公式的方式让 Excel 365 打开时看到的就是 HotXLS 算出的那几个值。版本、文档与试用下载见 HotXLS Delphi 电子表格组件