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, c’est la leçon derrière six versions de HotXLS entre v2.384.55 et v2.384.72. Chaque correctif a commencé par un fichier que HotXLS écrivait, relisait parfaitement, et qu’une des deux applications cibles se trompait à lire. Ce qui suit, c’est ce que chaque application accepte réellement, le balisage qui satisfait les deux, et les appels 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 application ne signale d’erreur. LibreOffice affiche #VALUE! dans les cellules dont il n’a pas pu analyser les formules ; 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 écrivain qui fait l’aller-retour sur sa propre sortie ne voit rien de tout cela. HotXLS est tombé exactement dans ce piège avec l’espace de noms des formules : son lecteur rapprochait le préfixe of: comme texte brut, donc chaque aller-retour interne passait pendant que LibreOffice affichait #VALUE! dans chaque cellule formule

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

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

Une cellule formule dans 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. Avant v2.384.56, la racine du content.xml utilisait les deux préfixes sans les déclarer, et LibreOffice ne pouvait pas du tout identifier la grammaire de formules

<!-- 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" ...>

L’espace de noms corrigé, l’expression elle-même doit encore être de l’OpenFormula valide, tel que défini dans OpenDocument 1.3 Partie 4. Les pièges sont les endroits où la syntaxe Excel et OpenFormula se ressemblent sans être les mêmes :

• Les références de cellules sont entre crochets avec préfixe point, et les marqueurs $ font partie de la référence : [.$A$1] et [.A$1:.$B2] sont de l’OpenFormula valide. Avant v2.384.55, l’écrivain HotXLS laissait tomber chaque $, si bien que les références absolues revenaient relatives et ne se trompaient qu’une fois la cellule copiée
• Les colonnes et lignes entières doivent utiliser la forme entre crochets [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Un of:=SUM(A:A) nu est toléré par LibreOffice, mais Excel 16 l’ouvre comme =SUM(A:(A)) avec #NAME?, et transforme les références de lignes et $A:$B en la constante 0. HotXLS écrit la forme entre crochets depuis v2.384.65
• Les arguments de fonction sont séparés par ;, pas par ,
• Les unions de références utilisent l’opérateur ~ : le AREAS((A1,B2)) d’Excel devient AREAS(([.A1]~[.B2])). Traduire cette virgule en ; transforme à la place un argument union en deux arguments
• Les tableaux littéraux séparent les colonnes par ; et les lignes par | : le {1,2;3,4} d’Excel devient {1;2|3;4}. Avant v2.384.55, HotXLS produisait {1;2;3;4}, une seule ligne de quatre valeurs

La virgule est la partie dure, parce qu’un seul caractère Excel porte trois sens. Depuis v2.384.55, l’écrivain HotXLS suit une pile de parenthèses pendant la traduction : un ( juste 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 à l’intérieur de {} sont des séparateurs de colonnes de tableau. Avec cela et le correctif d’espace de noms, LibreOffice 26.2 a évalué correctement les huit formules sondes de tableaux et d’unions, INDEX et AREAS sur unions comprises

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. Dans l’écrivain actuel, ce chemin inclut les références qualifiées par feuille telles que Sheet2!A1 et les références structurées de tableaux. 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’écrivain. Si une formule dont vos consommateurs dépendent sort avec le préfixe msoxl:, ouvrez le fichier dans les deux applications avant de livrer

Pourquoi Excel ne voit-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

Avant v2.384.69, HotXLS n’écrivait que le calcext, si bien qu’un fichier ODS avec un surlignage parfaitement bon s’ouvrait dans Excel sans aucune règle de valeur ni règle de formule. HotXLS écrit désormais les deux formes. La moitié style:map utilise la grammaire de condition du schéma OpenDocument (ODF 1.3 Partie 3), avec les épellations 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>

La contrainte de style:map, c’est qu’il vit sur les styles de cellules, donc il est par cellule. Chaque cellule de la plage de la règle doit porter un style tenant la map, cellules vides comprises, sinon la règle ne couvre tout simplement pas cette cellule dans Excel. HotXLS copie le style de formatage existant de chaque cellule, 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 au formatage identique ne produit toujours qu’un style. L’écrivain étend aussi la table écrite à la plage de la règle, ce qui veut dire que les lignes de queue vides à l’intérieur d’une règle sont émises plutôt que jetées. Depuis v2.384.69, le styles.xml porte aussi un style de cellule Default vide, donc 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 quand 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 quand elle est épelée formula-is(...). Ces deux points ont coûté chacun une version à HotXLS, parce que les mauvaises épellations produisent une règle qui s’importe sans erreur puis correspond aux mauvaises cellules

La première erreur était 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 importait chaque règle de valeur comme « égal à 0 ». La seconde était de mettre is-true-formula(...), l’épellation style:map, dans une condition calcext, que LibreOffice importait également comme comparaison de valeur de cellule avec 0. Le correctif de formule est sorti en v2.384.66 et celui de valeur en v2.384.69 :

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

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, y compris les ODS écrits par HotXLS avant v2.384.69, passent toujours par l’analyse historique. Les conditions de formule sont reconnues aussi bien formula-is(...) que is-true-formula(...)
• L’épellation style:map d’Excel. Excel préfixe les conditions de 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 deux la map des cellules vides sur le style par défaut de la colonne plutôt que sur une cellule, donc le lecteur résout les styles par défaut de colonne des cellules répétées avant de collecter les maps
• Reconstruction de régions. Les maps sont collectées par cellule, donc après lecture d’une feuille le lecteur refusionne en plages les cellules qui partagent la même condition et la même cellule de base, 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

Le correctif v2.384.72 concerne les styles de nombres, pas les règles. Excel 16 et LibreOffice 26.2 écrivent tous 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"/>. Le lecteur HotXLS traitait le compte manquant comme deux décimales fixes, si bien que chaque valeur du style Default s’importait avec 0.00 et 1.5 s’affichait 1.50. Depuis v2.384.72, un élément nombre simple sans décimales, sans décimales minimales, sans groupement et au plus un chiffre entier correspond à General, et un General seul laisse la cellule sans aucun format de nombre. Le texte autour est conservé, comme dans General" kg", et les nombres groupés gardent la correspondance précédente parce qu’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

Quelles sont les limites de l’interopérabilité des formats conditionnels ODS de HotXLS ?

L’approche à double balisage couvre les règles de comparaison de valeur et les règles de formule, et s’arrête là. Tout le reste est à sens unique ou pas écrit du tout :

• Les échelles de couleurs et barres de données ne sont écrites que comme éléments calcext, donc LibreOffice les affiche et Excel non
• Les autres sortes de règles, telles que jeux d’icônes, règles de texte, top-N, au-dessus-de-la-moyenne et doublons, n’ont pas de sortie ODS dans l’écrivain actuel. Une règle de texte peut généralement se reformuler en règle de formule, par exemple ISNUMBER(SEARCH("late",B2)) sur B2:B200, qui atteint alors les deux applications
• Les règles colonne entière et ligne entière telles que C:C ne se posent que 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, pas en décalant depuis la cellule de base déclarée
• Règles chevauchantes venant 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 le seul style:map, ce qui est une raison de plus pour le lecteur de préférer calcext quand les deux existent

La limite de processus compte plus que toutes celles-ci. Les défauts derrière ces versions passaient des allers-retours qui écrivaient de l’ODS et le relisaient avec HotXLS, et certains seraient aussi passés à une vérification manuelle dans la mauvaise application : les formules colonne entière marchaient dans LibreOffice pendant qu’Excel affichait #NAME?, et à partir de v2.384.66 les règles de formule marchaient dans LibreOffice pendant qu’Excel n’affichait encore aucune règle du tout jusqu’à v2.384.69. Si l’interopérabilité ODS est une exigence, le test d’acceptation, c’est ouvrir le fichier dans Excel et dans LibreOffice et comparer ce que chacun affiche. La même discipline vaut pour les styles vers lesquels pointent les règles ; l’article HotXLS sur le formatage conditionnel et les styles couvre comment les styles de surlignage sont définis côté classeur

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

• Déclarez xmlns:of et xmlns:msoxl sur la racine du content.xml, sinon LibreOffice affiche #VALUE! pour chaque formule (HotXLS depuis v2.384.56)
• Écrivez les références comme [.A1], gardez chaque $, et écrivez colonnes et lignes entières comme [.A:.A] et [.1:.1] (depuis v2.384.55 et v2.384.65)
• Utilisez ; pour les arguments, ~ pour les unions de références, et | entre les lignes de tableaux littéraux
• Écrivez chaque règle de valeur ou de formule en <style:map> sur le style de chaque cellule couverte pour Excel, et en condition calcext pour LibreOffice (depuis v2.384.69)
• En calcext, mettez l’opérateur dans la valeur (>3, between(1,10)) et épelez les règles de formule formula-is(...) avec une cellule de base (depuis v2.384.66 et v2.384.69)
• Attendez-vous à un style de nombre General sans number:decimal-places à l’import ; HotXLS le lit comme General depuis v2.384.72
• 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