HotXLS, la bibliothèque Excel native pour Delphi et C++Builder, lit la valeur qu’Excel a déjà enregistrée à côté d’une formule par le biais de TryGetCachedFormulaValue et de IXLSFormulaCacheReader. Aucun de ces deux points d’entrée n’appelle le calculateur, ne décompile les jetons de formule, ne met à jour l’état sale ni n’écrit quoi que ce soit dans le modèle, si bien qu’un classeur que vous ne faites que lire reste exactement comme vous l’avez ouvert
Le scénario qui motive tout cela est terne et extrêmement courant. Un travail nocturne ouvre quelques centaines de classeurs produits par quelqu’un d’autre, extrait une colonne de totaux de chacun et pousse les nombres vers un entrepôt de données. Les totaux sont déjà dans les fichiers — Excel les a calculés et enregistrés. Pourtant, dès que le travail demande la valeur d’une cellule à formule, une bibliothèque qui n’a qu’une seule réponse à cette question construit un graphe de dépendances et évalue la feuille entière, et un travail qui devrait être limité par les E/S se transforme en banc d’essai de calcul
Pourquoi la lecture d’une cellule à formule coûte-t-elle un recalcul complet ?
Parce qu’un accesseur de valeur sur une cellule à formule est une demande de produire une valeur, et que la seule façon universellement correcte d’en produire une est d’évaluer la formule. C’est le bon choix par défaut pour une application qui édite des classeurs, et le mauvais pour un pipeline qui les extrait. Pire, l’évaluation n’est pas sans effets de bord : elle réécrit les résultats dans les cellules, elle bascule des drapeaux sales, et elle peut résoudre différemment de l’application productrice quand une fonction n’est pas prise en charge ou qu’une référence externe est cassée. Un travail que vous avez décrit à votre équipe d’exploitation comme en lecture seule produit tranquillement un classeur qui ne correspond plus à celui sur disque, et si quoi que ce soit l’enregistre plus tard, le fichier sur disque change aussi
La lecture de valeurs en cache est l’autre moitié du contrat. Elle répond à une question plus étroite — qu’est-ce que l’application productrice a stocké ici ? — et refuse de répondre à autre chose. Quand vous voulez vraiment des nombres frais, HotXLS vous offre toujours le recalcul incrémental piloté par un graphe de dépendances ; le point est que l’extraction et l’évaluation devraient être deux appels distincts, pas un seul appel avec deux humeurs
Trois faits orthogonaux sur une seule cellule
La conclusion d’abord : une valeur de formule en cache porte trois faits indépendants, et les faire tenir dans un unique Variant perd des informations dont vous avez besoin. TXLSFormulaCacheInfo les tient séparés comme State, Kind et Value. TXLSFormulaCacheState enregistre la provenance sur cinq cas — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated et xlfcsInvalidated — tandis que TXLSFormulaCacheValueKind classe la charge utile en xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean ou xlfcvError. Cette séparation est ce qui permet de signaler la présence honnêtement : un blanc en cache, une chaîne vide en cache, un False en cache, un zéro en cache et une erreur en cache sont tous de vraies valeurs, si bien que la présence ne peut jamais être déduite de VarIsEmpty ou de VarIsNull. TryGetCachedFormulaValue renvoie True seulement pour xlfcsLoaded et xlfcsCalculated, et remplit quand même un état diagnostiquable quand elle renvoie False
var
Book: TXLSXWorkbook;
Info: TXLSFormulaCacheInfo;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('quarterly-model.xlsx');
// SheetIndex, Row et Col sont tous en base un ici
if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
Writeln('cached value: ', VarToStr(Info.Value))
else
Writeln('no usable cache, state ordinal ', Ord(Info.State));
finally
Book.Free;
end;
end;Pourquoi la valeur en cache manque-t-elle ?
Il y a exactement quatre raisons pour lesquelles TryGetCachedFormulaValue renvoie False, et l’état vous dit laquelle s’applique. xlfcsNotFormula signifie que la cellule porte un littéral ou rien du tout, et des coordonnées hors plage se replient dans la même réponse. xlfcsMissing signifie que la cellule est vraiment une formule mais que le producteur n’a enregistré aucune charge utile de valeur pour elle — une issue courante quand un générateur écrit des formules et laisse Excel remplir les résultats à la première ouverture. xlfcsInvalidated signifie que le texte de formule a été remplacé après le chargement, si bien que la valeur qui s’y trouvait décrit une expression qui n’existe plus. xlfcsCalculated, en revanche, est un cas de succès : il marque une valeur que votre propre code ou l’évaluateur HotXLS a produite pendant cette session, par opposition à xlfcsLoaded, qui vient du fichier
L’honnêteté face à un cache manquant compte plus que son maquillage. HotXLS refuse d’inventer une valeur, et à l’enregistrement il est tout aussi strict — seuls xlfcsLoaded et xlfcsCalculated émettent une valeur en cache, tandis que xlfcsMissing et xlfcsInvalidated écrivent la formule seule plutôt que de figer un nombre périmé dans le fichier. Cela vous laisse trois réponses saines dans un pipeline : sauter la ligne et consigner le trou, recalculer délibérément ce classeur-là et accepter le coût, ou évaluer puis rapprocher. Si le nombre évalué contredit ce que l’application productrice aurait écrit, le traceur d’évaluation de formules est l’outil pour découvrir où les deux calculs divergent, plutôt que de deviner d’après le résultat
Un lecteur unique à travers les moteurs classique, OOXML et ODF
Un pipeline ne devrait pas se soucier de savoir si le fichier qu’il vient d’ouvrir était du BIFF, de l’OOXML ou de l’ODF. IXLSFormulaCacheReader est le point d’entrée unique en lecture seule pour les trois : TXLSWorkbook.CreateFormulaCacheReader comme TXLSXWorkbook.CreateFormulaCacheReader renvoient un adaptateur léger par-dessus l’interrogation de cellules en structure creuse que chaque moteur utilise déjà, avec des coordonnées feuille, ligne et colonne identiques en base un. Les classes de classeur ne mettent délibérément pas en œuvre l’interface elles-mêmes — une référence d’interface vers le classeur changerait sa sémantique de propriété et laisserait les appelants se faufiler au-delà du bail de durée de vie. À la place, détruire le classeur efface le pointeur brut à l’intérieur de ce bail, et tout lecteur encore détenu par votre code lève EXLSFormulaCacheReaderInvalidated à sa requête suivante au lieu de déréférencer de la mémoire libérée. C’est une vérification de durée de vie en échec rapide, pas une garantie de concurrence
var
Reader: IXLSFormulaCacheReader;
Info: TXLSFormulaCacheInfo;
Row, Missing, Errors: Integer;
Total: Double;
begin
Reader := Book.CreateFormulaCacheReader;
Total := 0;
Missing := 0;
Errors := 0;
for Row := 2 to LastRow do
if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
begin
case Info.Kind of
xlfcvNumber: Total := Total + Double(Info.Value);
xlfcvError: Inc(Errors);
end;
end
else if Info.State = xlfcsMissing then
Inc(Missing);
// Aucun calculateur n'a tourné, aucun drapeau sale n'a bougé, Book est inchangé
end;Où résident réellement les octets en cache
Pour les fichiers .xls classiques, le cache est le champ FormulaValue de l’enregistrement Formula, huit octets décrits par [MS-XLS] §2.5.133. Quand le mot de poids fort vaut $FFFF, la charge utile n’est pas un double IEEE 754 mais un variant étiqueté, et il est facile de se tromper subtilement sur sa disposition : le type de variant se trouve dans val[0] et la charge utile booléenne ou BErr se trouve dans val[2], avec val[1] indéfini. HotXLS lisait auparavant la charge utile depuis val[1], le genre de décalage de un qui ne se manifeste que sur les fichiers précis qui mettent en cache un booléen ou une erreur plutôt qu’un nombre. Le lecteur et l’écrivain de formules partagées s’accordent maintenant sur les mêmes décalages, si bien qu’un TRUE en cache survit intact à un chargement et à un enregistrement au lieu de se dégrader en bruit
La fidélité des types dans les formats de paquet est un problème séparé avec son propre piège. En OOXML, la valeur en cache se suspend à l’élément c sous forme de <v>, l’attribut t nommant le type selon ECMA-376 Partie 1 §18.3.1.4. HotXLS lit t="e" directement dans un Variant varError et le ramène vers le texte d’erreur standard à l’enregistrement, si bien que les erreurs ne se déguisent jamais en entiers ordinaires — mais la RTL Delphi ne vous aidera pas ici, car VarAsType(Integer, varError) lève une exception de conversion. La construction qui fonctionne initialise TVarData.VType et TVarData.VError directement. Les dates suivent la même discipline dans l’autre sens : t="d" et le type de valeur de date ODF sont des déclarations de type explicites et deviennent varDate, tandis qu’un cache numérique BIFF ne porte aucun drapeau de date et reste donc un Double. HotXLS ne devine jamais une date d’après le format de nombre d’une cellule, car le format de nombre est de la présentation et le cache est de la donnée. L’ODF ajoute un cas de plus qui vaut la peine d’être connu — office:value-type="void" exprime un cache présent mais ne portant aucune valeur, et comme l’ODF n’a pas de type de valeur d’erreur, un texte ressemblant à une erreur est préservé comme texte plutôt que promu en erreur
function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
case Info.State of
xlfcsNotFormula: Result := 'not a formula cell';
xlfcsMissing: Result := 'formula stored with no cached value';
xlfcsInvalidated: Result := 'formula replaced since load';
else
case Info.Kind of
xlfcvError: Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
xlfcvBoolean: Result := BoolToStr(Info.Value, True);
xlfcvNumber: Result := FloatToStr(Double(Info.Value));
xlfcvString: Result := VarToStr(Info.Value);
else
Result := 'present but blank';
end;
end;
end;Les formules partagées partagent-elles leurs valeurs en cache ?
Non, et supposer le contraire est la façon dont un balayage finit par signaler le même nombre pour une colonne entière. Une formule partagée OOXML partage seulement l’expression de formule et l’optimisation de stockage ; chaque cellule membre possède toujours son propre <v>. HotXLS ne propage donc jamais le cache du membre racine vers un suiveur arrivé sans valeur, et un suiveur chargé comme xlfcsMissing signale toujours xlfcsMissing après un enregistrement et une réouverture. Si vous travaillez d’abord sur la façon dont le groupe est stocké et étendu, la mécanique de l’attribut si de la formule partagée et de son expansion est traitée séparément ; pour la lecture de cache, la règle se réduit à une ligne — interrogez chaque cellule, ne faites confiance à rien que vous n’ayez pas interrogé
La lecture de valeurs en cache, le lecteur unifié multi-moteurs et le moteur de recalcul que vous pouvez choisir de ne pas appeler sont tous livrés en standard dans le composant tableur HotXLS pour Delphi, utilisable depuis Delphi et C++Builder, sans dépendance envers Excel ni envers aucun serveur d’automatisation OLE ; la page produit porte la référence API complète des points d’entrée de classeur et de lecteur montrés ici