HotXLS répond à la question que tout pipeline tableur finit par devoir poser, à savoir si les nombres stockés dans un classeur correspondent encore aux formules qui les ont produits. CalculateAndVerify recalcule tout le graphe de dépendances dans une surcouche isolée, compare chaque résultat à la valeur en cache déjà présente dans la cellule, et rapporte les désaccords. Par défaut, il ne change rien
La raison pour laquelle cela compte est qu'un fichier tableur stocke deux choses par cellule à formule : la formule et la dernière valeur que quelqu'un a calculée pour elle. Excel les garde en phase. Tout le reste du monde, pas nécessairement. Un fichier passé par une bibliothèque plus ancienne, un recalcul partiel, une partie XML éditée à la main ou un outil qui a écrit des valeurs sans les recalculer présentera allègrement un total qui ne suit plus de ses entrées, et rien dans le format de fichier ne le signale
Pourquoi une valeur en cache qui contredit sa formule est-elle si dangereuse ?
Parce qu'elle est invisible dans chaque chemin de lecture ordinaire. Ouvrez le fichier dans une visionneuse, lisez la cellule via une API, exportez-la en CSV ou en PDF, et vous obtenez le nombre en cache. La formule est là, dans la même cellule, et personne ne les compare. Le décalage ne fait surface que quand quelqu'un ouvre le classeur dans Excel, qui recalcule au chargement sous la plupart des réglages, et soudain un rapport signé le trimestre dernier montre des totaux différents
L'audit existe pour faire de cette comparaison une opération délibérée et planifiée plutôt qu'un accident. C'est l'équivalent tableur de la vérification d'un checksum : assez bon marché pour tourner dans un pipeline d'ingestion, et la seule chose qui transforme un problème d'intégrité de données silencieux en rapport sur lequel on peut agir
var
Book: TXLSWorkbook;
Options: TXLSRecalcAuditOptions;
Report: TXLSCalculationAuditReport;
I: Integer;
begin
Book := TXLSWorkbook.Create(nil);
try
Book.LoadFromFile('quarterly-close.xls');
Options := TXLSRecalcAuditOptions.Default;
Options.MaxIssues := 500;
Report := Book.CalculateAndVerify(Options);
try
for I := 0 to Report.Count - 1 do
if Report[I].Kind = xlcaiCacheMismatch then
Writeln(Report[I].SheetName, '!',
Report[I].Row, ':', Report[I].Col, ' ',
Report[I].Formula,
' cached=', VarToStr(Report[I].Actual),
' recomputed=', VarToStr(Report[I].Expected));
if Report.Truncated then
Writeln('issue budget reached, raise MaxIssues');
finally
Report.Free;
end;
finally
Book.Free;
end;
end;Il y a trois surcharges et elles répondent à trois questions différentes. Le CalculateAndVerify sans paramètre renvoie un compte de désaccords, ce qui suffit à un contrôle de santé. La surcharge avec un tableau out de désaccords vous donne les cellules. La surcharge qui prend TXLSRecalcAuditOptions renvoie un TXLSCalculationAuditReport complet, et c'est celle qu'il faut quand on veut savoir non seulement qu'une valeur diverge mais pourquoi l'audit n'a pas pu évaluer quelque chose
La surcouche, et pourquoi l'audit n'écrit pas
Chaque valeur recalculée atterrit dans une surcouche plutôt que dans le cache de cellules, et la surcouche est injectée tout au début du callback de lecture de cellule dans les deux moteurs de classeur. Ce placement rend l'audit auto-cohérent : quand B1 est recalculée et que C1 dépend de B1, C1 voit la valeur de cette passe d'audit, pas l'ancienne valeur en cache. Sans cela, une seule erreur en amont serait rapportée une fois puis absorbée, et chaque cellule en aval semblerait d'accord avec une entrée fausse
Les cellules dont la valeur recalculée correspond au cache n'entrent pas du tout dans la surcouche. Ce n'est pas une micro-optimisation, c'est ce qui rend l'audit abordable. Un classeur propre de cent mille formules effectue zéro écriture de surcouche et la passe reste dans un budget de 1.35x par rapport à un recalcul complet, et c'est la différence entre quelque chose qu'on peut lancer à chaque ingestion et quelque chose qu'on lance une fois par trimestre
L'évaluation suit un ordre topologique sériel dérivé du graphe de dépendances, avec chaque nœud marqué sale d'abord, si bien que chaque cellule est calculée exactement une fois après ses entrées. Si vous voulez la machinerie incrémentale qui garde un classeur vivant à jour au lieu d'auditer un classeur stocké, c'est un autre mécanisme, décrit dans le recalcul incrémental et le graphe de dépendances
Les échecs sont classés, pas entassés
Une cellule que l'audit ne peut pas évaluer n'est pas la même constatation qu'une cellule dont la valeur diverge, et TXLSCalculationAuditIssueKind garde les catégories séparées. xlcaiCacheMismatch est le désaccord de valeur. xlcaiMissingFunction et xlcaiMissingName disent que l'évaluateur a rencontré quelque chose qu'il n'implémente pas ou ne peut pas résoudre. xlcaiUnsupportedArguments couvre les formes d'arguments hors du sous-ensemble pris en charge. xlcaiExternalReferenceDenied et xlcaiExternalReferenceMissing séparent un refus de politique d'un classeur absent. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled et xlcaiInternalFailure complètent l'ensemble
Une distinction vaut d'être énoncée parce qu'elle inverse une hypothèse courante. Un code d'erreur Excel positif est un résultat, pas un échec. Une cellule qui s'évalue légitimement en #DIV/0! a calculé correctement, donc l'audit stocke cette erreur dans la surcouche et la compare au cache comme toute autre valeur. Un classeur plein de cellules d'erreur intentionnelles produit zéro constatation, et un classeur où une erreur est apparue ou a disparu depuis la mise en cache des valeurs produit exactement les constatations voulues
Les références circulaires ont leur propre traitement. Les nœuds d'un cycle n'entrent jamais dans l'ordre topologique, donc chacun est rapporté individuellement comme xlcaiCircularReference, et l'audit ne lance pas le solveur itératif. C'est un contrat délibéré en lecture seule : le fait que l'itération soit activée change la façon d'interpréter le code de résultat, pas ce que fait l'audit. La mécanique de l'évaluation itérative est couverte séparément dans le calcul itératif et les références circulaires
Lire une chaîne d'échec
Quand une formule échoue à s'évaluer, savoir quelle cellule a échoué suffit rarement, car l'échec se trouve en général trois niveaux plus bas dans une chaîne de références. Chaque anomalie porte donc une chaîne Stack rendue avec le cadre le plus extérieur en premier, sous la forme Sheet1!A1 > Sheet1!B2 > Data!C7, si bien que le rapport pointe la cellule qui a réellement cassé plutôt que celle que vous regardiez par hasard
L'enregistreur est borné. MaxStackFrames vaut 64 par défaut avec un plancher de 8, et la chaîne d'échec la plus profonde est celle conservée : un cadre intérieur enregistre la chaîne quand l'échec y prend naissance, et les cadres extérieurs qui se déroulent ensuite ne l'écrasent pas. Si une chaîne a dépassé le budget, Report.StackTruncated est posé, ce qui vous dit la différence entre une chaîne courte et une chaîne dont vous n'avez pas tout vu
// Lecture seule par défaut. ApplyResults ne valide la surcouche qu'après un audit
// entièrement réussi, sous une garde d'écriture qui rejette la validation si la
// structure du classeur a changé pendant que l'audit tournait
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0; // comparaison exacte, fait voir la dérive
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;
Report := Book.CalculateAndVerify(Options);
try
if Report.Applied then
Book.SaveToFile('quarterly-close-repaired.xls')
else
Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
Report.Free;
end;
procedure THarness.HandleProgress(ASender: TObject;
ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
ACancel := FUserRequestedStop; // l'audit s'arrête à la frontière de nœud suivante
end;Quand laisser l'audit réparer le classeur ?
Seulement quand l'audit est revenu entièrement propre d'anomalies de classe échec, ce qui est précisément la condition que ApplyResults fait respecter pour vous. La validation se produit après une passe entièrement réussie, non annulée, et passe une garde structurelle : le moteur binaire surveille un identifiant de changement de classeur, le moteur OOXML photographie une génération de structure par feuille. Si quoi que ce soit a bougé pendant que l'audit tournait, les résultats décrivent un classeur qui n'existe plus et la validation est refusée
Notez l'asymétrie délibérée. Les désaccords de cache ne bloquent pas la validation, car ce sont exactement ce que la validation est là pour réparer. Les anomalies de classe échec la bloquent, car un classeur où certaines formules n'ont pas pu être évaluées serait à moitié réparé, et un classeur à moitié réparé est pire qu'un classeur non réparé que vous savez devoir méfiance
La tolérance est une décision de politique, pas un défaut
La comparaison par défaut est une tolérance absolue de 1E-6 avec la tolérance relative désactivée, ce qui préserve le comportement classique et accepte en silence une dérive de 4E-7. C'est en général juste : les différences d'ordre d'évaluation en virgule flottante entre ce qui a produit le fichier et l'évaluateur courant produiront des différences de cette taille sur de longues sommes, et les rapporter comme constatations d'intégrité est du bruit
Mettez les deux tolérances à zéro quand la question est différente, quand vous cherchez à savoir si un évaluateur a changé de comportement entre versions, ou si un outil tiers réécrit les valeurs d'une façon subtilement différente. À zéro, la même dérive de 4E-7 devient visible, et tout le reste aussi. Choisissez la tolérance selon la question que vous posez, et consignez le choix à côté du rapport, car un rapport sans sa tolérance n'est pas interprétable
Deux capacités voisines complètent le tableau. Quand vous voulez savoir pourquoi une formule donnée produit la valeur qu'elle produit, la vue pas à pas du traceur d'évaluation de formules est le bon outil. Quand vous voulez délibérément que les valeurs en cache soient honorées sans aucun recalcul, par exemple sur un chemin d'ingestion qui doit reproduire le fichier exactement tel qu'arrivé, ce mode est décrit dans la lecture des valeurs de formules en cache sans recalcul. L'audit est ce qui se tient entre les deux : il vous dit si faire confiance au cache est sûr. Il est livré avec le composant tableur Delphi HotXLS pour les moteurs binaire et OOXML