技術文章

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

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

最後那句話就是 ODS 互通的整門課:一個寫入器自己讀回來完美無缺的檔案,照樣可能被任何一個目標應用程式讀錯。接下來是各應用程式實際接受什麼、同時滿足雙方的標記長什麼樣,以及從 Delphi 產出這些的 HotXLS API 呼叫

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 值規則忽略匯入成「等於 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 就完全無從辨識公式文法

<!-- 錯:前綴用了卻沒宣告;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"/>

<!-- 對:兩個公式命名空間都宣告在根元素上 -->
<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。轉譯器把 $ 丟掉,絕對參照就變相對參照,而傷害要等到有人複製儲存格才現形
  • 整欄與整列必須用帶方括號的形式 [.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}。漏掉列分隔符,就得到 {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: 公式讀回來,所以它自己的往返能讓運算式原封不動;但別的應用程式怎麼對待它們,就不是寫入器管得著的。如果你的消費端依賴某條公式,而它出來帶著 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 呼叫寫出兩種寫法,是檔案在兩邊都驗得過的唯一辦法

只寫 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 個格式相同的儲存格照樣只產出一個樣式。寫入器也會把寫出的表格延伸到規則範圍,也就是規則內的空尾列會被輸出而不是被丟掉。styles.xml 另外帶著一個空的 Default 儲存格樣式,所以 style:apply-style-name="Default" 永遠有目標可指

LibreOffice 實際接受的 calcext 寫法

LibreOffice 只在比較運算子是值文字一部分時(如 >3 或 between(1,10))接受 calcext 值規則,公式規則則只在寫成 formula-is(...) 時接受。這兩點都很容易弄錯,因為錯的拼寫產生的規則匯入時一聲不吭,然後配到錯的儲存格

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

<!-- 錯: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 屬性的檔案照樣走舊式剖析。公式條件認得 formula-is(...) 或 is-true-formula(...) 兩種寫法
  • Excel 的 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 把「無小數位數、無最小小數位、無分組、至多一位整數」的純數字元素映射到 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 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 重複列

哪些條件式格式類型能同時送達兩個應用程式?

雙標記的做法涵蓋值比較規則與公式規則——兩個應用程式都讀的兩種。其餘類型各按簡單規則處理:

  • 色階與資料橫條寫成 calcext 元素,也就是 LibreOffice 讀的形式,所以請把 LibreOffice 當成它們的 ODS 消費端
  • 其他規則類型,如圖示集、文字規則、前 N 名、高於平均與重複值規則,只要條件允許,最好在 ODS 匯出前改寫成公式規則。例如文字規則可以變成對 B2:B200 的 ISNUMBER(SEARCH("late",B2)),之後就能同時送達兩個應用程式
  • 整欄與整列規則如 C:C,只鋪在實際有寫出的表格區域上,而不是 1,048,576 列全部,所以 Excel 只在檔案裡存在的儲存格上看到這些規則
  • 只有 style:map 的檔案。檔案裡沒有 calcext 區塊時,HotXLS 從重建範圍的左上角解讀公式規則裡的相對參照,而不是從宣告的基準儲存格位移
  • LibreOffice 產生的重疊規則。一格被多條規則涵蓋時,LibreOffice 只把第一條規則的對應寫到那格上。這類檔案光靠 style:map 讀不全,這也是讀取器在兩種形式都在時優先取 calcext 的另一個理由

流程上的規則比上述任何一條都要緊。本文的每一個陷阱都能通過「寫 ODS 再用同一個函式庫讀回」的往返測試,其中幾個還能在錯的應用程式裡通過人工檢查:整欄公式在 LibreOffice 一切正常,Excel 卻顯示 #NAME?;只寫成 calcext 的公式規則在 LibreOffice 正常,Excel 卻一條規則都不顯示。如果 ODS 互通是需求,驗收測試就是把檔案在 Excel 與 LibreOffice 各開一次、比對兩邊顯示的內容。同樣的紀律也適用於規則指向的樣式;HotXLS 條件式格式與樣式一文說明了螢光樣式在活頁簿側怎麼定義

速查:兩個應用程式都讀的 ODS

  • 在 content.xml 根元素宣告 xmlns:of 與 xmlns:msoxl,否則 LibreOffice 對每條公式顯示 #VALUE!
  • 參照寫成 [.A1]、每個 $ 都保留,整欄整列寫成 [.A:.A] 與 [.1:.1]
  • 引數用 ;、參照聯集用 ~、行內陣列列與列之間用 |
  • 每條值或公式規則,為 Excel 寫成涵蓋範圍內每個儲存格樣式上的 <style:map>,為 LibreOffice 寫成 calcext 條件
  • calcext 裡,運算子放進值(>3、between(1,10)),公式規則寫成帶基準儲存格的 formula-is(...)
  • 匯入時預期 General 數字樣式不帶 number:decimal-places;HotXLS 把它讀成 General
  • 每個新的匯出設定檔,都在 Excel 與 LibreOffice 兩邊各開一次驗證,絕不只驗一邊

HotXLS 是原生的 Delphi 與 C++Builder 試算表函式庫,不必安裝 Excel 或 LibreOffice 就能讀寫 XLS、XLSX 與 ODS;完整原始碼、功能清單與授權資訊見 HotXLS Delphi spreadsheet component 產品頁