要产出 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
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
只写 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()>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])>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=">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])>1)"
calcext:base-cell-address=".C1"/>
基单元格赋予相对引用意义。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 电子表格组件页