HotXLS ODS interop: formule i pravila koje Excel čita

Da proizvede ODS fajl koji i Excel i LibreOffice čitaju ispravno, HotXLS piše svaku formulu u OpenFormula sintaksi pod deklarisanom of: namespace-om, i piše svako uslovno formatiranje vrednosti ili formule dva puta: kao <style:map> na stilu svake pokrivene ćelije, što je jedini oblik koji Excel 16 čita, i kao blok calcext:conditional-formats, oblik kojem LibreOffice veruje. Svaka aplikacija ignoriše pola namenjeno drugoj, pa fajl koji se u jednoj prikazuje ispravno ne dokazuje ništa o drugoj

Ta poslednja rečenica je lekcija iza šest HotXLS izdanja između v2.384.55 i v2.384.72. Svaka popravka počela je s fajlom koji je HotXLS napisao, čitao nazad savršeno, a koju je jedna od dve ciljne aplikacije pogrešila. Dalje sledi ono što svaka aplikacija zapravo prima, označavanje koje zadovoljava obe, i HotXLS API pozivi koji to proizvode iz Delphija

Zašto ODS fajl izgleda dobro u jednoj aplikaciji a pokvaren u drugoj?

ODS fajl izgleda dobro u jednoj aplikaciji a pokvaren u drugoj jer Excel i LibreOffice čitaju različite delove istog paketa. OpenDocument daje formulama i uslovnim formatiranjima više od jednog legalnog zapisa, LibreOffice dodaje svoju ekstenzionu namespace nadzemlju, i svaki potrošač bira podskup koji implementira. Writer testiran nad samo jednim potrošačem srećno će se ugnestiti u označavanje koje drugi tiho pogrešno čita

Nijedna aplikacija ne prijavljuje grešku. LibreOffice pokazuje #VALUE! u ćelijama čije formule nije mogao da parsira; Excel otvara radnu svesku s uslovnim formatiranjima koja jednostavno nisu tu, ili s formulom preređenom u nešto što daje #NAME? ili konstantu 0. Writer koji radi round-trip sopstvenog izlaza nikad ovo ne vidi. HotXLS je udario baš u tu zamku s namespace-om formula: njegov čitač je poklapao of: prefiks kao običan tekst, pa je svaki sopstveni round-trip prolazio dok je LibreOffice pokazivao #VALUE! u svakoj ćeliji s formulom

Funkcionalnost	Excel 16 čita	LibreOffice 26.2 čita
Cela kolona zapisana kao A:A	Pogrešno čitana kao A:(A)	Tolerisano
Cela kolona zapisana kao [.A:.A]	Da	Da
Uslovna formatiranja u <style:map>	Da, jedini oblik koji čita	Ignorišu se kad je calcext prisutan
Uslovna formatiranja u calcext:conditional-formats	Ignorišu se	Da, preferirano
calcext pravilo vrednosti s atributom calcext:operator	Ignoriše se	Uvozi se kao "jednako 0"
calcext pravilo formule zapisano is-true-formula(...)	Ignoriše se	Uvozi se kao poređenje vrednosti s 0

OpenFormula u ODS-u: deklarišite namespace, pa pogodite sintaksu

Ćelija s formulom u ODS-u je čitljiva za LibreOffice samo kad se of: prefiks u table:formula razrešuje u deklarisan XML namespace. Prefiks nije dekoracija. of: se preslikava u urn:oasis:names:tc:opendocument:xmlns:of:1.2, a msoxl:, prefiks koji HotXLS koristi za formule koje njegov OpenFormula prevodilac ne modeluje, u http://schemas.microsoft.com/office/excel/formula. Pre v2.384.56 koren content.xml-a koristio je oba prefiksa bez da ih deklariše, i LibreOffice nije mogao uopšte da identifikuje gramatiku formule

<!-- Pre v2.384.56: prefiks korišćen, nikad deklarisan; LibreOffice pokazuje #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 namespace-a za formule deklarisana na korenu -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

S namespace-om sređenim, sam izraz i dalje mora biti valjan OpenFormula, kako je definisano u OpenDocument 1.3 Part 4. Zamke su mesta gde Excel sintaksa i OpenFormula izgledaju slično a nisu isto:

• Reference ćelija su u zagradama s tačkom ispred, i $ markeri su deo reference: [.$A$1] i [.A$1:.$B2] su valjana OpenFormula. Pre v2.384.55 HotXLS writer je odbacivao svaki $, pa su apsolutne reference nazad dolazile kao relativne i kvarile se tek kad bi neko kopirao ćeliju
• Cele kolone i redovi moraju koristiti oblik u zagradama [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Goli of:=SUM(A:A) LibreOffice toleriše, ali ga Excel 16 otvara kao =SUM(A:(A)) s #NAME?, i pretvara reference redova i $A:$B u konstantu 0. HotXLS piše oblik u zagradama od v2.384.65
• Argumenti funkcija se razdvajaju s ;, ne s ,
• Unije referenci koriste operator ~: Excel AREAS((A1,B2)) postaje AREAS(([.A1]~[.B2])). Prevod te zapete u ; umesto toga pretvara jedan argument unije u dva argumenta
• Inline nizovi razdvajaju kolone s ; i redove s |: Excel {1,2;3,4} postaje {1;2|3;4}. Pre v2.384.55 HotXLS je proizvodio {1;2;3;4}, jedan red s četiri vrednosti

Zapeta je najteži deo, jer jedan Excel znak nosi tri značenja. Od v2.384.55 HotXLS writer vodi stek zagrada dok prevodi: ( odmah iza imena otvara poziv funkcije čije zapete postaju ;; svaka druga ( je grupišuća zagrada čije zapete postaju ~; a zapete unutar {} su razdelnici kolona niza. S tim i popravkom namespace-a, LibreOffice 26.2 je izračunao svih osam probnih formula za nizove i unije ispravno, uključujući INDEX i AREAS nad unijama

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 kao of:=SUM([.A:.A]) od v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Zapisuje se kao of:=[.A1]*[.$B$1]; $ markeri prežive od v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formule koje prevodilac ne modeluje padaju na msoxl:= s nepromenjenim Excel tekstom, pa je zbog toga i deklaracija msoxl važna. U trenutnom writeru taj put obuhvata reference kvalifikovane listom poput Sheet2!A1 i struktuirane reference tabela. HotXLS čita msoxl: formule nazad pri uvozu, pa njegov sopstveni round-trip zadržava izraz netaknutim, ali kako će ih tretirati druga aplikacija van je kontrole writera. Ako formula od koje vaši potrošači zavise izađe s prefiksom msoxl:, otvorite fajl u obe aplikacije pre isporuke

Zašto Excel ne vidi uslovna formatiranja zapisana samo kao calcext?

Excel 16 ne vidi calcext uslovna formatiranja jer ODS uslovna formatiranja čita isključivo iz <style:map> dece stilova ćelija i blok calcext:conditional-formats ignoriše potpuno. Eksperiment koji to rešava je kratak: uzmite ODS sačuvan iz LibreOffice-a, obrišite style:map elemente, i Excel čita nula pravila; obrišite umesto toga calcext blok, i Excel i dalje čita sva. LibreOffice se ponaša obrnuto. calcext je ekstenzioni namespace LibreOffice-a, nije deo ODF standarda, i kad je calcext pravilo prisutno LibreOffice ga uzima i ignoriše style:map

Pre v2.384.69 HotXLS je pisao samo calcext, pa je ODS fajl sa sasvim dobrim isticanjem u Excelu otvaran bez ijednog pravila vrednosti i bez ijednog pravila formule. HotXLS sada piše oba oblika. Pola s style:map-om koristi gramatiku uslova šeme OpenDocument (ODF 1.3 Part 3), s tačnim zapisima koje i Excel 16 i LibreOffice 26.2 proizvode kad čuvaju ODS:

<!-- Pojednostavljeno. Nosiljački stil za svaku ćeliju A1:A50 (dva pravila vrednosti) -->
<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>

<!-- Nosiljački stil za svaku ćeliju C1:C50 (jedno pravilo formule) -->
<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>

Zamka s style:map-om je što živi na stilovima ćelija, dakle po ćeliji. Svaka ćelija u opsegu pravila mora nositi stil koji drži mapu, prazne ćelije uključene, ili pravilo jednostavno ne pokriva tu ćeliju u Excelu. HotXLS kopira postojeći stil formatiranja svake ćelije, dodaje mape i uklanja duplikate nosiljačkih stilova po paru originalni stil plus tekst mape, pa opseg od 500 ćelija s istim formatiranjem i dalje daje jedan stil. Writer takođe proteže zapisanu tabelu do opsega pravila, što znači da se prazni redovi repa unutar pravila emituju umesto da se odbace. Od v2.384.69 styles.xml takođe nosi prazan ćelijski stil Default, pa style:apply-style-name="Default" uvek ima cilj

calcext zapis koji LibreOffice zapravo prima

LibreOffice prima calcext pravilo vrednosti samo kad je operator poređenja deo teksta vrednosti, poput >3 ili between(1,10), i pravilo formule samo kad je zapisano formula-is(...). Oba su detalja HotXLS koštala jednog izdanja, jer pogrešni zapisi daju pravilo koje se uvozi bez greške, a onda poklapa pogrešne ćelije

Prva greška bila je atribut calcext:operator uz calcext:value. Čita se prirodno, ali je izmišljen: LibreOffice ne poznaje taj atribut, pa je svako pravilo vrednosti uvozio kao „jednako 0“. Druga je bilo stavljanje is-true-formula(...), zapisa iz style:map-a, u calcext uslov, koji je LibreOffice uvozio kao poređenje ćelijske vrednosti s 0. Popravka za formule isporučena je u v2.384.66, a za vrednosti u v2.384.69:

<!-- Pogrešno: LibreOffice ignoriše calcext:operator i uvozi „jednako 0“ -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Ispravno: operator putuje unutar vrednosti -->
<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"/>

<!-- Ispravno: pravila formule koriste formula-is, relativne reference usidrene na baznu ćeliju -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Bazna ćelija je ono što relativnim referencama daje značenje. HotXLS sidri svako pravilo na gornjo-levu ćeliju njegove prve oblasti opsega, pa formula napisana za C1 vrednuje se kao C2, C3 i dalje niz opseg, baš kao u Excelovom sopstvenom uslovnom formatiranju. Izraz pravila prolazi kroz isti prevodilac kao ćelijske formule, pa nizovi, unije, cele kolone i $ markeri izađu u gore opisanim oblicima. Na Delphi strani pravila dodajete tačno kao što biste za .xlsx fajl

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Pravila vrednosti: style:map cell-content()>100 plus calcext vrednost ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: svetlocrvena

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

  // Pravilo formule u Excel sintaksi (zapete kao razdelnici, relativno na C1):
  // style:map is-true-formula(...) i calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: svetložuta

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

Čitanje ODS-a iz Excela i LibreOffice-a nazad u Delphi

Kad HotXLS otvara ODS fajl, njegov čitač prima oba dijalekta uslovnih formatiranja i oba calcext zapisa, i ne broji pravilo dva puta kad ga fajl nosi u oba oblika. Pravi fajlovi dolaze od tri writera, svaki sa svojim navikama:

• Stari i novi calcext. Fajlovi s atributom calcext:operator, uključujući ODS napisan HotXLS-om pre v2.384.69, i dalje prolaze kroz legacy parsiranje. Uslovi formule prepoznaju se kao formula-is(...) ili is-true-formula(...)
• Excelov zapis style:map. Excel prefiksuje uslove s of:, kao u of:cell-content-is-between(1,10), i izostavlja baznu ćeliju kod pravila vrednosti. Oboje se prima
• Prazne ćelije. Excel i LibreOffice oba stavljaju mapu za prazne ćelije na podrazumevani stil kolone umesto na ćeliju, pa čitač razrešuje podrazumevane stilove kolona za ponovljene ćelije pre nego što sabere mape
• Obnova oblasti. Mape se skupljaju po ćeliji, pa čitač posle čitanja lista spaja ćelije koje dele isti uslov i baznu ćeliju nazad u opsege, prvo kroz svaki red pa naniže po poklapajućim kolonskim rasponima, i odbacuje pravilo već pročitano iz calcext-a

Popravka v2.384.72 tiče se brojevnih stilova, ne pravila. Excel 16 i LibreOffice 26.2 oba pišu General format kao brojni stil čiji element number:number nema number:decimal-places, tipično <number:number number:min-integer-digits="1"/>. HotXLS čitač je tretirao izostavljeni broj kao dve fiksne decimale, pa se svaka vrednost u stilu Default uvozila s 0.00 i 1.5 se prikazivalo kao 1.50. Od v2.384.72 običan brojni element bez decimalnih mesta, bez minimuma decimale, bez grupisanja i s najviše jednom celobrojnom cifrom preslikava se u General, a usamljeni General ostavlja ćeliju bez ikakvog formata broja. Tekst oko njega se čuva, kao u General" kg", a grupisani brojevi zadržavaju prethodno preslikavanje jer Excel nema grupisani 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]; // indekser Sheets kreće od 1
    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;

    // Ćelija u Excelovom General stilu čita se nazad bez formata broja
    // od v2.384.72, umesto '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formule pravila vraćaju se u Excel sintaksi sa zapetama kao razdelnicima, istim oblikom koji biste prosledili AddCondFormatExpression-u, pa pravilo napisano HotXLS-om čita se nazad kao identičan string. Za širu sliku o tome šta put ODS uvoza čuva a šta odbacuje, pogledajte HotXLS vodič kroz ODS otvaranje i čuvanje; a o tome kako se ponovljeni redovi iz Excela i LibreOffice-a šire pri uvozu, pogledajte ODS ponovljene redove kao nizove visina redova

Koji su limiti HotXLS ODS interopera uslovnih formatiranja?

Pristup s dvostrukim označavanjem pokriva pravila poređenja vrednosti i pravila formule, i tu staje. Sve ostalo je jednostrano ili se uopšte ne piše:

• Color scales i data bars pišu se samo kao calcext elementi, pa ih LibreOffice pokazuje a Excel ne
• Ostale vrste pravila, poput icon setova, tekstualnih pravila, top-N, iznad-proseka i pravila duplikata, nemaju ODS izlaz u trenutnom writeru. Tekstualno pravilo se obično može izreći kao pravilo formule, na primer ISNUMBER(SEARCH("late",B2)) nad B2:B200, što zatim stiže do obe aplikacije
• Pravila cele kolone i celog reda poput C:C polažu se samo preko oblasti tabele koja je zaista zapisana, umesto preko svih 1.048.576 redova, pa Excel ova pravila vidi samo na ćelijama koje postoje u fajlu
• Fajlovi samo sa style:map. Kad fajl nema calcext blok, HotXLS tumači relativne reference u pravilima formula od gornje-levog ugla obnovljenog opsega, ne pomeranjem od navedene bazne ćelije
• Preklapajuća pravila iz LibreOffice-a. Kad jednu ćeliju pokriva više pravila, LibreOffice na nju piše samo mapu prvog pravila. Takvi fajlovi ne mogu se potpuno pročitati samo iz style:map-a, što je još jedan razlog da čitač daje prednost calcext-u kad oba postoje

Limit procesa znači više od svih ovih. Defekti iza ovih izdanja prošli su kroz round-tripove koji su pisali ODS i čitali ga naziv HotXLS-om, a neki bi prošli i kroz ručnu proveru u pogrešnoj aplikaciji: formule cele kolone radile su u LibreOffice-u dok je Excel pokazivao #NAME?, a od v2.384.66 pravila formula radila su u LibreOffice-u dok Excel sve do v2.384.69 nije pokazivao nijedno pravilo. Ako je ODS interoperabilnost zahtev, prihvatni test je otvaranje fajla u Excelu i u LibreOffice-u i poređenje onoga što svaki pokazuje. Ista disciplina važi za stilove na koje pravila pokazuju; HotXLS tekst o uslovnom formatiranju i stilovima pokriva kako se stili isticanja definišu sa strane radne sveske

Brzi podsetnik: ODS koji obe aplikacije čitaju

• Deklarišite xmlns:of i xmlns:msoxl na korenu content.xml-a, ili LibreOffice pokazuje #VALUE! za svaku formulu (HotXLS od v2.384.56)
• Pišite reference kao [.A1], zadržite svaki $, a cele kolone i redove pišite kao [.A:.A] i [.1:.1] (od v2.384.55 i v2.384.65)
• Koristite ; za argumente, ~ za unije referenci i | između redova inline niza
• Pišite svako pravilo vrednosti ili formule kao <style:map> na stilu svake pokrivene ćelije za Excel, i kao calcext uslov za LibreOffice (od v2.384.69)
• U calcext stavite operator u vrednost (>3, between(1,10)) i zapišite pravila formule kao formula-is(...) s baznom ćelijom (od v2.384.66 i v2.384.69)
• Očekujte pri uvozu brojni stil General bez number:decimal-places; HotXLS ga od v2.384.72 čita kao General
• Proverite svaki novi izvozni profil otvaranjem fajla i u Excelu i u LibreOffice-u, nikad samo u jednoj od njih

HotXLS je nativna Delphi i C++Builder biblioteka za tabele koja čita i piše XLS, XLSX i ODS bez instaliranog Excela ili LibreOffice-a; pun izvorni kod, lista funkcija i licenciranje su na stranici HotXLS Delphi spreadsheet component