ODS interoperabilita v HotXLS: vzorce, ktoré Excel prečíta

Aby HotXLS vyrobil ODS súbor, ktorý správne prečíta Excel aj LibreOffice, zapisuje každú formulu v OpenFormula syntaxi pod deklarovaným namespace of: a zapisuje každý podmienený formát hodnoty alebo formuly dvakrát: ako <style:map> na štýle každej pokrytej bunky, čo je jediná forma, ktorú Excel 16 číta, a ako blok calcext:conditional-formats, čo je forma, ktorej LibreOffice verí. Každá aplikácia ignoruje polovicu určenú tej druhej, takže súbor, ktorý sa v jednej z nich zobrazí správne, o tej druhej nedokáže nič

Tá posledná veta je ponaučenie zo šiestich vydaní HotXLS medzi v2.384.55 a v2.384.72. Každá oprava začala súborom, ktorý HotXLS zapísal, načítal späť dokonale a jedna z dvoch cieľových aplikácií pokazila. Nasleduje to, čo každá aplikácia naozaj prijíma, markup, ktorý uspokojí oboch, a volania API HotXLS, ktoré to z Delphi vyrobia

Prečo vyzerá ODS súbor v jednej aplikácii dobre a v druhej pokazený?

ODS súbor vyzerá v jednej aplikácii dobre a v druhej pokazený preto, lebo Excel a LibreOffice čítajú rôzne časti toho istého balíka. OpenDocument dáva formulám a podmieneným formátom viac ako jedno legálne hláskovanie, LibreOffice pridáva vlastný rozšírovací namespace navyše a každý konzument si vyberie podmnožinu, ktorú implementuje. Zapisovač testovaný proti jedinému konzumentovi sa pohodlne zblíži s markupom, ktorý druhý poticho prečíta zle

Žiadna z aplikácií nehlási chybu. LibreOffice ukazuje #VALUE! v bunkách, ktorých formuly nedokázal rozobrať; Excel otvorí zošit s podmienenými formátmi jednoducho neprítomnými, alebo s formulou prepísanou na niečo, čo sa vyhodnotí na #NAME? alebo konštantu 0. Zapisovač, ktorý robí round-trip nad vlastným výstupom, nič z toho nikdy nevidí. HotXLS do tej pasce presne spadol pri formula namespace: jeho čítačka párovala prefix of: ako čistý text, takže každý vlastný round-trip prešiel, kým LibreOffice ukazoval #VALUE! v každej formulovej bunke

Vlastnosť	Excel 16 číta	LibreOffice 26.2 číta
Celý stĺpec zapísaný ako A:A	Prečíta zle ako A:(A)	Toleruje
Celý stĺpec zapísaný ako [.A:.A]	Áno	Áno
Podmienené formáty v <style:map>	Áno, jediná forma, ktorú číta	Ignoruje, keď je prítomný calcext
Podmienené formáty v calcext:conditional-formats	Ignoruje	Áno, uprednostňuje
Pravidlo hodnoty calcext s atribútom calcext:operator	Ignoruje	Importuje ako „rovné 0“
Pravidlo formuly calcext hláskované is-true-formula(...)	Ignoruje	Importuje ako porovnanie hodnoty s 0

OpenFormula v ODS: deklarujte namespace, potom správna syntax

Formulová bunka v ODS je pre LibreOffice čitateľná len vtedy, keď prefix of: v table:formula smeruje na deklarovaný XML namespace. Prefix nie je ozdoba. of: sa mapuje na urn:oasis:names:tc:opendocument:xmlns:of:1.2 a msoxl:, prefix, ktorý HotXLS používa pre formuly, ktoré jeho OpenFormula prekladač nemodeluje, sa mapuje na http://schemas.microsoft.com/office/excel/formula. Pred v2.384.56 používal koreň content.xml oba prefixy bez ich deklarácie a LibreOffice nedokázalo identifikovať formulovú gramatiku vôbec

<!-- Pred v2.384.56: prefix použitý, nikdy nedeklarovaný; LibreOffice ukazuje #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"/>

<!-- Od v2.384.56: oba formula namespace deklarované na koreni -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

S opraveným namespace musí byť aj samotný výraz platným OpenFormula, ako ho definuje OpenDocument 1.3 Part 4. Pasce sú na miestach, kde Excel syntax a OpenFormula vyzerajú podobne, ale nie sú tie isté:

• Bunkové referencie sú v zátvorkách a s bodkovým prefixom a značky $ sú súčasťou referencie: [.$A$1] a [.A$1:.$B2] sú platné OpenFormula. Pred v2.384.55 zahadzoval zapisovač HotXLS každé $, takže absolútne referencie sa vracali relatívne a pokazili sa, až keď niekto bunku skopíroval
• Celé stĺpce a riadky musia používať zátvorkovú formu [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Holé of:=SUM(A:A) LibreOffice toleruje, ale Excel 16 ho otvorí ako =SUM(A:(A)) s #NAME? a referencie riadkov aj $A:$B zmení na konštantu 0. HotXLS zapisuje zátvorkovú formu od v2.384.65
• Argumenty funkcií sa oddeľujú ;, nie ,
• Zjednotenia referencií používajú operátor ~: Excel AREAS((A1,B2)) sa stáva AREAS(([.A1]~[.B2])). Preložíte-li tú čiarku radšej na ;, jeden zjednotený argument sa zmení na dva argumenty
• Inline polia oddeľujú stĺpce ; a riadky |: Excel {1,2;3,4} sa stáva {1;2|3;4}. Pred v2.384.55 HotXLS vyrobil {1;2;3;4}, jediný riadok štyroch hodnôt

Čiarka je to najťažšie, lebo jeden znak Excelu nesie tri významy. Od v2.384.55 sleduje zapisovač HotXLS počas prekladu zásobník zátvoriek: ( priamo za menom otvára volanie funkcie, ktorého čiarky sa stávajú ;; ľubovoľná iná ( je zoskupovacia zátvorka, ktorej čiarky sa stávajú ~; a čiarky vo vnútri {} sú oddeľovače stĺpcov poľa. S týmto a s opravou namespace vyhodnotilo LibreOffice 26.2 správne všetkých osem testovacích formulí nad poľami a zjednoteniami, INDEX a AREAS nad zjednoteniami vrátane

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;

    // Zapisované ako of:=SUM([.A:.A]) od v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Zapisované ako of:=[.A1]*[.$B$1]; značky $ prežívajú od v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formuly, ktoré prekladač nemodeluje, padajú do msoxl:= s nezmneným Excel textom, preto má deklarácia msoxl tiež význam. V aktuálnom zapisovači zahŕňa táto cesta referencie s kvalifikátorom hárka ako Sheet2!A1 a štruktúrované referencie tabuliek. HotXLS číta formuly msoxl: späť pri importe, takže vlastný round trip drží výraz neporušený, ale ako k nim pristúpi iná aplikácia, je mimo kontroly zapisovača. Ak formula, na ktorej záleží vašim konzumentom, vyjde s prefixom msoxl:, otvorte súbor v oboch aplikáciách, než ho odošlete

Prečo Excel nevidí podmienené formáty zapísané len ako calcext?

Excel 16 nevidí podmienené formáty calcext, pretože číta ODS podmienené formáty výlučne z potomkov <style:map> bunkových štýlov a blok calcext:conditional-formats ignoruje úplne. Experiment, ktorý to vyrieši, je krátky: vezmite ODS uložený LibreOffice, zmažte elementy style:map a Excel prečíta nula pravidiel; zmažte namiesto toho blok calcext a Excel stále prečíta všetky. LibreOffice sa správa opačne. calcext je rozšírovací namespace LibreOffice, nie súčasť štandardu ODF, a keď je prítomné pravidlo calcext, LibreOffice si vezme ho a ignoruje style:map

Pred v2.384.69 zapisoval HotXLS len calcext, takže ODS súbor s úplne v poriadku zvýrazňovaním sa v Exceli otvoril bez akýchkoľvek pravidiel hodnôt aj formulí. HotXLS teraz zapisuje obe formy. Polovica style:map používa gramatiku podmienok schémy OpenDocument (ODF 1.3 Part 3), s presnými hláskovaniami, ktoré pri ukladaní ODS produkujú Excel 16 aj LibreOffice 26.2:

<!-- Zjednodušené. Nosičský štýl pre každú bunku A1:A50 (dve pravidlá hodnôt) -->
<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>

<!-- Nosičský štýl pre každú bunku C1:C50 (jedno pravidlo formuly) -->
<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>

Háčik s style:map je, že žije na bunkových štýloch, takže je na bunku. Každá bunka v rozsahu pravidla musí niesť štýl držiaci mapu, prázdne bunky vrátane, inak pravidlo jednoducho nepokrýva tú bunku v Exceli. HotXLS skopíruje existujúci formátovací štýl každej bunky, prilepí mapy a deduplikuje nosičské štýly podľa páru pôvodný štýl a text mapy, takže rozsah 500 buniek s identickým formátovaním stále vyprodukuje jeden štýl. Zapisovač tiež rozšíri zapísanú tabuľku na rozsah pravidla, čo znamená, že prázdne koncové riadky vo vnútri pravidla sa emitujú, nie zahodia. Od v2.384.69 nesie styles.xml aj prázdny bunkový štýl Default, takže style:apply-style-name="Default" má vždy cieľ

Hláskovanie calcext, ktoré LibreOffice naozaj prijíma

LibreOffice prijíma pravidlo hodnoty calcext len vtedy, keď je porovnávací operátor súčasťou textu hodnoty, ako >3 alebo between(1,10), a pravidlo formuly len vtedy, keď je hláskované formula-is(...). Obe poučky stáli HotXLS jedno vydanie, lebo zlé hláskovania vyrobia pravidlo, ktoré sa importuje bez chyby a potom sedí na zlých bunkách

Prvá chyba bol atribút calcext:operator vedľa calcext:value. Číta sa prirodzene, ale je vymyslený: LibreOffice ten atribút nepozná, takže importovalo každé pravidlo hodnoty ako „rovné 0“. Druhou bolo vloženie is-true-formula(...), hláskovania style:map, do podmienky calcext, ktoré LibreOffice tiež importovalo ako porovnanie hodnoty bunky s 0. Oprava formuly vyšla vo v2.384.66 a oprava hodnoty vo v2.384.69:

<!-- Zle: LibreOffice ignoruje calcext:operator a importuje "rovné 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Správne: operátor cestuje vo vnútri hodnoty -->
<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"/>

<!-- Správne: pravidlá formulí používajú formula-is, relatívne referencie ukotvené na základnú bunku -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Základná bunka je to, čo dáva relatívnym referenciám význam. HotXLS ukotvuje každé pravidlo na ľavej hornej bunke jeho prvej oblasti rozsahu, takže formula napísaná pre C1 sa vyhodnotí ako C2, C3 a tak ďalej po rozsahu, presne ako pri vlastnom podmienenom formátovaní Excelu. Výraz pravidla ide tým istým prekladačom ako formuly buniek, takže polia, zjednotenia, celé stĺpce aj značky $ vychádzajú vo formách popísaných vyššie. Na strane Delphi pridávate pravidlá presne tak, ako by ste ich pridávali pre súbor .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Pravidlá hodnôt: style:map cell-content()>100 plus calcext hodnota ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: svetločervená

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

  // Pravidlo formuly v Excel syntaxi (čiarky ako oddeľovače, relatívne k C1):
  // style:map is-true-formula(...) a calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: svetložltlá

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

Čítanie ODS z Excelu a LibreOffice späť do Delphi

Keď HotXLS otvorí ODS súbor, jeho čítačka prijíma obidva dialekty podmienených formátov aj obe hláskovania calcext a nepočíta pravidlo dvakrát, keď ho súbor nesie v oboch formách. Reálne súbory prichádzajú od troch zapisovačov, každý so svojimi zvykmi:

• Staré a nové calcext. Súbory s atribútom calcext:operator, vrátane ODS zapísaných HotXLS pred v2.384.69, idú stále cez legacy parse. Formulové podmienky sa rozpoznajú ako formula-is(...) alebo is-true-formula(...)
• Hláskovanie style:map od Excelu. Excel prefixuje podmienky cez of:, ako v of:cell-content-is-between(1,10) a vynecháva základnú bunku pri pravidlách hodnôt. Obe sa prijímajú
• Prázdne bunky. Excel aj LibreOffice dávajú mapu pre prázdne bunky na predvolený štýl stĺpca, nie na bunku, takže čítačka rozlíši predvolené štýly stĺpcov pre opakované bunky, než začne zbierať mapy
• Obnova regiónov. Mapy sa zbierajú na bunku, takže po prečítaní hárka zliepa čítačka bunky zdieľajúce tú istú podmienku a základnú bunku späť do rozsahov, najprv cez každý riadok a potom dole po zodpovedajúcich stĺpcových rozpätíach, a zahodí každé pravidlo už prečítané z calcext

Oprava vo v2.384.72 sa týka číselných štýlov, nie pravidiel. Excel 16 aj LibreOffice 26.2 zapisujú formát General ako číselný štýl, ktorého element number:number nemá number:decimal-places, typicky <number:number number:min-integer-digits="1"/>. Čítačka HotXLS brala chýbajúci počet ako dve fixné desatinné miesta, takže každá hodnota v štýle Default sa importovala s 0.00 a 1.5 sa zobrazovalo ako 1.50. Od v2.384.72 sa holý číselný element bez desatinných miest, bez minimálnych desatinných miest, bez zoskupovania a s najviac jednou celočíselnou číslicou mapuje na General a osamelý General nechá bunku bez akéhokoľvek číselného formátu. Text okolo neho zostáva, ako v General" kg" a zoskupené čísla držia predchádzajúce mapovanie, lebo Excel nemá zoskupený General formát

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]; // indexer Sheets je od jednej
    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;

    // Bunka v Excel štýle General sa načíta späť bez číselného formátu
    // od v2.384.72, namiesto '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formuly pravidiel sa vracajú v Excel syntaxi s čiarkami ako oddeľovačmi, v tej istej forme, ktorú by ste podali AddCondFormatExpression, takže pravidlo zapísané HotXLS sa načíta späť ako identický reťazec. Pre širší obraz o tom, čo importná cesta ODS drží a čo zahodí, pozrite sprievodcu round-trip otvárania a ukladania ODS v HotXLS; ako sa opakované riadky z Excelu a LibreOffice rozbaľujú pri importe, popisuje opakované riadky ODS ako behy výšok riadkov

Aké sú medze interoperability podmienených formátov ODS v HotXLS?

Prístup dvojitého markupu pokrýva pravidlá porovnania hodnôt a pravidlá formulí a tam aj končí. Všetko ostatné je jednostranné alebo sa nezapisuje vôbec:

• Farebné škály a dátové pruhy sa zapisujú len ako elementy calcext, takže LibreOffice ich ukáže a Excel nie
• Iné druhy pravidiel, ako sady ikon, textové pravidlá, top-N, nadpriemer a pravidlá duplicitných, nemajú v aktuálnom zapisovači ODS výstup. Textové pravidlo sa zvyčajne dá prepísať ako pravidlo formuly, napríklad ISNUMBER(SEARCH("late",B2)) nad B2:B200, čo potom dosiahne obe aplikácie
• Pravidlá celých stĺpcov a riadkov ako C:C sa kladú len nad oblasť tabuľky, ktorá sa skutočne zapisuje, nie nad všetkých 1 048 576 riadkov, takže Excel vidí tieto pravidlá len na bunkách, ktoré v súbore existujú
• Súbory len so style:map. Keď súbor nemá blok calcext, interpretuje HotXLS relatívne referencie v pravidlách formulí z ľavého horného rohu obnovenej oblasti, nie posunom od uvedenej základnej bunky
• Prekrývajúce sa pravidlá z LibreOffice. Keď jednu bunku pokrýva viac pravidiel, LibreOffice zapisuje na ňu mapu len prvého pravidla. Také súbory sa nedajú úplne prečítať zo samotného style:map, čo je ďalší dôvod, prečo čítačka uprednostní calcext, keď existujú obe

Procesné obmedzenie je dôležitejšie než ktorékoľvek z týchto. Chyby za týmito vydaniami prešli round-tripmi, ktoré zapísali ODS a načítali ho späť HotXLS a niektoré by prešli aj ručnou kontrolou v zlej aplikácii: formuly celých stĺpcov fungovali v LibreOffice, kým Excel ukazoval #NAME?, a od v2.384.66 fungovali pravidlá formulí v LibreOffice, kým Excel stále neukazoval žiadne pravidlá až do v2.384.69. Ak je interoperabilita ODS požiadavkou, akceptačným testom je otvoriť súbor v Exceli a v LibreOffice a porovnať, čo ktoré ukazuje. Rovnaká disciplína platí aj pre štýly, na ktoré pravidlá ukazujú; článok o podmienenom formátovaní a štýloch HotXLS popisuje, ako sa štýly zvýraznenia definujú na strane zošita

Rýchly prehľad: ODS, ktoré prečíta obe aplikácie

• Deklarujte xmlns:of a xmlns:msoxl na koreni content.xml, inak LibreOffice ukáže #VALUE! pre každú formulu (HotXLS od v2.384.56)
• Zapisujte referencie ako [.A1], zachovajte každé $ a zapisujte celé stĺpce a riadky ako [.A:.A] a [.1:.1] (od v2.384.55 a v2.384.65)
• Používajte ; pre argumenty, ~ pre zjednotenia referencií a | medzi riadkami inline poľa
• Zapisujte každé pravidlo hodnoty alebo formuly ako <style:map> na štýle každej pokrytej bunky pre Excel a ako podmienku calcext pre LibreOffice (od v2.384.69)
• V calcext dajte operátor do vnútra hodnoty (>3, between(1,10)) a hláskujte pravidlá formulí ako formula-is(...) so základnou bunkou (od v2.384.66 a v2.384.69)
• Pri importe počítajte s číselným štýlom General bez number:decimal-places; HotXLS ho číta ako General od v2.384.72
• Verifikujte každý nový exportný profil otvorením súboru v Exceli aj LibreOffice, nikdy len v jednom z nich

HotXLS je natívna tabuľková knižnica pre Delphi a C++Builder, ktorá číta a zapisuje XLS, XLSX aj ODS bez nainštalovaného Excelu alebo LibreOffice; plný zdrojový kód, zoznam funkcií a licencovanie sú na stránke tabuľkového komponentu HotXLS pre Delphi