Om een ODS-bestand te maken dat zowel Excel als LibreOffice correct leest, schrijft HotXLS elke formule in OpenFormula-syntax onder een gedeclareerde namespace of:, en schrijft hij elke conditional format voor waarden of formules dubbel: als <style:map> op de stijl van elke gedekte cel, de enige vorm die Excel 16 leest, en als blok calcext:conditional-formats, de vorm die LibreOffice vertrouwt. Elke applicatie negeert de helft die voor de ander bedoeld is, dus een bestand dat er in de ene goed uitziet bewijst niets over de andere
Die laatste zin is de hele les van ODS-interop: een bestand dat een writer zelf perfect terugleest kan nog steeds zo'n bestand zijn dat de ene of de andere doelapplicatie verkeerd interpreteert. Hieronder volgt wat elke applicatie werkelijk accepteert, de opmaak die beide tevreden houdt en de HotXLS-API-aanroepen die die vanuit Delphi produceren
Waarom ziet een ODS-bestand er in de ene applicatie goed uit en in de andere kapot?
Een ODS-bestand ziet er in de ene applicatie goed uit en in de andere kapot omdat Excel en LibreOffice verschillende delen van hetzelfde package lezen. OpenDocument geeft formules en conditional formats meer dan één legale spelling, LibreOffice voegt daarbovenop zijn eigen extensie-namespace toe, en elke consument pakt de subset die hij implementeert. Een writer die tegen slechts één consument is getest, convergeert graag op markup die de ander stilletjes verkeerd leest
Geen van beide applicaties meldt een fout. LibreOffice toont #VALUE! in cellen wiens formules hij niet kon parsen; Excel opent het workbook met de voorwaardelijke opmaak simpelweg afwezig, of met een formule herschreven naar iets dat evalueert tot #NAME? of de constante 0. Een writer die zijn eigen uitvoer rondstuurt ziet er nooit iets van. Het formule-naamruimtegeval is het schoolboekvoorbeeld: een lezer die de of:-prefix als platte tekst matcht, doorstaat elke self round trip terwijl LibreOffice in elke formulercel #VALUE! toont
| Feature | Excel 16 leest | LibreOffice 26.2 leest |
|---|---|---|
Hele kolom geschreven als A:A | Verkeerd gelezen als A:(A) | Getolereerd |
Hele kolom geschreven als [.A:.A] | Ja | Ja |
Conditional formats in <style:map> | Ja, de enige vorm die hij leest | Genegeerd zodra calcext aanwezig is |
Conditional formats in calcext:conditional-formats | Genegeerd | Ja, geprefereerd |
calcext-waarderegel met een attribuut calcext:operator | Genegeerd | Geïmporteerd als "gelijk aan 0" |
calcext-formuleregel gespeld als is-true-formula(...) | Genegeerd | Geïmporteerd als waardevergelijking met 0 |
OpenFormula in ODS: declareer eerst de namespace, en krijg dan de syntax goed
Een formulercel in ODS is voor LibreOffice alleen leesbaar als de of:-prefix in table:formula oplost naar een gedeclareerde XML-naamruimte. De prefix is geen versiering. of: mapt op urn:oasis:names:tc:opendocument:xmlns:of:1.2 en msoxl:, de prefix die HotXLS gebruikt voor formules die zijn OpenFormula-vertaler niet modelleert, mapt op http://schemas.microsoft.com/office/excel/formula. Gebruik een van beide prefixes zonder hem op de content.xml-root te declareren en LibreOffice kan de formulegrammatica helemaal niet identificeren
<!-- Fout: prefix gebruikt, nooit gedeclareerd; LibreOffice toont #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"/>
<!-- Goed: beide formule-naamruimten op de root gedeclareerd -->
<office:document-content
xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>
Met de naamruimte gedeclareerd moet de expressie zelf nog steeds geldige OpenFormula zijn, zoals gedefinieerd in OpenDocument 1.3 Part 4. De valkuilen zitten op de plekken waar Excel-syntaxis en OpenFormula op elkaar lijken maar niet hetzelfde zijn:
- Celverwijzingen staan tussen haakjes met een punt-voorvoegsel, en
$-markeringen zijn deel van de verwijzing:[.$A$1]en[.A$1:.$B2]zijn geldige OpenFormula. Een vertaler die de$laat vallen maakt absolute verwijzingen relatief, en de schade verschijnt pas als iemand de cel kopieert - Hele kolommen en rijen moeten de vorm tussen blokhaken gebruiken:
[.A:.A],[.$A:.$B],[.1:.1],[.$1:.$2]. Een kaleof:=SUM(A:A)wordt door LibreOffice getolereerd, maar Excel 16 opent hem als=SUM(A:(A))met#NAME?en maakt van rijverwijzingen en$A:$Bde constante 0. HotXLS schrijft de vorm tussen blokhaken - Functie-argumenten worden gescheiden door
;, niet door, - Referentie-unies gebruiken de operator
~: ExcelAREAS((A1,B2))wordtAREAS(([.A1]~[.B2])). Die komma naar;vertalen maakt van één unie-argument twee argumenten - Inline-arrays scheiden kolommen met
;en rijen met|: Excel{1,2;3,4}wordt{1;2|3;4}. Mist u de rij-scheider dan krijgt u{1;2;3;4}, één rij met vier waarden
De komma is het lastige deel, want één Excel-teken draagt drie betekenissen. De HotXLS-writer houdt tijdens het vertalen een hakenstack bij: een ( direct na een naam opent een functieaanroep, wiens komma's ; worden; elke andere ( is een groeperingshaak, wiens komma's ~ worden; en komma's binnen {} zijn array-kolomscheiders. Daarmee, plus de gedeclareerde naamruimte, evalueert LibreOffice 26.2 array- en unieformules correct, inclusief INDEX en AREAS over unies
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;
// Wordt geschreven als of:=SUM([.A:.A])
Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
// Wordt geschreven als of:=[.A1]*[.$B$1]; de $-markeringen overleven het
Sheet.Cells[2, 4].Formula := 'A1*$B$1';
Book.SaveAsODS('orders.ods');
finally
Book.Free;
end;
end;
Formules die de vertaler niet modelleert vallen terug op msoxl:= met de Excel-tekst ongewijzigd, en daarom telt ook de msoxl-declaratie mee. Zo'n pad omvat bladgekwalificeerde verwijzingen zoals Sheet2!A1 en gestructureerde tabelverwijzingen. HotXLS leest msoxl:-formules bij import weer terug, dus zijn eigen rondreis houdt de expressie intact, maar hoe een andere applicatie ze behandelt ligt buiten de controle van de writer. Komt een formule waar uw gebruikers van afhangen met het msoxl:-prefix uit de bus, open het bestand dan in beide applicaties voordat u het verstuurt
Waarom ziet Excel conditional formats die alleen als calcext zijn geschreven niet?
Excel 16 ziet calcext conditional formats niet omdat hij ODS conditional formats uitsluitend leest uit <style:map>-kinderen van celstijlen en het blok calcext:conditional-formats volledig negeert. Het experiment dat dat vastlegt is kort: neem een door LibreOffice opgeslagen ODS, verwijder de elementen style:map, en Excel leest nul regels; verwijder in plaats daarvan het calcext-blok, en Excel leest ze nog steeds allemaal. LibreOffice gedraagt zich precies andersom. calcext is de extensie-namespace van LibreOffice, geen deel van de ODF-standaard, en zodra een calcext-regel aanwezig is pakt LibreOffice die en negeert hij de style:map
Alleen calcext schrijven en een ODS-bestand met prima werkende accentuering opent in Excel zonder enkele waarderegel of formulegeregel, dus HotXLS schrijft beide vormen. De style:map-helft gebruikt de conditiegrammatica van het OpenDocument-schema (ODF 1.3 Part 3), met de exacte spellingen die Excel 16 en LibreOffice 26.2 beide produceren bij het opslaan van ODS:
<!-- Versimpeld. Dragerstijl voor elke cel van A1:A50 (twee waarderegels) -->
<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>
<!-- Dragerstijl voor elke cel van C1:C50 (één formuleregel) -->
<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>
De cat met style:map is dat hij op celstijlen leeft, dus per cel. Elke cel in zo'n regelbereik moet een stijl dragen die de map bevat, lege cellen incluis, anders dekt de regel die cel in Excel simpelweg niet. HotXLS kopieert de bestaande opmaakstijl van elke cel, plakt de maps erachteraan en ontdubbelt dragerstijlen op het paar van oorspronkelijke stijl en map-tekst, dus een bereik van 500 cellen met identieke opmaak levert toch één stijl op. De writer trekt de geschreven tabel ook uit tot het regelbereik, wat betekent dat lege staartrijen binnen een regel worden uitgeschreven in plaats van weggegooid. styles.xml draagt ook een lege celstijl Default, dus style:apply-style-name="Default" heeft altijd een doel
De calcext-spelling die LibreOffice werkelijk accepteert
LibreOffice accepteert een calcext-waarderegel alleen als de vergelijkingsoperator deel uitmaakt van de waardetekst, zoals >3 of between(1,10), en een formulegeregel alleen als die gespeld is als formula-is(...). Beide punten gaan makkelijk mis, want de verkeerde spellingen leveren een regel op die zonder foutmelding importeert en daarna de verkeerde cellen markeert
De eerste valkuil is een attribuut calcext:operator naast calcext:value. Het leest logisch, maar het is verzonnen: LibreOffice kent dat attribuut niet en importeert daardoor elke waarderegel als "gelijk aan 0". De tweede is de style:map-spelling is-true-formula(...) in een calcext-conditie stoppen, wat LibreOffice eveneens als celwaardevergelijking met 0 importeert:
<!-- Fout: LibreOffice negeert calcext:operator en importeert gelijk aan 0 -->
<calcext:condition calcext:apply-style-name="CF_Hit"
calcext:operator="greater-than" calcext:value="100"/>
<!-- Goed: de operator reist mee in de waarde -->
<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"/>
<!-- Goed: formuleregels gebruiken formula-is, relatieve refs verankerd aan de basis-cel -->
<calcext:condition calcext:apply-style-name="CF_Dup"
calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])>1)"
calcext:base-cell-address=".C1"/>
De basis-cel is wat relatieve verwijzingen hun betekenis geeft. HotXLS verankert elke regel aan de cel linksboven in zijn eerste bereikgebied, dus een formule geschreven voor C1 evalueert als C2, C3 en zo verder door het bereik, precies zoals in de eigen conditional formatting van Excel. De regelexpressie gaat door dezelfde translator als celformules, dus arrays, unies, hele kolommen en $-markeringen komen eruit in de hierboven beschreven vormen. Aan de Delphi-kant voegt u regels toe precies zoals u dat voor een .xlsx-bestand zou doen
uses
lxHandleX;
procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
Idx: Integer;
Opts: TODSExportOptions;
begin
// Waarderegels: style:map cell-content()>100 plus calcext-waarde ">100"
Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: lichtrood
Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);
// Formuleregel in Excel-syntax (komma-scheidingstekens, relatief aan C1):
// style:map is-true-formula(...) en calcext formula-is(...)
Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: lichtgeel
Opts := TODSExportOptions.Create;
try
Opts.Generator := 'OrderExport 3.1';
Book.SaveAsODS('orders.ods', Opts);
finally
Opts.Free;
end;
end;
ODS van Excel en LibreOffice teruglezen in Delphi
Wanneer HotXLS een ODS-bestand opent, accepteert zijn reader beide dialecten van conditional formats en beide calcext-spellingen, en hij telt een regel niet dubbel wanneer het bestand haar in beide vormen bevat. Echte bestanden komen van drie schrijvers, elk met eigen gewoontes:
- Oude en nieuwe calcext. Bestanden met een attribuut
calcext:operatorgaan nog steeds door de legacy-parse. Formulecondities worden herkend alsformula-is(...)of alsis-true-formula(...) - Excel's style:map-spelling. Excel plaatst
of:vóór condities, zoals inof:cell-content-is-between(1,10), en laat bij waarderegels de basiscel weg. Beide worden geaccepteerd - Lege cellen. Excel en LibreOffice zetten de map voor lege cellen beide op de kolomdefaultstijl in plaats van op een cel, dus de lezer lost kolomdefaultstijlen op voor herhaalde cellen voordat hij maps verzamelt
- Gebieden herbouwen. Maps worden per cel verzameld, dus na het lezen van een blad voegt de lezer cellen die dezelfde conditie en basiscel delen weer samen tot bereiken, eerst langs elke rij en daarna omlaag over passende kolomspansen, en gooit hij elke regel weg die al uit calcext was gelezen
Nummerstijlen vragen dezelfde zorg als regels. Excel 16 en LibreOffice 26.2 schrijven het General-formaat beide als een nummerstijl wiens element number:number geen number:decimal-places heeft, typisch <number:number number:min-integer-digits="1"/>. Lees de ontbrekende count als twee vaste decimalen en elke waarde in de stijl Default importeert met 0.00, dus 1.5 wordt getoond als 1.50. HotXLS mapt zo'n kale number-element zonder decimalen, zonder minimale decimalen, zonder groepering en met hooguit één geheel-cijfer op General, en een loutere General laat de cel zonder nummerformaat. Tekst eromheen blijft staan, zoals in General" kg", en gegroepeerde getallen worden niet op General gemapt omdat Excel geen gegroepeerd General-formaat heeft
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]; // de Sheets-indexer is 1-based
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;
// Een cel in Excel's General-stijl komt terug zonder nummerformaat,
// dus niet '0.00'
Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
finally
Book.Free;
end;
end;
Regelformules komen terug in Excel-syntax met komma-scheidingstekens, dezelfde vorm die u aan AddCondFormatExpression zou doorgeven, dus een door HotXLS geschreven regel leest terug als de identieke string. Voor het bredere beeld van wat het ODS-importpad houdt en laat vallen, zie de HotXLS ODS open- en opslag-round-trip-gids; voor hoe herhaalde rijen van Excel en LibreOffice bij import worden uitgeklapt, zie ODS herhaalde rijen als rijhoogte-runs
Welke soorten voorwaardelijke opmaak bereiken beide applicaties?
De dubbele-opmaak-aanpak dekt waardevergelijkingsregels en formulegeregels, de twee soorten die beide applicaties lezen. De andere soorten volgen eenvoudige regels:
- Kleurschalen en databalken worden als calcext-elementen geschreven, de vorm die LibreOffice leest, dus reken op LibreOffice als hun ODS-consument
- Andere regelsoorten, zoals pictogramverzamelingen, tekstregels, top-N, boven-gemiddelde en duplicaatregels, kun je voor een ODS-export het beste herschrijven als formulegeregel waar de conditie dat toelaat. Zo'n tekstregel wordt bijvoorbeeld
ISNUMBER(SEARCH("late",B2))overB2:B200, en die bereikt beide applicaties - Hele-kolom- en hele-rij-regels zoals
C:Cworden alleen over het tabelgebied gelegd dat werkelijk is geschreven, niet over alle 1.048.576 rijen, dus Excel ziet zulke regels alleen op cellen die in het bestand bestaan - Bestanden met alleen style:map. Heeft een bestand geen calcext-blok, dan interpreteert HotXLS relatieve verwijzingen in formulegeregels vanuit de linkerbovenhoek van het herbouwde bereik, niet door te schuiven vanaf de opgegeven basiscel
- Overlappende regels vanuit LibreOffice. Als één cel door meerdere regels wordt gedekt, schrijft LibreOffice alleen de map van de eerste regel op hem. Zo'n bestanden zijn niet volledig uit
style:mapalleen te lezen, wat een extra reden is dat de lezer calcext verkiest zodra beide bestaan
De procesregel weegt zwaarder dan al dit soort dingen. Elke valkuil in dit artikel glipt langs een rondreis die ODS schrijft en met dezelfde bibliotheek terugleest, en enkele passeren ook een handmatige controle in de verkeerde applicatie: hele-kolomformules werken in LibreOffice terwijl Excel #NAME? toont, en als alleen calcext geschreven formulegeregels werken in LibreOffice terwijl Excel helemaal geen regels toont. Als ODS-interop een eis is, is de acceptatietest het bestand openen in Excel en in LibreOffice en vergelijken wat elk toont. Dezelfde discipline geldt voor de stijlen waar regels naar wijzen; het HotXLS-artikel over voorwaardelijke opmaak en stijlen beschrijft hoe accentueringsstijlen aan de workbookkant worden gedefinieerd
Snelnaslag: ODS die beide applicaties lezen
- Declareer
xmlns:ofenxmlns:msoxlop decontent.xml-root, anders toont LibreOffice voor elke formule#VALUE! - Schrijf verwijzingen als
[.A1], behoud elke$en schrijf hele kolommen en rijen als[.A:.A]en[.1:.1] - Gebruik
;voor argumenten,~voor referentie-unies en|tussen inline-array-rijen - Schrijf elke waarderegel of formulegeregel als een
<style:map>op de stijl van elke gedekte cel voor Excel, en als calcext-conditie voor LibreOffice - Zet in calcext de operator in de waarde (
>3,between(1,10)) en spel formulegeregelsformula-is(...)met een basiscel - Verwacht bij import een General-nummerstijl zonder
number:decimal-places; HotXLS leest hem als General - Verifieer elk nieuw exportprofiel door het bestand in zowel Excel als LibreOffice te openen, nooit in maar één van de twee
HotXLS is een native spreadsheetbibliotheek voor Delphi en C++Builder die XLS, XLSX en ODS leest en schrijft zonder Excel of LibreOffice geïnstalleerd; volledige source, de functielijst en licentiëring staan op de HotXLS Delphi spreadsheet component-pagina