技术文章

HotXLS ODS 互操作:Excel 读得到的公式与规则

要产出 Excel 和 LibreOffice 都能正确读取的 ODS 文件,HotXLS 把每条公式以 OpenFormula 语法写在显式声明的 of: 命名空间下,并把每个值或公式条件格式写两遍:一遍作为每个被覆盖单元格样式上的 <style:map>,这是 Excel 16 唯一会读的形式;一遍作为 calcext:conditional-formats 块,这是 LibreOffice 信任的形式。两个应用各自无视为对方准备的那一半,所以在其中一个里显示正常的文件,对另一个什么也证明不了

那后半句就是 ODS 互操作的全部教训:一个写出器自己读回来毫无问题的文件,仍然可能是两个目标应用都会读错的文件。接下来是每个应用实际接受什么、同时满足两者的标记怎么写,以及 HotXLS 从 Delphi 产出这些标记的 API 调用

ODS 文件为什么在一个应用里好好的、在另一个里就坏了?

ODS 文件在一个应用里正常、在另一个里出问题,是因为 Excel 和 LibreOffice 读的是同一个包的不同部分。OpenDocument 给公式和条件格式留了不止一种合法拼法,LibreOffice 又在上面叠了自己的扩展命名空间,每个消费者各取自己实现的那块子集。只对一个消费者做测试的写出器,会心安理得地收敛到被另一个无声误读的标记上

两个应用都不会报错。LibreOffice 在它无法解析的公式单元格显示 #VALUE!;Excel 打开工作簿时条件格式直接缺席,或公式被改写成求值为 #NAME? 或常量 0 的东西。只对自己输出做往返测试的写出器永远看不到这些。公式命名空间就是教科书式的案例:把 of: 前缀当纯文本匹配的阅读器能通过每一次自我往返,而 LibreOffice 在每个公式单元格显示 #VALUE!

特性Excel 16 读取LibreOffice 26.2 读取
写成 A:A 的整列误读成 A:(A)容忍
写成 [.A:.A] 的整列是是
<style:map> 里的条件格式是,它唯一读的形式calcext 在场时忽略
calcext:conditional-formats 里的条件格式忽略是,优先
带 calcext:operator 属性的 calcext 值规则忽略按 "equal to 0"(等于 0)导入
拼成 is-true-formula(...) 的 calcext 公式规则忽略按与 0 的值比较导入

ODS 里的 OpenFormula:先声明命名空间,再把语法写对

ODS 里的公式单元格只有当 table:formula 中的 of: 前缀能解析到一条已声明的 XML 命名空间时,LibreOffice 才读得懂。这个前缀不是装饰。of: 映射到 urn:oasis:names:tc:opendocument:xmlns:of:1.2,而 msoxl:——HotXLS 用于它的 OpenFormula 转换器不建模的公式的前缀——映射到 http://schemas.microsoft.com/office/excel/formula。用了任一前缀却没在 content.xml 根上声明,LibreOffice 就根本识别不了公式语法

<!-- Wrong: prefix used, never declared; LibreOffice shows #VALUE! -->
<office:document-content xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" ...>
  <table:table-cell table:formula="of:=SUM([.A1:.A3])" office:value-type="float" office:value="245"/>

<!-- Right: both formula namespaces declared on the root -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

命名空间声明了,表达式本身还得是合法的 OpenFormula(OpenDocument 1.3 Part 4 定义)。陷阱在那些 Excel 语法与 OpenFormula 看似相同实则不同的地方:

  • 单元格引用带方括号、点前缀,$ 标记是引用的一部分:[.$A$1] 和 [.A$1:.$B2] 都是合法 OpenFormula。v2.384.55 之前 HotXLS 写出器丢掉了每个 $,绝对引用回读成了相对引用,只有等谁复制了单元格才出错
  • 整列和整行必须用带方括号的形式 [.A:.A]、[.$A:.$B]、[.1:.1]、[.$1:.$2]。裸写 of:=SUM(A:A) LibreOffice 能容忍,但 Excel 16 会以 =SUM(A:(A)) 打开并报 #NAME?,还会把行引用和 $A:$B 变成常量 0。HotXLS 写带方括号的形式
  • 函数实参用 ; 分隔,不是 ,
  • 引用并集用 ~ 运算符:Excel 的 AREAS((A1,B2)) 变成 AREAS(([.A1]~[.B2]))。把那个逗号翻成 ;,一个并集实参就悄悄变成两个实参
  • 内联数组用 ; 分列、| 分行:Excel 的 {1,2;3,4} 变成 {1;2|3;4}。v2.384.55 之前 HotXLS 产出 {1;2;3;4},四个值的一行

逗号是最难的部分,因为一个 Excel 字符带三种含义。HotXLS 写出器在转换时维护一个括号栈:名字后面紧跟的 ( 开启函数调用,其逗号变 ;;其他 ( 是分组括号,其逗号变 ~;{} 里的逗号是数组列分隔符。有了这条和已声明的命名空间,LibreOffice 26.2 能正确求值数组和并集公式,包括对并集的 INDEX 和 AREAS

HotXLS 的括号栈示意图,把 Excel 逗号翻译成 OpenFormula:紧跟名字后的括号开启函数调用,其逗号变分号;其他括号是分组,其逗号变并集运算符波浪号;花括号内的逗号是数组列分隔符,如 A1 与 B2 的并集作用于 AREAS
逗号在 Excel 语法里身兼三职,只有随行的括号栈分得清;把并集逗号翻成分号,一个实参就无声变成两个
uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 120;
    Sheet.Cells[2, 1].Value := 80;
    Sheet.Cells[3, 1].Value := 45;
    Sheet.Cells[1, 2].Value := 0.2;

    // 写成 of:=SUM([.A:.A])
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // 写成 of:=[.A1]*[.$B$1];$ 标记得以保留
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

    Book.SaveAsODS('orders.ods');
  finally
    Book.Free;
  end;
end;

转换器不建模的公式回退到 msoxl:=,Excel 文本原样保留,这也是 msoxl 声明同样重要的原因。这条路径包括 Sheet2!A1 这样的表名限定引用和结构化表格引用。HotXLS 在导入时会把 msoxl: 公式读回来,所以它自己的往返能保住表达式,但其他应用怎么对待它们,就不在 the writer's control 的范围之内了。如果你的消费方依赖的公式带着 msoxl: 前缀出来,发布前先在两个应用里都打开看看

只写成 calcext 的条件格式,为什么 Excel 看不见(doesn't see)?

Excel 16 看不见 calcext 条件格式,因为它读 ODS 条件格式只认单元格样式的 <style:map> 子元素,对 calcext:conditional-formats 块整个无视。一锤定音的实验很短:拿一份 LibreOffice 存的 ODS,删掉 style:map 元素,Excel 读到零条规则;改删 calcext 块,Excel 照样全读到。LibreOffice 恰好反过来。calcext 是 LibreOffice's 扩展命名空间,不在 ODF 标准之内,calcext 规则在场时 LibreOffice 拿它而忽略 style:map

HotXLS 的 ODS 条件格式双通道示意图:每个值或公式规则既写成每个被覆盖单元格样式上的样式映射——Excel 16 唯一读的形式——又写成运算符放在值里的 calcext 条件格式块——LibreOffice 偏好的形式,两个应用各自无声无视另一种拼法
Excel 读样式映射而忽略 calcext,LibreOffice 偏好 calcext 而丢掉映射,两边都不报错;一次 HotXLS 调用写出两种拼法,文件才能在两边都验收通过

只写 calcext,一份高亮显示完全正常的 ODS 在 Excel 里打开后既没有值规则也没有公式规则,所以 HotXLS 两种形式都写。style:map 一侧使用 OpenDocument schema(ODF 1.3 Part 3)的条件语法,拼写与 Excel 16 和 LibreOffice 26.2 保存 ODS 时自己产出的完全一致:

<!-- Simplified. Carrier style for every cell of A1:A50 (two value rules) -->
<style:style style:name="ce3" style:family="table-cell">
  <style:map style:condition="cell-content()&gt;100"
             style:apply-style-name="CF_Hit"
             style:base-cell-address="Orders.A1"/>
  <style:map style:condition="cell-content-is-between(1,10)"
             style:apply-style-name="CF_Low"
             style:base-cell-address="Orders.A1"/>
</style:style>

<!-- Carrier style for every cell of C1:C50 (one formula rule) -->
<style:style style:name="ce4" style:family="table-cell">
  <style:map style:condition="is-true-formula(COUNTIF([.$C:.$C];[.C1])&gt;1)"
             style:apply-style-name="CF_Dup"
             style:base-cell-address="Orders.C1"/>
</style:style>

style:map 的麻烦在于它长在单元格样式上,所以是按单元格生效的。规则范围(rule's range)里的每个单元格都得带一个持有该映射的样式,空单元格也不例外,否则在 Excel 里这条规则就管不到那个单元格。HotXLS 复制每个单元格现有的格式样式(cell's existing formatting style)、追加映射,并按「原始样式 + 映射文本」这对组合去重承载样式,所以 500 个格式相同的单元格仍只产出一个样式。写出器还会把写出的表格扩展到规则的范围(the rule's range),规则内的空尾行会照常写出而不是被丢掉。styles.xml 也带一个空的 Default 单元格样式,style:apply-style-name="Default" 因此总有目标

LibreOffice 实际接受的 calcext 拼法

只有当比较运算符写进值文本里(如 >3 或 between(1,10)),LibreOffice 才接受 calcext 值规则;公式规则只有写成 formula-is(...) 才接受。这两点都容易搞错,因为错误的拼写会产出一条导入无错、随后却匹配错误单元格的规则

第一个陷阱是在 calcext:value 旁边放一个 calcext:operator 属性。读起来很自然,但它是杜撰的:LibreOffice 不认识那个属性,于是把每条值规则都导入成 "equal to 0"(等于 0)。第二个陷阱是把 style:map 的拼法 is-true-formula(...) 放进 calcext 条件,LibreOffice 同样会把它导入成与 0 的单元格值比较:

<!-- Wrong: LibreOffice ignores calcext:operator and imports "equal to 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Right: the operator travels inside the value -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:value="&gt;100" calcext:base-cell-address=".A1"/>
<calcext:condition calcext:apply-style-name="CF_Low"
                   calcext:value="between(1,10)" calcext:base-cell-address=".A1"/>

<!-- Right: formula rules use formula-is, relative refs anchored at the base cell -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>
HotXLS 对比 calcext 条件错误与正确拼写的示意图:calcext operator 属性是杜撰的、会把每条值规则导入成 equal to 0,运算符应写在值内(如 greater than 100 或 between 1 and 10),公式规则必须写 formula-is 并锚定在基单元格上,而非 style:map 拼法 is-true-formula
两种错误拼写都导入无错、随后匹配错误的单元格——读作等于 0 的规则什么都不会高亮;正确拼法是运算符进值、表达式用 formula-is

基单元格赋予相对引用意义。HotXLS 把每条规则锚定在其第一个区域左上角,为 C1 写的公式沿区域向下按 C2、C3 求值,与 Excel 自家(Excel's own)条件格式的做法完全一致。规则表达式走的与单元格公式同一个翻译器,数组、并集、整列和 $ 标记都按上文所述形式产出。Delphi 这边加规则的方式与 .xlsx 文件一模一样

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // 值规则:style:map cell-content()>100 加 calcext 值 ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR:浅红

  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
  Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);

  // Excel 语法的公式规则(逗号分隔,相对 C1):
  // style:map is-true-formula(...) 加 calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR:浅黄

  Opts := TODSExportOptions.Create;
  try
    Opts.Generator := 'OrderExport 3.1';
    Book.SaveAsODS('orders.ods', Opts);
  finally
    Opts.Free;
  end;
end;

把 Excel 与 LibreOffice 的 ODS 读回 Delphi

HotXLS 打开 ODS 文件时,读取器两种条件格式方言、两种 calcext 拼法都收,文件同时带两种形式时也不会把一条规则数两遍。真实文件来自三类写出器,各有各的习惯:

  • 新旧 calcext。带 calcext:operator 属性的文件——包括 HotXLS v2.384.69 之前写出的 ODS——仍走旧式解析。公式条件 formula-is(...) 和 is-true-formula(...) 两种拼法都认
  • Excel's style:map 拼法。Excel 给条件加 of: 前缀,如 of:cell-content-is-between(1,10),且值规则省略基单元格。两者都接受
  • 空单元格。Excel 和 LibreOffice 都把空单元格的映射放在列默认样式上而不是单元格上,所以读取器在收集映射之前先解析重复单元格的列默认样式
  • 区域重建。映射逐单元格收集,读完一张表后,读取器把条件与基单元格都相同的单元格重新合并成区域,先横向扫每一行、再纵向对齐列段,并丢弃已从 calcext 读到的规则

数字样式需要与规则同样的细心。Excel 16 和 LibreOffice 26.2 都把 General 格式写成 number:number 元素不带 number:decimal-places 的数字样式,典型如 <number:number number:min-integer-digits="1"/>。把缺失的小数位读成两位固定小数,Default 样式里的每个值都会带着 0.00 导入,1.5 显示成 1.50。HotXLS 把不带小数位、不带最小小数位、不带分组且整数位至多一位的朴素 number 元素映射到 General,而孤立的 General 让单元格不带任何数字格式。周围文本保留,如 General" kg";带分组的数字不映射到 General,因为 Excel 没有带分组的 General 格式

uses
  SysUtils, lxCondFormat, lxHandleX;

procedure DumpOdsRules(const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Rule: TXLSXConditionalFormat;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <= 0 then
      raise Exception.Create('cannot open ' + FileName);
    if Book.SourceFormat <> xlsxOpenDocumentSpreadsheet then
      raise Exception.Create('not an ODS package');

    Sheet := Book.Sheets[1]; // Sheets 下标从 1 起
    for I := 0 to Sheet.ConditionalFormats.Count - 1 do
    begin
      Rule := Sheet.ConditionalFormats[I];
      case Rule.Kind of
        cfkCellIs:
          Writeln(Rule.Range, ' value rule ', Ord(Rule.Op), ' ',
            Rule.Formula1, ' ', Rule.Formula2);
        cfkExpression:
          Writeln(Rule.Range, ' formula rule ', Rule.Formula1);
      end;
    end;

    // Excel's General 样式里的单元格读回来没有数字格式,
    // 而不是 '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

规则公式以带逗号分隔符的 Excel 语法返回,与你传给 AddCondFormatExpression 的形式相同,HotXLS 写出的规则回读就是同一个字符串。想了解 ODS 导入路径整体上留什么、丢什么,见 HotXLS ODS 打开与保存往返指南;Excel 与 LibreOffice 的重复行在导入时如何展开,见 ODS 重复行即行高 run

哪些条件格式类型能同时到达两个应用?

双标记方案覆盖值比较规则和公式规则——两个应用都读的两种类型。其余类型遵循简单的规则:

  • 色阶和数据条只写成 calcext 元素,LibreOffice 显示它们,Excel 不显示
  • 其他规则类型——图标集、文本规则、前 N 项、高于平均值、重复值规则——当前写出器没有 ODS 输出。文本规则通常可以改写成公式规则,比如在 B2:B200 上用 ISNUMBER(SEARCH("late",B2)),这样就能送达两个应用
  • 整列整行规则如 C:C 只铺在实际写出的表格区域上,而不是全部 1,048,576 行,所以 Excel 只在文件里存在的单元格上看到这些规则
  • 只有 style:map 的文件。文件没有 calcext 块时,HotXLS 从重建区域的左上角解释公式规则里的相对引用,而不是从声明的基单元格平移
  • LibreOffice 的重叠规则。一个单元格被多条规则覆盖时,LibreOffice 只把第一条 rule's map 写到它上面。这类文件只靠 style:map 读不全,这是读取器在两者都在场时偏好 calcext 的又一个理由

流程规则比上面任何一条都重要。本文的每个陷阱都能骗过「用同一个库写 ODS 再读回来」的往返,其中几个还能在错误的应用里通过人工检查:整列公式在 LibreOffice 好用而 Excel 显示 #NAME?;只写成 calcext 的公式规则在 LibreOffice 好用而 Excel 一条规则都看不到。如果 ODS 互操作是硬需求,验收测试就是在 Excel 和 LibreOffice 里各打开一遍、比较两者显示的内容。同样的纪律也适用于规则指向的样式;HotXLS 条件格式与样式一文讲了高亮样式在工作簿一侧如何定义

速查:两个应用都读的 ODS

  • 在 content.xml 根上声明 xmlns:of 和 xmlns:msoxl,否则 LibreOffice 对每条公式显示 #VALUE!(HotXLS 自 v2.384.56 起)
  • 引用写成 [.A1],每个 $ 都保留,整列整行写成 [.A:.A] 和 [.1:.1](自 v2.384.55 和 v2.384.65 起)
  • 实参用 ;,引用并集用 ~,内联数组行间用 |
  • 每个值或公式规则,为 Excel 写成每个被覆盖单元格样式(cell's style)上的 <style:map>,为 LibreOffice 写成 calcext 条件(自 v2.384.69 起)
  • calcext 里,运算符放进值(>3、between(1,10)),公式规则拼作带基单元格的 formula-is(...)(自 v2.384.66 和 v2.384.69 起)
  • 导入时会遇到不带 number:decimal-places 的 General 数字样式;HotXLS 自 v2.384.72 起按 General 读它
  • 每个新的导出配置都开两个应用验收——Excel 和 LibreOffice 各开一遍,绝不只开其一

HotXLS 是原生 Delphi 和 C++Builder 电子表格库,无需安装 Excel 或 LibreOffice 即可读写 XLS、XLSX 和 ODS;完整源码、功能清单与授权见 HotXLS Delphi 电子表格组件页