HotXLS ODS-interoperabilitet: Formler Excel kan lese

Skal du lage en ODS-fil som både Excel og LibreOffice leser riktig, skriver HotXLS hver formel i OpenFormula-syntaks under et deklarert of:-navnerom, og skriver hver verdi- eller formelregel for betinget formatering to ganger: som en <style:map> på stilen til hver dekket celle, som er den eneste formen Excel 16 leser, og som en calcext:conditional-formats-blokk, som er formen LibreOffice stoler på. Hver applikasjon ignorerer halvparten som er ment for den andre, så en fil som vises riktig i én av dem beviser ingenting om den andre

Den siste setningen er lærdommen bak seks HotXLS-utgivelser mellom v2.384.55 og v2.384.72. Hver fiks begynte med en fil HotXLS skrev, som leste perfekt tilbake, og som én av de to mål-applikasjonene fikk feil. Det som følger er hva hver applikasjon faktisk godtar, oppmerkingen som tilfredsstiller begge, og HotXLS API-kallene som produserer den fra Delphi

Hvorfor ser en ODS-fil fin ut i én applikasjon og ødelagt ut i den andre?

En ODS-fil ser fin ut i én applikasjon og ødelagt ut i den andre fordi Excel og LibreOffice leser ulike deler av samme pakke. OpenDocument gir formler og betinget formatering mer enn én lovlig staving, LibreOffice legger sitt eget utvidelsesnavnerom oppå, og hver konsument plukker den delmengden den implementerer. En skriver testet mot bare én konsument vil gledelig konvergere mot oppmerking den andre stille misleser

Ingen av applikasjonene rapporterer en feil. LibreOffice viser #VALUE! i celler hvis formler den ikke kunne parse; Excel åpner arbeidsboken med den betingede formateringen rett og slett fraværende, eller med en formel omskrevet til noe som evaluerer til #NAME? eller konstanten 0. En skriver som rundturer sitt eget utdata, ser aldri noe av dette. HotXLS traff nøyaktig den fallen med formelnavnerommet: leseren matchet of:-prefikset som ren tekst, så hver egen rundtur passerte mens LibreOffice viste #VALUE! i hver formelcelle

Funksjon	Excel 16 leser	LibreOffice 26.2 leser
Hel kolonne skrevet som A:A	Mislest som A:(A)	Tolerert
Hel kolonne skrevet som [.A:.A]	Ja	Ja
Betinget formatering i <style:map>	Ja, den eneste formen den leser	Ignorert når calcext finnes
Betinget formatering i calcext:conditional-formats	Ignorert	Ja, foretrukket
calcext verdiregel med en calcext:operator-attributt	Ignorert	Importert som "lik 0"
calcext formelregel stavet is-true-formula(...)	Ignorert	Importert som en verdissammenligning med 0

OpenFormula i ODS: deklarer navnerommet, få så syntaksen riktig

En formelcelle i ODS er bare lesbar for LibreOffice når of:-prefikset i table:formula løses til et deklarert XML-navnerom. Prefikset er ikke pynt. of: mappes til urn:oasis:names:tc:opendocument:xmlns:of:1.2, og msoxl:, prefikset HotXLS bruker for formler OpenFormula-oversetteren ikke modellerer, mappes til http://schemas.microsoft.com/office/excel/formula. Før v2.384.56 brukte content.xml-roten begge prefiksene uten å deklarere dem, og LibreOffice kunne ikke identifisere formelgrammatikken i det hele tatt

<!-- Før v2.384.56: prefiks brukt, aldri deklarert; LibreOffice viser #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"/>

<!-- Siden v2.384.56: begge formel-navnerommene deklarert på roten -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Med navnerommet på plass må uttrykket selv fortsatt være gyldig OpenFormula, som definert i OpenDocument 1.3 Part 4. Fellene er stedene der Excel-syntaks og OpenFormula ligner, men ikke er det samme:

• Cellereferanser er klammete og prikk-prefikset, og $-markørene er del av referansen: [.$A$1] og [.A$1:.$B2] er gyldig OpenFormula. Før v2.384.55 slapp HotXLS-skriveren enhver $, så absolutte referanser kom tilbake relative og gikk bare galt når noen kopierte cellen
• Hele kolonner og rader må bruke den klammete formen [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Et bart of:=SUM(A:A) tolereres av LibreOffice, men Excel 16 åpner det som =SUM(A:(A)) med #NAME?, og gjør radreferanser og $A:$B om til konstanten 0. HotXLS skriver den klammete formen siden v2.384.65
• Funksjonsargumenter skilles med ;, ikke ,
• Referanseunioner bruker ~-operatoren: Excel AREAS((A1,B2)) blir AREAS(([.A1]~[.B2])). Å oversette det kommaet til ; gjør i stedet ett union-argument til to argumenter
• Innebygde matriser skiller kolonner med ; og rader med |: Excel {1,2;3,4} blir {1;2|3;4}. Før v2.384.55 produserte HotXLS {1;2;3;4}, én enkelt rad med fire verdier

Kommaet er den vanskelige delen, fordi ett Excel-tegn bærer tre betydninger. Siden v2.384.55 følger HotXLS-skriveren med på en parentesstakk mens den oversetter: en ( rett etter et navn åpner et funksjonskall, hvis kommaer blir ;; enhver annen ( er en grupperingsparentes, hvis kommaer blir ~; og kommaer inni {} er matrise-kolenneskillere. Med det og navneromsfiksen evaluerte LibreOffice 26.2 alle de åtte matrise- og union-probeformlene riktig, INDEX og AREAS over unioner inkludert

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;

    // Skrevet som of:=SUM([.A:.A]) siden v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Skrevet som of:=[.A1]*[.$B$1]; $-markørene overlever siden v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formler oversetteren ikke modellerer, faller tilbake til msoxl:= med Excel-teksten uendret, noe som er grunnen til at msoxl-deklarasjonen også betyr noe. I dagens skriver inkluderer den stien ark-kvalifiserte referanser som Sheet2!A1 og strukturerte tabellreferanser. HotXLS leser msoxl:-formler tilbake ved import, så dens egen rundtur holder uttrykket intakt, men hvordan en annen applikasjon behandler dem ligger utenfor skriverens kontroll. Kommer en formel konsumentene dine er avhengige av ut med msoxl:-prefikset, åpner du filen i begge applikasjonene før du sender den

Hvorfor ser Excel ikke betinget formatering skrevet bare som calcext?

Excel 16 ser ikke calcext-betinget formatering fordi den leser ODS-betinget formatering utelukkende fra <style:map>-barn av celletiler og ignorerer calcext:conditional-formats-blokken fullstendig. Eksperimentet som avgjør det, er kort: ta en ODS lagret av LibreOffice, slett style:map-elementene, og Excel leser null regler; slett i stedet calcext-blokken, og Excel leser fortsatt alle. LibreOffice oppfører seg omvendt. calcext er LibreOffice utvidelsesnavnerom, ikke en del av ODF-standarden, og når en calcext-regel finnes, tar LibreOffice den og ignorerer style:map

Før v2.384.69 skrev HotXLS bare calcext, så en ODS-fil med fullt brukbar utheving åpnet i Excel uten noen verdiregler og ingen formelregler i det hele tatt. HotXLS skriver nå begge former. style:map-halvdelen bruker betingelsesgrammatikken til OpenDocument-skjemaet (ODF 1.3 Part 3), med de nøyaktige stavingene Excel 16 og LibreOffice 26.2 begge produserer når de lagrer ODS:

<!-- Forenklet. Bærerstil for hver celle av A1:A50 (to verdiregler) -->
<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>

<!-- Bærerstil for hver celle av C1:C50 (én 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>

Haken ved style:map er at den bor på celletiler, så den er per celle. Hver celle i regelens område må bære en stil som holder kartet, tomme celler inkludert, ellers dekker regelen den cellen rett og slett ikke i Excel. HotXLS kopierer hver cells eksisterende formatstil, legger til kartene, og dedupliserer bærerstiler etter paret av opprinnelig stil og karttekst, så et område på 500 celler med identisk formatering fortsatt produserer én stil. Skriveren utvider også den skrevne tabellen til regelens område, noe som betyr at tomme halerader inne i en regel skrives ut i stedet for å sløyfes. Siden v2.384.69 bærer styles.xml også en tom Default-cellestil, så style:apply-style-name="Default" alltid har et mål

Calcext-stavingen LibreOffice faktisk godtar

LibreOffice godtar en calcext verdiregel bare når sammenligningsoperatoren er del av verditeksten, som >3 eller between(1,10), og en formelregel bare når den er stavet formula-is(...). Begge punktene kostet HotXLS en utgivelse, fordi de gale stavingene produserer en regel som importeres uten feil og deretter matcher feil celler

Den første feilen var en calcext:operator-attributt ved siden av calcext:value. Den leser naturlig, men den er oppfunnet: LibreOffice kjenner ikke den attributten, så den importerte hver verdiregel som "lik 0". Den andre var å putte is-true-formula(...), style:map-stavingen, inn i en calcext-betingelse, som LibreOffice importerte som en celleverdi-sammenligning med 0 også. Formelfiksen kom i v2.384.66 og verdifiksen i v2.384.69:

<!-- Feil: LibreOffice ignorerer calcext:operator og importerer «lik 0» -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Riktig: operatoren reiser inne i verdien -->
<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"/>

<!-- Riktig: formelregler bruker formula-is, relative referanser forankret i basiscellen -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Basiscellen er det som gir relative referanser mening. HotXLS forankrer hver regel i øverste venstre celle av sitt første områdeareal, så en formel skrevet for C1 evalueres som C2, C3 og så videre nedover området, nøyaktig som i Excels egen betingede formatering. Regeluttrykket går gjennom samme oversetter som celleformler, så matriser, unioner, hele kolonner og $-markører kommer ut i formene beskrevet over. På Delphi-siden legger du til regler nøyaktig som du ville gjort for en .xlsx-fil

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Verdiregler: style:map cell-content()>100 pluss calcext verdi ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: lys rød

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

  // Formelregel i Excel-syntaks (komma-separatorer, relativ til C1):
  // style:map is-true-formula(...) pluss calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: lys gul

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

Å lese ODS fra Excel og LibreOffice tilbake til Delphi

Når HotXLS åpner en ODS-fil, godtar leseren begge betinget formatering-dialektene og begge calcext-stavingene, og den teller ikke en regel dobbelt når filen bærer den i begge former. Virkelige filer kommer fra tre skrivere, hver med sine vaner:

• Gammel og ny calcext. Filer med en calcext:operator-attributt, inkludert ODS skrevet av HotXLS før v2.384.69, går fortsatt gjennom den gamle parsingen. Formelbetingelser gjenkjennes som enten formula-is(...) eller is-true-formula(...)
• Excels style:map-staving. Excel setter of: foran betingelser, som i of:cell-content-is-between(1,10), og utelater basiscellen på verdiregler. Begge godtas
• Tomme celler. Excel og LibreOffice legger begge kartet for tomme celler på kolonnens standardstil i stedet for på en celle, så leseren løser kolonne-standardstiler for gjentatte celler før den samler kart
• Ombygging av områder. Kart samles per celle, så etter at et ark er lest, slår leseren sammen celler som deler samme betingelse og basiscelle tilbake til områder, først over hver rad og så nedover matchende kolonnespenn, og dropper enhver regel allerede lest fra calcext

v2.384.72-fiksen gjelder tallstiler, ikke regler. Excel 16 og LibreOffice 26.2 skriver begge General-formatet som en tallstil hvis number:number-element ikke har number:decimal-places, typisk <number:number number:min-integer-digits="1"/>. HotXLS-leseren behandlet den manglende tellingen som to faste desimaler, så hver verdi i Default-stilen importerte med 0.00 og 1.5 viste som 1.50. Siden v2.384.72 mappes et bart tallelement uten desimaler, uten minimumsdesimaler, uten gruppering og med høyst ett heltallssiffer til General, og en enslig General etterlater cellen uten noe tallformat i det hele tatt. Tekst rundt den beholdes, som i General" kg", og grupperte tall beholder den tidligere mappeningen fordi Excel ikke har noe gruppert 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]; // Sheets-indeksatoren er 1-basert
    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;

    // En celle i Excels General-stil leses tilbake uten noe tallformat
    // siden v2.384.72, i stedet for '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Regelformler kommer tilbake i Excel-syntaks med komma-separatorer, samme form du ville sendt til AddCondFormatExpression, så en regel skrevet av HotXLS leses tilbake som den identiske strengen. For det større bildet av hva ODS-importstien beholder og dropper, se HotXLS-guiden om ODS-åpning- og lagring-rundtur; for hvordan gjentatte rader fra Excel og LibreOffice ekspanderes ved import, se ODS-gjentatte rader som radhøyde-løp

Hva er grensene for HotXLS ODS-betinget formatering-interoperabilitet?

Dobbeltmerknings-tilnærmingen dekker verdissammenligningsregler og formelregler, og stopper der. Alt annet er ensidig eller ikke skrevet i det hele tatt:

• Fargeskalaer og databar skrives bare som calcext-elementer, så LibreOffice viser dem og Excel gjør det ikke
• Andre regeltyper, som ikonsett, tekstregler, topp-N, over-gjennomsnitt- og duplikatregler, har ingen ODS-utdata i dagens skriver. En tekstregel kan vanligvis omformuleres som en formelregel, for eksempel ISNUMBER(SEARCH("late",B2)) over B2:B200, som så når begge applikasjonene
• Helkolonne- og helerad-regler som C:C legges bare over tabellorådet som faktisk er skrevet, snarere enn over alle 1 048 576 radene, så Excel ser disse reglene bare på celler som finnes i filen
• Filer med bare style:map. Har en fil ingen calcext-blokk, tolker HotXLS relative referanser i formelregler fra øverste venstre hjørne av det ombygde området, ikke ved å forskyve fra den oppgitte basiscellen
• Overlappende regler fra LibreOffice. Er én celle dekket av flere regler, skriver LibreOffice bare den første regelens kart på den. Slike filer kan ikke leses fullstendig fra style:map alene, noe som er enda en grunn til at leseren foretrekker calcext når begge finnes

Prosessgrensen betyr mer enn noen av disse. Defektene bak disse utgivelsene kom seg forbi rundturer som skrev ODS og leste den tilbake med HotXLS, og noen ville også passert en manuell sjekk i feil applikasjon: helkolonne-formler fungerte i LibreOffice mens Excel viste #NAME?, og fra v2.384.66 fungerte formelregler i LibreOffice mens Excel fortsatt viste ingen regler i det hele tatt frem til v2.384.69. Er ODS-interoperabilitet et krav, er akseptansten å åpne filen i Excel og i LibreOffice og sammenligne hva hver viser. Samme disiplin gjelder stilene reglene peker på; HotXLS-artikkelen om betinget formatering og stiler dekker hvordan uthevingsstiler defineres på arbeidsbok-siden

Hurtigreferanse: ODS begge applikasjoner leser

• Deklarer xmlns:of og xmlns:msoxl på content.xml-roten, ellers viser LibreOffice #VALUE! for hver formel (HotXLS siden v2.384.56)
• Skriv referanser som [.A1], behold hver $, og skriv hele kolonner og rader som [.A:.A] og [.1:.1] (siden v2.384.55 og v2.384.65)
• Bruk ; for argumenter, ~ for referanseunioner, og | mellom innebygde matriserader
• Skriv hver verdi- eller formelregel som en <style:map> på hver dekket celles stil for Excel, og som en calcext-betingelse for LibreOffice (siden v2.384.69)
• I calcext, legg operatoren i verdien (>3, between(1,10)) og stav formelregler formula-is(...) med en basiscelle (siden v2.384.66 og v2.384.69)
• Forvent en General-tallstil uten number:decimal-places ved import; HotXLS leser den som General siden v2.384.72
• Verifiser hver nye eksportprofil ved å åpne filen i både Excel og LibreOffice, aldri i bare én av dem

HotXLS er et nativt Delphi- og C++Builder-regneark-bibliotek som leser og skriver XLS, XLSX og ODS uten Excel eller LibreOffice installert; full kildekode, funksjonslisten og lisensiering finner du på siden for HotXLS Delphi regnearkkomponent