Article technique

Interop ODS HotXLS : formules et règles lisibles par Excel

Pour produire un fichier ODS qu’Excel et LibreOffice lisent correctement l’un et l’autre, HotXLS écrit chaque formule en syntaxe OpenFormula sous un espace de noms of: déclaré, et écrit chaque format conditionnel de valeur ou de formule deux fois : en <style:map> sur le style de chaque cellule couverte, seule forme que lise Excel 16, et en bloc calcext:conditional-formats, la forme à laquelle LibreOffice se fie. Chaque application ignore la moitié destinée à l’autre, donc un fichier qui s’affiche bien dans l’une ne prouve rien pour l’autre

Cette dernière phrase est toute la leçon de l'interop ODS : un fichier qu'un writer relit parfaitement peut rester un fichier que l'une ou l'autre des applications cibles lit de travers. Ce qui suit décrit ce que chaque application accepte réellement, le balisage qui satisfait les deux, et les appels d'API HotXLS qui le produisent depuis Delphi

Pourquoi un fichier ODS semble-t-il correct dans une application et cassé dans l’autre ?

Un fichier ODS semble correct dans une application et cassé dans l’autre parce qu’Excel et LibreOffice lisent des parties différentes du même paquet. OpenDocument donne aux formules et aux formats conditionnels plus d’une épellation légale, LibreOffice ajoute par-dessus son propre espace de noms d’extension, et chaque consommateur choisit le sous-ensemble qu’il implémente. Un écrivain testé contre un seul consommateur convergera volontiers vers du balisage que l’autre lit de travers en silence

Aucune des deux applications ne signale d'erreur. LibreOffice affiche #VALUE! dans les cellules dont il n'a pas pu analyser la formule ; Excel ouvre le classeur avec les formats conditionnels simplement absents, ou avec une formule réécrite en quelque chose qui s'évalue en #NAME? ou en la constante 0. Un writer qui fait l'aller-retour sur sa propre sortie ne voit rien de tout cela. L'espace de noms des formules est le cas d'école : un lecteur qui reconnaît le préfixe of: comme du texte simple passe tous les allers-retours sur lui-même pendant que LibreOffice affiche #VALUE! dans chaque cellule à formule

FonctionnalitéExcel 16 litLibreOffice 26.2 lit
Colonne entière écrite A:AMal lu comme A:(A)Toléré
Colonne entière écrite [.A:.A]OuiOui
Formats conditionnels en <style:map>Oui, la seule forme lueIgnorés quand calcext est présent
Formats conditionnels en calcext:conditional-formatsIgnorésOui, préférés
Règle de valeur calcext avec attribut calcext:operatorIgnoréeImportée comme "égal à 0"
Règle de formule calcext épelée is-true-formula(...)IgnoréeImportée comme comparaison de valeur avec 0

OpenFormula dans ODS : déclarer l’espace de noms, puis soigner la syntaxe

Une cellule à formule ODS n'est lisible par LibreOffice que si le préfixe of: de table:formula se résout vers un espace de noms XML déclaré. Le préfixe n'est pas une décoration. of: correspond à urn:oasis:names:tc:opendocument:xmlns:of:1.2, et msoxl:, le préfixe qu'utilise HotXLS pour les formules que son traducteur OpenFormula ne modélise pas, correspond à http://schemas.microsoft.com/office/excel/formula. Utilisez l'un ou l'autre préfixe sans le déclarer sur la racine de content.xml et LibreOffice ne peut pas identifier la grammaire de formule du tout

<!-- Avant v2.384.56 : préfixe utilisé, jamais déclaré ; LibreOffice affiche #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"/>

<!-- Depuis v2.384.56 : les deux namespaces de formules déclarés sur la racine -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Espace de noms déclaré, l'expression elle-même doit encore être du OpenFormula valide, au sens d'OpenDocument 1.3 Part 4. Les pièges sont les endroits où la syntaxe Excel et OpenFormula se ressemblent sans être identiques :

  • Références de cellules : entre crochets et préfixées par un point, et les marqueurs $ font partie de la référence : [.$A$1] et [.A$1:.$B2] sont du OpenFormula valide. Un traducteur qui perd le $ transforme les références absolues en relatives, et le dégât n'apparaît que quand quelqu'un copie la cellule
  • Colonnes et lignes entières : la forme entre crochets [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2] est obligatoire. Un of:=SUM(A:A) nu est toléré par LibreOffice, mais Excel 16 l'ouvre en =SUM(A:(A)) avec #NAME?, et transforme les références de lignes et $A:$B en constante 0. HotXLS écrit la forme entre crochets
  • Arguments de fonctions : séparés par ;, pas par ,
  • Unions de références : l'opérateur ~ ; le AREAS((A1,B2)) d'Excel devient AREAS(([.A1]~[.B2])). Traduire cette virgule en ; transforme un argument union en deux arguments
  • Tableaux en ligne : colonnes séparées par ; et lignes par | ; le {1,2;3,4} d'Excel devient {1;2|3;4}. Manquez le séparateur de lignes et vous obtenez {1;2;3;4}, une seule ligne de quatre valeurs

La virgule est la partie difficile, car un caractère Excel porte trois sens. Le writer HotXLS tient une pile de parenthèses pendant la traduction : un ( directement après un nom ouvre un appel de fonction, dont les virgules deviennent des ; ; tout autre ( est une parenthèse de groupement, dont les virgules deviennent des ~ ; et les virgules dans des {} sont des séparateurs de colonnes de tableau. Avec cela et l'espace de noms déclaré, LibreOffice 26.2 évalue correctement les formules de tableaux et d'unions, INDEX et AREAS sur des unions comprises

Schéma HotXLS de la pile de parenthèses qui traduit les virgules Excel en OpenFormula : une parenthèse juste après un nom ouvre un appel de fonction dont les virgules deviennent des points-virgules, toute autre parenthèse est du groupement dont les virgules deviennent le tilde opérateur d’union, et les virgules entre accolades sont des séparateurs de colonnes de tableau, comme dans AREAS de l’union de A1 et B2
La virgule porte trois sens dans la syntaxe Excel, et seule la pile de parenthèses en cours les départage ; traduisez une virgule d’union en point-virgule et un argument devient deux en silence
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;

    // Écrite of:=SUM([.A:.A]) depuis v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Écrite of:=[.A1]*[.$B$1] ; les marqueurs $ survivent depuis v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Les formules que le traducteur ne modélise pas retombent sur msoxl:= avec le texte Excel inchangé, voilà pourquoi la déclaration msoxl compte elle aussi. Ce chemin inclut les références qualifiées par feuille telles que Sheet2!A1 et les références structurées de tables. HotXLS relit les formules msoxl: à l'import, donc son propre aller-retour garde l'expression intacte, mais la façon dont une autre application les traite échappe au contrôle de l'auteur du fichier. Si une formule dont dépendent vos utilisateurs ressort avec le préfixe msoxl:, ouvrez le fichier dans les deux applications avant de le livrer

Pourquoi Excel n'affiche-t-il pas les formats conditionnels écrits uniquement en calcext ?

Excel 16 ne voit pas les formats conditionnels calcext parce qu'il ne lit les formats conditionnels ODS exclusivement que dans les enfants <style:map> des styles de cellules et ignore complètement le bloc calcext:conditional-formats. L’expérience qui tranche est courte : prenez un ODS sauvegardé par LibreOffice, supprimez les éléments style:map, et Excel lit zéro règle ; supprimez à la place le bloc calcext, et Excel les lit toujours tous. LibreOffice se comporte à l’inverse. calcext est l’espace de noms d’extension de LibreOffice, pas une partie du standard ODF, et quand une règle calcext est présente, LibreOffice la prend et ignore le style:map

Schéma double canal HotXLS pour les formats conditionnels ODS : chaque règle de valeur ou de formule est écrite comme un style map sur le style de chaque cellule couverte, seule forme que lise Excel 16, et comme un bloc calcext conditional formats avec l’opérateur dans la valeur, la forme que préfère LibreOffice, tandis que chaque application ignore en silence l’autre épellation
Excel lit les style maps et ignore calcext, LibreOffice préfère calcext et jette les maps, et aucun des deux n’affiche d’erreur ; écrire les deux épellations depuis un seul appel HotXLS est la seule façon qu’un fichier se vérifie dans les deux

Écrivez uniquement du calcext et un fichier ODS à la surbrillance parfaitement bonne s'ouvre dans Excel sans aucune règle de valeur ni règle de formule, donc HotXLS écrit les deux formes. La moitié style:map utilise la grammaire de condition du schéma OpenDocument (ODF 1.3 Part 3), avec les graphies exactes qu'Excel 16 et LibreOffice 26.2 produisent tous les deux à la sauvegarde ODS :

<!-- Simplifié. Style porteur pour chaque cellule de A1:A50 (deux règles de valeur) -->
<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>

<!-- Style porteur pour chaque cellule de C1:C50 (une règle de formule) -->
<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>

Le piège de style:map, c'est qu'il vit sur les styles de cellules, donc il est par cellule. Chaque cellule de la plage visée par la règle doit porter un style contenant la map, cellules vides comprises, sinon la règle ne couvre tout simplement pas cette cellule dans Excel. HotXLS copie le style de mise en forme existant de chaque cellule, y ajoute les maps, et déduplique les styles porteurs par la paire style d'origine et texte de map, si bien qu'une plage de 500 cellules de mise en forme identique produit quand même un seul style. Le writer étend aussi la table écrite jusqu'à la plage de la règle, ce qui signifie que les lignes de queue vides à l'intérieur d'une règle sont émises plutôt que jetées. styles.xml porte aussi un style de cellule Default vide, si bien que style:apply-style-name="Default" a toujours une cible

L’épellation calcext que LibreOffice accepte réellement

LibreOffice n'accepte une règle de valeur calcext que si l'opérateur de comparaison fait partie du texte de la valeur, tel que >3 ou between(1,10), et une règle de formule que si elle est orthographiée formula-is(...). Les deux points sont faciles à rater, car les mauvaises graphies produisent une règle qui s'importe sans erreur et correspond ensuite aux mauvaises cellules

Le premier piège est un attribut calcext:operator à côté de calcext:value. Il se lit naturellement, mais il est inventé : LibreOffice ne connaît pas cet attribut, donc il importe chaque règle de valeur comme "égale à 0". Le second est de mettre is-true-formula(...), la graphie style:map, dans une condition calcext, que LibreOffice importe aussi comme une comparaison de valeur de cellule avec 0 :

<!-- Faux : LibreOffice ignore calcext:operator et importe "égal à 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Juste : l’opérateur voyage dans la valeur -->
<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"/>

<!-- Juste : les règles de formule utilisent formula-is, refs relatives ancrées à la cellule de base -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>
Schéma HotXLS contrastant les épellations fausse et juste des conditions calcext : un attribut calcext operator est inventé et importe chaque règle de valeur comme égal à 0, l’opérateur appartient dans la valeur comme supérieur à 100 ou entre 1 et 10, et les règles de formule doivent dire formula-is ancré à une cellule de base plutôt que l’épellation style map is-true-formula
Les deux épellations fausses s’importent sans erreur puis correspondent aux mauvaises cellules, une règle lue comme égal à 0 ne surligne rien de ce que vous vouliez ; le correctif, c’est l’opérateur dans la valeur et formula-is pour les expressions

La cellule de base, c'est ce qui donne leur sens aux références relatives. HotXLS ancre chaque règle à la cellule en haut à gauche de sa première zone de plage, donc une formule écrite pour C1 s’évalue comme C2, C3 et ainsi de suite le long de la plage, exactement comme dans le formatage conditionnel d’Excel lui-même. L’expression de la règle passe par le même traducteur que les formules de cellules, donc tableaux, unions, colonnes entières et marqueurs $ sortent dans les formes décrites ci-dessus. Côté Delphi, vous ajoutez les règles exactement comme pour un fichier .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Règles de valeur : style:map cell-content()>100 plus calcext value ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR : rouge clair

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

  // Règle de formule en syntaxe Excel (séparateurs virgules, relative à C1) :
  // style:map is-true-formula(...) et calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR : jaune clair

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

Relire dans Delphi des ODS venus d’Excel et de LibreOffice

Quand HotXLS ouvre un fichier ODS, son lecteur accepte les deux dialectes de formats conditionnels et les deux épellations calcext, et il ne compte pas une règle deux fois quand le fichier la porte sous les deux formes. Les vrais fichiers viennent de trois écrivains, chacun avec ses habitudes :

  • Vieux et nouveau calcext. Les fichiers avec un attribut calcext:operator passent quand même par l'analyse historique. Les conditions de formule sont reconnues comme formula-is(...) ou is-true-formula(...)
  • La graphie style:map d'Excel. Excel préfixe les conditions avec of:, comme dans of:cell-content-is-between(1,10), et omet la cellule de base sur les règles de valeur. Les deux sont acceptés
  • Cellules vides. Excel et LibreOffice mettent tous les deux la map des cellules vides sur le style par défaut de colonne plutôt que sur une cellule, donc le lecteur résout les styles par défaut de colonne pour les cellules répétées avant de collecter les maps
  • Reconstruction de régions. Les maps sont collectées par cellule, donc après la lecture d'une feuille le lecteur refusionne les cellules qui partagent la même condition et la même cellule de base en plages, d'abord le long de chaque ligne puis en descendant les étendues de colonnes correspondantes, et jette toute règle déjà lue depuis calcext

Les styles de nombre exigent le même soin que les règles. Excel 16 et LibreOffice 26.2 écrivent tous les deux le format General comme un style de nombre dont l'élément number:number n'a pas de number:decimal-places, typiquement <number:number number:min-integer-digits="1"/>. Lisez le compte manquant comme deux décimales fixes et chaque valeur du style Default s'importe avec 0.00, donc 1.5 s'affiche en 1.50. HotXLS associe à General un élément de nombre simple sans décimales, sans minimum de décimales, sans groupement et avec au plus un chiffre entier, et un General solitaire laisse la cellule sans format de nombre du tout. Le texte autour est conservé, comme dans General" kg", et les nombres groupés ne sont pas associés à General car Excel n'a pas de format General groupé

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’indexeur Sheets est à base 1
    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;

    // Une cellule du style General d’Excel se relit sans format de nombre
    // depuis v2.384.72, au lieu de '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Les formules de règles reviennent en syntaxe Excel avec séparateurs virgules, la même forme que vous passeriez à AddCondFormatExpression, donc une règle écrite par HotXLS se relit comme la chaîne identique. Pour la vue d’ensemble de ce que le chemin d’import ODS garde et jette, voir le guide HotXLS de l’aller-retour ouverture-sauvegarde ODS ; pour la façon dont les lignes répétées d’Excel et LibreOffice sont déployées à l’import, voir les lignes répétées ODS comme suites de hauteurs de lignes

Quels genres de formats conditionnels atteignent les deux applications ?

L'approche à double balisage couvre les règles de comparaison de valeurs et les règles de formule, les deux genres que les deux applications lisent. Les autres genres suivent des règles simples :

  • Échelles de couleurs et barres de données : écrites comme éléments calcext, la forme que lit LibreOffice, donc comptez sur LibreOffice comme consommateur ODS pour elles
  • Les autres genres de règles, comme jeux d'icônes, règles de texte, top-N, au-dessus de la moyenne et doublons, gagnent à être reformulées en règles de formule avant un export ODS chaque fois que la condition le permet. Une règle de texte, par exemple, devient ISNUMBER(SEARCH("late",B2)) sur B2:B200, ce qui atteint alors les deux applications
  • Règles de colonnes et lignes entières telles que C:C : posées seulement sur la zone de table réellement écrite, plutôt que sur les 1 048 576 lignes, donc Excel ne voit ces règles que sur les cellules qui existent dans le fichier
  • Fichiers avec seulement du style:map. Quand un fichier n'a pas de bloc calcext, HotXLS interprète les références relatives des règles de formule depuis le coin en haut à gauche de la plage reconstruite, sans décalage depuis la cellule de base déclarée
  • Règles chevauchantes de LibreOffice. Quand une cellule est couverte par plusieurs règles, LibreOffice n'écrit sur elle que la map de la première règle. De tels fichiers ne peuvent pas être lus complètement depuis style:map seul, ce qui est une raison de plus pour que le lecteur préfère calcext quand les deux existent

La règle du processus compte plus que tout cela. Chaque piège de cet article passe un aller-retour qui écrit de l'ODS et le relit avec la même bibliothèque, et plusieurs passent aussi un contrôle manuel dans la mauvaise application : les formules de colonnes entières marchent dans LibreOffice pendant qu'Excel affiche #NAME?, et les règles de formule écrites uniquement en calcext marchent dans LibreOffice pendant qu'Excel n'affiche aucune règle. Si l'interop ODS est une exigence, le test d'acceptation est d'ouvrir le fichier dans Excel et dans LibreOffice et de comparer ce que chacun affiche. La même discipline vaut pour les styles que les règles pointent ; l'article HotXLS sur le formatage conditionnel et les styles explique comment les styles de surbrillance se définissent côté classeur

Aide-mémoire : l’ODS que les deux applications lisent

  • Déclarez xmlns:of et xmlns:msoxl sur la racine de content.xml, sinon LibreOffice affiche #VALUE! pour chaque formule
  • Écrivez les références comme [.A1], gardez chaque $, et écrivez colonnes et lignes entières comme [.A:.A] et [.1:.1]
  • Utilisez ; pour les arguments, ~ pour les unions de références, et | entre les lignes de tableaux en ligne
  • Écrivez chaque règle de valeur ou de formule comme une <style:map> sur le style de chaque cellule couverte pour Excel, et comme une condition calcext pour LibreOffice
  • Dans calcext, mettez l'opérateur dans la valeur (>3, between(1,10)) et orthographiez les règles de formule formula-is(...) avec une cellule de base
  • Attendez-vous à un style de nombre General sans number:decimal-places à l'import ; HotXLS le lit comme General
  • Vérifiez chaque nouveau profil d'export en ouvrant le fichier dans Excel et dans LibreOffice, jamais dans un seul des deux

HotXLS est une bibliothèque tableur Delphi et C++Builder native qui lit et écrit XLS, XLSX et ODS sans Excel ni LibreOffice installés ; code source complet, liste des fonctionnalités et licences sont sur la page du composant tableur HotXLS Delphi