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

技术文章

Excel 为什么无视缓存中的 #SPILL! 与 #CALC! 错误

HotXLS Delphi Component

Excel 只有在工作簿带齐完整的现代错误契约时才信任缓存的 #SPILL! 或 #CALC!：Rich Data 元数据的 XLRICHVALUE 扩展 URI 必须是小写 GUID、fileVersion 为 7、calcId 至少 191029，外加 microsoft.com:RD 计算特性。原生 Delphi XLSX 组件 HotXLS 在保存的单元格含有这类错误时，四样全部自动带上

缺了任何一块，失败都很少大声。有时 Excel 16 干脆拒绝打开文件；更常见的是正常打开，却在你的计算引擎明明产出 #SPILL! 的地方显示 #VALUE!——看着像计算 bug，让人在一完全正确的公式代码里翻找。按下 F9，正确的错误又出现了，于是问题往往以“在我机器上没问题”结案，直到某位客户在自动计算关闭的状态下打开报表。本文讲清每一块各起什么作用、那个看着绝不可能有影响的 GUID 大小写陷阱，以及以完全相同方式安静失效的数组文本缓存规则

Excel 为什么显示 #VALUE! 而不是缓存的 #SPILL!

Excel 显示 #VALUE!，因为单元格里存的字面上就是它。ECMA-376 SpreadsheetML 的旧式错误类型有一张固定的错误字面量清单，#SPILL! 和 #CALC! 不在清单上。带 t="e" 的单元格只能说 #NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM! 或 #N/A，所以较新的 Excel 版本把 #VALUE! 写成向后兼容的兜底，再把真正的错误类型挂在一个值元数据索引上

这个索引即 vm 属性，指向 xl/metadata.xml，后者指向描述真实错误的 Rich Data 值。旧读取器看到 #VALUE! 就走了；当前的 Excel 会顺着链子走并显示现代错误——但前提是它认定这条链可信。一个溢出被阻的公式在磁盘上长这样：

<!-- sheet1.xml：旧式字面量加一个值元数据指针 -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml：errorType 8 是 #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS 为 #SPILL! 写错误类型 8 并带列偏移和行偏移，为 #CALC! 写错误类型 13 且不带偏移。旧式 <v> 在两种情况下都刻意保持 #VALUE!，因为那正是无视 Rich Data 的读取器该看到的值

Excel 16 真正检查的小写 GUID

XLRICHVALUE future-metadata 块必须逐字符使用小写的扩展 URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623}。把十六进制字母换成大写，本地 Excel 16 构建就拒绝正常打开工作簿——尽管每个 XML 部件都能解析、每条关系都能解析、每个计数和索引都对得上

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

碰过 COM 或注册表的人都知道 GUID 比较时大小写不敏感，而正是这个习惯在这里咬人。我们是用笨办法隔离出来的：先构造一个 Excel 拒收的最小包，然后一次只换一样东西。换内容类型，没好；加 rich value types 部件，没好；改命名空间前缀或重写 rich value 数据，还是没好。把那一个 uri 属性改成小写，好了——同一个包随即打开并同时显示 #CALC! 和 #SPILL!。请把扩展 URI 当成不透明的字符串字面量，从格式定义里原样拷贝，绝不要让它过任何自作主张挑大小写的 GUID 格式化器

对徒手写诊断包的人还有一条实用推论：改动请用 XML 解析器做，别对序列化后的 XML 做全局文本替换。针对一个 token 的无差别替换可能弄坏部件里别处的实体（比如 &gt;），然后你就在调试一个坏掉的探针，而不是原来的问题

Excel 读取缓存的现代错误还需要什么

元数据部件及其关系是必要条件，但不是充分条件。Rich Data 链完全合法的情况下，Excel 仍无视缓存的错误，直到工作簿部件声明自己出自一个现代计算引擎。只要保存的单元格里有 #SPILL! 或 #CALC!，HotXLS 就往 workbook.xml 写入三条声明：

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

fileVersion 元素是普通的 ECMA-376 Part 1 工作簿元数据，lastEdited / lowestEdited 为 7 告诉 Excel：最后保存这个文件的应用世代理解 Rich Data。calcPr 上的 calcId 标识计算引擎版本，191029 是我们实测 Excel 接受这些缓存的最小值。计算特性扩展里（位于 http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures 命名空间）的 microsoft.com:RD 条目声明：产出缓存值的引擎是按 Rich Data 语义求值的。只给子类型、只给 types 部件、或只换前缀和关系路径，结果都不变；三条声明必须同时在场

如果你要自己设置这些值，HotXLS 应用它们的方式有三个细节值得知道：

  • TXLSXWorkbook.CalcId 不会被覆盖。写入器只在输出里把发出的值抬到 191029，配置了更高 CalcId 的调用者保住自己的值，内存中的属性始终是你赋的那个
  • 随打开的文件进来的既有计算特性和无关工作簿扩展会被保留，而不是被替换
  • 写入器在添加之前会检查既有的 microsoft.com:RD 特性，所以反复保存同一工作簿绝不会堆叠重复声明
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 溢出三行；A2 被占用，所以结果是 #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // 更高的 CalcId 会保留；写入器只在更低时抬到 191029
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // 自动写出 rich error 部件
  finally
    Book.Free;
  end;
end;

Rich Data 部件从不需要你亲手构建。写入之前，TXLSXWorkbook 会扫描每个单元格找 #SPILL! 或 #CALC! 值，创建或扩展 xl/metadata.xml 和两个 rich value 部件，并把错误已变化或已清除的单元格上陈旧的值元数据链接摘掉。上面显式的 CalcId 赋值是可选的，写出来只是证明它会被尊重

数组文本缓存属于 t="str"，不属于共享字符串

同样的安静失效也出现在数组内的文本结果上。HotXLS 保存公式单元格的缓存文本，也保存 CSE 或动态数组范围里每个 follower 单元格的缓存文本，方式是 t="str" 加 <v> 里的字面字符串。把这些缓存写成带 t="s" 的共享字符串，Excel 读缓存文件时就会在数组单元格里显示 #VALUE!

<!-- 正确：数组 follower 上的字面文本缓存 -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- 错误：共享字符串索引，Excel 从缓存显示 #VALUE! -->
<c r="B3" t="s"><v>17</v></c>

这个错误容易犯，因为 t="s" 对普通值单元格确实是正确选择，而大多数写入器默认把所有字符串都送进共享字符串表。陷阱在验证环节。在 Excel 里重算，数组单元格显示正确文本——这证明了公式没问题，对你写下的缓存却什么也证明不了。如果你的用户以手动计算方式打开报表，或下游工具不计算直接读缓存值，共享字符串版本就是彻底坏的

公式存储语法：前导等号、逗号与 _xlws

同一个写入器里还落地了一项相关清理：进 OOXML 的公式按文件语法存储，而不是用户输入的语法。HotXLS 保留你赋给 Formula 的原文用于往返显示，但序列化的公式会去掉前导 =、在函数参数间用逗号，数组常量的行分隔符和带引号文本则原样保留。FILTER 和 SORT 以 Excel 对这类仅工作表函数期望的 _xlfn._xlws. 前缀存储，SEQUENCE 等其它未来函数则用普通的 _xlfn. 前缀

// 输入用分号分隔，存储为
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // 没有超过 1000 的行时得到 #CALC!，随 Rich Data 保存

写入器内部的顺序有讲究。XML 转义在语法规范化之后运行，绝不在之前——理由与诊断包需要解析器相同：对已转义文本做分隔符重写，会把比较操作符里的 &gt; 之类的实体弄坏

如何在验证缓存错误时不骗过自己

唯一可信的测试是：在计算模式已是手动的状态下用 Excel 打开文件，先读单元格，然后再重算。Excel 会从一个会话里打开的第一份工作簿继承计算模式，所以我们的验收工具先自建一个种子工作簿、把它切成手动，然后才打开被测文件。跳过种子步骤的探针只能证明文件能打开、能算对，它们标记为“缓存”的东西早就被重算过了

这个区别就是全文的缩影。现代错误缓存正确的标准，是 Excel 在任何计算发生之前就把它显示出来；上文从小写 GUID 到 t="str" 文本 follower 的每项要求，都是靠检查这个状态、而不是重算后的状态找到的。HotXLS 最初如何确定和接管溢出范围的背景，见Delphi 与 HotXLS 的动态数组溢出公式；如果你的症状是修复提示而不是错误值，Excel 为何修复一个合法 XLSX 的包级规则是更好的起点

FAQ

正确保存 #SPILL! 和 #CALC! 需要 HotXLS 做什么设置吗

不需要。HotXLS 在保存时检测 #SPILL! 和 #CALC! 值，自动写出 Rich Data 部件、fileVersion、最小 calcId 和 microsoft.com:RD 特性

单元格 XML 里为什么仍有 #VALUE!

旧式 SpreadsheetML 错误类型没有 #SPILL! 或 #CALC! 的字面量，所以 #VALUE! 是向后兼容的兜底。当前的 Excel 经 vm 值元数据链接读取真实错误

HotXLS 会把我自己设的 CalcId 降低吗

不会。只有存在现代错误时，写入文件里的 TXLSXWorkbook.CalcId 才会被抬到 191029，你配置的更高值原样保留

HotXLS 用自己的计算引擎从 Delphi 和 C++Builder 读写 XLSX、XLSM 和旧式 XLS，动态数组也包括在内。详情与试用下载见 HotXLS Delphi spreadsheet component 页面