HotXLS ODS-interop: formler och regler Excel kan läsa

För att producera en ODS-fil som både Excel och LibreOffice läser rätt skriver HotXLS varje formel i OpenFormula-syntax under en deklarerad of:-namnrymd, och skriver varje villkorsformat för värde eller formel två gånger: som en <style:map> på stilen för varje täckt cell, den enda form Excel 16 läser, och som ett calcext:conditional-formats-block, den form LibreOffice litar på. Varje applikation ignorerar halvan som var ämnad för den andra, så en fil som visar rätt i den ena bevisar ingenting om den andra

Den sista meningen är lärdomen bakom sex HotXLS-utgåvor mellan v2.384.55 och v2.384.72. Varje fix började med en fil som HotXLS skrev, läste tillbaka perfekt, och som en av de två målapplikationerna fick fel. Det som följer är vad varje applikation faktiskt accepterar, markeringen som nöjer båda, och HotXLS-API-anropen som producerar den från Delphi

Varför ser en ODS-fil bra ut i en applikation och trasig i den andra?

En ODS-fil ser bra ut i en applikation och trasig i den andra för att Excel och LibreOffice läser olika delar av samma paket. OpenDocument ger formler och villkorsformat mer än en laglig stavning, LibreOffice lägger till sin egen utbyggnadsnamnrymd ovanpå, och varje konsument väljer den delmängd den implementerar. En skrivare testad mot bara en konsument kommer glatt att konvergera mot markering som den andra tyst missläser

Ingen av applikationerna rapporterar ett fel. LibreOffice visar #VALUE! i celler vars formler den inte kunde parsa; Excel öppnar arbetsboken med villkorsformaten helt enkelt frånvarande, eller med en formel omskriven till något som utvärderas till #NAME? eller konstanten 0. En skrivare som rondtrar sitt eget utdata ser aldrig något av detta. HotXLS råkade ut för exakt den fällan med formelnamnrymden: dess läsare matchade prefixet of: som vanlig text, så varje egen spar-och-återläsning passerade medan LibreOffice visade #VALUE! i varje formelcell

Funktion	Excel 16 läser	LibreOffice 26.2 läser
Hel kolumn skriven som A:A	Missläst som A:(A)	Tolereras
Hel kolumn skriven som [.A:.A]	Ja	Ja
Villkorsformat i <style:map>	Ja, den enda form den läser	Ignorerad när calcext finns
Villkorsformat i calcext:conditional-formats	Ignorerad	Ja, föredragen
calcext-värdesregel med ett calcext:operator-attribut	Ignorerad	Importerad som "lika med 0"
calcext-formelregel stavad is-true-formula(...)	Ignorerad	Importerad som en värdejämförelse med 0

OpenFormula i ODS: deklarera namnrymden, få sedan syntaxen rätt

En formelcell i ODS är bara läsbar för LibreOffice när prefixet of: i table:formula löser upp sig till en deklarerad XML-namnrymd. Prefixet är ingen dekoration. of: mappar till urn:oasis:names:tc:opendocument:xmlns:of:1.2, och msoxl:, prefixet HotXLS använder för formler dess OpenFormula-översättare inte modellerar, mappar till http://schemas.microsoft.com/office/excel/formula. Före v2.384.56 använde content.xml-roten båda prefixen utan att deklarera dem, och LibreOffice kunde inte identifiera formelgrammatiken alls

<!-- Före v2.384.56: prefix använt, aldrig deklarerat; LibreOffice visar #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"/>

<!-- Sedan v2.384.56: båda formelnamnrymderna deklarerade 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 namnrymden fixad måste uttrycket självt fortfarande vara giltig OpenFormula, enligt definitionen i OpenDocument 1.3 del 4. Fällorna är de ställen där Excel-syntax och OpenFormula ser likadana ut men inte är desamma:

• Cellreferenser är hakparenteserade och punktprefixade, och $-markörerna är en del av referensen: [.$A$1] och [.A$1:.$B2] är giltig OpenFormula. Före v2.384.55 tappade HotXLS-skrivaren varje $, så absoluta referenser kom tillbaka relativa och gick bara fel när någon kopierade cellen
• Hela kolumner och rader måste använda den hakparenteserade formen [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. En bara of:=SUM(A:A) tolereras av LibreOffice, men Excel 16 öppnar den som =SUM(A:(A)) med #NAME?, och gör om radreferenser och $A:$B till konstanten 0. HotXLS skriver den hakparenteserade formen sedan v2.384.65
• Funktionsargument separeras av ;, inte ,
• Referensföreningar använder operatorn ~: Excel AREAS((A1,B2)) blir AREAS(([.A1]~[.B2])). Översätter man det kommatecknet till ; i stället delas ett föreningsargument i två argument
• Infogade matriser skiljer kolumner med ; och rader med |: Excel {1,2;3,4} blir {1;2|3;4}. Före v2.384.55 producerade HotXLS {1;2;3;4}, en enda rad med fyra värden

Kommatecknet är den svåra delen, för ett enda Excel-tecken bär tre betydelser. Sedan v2.384.55 för HotXLS-skrivaren en parentesstack medan den översätter: en ( direkt efter ett namn öppnar ett funktionsanrop, vars kommatecken blir ;; varje annan ( är en grupperande parentes, vars kommatecken blir ~; och kommatecken inuti {} är matris-kolumnseparatorer. Med det och namnrymdsfixen utvärderade LibreOffice 26.2 alla åtta matris- och föreningstestformler korrekt, INDEX och AREAS över föreningar inkluderade

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;

    // Skrivs som of:=SUM([.A:.A]) sedan v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Skrivs som of:=[.A1]*[.$B$1]; $-markörerna överlever sedan v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formler som översättaren inte modellerar faller tillbaka på msoxl:= med Excel-texten oförändrad, vilket är varför msoxl-deklarationen också spelar roll. I nuvarande skrivare omfattar den vägen bladkvalificerade referenser som Sheet2!A1 och strukturerade tabellreferenser. HotXLS läser tillbaka msoxl:-formler vid import, så dess egen spar-och-återläsning håller uttrycket intakt, men hur en annan applikation behandlar dem ligger utanför skrivarens kontroll. Kommer en formel dina konsumenter är beroende av ut med prefixet msoxl:, öppna filen i båda applikationerna innan du levererar

Varför ser Excel inte villkorsformat skrivna enbart som calcext?

Excel 16 ser inte calcext-villkorsformat för att den läser ODS-villkorsformat uteslutande från <style:map>-barn till cellstilar och ignorerar calcext:conditional-formats-blocket helt. Experimentet som avgör saken är kort: ta en ODS sparad av LibreOffice, radera style:map-elementen, och Excel läser noll regler; radera calcext-blocket i stället, och Excel läser fortfarande alla. LibreOffice beter sig tvärtom. calcext är LibreOffice utbyggnadsnamnrymd, inte del av ODF-standarden, och när en calcext-regel finns tar LibreOffice den och ignorerar style:map

Före v2.384.69 skrev HotXLS bara calcext, så en ODS-fil med helt utmärkt färgmarkering öppnades i Excel utan några värdesregler och inga formelregler alls. HotXLS skriver nu båda formerna. style:map-halvan använder villkorsgrammatiken i OpenDocument-schemat (ODF 1.3 del 3), med de exakta stavningar som Excel 16 och LibreOffice 26.2 båda producerar när de sparar ODS:

<!-- Förenklad. Bärarstil för varje cell i A1:A50 (två värdesregler) -->
<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ärarstil för varje cell i C1:C50 (en 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>

Tjacken med style:map är att den bor på cellstilar, så den är per cell. Varje cell i regelns område måste bära en stil som håller mappen, tomma celler inkluderade, annars täcker regeln helt enkelt inte den cellen i Excel. HotXLS kopierar varje cells befintliga formatstil, lägger till mapparna och deduplicerar bärarstilar efter paret originalstil och mapptext, så ett område på 500 celler med identisk formatering producerar fortfarande en stil. Skrivaren utökar också den skrivna tabellen till regelns område, vilket betyder att tomma slutrader inuti en regel emitteras i stället för att släppas. Sedan v2.384.69 bär styles.xml också en tom Default-cellstil, så style:apply-style-name="Default" har alltid ett mål

Den calcext-stavning LibreOffice faktiskt accepterar

LibreOffice accepterar en calcext-värdesregel bara när jämförelseoperatorn är en del av värdetexten, som >3 eller between(1,10), och en formelregel bara när den är stavad formula-is(...). Båda punkterna kostade HotXLS en utgåva, för de felaktiga stavningarna producerar en regel som importeras utan fel och sedan matchar fel celler

Det första misstaget var ett calcext:operator-attribut bredvid calcext:value. Det läser sig naturligt, men det är uppfunnet: LibreOffice känner inte till det attributet, så det importerade varje värdesregel som "lika med 0". Det andra var att lägga is-true-formula(...), style:map-stavningen, i ett calcext-villkor, vilket LibreOffice också importerade som en cellvärdesjämförelse med 0. Formelfixen levererades i v2.384.66 och värdefixen i v2.384.69:

<!-- Fel: LibreOffice ignorerar calcext:operator och importerar "lika med 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Rätt: operatorn färds inuti värdet -->
<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"/>

<!-- Rätt: formelregler använder formula-is, relativa referenser förankrade vid bascellen -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Bascellen är det som ger relativa referenser sin mening. HotXLS förankrar varje regel vid den övre vänstra cellen i dess första område, så en formel skriven för C1 utvärderas som C2, C3 och så vidare ned genom området, precis som den gör i Excels egen villkorsformatering. Regeluttrycket går genom samma översättare som cellformler, så matriser, föreningar, hela kolumner och $-markörer kommer ut i formerna beskrivna ovan. På Delphi-sidan lägger du till regler precis som du skulle för en .xlsx-fil

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Värdesregler: style:map cell-content()>100 plus calcext-värde ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: ljusröd

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

  // Formelregel i Excels syntax (kommateckenseparatorer, relativ till C1):
  // style:map is-true-formula(...) och calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: ljusgul

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

Läsa ODS från Excel och LibreOffice tillbaka in i Delphi

När HotXLS öppnar en ODS-fil accepterar dess läsare både villkorsformatdialekterna och båda calcext-stavningarna, och den räknar inte en regel två gånger när filen bär den i båda formerna. Verkliga filer kommer från tre skrivare, var och en med sina vanor:

• Gammalt och nytt calcext. Filer med ett calcext:operator-attribut, inklusive ODS skrivna av HotXLS före v2.384.69, går fortfarande genom den äldre parsningen. Formelvillkor känns igen som antingen formula-is(...) eller is-true-formula(...)
• Excels style:map-stavning. Excel prefixar villkoren med of:, som i of:cell-content-is-between(1,10), och utelämnar bascellen på värdesregler. Båda accepteras
• Tomma celler. Excel och LibreOffice lägger båda mappen för tomma celler på kolumnens standardstil i stället för på en cell, så läsaren löser upp kolumnstandardstilar för upprepade celler innan den samlar mappar
• Regionåterbyggnad. Mappar samlas per cell, så efter att ett blad lästs slår läsaren ihop celler som delar samma villkor och bascell tillbaka till områden, först över varje rad och sedan nedåt över matchande kolumnspänn, och släpper regler redan lästa från calcext

Fixen i v2.384.72 gäller talstilar, inte regler. Excel 16 och LibreOffice 26.2 skriver båda General-formatet som en talstil vars number:number-element saknar number:decimal-places, typiskt <number:number number:min-integer-digits="1"/>. HotXLS-läsaren behandlade den saknade räkningen som två fasta decimaler, så varje värde i Default-stilen importerades med 0.00 och 1.5 visades som 1.50. Sedan v2.384.72 mappar ett bart talsnummer-element utan decimaler, utan minimidecimaler, utan gruppering och med högst en heltalssiffra till General, och en ensam General lämnar cellen utan något talformat alls. Text runt det behålls, som i General" kg", och grupperade tal behåller den tidigare mappningen för Excel har inget grupperat 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-indexeraren är ettbaserad
    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 cell i Excels General-stil läses tillbaka utan talformat
    // sedan v2.384.72, i stället för '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Regelformler kommer tillbaka i Excels syntax med kommateckenseparatorer, samma form du skulle skicka till AddCondFormatExpression, så en regel skriven av HotXLS läses tillbaka som identisk sträng. För den större bilden av vad ODS-importvägen behåller och släpper, se HotXLS-guiden för ODS-rundturn vid öppning och sparande; för hur upprepade rader från Excel och LibreOffice expanderas vid import, se ODS upprepade rader som radhöjdserier

Vad är gränserna för HotXLS ODS-interop för villkorsformat?

Den dubbla markeringsansatsen täcker värdejämförelseregler och formelregler, och stannar där. Allt annat är ensidigt eller skrivs inte alls:

• Färjskalor och databalkar skrivs bara som calcext-element, så LibreOffice visar dem och Excel gör inte det
• Andra regeltyper, som ikonuppsättningar, textregler, topp-N, över-medelvärde och dubblettregler, har ingen ODS-utdata i nuvarande skrivare. En textregel kan oftast skrivas om som en formelregel, till exempel ISNUMBER(SEARCH("late",B2)) över B2:B200, vilken då når båda applikationer
• Hela-kolumn- och hela-rad-regler som C:C läggs bara över tabellområdet som faktiskt skrivs, i stället för över alla 1 048 576 rader, så Excel ser de reglerna bara på celler som finns i filen
• Filer med bara style:map. När en fil saknar calcext-block tolkar HotXLS relativa referenser i formelregler från det övre vänstra hörnet av det återbyggda området, inte genom att förskjuta från den angivna bascellen
• Överlappande regler från LibreOffice. När en cell täcks av flera regler skriver LibreOffice bara den första regelns mapp på den. Filer som de kan inte läsas helt från style:map ensam, vilket är ytterligare en anledning till att läsaren föredrar calcext när båda finns

Processgränsen spelar större roll än något av dessa. Defekterna bakom de här utgåvorna kom förbi rundturner som skrev ODS och läste tillbaka den med HotXLS, och vissa skulle också ha passerat en manuell kontroll i fel applikation: hela-kolumn-formler fungerade i LibreOffice medan Excel visade #NAME?, och från v2.384.66 fungerade formelregler i LibreOffice medan Excel fortfarande inte visade några regler alls förrän v2.384.69. Om ODS-interop är ett krav är acceptanstestet att öppna filen i Excel och i LibreOffice och jämföra vad var och en visar. Samma disciplin gäller stilarna som reglerna pekar på; HotXLS-artikeln om villkorsformatering och stilar täcker hur markeringsstilar definieras på arbetsbokssidan

Snabbreferens: ODS som båda applikationer läser

• Deklarera xmlns:of och xmlns:msoxl på content.xml-roten, annars visar LibreOffice #VALUE! för varje formel (HotXLS sedan v2.384.56)
• Skriv referenser som [.A1], behåll varje $, och skriv hela kolumner och rader som [.A:.A] och [.1:.1] (sedan v2.384.55 och v2.384.65)
• Använd ; för argument, ~ för referensföreningar, och | mellan infogade matrisrader
• Skriv varje värde- eller formelregel som en <style:map> på varje täckt cells stil för Excel, och som ett calcext-villkor för LibreOffice (sedan v2.384.69)
• I calcext, lägg operatorn i värdet (>3, between(1,10)) och stava formelregler formula-is(...) med en bascell (sedan v2.384.66 och v2.384.69)
• Räkna med en General-talstil utan number:decimal-places vid import; HotXLS läser den som General sedan v2.384.72
• Verifiera varje ny exportprofil genom att öppna filen i både Excel och LibreOffice, aldrig i bara en av dem

HotXLS är ett nativt Delphi- och C++Builder-kalkylbibliotek som läser och skriver XLS, XLSX och ODS utan Excel eller LibreOffice installerat; full källkod, funktionslistan och licensiering finns på sidan för HotXLS Delphi spreadsheet component