Un total général qu'Excel affiche comme 37 peut revenir en 67 après qu'un programme a ouvert le fichier .xls et l'a enregistré sans modifier une seule cellule. Les 30 en plus ne sont pas aléatoires. C'est exactement la somme des deux sous-totaux de groupe, 10 et 20, qui se trouvent dans la plage couverte par le total général. Deux choses doivent mal tourner ensemble pour que ce nombre bouge : l'enregistrement doit recalculer chaque formule en sortie, et ce qui recalcule doit oublier qu'un SUBTOTAL saute les autres cellules SUBTOTAL. Supprimez l'un des deux et le fichier survit, ce qui explique pourquoi le premier problème peut rester invisible derrière le second
Pourquoi l'enregistrement d'un fichier XLS change-t-il une valeur de formule ?
Un enregistrement change une valeur de formule quand le writer calcule le nombre qu'il stocke à côté de chaque formule au lieu de copier celui qu'il a chargé. Chaque enregistrement Formula BIFF8 porte un champ FormulaValue de huit octets ([MS-XLS] §2.4.127, disposition de la valeur en §2.5.133) qui contient le dernier résultat qu'Excel a affiché pour cette cellule. Un writer doit mettre quelque chose dans ces huit octets, et le chemin de code le plus court consiste à remettre chaque formule au moteur de calcul. Une fois cela en place, chaque enregistrement est un recalcul complet que personne n'a demandé, et chaque point de désaccord entre ce moteur et Excel devient un nombre modifié dans un fichier que l'utilisateur croit intact
Le désaccord sur SUBTOTAL fait un bon exemple, parce que la règle du tableur est simple et que le piège est dans le typage Variant de Delphi. Excel définit chaque numéro de fonction SUBTOTAL comme ignorant les cellules dont la propre formule est un autre SUBTOTAL. Pour une feuille dont les lignes de détail s'additionnent à 37 (les lignes sous deux groupes qui sous-totalisent 10 et 20, plus 7 en dehors de tout groupe), le total général est 37. Un moteur qui ne repère pas les sous-totaux imbriqués compte le 10 et le 20 une seconde fois et arrive à 67. Les repérer demande en général une courte vérification sur le texte de la formule, et la version Delphi évidente compile, tourne et ne correspond jamais à rien :
// Une fonction que vous écrivez vous-même, par exemple pour parcourir une feuille
// à la recherche des formules qu'un SUBTOTAL englobant doit sauter
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// Le test temptant ne réussit jamais sur un Delphi Unicode : une String
// affectée à un Variant est varUString, pas varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // accepte varString, varOleStr et varUString
Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
SameText(Copy(F, 1, 10), '=SUBTOTAL(') or
SameText(Copy(F, 1, 10), 'AGGREGATE(') or
SameText(Copy(F, 1, 11), '=AGGREGATE(');
end;
// Utilisation avec HotXLS (feuilles, lignes et colonnes sont basées sur 1) :
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr est le test que l'évaluateur HotXLS applique à la même question, et il traite aussi les cellules AGGREGATE comme exclues d'un sous-total englobant, comme le fait Excel. Corriger le prédicat ne fait pourtant pas tout. Avec une vérification correcte, l'enregistrement recalculant n'écrit 37 que parce que le moteur se trouve d'accord avec Excel sur ce fichier particulier. La prochaine formule que le moteur traitera légèrement différemment bougera encore. Les règles disant quelles cellules SUBTOTAL et AGGREGATE sautent, lignes masquées comprises, sont traitées dans l'article sur SUBTOTAL et AGGREGATE avec lignes masquées. Avec la sauvegarde cache d'abord, un fichier que vous n'avez jamais demandé de calculer repart sur disque avec 37 dans la cellule, quoi qu'en aurait fait le moteur
Ce qu'un enregistrement XLS classique écrit dans chaque enregistrement Formula
HotXLS enregistre un classeur BIFF8 cache d'abord : pour chaque cellule à formule, TXLSWorkbook.SaveAs écrit la valeur en cache chaque fois que TryGetCachedFormulaValue en rapporterait une, et ne lance l'évaluateur que sinon. L'état du cache décide. Une cellule en état xlfcsLoaded (la valeur vient du fichier source) ou xlfcsCalculated (HotXLS l'a calculée pendant cette session) voit son CacheInfo.Value écrit octet pour octet. Une cellule en état xlfcsMissing ou xlfcsInvalidated est évaluée au moment de l'enregistrement. Cela correspond à la façon dont Excel traite un enregistrement : un instantané de ce que chaque cellule affiche, qui en mode de calcul manuel peut être très en retard, écrit fidèlement quand même
Le chemin d'évaluation reste là pour de bonnes raisons. Une formule que vous affectez en code via Cells[Row, Col].Formula n'a pas encore de cache, et affecter une nouvelle formule à une cellule chargée la passe en xlfcsInvalidated, parce que l'ancien résultat appartient à l'ancienne expression. Toutes deux sont calculées pendant l'enregistrement, si bien qu'un classeur généré de zéro s'ouvre dans Excel avec des nombres calculés au lieu de zéros. Si même l'évaluateur ne peut pas produire de valeur (fonction non prise en charge, lien externe cassé), le writer stocke un zéro et positionne fAlwaysCalc, bit 0 des drapeaux de l'enregistrement en §2.4.127, pour qu'Excel recalcule la cellule à l'ouverture au lieu de se fier au substitut
Une propriété semble liée et ne l'est pas. TXLSWorkbook.RecalcOnSave reflète l'option « Recalculate workbook before saving » d'Excel depuis l'enregistrement CalcSaveRecalc ([MS-XLS] §2.4.37). Elle ne concerne qu'Excel, uniquement en mode de calcul manuel, et HotXLS se contente de l'écrire dans le fichier. SaveAs ne la consulte pas pour décider ce qui va dans FormulaValue. L'aller-retour ci-dessous est la façon la plus rapide de voir le contrat sur l'un de vos propres fichiers :
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Feuille, ligne et colonne sont basées sur 1
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // les cellules en cache sont écrites telles quelles
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Attendu : After.Value égale Before.Value, et After.State revient à
// xlfcsLoaded, parce que la valeur vient du fichier enregistré
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;Le côté lecture de ce contrat, y compris la signification de chaque état et pourquoi un blanc ou un False en cache compte quand même comme une valeur, est décrit dans Read Excel Cached Formula Values in Delphi Without Recalc. Exécutez la vérification ci-dessus sur la cellule du total général d'un classeur avec sous-totaux imbriqués et les deux lignes affichent le même nombre
Où la racine d'une formule partagée BIFF garde-t-elle sa valeur en cache ?
Dans son propre enregistrement Formula, comme toute autre cellule à formule, mais l'expression arrive un enregistrement plus tard, et ce gap est l'endroit où un lecteur imprudent perd le cache. Le BIFF8 stocke une formule partagée comme un enregistrement ShrFmla ([MS-XLS] §2.4.260) placé juste après l'enregistrement Formula de la cellule en haut à gauche du groupe. Chaque cellule membre, racine comprise, porte un rgce fait d'un unique token PtgExp (§2.5.198) : l'octet $01 suivi de la ligne et de la colonne de la racine. Les cellules suiveuses sont faciles, puisque au moment où elles apparaissent l'expression de la racine existe déjà et leur propre FormulaValue est dans leur propre enregistrement. La racine ne l'est pas. Quand son enregistrement Formula est lu, elle a une valeur en cache de 37 et pas encore d'expression
Installer une expression sur une cellule efface son cache, et cela doit rester vrai, parce qu'une formule remplacée rend vraiment l'ancien résultat caduc. Appliquée aveuglément à l'enregistrement ShrFmla, pourtant, la même règle jette le 37 de la racine au moment où l'expression arrive, TryGetCachedFormulaValue rapporte la racine comme xlfcsMissing, et l'enregistrement retombe sur l'évaluateur pour exactement la cellule qu'un utilisateur est le plus susceptible de regarder. Le lecteur HotXLS retient la valeur décodée de la racine en attendant l'enregistrement ShrFmla, installe l'expression, puis remet la valeur chargée avec l'état xlfcsLoaded. Les formules matricielles (l'enregistrement Array, §2.4.4) suivent le même ordre d'enregistrements et reçoivent le même traitement. La forme String du cache n'a jamais eu ce problème, parce que son texte arrive dans un enregistrement String séparé et est apparié à la cellule par sa ligne et sa colonne, si bien que sa position dans le flux n'a jamais d'importance
Si vous traitez aussi le côté OOXML, l'article sur l'expansion si des formules partagées XLSX explique pourquoi le format paquet n'a pas de gap d'ordre d'enregistrements mais apporte ses propres pièges d'expansion
Les cellules suiveuses d'une formule partagée possèdent une copie décalée de l'expression
L'expression d'un enregistrement ShrFmla est écrite relativement à la cellule racine, donc chaque suiveuse a besoin de sa propre copie déplacée de sa distance à la racine. Copiez-la telle quelle et un groupe enraciné en B1 avec =A1*3 remet le même =A1*3 à B2 et B3. HotXLS donne à chaque suiveuse une copie dont les références relatives sont décalées de la distance en lignes et colonnes de la suiveuse, si bien que B2 possède un vrai =A2*3 et B3 un =A3*3
C'est aussi pourquoi un simple aller-retour ne peut pas vous dire si les expressions des suiveuses sont justes. Les suiveuses gardent leurs valeurs chargées pendant un enregistrement, donc une copie verbatim passerait. Une vérification qui prouve quelque chose doit changer une entrée et recalculer. La routine ci-dessous écrit un groupe partagé avec UseSharedFormulas activé (le défaut est False, qui écrit un enregistrement Formula par cellule), rouvre le fichier, modifie A2 et observe B2 :
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 deviennent un groupe ShrFmla
Book.SaveAs(FileName); // pas encore de caches : évalué à 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // modifie l'entrée de la suiveuse
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Attendu : 12, toujours chargé
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Attendu : 30, depuis =A2*3
finally
Book.Free;
end;
end;Une suiveuse qui aurait réutilisé =A1*3 afficherait 6 sur la seconde ligne. La première ligne compte tout autant : elle montre que modifier un littéral laisse la valeur chargée de la formule dépendante en place jusqu'à ce que quelque chose recalcule
Ce que la sauvegarde cache d'abord vous laisse
La sauvegarde cache d'abord préserve ce qui a été chargé. Elle ne suit pas si ce qui a été chargé est encore vrai. Modifier une cellule dont une formule dépend marque le graphe de dépendances pour le prochain recalcul, mais la cellule dépendante garde sa valeur xlfcsLoaded, et SaveAs écrit ce nombre périmé sauf si vous appelez Recalculate d'abord ou lisez la Value de la cellule, ce qui calcule la formule et la passe en xlfcsCalculated. Recalculate renvoie le nombre de cellules à formule qu'il n'a pas pu évaluer, donc un résultat non nul mérite d'être journalisé avant l'enregistrement
Dans le pipeline courant, un programme ouvre un classeur construit par quelqu'un d'autre, remplit quelques libellés ou un en-tête, et enregistre. Rien dans ce fichier ne devrait changer de valeur, et avec la sauvegarde cache d'abord rien ne change. Un programme qui modifie des entrées possède au contraire l'étape de recalcul, et cette étape a sa place dans un appel Recalculate visible à côté des modifications, là où quiconque lit le code peut le voir. Excel fait le même compromis en mode de calcul manuel. Le writer XLSX a sa propre politique, RecalcBeforeSave avec les modes xrmManual, xrmIfDirty et xrmAlways, positionnée sur la classe de classeur XLSX et indépendante de tout ce qui précède
Deux frontières découlent du design. Le chemin du cache n'aide que les cellules en état xlfcsLoaded ou xlfcsCalculated, donc un générateur qui écrit des milliers de nouvelles formules sans jamais les évaluer paie toujours une évaluation par cellule pendant l'enregistrement. Et le contrat gouverne ce qu'un enregistrement écrit, pas ce qu'un recalcul explicite calcule : un fichier intact garde les nombres qu'Excel a stockés, tandis qu'un Recalculate écrit les résultats de l'évaluateur HotXLS. Comparez les deux sur quelques classeurs représentatifs avant de basculer un pipeline vers des enregistrements recalculés
Le HotXLS Delphi Spreadsheet Component lit et écrit des classeurs BIFF8 classiques et XLSX depuis Delphi et C++Builder sans Excel ni automatisation OLE installés. Sa page produit documente les appels classeur, valeur en cache et recalcul utilisés dans ces exemples