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 ganze Lehre des ODS-Interop: Eine Datei, die ein Writer perfekt zurückliest, kann trotzdem eine sein, die eine der beiden Zielanwendungen falsch behandelt. 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. Der Formula-Namespace ist der Lehrbuchfall: Ein Reader, der den of:-Präfix als schlichten Text matcht, besteht jeden Selbst-Roundtrip, während LibreOffice in jeder Formelzelle #VALUE! zeigt
| 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. Benutzen Sie einen der beiden Präfixe, ohne ihn am content.xml-Root zu deklarieren, und LibreOffice kann die Formel-Grammatik überhaupt nicht identifizieren
<!-- Falsch: 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"/>
<!-- Richtig: 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 deklarierten 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. Ein Übersetzer, der das$wegwirft, macht aus absoluten relative Referenzen, und der Schaden zeigt sich erst, wenn jemand die Zelle kopiert - Ganze Spalten und Zeilen müssen die eingeklammerte Form
[.A:.A],[.$A:.$B],[.1:.1],[.$1:.$2]verwenden. Ein nacktesof:=SUM(A:A)toleriert LibreOffice, aber Excel 16 öffnet es als=SUM(A:(A))mit#NAME?und macht aus Zeilenreferenzen und$A:$Bdie Konstante 0. HotXLS schreibt die eingeklammerte Form - Funktionsargumente werden durch
;getrennt, nicht durch, - Referenz-Vereinigungen benutzen den
~-Operator: Excel-AREAS((A1,B2))wird zuAREAS(([.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}. Verpasst man den Zeilentrenner, entsteht{1;2;3;4}, eine einzige Zeile mit vier Werten
Das Komma ist der harte Teil, denn ein Excel-Zeichen trägt drei Bedeutungen. Der HotXLS-Writer führt 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 deklarierten Namespace evaluiert LibreOffice 26.2 Array- und Vereinigungsformeln 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])
Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
// Geschrieben als of:=[.A1]*[.$B$1]; die $-Marker überleben
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. Dieser Pfad umfasst 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's andere Anwendungen mit ihnen halten, weiß der Writer nicht. 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's Excel nicht, wenn Conditional Formats nur als calcext geschrieben sind?
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's Erweiterungs-Namespace, kein Teil des ODF-Standards, und ist eine calcext-Regel vorhanden, nimmt LibreOffice sie und ignoriert die style:map
Schreibt man nur calcext, öffnet eine ODS-Datei mit tadellosen Hervorhebungen in Excel ohne jede Wertregel und ohne jede Formelregel, deshalb schreibt HotXLS 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()>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])>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, gilt 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 die Zelle in Excel schlicht nicht ab, so einfach ist's. 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, und schon reicht's: Ein Bereich aus 500 Zellen mit identischer Formatierung produziert 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. styles.xml trägt auch einen leeren Default-Cell-Style, style:apply-style-name="Default" hat's also stets mit einem Ziel zu tun
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 sind leicht verkehrt zu bekommen, denn die falschen Schreibweisen produzieren eine Regel, die fehlerfrei importiert und dann die falschen Zellen matcht
Die erste Falle ist ein calcext:operator-Attribut neben calcext:value. Es liest sich natürlich, ist aber erfunden: LibreOffice kennt dieses Attribut nicht, importiert also jede Wertregel als "gleich 0". Die zweite ist, is-true-formula(...), die style:map-Schreibweise, in eine calcext-Bedingung zu stecken, was LibreOffice ebenfalls als Zellwertvergleich mit 0 importiert:
<!-- 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=">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])>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 so, wie's Excels eigene Conditional Formatting auch macht. 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 laufen weiterhin durch das Legacy-Parsing; Formelbedingungen werden alsformula-is(...)oderis-true-formula(...)erkannt, je nachdem, wie's geschrieben ist - Excels style:map-Schreibweise. Excel präfixiert Bedingungen mit
of:, wie inof: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 wiederholte Zellen 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
Zahlen-Styles brauchen dieselbe Sorgfalt wie 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"/>. Liest man die fehlende Anzahl als zwei feste Dezimalstellen, importiert jeder Wert im Default-Style mit 0.00, und 1.5 zeigt sich als 1.50. HotXLS 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 werden nicht auf General gemappt, 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,
// nicht als '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
Welche Conditional-Format-Arten erreichen beide Anwendungen?
Der Doppel-Markup-Ansatz deckt Wertvergleichsregeln und Formelregeln ab, die zwei Arten, die beide Anwendungen lesen. Für die anderen gilt simples Handwerk:
- Color Scales und Data Bars werden als calcext-Elemente geschrieben, in der Form, die LibreOffice liest; planen Sie also LibreOffice als ihren ODS-Consumer
- Andere Regelarten, etwa Icon Sets, Textregeln, Top-N, über-dem-Durchschnitt- und Duplikatregeln, lässt sich vor einem ODS-Export am besten als Formelregel ausdrücken, wo die Bedingung es erlaubt. Eine Textregel wird zum Beispiel
ISNUMBER(SEARCH("late",B2))überB2:B200und erreicht dann beide Anwendungen - Ganze-Spalten- und ganze-Zeilen-Regeln wie
C:Cwerden 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:maplesen, ein Grund mehr, dass der Reader calcext bevorzugt, wenn's beide gibt
Die Prozessregel zählt mehr als jedes dieser Details. Jede Falle in diesem Artikel passiert einen Roundtrip, der ODS schreibt und mit derselben Bibliothek zurückliest, und mehrere bestehen auch eine manuelle Prüfung in der falschen Anwendung: Ganz-Spalten-Formeln funktionieren in LibreOffice, während Excel #NAME? zeigt, und Formelregeln, die nur als calcext geschrieben sind, funktionieren in LibreOffice, während Excel gar keine Regeln zeigt. 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:ofundxmlns:msoxlauf demcontent.xml-Root, sonst gibt's für jede Formel#VALUE! - Schreiben Sie Referenzen als
[.A1], behalten Sie jedes$, und schreiben Sie ganze Spalten und Zeilen als[.A:.A]und[.1:.1] - 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 - In calcext gehört der Operator in den Wert (
>3,between(1,10)), und Formelregeln heißenformula-is(...)mit einer Basiszelle - Rechnen Sie beim Import mit einem General-Zahlen-Style ohne
number:decimal-places; HotXLS liest ihn 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