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 les achter zes HotXLS-releases tussen v2.384.55 en v2.384.72. Elke fix begon met een bestand dat HotXLS schreef, foutloos teruglas, en dat een van de twee doelapplicaties verkeerd las. Wat volgt is wat elke applicatie werkelijk accepteert, de markup die beide tevreden stelt, en de HotXLS-API-aanroepen die dat 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 waarvan hij de formules niet kon parsen; Excel opent het workbook met de conditional formats er simpelweg niet, of met een formule die herschreven is naar iets dat evalueert tot #NAME? of de constante 0. Een writer die zijn eigen output round-tript ziet hiervan nooit iets. HotXLS trapte precies in die val met de formula-namespace: zijn reader matchte het prefix of: als platte tekst, dus elke self round trip slaagde terwijl LibreOffice in elke formulecel #VALUE! toonde

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 formulecel in ODS is door LibreOffice alleen leesbaar wanneer het prefix of: in table:formula uitkomt bij een gedeclareerde XML-namespace. Het prefix is geen versiering. of: verwijst naar urn:oasis:names:tc:opendocument:xmlns:of:1.2, en msoxl:, het prefix dat HotXLS gebruikt voor formules die zijn OpenFormula-translator niet modelleert, verwijst naar http://schemas.microsoft.com/office/excel/formula. Vóór v2.384.56 gebruikte de root van content.xml beide prefixes zonder ze te declareren, en LibreOffice kon de formulegrammatica helemaal niet herkennen

<!-- Vóór v2.384.56: 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"/>

<!-- Sinds v2.384.56: beide formula-namespaces gedeclareerd op de root -->
<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 namespace op orde moet de expressie zelf nog steeds geldige OpenFormula zijn, zoals gedefinieerd in OpenDocument 1.3 Part 4. De valkuilen zitten op de plekken waar de syntax van Excel en OpenFormula op elkaar lijken maar niet hetzelfde zijn:

• Celverwijzingen staan tussen haken met een punt ervoor, en $-markeringen horen bij de verwijzing: [.$A$1] en [.A$1:.$B2] zijn geldige OpenFormula. Vóór v2.384.55 liet de HotXLS-writer elke $ vallen, dus absolute verwijzingen kwamen relatief terug en ging het pas mis zodra iemand de cel kopieerde
• Hele kolommen en rijen moeten de gehaakte vorm [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2] gebruiken. Een kaal of:=SUM(A:A) wordt door LibreOffice getolereerd, maar Excel 16 opent het als =SUM(A:(A)) met #NAME?, en maakt van rijverwijzingen en $A:$B de constante 0. HotXLS schrijft de gehaakte vorm sinds v2.384.65
• Functieargumenten 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}. Vóór v2.384.55 produceerde HotXLS {1;2;3;4}, één rij van vier waarden

De komma is het lastige deel, want één Excel-teken draagt er drie betekenissen. Sinds v2.384.55 houdt de HotXLS-writer tijdens het vertalen een haakopeningsstapel bij: een ( direct achter een naam opent een functieaanroep waarvan de komma's ; worden; elke andere ( is een groeperingshaak waarvan de komma's ~ worden; en komma's binnen {} zijn kolomscheiders van arrays. Daarmee en met de namespacefix evalueerde LibreOffice 26.2 alle acht array- en unie-probeformules correct, INDEX en AREAS over unies inbegrepen

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;

    // Geschreven als of:=SUM([.A:.A]) sinds v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Geschreven als of:=[.A1]*[.$B$1]; de $-markeringen overleven sinds v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formules die de translator niet modelleert vallen terug op msoxl:= met de Excel-tekst ongewijzigd, en daarom telt de msoxl-declaratie ook mee. In de huidige writer omvat dat pad bladgekwalificeerde verwijzingen zoals Sheet2!A1 en gestructureerde tabelverwijzingen. HotXLS leest msoxl:-formules bij import weer terug, dus zijn eigen round trip houdt de expressie intact, maar hoe een andere applicatie ze behandelt ligt buiten de controle van de writer. Komt een formule waar uw consumenten van afhangen met het prefix msoxl: 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

Vóór v2.384.69 schreef HotXLS alleen calcext, dus een ODS-bestand met prima highlighting opende in Excel zonder enige waarderegel of formuleregel. HotXLS schrijft nu beide vormen. De helft style:map 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 wanneer ze ODS opslaan:

<!-- 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 vang van style:map is dat hij op celstijlen leeft, dus per cel geldt. Elke cel in het bereik van de regel moet een stijl dragen die de map bevat, lege cellen inbegrepen, anders dekt de regel die cel in Excel simpelweg niet. HotXLS kopieert de bestaande opmaakstijl van elke cel, voegt de maps toe en dedupliceert dragerstijlen op het paar van originele stijl en maptekst, dus een bereik van 500 cellen met identieke opmaak levert nog steeds één stijl op. De writer breidt de geschreven tabel ook uit tot het bereik van de regel, wat betekent dat lege staartrijen binnen een regel worden ge-emit in plaats van weggegooid. Sinds v2.384.69 draagt styles.xml 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 wanneer de vergelijkingsoperator deel uitmaakt van de waardetekst, zoals >3 of between(1,10), en een formuleregel alleen wanneer die gespeld is als formula-is(...). Beide punten kostten HotXLS een release, want de verkeerde spellingen leveren een regel op die zonder fout importeert en daarna de verkeerde cellen matcht

De eerste fout was een attribuut calcext:operator naast calcext:value. Het leest logisch, maar het is verzonnen: LibreOffice kent dat attribuut niet, dus hij importeerde elke waarderegel als gelijk aan 0. De tweede was het plaatsen van is-true-formula(...), de spelling van style:map, in een calcext-conditie, die LibreOffice eveneens als celwaardevergelijking met 0 importeerde. De formulefix kwam uit in v2.384.66 en de waardefix in v2.384.69:

<!-- 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"/>

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, waaronder ODS geschreven door HotXLS vóór v2.384.69, gaan nog steeds door de legacy-parse. Formulecondities worden herkend als formula-is(...) of als is-true-formula(...)
• De style:map-spelling van Excel. Excel zet of: vóór condities, zoals in of:cell-content-is-between(1,10), en laat de basis-cel weg bij waarderegels. Beide worden geaccepteerd
• Lege cellen. Excel en LibreOffice zetten de map voor lege cellen beide op de kolom-defaultstijl in plaats van op een cel, dus de reader lost kolom-defaultstijlen voor herhaalde cellen op voordat hij maps verzamelt
• Gebied herbouwen. Maps worden per cel verzameld, dus nadat een werkblad is gelezen voegt de reader cellen die dezelfde conditie en basis-cel delen weer samen tot bereiken, eerst langs elke rij en daarna omlaag over overeenkomende kolomspannen, en gooit regels die al uit calcext zijn gelezen weg

De fix in v2.384.72 gaat over getalstijlen, niet over regels. Excel 16 en LibreOffice 26.2 schrijven het formaat General beide als een getalstijl waarvan het element number:number geen number:decimal-places heeft, typisch <number:number number:min-integer-digits="1"/>. De HotXLS-reader behandelde de ontbrekende telling als twee vaste decimalen, dus elke waarde in de stijl Default werd geïmporteerd met 0.00 en 1.5 werd getoond als 1.50. Sinds v2.384.72 wijst een kaal getalelement zonder decimalen, zonder minimale decimalen, zonder groepering en met hooguit één cijfer voor de komma naar General, en een loutere General laat de cel zonder enige getalnotatie. Tekst eromheen blijft staan, zoals in General" kg", en gegroepeerde getallen houden de vorige toewijzing 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-indexeerder 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 de General-stijl van Excel leest terug zonder getalnotatie
    // sinds v2.384.72, in plaats van '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

Wat zijn de grenzen van de HotXLS ODS conditional format-interop?

De dubbele-markupbenadering dekt waardevergelijkingsregels en formuleregels, en houdt daar op. Al het andere is eenzijdig of wordt helemaal niet geschreven:

• Kleurschalen en databalken worden alleen als calcext-elementen geschreven, dus LibreOffice toont ze en Excel niet
• Andere regelsoorten, zoals icon sets, tekstregels, top-N, boven-gemiddelde en duplicaatregels, hebben geen ODS-output in de huidige writer. Een tekstregel laat zich meestal herschrijven als formuleregel, bijvoorbeeld ISNUMBER(SEARCH("late",B2)) over B2:B200, en bereikt dan beide applicaties
• Hele-kolom- en hele-rij-regels zoals C:C worden alleen gelegd over het tabelgebied dat werkelijk is geschreven, niet over alle 1.048.576 rijen, dus Excel ziet deze 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 formuleregels vanuit de hoek linksboven van het herbouwde bereik, niet door te verschuiven vanaf de opgegeven basis-cel
• Overlappende regels uit LibreOffice. Is één cel door meerdere regels gedekt, dan schrijft LibreOffice alleen de map van de eerste regel erop. Zulke bestanden zijn niet volledig uit style:map alleen te lezen, wat nog een reden is waarom de reader calcext prefereert zodra beide bestaan

De procesgrens is belangrijker dan elk van deze punten. De defecten achter deze releases glipten langs round trips die ODS schreven en met HotXLS teruglazen, en sommigen zouden ook een handmatige controle in de verkeerde applicatie hebben doorstaan: hele-kolomformules werkten in LibreOffice terwijl Excel #NAME? toonde, en vanaf v2.384.66 werkten formuleregels in LibreOffice terwijl Excel tot v2.384.69 nog steeds helemaal geen regels toonde. Is ODS-interop een eis, dan is de acceptatietest het bestand in Excel en in LibreOffice openen en vergelijken wat elk toont. Dezelfde discipline geldt voor de stijlen waarnaar regels wijzen; het HotXLS-artikel over conditional formatting en stijlen behandelt hoe highlight-stijlen aan de workbookkant worden gedefinieerd

Snelnaslag: ODS die beide applicaties lezen

• Declareer xmlns:of en xmlns:msoxl op de root van content.xml, of LibreOffice toont #VALUE! bij elke formule (HotXLS sinds v2.384.56)
• Schrijf verwijzingen als [.A1], houd elke $ vast, en schrijf hele kolommen en rijen als [.A:.A] en [.1:.1] (sinds v2.384.55 en v2.384.65)
• Gebruik ; voor argumenten, ~ voor referentie-unies en | tussen rijen van inline arrays
• Schrijf elke waarde- of formuleregel als <style:map> op de stijl van elke gedekte cel voor Excel, en als calcext-conditie voor LibreOffice (sinds v2.384.69)
• Zet in calcext de operator in de waarde (>3, between(1,10)) en spel formuleregels formula-is(...) met een basis-cel (sinds v2.384.66 en v2.384.69)
• Verwacht bij import een getalstijl General zonder number:decimal-places; HotXLS leest haar als General sinds v2.384.72
• Controleer elk nieuw exportprofiel door het bestand in zowel Excel als LibreOffice te openen, nooit in slechts één van beide

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