Interop ODS HotXLS: formule și reguli citite de Excel

Ca să produci un fișier ODS pe care Excel și LibreOffice îl citesc corect amândouă, HotXLS scrie fiecare formulă în sintaxă OpenFormula sub un namespace of: declarat, și scrie fiecare format condiționat de valoare sau de formulă de două ori: ca <style:map> pe stilul fiecărei celule acoperite, care e singura formă citită de Excel 16, și ca bloc calcext:conditional-formats, forma în care are încredere LibreOffice. Fiecare aplicație ignoră jumătatea destinată celeilalte, deci un fișier care arată corect în una nu dovedește nimic despre cealaltă

Propoziția din urmă e lecția din spatele a șase versiuni HotXLS între v2.384.55 și v2.384.72. Fiecare reparare a pornit de la un fișier pe care HotXLS îl scria, îl recitea perfect, iar una dintre cele două aplicații țintă îl citea greșit. Ce urmează e ce acceptă de fapt fiecare aplicație, markup-ul care le mulțumește pe amândouă și apelurile de API HotXLS care îl produc din Delphi

De ce un fișier ODS arată bine într-o aplicație și stricat în cealaltă?

Un fișier ODS arată bine într-o aplicație și stricat în cealaltă pentru că Excel și LibreOffice citesc părți diferite din același pachet. OpenDocument dă formulelor și formatelor condiționate mai mult de o scriere legală, LibreOffice adaugă deasupra propriul lui namespace de extensie, iar fiecare consumator alege submulțimea pe care o implementează. Un writer testat contra unui singur consumator va converge voios pe markup pe care celălalt îl citește greșit în tăcere

Nicio aplicație nu raportează o eroare. LibreOffice arată #VALUE! în celulele ale căror formule nu le-a putut parsa; Excel deschide workbook-ul cu formatele condiționate pur și simplu absente, sau cu o formulă rescrisă în ceva ce evaluează la #NAME? sau la constanta 0. Un writer care face round-trip pe propria producție nu vede nimic din toate astea. HotXLS a lovit exact capcana asta cu namespace-ul de formule: reader-ul lui potriveau prefixul of: ca text simplu, deci fiecare round-trip propriu trecea în timp ce LibreOffice arăta #VALUE! în fiecare celulă de formulă

Funcționalitate	Excel 16 citește	LibreOffice 26.2 citește
Coloană întreagă scrisă ca A:A	Citit greșit ca A:(A)	Tolerat
Coloană întreagă scrisă ca [.A:.A]	Da	Da
Formate condiționate în <style:map>	Da, singura formă citită	Ignorate când calcext e prezent
Formate condiționate în calcext:conditional-formats	Ignorate	Da, preferate
Regulă de valoare calcext cu atribut calcext:operator	Ignorată	Importată drept „egal cu 0”
Regulă de formulă calcext scrisă is-true-formula(...)	Ignorată	Importată drept comparație de valoare cu 0

OpenFormula în ODS: declară namespace-ul, apoi nimeri sintaxa

O celulă de formulă în ODS e lizibilă de LibreOffice doar când prefixul of: din table:formula se rezolvă la un namespace XML declarat. Prefixul nu e decorațiune. of: se mapează la urn:oasis:names:tc:opendocument:xmlns:of:1.2, iar msoxl:, prefixul pe care HotXLS îl folosește pentru formulele pe care translatorul lui OpenFormula nu le modelează, se mapează la http://schemas.microsoft.com/office/excel/formula. Înainte de v2.384.56 rădăcina content.xml folosea ambele prefixe fără să le declare, iar LibreOffice nu putea identifica deloc gramatica formulei

<!-- Înainte de v2.384.56: prefix folosit, niciodată declarat; LibreOffice arată #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"/>

<!-- Din v2.384.56: ambele namespace-uri de formule declarate pe rădăcină -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Cu namespace-ul reparat, expresia în sine trebuie totuși să fie OpenFormula valid, cum e definit în OpenDocument 1.3 Partea 4. Capcanele sunt locurile unde sintaxa Excel și OpenFormula arată asemănător dar nu sunt aceleași:

• Referințele de celule sunt între paranteze pătrate cu punct în față, iar markerii $ fac parte din referință: [.$A$1] și [.A$1:.$B2] sunt OpenFormula valid. Înainte de v2.384.55 writer-ul HotXLS arunca fiecare $, deci referințele absolute se întorceau relative și greșeau doar când cineva copia celula
• Coloanele și rândurile întregi trebuie să folosească forma cu paranteze [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Un of:=SUM(A:A) nud e tolerat de LibreOffice, dar Excel 16 îl deschide ca =SUM(A:(A)) cu #NAME?, și transformă referințele de rânduri și $A:$B în constanta 0. HotXLS scrie forma cu paranteze din v2.384.65
• Argumentele de funcții sunt separate prin ;, nu ,
• Uniunile de referințe folosesc operatorul ~: Excel AREAS((A1,B2)) devine AREAS(([.A1]~[.B2])). Traducerea virgulei aceleia în ; transformă în schimb un argument de uniune în două argumente
• Array-urile inline separă coloanele cu ; și rândurile cu |: Excel {1,2;3,4} devine {1;2|3;4}. Înainte de v2.384.55 HotXLS producea {1;2;3;4}, un singur rând cu patru valori

Virgula e partea grea, pentru că un singur caracter Excel poartă trei sensuri. Din v2.384.55 writer-ul HotXLS ține o stivă de paranteze în timp ce traduce: un ( imediat după un nume deschide un apel de funcție, ale cărui virgule devin ;; orice alt ( e o paranteză de grupare, ale cărei virgule devin ~; iar virgulele din {} sunt separatoare de coloane de array. Cu asta și cu repararea namespace-ului, LibreOffice 26.2 a evaluat corect toate cele opt formule de sondă cu array și uniune, INDEX și AREAS peste uniuni incluse

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;

    // Scris ca of:=SUM([.A:.A]) din v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Scris ca of:=[.A1]*[.$B$1]; markerii $ supraviețuiesc din v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formulele pe care translatorul nu le modelează cad pe msoxl:= cu textul Excel neschimbat, de aceea declararea msoxl contează și ea. În writer-ul curent calea asta include referințe calificate cu sheet precum Sheet2!A1 și referințe de tabele structurate. HotXLS citește formulele msoxl: înapoi la import, deci round-trip-ul propriu păstrează expresia intactă, dar cum o tratează o altă aplicație e în afara controlului writer-ului. Dacă o formulă de care depind consumatorii voștri iese cu prefixul msoxl:, deschideți fișierul în ambele aplicații înainte să-l livrați

De ce nu vede Excel formatele condiționate scrise doar ca calcext?

Excel 16 nu vede formatele condiționate calcext pentru că citește formatele condiționate ODS exclusiv din copiii <style:map> ai stilurilor de celulă și ignoră complet blocul calcext:conditional-formats. Experimentul care lămurește e scurt: ia un ODS salvat de LibreOffice, șterge elementele style:map, iar Excel citește zero reguli; șterge în schimb blocul calcext, iar Excel le citește în continuare pe toate. LibreOffice se poartă invers. calcext e namespace-ul de extensie al LibreOffice, nu face parte din standardul ODF, iar când o regulă calcext e prezentă LibreOffice o ia și ignoră style:map-ul

Înainte de v2.384.69 HotXLS scria doar calcext, deci un fișier ODS cu highlight-uri perfect bune se deschidea în Excel fără nicio regulă de valoare și fără nicio regulă de formulă. HotXLS scrie acum ambele forme. Jumătatea style:map folosește gramatica de condiții a schemei OpenDocument (ODF 1.3 Partea 3), cu scrierile exacte pe care Excel 16 și LibreOffice 26.2 le produc amândouă când salvează ODS:

<!-- Simplificat. Stil purtător pentru fiecare celulă din A1:A50 (două reguli de valoare) -->
<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>

<!-- Stil purtător pentru fiecare celulă din C1:C50 (o regulă de formulă) -->
<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>

Problema cu style:map e că trăiește pe stiluri de celule, deci e per celulă. Fiecare celulă din intervalul regulii trebuie să poarte un stil care conține map-ul, celulele goale incluse, altfel regula pur și simplu nu acoperă celula aceea în Excel. HotXLS copiază stilul de formatare existent al fiecărei celule, adaugă map-urile și deduplică stilurile purtătoare după perechea de stil original și text de map, deci un interval de 500 de celule cu formatare identică produce tot un singur stil. Writer-ul extinde de asemenea tabelul scris până la intervalul regulii, ceea ce înseamnă că rândurile de coadă goale din interiorul unei reguli sunt emise, nu aruncate. Din v2.384.69 styles.xml poartă și un stil de celulă Default gol, deci style:apply-style-name="Default" are mereu o țintă

Scrierea calcext pe care LibreOffice o acceptă de fapt

LibreOffice acceptă o regulă de valoare calcext doar când operatorul de comparație face parte din textul valorii, precum >3 sau between(1,10), iar o regulă de formulă doar când e scrisă formula-is(...). Ambele puncte au costat HotXLS o versiune, fiindcă scrierile greșite produc o regulă care se importă fără eroare și apoi potrivește celulele greșite

Prima greșeală a fost un atribut calcext:operator lângă calcext:value. Se citește natural, dar e inventat: LibreOffice nu cunoaște atributul acela, deci importa fiecare regulă de valoare drept „egal cu 0”. A doua a fost punerea lui is-true-formula(...), scrierea din style:map, într-o condiție calcext, pe care LibreOffice o importa tot drept comparație de valoare a celulei cu 0. Repararea formulei a ieșit în v2.384.66 și cea a valorii în v2.384.69:

<!-- Greșit: LibreOffice ignoră calcext:operator și importă „egal cu 0” -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Corect: operatorul călătorește în interiorul valorii -->
<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"/>

<!-- Corect: regulile de formulă folosesc formula-is, referințe relative ancorate la celula de bază -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Celula de bază e ceea ce dă referințelor relative sensul lor. HotXLS ancorează fiecare regulă la celula din stânga-sus a primei zone de interval al ei, deci o formulă scrisă pentru C1 evaluează ca C2, C3 și așa mai departe de-a lungul intervalului, exact cum face în propria formatare condiționată a Excel-ului. Expresia regulii trece prin același translator ca formulele de celule, deci array-urile, uniunile, coloanele întregi și markerii $ ies în formele descrise mai sus. Pe partea de Delphi adăugați regulile exact cum ați face-o pentru un fișier .xlsx

uses
  lxHandleX;

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

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

  // Regulă de formulă în sintaxă Excel (separatori virgulă, relativ la 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: galben deschis

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

Citirea ODS din Excel și LibreOffice înapoi în Delphi

Când HotXLS deschide un fișier ODS, reader-ul lui acceptă ambele dialecte de formate condiționate și ambele scrieri calcext, și nu numără o regulă de două ori când fișierul o poartă în ambele forme. Fișierele reale vin de la trei writer-e, fiecare cu obiceiurile lui:

• calcext vechi și nou. Fișierele cu atribut calcext:operator, inclusiv ODS-urile scrise de HotXLS înainte de v2.384.69, trec în continuare prin parsarea legacy. Condițiile de formulă sunt recunoscute fie ca formula-is(...), fie ca is-true-formula(...)
• Scrierea style:map a Excel-ului. Excel prefixează condițiile cu of:, ca în of:cell-content-is-between(1,10), și omite celula de bază la regulile de valoare. Ambele sunt acceptate
• Celulele goale. Excel și LibreOffice pun amândouă map-ul pentru celulele goale pe stilul implicit al coloanei, nu pe o celulă, deci reader-ul rezolvă stilurile implicite de coloană pentru celulele repetate înainte să colecteze map-urile
• Reconstruirea intervalelor. Map-urile se colectează per celulă, deci după citirea unui sheet reader-ul îmbină celulele care împart aceeași condiție și aceeași celulă de bază înapoi în intervale, întâi de-a lungul fiecărui rând și apoi în jos pe întinderile de coloane care se potrivesc, și aruncă orice regulă citită deja din calcext

Repararea din v2.384.72 privește stilurile de numere, nu regulile. Excel 16 și LibreOffice 26.2 scriu amândouă formatul General ca stil de număr al cărui element number:number nu are number:decimal-places, de obicei <number:number number:min-integer-digits="1"/>. Reader-ul HotXLS trata numărul lipsă ca două zecimale fixe, deci fiecare valoare în stilul Default se importa cu 0.00 și 1.5 se afișa 1.50. Din v2.384.72 un element de număr simplu fără zecimale, fără minim de zecimale, fără grupare și cu cel mult o cifră întreagă se mapează la General, iar un General singur lasă celula fără vreun format de număr. Textul din jur se păstrează, ca în General" kg", iar numerele grupate păstrează maparea anterioară fiindcă Excel nu are format General grupat

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]; // indexatorul Sheets are baza unu
    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;

    // O celulă în stilul General al Excel-ului se citește înapoi fără format de număr
    // din v2.384.72, în loc de '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formulele regulilor se întorc în sintaxă Excel cu separatori virgulă, aceeași formă pe care i-ați pasa lui AddCondFormatExpression, deci o regulă scrisă de HotXLS se citește înapoi ca șirul identic. Pentru imaginea mai largă despre ce păstrează și ce aruncă calea de import ODS, vezi ghidul HotXLS de round-trip deschidere și salvare ODS; pentru cum sunt extinse la import rândurile repetate din Excel și LibreOffice, vezi rândurile repetate ODS drept șiruri de înălțimi de rând

Care sunt limitele interoperabilității de formate condiționate ODS în HotXLS?

Abordarea cu markup dual acoperă regulile de comparație de valoare și regulile de formulă, și se oprește acolo. Tot restul e unilateral sau nu e scris deloc:

• Scalele de culori și barele de date sunt scrise doar ca elemente calcext, deci LibreOffice le arată, iar Excel nu
• Alte feluri de reguli, precum seturi de pictograme, reguli de text, top-N, peste medie și reguli de duplicate, nu au output ODS în writer-ul curent. O regulă de text se poate de obicei reformula drept regulă de formulă, de exemplu ISNUMBER(SEARCH("late",B2)) peste B2:B200, care ajunge apoi în ambele aplicații
• Regulile pe coloane și rânduri întregi precum C:C se pun doar peste aria de tabel efectiv scrisă, nu peste toate cele 1.048.576 de rânduri, deci Excel vede aceste reguli doar pe celulele care există în fișier
• Fișierele cu doar style:map. Când un fișier nu are bloc calcext, HotXLS interpretează referințele relative din regulile de formulă de la colțul din stânga-sus al intervalului reconstruit, nu prin deplasare de la celula de bază declarată
• Regulile suprapuse din LibreOffice. Când o celulă e acoperită de mai multe reguli, LibreOffice scrie pe ea doar map-ul primei reguli. Fișierele asemenea nu pot fi citite complet doar din style:map, ceea ce e încă un motiv pentru care reader-ul preferă calcext când ambele există

Limita de proces contează mai mult decât oricare dintre acestea. Defectele din spatele acestor versiuni au trecut prin round-trip-uri care scriau ODS și îl reciteau cu HotXLS, iar unele ar fi trecut și printr-o verificare de mână în aplicația greșită: formulele pe coloane întregi funcționau în LibreOffice în timp ce Excel arăta #NAME?, iar din v2.384.66 regulile de formulă funcționau în LibreOffice în timp ce Excel nu arăta nicio regulă deloc până la v2.384.69. Dacă interoperabilitatea ODS e o cerință, testul de acceptanță e deschiderea fișierului în Excel și în LibreOffice și compararea a ceea ce arată fiecare. Aceeași disciplină se aplică și stilurilor spre care pointează regulile; articolul HotXLS despre formatarea condiționată și stiluri acoperă cum sunt definite stilurile de highlight pe partea de workbook

Referință rapidă: ODS pe care ambele aplicații îl citesc

• Declarați xmlns:of și xmlns:msoxl pe rădăcina content.xml, altfel LibreOffice arată #VALUE! pentru fiecare formulă (HotXLS din v2.384.56)
• Scrieți referințele ca [.A1], păstrați fiecare $, și scrieți coloanele și rândurile întregi ca [.A:.A] și [.1:.1] (din v2.384.55 și v2.384.65)
• Folosiți ; pentru argumente, ~ pentru uniuni de referințe și | între rândurile de array inline
• Scrieți fiecare regulă de valoare sau de formulă ca <style:map> pe stilul fiecărei celule acoperite pentru Excel, și ca condiție calcext pentru LibreOffice (din v2.384.69)
• În calcext, puneți operatorul în valoare (>3, between(1,10)) și scrieți regulile de formulă formula-is(...) cu o celulă de bază (din v2.384.66 și v2.384.69)
• Așteptați-vă la un stil de număr General fără number:decimal-places la import; HotXLS îl citește drept General din v2.384.72
• Verificați fiecare profil nou de export deschizând fișierul în Excel și în LibreOffice, niciodată doar într-unul dintre ele

HotXLS e o bibliotecă spreadsheet nativă Delphi și C++Builder care citește și scrie XLS, XLSX și ODS fără Excel sau LibreOffice instalate; sursa completă, lista de funcționalități și licențierea sunt pe pagina componentei HotXLS Delphi spreadsheet