HotXLS ODS interop: formule i pravila koje Excel čita

Da proizvede ODS datoteku koju Excel i LibreOffice čitaju ispravno, HotXLS svaku formulu piše u OpenFormula sintaksi pod deklariranim of: namespaceom, a svaki uvjetni format vrijednosti ili formule zapisuje dvaput: kao <style:map> na stilu svake pokrivene ćelije, što je jedini oblik koji Excel 16 čita, i kao blok calcext:conditional-formats, što je oblik kojem LibreOffice vjeruje. Svaka aplikacija ignorira onu polovicu koja je namijenjena drugoj, pa datoteka koja se u jednoj od njih prikazuje ispravno o drugoj ne dokazuje ništa

Ta zadnja rečenica jest lekcija iza šest HotXLS izdanja između v2.384.55 i v2.384.72. Svaki je popravak počeo s datotekom koju je HotXLS napisao, savršeno pročitao natrag, a jedna od dviju ciljnih aplikacija pogriješila. Ono što slijedi jest ono što svaka aplikacija stvarno prihvaća, markup koji zadovolji obje i HotXLS API pozivi koji to proizvode iz Delphija

Zašto se ODS datoteka u jednoj aplikaciji prikazuje ispravno, a u drugoj slomljeno?

ODS datoteka se u jednoj aplikaciji prikazuje ispravno, a u drugoj slomljeno jer Excel i LibreOffice čitaju različite dijelove istog paketa. OpenDocument formulama i uvjetnim oblikovanjima dopušta više od jednog legalnog zapisa, LibreOffice na to dodaje vlastiti prošireni namespace, a svaki potrošač bira podskup koji implementira. Pisač testiran protiv samo jednog potrošača rado će konvergirati prema markupu koji drugi tiho krivo čita

Nijedna aplikacija ne javlja grešku. LibreOffice u ćelijama čije formule nije mogao parsirati pokazuje #VALUE!; Excel otvara radnu knjigu s uvjetnim oblikovanjima koja jednostavno nedostaju, ili s formulom prerisanom u nešto što evaluira u #NAME? ili konstantu 0. Pisač koji radi povratni ciklus sa svojim vlastitim izlazom nikad ovo ne vidi. HotXLS je upravo u tu zamku upao s formula namespaceom: njegov je čitač prefiks of: poklapao kao običan tekst, pa je svaki samopovratni ciklus prolazio dok je LibreOffice u svakoj ćeliji formule pokazivao #VALUE!

Značajka	Excel 16 čita	LibreOffice 26.2 čita
Cijeli stupac zapisan kao A:A	Krivo čitano kao A:(A)	Tolerirano
Cijeli stupac zapisan kao [.A:.A]	Da	Da
Uvjetna oblikovanja u <style:map>	Da, jedini oblik koji čita	Ignorirano kad je calcext prisutan
Uvjetna oblikovanja u calcext:conditional-formats	Ignorirano	Da, preferirano
calcext pravilo vrijednosti s atributom calcext:operator	Ignorirano	Uvezeno kao "jednako 0"
calcext pravilo formule zapisano is-true-formula(...)	Ignorirano	Uvezeno kao usporedba vrijednosti s 0

OpenFormula u ODS-u: deklarirajte namespace, pa pogodite sintaksu

Ćelija formule u ODS-u čita se u LibreOfficeu samo kad se prefiks of: u table:formula razriješi u deklarirani XML namespace. Prefiks nije dekoracija. of: mapira se na urn:oasis:names:tc:opendocument:xmlns:of:1.2, a msoxl:, prefiks koji HotXLS koristi za formule koje njegov OpenFormula prevoditelj ne modelira, mapira se na http://schemas.microsoft.com/office/excel/formula. Prije v2.384.56 korijen content.xml koristio je oba prefiksa bez da ih deklarira, i LibreOffice uopće nije mogao prepoznati gramatiku formule

<!-- Prije v2.384.56: prefiksi korišteni, nikad deklarirani; 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 formula namespacea deklarirana na korijenu -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Kad je namespace popravljen, sam izraz i dalje mora biti valjani OpenFormula, kako ga definira OpenDocument 1.3 Part 4. Zamke su ona mjesta gdje Excel sintaksa i OpenFormula izgledaju slično, a nisu isti:

• Reference ćelija stoje u zagradama s točkom kao prefiksom, a oznake $ dio su reference: [.$A$1] i [.A$1:.$B2] valjani su OpenFormula. Prije v2.384.55 HotXLS pisač bacao je svaki $, pa su apsolutne reference dolazile kao relativne i kvarile se tek kad je netko kopirao ćeliju
• Cijeli stupci i redovi moraju koristiti zagradske oblike [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Goli of:=SUM(A:A) LibreOffice tolerira, ali ga Excel 16 otvara kao =SUM(A:(A)) s #NAME?, a reference redova i $A:$B pretvara u konstantu 0. HotXLS zagradske oblike piše od v2.384.65
• Argumenti funkcija odvajaju se s ;, ne s ,
• Unije referenci koriste operator ~: Excelov AREAS((A1,B2)) postaje AREAS(([.A1]~[.B2])). Ako se taj zarez prevede u ;, jedan argument unije postaje dva argumenta
• Ugrađena polja stupce odvajaju s ;, a redove s |: Excelovo {1,2;3,4} postaje {1;2|3;4}. Prije v2.384.55 HotXLS je proizvodio {1;2;3;4}, jedan red od četiri vrijednosti

Zarez je težak dio jer jedan Excel znak nosi tri značenja. Od v2.384.55 HotXLS pisač vodi stog zagrada tijekom prevođenja: ( izravno iza imena otvara poziv funkcije čiji zarezzi postaju ;; svaka druga ( je grupirajuća zagrada čiji zarezzi postaju ~; a zarezzi unutar {} su razdjelnici stupaca polja. S tim i s popravkom namespacea LibreOffice 26.2 točno je evaluirao svih osam probnih formula polja i unija, 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]; oznake $ 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 prevoditelj ne modelira vraćaju se na msoxl:= s nepromijenjenim Excel tekstom, pa i deklaracija msoxl znači. U trenutačnom pisaču taj put uključuje reference kvalificirane listom poput Sheet2!A1 i strukturirane reference tablica. HotXLS msoxl: formule čita natrag pri uvozu, pa vlastiti povratni ciklus zadržava izraz netaknutim, ali kako ih tretira druga aplikacija izvan je kontrole pisača. Ako formula od koje ovise vaši potrošači izađe s prefiksom msoxl:, otvorite datoteku u obje aplikacije prije isporuke

Zašto Excel ne vidi uvjetna oblikovanja zapisana samo kao calcext?

Excel 16 ne vidi calcext uvjetna oblikovanja jer ODS uvjetna oblikovanja čita isključivo iz <style:map> djece stilova ćelija i potpuno ignorira blok calcext:conditional-formats. Eksperiment koji to rješava kratak je: uzmite ODS spremljen u LibreOfficeu, izbrišite elemente style:map i Excel čita nula pravila; izbrišite umjesto toga calcext blok i Excel i dalje čita sva. LibreOffice ponaša se obrnuto. calcext je prošireni namespace LibreOffica, nije dio ODF standarda, i kad je calcext pravilo prisutno, LibreOffice uzme njega i ignorira style:map

Prije v2.384.69 HotXLS je pisao samo calcext, pa se ODS datoteka sa sasvim dobrim isticanjem u Excelu otvarala bez ijednog pravila vrijednosti i bez ijednog pravila formule. HotXLS sada piše oba oblika. style:map polovica koristi gramatiku uvjeta sheme OpenDocument (ODF 1.3 Part 3), s točnim zapisima koje i Excel 16 i LibreOffice 26.2 proizvode kad spremaju ODS:

<!-- Pojednostavljeno. Nosivi stil za svaku ćeliju od A1:A50 (dva pravila vrijednosti) -->
<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>

<!-- Nosivi stil za svaku ćeliju od 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>

Hvat sa style:map jest što živi na stilovima ćelija, pa je po ćeliji. Svaka ćelija u rasponu pravila mora nositi stil koji drži mapu, prazne ćelije uključene, inače pravilo tu ćeliju u Excelu jednostavno ne pokriva. HotXLS kopira postojeći stil oblikovanja svake ćelije, dodaje mapove i nosive stilove deduplicira po paru izvornog stila i teksta mape, pa raspon od 500 ćelija s istim oblikovanjem i dalje proizvodi jedan stil. Pisač također proširuje zapisanu tablicu do raspona pravila, što znači da se prazni završni redovi unutar pravila ispisuju umjesto da se odbacuju. Od v2.384.69 styles.xml nosi i prazan stil ćelije Default, pa style:apply-style-name="Default" uvijek ima cilj

Calcext zapis koji LibreOffice stvarno prihvaća

LibreOffice prihvaća calcext pravilo vrijednosti samo kad je operator usporedbe dio teksta vrijednosti, poput >3 ili between(1,10), a pravilo formule samo kad je zapisano formula-is(...). Obje točke koštale su HotXLS po jedno izdanje, jer pogrešni zapisi proizvode pravilo koje se uvozi bez greške, a zatim pogađa krive ćelije

Prva pogreška bila je atribut calcext:operator uz calcext:value. Čita se prirodno, ali je izmišljen: LibreOffice taj atribut ne poznaje, pa je svako pravilo vrijednosti uvezao kao "jednako 0". Druga je bila stavljanje is-true-formula(...), zapisa iz style:map, u calcext uvjet, koji je LibreOffice također uvezao kao usporedbu vrijednosti ćelije s 0. Popravak formule stigao je u v2.384.66, a popravak vrijednosti u v2.384.69:

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

<!-- Točno: operator putuje unutar vrijednosti -->
<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"/>

<!-- Točno: pravila formule koriste formula-is, relativne reference sidrene 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 svako pravilo sidri na gornju lijevu ćeliju njegova prvog područja raspona, pa formula napisana za C1 evaluira kao C2, C3 i tako dalje niz raspon, točno kao u Excelovu vlastitom uvjetnom oblikovanju. Izraz pravila prolazi kroz isti prevoditelj kao formule ćelija, pa polja, unije, cijeli stupci i oznake $ izlaze u gore opisanim oblicima. Na strani Delphija pravila dodajete točno kao što biste ih dodali za .xlsx datoteku

uses
  lxHandleX;

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

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

  // Pravilo formule u Excel sintaksi (zarezima odvojeno, relativno na 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: svijetlo ž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 LibreOffica natrag u Delphi

Kad HotXLS otvara ODS datoteku, njegov čitač prihvaća oba dijalekta uvjetnog oblikovanja i oba calcext zapisa, i ne broji pravilo dvaput kad ga datoteka nosi u oba oblika. Prave datoteke dolaze od tri pisača, svaki sa svojim navikama:

• Stari i novi calcext. Datoteke s atributom calcext:operator, uključujući ODS koji je HotXLS pisao prije v2.384.69, i dalje prolaze kroz naslijeđeno parsiranje. Uvjeti formule prepoznaju se kao formula-is(...) ili is-true-formula(...)
• Excelov style:map zapis. Excel uvjete prefiksira s of:, kao u of:cell-content-is-between(1,10), i izostavlja baznu ćeliju kod pravila vrijednosti. Oboje se prihvaća
• Prazne ćelije. Excel i LibreOffice mapu za prazne ćelije stavljaju na zadani stil stupca, a ne na ćeliju, pa čitač razriješuje zadane stilove stupaca za ponavljane ćelije prije sakupljanja mapa
• Ponovna izgradnja područja. Mapovi se sakupljaju po ćeliji, pa nakon čitanja lista čitač ćelije koje dijele isti uvjet i baznu ćeliju spaja natrag u raspone, prvo preko svakog reda, a zatim niz odgovarajuće stupčane raspone, i odbacuje svako pravilo već pročitano iz calcexta

Popravak iz v2.384.72 tiče se brojčanih stilova, ne pravila. Excel 16 i LibreOffice 26.2 oba zapisuju format General kao brojčani stil čiji element number:number nema number:decimal-places, tipično <number:number number:min-integer-digits="1"/>. HotXLS čitač je broj koji nedostaje tretirao kao dva fiksna decimalna mjesta, pa se svaka vrijednost u stilu Default uvozila s 0.00 i 1.5 se prikazivalo kao 1.50. Od v2.384.72 element običnog broja bez decimalnih mjesta, bez minimalnih decimala, bez grupiranja i s najviše jednom cifrom cijelog dijela mapira se na General, a samostalni General ostavlja ćeliju potpuno bez brojčanog formata. Tekst oko njega zadržava se, kao u General" kg", a grupirani brojevi zadržavaju prijašnje mapiranje jer Excel nema grupirani 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]; // indeksiranje 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 natrag bez brojčanog formata
    // od v2.384.72, umjesto '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formule pravila vraćaju se u Excel sintaksi sa zarezima, u istom obliku koji biste proslijedili u AddCondFormatExpression, pa pravilo napisano u HotXLS-u čita se natrag kao identičan niz. Za širu sliku o tome što put uvoza ODS-a zadržava, a što odbacuje, pogledajte HotXLS vodič kroz povratni ciklus otvaranja i spremanja ODS-a; za to kako se ponovljeni redovi iz Excela i LibreOffica šire pri uvozu, pogledajte ODS ponovljene redove kao nizove visina redova

Koji su limiti HotXLS interopa uvjetnog oblikovanja u ODS-u?

Pristup s dvostrukim markupom pokriva pravila usporedbe vrijednosti i pravila formule, i tu staje. Sve ostalo je jednostrano ili se uopće ne zapisuje:

• Color scales i data bars pišu se samo kao calcext elementi, pa ih LibreOffice prikazuje, a Excel ne
• Ostale vrste pravila, poput icon setova, tekstualnih pravila, top-N, iznad-prosjeka i pravila duplicata, u trenutačnom pisaču nemaju ODS izlaz. Tekstualno se pravilo obično može preformulirati kao pravilo formule, na primjer ISNUMBER(SEARCH("late",B2)) nad B2:B200, koje tada doseže obje aplikacije
• Pravila preko cijelog stupca i reda poput C:C polažu se samo preko područja tablice koje je stvarno zapisano, a ne preko svih 1,048,576 redova, pa Excel ta pravila vidi samo na ćelijama koje postoje u datoteci
• Datoteke samo sa style:map. Kad datoteka nema calcext blok, HotXLS relativne reference u pravilima formule tumači od gornjeg lijevog kuta ponovno izgrađenog raspona, a ne pomakom od navedene bazne ćelije
• Preklapajuća pravila iz LibreOffica. Kad jednu ćeliju pokriva više pravila, LibreOffice na nju upisuje samo mapu prvog pravila. Takve se datoteke ne mogu u potpunosti čitati samo iz style:map-a, što je još jedan razlog zašto čitač preferira calcext kad oba postoje

Procesni limit važniji je od svih navedenih. Defekti iza tih izdanja prošli su kroz povratne cikluse koji su ODS napisali i pročitali natrag s HotXLS-om, a neki bi prošli i ručnu provjeru u krivoj aplikaciji: formule preko cijelog stupca radile su u LibreOfficeu dok je Excel pokazivao #NAME?, a od v2.384.66 pravila formule radila su u LibreOfficeu dok Excel sve do v2.384.69 nije pokazivao nijedno pravilo. Ako je ODS interop zahtjev, prihvatni test jest otvaranje datoteke u Excelu i u LibreOfficeu i usporedba onoga što svaki prikazuje. Ista se disciplina odnosi i na stilove na koje pravila pokazuju; HotXLS članak o uvjetnom oblikovanju i stilovima pokriva kako se stilovi isticanja definiraju na strani radne knjige

Brza referenca: ODS koji obje aplikacije čitaju

• Deklarirajte xmlns:of i xmlns:msoxl na korijenu content.xml, inače LibreOffice za svaku formulu pokazuje #VALUE! (HotXLS od v2.384.56)
• Reference pišite kao [.A1], zadržite svaki $, a cijele stupce 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 ugrađenog polja
• Svako pravilo vrijednosti ili formule za Excel pišite kao <style:map> na stilu svake pokrivene ćelije, a za LibreOffice kao calcext uvjet (od v2.384.69)
• U calcextu operator stavite u vrijednost (>3, between(1,10)), a pravila formule pišite kao formula-is(...) s baznom ćelijom (od v2.384.66 i v2.384.69)
• Pri uvozu očekujte brojčani stil General bez number:decimal-places; HotXLS ga od v2.384.72 čita kao General
• Svaki novi izvozni profil provjerite otvaranjem datoteke i u Excelu i u LibreOfficeu, nikad u samo jednoj od njih

HotXLS je nativna biblioteka proračunskih tablica za Delphi i C++Builder koja čita i piše XLS, XLSX i ODS bez instaliranog Excela ili LibreOffica; puni izvorni kod, popis značajki i licenciranje nalaze se na stranici HotXLS Delphi proračunske komponente