La demande de changement tenait en une ligne : passer le taux de taxe de 8 % à 8,5 %. Le générateur de classeur, lui, avait l'ancien taux codé en dur comme littéral 0.08 dans quatorze chaînes de formule assemblées par du code Pascal, plus deux occurrences dans un modèle que plus personne ne se souvenait avoir modifié. Les constantes et adresses de cellules codées en dur dans des formules générées sont l'équivalent tableur des nombres magiques, sauf que ces nombres magiques sont livrés aux clients et audités par leurs comptables. Les noms définis existent précisément pour supprimer ce mode de panne, et HotXLS — la bibliothèque native Delphi de losLab pour les fichiers XLS et XLSX — expose la table des noms des deux formats avec accès complet en création, recherche et suppression.
Cet article montre comment fonctionnent les deux magasins de noms, pourquoi le scope est la première décision et non un détail tardif, comment les formules interfeuilles interagissent avec les noms, et quels noms intégrés vous devez laisser tranquilles.
Deux magasins de noms, un même concept
HotXLS possède des hiérarchies de classes séparées pour les deux formats de fichier, et chacune transporte sa propre collection de noms. Côté XLS, TXLSWorkbook.GetNames renvoie une collection IXLSNames dont la surcharge Add(Name, RefersTo, Visible) crée un nom dans la table BIFF ; les entrées individuelles reviennent comme objets IXLSName avec Name, RefersTo, une RefersToRange résolue et une méthode Delete. Côté XLSX, TXLSXWorkbook.DefinedNames est une collection TXLSXDefinedNames avec Add, FindByName et DeleteByName.
Les conventions de recherche diffèrent d'une manière qui mord pendant les portages : la propriété Item par défaut de la collection XLS accepte un Variant, donc Names[0] et Names['TaxRate'] se résolvent tous deux, tandis que la collection XLSX attend un appel explicite FindByName('TaxRate') qui renvoie nil lorsque le nom est absent. Le code qui suppose une convention ne compile contre l'autre façade que par accident.
Le scope décide qui peut voir le nom
Un nom défini est soit au scope classeur — visible par les formules de toutes les feuilles — soit au scope feuille, visible seulement par les formules de sa feuille propriétaire. Dans l'API XLSX, la distinction est un seul paramètre optionnel : DefinedNames.Add(AName, AFormula) crée un nom au niveau classeur, tandis que Add(AName, AFormula, ASheetIndex) le lie à une feuille. À la relecture, TXLSXDefinedName.SheetIndex renvoie -1 pour le scope classeur et l'index de feuille 0-based sinon.
Le scope est aussi votre stratégie de collision. Excel autorise un Total local à chaque feuille plus un Total au niveau classeur, et une formule sur une feuille donnée résout d'abord le local. Les classeurs générés doivent l'exploiter volontairement : les hypothèses métier consommées par plusieurs feuilles (taux de taxe, taux de change, période de reporting) appartiennent au scope classeur, tandis que les plages auxiliaires référencées uniquement par les formules d'une feuille sont plus sûres en scope feuille, où elles ne peuvent ni masquer autre chose ni être masquées.
var
Book: TXLSXWorkbook;
Data, Summary: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Summary := Book.Sheets.Add('Summary');
// ... fill Data!A2:D100 with detail rows ...
Book.DefinedNames.Add('TaxRate', '0.08'); // workbook scope, a constant
Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100'); // workbook scope, a range
Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1); // scoped to sheet index 1 only
// XLSX formulas take no leading '='
Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
Book.SaveAs('model.xlsx');
finally
Book.Free;
end;
end;Notez qu'un nom défini n'a pas à pointer vers une plage. TaxRate ci-dessus réfère à la constante 0.08, ce qui est la manière la plus propre de publier une hypothèse métier : elle apparaît une seule fois dans le gestionnaire de noms d'Excel, chaque formule la référence symboliquement, et le changement de taux du trimestre suivant devient une modification d'une ligne dans le générateur.
Le signe égal qui n'est pas là
Le bug de portage le plus courant entre les deux façades est le canal de saisie de formule. Les cellules XLS reçoivent les formules via Value avec un = initial ; les cellules XLSX ont une propriété dédiée Formula qui prend l'expression sans le préfixe. Écrivez '=SUM(A1:A10)' dans TXLSXCell.Formula et le signe égal devient une partie du texte d'expression stocké plutôt qu'un marqueur — le fichier ne se comportera pas comme la même chaîne côté XLS.
var
Book: IXLSWorkbook; // interface-counted: do not Free
Names: IXLSNames;
begin
Book := TXLSWorkbook.Create;
// assume a sheet named 'Data' already holds the detail rows
Names := Book.GetNames;
Names.Add('TaxRate', '0.08');
Names.Add('Helper', 'Data!$A$2:$A$100', False); // False = hidden from the Name Manager
// XLS formulas go through Value, with the '=' prefix
Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
Book.SaveAs('model.xls');
end;Deux autres détails côté XLS sont visibles dans cet extrait : la collection de feuilles est 1-based (Sheets[1] est la première feuille, contrairement au Sheets[0] 0-based de XLSX), et le troisième paramètre Add crée un nom masqué — présent dans le fichier et utilisable par les formules, mais invisible dans le gestionnaire de noms d'Excel. Les noms masqués sont le bon véhicule pour la plomberie interne au générateur que les utilisateurs finaux ne doivent ni modifier ni supprimer par accident.
Références interfeuilles qui survivent aux modifications
Les deux moteurs de formules acceptent la syntaxe interfeuille standard : Data!A1 pour les noms de feuilles simples et 'Sheet With Space'!A1 avec apostrophes lorsque le nom contient des espaces ou de la ponctuation. Dans le texte RefersTo d'un nom, les références absolues (Data!$A$2:$D$100) sont presque toujours ce qu'il vous faut — une référence relative dans un nom défini se résout par rapport à la cellule qui l'utilise, fonctionnalité Excel volontaire et source complète de confusion lorsqu'elle arrive sans intention.
Les opérations d'édition structurelle gardent les noms cohérents côté XLSX : InsertRows et DeleteRows déplacent les plages de noms définis avec les cellules, fusions, liens hypertexte et ancres de graphiques ; un nom pointant vers Data!$A$2:$D$100 couvre donc encore le bloc de données après que le générateur a ouvert un espace au-dessus. La réserve documentée du moteur s'applique cependant aux formules : l'insertion de lignes ajuste seulement les références qui ciblent la feuille en cours d'édition. Une formule Summary qui référence Data!D2:D100 est réécrite lorsque des lignes sont insérées dans Data — mais prévoyez quand même l'étape de vérification, car le moyen bon marché de savoir consiste à interroger le moteur :
// the calculation engine resolves names and cross-sheet references in-process
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
Log('net total checks out: ' + FloatToStr(V));Calculate évalue une expression arbitraire contre l'état courant du classeur sans rien enregistrer, ce qui en fait la primitive naturelle d'assertion pour les tests de générateur : calculez l'agrégat attendu depuis les données source en Pascal, évaluez la formule du classeur lui-même, puis comparez. L'article sur le moteur de formules couvre ce que le moteur évalue, quand, et comment l'étendre avec des fonctions personnalisées.
Les noms _xlnm à ne pas redéfinir
Ouvrez la table des noms d'un fichier généré dans un inspecteur bas niveau et vous trouverez des entrées que vous n'avez jamais créées : _xlnm.Print_Area, _xlnm.Print_Titles et leurs proches. C'est ainsi que OOXML (ECMA-376 / ISO 29500) représente les zones d'impression et les lignes de titre répétées — ce sont des noms définis avec des identifiants réservés. HotXLS les gère via les propriétés dédiées de feuille : définir PrintArea ou PrintTitleRows écrit pour vous l'entrée _xlnm.* correspondante.
Le piège consiste à ajouter manuellement une entrée _xlnm.Print_Area via DefinedNames.Add tout en définissant aussi la propriété PrintArea : le classeur se retrouve avec des définitions conflictuelles pour un nom réservé, et le comportement d'Excel sur ce type de fichier n'est pas une base pour un produit. Traitez tout identifiant commençant par _xlnm. comme appartenant à la couche de propriétés ; si vous devez inspecter la mise en page d'impression, lisez les propriétés, pas la table des noms. L'article sur la protection et la mise en page couvre les propriétés de zone d'impression dans leur contexte.
Questions de revue de code sur la table des noms
Les noms définis survivent-ils à la conversion XLS vers XLSX ?
Pas via le pont de commodité. SaveXLSWorkbookAsXLSX copie le contenu des cellules et le formatage de base, et la table des noms ne figure pas dans sa liste documentée de copie — recréez les noms via DefinedNames.Add après conversion, ce qui vous donne aussi l'occasion de normaliser leurs scopes.
Que deviennent les formules quand un utilisateur renomme une feuille dans Excel ?
Excel réécrit les références de feuille dans les formules et les noms pendant un renommage interactif, donc les fichiers édités dans Excel restent cohérents. Le risque se situe dans votre générateur : si le code Pascal construit des chaînes de formule depuis une constante de nom de feuille, renommer la feuille à un endroit et pas l'autre produit une référence vers une feuille qui n'existe plus. Garder le nom de feuille dans une seule constante Delphi utilisée à la fois pour Sheets.Add et l'assemblage de formules supprime cette dérive.
Les modèles de rapport doivent-ils utiliser des noms ou des adresses fixes ?
Des noms, pour tout ce que le code de remplissage doit retrouver. Un modèle dont la cellule de total est nommée continue de fonctionner après qu'un concepteur insère trois lignes au-dessus ; un générateur qui écrit dans un B17 codé en dur non. L'article sur la génération de rapports à partir de modèles développe exactement ce modèle.
L'API complète des noms définis pour les deux formats, avec la référence du moteur de formules, est livrée avec HotXLS Component.