HotXLS ODS: formule in pravila, ki jih bere Excel

Da nastane datoteka ODS, ki jo Excel in LibreOffice oba prebereta pravilno, HotXLS zapiše vsako formulo v OpenFormula sintaksi pod deklariranim imenskim prostorom of:, vsak pogojni format vrednosti ali formule pa zapiše dvakrat: kot <style:map> na stilu vsake pokrite celice, kar je edina oblika, ki jo Excel 16 bere, in kot blok calcext:conditional-formats, oblika, ki ji LibreOffice zaupa. Vsaka aplikacija ignorira polovico, namenjeno drugi, zato datoteka, ki se v eni od njiju prikaže pravilno, o drugi ne dokazuje nič

Zadnji stavek je pouk za šest izdaj HotXLS med v2.384.55 in v2.384.72. Vsak popravek se je začel z datoteko, ki jo je HotXLS zapisal, sam popolnoma prebral nazaj, ena od obeh ciljnih aplikacij pa zgrešila. Sledi tisto, kar vsaka aplikacija dejansko sprejme, označevanje, ki zadovolji oboje, ter klici HotXLS API-ja, ki ga izdelajo iz Delphija

Zakaj se datoteka ODS v eni aplikaciji zdi v redu, v drugi pa polomljena?

Datoteka ODS se v eni aplikaciji zdi v redu, v drugi pa polomljena, ker Excel in LibreOffice bereta različne dele istega paketa. OpenDocument dovoljuje formulam in pogojnim formatom več kot eno veljavno črkovanje, LibreOffice doda na vrh svoj lasten razširitveni imenski prostor, vsak potrošnik pa izbere podmnožico, ki jo implementira. Zapisovalnik, testiran proti samo enem potrošniku, se bo brezskrbno usmeril v označevanje, ki ga drugi tiho prebere narobe

Nobena aplikacija ne poroča o napaki. LibreOffice v celice, katerih formul ne zna razčleniti, pokaže #VALUE!; Excel odpre delovni zvezek s pogojnimi formati, ki jih enostavno ni, ali s formulo, prepisano v kaj, kar se vrednoti v #NAME? ali konstanto 0. Zapisovalnik, ki pretvarja svoj izhod naprej in nazaj, tega ne vidi nobenega. HotXLS se je ujel točno to past s formulskim imenskim prostorom: njegov bralnik je ujel predpono of: kot čisto besedilo, tako da je vsak krog skozi samega sebe šel gladko, LibreOffice pa je v vsaki formulski celici prikazoval #VALUE!

Zmožnost	Excel 16 prebere	LibreOffice 26.2 prebere
Celi stolpec zapisan kot A:A	Prebran narobe kot A:(A)	Toleriran
Celi stolpec zapisan kot [.A:.A]	Da	Da
Pogojni formati v <style:map>	Da, edina oblika, ki jo bere	Ignorirani, kadar je navzoč calcext
Pogojni formati v calcext:conditional-formats	Ignorirani	Da, prednostni
Pravilo vrednosti calcext z atributom calcext:operator	Ignorirano	Uvoženo kot "enako 0"
Formulsko pravilo calcext zapisano is-true-formula(...)	Ignorirano	Uvoženo kot primerjava vrednosti z 0

OpenFormula v ODS: deklarirajte imenski prostor, nato pa zadete sintakso

Formulska celica v ODS je za LibreOffice berljiva samo, kadar se predpona of: v table:formula razreši v deklariran XML imenski prostor. Predpona ni okras. of: se preslika v urn:oasis:names:tc:opendocument:xmlns:of:1.2, msoxl:, predpona, ki jo HotXLS uporablja za formule, ki jih njegov prevajalnik OpenFormula ne modelira, pa v http://schemas.microsoft.com/office/excel/formula. Pred v2.384.56 je koren content.xml uporabljal obe predponi, brez da bi ju deklariral, LibreOffice pa sploh ni mogel prepoznati formulskih slovnic

<!-- Pred v2.384.56: predpona rabljena, nikoli deklarirana; LibreOffice pokaže #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 formulska imenska prostora deklarirana 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" ...>

Z uredjenim imenskim prostorom mora biti izraz sam še vedno veljaven OpenFormula, kot ga definira OpenDocument 1.3, 4. del. Pasti so mesta, kjer si Excelova sintaksa in OpenFormula izgledata podobno, pa nista ista:

• Reference celic so v oglatih oklepajih s predpono pike, oznake $ pa so del reference: [.$A$1] in [.A$1:.$B2] sta veljavna OpenFormula. Pred v2.384.55 je zapisovalnik HotXLS odvrgel vsak $, zato so absolutne reference prišle nazaj kot relativne in so šle narobe šele, ko je nekdo celico kopiral
• Celi stolpci in vrstice morajo uporabiti oklepajno obliko [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Gol of:=SUM(A:A) LibreOffice tolerira, Excel 16 pa ga odpre kot =SUM(A:(A)) z #NAME?, reference na vrstice in $A:$B pa spremeni v konstanto 0. HotXLS oklepajno obliko zapiše od v2.384.65
• Argumenti funkcij so ločeni s ;, ne z ,
• Unije referenc uporabijo operator ~: Excelov AREAS((A1,B2)) postane AREAS(([.A1]~[.B2])). Če to vejico prevedete v ;, se en argument unije spremeni v dva argumenta
• Vgrajene matrike ločijo stolpce s ; in vrstice z |: Excelova {1,2;3,4} postane {1;2|3;4}. Pred v2.384.55 je HotXLS izdelal {1;2;3;4}, eno vrstico štirih vrednosti

Vejica je težaven del, ker en Excelov znak nosi tri pomene. Od v2.384.55 zapisovalnik HotXLS med prevajanjem vodi sklad oklepajev: ( tik za imenom odpre klic funkcije, katerega vejice postanejo ;; vsak drug ( je združevalni oklepaj, katerega vejice postanejo ~; vejice znotraj {} pa so ločila stolpcev matrike. S tem in s popravkom imenskega prostora je LibreOffice 26.2 pravilno vrednotil vseh osem preizkusnih formul matrik in unij, vključno s INDEX in AREAS nad unijami

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;

    // Zapisano kot of:=SUM([.A:.A]) od v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Zapisano kot of:=[.A1]*[.$B$1]; oznake $ preživijo od v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formule, ki jih prevajalnik ne modelira, gredo na rezervo msoxl:= z nespremenjenim Excelovim besedilom, zato je pomembna tudi deklaracija msoxl. V trenutnem zapisovalniku ta pot vključuje reference s kvalifikatorjem lista, kot je Sheet2!A1, in strukturne reference na tabele. HotXLS formule msoxl: ob uvozu prebere nazaj, tako da njegov lasten krog ohrani izraz nedotaknjen, kako z njimi ravna druga aplikacija, pa je zunaj nadzora zapisovalnika. Če formula, od katere so vaši potrošniki odvisni, pride ven s predpono msoxl:, odprite datoteko v obeh aplikacijah, preden jo odprete v svet

Zakaj Excel ne vidi pogojnih formatov, zapisanih samo kot calcext?

Excel 16 ne vidi pogojnih formatov calcext, ker pogojne formate ODS bere izključno iz otrok <style:map> stilov celic in blok calcext:conditional-formats ignorira v celoti. Poskus, ki to poravna, je kratek: vzemite ODS, shranjen z LibreOffice, izbrišite elemente style:map, in Excel prebere nič pravil; izbrišite namesto tega blok calcext, in Excel še vedno prebere vsa. LibreOffice se vede obratno. calcext je razširitveni imenski prostor LibreOffice, ni del standarda ODF, kadar je prisotno calcext pravilo pa ga LibreOffice vzame in ignorira style:map

Pred v2.384.69 je HotXLS zapisal samo calcext, zato se je datoteka ODS s popolnoma dobrim poudarjanjem v Excelu odprla brez pravil vrednosti in brez formulskih pravil. HotXLS zdaj zapiše obe obliki. Polovica style:map uporablja slovnico pogojev sheme OpenDocument (ODF 1.3, 3. del), z natanko tistimi črkovanji, ki jih Excel 16 in LibreOffice 26.2 oba izdelata, kadar shranita ODS:

<!-- Poenostavljeno. Nosilni stil za vsako celico A1:A50 (dve pravili 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>

<!-- Nosilni stil za vsako celico C1:C50 (eno formulsko pravilo) -->
<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>

Past pri style:map je, da živi na stilih celic, torej je na celico. Vsaka celica v obsegu pravila mora nositi stil, ki drži preslikavo, prazne celice vključene, sicer pravilo te celice v Excelu preprosto ne pokrije. HotXLS kopira obstoječi oblikovalski stil vsake celice, pripne preslikave in nosilne stile razdvoji po paru izvirni stil in besedilo preslikave, tako da obseg 500 celic z identičnim oblikovanjem še vedno izdela en sam stil. Zapisovalnik tudi raztegne zapisano tabelo do obsega pravila, kar pomeni, da se prazne repne vrstice znotraj pravila izdajo, namesto da bi jih odvrgli. Od v2.384.69 styles.xml nosi tudi prazen celični stil Default, tako da style:apply-style-name="Default" vedno ima tarčo

Črkovanje calcext, ki ga LibreOffice dejansko sprejme

LibreOffice sprejme pravilo vrednosti calcext samo, kadar je primerjalni operator del besedila vrednosti, na primer >3 ali between(1,10), formulsko pravilo pa samo, kadar je zapisano formula-is(...). Za obe točki je HotXLS plačal po eno izdajo, ker napačna črkovanja izdelajo pravilo, ki se uvozi brez napake in potem ujame napačne celice

Prva napaka je bil atribut calcext:operator poleg calcext:value. Bere se naravnost, a je izmišljen: LibreOffice tega atributa ne pozna, zato je uvozil vsako pravilo vrednosti kot »enako 0«. Druga je bila, da se je v calcext pogoj dalo is-true-formula(...), črkovanje style:map, kar je LibreOffice uvozil prav tako kot primerjavo celične vrednosti z 0. Formulski popravek je odpotoval v v2.384.66, popravek vrednosti pa v v2.384.69:

<!-- Narobe: LibreOffice ignorira calcext:operator in uvozi kot "enako 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Prav: operator potuje znotraj 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"/>

<!-- Prav: formulska pravila uporabijo formula-is, relativne reference zasidrane na osnovno celico -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Osnovna celica je tista, ki relativnim referencam da pomen. HotXLS zasidra vsako pravilo na levo-zgornjo celico svojega prvega območja obsega, zato se formula, napisana za C1, vrednoti kot C2, C3 in tako naprej po obsegu navzdol, točno kot v Excelovem lastnem pogojnem oblikovanju. Izraz pravila gre skozi isti prevajalnik kot celične formule, zato matrike, unije, celi stolpci in oznake $ pridejo ven v zgoraj opisanih oblikah. Na strani Delphija pravila dodate točno tako, kot bi jih za datoteko .xlsx

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: svetlo rdeča

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

  // Formulsko pravilo v Excelovi sintaksi (ločila z vejico, relativno na C1):
  // style:map is-true-formula(...) in calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: svetlo rumena

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

Branje datotek ODS iz Excela in LibreOffice nazaj v Delphi

Ko HotXLS odpre datoteko ODS, njegov bralnik sprejme oba narečji pogojnih formatov in obe črkovanji calcext, pravil, ki jih datoteka nosi v obeh oblikah, pa ne prešteje dvakrat. Prave datoteke pridejo od treh zapisovalnikov, vsak s svojimi navadami:

• Staro in novo calcext. Datoteke z atributom calcext:operator, vključno z datotekami ODS, ki jih je zapisal HotXLS pred v2.384.69, gredo še naprej skozi zapuščinsko razčlenjevanje. Formulski pogoji so prepoznani kot formula-is(...) ali is-true-formula(...)
• Črkovanje style:map od Excela. Excel pogoje predponi z of:, kot v of:cell-content-is-between(1,10), in na pravilih vrednosti izpusti osnovno celico. Oboje je sprejeto
• Prazne celice. Excel in LibreOffice oba postavita preslikavo za prazne celice na privzeti stil stolpca, ne na celico, zato bralnik razreši privzete stile stolpcev za ponovljene celice, preden zbere preslikave
• Znova izgradnja območij. Preslikave so zbrane na celico, zato bralnik po branju lista združi celice, ki si delijo isti pogoj in osnovno celico, nazaj v obsege — najprej čez vsako vrstico, nato po ujemajočih razponih stolpcev — in odvrže vsako pravilo, ki ga je že prebral iz calcext

Popravek v2.384.72 zadeva številčne stile, ne pravil. Excel 16 in LibreOffice 26.2 oba zapišeta format General kot številčni stil, katerega element number:number nima number:decimal-places, običajno <number:number number:min-integer-digits="1"/>. Bralnik HotXLS je manjkajoče število obravnaval kot dve fiksni decimalni mesti, zato se je vsaka vrednost v stilu Default uvozila s 0.00 in je 1.5 izgledalo kot 1.50. Od v2.384.72 se čist številčni element brez decimalnih mest, brez najmanjših decimalk, brez grupiranja in z največ eno celoštevilčno mesto preslika v General, samoten General pa pusti celico brez vsakršnega številčnega formata. Besedilo okoli njega se obdrži, kot v General" kg", grupirana števila pa obdržijo prejšnjo preslikavo, ker Excel nima grupiranega formata 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]; // indeksator Sheets je eno-osnovni
    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;

    // Celica v General stilu Excela se prebere brez številčnega formata
    // od v2.384.72, namesto '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formule pravil pridejo nazaj v Excelovi sintaksi z ločili z vejico, v isti obliki, ki bi jo podali AddCondFormatExpression, zato se pravilo, zapisano s HotXLS, prebere nazaj kot identični niz. Za širšo sliko tega, kaj pot uvoza ODS obdrži in kaj odvrže, glej vodnik HotXLS po odpiranju in shranjevanju ODS v krogu; kako se ponovljene vrstice iz Excela in LibreOffice ob uvozu razširijo, pa je opisano v ponovljenih vrsticah ODS kot skupinah višin vrstic

Kje so meje medsebojne združljivosti pogojnih formatov ODS pri HotXLS?

Pristop z dvojnim označevanjem pokrije pravila primerjave vrednosti in formulska pravila, tam pa se ustavi. Vse ostalo je enostransko ali pa se sploh ne zapiše:

• Barvne lestvice in podatkovne letvici so zapisani samo kot elementi calcext, zato jih LibreOffice pokaže, Excel pa ne
• Druge vrste pravil, kot so ikonske množice, besedilna pravila, top-N, nad-povprečje in pravila dvojnikov, v trenutnem zapisovalniku nimajo ODS izhoda. Besedilno pravilo se običajno da preformulirati v formulsko pravilo, na primer ISNUMBER(SEARCH("late",B2)) nad B2:B200, ki potem doseže obe aplikaciji
• Pravila celega stolpca in celege vrstice, kot je C:C, se razlezejo samo nad tabelarskim območjem, ki je dejansko zapisano, ne nad vseh 1.048.576 vrstic, zato Excel ta pravila vidi samo na celicah, ki v datoteki obstajajo
• Datoteke samo s style:map. Ko datoteka nima bloka calcext, HotXLS interpretira relativne reference v formulskih pravilih z levega zgornjega kota znova zgrajenega obsega, ne s premikanjem od navedene osnovne celice
• Prekrivajoča pravila iz LibreOffice. Ko eno celico pokrije več pravil, LibreOffice nanjo zapiše samo preslikavo prvega pravila. Takšnih datotek ni mogoče popolnoma prebrati iz samega style:map, kar je še en razlog, da bralnik raje ima calcext, kadar obstajata oba

Omejitev procesa šteje več kot katera koli od teh. Defekti za temi izdajami so šli mimo krogov, ki so zapisali ODS in ga prebrali nazaj s HotXLS, nekateri bi šli mimo tudi ročnega preverjanja v napačni aplikaciji: formule celih stolpcev so delovale v LibreOffice, medtem ko je Excel pokazal #NAME?, formulska pravila pa so od v2.384.66 delovala v LibreOffice, medtem ko Excel do v2.384.69 še vedno ni pokazal nobenega pravila. Če je medsebojna združljivost ODS zahteva, je sprejemni test odpreti datoteko v Excelu in v LibreOffice ter primerjati, kaj vsak pokaže. Istiška velja za stile, na katere pravila kažejo; članek HotXLS o pogojnem oblikovanju in stilih pokrije, kako so stili poudarjanja definirani na strani delovnega zvezka

Hiter pregled: ODS, ki jo prebereta obe aplikaciji

• Deklarirajte xmlns:of in xmlns:msoxl na korenu content.xml, sicer LibreOffice za vsako formulo pokaže #VALUE! (HotXLS od v2.384.56)
• Zapišite reference kot [.A1], obdržite vsak $, celi stolpci in vrstice pa naj gredo kot [.A:.A] in [.1:.1] (od v2.384.55 in v2.384.65)
• Uporabite ; za argumente, ~ za unije referenc in | med vrsticami vgrajene matrike
• Vsako pravilo vrednosti ali formule zapišite kot <style:map> na stil vsake pokrite celice za Excel in kot calcext pogoj za LibreOffice (od v2.384.69)
• V calcext postavite operator v vrednost (>3, between(1,10)) in formulskim pravilom rečite formula-is(...) z osnovno celico (od v2.384.66 in v2.384.69)
• Ob uvozu pričakujte številčni stil General brez number:decimal-places; HotXLS ga od v2.384.72 bere kot General
• Vsak nov izvozni profil preverite z odpiranjem datoteke v Excelu in v LibreOffice, nikoli pa samo v enem od njiju

HotXLS je izvorna knjižnica preglednic za Delphi in C++Builder, ki bere in zapisuje XLS, XLSX in ODS brez nameščenega Excela ali LibreOffice; polni vir, seznam zmožnosti in licenciranje pa so na strani komponente HotXLS Delphi preglednic