LlosLab 軟體開發部落格面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

HotXLS ODS 互通：Excel 讀得懂的公式與規則

HotXLS Delphi Component

要產出 Excel 與 LibreOffice 都讀得正確的 ODS 檔案，HotXLS 把每條公式以 OpenFormula 語法寫在已宣告的 of: 命名空間下，並把每條值或公式條件式格式寫兩次：一次寫成涵蓋範圍內每個儲存格樣式上的 <style:map>，這是 Excel 16 唯一讀的形式；一次寫成 calcext:conditional-formats 區塊，這是 LibreOffice 信任的形式。兩個應用程式各自無視給對方的那一半，所以在其中一個裡顯示正確的檔案，證明不了另一個也沒問題

最後那句話，是 v2.384.55 到 v2.384.72 之間六個 HotXLS 版本換來的教訓。每個修正的起點都是同一種檔案：HotXLS 寫的、自己讀回來完美無缺、兩個目標應用程式之一卻讀錯。以下整理的是兩邊實際接受什麼、同時滿足雙方的標記長什麼樣，以及從 Delphi 產出這些的 HotXLS API 呼叫

ODS 檔案為什麼在一個應用程式裡正常、在另一個裡壞掉？

因為 Excel 與 LibreOffice 讀的是同一個套件的不同部分。OpenDocument 給公式與條件式格式不止一種合法寫法，LibreOffice 又在上面加了自己的擴充命名空間，每個消費端各挑自己實作的那個子集。只對著一個消費端測試的寫入器，會順理成章收斂到另一個消費端默默讀錯的標記

兩個應用程式都不報錯。LibreOffice 把剖析不了的公式格顯示成 #VALUE!；Excel 開啟活頁簿，條件式格式就這麼憑空消失，或者公式被改寫成求值得到 #NAME? 或常數 0 的東西。只往返自家輸出的寫入器永遠看不到這些。HotXLS 在公式命名空間上正正踩中這個陷阱：它的讀取器把 of: 前綴當純文字比對，自家往返回回通過，LibreOffice 卻在每個公式格顯示 #VALUE!

功能Excel 16 讀取LibreOffice 26.2 讀取
寫成 A:A 的整欄誤讀成 A:(A)寬容接受
寫成 [.A:.A] 的整欄是是
<style:map> 裡的條件式格式是，唯一讀取的形式有 calcext 時予以忽略
calcext:conditional-formats 裡的條件式格式忽略是，優先採用
帶 calcext:operator 屬性的 calcext 值規則忽略匯入成「等於 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。v2.384.56 之前，content.xml 根元素用了兩個前綴卻沒宣告，LibreOffice 根本認不出公式文法

<!-- v2.384.56 之前：前綴用了卻沒宣告；LibreOffice 顯示 #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"/>

<!-- v2.384.56 起：兩個公式命名空間都在根元素宣告 -->
<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 第 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 自 v2.384.65 起寫帶括號形式
  • 函式引數用 ; 分隔，不是 ,
  • 參照聯集用 ~ 運算子：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 字元扛了三種語意。v2.384.55 起，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;

    // 自 v2.384.65 起寫成 of:=SUM([.A:.A])
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // 寫成 of:=[.A1]*[.$B$1]；$ 標記自 v2.384.55 起保留
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

轉譯器沒建模的公式，退回 msoxl:=、Excel 文字原樣保留，這也是 msoxl 宣告同樣要緊的原因。現行寫入器裡，這條路徑包括 Sheet2!A1 這類帶工作表限定的參照與結構化表格參照。HotXLS 匯入時會把 msoxl: 公式讀回來，自家往返運算式原封不動；別的應用程式怎麼對待它們，寫入器管不著。您的消費端依賴的公式若帶著 msoxl: 前綴出爐，出貨前請在兩個應用程式裡都開過一次

只寫成 calcext 的條件式格式，Excel 為什麼看不到？

Excel 16 看不到 calcext 條件式格式，因為它讀 ODS 條件式格式只認儲存格樣式的 <style:map> 子元素，calcext:conditional-formats 區塊整個無視。定案的實驗很短：拿一份 LibreOffice 存的 ODS，刪掉 style:map 元素，Excel 讀到零條規則；改刪 calcext 區塊，Excel 照樣全讀。LibreOffice 行為正好相反。calcext 是 LibreOffice 的擴充命名空間，不屬於 ODF 標準；calcext 規則在場時，LibreOffice 收下它、無視 style:map

HotXLS 的 ODS 條件式格式雙通道示意圖：每條值或公式規則既寫成涵蓋範圍內每個儲存格樣式上的樣式對應（style map），即 Excel 16 唯一讀的形式；也寫成運算子放在值裡面的 calcext 條件式格式區塊，即 LibreOffice 偏好的形式；兩個應用程式各自默默無視另一種寫法
Excel 讀樣式對應、無視 calcext，LibreOffice 偏好 calcext、丟掉樣式對應，兩邊都不報錯；一次 HotXLS 呼叫寫出兩種寫法，是檔案在兩邊都驗得過的唯一辦法

v2.384.69 之前 HotXLS 只寫 calcext，一份螢光標記完好無缺的 ODS，到 Excel 裡開啟，值規則與公式規則一條不剩。HotXLS 現在兩種形式都寫。style:map 這一半用 OpenDocument schema（ODF 1.3 第 3 部）的條件文法，寫法與 Excel 16、LibreOffice 26.2 存 ODS 時產出的一字不差：

<!-- 已簡化。A1:A50 每個儲存格的載體樣式（兩條值規則） -->
<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>

<!-- C1:C50 每個儲存格的載體樣式（一條公式規則） -->
<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 的麻煩在於它長在儲存格樣式上，所以是逐格的。規則範圍裡每個儲存格都得帶著持有對應的樣式，空儲存格也算，否則這條規則在 Excel 裡就是不涵蓋那格。HotXLS 複製每個儲存格現有的格式樣式、附上對應，並以「原始樣式加對應文字」這一組為鍵替載體樣式去重，所以 500 個格式相同的儲存格仍然只產出一個樣式。寫入器還會把寫出的表格延伸到規則範圍，規則內部的空尾列於是會被寫出、而不是丟掉。v2.384.69 起 styles.xml 也帶一個空的 Default 儲存格樣式，style:apply-style-name="Default" 永遠有目標可指

LibreOffice 實際接受的 calcext 寫法

LibreOffice 只在比較運算子是值文字一部分時（如 >3 或 between(1,10)）接受 calcext 值規則，公式規則則只在寫成 formula-is(...) 時接受。這兩點各讓 HotXLS 賠掉一個版本，因為錯的寫法產出的規則匯入時無錯可報，然後命中一堆錯的儲存格

第一個錯誤是在 calcext:value 旁邊放了 calcext:operator 屬性。讀起來順理成章，卻是發明的：LibreOffice 不認得那個屬性，每條值規則於是被匯入成「等於 0」。第二個是把 style:map 的寫法 is-true-formula(...) 塞進 calcext 條件，LibreOffice 同樣把它匯入成與 0 的儲存格值比較。公式修正隨 v2.384.66 出貨，值修正隨 v2.384.69：

<!-- 錯：LibreOffice 無視 calcext:operator，匯入成「等於 0」 -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- 對：運算子放進值裡面 -->
<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"/>

<!-- 對：公式規則用 formula-is，相對參照錨定在基準儲存格 -->
<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 屬性是發明的、會讓每條值規則匯入成等於 0，運算子該放進值裡面（如大於 100 或 between 1 與 10），公式規則必須寫 formula-is 並錨定基準儲存格，而非樣式對應的 is-true-formula 寫法
兩種錯誤寫法匯入時都無錯可報，然後命中錯的儲存格；讀作等於 0 的規則，您想標記的格一格都不亮。解方是運算子放進值裡、運算式用 formula-is

基準儲存格是相對參照意義的來源。HotXLS 把每條規則錨定在其第一個範圍區塊的左上角儲存格，為 C1 寫的公式沿著範圍往下依次以 C2、C3 求值，與 Excel 自己的條件式格式如出一轍。規則運算式走與儲存格公式同一個轉譯器，陣列、聯集、整欄與 $ 標記於是都以上述形式出爐。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 屬性的檔案，包括 v2.384.69 之前 HotXLS 寫的 ODS，照樣走舊式剖析。公式條件認得 formula-is(...) 或 is-true-formula(...) 兩種
  • Excel 的 style:map 寫法。Excel 給條件加 of: 前綴，如 of:cell-content-is-between(1,10)，值規則則省略基準儲存格。兩種都收
  • 空儲存格。Excel 與 LibreOffice 都把空儲存格的對應放在欄預設樣式、而非儲存格上，讀取器因此在收集對應之前，會先為重複儲存格解析欄預設樣式
  • 區域重建。對應是逐格收集的，工作表讀完後，讀取器把條件與基準儲存格相同的儲存格合併回範圍——先沿每列橫向合併、再往下游合欄跨距——並丟棄已從 calcext 讀過的規則

v2.384.72 的修正關於數字樣式，不關規則。Excel 16 與 LibreOffice 26.2 都把 General 格式寫成數字樣式，其 number:number 元素不帶 number:decimal-places，典型如 <number:number number:min-integer-digits="1"/>。HotXLS 讀取器過去把缺席的位數當成兩位固定小數，Default 樣式裡的每個值於是以 0.00 匯入，1.5 顯示成 1.50。v2.384.72 起，無小數位、無最小小數、無分組、整數位至多一位的純數字元素映射到 General；單獨一個 General 則讓儲存格完全不帶數字格式。周圍的文字保留，如 General" kg"；分組數字維持原本的映射，因為 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 General 樣式的儲存格，v2.384.72 起讀回來不帶數字格式
    // 而不是 '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

規則公式以 Excel 語法、逗號分隔讀回，與您傳給 AddCondFormatExpression 的形式相同，HotXLS 寫出的規則讀回來就是同一串字。ODS 匯入路徑整體保留與捨棄了什麼，見HotXLS ODS 開啟與存檔往返指南；Excel 與 LibreOffice 的重複列在匯入時怎麼展開，見以列高區段呈現的 ODS 重複列

HotXLS 的 ODS 條件式格式互通，極限在哪？

雙標記的做法只涵蓋值比較規則與公式規則，到此為止。其餘要嘛單邊有效，要嘛根本不寫：

  • 色階與資料橫條只寫成 calcext 元素，LibreOffice 看得到，Excel 看不到
  • 其他規則類型，如圖示集、文字規則、前 N 名、高於平均與重複值規則，現行寫入器沒有 ODS 輸出。文字規則通常可以改寫成公式規則，例如在 B2:B200 上用 ISNUMBER(SEARCH("late",B2))，兩個應用程式就都收得到
  • 整欄與整列規則如 C:C，只鋪在實際寫出的表格區域上，不鋪滿 1,048,576 列，Excel 因此只在檔案裡存在的儲存格上看到這些規則
  • 只有 style:map 的檔案。檔案沒有 calcext 區塊時，HotXLS 從重建範圍的左上角解讀公式規則裡的相對參照，而不是從聲明的基準儲存格位移
  • LibreOffice 的重疊規則。一格被多條規則涵蓋時，LibreOffice 只把第一條規則的對應寫上去。這種檔案光靠 style:map 讀不完整，也是讀取器在兩者都在場時偏好 calcext 的另一個理由

流程上的極限比上述任何一條都要緊。這幾個版本背後的缺陷，全部通過了「寫 ODS 再用 HotXLS 讀回」的往返測試，有些甚至在錯的應用程式裡做人工檢查也攔不住：整欄公式在 LibreOffice 一切正常，Excel 卻顯示 #NAME?；v2.384.66 起公式規則在 LibreOffice 正常，Excel 卻要等到 v2.384.69 才看得到任何規則。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 寫成每個涵蓋儲存格樣式上的 <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 spreadsheet component 產品頁