LlosLab Software Development BlogNotes d'ingénierie pour les développeurs de PDF, Delphi et de logiciels de documents.

Article technique

Pourquoi Excel ignore les erreurs #SPILL! et #CALC! en cache

HotXLS Delphi Component

Excel ne fait confiance à un #SPILL! ou #CALC! en cache que si le classeur porte le contrat complet des erreurs modernes : des métadonnées Rich Data dont l'URI d'extension XLRICHVALUE est le GUID en minuscules, un fileVersion de 7, un calcId d'au moins 191029, et la fonction de calcul microsoft.com:RD. HotXLS, le composant XLSX natif Delphi, émet les quatre dès qu'une cellule enregistrée porte l'une de ces erreurs

Manquez une pièce et la panne est rarement bruyante. Parfois Excel 16 refuse carrément d'ouvrir le fichier. Plus souvent il s'ouvre bien et montre #VALUE! là où votre moteur de calcul a produit #SPILL!, ce qui ressemble à un bug de calcul et vous envoie chercher dans du code de formule parfaitement correct. Appuyez sur F9 et la bonne erreur apparaît, si bien que le problème tend à être fermé comme "ça marche sur ma machine" jusqu'à ce qu'un client ouvre le rapport avec le calcul automatique désactivé. Ce billet parcourt ce que fait chaque pièce, le piège de casse du GUID qui semble ne pas pouvoir compter, et la règle du cache de texte de tableau qui échoue exactement de la même façon silencieuse

Pourquoi Excel montre #VALUE! au lieu d'un #SPILL! en cache ?

Excel montre #VALUE! parce que c'est littéralement ce que la cellule stocke. Le type d'erreur historique du SpreadsheetML ECMA-376 a une liste fixe de littéraux d'erreur, et #SPILL! et #CALC! n'y sont pas. Une cellule avec t="e" ne peut dire que #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! ou #N/A, si bien que les versions récentes d'Excel écrivent #VALUE! comme repli rétrocompatible et suspendent le vrai type d'erreur à un indice de métadonnées de valeur

Cet indice, l'attribut vm, pointe vers xl/metadata.xml, qui pointe vers une valeur Rich Data décrivant l'erreur réelle. Les lecteurs anciens voient #VALUE! et passent. Excel courant suit la chaîne et affiche l'erreur moderne, mais seulement s'il juge la chaîne digne de confiance. Voilà à quoi ressemble sur disque une formule renversée et bloquée :

<!-- sheet1.xml : littéral historique plus pointeur de métadonnées de valeur -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml : errorType 8 est #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS écrit le type d'erreur 8 avec décalages de colonne et de ligne pour #SPILL!, et le type d'erreur 13 sans décalages pour #CALC!. Le <v> historique reste #VALUE! dans les deux cas exprès, parce que c'est la valeur que tout lecteur ignorant Rich Data est censé voir

Le GUID en minuscules qu'Excel 16 vérifie vraiment

Le bloc de métadonnées future XLRICHVALUE doit utiliser l'URI d'extension {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} en minuscules, caractère pour caractère. Passez les lettres hexadécimales en majuscules et la build locale d'Excel 16 refusait d'ouvrir normalement le classeur, alors que chaque partie XML parsait, chaque relation se résolvait, et chaque compte et indice s'alignait

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

Quiconque a travaillé avec COM ou le registre sait que les GUID se comparent sans tenir compte de la casse, et c'est exactement cette habitude qui mord ici. On a isolé le problème à la manière lente : construit un paquet minimal qu'Excel rejetait, puis échangé une chose à la fois. Remplacer les types de contenu n'a pas corrigé. Ajouter une partie de types de valeurs riches n'a pas corrigé. Changer les préfixes de namespace ou réécrire les données de valeurs riches n'a pas corrigé. Mettre en minuscules ce seul attribut uri a corrigé, et le même paquet s'ouvrait alors et affichait à la fois #CALC! et #SPILL!. Traitez l'URI d'extension comme un littéral de chaîne opaque, copiez-le depuis la définition du format, et ne le passez jamais dans un formateur de GUID qui choisit sa propre casse

Il y a un corollaire pratique pour quiconque écrit des paquets de diagnostic à la main. Faites les modifications avec un parseur XML, pas avec un remplacement de texte global sur du XML sérialisé. Une substitution en bloc visant un seul token peut corrompre des entités comme &gt; ailleurs dans la partie, et vous vous retrouvez à déboguer une sonde cassée au lieu de la question d'origine

Que faut-il de plus à Excel pour lire une erreur moderne en cache ?

Les parties de métadonnées et leurs relations sont nécessaires mais pas suffisantes. Avec une chaîne Rich Data parfaitement valide en place, Excel ignorait encore l'erreur en cache jusqu'à ce que la partie classeur se déclare comme venant d'un moteur de calcul moderne. HotXLS écrit trois déclarations dans workbook.xml dès qu'une cellule enregistrée porte #SPILL! ou #CALC! :

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

L'élément fileVersion est du simple métadonnée de classeur ECMA-376 Part 1, et des lastEdited / lowestEdited de 7 disent à Excel que le fichier a été enregistré pour la dernière fois par une génération d'application qui comprend Rich Data. Le calcId de calcPr identifie la version du moteur de calcul, et 191029 est le minimum que nous avons vu Excel accepter pour ces caches. L'entrée microsoft.com:RD dans l'extension des fonctions de calcul, dans le namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, déclare que le moteur qui a produit les valeurs en cache a évalué avec la sémantique Rich Data. Ne fournir que le sous-type, que la partie types, ou que des préfixes et chemins de relations différents ne changeait rien au résultat. Les trois déclarations devaient être présentes ensemble

Trois détails dans la façon dont HotXLS les applique valent d'être connus si vous posez ces valeurs vous-même :

  • TXLSXWorkbook.CalcId n'est pas écrasé. Le rédacteur monte la valeur émise à 191029 seulement dans la sortie, si bien qu'un appelant qui a configuré un CalcId plus haut le garde, et la propriété en mémoire reste exactement ce que vous avez affecté
  • Les fonctions de calcul existantes et les extensions de classeur sans rapport venues avec un fichier ouvert sont préservées, pas remplacées
  • Le rédacteur vérifie l'existence d'une fonction microsoft.com:RD avant d'en ajouter une, si bien qu'enregistrer le même classeur de façon répétée n'empile jamais des déclarations dupliquées
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 se renverse sur trois lignes ; A2 est occupée, donc le résultat est #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Un CalcId plus haut survit ; le rédacteur ne le monte à 191029 que s'il est plus bas
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // parties d'erreurs riches écrites automatiquement
  finally
    Book.Free;
  end;
end;

Vous ne construisez jamais les parties Rich Data vous-même. Avant d'écrire, TXLSXWorkbook scanne chaque cellule à la recherche de valeurs #SPILL! ou #CALC!, crée ou étend xl/metadata.xml et les deux parties de valeurs riches, et lâche les liens de métadonnées de valeur périmés des cellules dont l'erreur a changé ou été effacée depuis. L'affectation explicite de CalcId ci-dessus est optionnelle et montrée seulement pour faire voir qu'elle est respectée

Les caches de texte de tableau vont dans t="str", pas dans les chaînes partagées

La même panne silencieuse apparaît pour les résultats de texte au sein de tableaux. HotXLS enregistre le texte en cache d'une cellule de formule, et de chaque cellule suiveuse dans une plage CSE ou tableau dynamique, en t="str" avec la chaîne littérale dans <v>. Écrire ces caches en chaînes partagées avec t="s" fait afficher à Excel #VALUE! dans les cellules du tableau quand il lit le fichier en cache

<!-- Correct : cache de texte littéral sur une cellule suiveuse de tableau -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Faux : indice de chaîne partagée, Excel montre #VALUE! depuis le cache -->
<c r="B3" t="s"><v>17</v></c>

Celui-là est facile à rater parce que t="s" est le bon choix pour les cellules de valeur ordinaires, et la plupart des rédacteurs routent chaque chaîne vers la table de chaînes partagées par défaut. Le piège est dans la vérification. Recalculez dans Excel et les cellules du tableau montrent le bon texte, ce qui prouve que la formule est bonne et ne prouve rien du cache que vous avez écrit. Si vos utilisateurs ouvrent des rapports en calcul manuel, ou qu'un outil en aval lit les valeurs en cache sans calculer, la version chaînes partagées est simplement cassée

Syntaxe de stockage des formules : égalité de tête, virgules et _xlws

Un nettoyage lié atterrit dans le même rédacteur : les formules qui partent en OOXML sont stockées dans la grammaire du fichier, pas dans celle qu'un utilisateur a tapée. HotXLS garde le texte que vous avez affecté à Formula pour l'affichage en aller-retour, mais la formule sérialisée lâche le = de tête, utilise des virgules entre les arguments de fonction, et laisse tranquilles les séparateurs de lignes des constantes de tableau et le texte entre guillemets. FILTER et SORT sont stockés avec le préfixe _xlfn._xlws. qu'Excel attend pour ces fonctions propres à la feuille, tandis que d'autres fonctions future comme SEQUENCE prennent le préfixe simple _xlfn. devant leur nom

// Saisi avec séparateurs points-virgules, stocké en
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // aucune ligne au-dessus de 1000 donne #CALC!, enregistré avec Rich Data

L'ordre compte au sein du rédacteur. L'échappement XML tourne après la normalisation syntaxique, jamais avant, pour la même raison que les paquets de diagnostic demandent un parseur : une réécriture de séparateurs appliquée à du texte déjà échappé mutilerait des entités comme &gt; dans les opérateurs de comparaison

Comment vérifier une erreur en cache sans se tromper soi-même

Le seul test digne de confiance est d'ouvrir le fichier dans Excel avec le calcul déjà en manuel, lire les cellules, et seulement ensuite recalculer. Excel hérite du mode de calcul du premier classeur ouvert dans une session, si bien que notre harnais d'acceptation crée d'abord son propre classeur amorce, le passe en manuel, puis ouvre le fichier testé. Les sondes qui sautaient l'étape amorce ne prouvaient que le fichier s'ouvrait et calculait correctement. Tout ce qu'elles étiquetaient comme en cache avait déjà été recalculé

Cette distinction est tout l'article en miniature. Un cache d'erreurs moderne est correct quand Excel l'affiche avant que tout calcul ne tourne, et chaque exigence ci-dessus, du GUID en minuscules aux suiveurs de texte t="str", a été trouvée en vérifiant cet état plutôt que celui recalculé. Pour l'arrière-plan sur la façon dont HotXLS dimensionne et possède les plages de renversement au départ, voir les formules de renversement de tableaux dynamiques en Delphi avec HotXLS. Si votre symptôme est une invite de réparation plutôt qu'une erreur fausse, les règles au niveau du paquet dans pourquoi Excel répare un XLSX valide sont le meilleur point de départ

FAQ

HotXLS demande-t-il un réglage pour enregistrer #SPILL! et #CALC! correctement ?

Non. HotXLS détecte les valeurs #SPILL! et #CALC! à l'enregistrement et écrit les parties Rich Data, le fileVersion, le calcId minimum et la fonction microsoft.com:RD automatiquement

Pourquoi le XML de la cellule contient-il encore #VALUE! ?

Le type d'erreur historique SpreadsheetML n'a pas de littéral pour #SPILL! ou #CALC!, donc #VALUE! est le repli rétrocompatible. Excel courant lit la vraie erreur à travers le lien de métadonnées de valeur vm

HotXLS abaissera-t-il un CalcId que j'ai posé moi-même ?

Non. TXLSXWorkbook.CalcId n'est monté à 191029 dans le fichier écrit que quand une erreur moderne est présente, et une valeur plus haute que vous avez configurée est gardée telle quelle

HotXLS lit et écrit XLSX, XLSM et XLS historique depuis Delphi et C++Builder avec son propre moteur de calcul, tableaux dynamiques compris. Détails et téléchargement d'essai sont sur la page du composant tableur HotXLS Delphi