Articolo tecnico

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 è l'intera lezione dell'interop ODS: un file che un writer rilegge alla perfezione può comunque essere uno che una delle due applicazioni bersaglio interpreta 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 interpretare; 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. Il namespace delle formule è il caso da manuale: un reader che riconosce il prefisso of: come testo semplice passa ogni round trip con sé stesso mentre LibreOffice mostra #VALUE! in ogni cella formula

FunzionalitàExcel 16 leggeLibreOffice 26.2 legge
Intera colonna scritta come A:ALetta male come A:(A)Tollerato
Intera colonna scritta come [.A:.A]SìSì
Formati condizionali in <style:map>Sì, l'unica forma che leggeIgnorati quando calcext è presente
Formati condizionali in calcext:conditional-formatsIgnoratiSì, preferiti
Regola value calcext con attributo calcext:operatorIgnorataImportata come "uguale a 0"
Regola formula calcext scritta is-true-formula(...)IgnorataImportata 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. Usate uno dei due prefissi senza dichiararlo sulla radice di content.xml e LibreOffice non riesce a identificare affatto la grammatica delle formule

<!-- Sbagliato: 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"/>

<!-- Giusto: 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 dichiarato, 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:

  • Riferimenti di cella sono racchiusi tra parentesi con prefisso punto, e i marker $ fanno parte del riferimento: [.$A$1] e [.A$1:.$B2] sono OpenFormula validi. Un traduttore che butta via il $ trasforma riferimenti assoluti in relativi, e il danno si vede solo quando qualcuno copia la cella
  • Colonne e righe intere devono usare la forma tra parentesi [.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 di riga e $A:$B nella costante 0. HotXLS scrive la forma tra parentesi
  • Argomenti delle funzioni sono separati da ;, non da ,
  • Unioni di riferimenti usano l'operatore ~: AREAS((A1,B2)) di Excel diventa AREAS(([.A1]~[.B2])). Tradurre quella virgola in ; trasforma un argomento unione in due argomenti
  • Array inline separano le colonne con ; e le righe con |: {1,2;3,4} di Excel diventa {1;2|3;4}. Fatevi sfuggire il separatore di riga e otterrete {1;2;3;4}, una singola riga di quattro valori

La virgola è la parte difficile, perché un solo carattere Excel porta tre significati. 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 il namespace dichiarato, LibreOffice 26.2 valuta correttamente le formule array e unione, INDEX e AREAS su unioni comprese

Diagramma HotXLS dello stack di parentesi che traduce le virgole Excel in OpenFormula: una parentesi subito dopo un nome apre una chiamata di funzione le cui virgole diventano punti e virgola, qualsiasi altra parentesi è raggruppamento e le sue virgole diventano l'operatore unione tilde, e le virgole dentro le graffe sono separatori di colonna array, come in AREAS dell'unione di A1 e B2
La virgola porta tre significati nella sintassi Excel, e solo lo stack di parentesi in esecuzione li distingue; tradurre una virgola di unione in un punto e virgola e un argomento diventa silenziosamente due
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])
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Scritta come of:=[.A1]*[.$B$1]; i marker $ sopravvivono
    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. 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 conserva l'espressione intatta, ma come le tratta un'altra applicazione è fuori dal controllo del writer. Se una formula da cui i vostri consumatori dipendono esce con il prefisso msoxl:, aprite 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

Diagramma HotXLS del doppio canale per i formati condizionali ODS: ogni regola value o formula viene scritta come style map sullo stile di ogni cella coperta, l'unica forma che Excel 16 legge, e come blocco calcext conditional formats con l'operatore dentro il value, la forma che LibreOffice preferisce, mentre ogni applicazione ignora in silenzio l'altra grafia
Excel legge le style map e ignora calcext, LibreOffice preferisce calcext e butta via le map, e nessuna mostra un errore; scrivere entrambe le grafie da una sola chiamata HotXLS è l'unico modo in cui un file si verifica in entrambe

Scrivete solo calcext e un file ODS con un'evidenziazione perfettamente buona si apre in Excel senza regole value e senza regole formula, quindi HotXLS 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. 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 facili da sbagliare, perché le grafie sbagliate producono una regola che importa senza errori e poi combacia le celle sbagliate

La prima trappola è un attributo calcext:operator accanto a calcext:value. Si legge naturalmente, ma è inventato: LibreOffice non conosce quell'attributo, quindi importa ogni regola value come "uguale a 0". La seconda è mettere is-true-formula(...), la grafia della style:map, dentro una condizione calcext, che LibreOffice importa anch'essa come confronto cell-value con 0:

<!-- 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"/>
Diagramma HotXLS che contrappone le grafie calcext delle condizioni sbagliate e giuste: un attributo calcext operator è inventato e importa ogni regola value come uguale a 0, l'operatore sta dentro il value come in maggiore di 100 o tra 1 e 10, e le regole formula devono dire formula-is ancorate a una cella base anziché la grafia is-true-formula della style map
Entrambe le grafie sbagliate importano senza errore e poi combaciano le celle sbagliate, una regola che si legge come uguale a 0 non evidenzia nulla di ciò che volevate; la grafia giusta è l'operatore nel value e formula-is per le espressioni

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 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: l'una e l'altra variante 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

Gli stili numerici richiedono la stessa cura delle 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"/>. Leggete il conteggio mancante come due decimali fissi e ogni valore nello stile Default importa con 0.00, così 1.5 si mostra come 1.50. HotXLS mappa su General un elemento numero semplice senza posizioni decimali, senza decimali minimi, senza raggruppamento e con al massimo una cifra intera, 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 non vengono mappati su General 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,
    // non '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 tipi di formato condizionale arrivano a entrambe le applicazioni?

L'approccio a doppio markup copre le regole di confronto value e le regole formula, i due tipi che entrambe le applicazioni leggono. Gli altri tipi seguono regole semplici:

  • Scale di colore e barre dati vengono scritte come elementi calcext, la forma che LibreOffice legge, quindi considerate LibreOffice come loro consumatore ODS
  • Altri tipi di regole, come set di icone, regole testuali, top-N, sopra-la-media e duplicati, hanno come migliore opzione la riscrittura come regole formula prima di un export ODS ovunque la condizione lo consenta. Una regola testuale, per esempio, diventa 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, così 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 vi scrive solo la mappa della prima regola. File così non si possono leggere completamente dalla sola style:map, perché manca l'apporto delle regole successive, il che è un motivo in più per cui il reader preferisce calcext quando esistono entrambi

La regola di processo conta più di tutte queste. Ogni trappola in questo articolo passa un round trip che scrive ODS e lo rilegge con la stessa libreria, e parecchie passano anche un controllo manuale nell'applicazione sbagliata: le formule su colonne intere funzionano in LibreOffice mentre Excel mostra #NAME?, e le regole formula scritte solo come calcext funzionano in LibreOffice mentre Excel non mostra proprio nessuna regola. 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

  • Dichiarate xmlns:of e xmlns:msoxl sulla radice di content.xml, o LibreOffice mostra #VALUE! per ogni formula
  • Scrivete i riferimenti come [.A1], conservate ogni $, e scrivete colonne e righe intere come [.A:.A] e [.1:.1]
  • Usate ; per gli argomenti, ~ per le unioni di riferimenti, e | fra le righe degli array inline
  • Scrivete ogni regola value o formula come <style:map> sullo stile di ogni cella coperta per Excel, e come condizione calcext per LibreOffice: l'importante è produrre entrambe le forme
  • In calcext, mettete l'operatore nel value (>3, between(1,10)) e scrivete le regole formula come formula-is(...) con una cella base
  • Aspettatevi uno stile numerico General senza number:decimal-places all'importazione; HotXLS lo legge come General
  • Verificate ogni nuovo profilo di esportazione aprendo il file sia in Excel sia in LibreOffice, mai in uno soltanto 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