ODS interoperabilita HotXLS: formule a pravidla pro Excel

Aby vznikl soubor ODS, který čtou správně Excel i LibreOffice, zapisuje HotXLS každou formuli v syntaxi OpenFormula pod deklarovaným namespace of: a každé hodnotové či formulové pravidlo podmíněného formátování zapíše dvakrát: jako <style:map> na stylu každé pokryté buňky, což je jediná forma, kterou čte Excel 16, a jako blok calcext:conditional-formats, forma, které LibreOffice věří. Každá aplikace tu druhou polovinu ignoruje, takže soubor, který se zobrazí správně v jedné z nich, o té druhé nic neprokazuje

Ta poslední věta je poučení za šest vydání HotXLS mezi v2.384.55 a v2.384.72. Každá oprava začínala souborem, který HotXLS zapsal, sám četl zpět bezchybně a jedna ze dvou cílových aplikací si z něj udělala nesmysl. Následuje to, co každá aplikace doopravdy přijímá, zápis, který uspokojí obě, a volání API HotXLS, která to z Delphi vyprodukují

Proč vypadá soubor ODS v jedné aplikaci dobře a v druhé rozbitě?

Soubor ODS vypadá v jedné aplikaci dobře a v druhé rozbitě, protože Excel a LibreOffice čtou různé části téhož balíčku. OpenDocument nabízí formule a podmíněné formátování v víc než jednom legálním zápisu, LibreOffice přidává na to vlastní rozšiřující namespace a každý konzument si vybere podmnožinu, kterou implementuje. Zapisovač testovaný proti jedinému konzumentovi se ochotně sbalí k zápisu, který druhý potichu špatně přečte

Žádná aplikace nehlásí chybu. LibreOffice ukáže #VALUE! v buňkách, jejichž formule nerozparsuje; Excel otevře sešit s podmíněným formátováním prostě nepřítomným, nebo s formulí přepsanou na něco, co se vyhodnotí jako #NAME? nebo konstanta 0. Zapisovač, který dělá round-trip s vlastním výstupem, nevidí nic z toho. HotXLS do přesně té pasti šlápl s formulovým namespace: jeho čtečka hledala prefix of: jako prostý text, takže každý vlastní round-trip vyšel, zatímco LibreOffice ukazoval #VALUE! v každé buňce s formulí

Vlastnost	Čte Excel 16	Čte LibreOffice 26.2
Celý sloupec zapsaný jako A:A	Přečte špatně jako A:(A)	Tolerováno
Celý sloupec zapsaný jako [.A:.A]	Ano	Ano
Podmíněné formátování v <style:map>	Ano, jediná forma, kterou čte	Ignorováno, je-li přítomen calcext
Podmíněné formátování v calcext:conditional-formats	Ignorováno	Ano, upřednostňováno
calcext hodnotové pravidlo s atributem calcext:operator	Ignorováno	Importuje jako „rovno 0“
calcext formulové pravidlo zapsané is-true-formula(...)	Ignorováno	Importuje jako hodnotové srovnání s 0

OpenFormula v ODS: deklarujte namespace, pak doladte syntaxi

Buňka s formulí je v ODS čitelná pro LibreOffice jen tehdy, když se prefix of: v table:formula rozvede na deklarovaný XML namespace. Prefix není ozdoba. of: se mapuje na urn:oasis:names:tc:opendocument:xmlns:of:1.2 a msoxl:, prefix, který HotXLS používá pro formule, které jeho překladač OpenFormula nemodeluje, se mapuje na http://schemas.microsoft.com/office/excel/formula. Před v2.384.56 užíval kořen content.xml oba prefixy bez deklarace a LibreOffice nedokázalo poznat gramatiku formulí vůbec

<!-- Před v2.384.56: prefix použit, nikdy deklarován; 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 formulové namespace deklarované na kořeni -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

S napraveným namespace musí být ještě samotný výraz platné OpenFormula, jak je definováno v OpenDocument 1.3 Part 4. Pasti leží tam, kde se syntaxe Excelu a OpenFormula jen podobají, ale nejsou totéž:

• Reference buněk jsou v závorkách s tečkou a značky $ jsou součástí reference: [.$A$1] a [.A$1:.$B2] jsou platné OpenFormula. Před v2.384.55 zapisovač HotXLS každou značku $ zahodil, takže absolutní reference se vracely relativní a pokazily se teprve, jakmile někdo buňku zkopíroval
• Celé sloupce a řádky musí používat formu v závorkách [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Holé of:=SUM(A:A) LibreOffice toleruje, ale Excel 16 ho otevře jako =SUM(A:(A)) s #NAME? a řádkové reference i $A:$B promění v konstantu 0. HotXLS zapisuje formu v závorkách od v2.384.65
• Argumenty funkcí se oddělují ;, nikoli ,
• Sjednocení referencí užívají operátor ~: Excel AREAS((A1,B2)) se stane AREAS(([.A1]~[.B2])). Přeložíte-li tu čárku na ;, z jednoho sjednocovacího argumentu se potichu stanou dva
• Vložená pole oddělují sloupce ; a řádky |: Excel {1,2;3,4} se stane {1;2|3;4}. Před v2.384.55 vyráběl HotXLS {1;2;3;4}, jediný řádek se čtyřmi hodnotami

Čárka je ta těžká část, protože jeden znak Excelu nese tři významy. Od v2.384.55 sleduje zapisovač HotXLS při překladu zásobník závorek: ( přímo za názvem otevírá volání funkce, jejíž čárky se stanou ;; jakákoli jiná ( je seskupovací závorka, jejíž čárky se stanou ~; a čárky uvnitř {} jsou oddělovače sloupců pole. S tímhle a s opravou namespace vyhodnotilo LibreOffice 26.2 všech osm testovacích formulí nad poli a sjednoceními správně, INDEX a AREAS nad sjednoceními včetně

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;

    // Zapisuje se jako of:=SUM([.A:.A]) od v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Zapisuje se jako of:=[.A1]*[.$B$1]; značky $ přežívají od v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formule, které překladač nemodeluje, padají do zálohy msoxl:= s nezměněným textem Excelu, a proto záleží i na deklaraci msoxl. V současném zapisovači patří na tuhle cestu reference kvalifikované listem jako Sheet2!A1 a strukturované reference na tabulky. HotXLS čte formule msoxl: zpět při importu, takže jeho vlastní round-trip výraz udrží v celku, ale jak si je poradí jiná aplikace, je mimo kontrolu zapisovače. Vyjde-li formule, na které vaši konzumenti závisí, s prefixem msoxl:, otevřete soubor v obou aplikacích, než ho odešlete

Proč Excel nevidí podmíněné formátování zapsané jen jako calcext?

Excel 16 nevidí podmíněné formátování calcext, protože ODS podmíněné formátování čte výhradně z potomků <style:map> stylů buněk a blok calcext:conditional-formats ignoruje úplně. Pokus, který to usadí, je krátký: vezměte ODS uložené LibreOffice, smažte prvky style:map a Excel přečte nula pravidel; smažte místo nich blok calcext a Excel pořád přečte všechna. LibreOffice se chová přesně obráceně. calcext je rozšiřující namespace LibreOffice, není součástí standardu ODF, a je-li calcext pravidlo přítomno, LibreOffice si ho vezme a style:map ignoruje

Před v2.384.69 zapisoval HotXLS jen calcext, takže soubor ODS s naprosto v pořádku zvýrazňováním se otevřel v Excelu bez jediného hodnotového či formulového pravidla. HotXLS teď zapisuje obě formy. Polovina style:map užívá gramatiku podmínek schématu OpenDocument (ODF 1.3 Part 3) s přesnými zápisy, které při ukládání ODS produkují Excel 16 i LibreOffice 26.2:

<!-- Zjednodušeno. Nositelský styl pro každou buňku A1:A50 (dvě hodnotová pravidla) -->
<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>

<!-- Nositelský styl pro každou buňku C1:C50 (jedno formulové pravidlo) -->
<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áček s style:map je, že žije na stylech buněk, takže je na buňku. Každá buňka v rozsahu pravidla musí nést styl držící mapu, prázdné buňky nevyjímaje, jinak pravidlo tu buňku v Excelu prostě nepokryje. HotXLS zkopíruje stávající formátovací styl každé buňky, přilepí mapy a deduplikuje nositelské styly podle páru původní styl a text mapy, takže rozsah 500 buněk se stejným formátováním stále vyprodukuje jediný styl. Zapisovač navíc rozšiřuje zapsanou tabulku na rozsah pravidla, což znamená, že prázdné ocasy řádků uvnitř pravidla se vypustí, místo aby se zahodily. Od v2.384.69 nese styles.xml navíc prázdný styl buňky Default, takže style:apply-style-name="Default" má vždycky cíl

Zápis calcext, který LibreOffice doopravdy přijímá

LibreOffice přijme calcext hodnotové pravidlo jen tehdy, když je srovnávací operátor součástí textu hodnoty, jako >3 nebo between(1,10), a formulové pravidlo jen tehdy, když je zapsáno formula-is(...). Obě místa stála HotXLS jedno vydání, protože špatné zápisy vyprodukují pravidlo, které se naimportuje bez chyby a pak chytá špatné buňky

První omyl byl atribut calcext:operator vedle calcext:value. Čte se přirozeně, ale je vynalezený: LibreOffice ten atribut nezná, takže importovalo každé hodnotové pravidlo jako „rovno 0“. Druhý bylo vsazení is-true-formula(...), zápisu z style:map, do calcext podmínky, kterou LibreOffice importovalo také jako srovnání hodnoty buňky s 0. Oprava formulí vyšla ve v2.384.66 a oprava hodnot ve v2.384.69:

<!-- Špatně: LibreOffice ignoruje calcext:operator a importuje "equal to 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Správně: operátor cestuje uvnitř 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ávně: formulová pravidla užívají formula-is, relativní reference kotvené na základní buňku -->
<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í buňka je to, co dává relativním referencím význam. HotXLS kotví každé pravidlo na levou horní buňku jeho první oblasti rozsahu, takže formule napsaná pro C1 se vyhodnotí jako C2, C3 a tak dál po rozsahu, přesně jako v podmíněném formátování samotného Excelu. Výraz pravidla jde přes tenže překladač jako formule buněk, takže pole, sjednocení, celé sloupce i značky $ vypadají ve výše popsaných formách. Ze strany Delphi přidáváte pravidla úplně stejně jako u souboru .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Hodnotová pravidla: style:map cell-content()>100 plus calcext value ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: světle červená

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

  // Formulové pravidlo v Excel syntaxi (čárkové oddělovače, relativně 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: světle žlutá

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

Čtení ODS od Excelu a LibreOffice zpět do Delphi

Když HotXLS otevře soubor ODS, jeho čtečka přijímá oba dialekty podmíněného formátování i oba zápisy calcext a pravidlo nesené v obou formách nepočítá dvakrát. Reálné soubory přicházejí od tří zapisovačů, každý se svými zvyky:

• Staré a nové calcext. Soubory s atributem calcext:operator, včetně ODS zapsaných HotXLS před v2.384.69, jdou pořád přes legacy parsování. Formulové podmínky se poznají jako formula-is(...) i is-true-formula(...)
• Zápis style:map od Excelu. Excel předřazuje podmínkám of:, jako v of:cell-content-is-between(1,10), a u hodnotových pravidel vynechává základní buňku. Obojí se přijímá
• Prázdné buňky. Excel i LibreOffice staví mapu pro prázdné buňky na výchozí styl sloupce, ne na buňku, takže čtečka rozvíjí výchozí styly sloupců pro opakované buňky, dřív než sbírá mapy
• Stavba oblastí. Mapy se sbírají po buňkách, takže po přečtení listu čtečka slepuje buňky sdílející tutéž podmínku a základní buňku zpátky do rozsahů, nejdřív napříč každým řádkem a pak dolů po odpovídajících sloupcových rozsecích, a zahazuje každé pravidlo už načtené z calcext

Oprava ve v2.384.72 se týká číselných stylů, ne pravidel. Excel 16 i LibreOffice 26.2 zapisují formát General jako číselný styl, jehož prvek number:number nemá number:decimal-places, typicky <number:number number:min-integer-digits="1"/>. Čtečka HotXLS brala chybějící počet jako dvě pevná desetinná místa, takže každá hodnota ve stylu Default se importovala s 0.00 a 1.5 se zobrazilo jako 1.50. Od v2.384.72 se prostý číselný prvek bez desetinných míst, bez minimálních desetinných míst, bez seskupování a s nejvýš jednou celočíselnou číslicí mapuje na General a osamocený General nechá buňku úplně bez číselného formátu. Text kolem se udrží, jako v General" kg", a seskupená čísla si nechají předchozí mapování, protože Excel nemá seskupený formát General

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]; // indexér Sheets je od jedničky
    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;

    // Buňka ve stylu General Excelu se čte zpět bez číselného formátu
    // od v2.384.72, místo '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formule pravidel se vrací v syntaxi Excelu s čárkovými oddělovači, ve formě, kterou byste podali AddCondFormatExpression, takže pravidlo zapsané HotXLS se čte zpět jako identický řetězec. Širší obraz o tom, co cesta importu ODS udrží a co zahodí, přináší průvodce round-tripem otevírání a ukládání ODS v HotXLS; jak se opakované řádky od Excelu a LibreOffice rozvíjejí při importu, popisuje opakované řádky ODS jako běhy výšek řádků

Jaké jsou meze interoperability podmíněného formátování ODS v HotXLS?

Přístup se zdvojeným zápisem pokrývá hodnotová srovnávací pravidla a formulová pravidla a tam končí. Všechno ostatní je jednostranné nebo se nezapisuje vůbec:

• Barevné škály a datové lišty se zapisují jen jako prvky calcext, takže je LibreOffice ukáže a Excel ne
• Jiné druhy pravidel, jako sady ikon, textová pravidla, top-N, nadprůměr a pravidla duplicit, nemají v současném zapisovači výstup do ODS. Textové pravidlo jde obvykle přepsat na formulové, třeba ISNUMBER(SEARCH("late",B2)) nad B2:B200, čímž dosáhne do obou aplikací
• Pravidla celých sloupců a řádků jako C:C se kladou jen nad tabulkovou oblast, která je doopravdy zapsaná, místo nad všech 1 048 576 řádků, takže Excel vidí tato pravidla jen na buňkách, které v souboru existují
• Soubory jen se style:map. Nemá-li soubor blok calcext, interpretuje HotXLS relativní reference v formulových pravidlech z levého horního rohu znovu postaveného rozsahu, ne posunem od udané základní buňky
• Překrývající se pravidla od LibreOffice. Je-li jedna buňka pokrytá víc pravidly, zapisuje LibreOffice na ni mapu jen prvního pravidla. Takové soubory se z samotných style:map načíst nedají, a to je další důvod, proč čtečka při existenci obou preferuje calcext

Procesní limit je důležitější než kterákoli z těchto věcí. Vadám za těmito vydáními proklouzly round-tripy, které zapsaly ODS a přečetly je zpět v HotXLS, a některé by prošly i ruční kontrolou ve špatné aplikaci: formule celých sloupců fungovaly v LibreOffice, zatímco Excel ukazoval #NAME? a od v2.384.66 fungovala formulová pravidla v LibreOffice, zatímco Excel nezobrazoval žádná pravidla vůbec až do v2.384.69. Je-li interoperabilita ODS požadavkem, akceptačním testem je otevřít soubor v Excelu i v LibreOffice a porovnat, co které ukazuje. Táž disciplína platí pro styly, na které pravidla ukazují; článek o podmíněném formátování a stylech v HotXLS popisuje, jak se styly zvýraznění definují na straně sešitu

Rychlý přehled: ODS, které čtou obě aplikace

• Deklarujte xmlns:of a xmlns:msoxl na kořeni content.xml, jinak LibreOffice ukáže #VALUE! pro každou formuli (HotXLS od v2.384.56)
• Zapisujte reference jako [.A1], zachovejte každou $ a celé sloupce a řádky pište jako [.A:.A] a [.1:.1] (od v2.384.55 a v2.384.65)
• Používejte ; pro argumenty, ~ pro sjednocení referencí a | mezi řádky vloženého pole
• Zapište každé hodnotové či formulové pravidlo jako <style:map> na stylu každé pokryté buňky pro Excel a jako calcext podmínku pro LibreOffice (od v2.384.69)
• V calcext dejte operátor do hodnoty (>3, between(1,10)) a pište formulová pravidla formula-is(...) se základní buňkou (od v2.384.66 a v2.384.69)
• Počítejte při importu s číselným stylem General bez number:decimal-places; HotXLS ho od v2.384.72 čte jako General
• Ověřte každý nový exportní profil otevřením souboru v Excelu i v LibreOffice, nikdy jen v jednom z nich

HotXLS je nativní tabulková knihovna pro Delphi a C++Builder, která čte a zapisuje XLS, XLSX a ODS bez nainstalovaného Excelu či LibreOffice; plné zdrojové kódy, seznam funkcí a licencování najdete na stránce tabulkové komponenty HotXLS pro Delphi