HotXLS-ODS-Interop: Formeln und Regeln, die Excel liest

Um eine ODS-Datei zu erzeugen, die sowohl Excel als auch LibreOffice korrekt liest, schreibt HotXLS jede Formel in OpenFormula-Syntax unter einem deklarierten of:-Namespace und schreibt jede Wert- oder Formel-Conditional-Format zweifach: als <style:map> auf dem Style jeder abgedeckten Zelle, der einzigen Form, die Excel 16 liest, und als calcext:conditional-formats-Block, der Form, der LibreOffice vertraut. Jede Anwendung ignoriert die Hälfte, die für die andere gedacht war, eine Datei, die in der einen korrekt aussieht, beweist also nichts über die andere

Dieser letzte Satz ist die Lehre aus sechs HotXLS-Versionen zwischen v2.384.55 und v2.384.72. Jeder Fix begann mit einer Datei, die HotXLS schrieb, perfekt zurücklas und die eine der beiden Zielanwendungen falsch behandelte. Es folgt, was jede Anwendung tatsächlich akzeptiert, das Markup, das beide zufriedenstellt, und die HotXLS-API-Aufrufe, die das aus Delphi heraus erzeugen

Warum sieht eine ODS-Datei in der einen Anwendung intakt aus und in der anderen kaputt?

Eine ODS-Datei sieht in der einen Anwendung intakt und in der anderen kaputt aus, weil Excel und LibreOffice verschiedene Teile desselben Pakets lesen. OpenDocument gibt Formeln und Conditional Formats mehr als eine legale Schreibweise, LibreOffice legt obendrauf seinen eigenen Erweiterungs-Namespace, und jeder Consumer pickt sich die Teilmenge heraus, die er implementiert. Ein Writer, der nur gegen einen Consumer getestet wurde, konvergiert gern auf Markup, das der andere stillschweigend falsch liest

Keine der Anwendungen meldet einen Fehler. LibreOffice zeigt #VALUE! in Zellen, deren Formeln es nicht parsen konnte; Excel öffnet die Arbeitsmappe mit schlicht fehlenden Conditional Formats oder mit einer Formel, die in etwas umgeschrieben wurde, das zu #NAME? oder der Konstanten 0 evaluiert. Ein Writer, der seine eigene Ausgabe round-tript, sieht davon nichts. HotXLS lief genau in diese Falle mit dem Formula-Namespace: Sein Reader matchte den of:-Präfix als schlichten Text, jeder Selbst-Roundtrip lief also durch, während LibreOffice in jeder Formelzelle #VALUE! zeigte

Feature	Excel 16 liest	LibreOffice 26.2 liest
Ganze Spalte als A:A geschrieben	Fehlgelesen als A:(A)	Toleriert
Ganze Spalte als [.A:.A] geschrieben	Ja	Ja
Conditional Formats in <style:map>	Ja, die einzige Form, die es liest	Ignoriert, wenn calcext vorhanden ist
Conditional Formats in calcext:conditional-formats	Ignoriert	Ja, bevorzugt
calcext-Wertregel mit einem calcext:operator-Attribut	Ignoriert	Importiert als „gleich 0“
calcext-Formelregel geschrieben als is-true-formula(...)	Ignoriert	Importiert als Wertvergleich mit 0

OpenFormula in ODS: Namespace deklarieren, dann die Syntax treffen

Eine Formelzelle in ODS ist für LibreOffice nur lesbar, wenn der of:-Präfix in table:formula zu einem deklarierten XML-Namespace auflöst. Der Präfix ist keine Dekoration. of: mappt auf urn:oasis:names:tc:opendocument:xmlns:of:1.2, und msoxl:, der Präfix, den HotXLS für Formeln benutzt, die sein OpenFormula-Übersetzer nicht modelliert, mappt auf http://schemas.microsoft.com/office/excel/formula. Vor v2.384.56 benutzte der content.xml-Root beide Präfixe, ohne sie zu deklarieren, und LibreOffice konnte die Formel-Grammatik überhaupt nicht identifizieren

<!-- Vor v2.384.56: Präfix benutzt, nie deklariert; LibreOffice zeigt #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"/>

<!-- Seit v2.384.56: beide Formula-Namespaces auf dem Root deklariert -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Mit dem gefixten Namespace muss der Ausdruck selbst weiterhin valides OpenFormula sein, wie in OpenDocument 1.3 Part 4 definiert. Die Fallen sind die Stellen, an denen Excel-Syntax und OpenFormula sich ähnlich sehen, aber nicht dasselbe sind:

• Zellreferenzen sind eingeklammert und mit Punkt präfixiert, und die $-Marker sind Teil der Referenz: [.$A$1] und [.A$1:.$B2] sind valides OpenFormula. Vor v2.384.55 warf der HotXLS-Writer jedes $ weg, absolute Referenzen kamen also relativ zurück und gingen erst schief, als jemand die Zelle kopierte
• Ganze Spalten und Zeilen müssen die eingeklammerte Form [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2] verwenden. Ein nacktes of:=SUM(A:A) toleriert LibreOffice, aber Excel 16 öffnet es als =SUM(A:(A)) mit #NAME? und macht aus Zeilenreferenzen und $A:$B die Konstante 0. HotXLS schreibt die eingeklammerte Form seit v2.384.65
• Funktionsargumente werden durch ; getrennt, nicht durch ,
• Referenz-Vereinigungen benutzen den ~-Operator: Excel-AREAS((A1,B2)) wird zu AREAS(([.A1]~[.B2])). Dieses Komma stattdessen zu einem ; zu übersetzen, macht aus einem Vereinigungsargument zwei Argumente
• Inline-Arrays trennen Spalten mit ; und Zeilen mit |: Excel-{1,2;3,4} wird zu {1;2|3;4}. Vor v2.384.55 produzierte HotXLS {1;2;3;4}, eine einzige Zeile mit vier Werten

Das Komma ist der harte Teil, denn ein Excel-Zeichen trägt drei Bedeutungen. Seit v2.384.55 führt der HotXLS-Writer beim Übersetzen einen Klammer-Stapel: Eine ( direkt nach einem Namen öffnet einen Funktionsaufruf, dessen Kommas zu ; werden; jede andere ( ist eine Gruppierungsklammer, deren Kommas zu ~ werden; und Kommas innerhalb von {} sind Array-Spaltentrenner. Damit und mit dem Namespace-Fix evaluierte LibreOffice 26.2 alle acht Array- und Vereinigungs-Testformeln korrekt, INDEX und AREAS über Vereinigungen eingeschlossen

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;

    // Geschrieben als of:=SUM([.A:.A]) seit v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Geschrieben als of:=[.A1]*[.$B$1]; die $-Marker überleben seit v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formeln, die der Übersetzer nicht modelliert, fallen auf msoxl:= mit unverändertem Excel-Text zurück, deshalb zählt auch die msoxl-Deklaration. Im aktuellen Writer umfasst dieser Pfad blattqualifizierte Referenzen wie Sheet2!A1 und strukturierte Tabellenreferenzen. HotXLS liest msoxl:-Formeln beim Import zurück, der eigene Roundtrip erhält den Ausdruck also intakt, aber wie eine andere Anwendung sie behandelt, liegt außerhalb der Kontrolle des Writers. Kommt eine Formel, von der Ihre Consumer abhängen, mit dem msoxl:-Präfix heraus, öffnen Sie die Datei in beiden Anwendungen, bevor Sie sie ausliefern

Warum sieht Excel Conditional Formats, die nur als calcext geschrieben wurden, nicht?

Excel 16 sieht calcext-Conditional Formats nicht, weil es ODS-Conditional Formats ausschließlich aus <style:map>-Kindern von Cell-Styles liest und den calcext:conditional-formats-Block komplett ignoriert. Das Experiment, das das entscheidet, ist kurz: Man nehme eine von LibreOffice gespeicherte ODS-Datei, lösche die style:map-Elemente, und Excel liest null Regeln; lösche stattdessen den calcext-Block, und Excel liest weiterhin alle. LibreOffice verhält sich genau umgekehrt. calcext ist LibreOffice-Erweiterungs-Namespace, kein Teil des ODF-Standards, und ist eine calcext-Regel vorhanden, nimmt LibreOffice sie und ignoriert die style:map

Vor v2.384.69 schrieb HotXLS nur calcext, eine ODS-Datei mit tadellosen Hervorhebungen öffnete in Excel also ohne jede Wertregel und ohne jede Formelregel. HotXLS schreibt jetzt beide Formen. Die style:map-Hälfte nutzt die Bedingungs-Grammatik des OpenDocument-Schemas (ODF 1.3 Part 3), mit den exakten Schreibweisen, die Excel 16 und LibreOffice 26.2 beide produzieren, wenn sie ODS sichern:

<!-- Vereinfacht. Träger-Style für jede Zelle von A1:A50 (zwei Wertregeln) -->
<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>

<!-- Träger-Style für jede Zelle von C1:C50 (eine Formelregel) -->
<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>

Der Haken an style:map: Sie lebt auf Cell-Styles, ist also pro Zelle. Jede Zelle im Bereich der Regel muss einen Style tragen, der die Map hält, leere Zellen eingeschlossen, sonst deckt die Regel diese Zelle in Excel schlicht nicht ab. HotXLS kopiert den bestehenden Formatierungs-Style jeder Zelle, hängt die Maps an und dedupliziert Träger-Styles über das Paar aus Original-Style und Map-Text, ein Bereich aus 500 Zellen mit identischer Formatierung produziert also weiterhin einen Style. Der Writer dehnt die geschriebene Tabelle außerdem auf den Bereich der Regel aus, leere Endzeilen innerhalb einer Regel werden also emittiert statt weggelassen. Seit v2.384.69 trägt styles.xml auch einen leeren Default-Cell-Style, style:apply-style-name="Default" hat also stets ein Ziel

Die calcext-Schreibweise, die LibreOffice tatsächlich akzeptiert

LibreOffice akzeptiert eine calcext-Wertregel nur, wenn der Vergleichsoperator Teil des Werttexts ist, etwa >3 oder between(1,10), und eine Formelregel nur, wenn sie als formula-is(...) geschrieben ist. Beide Punkte kosteten HotXLS je eine Version, denn die falschen Schreibweisen produzieren eine Regel, die fehlerfrei importiert und dann die falschen Zellen matcht

Der erste Fehler war ein calcext:operator-Attribut neben calcext:value. Es liest sich natürlich, ist aber erfunden: LibreOffice kennt dieses Attribut nicht, importierte also jede Wertregel als „gleich 0“. Der zweite war, is-true-formula(...), die style:map-Schreibweise, in eine calcext-Bedingung zu stecken, was LibreOffice ebenfalls als Zellwertvergleich mit 0 importierte. Der Formel-Fix kam in v2.384.66, der Wert-Fix in v2.384.69:

<!-- Falsch: LibreOffice ignoriert calcext:operator und importiert gleich 0 -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Richtig: Der Operator reist im Wert mit -->
<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"/>

<!-- Richtig: Formelregeln nutzen formula-is, relative Referenzen an der Basiszelle verankert -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Die Basiszelle ist das, was relativen Referenzen ihre Bedeutung gibt. HotXLS verankert jede Regel an der Zelle oben links ihrer ersten Bereichsfläche, eine für C1 geschriebene Formel evaluiert also als C2, C3 und so den Bereich hinunter, exakt wie in Excels eigener Conditional Formatting. Der Regelausdruck läuft durch denselben Übersetzer wie Zellformeln, Arrays, Vereinigungen, ganze Spalten und $-Marker kommen also in den oben beschriebenen Formen heraus. Auf der Delphi-Seite fügen Sie Regeln genau so hinzu, wie Sie es für eine .xlsx-Datei täten

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Wertregeln: style:map cell-content()>100 plus calcext value ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: helles Rot

  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
  Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);

  // Formelregel in Excel-Syntax (Komma-Trenner, relativ zu C1):
  // style:map is-true-formula(...) und calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: helles Gelb

  Opts := TODSExportOptions.Create;
  try
    Opts.Generator := 'OrderExport 3.1';
    Book.SaveAsODS('orders.ods', Opts);
  finally
    Opts.Free;
  end;
end;

ODS aus Excel und LibreOffice zurück nach Delphi lesen

Wenn HotXLS eine ODS-Datei öffnet, akzeptiert sein Reader beide Conditional-Format-Dialekte und beide calcext-Schreibweisen, und er zählt eine Regel nicht doppelt, wenn die Datei sie in beiden Formen trägt. Echte Dateien kommen von drei Writers, jeder mit eigenen Gewohnheiten:

• Alter und neuer calcext. Dateien mit einem calcext:operator-Attribut, eingeschlossen von HotXLS vor v2.384.69 geschriebene ODS-Dateien, laufen weiterhin durch das Legacy-Parsing. Formelbedingungen werden als formula-is(...) oder is-true-formula(...) erkannt
• Excels style:map-Schreibweise. Excel präfixiert Bedingungen mit of:, wie in of:cell-content-is-between(1,10), und lässt bei Wertregeln die Basiszelle weg. Beides wird akzeptiert
• Leere Zellen. Excel und LibreOffice legen die Map für leere Zellen beide auf den Spalten-Default-Style statt auf eine Zelle, der Reader löst also Spalten-Default-Styles für repeated cells auf, bevor er Maps einsammelt
• Bereichs-Rekonstruktion. Maps werden pro Zelle eingesammelt, nach dem Lesen eines Blatts fusioniert der Reader also Zellen mit derselben Bedingung und Basiszelle zurück zu Bereichen, erst quer durch jede Zeile, dann abwärts über passende Spalten-Spans, und verwirft Regeln, die bereits aus calcext gelesen wurden

Der v2.384.72-Fix betrifft Zahlen-Styles, nicht Regeln. Excel 16 und LibreOffice 26.2 schreiben beide das General-Format als Zahlen-Style, dessen number:number-Element kein number:decimal-places hat, typischerweise <number:number number:min-integer-digits="1"/>. Der HotXLS-Reader behandelte die fehlende Anzahl als zwei feste Dezimalstellen, jeder Wert im Default-Style importierte also mit 0.00, und 1.5 zeigte sich als 1.50. Seit v2.384.72 mappt ein schlichtes Zahlen-Element ohne Dezimalstellen, ohne Mindest-Dezimalstellen, ohne Gruppierung und mit höchstens einer Ganzzahlziffer auf General, und ein einzelnes General lässt die Zelle ganz ohne Zahlenformat. Text drumherum bleibt erhalten, wie in General" kg", und gruppierte Zahlen behalten das alte Mapping, denn Excel hat kein gruppiertes General-Format

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]; // der Sheets-Indexer ist 1-basiert
    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;

    // Eine Zelle in Excels General-Style liest sich ohne Zahlenformat zurück
    // seit v2.384.72, statt '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Regelformeln kommen in Excel-Syntax mit Komma-Trennern zurück, derselben Form, die Sie an AddCondFormatExpression übergeben würden, eine von HotXLS geschriebene Regel liest sich also als identischer String zurück. Für das größere Bild, was der ODS-Import-Pfad behält und was er fallen lässt, siehe den HotXLS-ODS-Öffnen-und-Sichern-Roundtrip-Leitfaden; wie repeated rows aus Excel und LibreOffice beim Import expandiert werden, siehe ODS repeated rows als Zeilenhöhen-Läufe

Wo liegen die Grenzen des HotXLS-ODS-Conditional-Format-Interops?

Der Doppel-Markup-Ansatz deckt Wertvergleichsregeln und Formelregeln ab, und da endet er. Alles andere ist einseitig oder wird gar nicht geschrieben:

• Color Scales und Data Bars werden nur als calcext-Elemente geschrieben, LibreOffice zeigt sie also an, Excel nicht
• Andere Regelarten, etwa Icon Sets, Textregeln, Top-N, über-dem-Durchschnitt- und Duplikatregeln, haben im aktuellen Writer keine ODS-Ausgabe. Eine Textregel lässt sich meist als Formelregel ausdrücken, etwa ISNUMBER(SEARCH("late",B2)) über B2:B200, die dann beide Anwendungen erreicht
• Ganze-Spalten- und ganze-Zeilen-Regeln wie C:C werden nur über den tatsächlich geschriebenen Tabellenbereich gelegt, nicht über alle 1.048.576 Zeilen, Excel sieht diese Regeln also nur auf Zellen, die in der Datei existieren
• Dateien nur mit style:map. Hat eine Datei keinen calcext-Block, interpretiert HotXLS relative Referenzen in Formelregeln von der Ecke oben links des rekonstruierten Bereichs aus, nicht durch Verschieben von der angegebenen Basiszelle
• Überlappende Regeln aus LibreOffice. Ist eine Zelle von mehreren Regeln abgedeckt, schreibt LibreOffice nur die Map der ersten Regel auf sie. Solche Dateien lassen sich nicht komplett allein aus style:map lesen, ein Grund mehr, dass der Reader calcext bevorzugt, wenn beide existieren

Die Prozessgrenze zählt mehr als jedes dieser Details. Die Defekte hinter diesen Versionen kamen an Roundtrips vorbei, die ODS schrieben und mit HotXLS zurücklasen, und manche hätten auch eine manuelle Prüfung in der falschen Anwendung bestanden: Ganz-Spalten-Formeln funktionierten in LibreOffice, während Excel #NAME? zeigte, und ab v2.384.66 funktionierten Formelregeln in LibreOffice, während Excel bis v2.384.69 überhaupt keine Regeln zeigte. Ist ODS-Interop eine Anforderung, ist der Abnahmetest, die Datei in Excel und in LibreOffice zu öffnen und zu vergleichen, was jede zeigt. Dasselbe Disziplin gilt für die Styles, auf die Regeln zeigen; der HotXLS-Artikel zu Conditional Formatting und Styles behandelt, wie Highlight-Styles auf der Workbook-Seite definiert werden

Kurzreferenz: ODS, das beide Anwendungen lesen

• Deklarieren Sie xmlns:of und xmlns:msoxl auf dem content.xml-Root, sonst zeigt LibreOffice für jede Formel #VALUE! (HotXLS seit v2.384.56)
• Schreiben Sie Referenzen als [.A1], behalten Sie jedes $, und schreiben Sie ganze Spalten und Zeilen als [.A:.A] und [.1:.1] (seit v2.384.55 und v2.384.65)
• Nehmen Sie ; für Argumente, ~ für Referenz-Vereinigungen und | zwischen Inline-Array-Zeilen
• Schreiben Sie jede Wert- oder Formelregel als <style:map> auf dem Style jeder abgedeckten Zelle für Excel und als calcext-Bedingung für LibreOffice (seit v2.384.69)
• In calcext gehört der Operator in den Wert (>3, between(1,10)), und Formelregeln heißen formula-is(...) mit einer Basiszelle (seit v2.384.66 und v2.384.69)
• Rechnen Sie beim Import mit einem General-Zahlen-Style ohne number:decimal-places; HotXLS liest ihn seit v2.384.72 als General
• Verifizieren Sie jedes neue Export-Profil, indem Sie die Datei in Excel und in LibreOffice öffnen, niemals nur in einer von beiden

HotXLS ist eine native Delphi- und C++Builder-Spreadsheet-Bibliothek, die XLS, XLSX und ODS ohne installiertes Excel oder LibreOffice liest und schreibt; vollständiger Quellcode, Feature-Liste und Lizenzierung finden sich auf der HotXLS Delphi spreadsheet component page