La méthode AddCopy de HotXLS copie une feuille de calcul d'un classeur Excel vers un autre en décompilant chaque formule de cette feuille en texte de style A1 et en recompilant ce texte à l'intérieur du classeur de destination, plutôt qu'en copiant directement l'arbre de formule compilé, car les références de série de graphique, les indices de police de texte enrichi, et la numérotation des liens externes sont tous assignés indépendamment à l'intérieur de chaque fichier de classeur
L'échec se manifeste exactement dans le classeur auquel on s'attendrait : une tâche de fin de mois qui extrait une feuille du rapport de chaque agence et l'ajoute à un fichier de synthèse. Ouvrez le résultat et un graphique de sous-total trace les chiffres d'une agence complètement différente, une note qui était en gras et rouge dans la source est revenue à du texte noir plat, et une formule qui autrefois puisait un taux de taxe dans un classeur de référence compagnon affiche désormais un nombre figé que personne ne peut expliquer. Rien ne lève d'exception ici — le fichier s'ouvre, les chiffres semblent plausibles, et les dégâts restent là jusqu'à ce que quelqu'un remarque un graphique au mauvais titre assis juste à côté
Pourquoi AddCopy ne peut-elle pas simplement copier l'arbre de formule compilé ?
AddCopy ne peut pas déplacer l'arbre de formule compilé sans le modifier, car une formule BIFF compilée n'est pas du texte autonome — c'est une séquence de jetons, et plusieurs de ces jetons sont de petits entiers qui ne se résolvent correctement qu'à l'intérieur du classeur qui les a produits. Une référence 3D telle que Sheet2!A1:A10 ne porte pas le nom littéral Sheet2 une fois compilée ; elle porte un champ que la spécification BIFF appelle ixti (HotXLS garde la même valeur dans son propre arbre compilé sous le nom de champ FExternID), un index dans la table EXTERNSHEET privée de ce classeur, numéroté selon l'ordre dans lequel ce classeur particulier a enregistré ses feuilles et livres externes. Déplacez le jeton sans le modifier dans un classeur dont la table EXTERNSHEET a été construite dans un ordre différent et l'index 3 ne signifie plus Sheet2 — il signifie quelle que soit la feuille qui occupe l'emplacement 3 là-bas, et Excel n'a aucun moyen de signaler l'erreur, car pour ce qui est du format de fichier, la formule est parfaitement bien formée. C'est exactement l'échec que TXLSWorksheets.AddCopy existe pour éviter : appelée depuis la propre collection de feuilles de l'un ou l'autre classeur en code Delphi ou C++Builder, elle copie une feuille de calcul — valeurs de cellules, formats, formules, graphiques, commentaires, fusions, mise en page, et plus — depuis un classeur source qui peut être ou non celui sur lequel vous l'appelez, et ajoute le résultat à la destination sous un nom de votre choix ou une copie désambiguïsée de l'original
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;La solution : décompiler en texte, recompiler dans la destination
HotXLS résout le problème d'indexation en ne laissant jamais l'arbre compilé lui-même traverser la frontière du classeur. Pour chaque cellule de formule lors d'une copie inter-classeurs, AddCopy décompile la formule source dans le même texte de style A1 qu'un utilisateur verrait dans la barre de formule d'Excel, puis transmet ce texte au classeur de destination, qui le réanalyse en un arbre en utilisant ses propres tables depuis zéro — une référence qualifiée par feuille comme Data!D2:D100 n'est qu'une chaîne à ce stade, et une chaîne signifie la même chose dans n'importe quel classeur, si bien que si la destination possède déjà une feuille nommée Data, la référence se résout correctement sans aucune traduction d'index, car il n'y avait jamais d'index brut en transit à traduire. HotXLS ne paie ce coût d'aller-retour que lorsque c'est nécessaire : copier une feuille à l'intérieur du même classeur emprunte un chemin moins coûteux où l'arbre compilé est simplement dupliqué en mémoire, puisque chaque index à l'intérieur est déjà valide là où il reste, et le détour par le texte ne s'exécute qu'une fois qu'AddCopy détecte que la source et la destination sont réellement des instances de classeur différentes. Il vaut aussi la peine de préciser ce que cette réécriture n'est pas. Elle n'a rien à voir avec le décalage de lignes et de colonnes qui s'exécute quand vous insérez ou supprimez des lignes à l'intérieur d'une seule feuille, que un article compagnon couvre en détail — ce moteur réécrit le texte A1 sur place pour suivre les cellules qui se sont déplacées de quelques lignes vers le haut ou le bas dans un même classeur, tandis que celui-ci s'exécute quand une formule quitte entièrement le classeur qui l'a compilée, où les lignes déplacées ne sont pas le problème et où la numérotation privée au classeur l'est
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Que se passe-t-il si la destination n'a pas encore cette feuille, ou ce nom ?
La recompilation d'AddCopy ne réussit que lorsque le classeur de destination possède déjà tout ce à quoi le texte de formule fait référence, et les deux lacunes qui apparaissent en pratique sont une feuille de même nom qui n'a pas encore été copiée dans ce lot, et un nom défini à portée classeur qui n'a jamais existé du tout dans la destination. HotXLS ne lève pas d'exception quand la recompilation échoue en cours de copie de feuille — l'assignation Value de la cellule stocke silencieusement le texte de formule sous forme de chaîne simple à la place, un mode d'échec délibéré et inspectable plutôt que silencieux, puisqu'une cellule de formule qui affiche de manière inattendue du texte littéral comme =SUM(Q1!B2:B12) au lieu d'un nombre calculé est l'indice que quelque chose en amont dans la copie ne s'est pas résolu. Avant d'abandonner, AddCopy tente une réparation : elle parcourt l'arbre syntaxique de la formule échouée en collectant chaque identifiant de nom défini que la formule touche, et pour chaque nom à portée classeur qui existe dans la source mais pas encore dans la destination, elle copie le nom et recompile le même texte une seconde fois. Les noms à portée feuille se situent en dehors de ce que cette réparation peut corriger, puisqu'un nom visible uniquement par les formules d'une feuille du classeur source n'a pas d'emplacement équivalent vers lequel migrer, et une destination qui possède déjà un nom à l'orthographe identique est laissée intacte plutôt qu'écrasée, dans l'hypothèse qu'un nom que l'appelant a délibérément pré-créé est celui qu'il souhaite voir honoré. À l'intérieur d'un seul classeur, la recherche de nom d'une formule inter-feuille remonte automatiquement de la portée feuille vers la portée classeur, ce qui est le mécanisme que l'article de HotXLS sur les noms définis et les formules inter-feuilles couvre ; franchir une véritable frontière de classeur retire entièrement ce filet de sécurité, et un nom doit être délibérément transporté à travers ou la formule qui en dépend se dégrade en texte
Les références de série de graphique ont besoin de la même correction, mais un chemin de code différent
Une série de graphique HotXLS qui trace une plage de cellules rencontre précisément le même problème de numérotation qu'une formule de cellule ordinaire, car la référence de plage de données d'un graphique est aussi un flux de jetons de formule compilé — la spécification BIFF appelle l'enregistrement qui la porte BRAI ([MS-XLS] section 2.4.51) — mais AddCopy ne peut pas la corriger en réutilisant le chemin de chargement de graphique normal, car ce chemin est exactement ce qui crée le bogue. Lorsqu'un enregistrement de graphique est analysé depuis le disque dans le cours normal de l'ouverture d'un fichier, son arbre de formule est construit en traduisant les octets bruts via quelle que soit l'instance de calculateur effectuant l'analyse ; faites passer les octets BRAI bruts d'un graphique source par le propre chargeur d'enregistrements ordinaire du classeur de destination à la place, et le ixti intégré dans ces octets se résout par rapport à la table EXTERNSHEET de la destination, si bien que la série pointe silencieusement vers quelle que soit la feuille qui occupe cet emplacement là-bas — la même classe d'erreur que copier l'arbre compilé d'une cellule sans le modifier, simplement plus difficile à remarquer car personne ne lit les formules de série de graphique comme on lit les formules de cellule. HotXLS évite ce piège avec un chemin de clonage dédié à la place : TXLSCustomChart.AssignFrom copie textuellement les propres octets d'en-tête non-formule de chaque enregistrement de graphique, puis reconstruit la plage attachée via la même primitive de décompilation-recompilation utilisée pour les cellules ordinaires, si bien que le nouvel arbre est construit contre la table EXTERNSHEET de la destination depuis zéro plutôt que réinterprété contre elle après coup
Le même problème de numérotation, un index de police à la fois
Tous les nombres locaux au classeur à l'intérieur d'un graphique ou d'une cellule de texte enrichi ne sont pas des formules, et un index de police est la même classe de problème en miniature. Les segments de texte enrichi, ainsi que deux autres types d'enregistrement de graphique qui portent une légende ou une police d'axe, stockent une référence de police comme un simple entier brut dans la table de polices propre au classeur propriétaire, et cet index ne signifie rien dans la table d'un classeur différent — il pourrait tout aussi bien pointer vers une police, une taille, ou une couleur complètement différente là-bas. HotXLS résout cela par valeur plutôt que par nombre : il recherche les attributs de police réels à cet index dans la table source, trouve ou crée une entrée correspondante dans la table de polices de la destination, et réécrit l'index stocké pour pointer vers ce nouvel emplacement. Une bizarrerie de format rend la recherche elle-même délicate — l'index sur fichier saute l'emplacement 4, un écart de numérotation que documente [MS-XLS] section 2.5.339, si bien que le code doit décaler l'index vers le bas d'une unité avant de comparer les polices et le décaler à nouveau vers le haut d'une unité avant d'écrire le résultat
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Que se passe-t-il pour une formule qui pointe déjà en dehors du classeur ?
Une formule qui atteint un troisième classeur avant même que vous n'appeliez AddCopy est le seul cas que l'aller-retour par le texte ne peut pas transporter, car le propre décompileur formule-vers-texte de HotXLS ne synthétise délibérément pas de texte entre crochets [Book]Sheet! pour une référence externe, et le compilateur de l'autre côté n'accepte pas non plus cette syntaxe en entrée — ce cas unique passe donc par un second mécanisme qui ne touche jamais au texte du tout. Lorsque la réparation de migration de noms décrite ci-dessus laisse tout de même une cellule sous forme de chaîne, et que le classeur source a un véritable nom de fichier, AddCopy change de stratégie : elle copie en profondeur l'arbre de formule compilé lui-même plutôt que son texte, puis transmet la copie à une passe de rebranchement dédiée, RebindExternRefsInTree, qui la parcourt nœud par nœud. Pour chaque référence de plage qu'elle trouve, cette passe résout l'entrée EXTERNSHEET de la source en une paire de noms de feuille, et enregistre, ou réutilise, une entrée équivalente dans les propres tables de référence externe de la destination, créant un tout nouveau lien de classeur externe si la destination n'a jamais référencé ce fichier source auparavant
C'est ici que le problème de numérotation locale au classeur est le plus littéral, car un jeton de référence externe regroupe trois coordonnées distinctes en un seul champ et chacune d'elles est privée au classeur qui l'a écrit : quel classeur externe, un emplacement dans la propre liste de livres externes de la destination assignée dans quel que soit l'ordre où ce classeur les a enregistrés ; quelle feuille à l'intérieur de la propre liste de feuilles de ce classeur externe, stockée comme un index basé sur 1 limité au livre externe spécifiquement, un domaine de numérotation entièrement différent des propres identifiants de feuille internes de la destination ; et la plage de cellules elle-même, de simples coordonnées de ligne et de colonne qui n'ont besoin d'aucune traduction car elles n'étaient jamais relatives au classeur en premier lieu. Trompez-vous sur l'un des deux premiers et Excel ouvre tout de même le fichier, montre toujours une formule, et l'évalue contre les mauvaises cellules externes sans se plaindre. Un type de nœud met en échec même ce rebranchement au niveau de l'arbre : une référence à un nom défini, un index dans la table de noms privée de son propre classeur exactement comme un index de feuille est privé à son propre EXTERNSHEET, sans réparation équivalente disponible au niveau de l'arbre — au moment où le parcours de rebranchement rencontre une référence de nom n'importe où dans l'arbre, il abandonne la formule entière plutôt que d'en écrire une partiellement correcte. Même quand le rebranchement réussit, la cellule de destination ne montre pas un nombre fraîchement recalculé ; elle montre la valeur que la cellule source détenait déjà au moment de la copie, conservée dans un emplacement en cache de la même façon qu'Excel lui-même met en cache la dernière valeur connue de toute référence externe jusqu'à ce que vous actualisiez explicitement les liens, ce qui est le bon comportement par défaut, puisque recalculer à travers un lien actif vers un autre fichier est exactement le genre d'opération que vous voulez déclencher une fois, délibérément, plutôt qu'à chaque ouverture
Ce que ce choix de conception vous coûte
La mécanique de décompilation-recompilation d'AddCopy n'est pas gratuite, et le coût mérite d'être anticipé avant de scripter une tâche de consolidation volumineuse plutôt qu'après. Copier une feuille à l'intérieur du même classeur emprunte le chemin bon marché, une simple duplication en mémoire de l'arbre compilé, car chaque index à l'intérieur est déjà valide dans le classeur où il reste ; une copie inter-classeurs paie pour une véritable analyse à chaque cellule de formule à la place, décompiler en texte puis compiler ce texte à nouveau à partir de rien, et bien que la différence ne vaille pas la peine d'être mesurée sur une feuille comportant quelques dizaines de formules, un classeur source avec des dizaines de milliers de cellules de formule, copié comme une feuille parmi des dizaines dans une tâche par lot, devrait s'attendre à ce que la recompilation domine le temps d'exécution plutôt que les E/S de fichier autour. L'ordre de copie compte pour une seconde raison au-delà de la vitesse : une formule qui référence une feuille qu'AddCopy n'a pas encore atteinte dans ce lot échoue sa recompilation pour la même raison qu'une formule référençant une feuille véritablement inexistante, si bien qu'une tâche qui copie la feuille B avant la feuille A dont dépend sa formule verra cette formule se dégrader exactement comme décrit plus haut, texte sous forme de chaîne ou repli de lien externe pointant droit vers le fichier source d'où elle vient tout juste. Et parce que chaque classeur source dans un lot de consolidation est habituellement rédigé indépendamment, il vaut la peine de tester explicitement le seul mode d'échec dont aucun fichier source unique n'aurait jamais pu vous avertir — cinq classeurs d'agence totalisant chacun les chiffres d'une agence pair peuvent se combiner en une véritable référence circulaire à l'intérieur du classeur de synthèse sans qu'aucun fichier source individuel n'en contienne jamais une, un cycle qui n'existe qu'une fois que chaque feuille a atterri au même endroit et que le recalcul s'exécute sur l'ensemble combiné
La copie de feuille de calcul inter-classeurs fait partie du comportement standard d'AddCopy dans le composant Excel Delphi HotXLS pour Delphi et C++Builder ; la page produit porte la référence complète de l'API de feuille de calcul et de classeur, y compris le comportement de graphique, de texte enrichi, et de référence externe décrit ici