HotXLS ODS interop: képletek és szabályok az Excelnek

Hogy egy ODS fájl jól olvasódjon Excelben és LibreOffice-ban egyaránt, a HotXLS minden képletet OpenFormula szintaxissal ír egy deklarált of: névtér alatt, és minden érték- vagy képletfeltétel-formátumot kétszer ír: <style:map>-ként minden lefedett cella stílusán, ami az egyetlen forma, amit az Excel 16 olvas, és calcext:conditional-formats blokként, ami az a forma, amiben a LibreOffice bízik. Mindkét alkalmazás ignorálja a másiknak szánt felét, tehát egy, az egyikben jól mutató fájl semmit sem bizonyít a másikról

Az az utolsó mondat a tanulsága hat HotXLS kiadásnak a v2.384.55 és v2.384.72 között. Minden javítás egy olyan fájllal kezdődött, amit a HotXLS írt, tökéletesen visszaolvasott, és amit a két célalkalmazás egyike rosszul kezelt. Az alábbiakban az következik, amit mindegyik alkalmazás valójában elfogad, a markup, ami mindkettőt kielégíti, meg azok a HotXLS API hívások, amik Delphiből előállítják

Miért néz ki jól egy ODS fájl az egyik alkalmazásban, és törve a másikban?

Egy ODS fájl azért néz ki jól az egyik alkalmazásban, és törve a másikban, mert az Excel és a LibreOffice ugyanannak a csomagnak más részeit olvassa. Az OpenDocument a képleteknek és feltételformátumoknak egynél több legális helyesírást ad, a LibreOffice a saját kiterjesztésnévterét teszi rá, és mindegyik fogyasztó azt a részhalmazt választja, amit implementál. Egy csak egyetlen fogyasztóra tesztelt író szívesen összehangolódik olyan markupra, amit a másik csendben félreolvas

Egyik alkalmazás sem jelent hibát. A LibreOffice #VALUE!-t mutat azokban a cellákban, amiknek a képleteit nem tudta parzolni; az Excel a munkafüzetet úgy nyitja meg, hogy a feltételformátumok egyszerűen hiányoznak, vagy egy képlet olyanná íródik át, ami #NAME?-re vagy 0 konstansra értékelődik. Egy író, ami a saját kimenetét körbeviszi, egyet sem lát ebből. A HotXLS pontosan ebbe a csapdába esett a képlernévtérrel: az olvasója a of: prefixet sima szövegként illesztette, így minden ön-körutazás átment, miközben a LibreOffice #VALUE!-t mutatott minden képletcellában

Funkció	Az Excel 16 olvassa	A LibreOffice 26.2 olvassa
A:A-ként írt teljes oszlop	A:(A)-ként félreolvasva	Tolerált
[.A:.A]-ként írt teljes oszlop	Igen	Igen
Feltételformátumok <style:map>-ban	Igen, az egyetlen forma, amit olvas	Ignorálva, ha a calcext jelen van
Feltételformátumok calcext:conditional-formats-ban	Ignorálva	Igen, előnyben részesítve
calcext értékszabály calcext:operator attribútummal	Ignorálva	"egyenlő 0"-ként importálva
is-true-formula(...) helyesírással írt calcext képletszabály	Ignorálva	0-val való értékösszehasonlításként importálva

OpenFormula ODS-ben: deklaráld a névteret, aztán a szintaxissal legyen rend

Egy ODS-beli képletcella csak akkor olvasható LibreOffice-ban, ha a table:formula-ban álló of: prefix egy deklarált XML névtérre oldódik fel. A prefix nem dísz. Az of: az urn:oasis:names:tc:opendocument:xmlns:of:1.2-re mutat, az msoxl:, az a prefix, amit a HotXLS azokhoz a képletekhez használ, amiket az OpenFormula transzlatore nem modellez, a http://schemas.microsoft.com/office/excel/formula-ra. v2.384.56 előtt a content.xml gyökere mindkét prefixet használta deklaráció nélkül, és a LibreOffice egyáltalán nem tudta azonosítani a képletgrammatikát

<!-- v2.384.56 előtt: prefix használva, soha nem deklarálva; a LibreOffice #VALUE!-t mutat -->
<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"/>

<!-- v2.384.56 óta: mindkét képlernévtér deklarálva a gyökéren -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

A névtér javítása után magának a kifejezésnek is érvényes OpenFormula-nak kell lennie, ahogy az OpenDocument 1.3 Part 4 definiálja. A csapdák azok a helyek, ahol az Excel szintaxis és az OpenFormula hasonlónak néz ki, de nem ugyanaz:

• A cellahivatkozások zárójelezettek és pontprefixesek, és a $ jelek a hivatkozás részei: az [.$A$1] és az [.A$1:.$B2] érvényes OpenFormula. v2.384.55 előtt a HotXLS író minden $-ot eldobott, így az abszolút hivatkozások relatívként jöttek vissza, és csak akkor mentek rosszra, ha valaki másolta a cellát
• A teljes oszlopoknak és soroknak a zárójeles formát kell használniuk: [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Egy csupasz of:=SUM(A:A)-t a LibreOffice tolerál, de az Excel 16 =SUM(A:(A))-ként nyitja meg #NAME?-sel, a sorhivatkozásokat és a $A:$B-t pedig 0 konstanssá teszi. A HotXLS a zárójeles formát írja v2.384.65 óta
• A függvényargumentumokat ; választja el, nem ,
• A hivatkozásuniók a ~ operátort használják: az Excel AREAS((A1,B2))-je AREAS(([.A1]~[.B2])) lesz. Annak a vesszőnek a ;-ra fordítása egyetlen unióargumentumot két argumentummá tesz
• Az inline tömbök az oszlopokat ;-tal, a sorokat |-lal választják el: az Excel {1,2;3,4}-je {1;2|3;4} lesz. v2.384.55 előtt a HotXLS {1;2;3;4}-et adott, egyetlen sort négy értékkel

A vessző a nehéz rész, mert egyetlen Excel karakter három jelentést hordoz. v2.384.55 óta a HotXLS író zárójelstacket követ a fordítás közben: egy név után közvetlenül álló ( függvényhívást nyit, aminek a vesszői ;-dé válnak; bármely más ( csoportosító zárójel, aminek a vesszői ~-dé válnak; és a {} belüli vesszők tömboszlop-elválasztók. Ezzel meg a névtérjavítással a LibreOffice 26.2 mind a nyolc tömb és unió próbaképletet helyesen értékelte ki, a uniók fölötti INDEX és AREAS is beleértve

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;

    // of:=SUM([.A:.A])-ként íródik v2.384.65 óta
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // of:=[.A1]*[.$B$1]-ként íródik; a $ jelek v2.384.55 óta túlélik
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Azok a képletek, amiket a transzlator nem modellez, msoxl:=-re esnek vissza az Excel szöveggel változatlanul, ezért számít az msoxl deklaráció is. A jelenlegi íróban az az út sheet-minősített hivatkozásokat foglal magában, mint a Sheet2!A1, meg strukturált táblahivatkozásokat. A HotXLS importnál visszaolvassa az msoxl: képleteket, így a saját körutazása érintetlenül tartja a kifejezést, de ahogy egy másik alkalmazás bánik velük, az az író kontrollján kívül van. Ha egy képlet, amitől a fogyasztóid függenek, msoxl: prefixszel jön ki, nyisd meg a fájlt mindkét alkalmazásban, mielőtt kiszállítod

Miért nem látja az Excel a csak calcextként írt feltételformátumokat?

Az Excel 16 azért nem látja a calcext feltételformátumokat, mert az ODS feltételformátumokat kizárólag cellastílusok <style:map> gyerekeiből olvassa, és a calcext:conditional-formats blokkot teljesen ignorálja. A kísérlet, ami ezt eldönti, rövid: vegyél egy LibreOffice által mentett ODS-t, töröld a style:map elemeket, és az Excel nulla szabályt olvas; töröld helyette a calcext blokkot, és az Excel mindet olvassa. A LibreOffice fordítva viselkedik. A calcext a LibreOffice kiterjesztésnévtere, nem része az ODF szabványnak, és ha calcext szabály van jelen, a LibreOffice azt viszi, és ignorálja a style:map-et

v2.384.69 előtt a HotXLS csak calcextet írt, így egy tökéletesen jó kiemeléses ODS fájl Excelben értékszabályok és képletszabályok nélkül nyílt meg. A HotXLS mostantól mindkét formát írja. A style:map fél az OpenDocument séma (ODF 1.3 Part 3) feltételgrammatikáját használja, azokkal a pontos helyesírásokkal, amiket az Excel 16 és a LibreOffice 26.2 egyaránt produkál ODS mentésekor:

<!-- Egyszerűsítve. Hordozó stílus az A1:A50 minden cellájára (két értékszabály) -->
<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>

<!-- Hordozó stílus a C1:C50 minden cellájára (egy képletszabály) -->
<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>

A style:map csavarja az, hogy cellastílusokon él, tehát cellánkénti. A szabály tartományának minden cellájának hordoznia kell egy olyan stílust, ami tartja a mapet, üres cellák is beleértve, különben a szabály Excelben egyszerűen nem fedi le azt a cellát. A HotXLS minden cella meglévő formázási stílusát lemásolja, hozzáfűzi a mapokat, és a hordozó stílusokat az eredeti stílus meg a map szöveg párja szerint deduplikálja, így egy 500 cellás, azonos formázású tartomány is egyetlen stílust produkál. Az író emellett a szabály tartományára terjeszti az írt táblát, ami azt jelenti, hogy a szabályon belüli üres faroksorok kiíródnak, nem eldobódnak. v2.384.69 óta a styles.xml üres Default cellastílust is hordoz, így a style:apply-style-name="Default"-nak mindig van célja

A calcext helyesírás, amit a LibreOffice ténylegesen elfogad

A LibreOffice egy calcext értékszabályt csak akkor fogad el, ha az összehasonlító operátor az értékszöveg része, mint a >3 vagy a between(1,10), egy képletszabályt pedig csak akkor, ha az formula-is(...) helyesírással íródott. Mindkét pont egy kiadásba került a HotXLS-nek, mert a rossz helyesírások olyan szabályt adnak, ami hiba nélkül importálódik, aztán rossz cellákat talál el

Az első hiba egy calcext:operator attribútum volt a calcext:value mellett. Természetesen olvasható, de kitalált: a LibreOffice nem ismeri azt az attribútumot, így minden értékszabályt "egyenlő 0"-ként importált. A második az volt, hogy az is-true-formula(...)-ot, a style:map helyesírását, tették calcext feltételbe, amit a LibreOffice szintén 0-val való cellaérték-összehasonlításként importált. A képletjavítás a v2.384.66-ban, az értékjavítás a v2.384.69-ben érkezett:

<!-- Rossz: a LibreOffice ignorálja a calcext:operatort, és "egyenlő 0"-ként importál -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Helyes: az operátor az értéken belül utazik -->
<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"/>

<!-- Helyes: a képletszabályok formula-is-t használnak, relatív hivatkozások a bázis cellához rögzítve -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

A bázis cella adja a relatív hivatkozások értelmét. A HotXLS minden szabályt az első tartományterület bal-felső cellájához rögzít, így egy C1-re írt képlet C2-ként, C3-ként és így tovább értékelődik a tartományban lefelé, pontosan úgy, ahogy az Excel saját feltételformázásában. A szabálykifejezés ugyanazon a transzlatoren megy át, mint a cellaképletek, így a tömbök, uniók, teljes oszlopok és $ jelek a fent leírt formákban jönnek ki. Delphi oldalon a szabályokat pontosan úgy adod hozzá, ahogy egy .xlsx fájlhoz tennéd

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Értékszabályok: style:map cell-content()>100 plusz calcext value ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: világospiros

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

  // Képletszabály Excel szintaxisban (vesszőelválasztók, a C1-hez relatíven):
  // style:map is-true-formula(...) plusz calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: világossárga

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

ODS olvasása Excelből és LibreOffice-ból vissza Delphibe

Amikor a HotXLS megnyit egy ODS fájlt, az olvasója mindkét feltételformátum-dialektust és mindkét calcext helyesírást elfogadja, és nem számol egy szabályt kétszer, amikor a fájl mindkét formában hordozza. A valódi fájlok három írótól jönnek, mindegyik a maga szokásaival:

• Régi és új calcext. A calcext:operator attribútumos fájlok, beleértve a HotXLS v2.384.69 előtti ODS-eit, továbbra is az örökölt parzoláson mennek át. A képletfeltételek vagy formula-is(...)-ként vagy is-true-formula(...)-ként ismerhetők fel
• Az Excel style:map helyesírása. Az Excel a feltételeket of:-tal prefixeli, mint az of:cell-content-is-between(1,10), és értékszabályoknál elhagyja a bázis cellát. Mindkettő elfogadott
• Üres cellák. Az Excel és a LibreOffice egyaránt az üres cellák mapjét az oszlop alapértelmezett stílusára teszi, nem cellára, így az olvasó az ismételt cellákhoz feloldja az oszlop alapértelmezett stílusait, mielőtt a mapokat gyűjtené
• Terület-visszaépítés. A mapok cellánként gyűlnek, tehát miután egy sheet beolvasódott, az olvasó visszaolvasztja tartományokká azokat a cellákat, amik ugyanazt a feltételt és bázis cellát osztják, előbb soronként át, aztán lefelé az illeszkedő oszlopfutásokon, és eldobja minden olyan szabályt, amit már a calcextből olvasott

A v2.384.72-es javítás a számstílusokat érinti, nem szabályokat. Az Excel 16 és a LibreOffice 26.2 egyaránt a General formátumot olyan számstípusként írja, aminek a number:number elemének nincs number:decimal-places-e, jellemzően <number:number number:min-integer-digits="1"/>. A HotXLS olvasója a hiányzó számot két rögzített tizedesként kezelte, így a Default stílus minden értéke 0.00-mal importálódott, és az 1.5 1.50-ként jelent meg. v2.384.72 óta egy sima szám elem, tizedesek, minimális tizedesek, csoportosítás nélkül, legfeljebb egy egész számjeggyel a Generalra képződik, és egy magános General elhagyja a cellát minden számformátum nélkül. A körülötte lévő szöveg megtartódik, mint a General" kg"-ben, és a csoportosított számok megtartják az előző leképezést, mert az Excelnek nincs csoportosított General formátuma

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]; // a Sheets indexelő 1-alapú
    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;

    // Egy Excel General stílusú cella v2.384.72 óta számformátum
    // nélkül olvasódik vissza az '0.00' helyett
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

A szabályképletek Excel szintaxisban, vesszőelválasztókkal jönnek vissza, ugyanabban a formában, amiben a AddCondFormatExpression-nek átadnád, így egy HotXLS által írt szabály azonos stringként olvasódik vissza. Hogy az ODS import út mit tart meg és mit dob el, az szélesebb képhez lásd a HotXLS ODS megnyitási és mentési körutazási útmutatóját; hogy az Excel és LibreOffice ismételt sorai hogyan bontódnak ki importnál, lásd a ODS ismételt sorok sormagasság-futásokként cikket

Mik a HotXLS ODS feltételformátum-interop határai?

A kettős-markup megközelítés az értékösszehasonlító szabályokat és a képletszabályokat fedi le, és itt áll meg. Minden más egyoldalú vagy egyáltalán nem íródik:

• A színskálák és adatsávok csak calcext elemként íródnak, így a LibreOffice mutatja őket, az Excel nem
• A többi szabályfajta, mint ikonhalmazok, szövegszabályok, top-N, átlag feletti és duplikátumszabályok, nincs ODS kimenete a jelenlegi íróban. Egy szövegszabály általában képletszabályként átírható, például ISNUMBER(SEARCH("late",B2)) a B2:B200 fölött, ami így mindkét alkalmazásba elér
• A teljes oszlopos és teljes soros szabályok, mint a C:C, csak a ténylegesen írt táblaarea fölé fektetnek, nem mind az 1 048 576 sorra, így az Excel ezeket a szabályokat csak a fájlban létező cellákon látja
• Csak style:map fájlok. Ha egy fájlnak nincs calcext blokkja, a HotXLS a képletszabályok relatív hivatkozásait az újjáépített tartomány bal-felső sarkából értelmezi, nem a megadott bázis cellából való eltolással
• Átfedő szabályok LibreOffice-ból. Amikor egy cellát több szabály fed, a LibreOffice csak az első szabály mapjét írja rá. Az ilyen fájlok nem olvashatók teljesen style:map-ből egyedül, ami még egy ok arra, hogy az olvasó calcextet preferál, ha mindkettő jelen van

A folyamatkorlát fontosabb, mint bármelyik ezek közül. Azok a hibák, amik e kiadások mögött állnak, átmentek olyan körutazásokon, amik ODS-t írtak és HotXLS-szel olvastak vissza, és néhány átment volna egy rossz alkalmazásban végzett kézi ellenőrzésen is: a teljes oszlopos képletek LibreOffice-ban működtek, miközben az Excel #NAME?-et mutatott, és a v2.384.66-tól a képletszabályok LibreOffice-ban működtek, miközben az Excel egészen v2.384.69-ig egyáltalán nem mutatott szabályokat. Ha ODS interop a követelmény, az átvételi teszt az, hogy megnyitod a fájlt Excelben és LibreOffice-ban, és összeveted, mit mutat mindegyik. Ugyanez a fegyelem érvényes azokra a stílusokra is, amikre a szabályok mutatnak; a HotXLS feltételformázás és stílusok cikk fedi le, hogyan definiálódnak a kiemelési stílusok a munkafüzet oldalán

Gyorsreferencia: ODS, amit mindkét alkalmazás olvas

• Deklárd a xmlns:of-ot és a xmlns:msoxl-t a content.xml gyökerén, különben a LibreOffice #VALUE!-t mutat minden képletre (HotXLS v2.384.56 óta)
• Írd a hivatkozásokat [.A1]-ként, tarts meg minden $-ot, és a teljes oszlopokat és sorokat [.A:.A]-ként és [.1:.1]-ként írd (v2.384.55 és v2.384.65 óta)
• Használj ;-t argumentumokhoz, ~-t hivatkozásuniókhoz, és |-t inline tömbsorok közt
• Írd minden érték- vagy képletszabályt <style:map>-ként minden lefedett cella stílusán az Excelnek, és calcext feltételként a LibreOffice-nak (v2.384.69 óta)
• Calcextben tedd az operátort az értékbe (>3, between(1,10)), és a képletszabályokat formula-is(...)-ként írd bázis cellával (v2.384.66 és v2.384.69 óta)
• Számolj azzal, hogy importnál General számstílus jön number:decimal-places nélkül; a HotXLS v2.384.72 óta Generalnak olvassa
• Ellenőrizd minden új exportprofilt a fájl mind Excelben, mind LibreOffice-ban való megnyitásával, soha csak az egyikben

A HotXLS natív Delphi és C++Builder spreadsheet library, ami XLS-t, XLSX-et és ODS-t olvas és ír Excel vagy LibreOffice telepítése nélkül; teljes forráskód, a funkciólista és a licencelés a HotXLS Delphi spreadsheet component oldalon van