技術記事

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

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

その最後の一文こそ、ODS 相互運用の教訓のすべてです。ライターが完璧に読み戻せるファイルでも、どちらかのターゲットアプリケーションには間違って見え得ます。以下では、各アプリケーションが実際に受け入れるもの、両方を満たすマークアップ、そしてそれを Delphi から組み立てる HotXLS API 呼び出しを見ていきます

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

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

どちらのアプリケーションもエラーを報告しません。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 の数式セルが LibreOffice に読めるのは、table:formula内のof:プレフィックスが宣言済みの XML 名前空間に解決するときだけです。プレフィックスは飾りではありません。of:はurn:oasis:names:tc:opendocument:xmlns:of:1.2に対応し、HotXLS が OpenFormula トランスレータがモデル化しない数式に使うmsoxl:は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" ...>

名前空間が宣言されていても、式そのものは OpenDocument 1.3 Part 4 で定義される有効な OpenFormula でなければなりません。罠は、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]))になります。このカンマを代わりに;へ訳すと、1つのユニオン引数が2つの引数になります
  • インライン配列は列を;で、行を|で区切ります。Excel の{1,2;3,4}は{1;2|3;4}になります。行セパレータを落とすと{1;2;3;4}、4値の1行になってしまいます

カンマが難所です。1つの Excel 文字が3つの意味を運ぶからです。HotXLS のライターは翻訳中、括弧スタックを追跡します。名前の直後の(は関数呼び出しを開き、そのカンマは;になります。それ以外の(はグループ化の括弧で、そのカンマは~になります。そして{}の中のカンマは配列の列セパレータです。これと宣言済み名前空間があれば、LibreOffice 26.2 は配列数式とユニオン数式を、ユニオン上のINDEXやAREASを含めて正しく評価します

ExcelのカンマをOpenFormulaへ翻訳する括弧スタックのHotXLSの図。名前の直後の括弧は関数呼び出しを開き、そのカンマはセミコロンになります。それ以外の括弧はグルーピングで、そのカンマは共用体演算子のチルダになります。波括弧の中のカンマは配列の列区切りです。A1とB2の共用体に対するAREASが例です
カンマはExcel構文で3つの意味を運びます。見分けられるのは走行中の括弧スタックだけです。共用体のカンマをセミコロンへ訳すと、1つの引数が静かに2つへ化けます
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;

トランスレータがモデル化しない数式は、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を無視します

ODSの条件付き書式に関するHotXLSのデュアルチャネルの図。値または数式のルールはすべて、対象セルのスタイル上のスタイルマップとして書かれます。Excel 16が読める唯一の形です。同時に、演算子を値の中に入れたcalcextの条件付き書式ブロックとしても書かれます。こちらはLibreOfficeが好む形です。各アプリケーションはもう片方の綴りを静かに無視します
Excelはスタイルマップを読みcalcextを無視し、LibreOfficeはcalcextを優先してマップを捨てます。どちらもエラーは出しません。1つのHotXLS呼び出しから両方の綴りを書くことだけが、両方で検証に合格する方法です

calcext だけを書くと、ハイライトは完璧なのに、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つで済みます。ライターはまた、書き出す表をルールの範囲まで広げるので、ルールの内側にある空の末尾行は落とされずに出力されます。styles.xmlは空のDefaultセルスタイルも運ぶので、style:apply-style-name="Default"には常にターゲットがあります

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

LibreOffice が calcext の値ルールを受け入れるのは、比較演算子が値テキストの一部であるとき、つまり>3やbetween(1,10)のような形だけです。数式ルールを受け入れるのはformula-is(...)と綴られているときだけです。どちらも間違えやすいポイントです。間違った綴りは、エラーなしで取り込まれた上で、間違ったセルに一致するルールになってしまうからです

最初の罠は、calcext:valueのとなりにcalcext:operator属性を置くことです。自然に読めますが、これは発明品です。LibreOffice はその属性を知らないので、すべての値ルールを "equal to 0"(0 に等しい)として取り込みます。2つ目は、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 属性は発明品ですべての値ルールを equal to 0 として取り込み、演算子は greater than 100 や between 1 and 10 のように値の中に属し、数式ルールは style map の綴り is-true-formula ではなく、ベースセルにアンカーされた formula-is で書く
どちらの誤った綴りもエラーなしで取り込まれ、それから間違ったセルに一致する。equal to 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の綴りを受け入れ、ファイルが両方の形式でルールを運んでいても1ルールを2回数えません。実在するファイルは3つのライターから来ます。それぞれ癖があります:

  • 旧い 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"/>です。欠けた桁数を小数2桁と読むと、Defaultスタイルのすべての値が0.00付きで取り込まれ、1.5 は 1.50 と表示されます。HotXLS は、小数桁がなく最小小数桁もなくグループ区切りもなく整数桁が高々1桁の素の 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 の General スタイルのセルは、数値書式なしで読み戻される。
    // '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をご覧ください

どの条件付き書式の種類が両方のアプリケーションに届くのか?

双マークアップ方式がカバーするのは値比較ルールと数式ルール、両アプリケーションが読める2種類です。それ以外の種類は単純な規則に従います:

  • カラースケールとデータバーは calcext 要素、つまり LibreOffice が読む形式で書かれるので、ODS での消費者は LibreOffice と考えてください
  • その他のルール種類、アイコンセット、テキストルール、上位N、平均以上、重複ルールなどは、条件が許す限り、ODS エクスポートの前に数式ルールへ言い換えるのが最善です。たとえばテキストルールは、B2:B200の上のISNUMBER(SEARCH("late",B2))になり、両方のアプリケーションに届きます
  • 列全体・行全体のルール、たとえばC:Cは、実際に書かれたテーブル領域の上だけに敷かれます。全1,048,576行の上ではありません。Excel がこれらのルールを見るのは、ファイルに存在するセルの上だけです
  • style:map だけのファイル。calcext ブロックを持たないファイルでは、HotXLS は数式ルール内の相対参照を、明示されたベースセルからのシフトではなく、再構築した範囲の左上隅から解釈します
  • LibreOffice 由来の重なり合うルール。1つのセルが複数のルールで覆われるとき、LibreOffice は最初のルールのマップ(the first rule's map)だけをそのセルに書きます。そういうファイルはstyle:mapだけからは完全には読めず、これが、両方存在するときにリーダーが calcext を優先するもう1つの理由です

この中でいえば、プロセスの規則が何よりも重要です。この記事の罠はどれも、ODS を書いて同じライブラリで読み戻すラウンドトリップは通ってしまいます。いくつかは、間違ったアプリケーションでの手動チェックまで通ります。列全体の数式は LibreOffice では動くのに Excel では#NAME?を表示し、calcext だけとして書かれた数式ルールは LibreOffice では動くのに Excel ではルールがまったく表示されない、という具合です。ODS 相互運用が要件なら、受け入れテストは Excel と LibreOffice の両方でファイルを開き、それぞれに何が表示されるかを比べることです。同じ規律は、ルールが指すスタイルにも当てはまります。ハイライトスタイルをワークブック側でどう定義するかは、HotXLS の条件付き書式とスタイルの記事が扱っています

クイックリファレンス:両アプリケーションが読めるODS

  • content.xmlのルートでxmlns:ofとxmlns:msoxlを宣言する。さもないと LibreOffice はすべての数式に#VALUE!を表示します
  • 参照は[.A1]と書き、$を1つも落とさず、列全体と行全体は[.A:.A]と[.1:.1]と書きます
  • 引数には;、参照ユニオンには~、インライン配列の行間には|を使います
  • 値ルールも数式ルールも、Excel 向けには対象セルそれぞれのスタイルの上の<style:map>として、LibreOffice 向けには calcext 条件として書きます
  • calcext では演算子を値の中に置き(>3、between(1,10))、数式ルールはベースセル付きでformula-is(...)と綴ります
  • インポートではnumber:decimal-placesのない General 数値スタイルを想定します。HotXLS はそれを General として読みます
  • 新しいエクスポートプロファイルの検証は、Excel と LibreOffice の両方でファイルを開いて行います。片方だけで済ませません

HotXLSは、ExcelもLibreOfficeもインストールせずにXLS、XLSX、ODSを読み書きする、ネイティブのDelphiとC++Builderスプレッドシートライブラリです。フルソース、機能一覧、ライセンスは、HotXLS Delphi spreadsheet component pageにあります