HotXLSのODS相互運用：Excelも読める数式とルール

ExcelとLibreOfficeの両方が正しく読めるODSファイルを作るには、HotXLSはすべての数式を、宣言されたof:名前空間の下でOpenFormula構文で書きます。そして値または数式の条件付き書式は2回書きます。対象セルのスタイルに付けた<style:map>として。これがExcel 16が読む唯一の形です。もう1つはcalcext:conditional-formatsブロックとして。これがLibreOfficeが信頼する形です。各アプリケーションは相手宛ての半分を無視するので、片方で正しく見えるファイルは、もう片方については何も証明しません

この最後の一文こそ、v2.384.55からv2.384.72までの6つのHotXLSリリースの背後にある教訓です。それぞれの修正は、HotXLSが書いて完璧に読み戻せたのに、2つのターゲットアプリケーションのどちらかが間違えたファイルから始まりました。以下では、各アプリケーションが実際に受け入れるもの、両方を満たすマークアップ、そしてそれをDelphiから作り出すHotXLSのAPI呼び出しを扱います

ODSファイルが片方のアプリでは正常、もう片方では壊れて見えるのはなぜか

ExcelとLibreOfficeは同じパッケージの異なる部分を読むので、ODSファイルは片方では正常、もう片方では壊れて見えます。OpenDocumentは数式と条件付き書式に複数の合法な綴りを許し、LibreOfficeはその上に独自の拡張名前空間を足し、各コンシューマーは自分が実装したサブセットを選びます。1つのコンシューマーだけで検証されたライターは、もう片方が静かに誤読するマークアップへ、いとも簡単に収束します

どちらのアプリケーションもエラーを報告しません。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の数式セルがLibreOfficeに読めるのは、table:formulaのof:プレフィックスが宣言済みのXML名前空間へ解決するときだけです。プレフィックスは飾りではありません。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" ...>

名前空間を直しても、式そのものはOpenDocument 1.3 Part 4で定義された有効なOpenFormulaである必要があります。罠は、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]))になります。このカンマを;へ訳すと、1つの共用体引数が2つの引数に化けます
• インライン配列は列を;で、行を|で区切ります。Excelの{1,2;3,4}は{1;2|3;4}になります。v2.384.55より前のHotXLSは{1;2;3;4}、つまり4値の1行を作っていました

厄介なのはカンマです。Excelの1文字が3つの意味を運ぶからです。v2.384.55から、HotXLSライターは翻訳の間、括弧スタックを追跡します。名前の直後の(は関数呼び出しを開き、そのカンマは;になります。それ以外の(はグルーピングの括弧で、そのカンマは~になります。そして{}の中のカンマは配列の列区切りです。これと名前空間の修正により、LibreOffice 26.2は8本すべての配列と共用体のプローブ数式を正しく評価しました。共用体上の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;

    // 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;

トランスレーターがモデル化しない数式は、Excelのテキストを変えずにmsoxl:=へフォールバックします。だからmsoxlの宣言も重要なのです。現在のライターでは、この経路にはSheet2!A1のようなシート修飾参照や、構造化テーブル参照が含まれます。HotXLSはインポート時にmsoxl:の数式を読み戻すので、自己の往復では式が保たれます。ただし他のアプリケーションがそれをどう扱うかは、ライターの制御外です。自分のコンシューマーが依存する数式がmsoxl:プレフィックス付きで出てきたら、出荷前に両方のアプリケーションでファイルを開いてください

calcextだけとして書かれた条件付き書式を、Excelはなぜ見ないのか

Excel 16がcalcextの条件付き書式を見ないのは、ODSの条件付き書式をセルスタイルの<style:map>子要素だけから読み、calcext:conditional-formatsブロックを丸ごと無視するからです。決着をつける実験は短くて済みます。LibreOfficeが保存したODSを取り、style:map要素を削除すると、Excelは0ルールを読みます。代わりにcalcextブロックを削除すると、Excelは今度も全部読みます。LibreOfficeは逆の振る舞いです。calcextはLibreOfficeの拡張名前空間で、ODF標準の一部ではありません。calcextのルールが存在すると、LibreOfficeはそれを採り、style:mapを無視します

v2.384.69より前のHotXLSはcalcextだけを書いていたため、完璧なハイライトを持つODSファイルが、Excelでは値ルールも数式ルールもまったくない状態で開きました。HotXLSは今、両方の形式を書きます。style:mapの側はOpenDocumentスキーマ（ODF 1.3 Part 3）の条件文法を使い、Excel 16とLibreOffice 26.2がODSを保存するときに両者とも出す正確な綴りに合わせます:

<!-- 簡略化。A1:A50の全セルのキャリアスタイル（値ルール2件） -->
<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の全セルのキャリアスタイル（数式ルール1件） -->
<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セルの範囲でも、スタイルは1つしか生まれません。ライターは書かれるテーブルをルールの範囲まで広げるので、ルール内の空の末尾行は捨てられずに出力されます。v2.384.69から、styles.xmlは空のDefaultセルスタイルも持つので、style:apply-style-name="Default"には常にターゲットがあります

LibreOfficeが実際に受け入れるcalcextの綴り

LibreOfficeがcalcextの値ルールを受け入れるのは、比較演算子が値のテキストの一部、たとえば>3やbetween(1,10)のときだけです。数式ルールはformula-is(...)と綴られたときだけ受け入れます。この2点でHotXLSはそれぞれ1リリースを費やしました。間違った綴りでも、ルールはエラーなくインポートでき、そのうえで間違ったセルに一致するからです

最初の間違いは、calcext:valueの隣に置いたcalcext:operator属性です。自然に読めますが、これは捏造です。LibreOfficeはその属性を知らないので、すべての値ルールを「0と等しい」としてインポートしました。2つ目は、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はすべてのルールを、最初の範囲領域の左上セルに固定します。だから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の綴りを受け入れ、ファイルが両方の形式でルールを運んでいても1ルールを2回数えません。実在するファイルは3つのライターから来ます。それぞれ癖があります:

• 旧と新の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はどちらも、空セル用のマップをセルではなく列のデフォルトスタイルに置きます。だからリーダーは、マップを集める前に、repeated cellに対する列のデフォルトスタイルを解決します
• 領域の再構築。マップはセルごとに集まるので、シートを読み終えると、リーダーは同じ条件と基準セルを共有するセルを範囲へ統合し直します。まず各行を横断し、次に一致する列スパンを下ります。calcextからすでに読んだルールは落とします

v2.384.72の修正は、ルールではなく数値スタイルに関するものです。Excel 16とLibreOffice 26.2はどちらも、Generalフォーマットを、number:number要素にnumber:decimal-placesを持たない数値スタイルとして書きます。典型的には<number:number number:min-integer-digits="1"/>です。HotXLSのリーダーは、この欠けた桁数を固定小数2桁として扱っていました。だからDefaultスタイルのすべての値が0.00でインポートされ、1.5は1.50と表示されていました。v2.384.72からは、小数桁なし、最小小数なし、グルーピングなし、整数桁1以下の素の数値要素は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のrepeated rowがインポート時にどう展開されるかは、行高ランとしてのODS repeated rowsをご覧ください

HotXLSのODS条件付き書式相互運用の限界はどこか

デュアルマークアップの方式がカバーするのは値比較のルールと数式ルールで、そこまでです。それ以外は片側だけか、まったく書かれません:

• カラースケールとデータバーはcalcext要素としてだけ書かれるので、LibreOfficeは表示し、Excelはしません
• その他のルール種別、アイコンセット、テキストルール、トップN、平均以上、重複ルールは、現在のライターにはODS出力がありません。テキストルールはたいてい数式ルールとして言い換えられます。たとえばB2:B200に対するISNUMBER(SEARCH("late",B2))なら、両方のアプリケーションに届きます
• C:Cのような列全体・行全体のルールは、実際に書かれたテーブル領域にだけ敷かれます。1,048,576行すべてではありません。だからExcelがこれらのルールを見るのは、ファイルに存在するセルの上だけです
• style:mapだけのファイル。calcextブロックがないファイルでは、HotXLSは数式ルール内の相対参照を、明示された基準セルからのシフトではなく、再構築した範囲の左上隅から解釈します
• LibreOffice由来の重複ルール。1つのセルが複数のルールで覆われるとき、LibreOfficeは最初のルールのマップだけを書き込みます。こういうファイルはstyle:mapだけからは完全には読めません。両方あるときにリーダーがcalcextを優先する、もう1つの理由です

これらのどれよりも、プロセスの限界が重要です。これらのリリースの背後にある欠陥は、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は、ExcelもLibreOfficeもインストールせずにXLS、XLSX、ODSを読み書きする、ネイティブのDelphiとC++Builderスプレッドシートライブラリです。フルソース、機能一覧、ライセンスは、HotXLS Delphi spreadsheet component pageにあります