Interop ODS di HotXLS: formule e regole che Excel legge

Per produrre un file ODS che Excel e LibreOffice leggano correttamente entrambi, HotXLS scrive ogni formula in sintassi OpenFormula sotto un namespace of: dichiarato, e scrive ogni formato condizionale value o formula due volte: come <style:map> sullo stile di ogni cella coperta, che è l'unica forma che Excel 16 legge, e come blocco calcext:conditional-formats, che è la forma di cui si fida LibreOffice. Ciascuna applicazione ignora la metà destinata all'altra, quindi un file che si mostra correttamente in una delle due non dimostra nulla sull'altra

Quell'ultima frase è la lezione dietro a sei release HotXLS tra la v2.384.55 e la v2.384.72. Ogni correzione è partita da un file che HotXLS scriveva, rileggeva alla perfezione, e una delle due applicazioni bersaglio interpretava male. Quello che segue è ciò che ogni applicazione accetta davvero, il markup che soddisfa entrambe, e le chiamate API HotXLS che lo producono da Delphi

Perché un file ODS sta bene in un'applicazione e si rompe nell'altra?

Un file ODS sta bene in un'applicazione e si rompe nell'altra perché Excel e LibreOffice leggono parti diverse dello stesso pacchetto. OpenDocument dà a formule e formati condizionali più di una grafia legale, LibreOffice ci aggiunge sopra il proprio namespace di estensione, e ogni consumatore sceglie il sottoinsieme che implementa. Un writer testato contro un solo consumatore converge felicemente su markup che l'altro legge male in silenzio

Nessuna delle due applicazioni segnala un errore. LibreOffice mostra #VALUE! nelle celle le cui formule non riesce a parseare; Excel apre la cartella di lavoro con i formati condizionali semplicemente assenti, o con una formula riscritta in qualcosa che valuta #NAME? o la costante 0. Un writer che fa il round trip del proprio output non ne vede nessuno. HotXLS è caduto esattamente in quella trappola con il namespace delle formule: il suo reader riconosceva il prefisso of: come testo semplice, così ogni round trip con sé stesso passava mentre LibreOffice mostrava #VALUE! in ogni cella formula

Funzionalità	Excel 16 legge	LibreOffice 26.2 legge
Intera colonna scritta come A:A	Letta male come A:(A)	Tollerato
Intera colonna scritta come [.A:.A]	Sì	Sì
Formati condizionali in <style:map>	Sì, l'unica forma che legge	Ignorati quando calcext è presente
Formati condizionali in calcext:conditional-formats	Ignorati	Sì, preferiti
Regola value calcext con attributo calcext:operator	Ignorata	Importata come "uguale a 0"
Regola formula calcext scritta is-true-formula(...)	Ignorata	Importata come confronto value con 0

OpenFormula in ODS: dichiara il namespace, poi azzecca la sintassi

Una cella formula in ODS è leggibile da LibreOffice solo quando il prefisso of: in table:formula si risolve in un namespace XML dichiarato. Il prefisso non è decorazione. of: si mappa su urn:oasis:names:tc:opendocument:xmlns:of:1.2, e msoxl:, il prefisso che HotXLS usa per le formule che il suo traduttore OpenFormula non modella, si mappa su http://schemas.microsoft.com/office/excel/formula. Prima della v2.384.56 la radice di content.xml usava entrambi i prefissi senza dichiararli, e LibreOffice non riusciva a identificare affatto la grammatica delle formule

<!-- Prima della v2.384.56: prefisso usato, mai dichiarato; LibreOffice mostra #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"/>

<!-- Dalla v2.384.56: entrambi i namespace formula dichiarati sulla radice -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Con il namespace sistemato, l'espressione deve comunque essere OpenFormula valido, come definito in OpenDocument 1.3 Parte 4. Le trappole sono i punti in cui la sintassi Excel e OpenFormula sembrano simili ma non sono la stessa cosa:

• I riferimenti a celle sono tra parentesi quadre con prefisso punto, e i marker $ fanno parte del riferimento: [.$A$1] e [.A$1:.$B2] sono OpenFormula valido. Prima della v2.384.55 il writer HotXLS buttava via ogni $, così i riferimenti assoluti tornavano relativi e sbagliavano solo quando qualcuno copiava la cella
• Le colonne e righe intere devono usare la forma tra parentesi quadre [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Un nudo of:=SUM(A:A) è tollerato da LibreOffice, ma Excel 16 lo apre come =SUM(A:(A)) con #NAME?, e trasforma i riferimenti a righe e $A:$B nella costante 0. HotXLS scrive la forma tra parentesi quadre dalla v2.384.65
• Gli argomenti delle funzioni sono separati da ;, non da ,
• Le unioni di riferimenti usano l'operatore ~: AREAS((A1,B2)) di Excel diventa AREAS(([.A1]~[.B2])). Tradurre quella virgola in ; trasforma invece un argomento unione in due argomenti
• Gli array inline separano le colonne con ; e le righe con |: {1,2;3,4} di Excel diventa {1;2|3;4}. Prima della v2.384.55 HotXLS produceva {1;2;3;4}, una singola riga di quattro valori

La virgola è la parte difficile, perché un solo carattere Excel porta tre significati. Dalla v2.384.55 il writer HotXLS tiene traccia di uno stack di parentesi mentre traduce: una ( direttamente dopo un nome apre una chiamata di funzione, le cui virgole diventano ;; qualsiasi altra ( è una parentesi di raggruppamento, le cui virgole diventano ~; e le virgole dentro {} sono separatori di colonna array. Con quello e la correzione del namespace, LibreOffice 26.2 ha valutato correttamente tutte e otto le formule sonda array e unione, INDEX e AREAS su unioni comprese

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;

    // Scritta come of:=SUM([.A:.A]) dalla v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Scritta come of:=[.A1]*[.$B$1]; i marker $ sopravvivono dalla v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Le formule che il traduttore non modella ricadono su msoxl:= con il testo Excel invariato, ecco perché conta anche la dichiarazione msoxl. Nel writer attuale quel percorso include i riferimenti qualificati per foglio come Sheet2!A1 e i riferimenti strutturati a tabelle. HotXLS rilegge le formule msoxl: all'importazione, quindi il suo round trip interno conserva l'espressione intatta, ma come le tratta un'altra applicazione è fuori dal controllo del writer. Se una formula da cui i tuoi consumatori dipendono esce con il prefisso msoxl:, apri il file in entrambe le applicazioni prima di spedirlo

Perché Excel non vede i formati condizionali scritti solo come calcext?

Excel 16 non vede i formati condizionali calcext perché legge i formati condizionali ODS esclusivamente dai figli <style:map> degli stili di cella e ignora del tutto il blocco calcext:conditional-formats. L'esperimento che lo stabilisce è breve: prendi un ODS salvato da LibreOffice, cancella gli elementi style:map, ed Excel legge zero regole; cancella invece il blocco calcext, ed Excel le legge ancora tutte. LibreOffice si comporta al contrario. calcext è il namespace di estensione di LibreOffice, non fa parte dello standard ODF, e quando una regola calcext è presente LibreOffice la prende e ignora la style:map

Prima della v2.384.69 HotXLS scriveva solo calcext, quindi un file ODS con un'evidenziazione perfettamente buona si apriva in Excel senza regole value e senza regole formula. HotXLS ora scrive entrambe le forme. La metà style:map usa la grammatica delle condizioni dello schema OpenDocument (ODF 1.3 Parte 3), con le grafie esatte che Excel 16 e LibreOffice 26.2 producono entrambi quando salvano ODS:

<!-- Semplificato. Stile portatore per ogni cella di A1:A50 (due regole value) -->
<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>

<!-- Stile portatore per ogni cella di C1:C50 (una regola formula) -->
<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>

Il rovescio della medaglia di style:map è che vive sugli stili di cella, quindi è per cella. Ogni cella nell'intervallo della regola deve portare uno stile che contiene la mappa, celle vuote comprese, altrimenti la regola semplicemente non copre quella cella in Excel. HotXLS copia lo stile di formattazione esistente di ogni cella, appende le mappe, e deduplica gli stili portatori per la coppia stile originale e testo della mappa, così un intervallo di 500 celle con formattazione identica produce comunque un solo stile. Il writer estende anche la tabella scritta all'intervallo della regola, il che significa che le righe finali vuote dentro una regola vengono emesse anziché scartate. Dalla v2.384.69 styles.xml porta anche uno stile di cella Default vuoto, così style:apply-style-name="Default" ha sempre un bersaglio

La grafia calcext che LibreOffice accetta davvero

LibreOffice accetta una regola value calcext solo quando l'operatore di confronto fa parte del testo del value, come >3 o between(1,10), e una regola formula solo quando è scritta formula-is(...). Entrambi i punti sono costati a HotXLS una release, perché le grafie sbagliate producono una regola che importa senza errori e poi combacia le celle sbagliate

Il primo errore fu un attributo calcext:operator accanto a calcext:value. Si legge naturalmente, ma è inventato: LibreOffice non conosce quell'attributo, quindi importava ogni regola value come "uguale a 0". Il secondo fu mettere is-true-formula(...), la grafia della style:map, dentro una condizione calcext, che LibreOffice importava anch'essa come confronto cell-value con 0. La correzione formula è uscita nella v2.384.66 e quella value nella v2.384.69:

<!-- Sbagliato: LibreOffice ignora calcext:operator e importa "uguale a 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Giusto: l'operatore viaggia dentro il valore -->
<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"/>

<!-- Giusto: le regole formula usano formula-is, riferimenti relativi ancorati alla cella base -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

La cella base è ciò che dà significato ai riferimenti relativi. HotXLS ancora ogni regola alla cella in alto a sinistra della sua prima area, così una formula scritta per C1 valuta come C2, C3 e così via lungo l'intervallo, esattamente come fa nella formattazione condizionale di Excel stessa. L'espressione della regola passa per lo stesso traduttore delle formule di cella, quindi array, unioni, colonne intere e marker $ escono nelle forme descritte sopra. Sul lato Delphi aggiungi le regole esattamente come faresti per un file .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Regole value: style:map cell-content()>100 più calcext value ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: rosso chiaro

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

  // Regola formula in sintassi Excel (separatori virgola, relativa a C1):
  // style:map is-true-formula(...) più calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: giallo chiaro

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

Rileggere in Delphi ODS prodotti da Excel e LibreOffice

Quando HotXLS apre un file ODS, il suo reader accetta entrambi i dialetti di formato condizionale e entrambe le grafie calcext, e non conta due volte una regola che il file porta in entrambe le forme. I file reali vengono da tre writer, ognuno con le sue abitudini:

• Calcext vecchio e nuovo. I file con un attributo calcext:operator, compresi gli ODS scritti da HotXLS prima della v2.384.69, passano comunque per il parse legacy. Le condizioni formula vengono riconosciute sia come formula-is(...) sia come is-true-formula(...)
• La grafia style:map di Excel. Excel prefigge le condizioni con of:, come in of:cell-content-is-between(1,10), e omette la cella base sulle regole value. Entrambe le cose sono accettate
• Celle vuote. Excel e LibreOffice mettono entrambi la mappa per le celle vuote sullo stile default della colonna anziché su una cella, quindi il reader risolve gli stili default di colonna per le celle ripetute prima di raccogliere le mappe
• Ricostruzione degli intervalli. Le mappe vengono raccolte per cella, quindi dopo la lettura di un foglio il reader rifonde le celle che condividono la stessa condizione e cella base in intervalli, prima lungo ogni riga e poi giù per le estensioni di colonna corrispondenti, e scarta ogni regola già letta da calcext

La correzione v2.384.72 riguarda gli stili numerici, non le regole. Excel 16 e LibreOffice 26.2 scrivono entrambi il formato General come uno stile numerico il cui elemento number:number non ha number:decimal-places, tipicamente <number:number number:min-integer-digits="1"/>. Il reader HotXLS trattava il conteggio mancante come due decimali fissi, così ogni valore nello stile Default importava con 0.00 e 1.5 si mostrava come 1.50. Dalla v2.384.72 un elemento numero semplice senza posizioni decimali, senza decimali minimi, senza raggruppamento e con al massimo una cifra intera si mappa su General, e un General da solo lascia la cella senza alcun formato numerico. Il testo attorno viene conservato, come in General" kg", e i numeri raggruppati conservano la mappatura precedente perché Excel non ha un formato General raggruppato

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]; // l'indicizzatore Sheets è a base uno
    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;

    // Una cella nello stile General di Excel si rilegge senza formato numerico
    // dalla v2.384.72, invece di '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Le formule delle regole tornano in sintassi Excel con separatori virgola, la stessa forma che passeresti a AddCondFormatExpression, quindi una regola scritta da HotXLS si rilegge come la stringa identica. Per il quadro più ampio di ciò che il percorso di importazione ODS conserva e scarta, vedi la guida HotXLS al round trip di apertura e salvataggio ODS; per come le righe ripetute di Excel e LibreOffice vengono espanse all'importazione, vedi righe ripetute ODS come tronconi di altezza riga

Quali sono i limiti dell'interop dei formati condizionali ODS di HotXLS?

L'approccio a doppio markup copre le regole di confronto value e le regole formula, e fino a lì. Tutto il resto è a senso unico o non viene scritto affatto:

• Scale di colore e barre dati vengono scritti solo come elementi calcext, quindi LibreOffice li mostra ed Excel no
• Altri tipi di regole, come set di icone, regole testuali, top-N, sopra-la-media e duplicati, non hanno output ODS nel writer attuale. Una regola testuale di solito si può riscrivere come regola formula, per esempio ISNUMBER(SEARCH("late",B2)) su B2:B200, che poi raggiunge entrambe le applicazioni
• Regole su colonne e righe intere come C:C vengono stese solo sull'area della tabella effettivamente scritta, anziché su tutte le 1.048.576 righe, quindi Excel vede queste regole solo sulle celle che esistono nel file
• File con solo style:map. Quando un file non ha blocco calcext, HotXLS interpreta i riferimenti relativi nelle regole formula dall'angolo in alto a sinistra dell'intervallo ricostruito, non spostandosi dalla cella base dichiarata
• Regole sovrapposte da LibreOffice. Quando una cella è coperta da più regole, LibreOffice scrive su di essa solo la mappa della prima regola. File così non si possono leggere completamente dalla sola style:map, il che è un motivo in più per cui il reader preferisce calcext quando esistono entrambi

Il limite di processo conta più di tutti questi. I difetti dietro a queste release sono passati per round trip che scrivevano ODS e lo rileggevano con HotXLS, e alcuni avrebbero passato anche un controllo manuale nell'applicazione sbagliata: le formule su colonne intere funzionavano in LibreOffice mentre Excel mostrava #NAME?, e dalla v2.384.66 le regole formula funzionavano in LibreOffice mentre Excel non mostrava ancora nessuna regola fino alla v2.384.69. Se l'interop ODS è un requisito, il test di accettazione è aprire il file in Excel e in LibreOffice e confrontare ciò che ciascuno mostra. La stessa disciplina vale per gli stili a cui le regole puntano; l'articolo HotXLS su formattazione condizionale e stili copre come gli stili di evidenziazione vengono definiti sul lato cartella di lavoro

Riferimento rapido: ODS che entrambe le applicazioni leggono

• Dichiara xmlns:of e xmlns:msoxl sulla radice di content.xml, o LibreOffice mostra #VALUE! per ogni formula (HotXLS dalla v2.384.56)
• Scrivi i riferimenti come [.A1], conserva ogni $, e scrivi colonne e righe intere come [.A:.A] e [.1:.1] (dalla v2.384.55 e v2.384.65)
• Usa ; per gli argomenti, ~ per le unioni di riferimenti, e | tra le righe degli array inline
• Scrivi ogni regola value o formula come <style:map> sullo stile di ogni cella coperta per Excel, e come condizione calcext per LibreOffice (dalla v2.384.69)
• In calcext, metti l'operatore nel value (>3, between(1,10)) e scrivi le regole formula formula-is(...) con una cella base (dalla v2.384.66 e v2.384.69)
• Aspettati uno stile numerico General senza number:decimal-places all'importazione; HotXLS lo legge come General dalla v2.384.72
• Verifica ogni nuovo profilo di esportazione aprendo il file sia in Excel sia in LibreOffice, mai in uno solo dei due

HotXLS è una libreria foglio di calcolo nativa Delphi e C++Builder che legge e scrive XLS, XLSX e ODS senza Excel o LibreOffice installati; sorgente completo, lista delle funzionalità e licenze sono sulla pagina del componente foglio di calcolo Delphi HotXLS