Arrêter le recalcul silencieux des formules XLS en Delphi

HotXLS, la bibliothèque Excel native pour Delphi et C++Builder, sauvegarde un classeur BIFF8 classique en .xls cache en premier : TXLSWorksheet.WriteFormula demande à TXLSWorkbook.TryGetCachedFormulaValue la valeur qu'Excel a stockée à côté de chaque formule et n'appelle l'évaluateur que lorsque ce cache est absent ou invalidé. Un classeur que vous avez ouvert et jamais touché réécrit les mêmes nombres, et des résultats frais nécessitent un appel explicite à Recalculate au lieu d'être un effet de bord caché de SaveAs

Le bug qui a fait remonter ce contrat au grand jour était d'une taille gênante. Un fichier du corpus nommé nested-subtotals.xls contient un total général en R2C4 dont la valeur en cache est 37. Ouvrez-le avec HotXLS, demandez TryGetCachedFormulaValue pour la cellule, obtenez 37. Sauvegardez-le sans changer une seule cellule, ouvrez la copie sauvegardée, posez la même question, obtenez 67. Rien dans l'API n'avait été prié de calculer quoi que ce soit, et pourtant un nombre du fichier avait bougé de exactement 30 — et 30 se trouve être la somme des deux sous-totaux de groupe, 10 et 20, situés dans la plage que le total général couvre

Pourquoi sauvegarder un fichier XLS change-t-il la valeur d'une formule ?

Il fallait deux défauts indépendants alignés pour que ce 37 devienne 67, et corriger l'un seul aurait masqué l'autre. Le premier était structurel : l'écrivain classique recalculait chaque formule à chaque sauvegarde. Le second était un test de type qui ne pouvait jamais être vrai pour une formule chargée depuis le disque, ce qui faisait compter deux fois à l'évaluateur les cellules SUBTOTAL imbriquées. Le fichier du corpus était simplement la première entrée où un recalcul à la sauvegarde produisait une réponse différente d'Excel et où quelqu'un comparait les deux. Le défaut structurel est facile à énoncer : avant la v2.382.3, TXLSWorksheet.WriteFormula et son jumeau pour les formules partagées WriteFormulaWithTExp obtenaient le champ FormulaValue de huit octets de chaque enregistrement Formula en appelant TXLSWorkbook.GetFormulaValue, c'est-à-dire l'évaluateur. Le cache que ParseFormula avait soigneusement décodé du fichier source au chargement n'était jamais consulté à la sortie. En pratique, chaque sauvegarde était un recalcul complet avec l'API de recalcul au niveau classeur contournée, donc rien de ce que vous auriez pu régler sur le classeur ne l'aurait arrêtée. Partout où l'évaluateur HotXLS divergeait d'Excel, qu'il s'agisse d'une fonction légitimement non prise en charge ou d'un simple bug, cela devenait une modification silencieuse des données à la sauvegarde

Le second défaut vivait dans le rappel de sous-total imbriqué qu'utilise l'évaluateur. Excel définit chaque forme de SUBTOTAL comme ignorant les cellules dont la formule est elle-même un SUBTOTAL, donc le calculateur dans lxCalc.pas arme FIgnoreSubtotalCells pendant l'agrégation et demande au classeur, via TXLSWorkbook.GetClassicIsSubtotalCell, si chaque cellule de la plage en est un. Ce rappel récupérait le texte de la formule sous forme de Variant et le testait avec VarType(f) = varOleStr. Le texte revient de GetUnCompiledFormula sous forme de String Delphi, et un String affecté à un Variant est varUString, jamais varOleStr. Le prédicat était faux pour chaque cellule de chaque fichier chargé, les sous-totaux de groupe étaient rajoutés une seconde fois au total général, et sur une sauvegarde qui recalculait tout, 10 + 20 + 7 devenait 67

// HotXLS 2.381 et antérieur : un Variant de formule construit depuis un String
// est varUString, donc cette comparaison n'a jamais réussi
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0 : VarIsStr accepte varString, varOleStr et varUString,
// et AGGREGATE est exclu des sous-totaux englobants comme le fait Excel
if VarIsStr(f) then
  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(');

La v2.382.0 a livré le correctif VarIsStr et, tant qu'elle était dans la même fonction, a appris au rappel que les cellules AGGREGATE sont elles aussi exclues des sous-totaux englobants. Cela seul a fait passer l'assertion du corpus, parce que le 37 recalculé correspondait désormais au 37 chargé. Cela n'a pas rendu la bibliothèque honnête : la sauvegarde recalculait toujours, et le test n'était vert que parce que l'évaluateur se trouvait d'accord avec Excel sur ce fichier précis. Les règles déterminant quelles cellules SUBTOTAL et AGGREGATE sautent, y compris les rangées masquées, sont traitées dans l'article sur SUBTOTAL, AGGREGATE et les rangées masquées ; ce qui compte ici, c'est qu'aucun évaluateur ne devrait avoir voix au chapitre sur un fichier que vous ne lui avez pas demandé de calculer

Que garantit Excel sur les valeurs en cache lors d'une sauvegarde ?

Excel traite une sauvegarde comme un instantané, pas comme un événement de calcul. La valeur écrite dans le champ FormulaValue d'un enregistrement Formula ([MS-XLS] §2.4.127, disposition en §2.5.133) est ce que la cellule affiche à ce moment-là, ce qui en mode de calcul manuel peut dater de plusieurs années, et Excel l'écrit quand même fidèlement. Le recalcul est une opération distincte avec son propre déclencheur. HotXLS suit désormais la même règle pour les sauvegardes classiques : WriteFormula et WriteFormulaWithTExp appellent d'abord TryGetCachedFormulaValue, prennent CacheInfo.Value quand l'état est xlfcsLoaded ou xlfcsCalculated, et ne retombent sur GetFormulaValue que pour xlfcsMissing et xlfcsInvalidated. La moitié lecture de ce contrat, y compris ce que signifie chaque état et pourquoi une valeur en cache vide ou False compte quand même comme une valeur, est décrite dans Lire les valeurs de formule en cache d'Excel en Delphi sans recalcul

Le chemin de repli est délibérément conservé, pas supprimé. Une formule que vous avez affectée dans cette session via Cells[Row, Col].Formula arrive sans cache, et une formule que vous avez remplacée sur une cellule chargée est marquée xlfcsInvalidated par _SetCompiledFormula ; les deux sont évaluées au moment de la sauvegarde exactement comme avant, donc un classeur généré s'ouvre encore dans Excel avec des nombres dedans. Quand même l'évaluateur ne parvient pas à produire une valeur, l'écrivain émet une charge nulle et positionne fAlwaysCalc (bit 0 de grbit de §2.4.127) pour qu'Excel recalcule la cellule à l'ouverture au lieu de faire confiance à la valeur de remplissage

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // Feuille, rangée et colonne en base 1 : R2C4 sur la première feuille
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // aucun évaluateur impliqué pour les cellules en cache
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 pour nested-subtotals.xls
    // Une sauvegarde qui recalculait aurait écrit 67 ici
  finally
    Book.Free;
  end;
end;

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 de formule, et c'est précisément ce qui faisait de la cellule racine d'un groupe partagé le seul endroit où la sauvegarde cache en premier perdait encore. Une formule partagée en BIFF8 est stockée comme un enregistrement ShrFmla ([MS-XLS] §2.4.260) qui suit l'enregistrement Formula de la cellule en haut à gauche, et chaque cellule membre, racine comprise, porte un rgce composé d'un unique jeton PtgExp (§2.5.198) : le premier octet de l'expression analysée est $01, suivi de la rangée et de la colonne de la cellule racine. Les cellules suiveuses sont autonomes — HotXLS lit le FormulaValue de chacune et résout l'expression en cherchant la formule compilée de la racine. La cellule racine est différente, parce qu'au moment où son enregistrement Formula est analysé, l'expression n'existe pas encore ; elle arrive un enregistrement plus tard

C'est dans cet écart d'un enregistrement que le cache s'est perdu. TXLSReader.ParseFormula décode la valeur en cache et, en voyant un PtgExp dont les coordonnées égalent celles de la cellule elle-même, retient la cellule dans FSharedFormulaRow et FSharedFormulaCol et publie le cache vers la cellule. Quand l'enregistrement ShrFmla ($04BC) arrive, ParseSharedFormula compile l'expression et l'installe avec _SetCompiledFormula, et _SetCompiledFormula fait ce qu'il doit faire pour tout changement de formule : il efface FCachedFormulaValue et remet l'état à xlfcsMissing. Le 37 chargé de la racine était donc jeté avant que quiconque puisse le lire, TryGetCachedFormulaValue signalait la racine comme non mise en cache, et l'écrivain cache en premier retombait docilement sur l'évaluateur précisément pour la cellule que tout le monde regardait. L'enregistrement Array (§2.4.4) partage le même ordre et avait le même trou

Le correctif de la v2.382.3 ajoute un troisième champ, FSharedFormulaCachedValue, à côté des coordonnées de racine en attente. ParseFormula y range le cache décodé quand il reconnaît une racine, et ParseSharedFormula comme ParseArrayFormula le rejouent via _SetCellCachedFormulaValue immédiatement après avoir installé l'expression compilée, puis remettent la réserve à Unassigned. La variante String du cache n'est affectée par rien de tout cela parce que sa charge arrive dans un enregistrement String séparé et est routée par coordonnées de cellule, pas par ordre d'enregistrement. Si vous travaillez sur le versant OOXML du même concept, l'article sur l'expansion si des formules partagées XLSX explique pourquoi le format paquet n'a pas de problème d'ordre équivalent mais a ses propres pièges d'expansion

Pourquoi les suiveuses d'une formule partagée ont-elles besoin d'un décalage relatif ?

Parce que l'expression stockée dans ShrFmla est écrite relativement à la cellule racine, et qu'une suiveuse qui la réutilise telle quelle évalue les références de la racine au lieu des siennes. L'ancien lecteur installait Value.GetCopy() sur chaque suiveuse, une copie profonde sans déplacement, donc un groupe dont la racine était B1 avec =A1*3 donnait aussi =A1*3 à chaque suiveuse. La sauvegarde cache en premier masquait en fait ce bug pour les fichiers chargés, puisque les suiveuses avaient leur propre FormulaValue et n'avaient jamais besoin de l'expression pour se sauvegarder correctement ; le bug remontait dès que quelque chose recalculait. Le lecteur installe désormais TXLSCompiledFormula.GetCopy(row - srow, col - scol), qui parcourt l'arbre syntaxique et décale chaque référence relative de la distance entre la suiveuse et la racine, donc la suiveuse en B2 possède un vrai =A2*3

Le test de régression qui verrouille les deux comportements vaut la peine d'être lu parce qu'il refuse de laisser passer une coïncidence. Il construit un classeur avec =A1*3 et =A2*3 sur les entrées 2 et 4, puis injecte les caches délibérément faux 999 et 888 via _SetCellCachedFormulaValue, une fois avec UseSharedFormulas activé et une fois désactivé. Après une sauvegarde et un rechargement, les deux cellules doivent toujours renvoyer 999 et 888 — preuve que la sauvegarde n'a touché ni le cache de la racine ni celui de la suiveuse. Ce n'est qu'après un Recalculate explicite qu'elles doivent devenir 6 et 12, preuve que l'expression décalée de la suiveuse est correcte. Un test qui aurait injecté les vraies valeurs serait passé avec l'ancien écrivain aussi, et c'est tout l'intérêt d'injecter de fausses valeurs

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // modifier une entrée

    // Les caches chargés des formules dépendantes ne sont PAS invalidés par une
    // modification de littéral, donc un simple SaveAs garderait les anciens nombres.
    // Demandez un recalcul quand vous voulez vraiment des résultats frais :
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Ce que le contrat cache en premier ne fait pas pour vous

La sauvegarde cache en premier préserve ce qui a été chargé ; elle ne suit pas si ce qui a été chargé est encore vrai. Modifier un littéral dont dépend une formule marque le graphe de dépendances comme sale pour l'évaluateur, mais laisse en place le cache xlfcsLoaded de la cellule dépendante, et l'écrivain classique écrira volontiers cette valeur périmée sauf si vous appelez Recalculate ou lisez d'abord la Value de la cellule, ce qui la calcule et fait passer l'état à xlfcsCalculated. C'est le même compromis qu'Excel fait en mode de calcul manuel, et c'est le bon pour un pipeline qui ouvre des fichiers tiers, modifie quelques libellés et sauvegarde — mais cela signifie qu'un classeur qui modifie des entrées doit assumer explicitement son étape de recalcul. La politique RecalcBeforeSave de l'écrivain XLSX n'est pas touchée par ce travail et a son propre mode manuel qui préserve les caches dans le même esprit. Deux limites plus petites en découlent : le chemin cache en premier n'aide que les cellules dont l'état est xlfcsLoaded ou xlfcsCalculated ; un générateur qui écrit des formules et ne les évalue jamais paie toujours une évaluation par cellule au moment de la sauvegarde, exactement comme avant. Et le correctif des sous-totaux imbriqués corrige les cellules que l'évaluateur saute, pas toutes les fonctions que l'évaluateur implémente — un fichier dont les formules ne sont pas calculables par HotXLS à l'identique d'Excel peut désormais faire l'aller-retour sans être touché en toute sécurité, mais un Recalculate délibéré sur ce fichier produira quand même la réponse de la bibliothèque plutôt que celle d'Excel, et vous devriez comparer les deux avant de faire confiance à une sauvegarde recalculée

Les sauvegardes classiques cache en premier, les caches restaurés des racines de formules partagées et matricielles, le décalage des références relatives pour les suiveuses partagées et les règles corrigées d'imbrication de SUBTOTAL et AGGREGATE sont tous livrés dans le HotXLS Delphi Spreadsheet Component standard pour Delphi et C++Builder, sans aucune dépendance à Excel ni à un serveur d'automation OLE ; la page produit contient la référence complète de l'API pour les points d'entrée classeur, lecture de cache et recalcul utilisés ici