HotXLS peut évaluer une formule telle que =[Rates.xlsx]FX!B4*C10 par rapport à un autre classeur ouvert en mémoire, et non par rapport à une valeur mise en cache et potentiellement obsolète. Vous enregistrez le second classeur par son nom avec RegisterExternalWorkbook, et chaque recalcul lit les cellules référencées directement depuis cet objet. Désenregistrez-le et l'évaluation revient au cache de liens externes que le fichier porte déjà
Cela comble une lacune précise et frustrante. Les classeurs Excel se référencent couramment entre eux : une table de taux, un plan comptable, une liste de prix maintenue par une autre équipe. Quand un tel fichier est ouvert par une bibliothèque plutôt que par Excel, ces références se résolvent normalement vers les valeurs mises en cache lors du dernier recalcul par Excel, qui peuvent dater de plusieurs mois, et aucun recalcul ne corrige cela puisque les données ne sont tout simplement pas là
À quoi ressemble une référence inter-classeurs à l'intérieur du fichier ?
Une référence externe n'est pas stockée comme le texte que vous voyez dans la barre de formule. Le classeur conserve une table de classeurs de support, chaque jeton de formule pointe vers un index dans cette table, et une partie séparée de valeurs mises en cache conserve le dernier résultat connu pour chaque cellule référencée. C'est pourquoi un fichier ouvert sans ses compagnons affiche quand même des nombres
L'analyse de la forme textuelle a ses propres subtilités. L'analyseur lexical traite un nom de classeur entre crochets comme faisant partie d'un identifiant unique, si bien que [Book.xlsx]Sheet!A1 devient un seul jeton plutôt qu'un crochet non équilibré suivi d'une référence de feuille. Les noms sont ensuite normalisés pour la recherche : la mise entre guillemets, les préfixes de chemin, l'extension de fichier et la forme d'index numérique se réduisent tous à un nom de base en majuscules, si bien que 'C:\Rates\[Rates.xlsx]FX'!B4 et [rates.xlsx]FX!B4 se résolvent vers le même classeur enregistré
Enregistrer un classeur et recalculer
L'enregistrement se fait par nom, et le nom est celui qui apparaît dans les formules du classeur qui effectue la référence :
uses
lxHandleX;
var
Main, Rates: TXLSXWorkbook;
begin
Main := TXLSXWorkbook.Create;
Rates := TXLSXWorkbook.Create;
try
if (Main.Open('valuation.xlsx') <> 1) or
(Rates.Open('rates.xlsx') <> 1) then
Exit;
// Les formules de valuation.xlsx qui indiquent [Rates.xlsx]... lisent désormais
// depuis l'objet en direct plutôt que depuis les valeurs externes mises en cache
Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
Main.Recalculate;
Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
// Changer la source et recalculer à nouveau : la nouvelle valeur se propage
Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
Main.Recalculate;
Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
finally
Main.UnregisterExternalWorkbook('Rates.xlsx');
Rates.Free;
Main.Free;
end;
end;L'enregistrement conserve une référence faible. HotXLS ne prend pas possession du classeur enregistré et ne le libérera pas, ce qui signifie que la durée de vie vous appartient et que l'ordre compte : désenregistrez avant de libérer le classeur source, ou au minimum ne recalculez pas après l'avoir libéré
Que se passe-t-il quand un classeur n'est pas enregistré ?
L'évaluation revient au cache de liens externes stocké dans le fichier, ce qui est exactement le comportement d'un classeur ouvert sans ses compagnons. Ce repli est la raison pour laquelle désenregistrer est sûr plutôt que destructeur
Le comportement après désenregistrement mérite d'être énoncé précisément, car il surprend les gens. Quand une référence ne peut être résolue ni depuis un classeur enregistré ni depuis le cache, l'évaluation produit une erreur, et cette erreur n'est pas réécrite dans la cellule. La cellule conserve sa dernière valeur calculée. Réenregistrez la source et recalculez, et la valeur se met à jour à nouveau
Cette règle de préservation en cas d'échec est délibérée. L'alternative, écrire une valeur d'erreur dans chaque cellule dépendante dès qu'une source devient indisponible, transformerait une table de taux temporairement manquante en un classeur rempli de marqueurs d'erreur qu'un recalcul réussi ultérieur devrait réparer. Conserver la dernière bonne valeur signifie qu'un pipeline interrompu laisse un document qui reste sensé à la lecture
La propagation de la saleté s'arrête à la frontière du classeur
Éditer une cellule dans la source enregistrée ne marque pas comme sales les cellules dépendantes du classeur principal. Le recalcul incrémental suit les dépendances à l'intérieur d'un seul classeur ; il ne surveille pas au-delà de la frontière. Excel se comporte de la même façon, ce qui explique pourquoi ses références externes se mettent à jour à l'ouverture ou lors d'un rafraîchissement explicite plutôt que continuellement
La conséquence pratique est que changer une valeur source exige un recalcul explicite du classeur qui effectue la référence, comme dans l'exemple ci-dessus. Si votre application édite les deux classeurs dans une boucle, recalculez une seule fois à la fin plutôt qu'après chaque édition ; le moteur incrémental décrit dans le recalcul incrémental et le graphe de dépendances effectue alors le travail minimal à l'intérieur de chaque classeur
Trois cas d'usage où cette fonctionnalité s'applique
La consolidation est le cas évident. Un classeur parent référence douze fichiers mensuels ; enregistrez les douze, recalculez une fois, et lisez les chiffres consolidés sans jamais ouvrir Excel. Le nom d'enregistrement de chacun doit correspondre à ce que les formules du parent disent réellement, ce qu'il vaut la peine de vérifier dans le texte des formules plutôt que de supposer qu'il correspond au nom du fichier
L'analyse de scénarios est le deuxième cas. Enregistrez une source, écrivez-y un jeu d'hypothèses, recalculez le modèle dépendant, enregistrez les résultats, puis répétez. Comme la source est un objet en direct, aucun fichier n'est écrit entre les itérations, et cent scénarios coûtent cent recalculs plutôt que cent cycles d'enregistrement puis rechargement
La validation avant livraison est le troisième cas, et le moins évident. Ouvrez un classeur, enregistrez ses compagnons, recalculez, et comparez les résultats aux valeurs que le fichier portait déjà. Une divergence signifie que les valeurs en cache sont obsolètes, ce qui est un défaut qu'il vaut la peine de détecter avant qu'un client reçoive le fichier. Cette comparaison est un complément naturel à la comparaison de classeurs décrite dans comparer deux classeurs Excel
Hygiène des références avant d'y faire confiance
Exécutez d'abord une passe d'analyse des formules. Elle rapporte fonctions manquantes, références externes et composants de dépendance circulaire en un seul parcours, ce qui vous indique exactement quels noms de classeurs un document attend avant même de commencer à chercher les fichiers :
var
Issues: TArray<TXLSXFormulaIssue>;
I: Integer;
begin
Issues := Main.AnalyzeFormulaIssues;
for I := 0 to High(Issues) do
if Issues[I].Kind = xlsxFormulaIssueExternalReference then
Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
[Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;Deux autres habitudes font gagner du temps. Gardez le nom d'enregistrement canonique — enregistrez sous le nom de fichier simple et laissez la normalisation gérer les variantes entre guillemets et préfixées par un chemin dans les formules. Et traitez un classeur qui référence des fichiers que vous ne pouvez pas fournir comme un artefact en lecture seule : le recalculer préservera les valeurs mises en cache, mais toute édition qui change une cellule dépendante laisse le document dans un état où certaines valeurs sont actuelles et d'autres historiques, sans rien dans le fichier pour indiquer lesquelles. Copier des formules entre classeurs est une opération différente avec ses propres règles de reliaison, traitée dans la copie inter-classeurs et la reliaison de formules, et les plages nommées ajoutent une couche supplémentaire décrite dans les noms définis et les formules inter-feuilles
L'évaluation inter-classeurs en direct, le recalcul incrémental et l'analyse de formules s'exécutent sur le même moteur pour Delphi et C++Builder ; la liste complète des fonctionnalités se trouve sur la page du composant tableur HotXLS Delphi