Technisch artikel

HotXLS ODS-interop: formules en regels die Excel kan lezen

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

FeatureExcel 16 leestLibreOffice 26.2 leest
Hele kolom geschreven als A:AVerkeerd gelezen als A:(A)Getolereerd
Hele kolom geschreven als [.A:.A]JaJa
Conditional formats in <style:map>Ja, de enige vorm die hij leestGenegeerd zodra calcext aanwezig is
Conditional formats in calcext:conditional-formatsGenegeerdJa, geprefereerd
calcext-waarderegel met een attribuut calcext:operatorGenegeerdGeïmporteerd als "gelijk aan 0"
calcext-formuleregel gespeld als is-true-formula(...)GenegeerdGeï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 kale of:=SUM(A:A) wordt door LibreOffice getolereerd, maar Excel 16 opent hem als =SUM(A:(A)) met #NAME? en maakt van rijverwijzingen en $A:$B de constante 0. HotXLS schrijft de vorm tussen blokhaken
  • Functie-argumenten worden gescheiden door ;, niet door ,
  • Referentie-unies gebruiken de operator ~: Excel AREAS((A1,B2)) wordt AREAS(([.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

HotXLS-diagram van de haakopeningsstapel die Excel-komma's naar OpenFormula vertaalt: een haak direct achter een naam opent een functieaanroep waarvan de komma's puntkomma's worden, elke andere haak groepeert en krijgt komma's die de unie-operator tilde worden, en komma's binnen accolades zijn kolomscheiders van arrays, zoals bij AREAS over de unie van A1 en B2
De komma draagt drie betekenissen in de Excel-syntax, en alleen de lopende haakopeningsstapel houdt ze uit elkaar; vertaal een unie-komma naar een puntkomma en één argument wordt stilletjes twee
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

HotXLS-diagram met dubbel kanaal voor ODS conditional formats: elke waarde- of formuleregel wordt geschreven als style map op de stijl van elke gedekte cel, de enige vorm die Excel 16 leest, en als calcext conditional formats-blok met de operator in de waarde, de vorm die LibreOffice prefereert, terwijl elke applicatie de andere spelling stilletjes negeert
Excel leest style maps en negeert calcext, LibreOffice prefereert calcext en laat de maps vallen, en geen van beide toont een fout; beide spellingen uit één HotXLS-aanroep schrijven is de enige manier waarop een bestand in beide klopt

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()&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>

<!-- 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])&gt;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="&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"/>

<!-- 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])&gt;1)"
                   calcext:base-cell-address=".C1"/>
HotXLS-diagram met de tegenstelling tussen verkeerde en goede calcext-conditie-spellingen: een calcext-operator-attribuut is verzonnen en importeert elke waarderegel als gelijk aan 0, de operator hoort in de waarde thuis zoals greater than 100 of between 1 and 10, en formulegeregels moeten formula-is zeggen, geankerd op een basiscel, in plaats van de style map-spelling is-true-formula
Beide verkeerde spellingen importeren zonder foutmelding en markeren daarna de verkeerde cellen; een regel die als gelijk aan 0 leest accentueert niets dat u wilde; de juiste spelling is de operator in de waarde en formula-is voor expressies

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:operator gaan nog steeds door de legacy-parse. Formulecondities worden herkend als formula-is(...) of als is-true-formula(...)
  • Excel's style:map-spelling. Excel plaatst of: vóór condities, zoals in of: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)) over B2:B200, en die bereikt beide applicaties
  • Hele-kolom- en hele-rij-regels zoals C:C worden 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:map alleen 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:of en xmlns:msoxl op de content.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 formulegeregels formula-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