HotXLS ODS Interop: Formler og regler Excel kan læse

For at producere en ODS-fil, som både Excel og LibreOffice læser korrekt, skriver HotXLS hver formel i OpenFormula-syntaks under et deklareret of:-namespace og skriver hver værdi- eller formel-betinget formatering to gange: som en <style:map> på stilen for hver dækket celle, den eneste form, Excel 16 læser, og som en calcext:conditional-formats-blok, den form, LibreOffice stoler på. Hvert program ignorerer halvdelen, der er ment til det andet, så en fil, der vises korrekt i det ene, beviser ingenting om det andet

Den sidste sætning er læringen bag seks HotXLS-udgivelser mellem v2.384.55 og v2.384.72. Hvert fix startede med en fil, HotXLS skrev, læste perfekt tilbage, og som et af de to målprogrammer tog forkert. Det følgende er, hvad hvert program faktisk accepterer, den markup, der tilfredsstiller begge, og de HotXLS-API-kald, der producerer det fra Delphi

Hvorfor ser en ODS-fil fin ud i det ene program og i stykker i det andet?

En ODS-fil ser fin ud i det ene program og i stykker i det andet, fordi Excel og LibreOffice læser forskellige dele af samme pakke. OpenDocument giver formler og betinget formatering mere end én lovlig stavning, LibreOffice tilføjer sit eget extension-namespace ovenpå, og hver forbruger vælger den delmængde, den implementerer. En skriver testet mod kun én forbruger konvergerer gladeligt i markup, som den anden lydløst mislæser

Ingen af programmerne melder en fejl. LibreOffice viser #VALUE! i celler, hvis formler den ikke kunne parse; Excel åbner workbooken med den betingede formatering ganske enkelt fraværende, eller med en formel omskrevet til noget, der evaluerer til #NAME? eller konstanten 0. En skriver, der round-tripper sit eget output, ser ingenting af det. HotXLS ramte præcis den fælde med formel-namespace: dens læser matchede of:-præfikset som ren tekst, så hver selv-round-trip passede, mens LibreOffice viste #VALUE! i hver formelcelle

Feature	Excel 16 læser	LibreOffice 26.2 læser
Hel kolonne skrevet som A:A	Mislæst som A:(A)	Tolereret
Hel kolonne skrevet som [.A:.A]	Ja	Ja
Betinget formatering i <style:map>	Ja, den eneste form, den læser	Ignoreret, når calcext er til stede
Betinget formatering i calcext:conditional-formats	Ignoreret	Ja, foretrukket
calcext-værdiregel med en calcext:operator-attribut	Ignoreret	Importeret som "equal to 0"
calcext-formelregel stavet is-true-formula(...)	Ignoreret	Importeret som en værdisammenligning med 0

OpenFormula i ODS: deklarér namespace, og få så syntaksen rigtigt

En formelcelle i ODS kan kun læses af LibreOffice, når of:-præfikset i table:formula opløses til et deklareret XML-namespace. Præfikset er ikke pynt. of: mapper til urn:oasis:names:tc:opendocument:xmlns:of:1.2, og msoxl:, præfikset HotXLS bruger til formler, som dets OpenFormula-oversætter ikke modellerer, mapper til http://schemas.microsoft.com/office/excel/formula. Før v2.384.56 brugte content.xml-roden begge præfikser uden at deklarere dem, og LibreOffice kunne slet ikke identificere formelgrammatikken

<!-- Før v2.384.56: præfiks brugt, aldrig deklareret; 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-namespaces deklareret på roden -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Med namespace på plads skal udtrykket selv stadig være gyldig OpenFormula, som defineret i OpenDocument 1.3 Part 4. Faldgruberne er de steder, hvor Excel-syntaks og OpenFormula ligner hinanden, men ikke er det samme:

• Cellereferencer er klamrede og punktpræfikserede, og $-markørerne er en del af referencen: [.$A$1] og [.A$1:.$B2] er gyldig OpenFormula. Før v2.384.55 droppede HotXLS-skriveren hver $, så absolutte referencer kom tilbage som relative og først gik galt, da nogen kopierede cellen
• Hele kolonner og rækker skal bruge den klamrede form [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Et blottet of:=SUM(A:A) tolereres af LibreOffice, men Excel 16 åbner det som =SUM(A:(A)) med #NAME? og omdanner rækken referencer og $A:$B til konstanten 0. HotXLS skriver den klamrede form siden v2.384.65
• Funktionsargumenter adskilles af ;, ikke ,
• Reference-unioner bruger operatoren ~: Excels AREAS((A1,B2)) bliver til AREAS(([.A1]~[.B2])). Oversætter man dét komma til ; i stedet, bliver ét union-argument til to argumenter
• Inline arrays adskiller kolonner med ; og rækker med |: Excels {1,2;3,4} bliver til {1;2|3;4}. Før v2.384.55 producerede HotXLS {1;2;3;4}, en enkelt række med fire værdier

Kommaet er den svære del, fordi ét Excel-tegn bærer tre betydninger. Siden v2.384.55 holder HotXLS-skriveren en parentes-stak under oversættelsen: en ( direkte efter et navn åbner et funktionskald, hvis kommaer bliver til ;; enhver anden ( er en grupperingsparentes, hvis kommaer bliver til ~; og kommaer inde i {} er array-kolonne-separatorer. Med det og namespace-fixet evaluerede LibreOffice 26.2 alle otte array- og union-probeformler korrekt, INDEX og AREAS over unioner inkluderet

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ørerne overlever siden v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formler, oversætteren ikke modellerer, falder tilbage til msoxl:= med Excel-teksten uændret, hvilket er grunden til, at msoxl-deklarationen også tæller. I den aktuelle skriver omfatter den vej ark-kvalificerede referencer som Sheet2!A1 og strukturerede tabelreferencer. HotXLS læser msoxl:-formler tilbage ved import, så dets egen round-trip bevarer udtrykket intakt, men hvordan et andet program behandler dem, ligger uden for skriverens kontrol. Kommer en formel, dine forbrugere er afhængige af, ud med msoxl:-præfikset, så åbn filen i begge programmer, før du skiber den

Hvorfor kan Excel ikke se betinget formatering skrevet kun som calcext?

Excel 16 kan ikke se calcext-betinget formatering, fordi den læser ODS-betinget formatering udelukkende fra <style:map>-børn af celletyle og ignorerer calcext:conditional-formats-blokken helt. Eksperimentet, der afgør det, er kort: tag en ODS gemt af LibreOffice, slet style:map-elementerne, og Excel læser nul regler; slet i stedet calcext-blokken, og Excel læser stadig alle. LibreOffice opfører sig omvendt. calcext er LibreOffice' extension-namespace, ikke en del af ODF-standarden, og når en calcext-regel er til stede, tager LibreOffice den og ignorerer style:map

Før v2.384.69 skrev HotXLS kun calcext, så en ODS-fil med perfekt god fremhævning åbnede i Excel uden værdiregler og uden formelregler overhovedet. HotXLS skriver nu begge former. style:map-halvdelen bruger betingelsesgrammatikken fra OpenDocument-skemaet (ODF 1.3 Part 3), med de præcise stavinger, som Excel 16 og LibreOffice 26.2 begge producerer, når de gemmer ODS:

<!-- Forenklet. Bærer-stil for hver celle i A1:A50 (to værdiregler) -->
<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ærer-stil for hver celle i 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>

Hagen ved style:map er, at den bor på celletyle, så den er pr. celle. Hver celle i regelens område skal bære en stil, der indeholder map'et, tomme celler inkluderet, ellers dækker reglen simpelthen ikke den celle i Excel. HotXLS kopierer hver celles eksisterende formatstil, tilføjer map'ene og deduplikerer bærer-tyle ud fra parret af oprindelig stil og map-tekst, så et område på 500 celler med identisk formatering stadig producerer én stil. Skriveren udvider også den skrevne tabel til regelens område, hvilket betyder, at tomme halerækker inde i en regel udsendes frem for at blive droppet. Siden v2.384.69 bærer styles.xml også en tom Default-cellestil, så style:apply-style-name="Default" altid har et mål

Den calcext-stavning, LibreOffice faktisk accepterer

LibreOffice accepterer kun en calcext-værdiregel, når sammenligningsoperatoren er en del af værditeksten, som >3 eller between(1,10), og en formelregel kun, når den er stavet formula-is(...). Begge punkter kostede HotXLS en udgivelse, fordi de forkerte stavinger producerer en regel, der importeres uden fejl og derefter matcher de forkerte celler

Den første fejl var en calcext:operator-attribut ved siden af calcext:value. Den læser naturligt, men den er opfundet: LibreOffice kender ikke den attribut, så den importerede hver værdiregel som "equal to 0". Den anden var at sætte is-true-formula(...), style:map-stavningen, ind i en calcext-betingelse, som LibreOffice også importerede som en celleværdi-sammenligning med 0. Formelfixet skibede i v2.384.66 og værdifixet i v2.384.69:

<!-- Forkert: LibreOffice ignorerer calcext:operator og importerer "equal to 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Rigtigt: operatoren rejser inde i værdien -->
<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"/>

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

Basis-cellen er det, der giver relative referencer deres betydning. HotXLS forankrer hver regel i den øverste venstre celle af dens første område-del, så en formel skrevet til C1 evalueres som C2, C3 og så videre ned gennem området, præcis som i Excels egen betingede formatering. Regeludtrykket går gennem samme oversætter som celformler, så arrays, unioner, hele kolonner og $-markører kommer ud i formerne beskrevet ovenfor. På Delphi-siden tilføjer du regler præcis, som du ville til en .xlsx-fil

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Værdiregler: style:map cell-content()>100 plus calcext value ">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(...) og 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;

Læsning af ODS fra Excel og LibreOffice tilbage til Delphi

Når HotXLS åbner en ODS-fil, accepterer dens læser begge dialecter af betinget formatering og begge calcext-stavinger, og den tæller ikke en regel dobbelt, når filen bærer den i begge former. Rigtige filer kommer fra tre skrivere, hver med sine egne vaner:

• Gammel og ny calcext. Filer med en calcext:operator-attribut, inklusive ODS skrevet af HotXLS før v2.384.69, går stadig gennem den legacy-parsing. Formelbetingelser genkendes som enten formula-is(...) eller is-true-formula(...)
• Excels style:map-stavning. Excel præfikser betingelser med of:, som i of:cell-content-is-between(1,10), og udelader basis-cellen på værdiregler. Begge accepteres
• Tomme celler. Excel og LibreOffice sætter begge map'et for tomme celler på kolonnens default-stil frem for på en celle, så læseren opløser kolonners default-tyle for gentagne celler, før den samler map'ene
• Genopbygning af områder. Map'ene samles pr. celle, så efter et ark er læst, fletter læseren celler, der deler samme betingelse og basis-celle, tilbage til områder, først hen over hver række og derefter ned gennem matchende kolonnespænd, og dropper enhver regel, der allerede er læst fra calcext

Fixet i v2.384.72 handler om talstile, ikke regler. Excel 16 og LibreOffice 26.2 skriver begge General-formatet som en talstil, hvis number:number-element ikke har number:decimal-places, typisk <number:number number:min-integer-digits="1"/>. HotXLS-læseren behandlede den manglende optælling som to faste decimaler, så hver værdi i Default-stilen blev importeret med 0.00, og 1.5 viste sig som 1.50. Siden v2.384.72 mapper et rent talelement uden decimaler, uden minimumsdecimaler, uden gruppering og med højst ét heltalsciffer til General, og en enlig General efterlader cellen uden noget talformat overhovedet. Tekst omkring den bevares, som i General" kg", og grupperede tal beholder den tidligere mapping, fordi Excel ikke har noget grupperet 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-indekseren er 1-baseret
    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 læses tilbage uden noget talformat
    // siden v2.384.72, i stedet for '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Regelformler kommer tilbage i Excel-syntaks med komma-separatorer, samme form, du ville sende til AddCondFormatExpression, så en regel skrevet af HotXLS læses tilbage som den identiske streng. For det bredere billede af, hvad ODS-importvejen beholder og dropper, se HotXLS ODS open- og save-round-trip-guiden; for hvordan gentagne rækker fra Excel og LibreOffice ekspanderes ved import, se ODS gentagne rækker som row-height runs

Hvad er grænserne for HotXLS' ODS-betinget-formatering-interop?

Dobbelt-markup-tilgangen dækker værbisammenligningsregler og formelregler og stopper dér. Alt andet er ensidigt eller slet ikke skrevet:

• Color scales og data bars skrives kun som calcext-elementer, så LibreOffice viser dem, og Excel gør ikke
• Andre regeltyper, som ikonsæt, tekstregler, top-N, over-gennemsnit og duplikeringsregler, har intet ODS-output i den aktuelle skriver. En tekstregel kan som regel omformuleres til en formelregel, for eksempel ISNUMBER(SEARCH("late",B2)) over B2:B200, som så når begge programmer
• Hel-kolonne- og hel-række-regler som C:C lægges kun over det tabelområde, der faktisk er skrevet, frem for over alle 1.048.576 rækker, så Excel ser disse regler kun på celler, der findes i filen
• Filer med kun style:map. Når en fil ikke har nogen calcext-blok, fortolker HotXLS relative referencer i formelregler fra den øverste venstre hjørne af det genopbyggede område, ikke ved at skifte fra den angivne basis-celle
• Overlappende regler fra LibreOffice. Når én celle er dækket af flere regler, skriver LibreOffice kun den første regels map på den. Sådanne filer kan ikke læses komplet alene ud fra style:map, hvilket er endnu en grund til, at læseren foretrækker calcext, når begge findes

Procesgrænsen tæller mere end nogen af disse. Defekterne bag disse udgivelser kom forbi round trips, der skrev ODS og læste den tilbage med HotXLS, og nogle ville også have bestået et manuelt tjek i det forkerte program: hel-kolonne-formler virkede i LibreOffice, mens Excel viste #NAME?, og fra v2.384.66 virkede formelregler i LibreOffice, mens Excel stadig ikke viste nogen regler overhovedet indtil v2.384.69. Er ODS-interop et krav, er acceptancetesten at åbne filen i Excel og i LibreOffice og sammenligne, hvad hvert program viser. Samme disciplin gælder de stile, reglerne peger på; HotXLS-artiklen om betinget formatering og stilarter dækker, hvordan fremhævningsstile defineres på workbook-siden

Hurtig reference: ODS, som begge programmer læser

• Deklarér xmlns:of og xmlns:msoxl på content.xml-roden, ellers viser LibreOffice #VALUE! for hver formel (HotXLS siden v2.384.56)
• Skriv referencer som [.A1], behold hver $, og skriv hele kolonner og rækker som [.A:.A] og [.1:.1] (siden v2.384.55 og v2.384.65)
• Brug ; til argumenter, ~ til reference-unioner og | mellem inline array-rækker
• Skriv hver værdi- eller formelregel som en <style:map> på hver dækket celles stil til Excel og som en calcext-betingelse til LibreOffice (siden v2.384.69)
• I calcext: sæt operatoren i værdien (>3, between(1,10)) og stav formelregler formula-is(...) med en basis-celle (siden v2.384.66 og v2.384.69)
• Forvent en General-talstil uden number:decimal-places ved import; HotXLS læser den som General siden v2.384.72
• Verificér hver ny eksportprofil ved at åbne filen i både Excel og LibreOffice, aldrig i kun én af dem

HotXLS er et nativt Delphi- og C++Builder-spreadsheet-bibliotek, der læser og skriver XLS, XLSX og ODS uden Excel eller LibreOffice installeret; fuld kildekode, funktionslisten og licensering står på siden om HotXLS Delphi spreadsheet-komponenten