Noms définis et formules inter-feuilles en Delphi (HotXLS)

Un nom défini est une étiquette qui tient lieu de constante, de plage de cellules ou d'expression de formule, rangée une seule fois dans le classeur et référencée symboliquement partout où elle sert. Écrivez TaxRate dans une formule et le moteur le résout vers ce que contient la définition du nom, que ce soit le littéral 0.08 ou la plage Data!$A$2:$D$100. Une référence inter-feuilles est l'idée orthogonale : Data!D2 atteint une cellule d'une autre feuille en qualifiant l'adresse par un nom de feuille. Réunissez les deux et une feuille de synthèse peut totaliser une feuille de détail par un nom qui ne mentionne jamais d'adresse littérale, ce qui est exactement ce que vous voulez dans un classeur assemblé par un générateur et audité plus tard par un comptable

HotXLS, la bibliothèque Delphi native de losLab pour les fichiers XLS et XLSX, expose la table des noms des deux formats avec un accès en création, en recherche et en suppression, ainsi qu'un moteur de formules qui résout les noms et les références inter-feuilles dans le processus. Les deux formats conservent des hiérarchies de classes distinctes, et les différences entre leurs API de noms sont ce qui fait trébucher le code porté de l'un vers l'autre

Deux magasins de noms qui ne partagent pas la même interface

Côté XLS, TXLSWorkbook.GetNames renvoie une collection IXLSNames dont la surcharge Add(Name, RefersTo, Visible) écrit un nom dans la table de noms BIFF. Les entrées individuelles reviennent sous forme d'objets IXLSName portant Name, RefersTo, une plage résolue RefersToRange et une méthode Delete. Côté XLSX, TXLSXWorkbook.DefinedNames est une collection TXLSXDefinedNames dotée de Add, FindByName et DeleteByName

Les conventions de recherche divergent d'une manière qui se révèle lors du portage plutôt qu'à la compilation. La propriété Item par défaut de la collection XLS accepte un Variant, si bien que Names[0] comme Names['TaxRate'] se résolvent sur elle. La collection XLSX n'a pas de propriété par défaut ; vous appelez FindByName('TaxRate'), qui renvoie nil quand le nom est absent. Le code écrit pour une façade ne compile sur l'autre que par accident, et la panne se manifeste plutôt par un accès nil à l'exécution que par un soulignement rouge dans l'IDE

La portée est la première décision, pas un drapeau ajouté après coup

Un nom défini est soit de portée classeur, visible par les formules de toutes les feuilles, soit de portée feuille, visible seulement par les formules de la feuille qui le possède. Dans l'API XLSX, la distinction tient à un unique paramètre facultatif. DefinedNames.Add(AName, AFormula) crée un nom au niveau du classeur, tandis que Add(AName, AFormula, ASheetIndex) le lie à une seule feuille. À la relecture, TXLSXDefinedName.SheetIndex renvoie -1 pour la portée classeur et l'index de feuille en base 0 sinon

La portée fait aussi office de politique de collision, et c'est la raison de la trancher avant d'écrire le premier nom. Excel autorise un Total local sur chaque feuille en plus d'un Total au niveau du classeur, et une formule d'une feuille donnée résout d'abord le nom local. Les classeurs générés devraient s'appuyer là-dessus délibérément. Les hypothèses métier que plusieurs feuilles consomment, comme les taux de taxe, les taux de change et la période de reporting, ont leur place à la portée classeur. Les plages auxiliaires que seules les formules d'une feuille référencent sont plus sûres en portée feuille, où rien ne peut les masquer et où elles ne masquent rien

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... remplir Data!A2:D100 avec les lignes de détail ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // portée classeur, une constante
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // portée classeur, une plage
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // limité à la feuille d'index 1

    // les formules XLSX ne prennent pas de '=' initial
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Un nom défini n'a pas besoin de pointer vers une plage. TaxRate ci-dessus désigne la constante nue 0.08, et c'est la façon la plus propre de publier une hypothèse métier. Elle apparaît une fois dans le Gestionnaire de noms d'Excel, chaque formule la référence symboliquement, et le changement de taux du trimestre suivant est une modification d'une ligne dans le générateur au lieu d'une recherche à travers quatorze chaînes de formules assemblées

Le signe égal qui ne va que d'un seul côté

Le canal de saisie des formules est l'endroit où le code porté casse le plus souvent, car les deux façades ne s'accordent pas sur le signe égal. Les cellules XLS reçoivent les formules par Value avec un = initial. Les cellules XLSX ont une propriété Formula dédiée qui prend l'expression sans ce préfixe. Écrivez '=SUM(A1:A10)' dans TXLSXCell.Formula et le signe égal devient une partie du texte de l'expression stockée au lieu d'un marqueur, et le fichier ne se comportera pas comme la même chaîne le faisait côté XLS

var
  Book: IXLSWorkbook;   // compté par interface : ne pas appeler Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // on suppose qu'une feuille nommée 'Data' contient déjà les lignes de détail
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = masqué dans le Gestionnaire de noms

  // les formules XLS passent par Value, avec le préfixe '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Cet extrait montre deux autres bizarreries côté XLS. La collection de feuilles est en base 1, donc Sheets[1] est la première feuille, face au Sheets[0] en base 0 du XLSX. Et le troisième paramètre de 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 d'un générateur que les utilisateurs finaux ne devraient jamais modifier ni supprimer par accident

Références inter-feuilles, et ce qui arrive quand les lignes bougent

Les deux moteurs de formules acceptent la syntaxe inter-feuilles standard. Les noms de feuille simples se qualifient directement comme Data!A1 ; un nom comportant des espaces ou de la ponctuation demande des apostrophes simples, comme dans 'Sheet With Space'!A1. À l'intérieur du texte RefersTo d'un nom, optez pour des références absolues telles que Data!$A$2:$D$100 à peu près à chaque fois. Une référence relative à l'intérieur d'un nom défini se résout relativement à la cellule qui l'utilise, ce qui est une fonctionnalité Excel délibérée et une source fiable de confusion quand elle se déclenche par accident

Les modifications structurelles sont là où la comptabilité inter-feuilles gagne son salaire, et le côté XLSX garde les noms cohérents à travers elles. InsertRows et DeleteRows décalent les plages des noms définis en même temps que les cellules, les fusions, les liens hypertexte et les ancrages de graphiques, si bien qu'un nom pointant vers Data!$A$2:$D$100 couvre encore le bloc de données après que le générateur a ouvert un espace au-dessus. Les formules viennent avec une réserve documentée : l'insertion de lignes n'ajuste que les références qui visent la feuille en cours de modification. Une formule de Summary qui référence Data!D2:D100 est réécrite quand des lignes entrent dans Data, ce qui est le cas que vous voulez généralement. Vérifiez-le plutôt que de le supposer, car le moteur vous le dira à peu de frais :

// le moteur de calcul résout les noms et les références inter-feuilles dans le processus
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate évalue une expression arbitraire sur l'état courant du classeur sans rien enregistrer, ce qui en fait la primitive d'assertion naturelle pour les tests de générateur. Calculez l'agrégat attendu à partir des données sources en Pascal, évaluez la formule du classeur lui-même, et comparez les deux. L'article sur le moteur de formules détaille ce que le moteur évalue, à quel moment, et comment l'étendre avec des fonctions personnalisées

Les noms _xlnm qui appartiennent à la couche des propriétés

Ouvrez la table des noms d'un fichier généré dans un inspecteur bas niveau et vous y trouverez des entrées que vous n'avez jamais écrites : _xlnm.Print_Area, _xlnm.Print_Titles et leurs proches. C'est ainsi que OOXML (ECMA-376 / ISO 29500) range les zones d'impression et les lignes de titre répétées, sous forme de noms définis à identifiants réservés. HotXLS les gère par des propriétés de feuille de calcul dédiées, si bien que renseigner PrintArea ou PrintTitleRows écrit l'entrée _xlnm.* correspondante à votre place

Le piège consiste à entrer à la main dans cet espace de noms réservé. Ajoutez une entrée _xlnm.Print_Area par DefinedNames.Add tout en renseignant aussi la propriété PrintArea et le classeur porte deux définitions contradictoires pour un même nom réservé, un état qu'Excel résout d'une manière sur laquelle aucun produit ne devrait compter. Considérez tout identifiant qui commence par _xlnm. comme appartenant à la couche des propriétés. Pour inspecter la configuration d'impression, lisez les propriétés, pas la table des noms. L'article sur la protection et la mise en page traite les propriétés de zone d'impression dans leur contexte

Deux limites à connaître avant d'arrêter une conception

Les noms définis ne suivent pas à travers la passerelle de commodité XLS vers XLSX. SaveXLSWorkbookAsXLSX copie le contenu des cellules et la mise en forme de base, et la table des noms ne figure pas sur sa liste de copie documentée, si bien qu'un classeur qui dépendait de ses noms les perd à la traversée. Recréez les noms par DefinedNames.Add après la conversion. Cette étape est moins pénible qu'elle en a l'air, car elle vous donne un moment pour normaliser leurs portées au lieu de reprendre ce que le fichier XLS contenait par hasard

L'autre limite est la dérive entre les chaînes de formules et les noms de feuilles. Excel réécrit les références de feuille dans les formules et les noms lors d'un renommage interactif, si bien que les fichiers modifiés par un utilisateur dans Excel restent cohérents tout seuls. L'exposition est du côté du générateur : quand du code Pascal assemble des chaînes de formules à partir d'un littéral de nom de feuille, renommer la feuille à un endroit et l'oublier à l'autre produit une référence vers une feuille qui n'existe plus. Gardez le nom de feuille dans une seule constante Delphi et alimentez-en à la fois Sheets.Add et votre assemblage de formules, et les deux ne pourront jamais diverger. C'est le même instinct qui plaide pour nommer les cellules de sortie d'un rapport plutôt que de coder les adresses en dur : un modèle dont la cellule de total est nommée continue de fonctionner après qu'un concepteur a inséré trois lignes au-dessus, tandis qu'un générateur qui écrit dans un B17 littéral pose discrètement son nombre au mauvais endroit. L'article sur la génération de rapports par modèle bâtit exactement sur ce motif

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 le HotXLS Delphi Component