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 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 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. Unof:=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:$Ben 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
~; leAREAS((A1,B2))d'Excel devientAREAS(([.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
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
É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()>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])>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=">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])>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:operatorpassent quand même par l'analyse historique. Les conditions de formule sont reconnues commeformula-is(...)ouis-true-formula(...) - La graphie style:map d'Excel. Excel préfixe les conditions avec
of:, comme dansof: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))surB2: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:mapseul, 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:ofetxmlns:msoxlsur la racine decontent.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 formuleformula-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