Un suiveur de formule partagée en XLSX ne porte aucun texte de formule. Son élément <f t="shared" si="N"/> pointe vers une cellule maître ailleurs dans la feuille, et le lecteur doit reconstruire le texte en décalant la formule maître selon la différence de ligne et de colonne. Le composant HotXLS pour Delphi et C++Builder effectue cette expansion à l'ouverture, si bien que chaque suiveur rapporte une formule complète
Si vous avez déjà chargé un XLSX réel dans une bibliothèque tierce et constaté qu'une colonne de mille formules a du texte dans exactement une cellule et des chaînes vides dans les 999 autres, vous avez rencontré cette fonctionnalité du mauvais côté. Rien n'est corrompu. Le fichier fait ce qu'ECMA-376 lui permet de faire, et le lecteur s'est simplement arrêté là où le XML s'est arrêté
Pourquoi la cellule de formule partagée est-elle vide ?
Parce que le format stocke délibérément la formule une seule fois. Dans ECMA-376 Part 1 et ISO/IEC 29500-1, l'élément <f> (§18.3.1.40) porte un attribut t de type ST_CellFormulaType, et la valeur shared signifie que cette cellule participe à un groupe identifié par l'attribut si. Exactement une cellule du groupe, le maître, porte aussi un attribut ref donnant la plage à laquelle le groupe s'applique, et seule cette cellule porte le texte de la formule comme contenu d'élément. Chaque autre cellule du groupe est un suiveur. Elle répète t="shared" et le même si, et son contenu d'élément est vide. Excel écrit ces groupes de façon agressive, car un remplissage vers le bas sur une colonne de 200 000 lignes se réduit de 200 000 chaînes de formule à une seule chaîne plus 199 999 minuscules éléments d'espace réservé. L'économie est réelle et le coût retombe entièrement sur le lecteur : sans expansion, le suiveur n'a aucun sens par lui-même
Le décalage est une traduction, pas une copie de texte
HotXLS résout un suiveur en localisant le maître enregistré sous le même si, en calculant le delta de ligne et de colonne entre l'ancrage du maître et la cellule courante, et en traduisant chaque référence de la formule maître de ce delta. Les dimensions relatives se déplacent, les dimensions absolues ne bougent pas, et les références mixtes ne déplacent que leur moitié non absolue. Les littéraux de chaîne sont entièrement ignorés, si bien qu'une formule contenant par hasard le texte "A1" conserve ce texte inchangé dans chaque suiveur
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;L'attribut ref est une porte, pas une décoration. Un suiveur dont les coordonnées tombent en dehors de la plage applicable du maître n'est pas développé, car le fichier fait alors une affirmation que le groupe ne soutient pas. De même, quand un décalage pousserait une référence au-dessus de la ligne un ou à gauche de la colonne A, HotXLS émet #REF! pour ce jeton plutôt que de le tronquer silencieusement, ce qu'Excel lui-même produirait pour la même modification. Cette traduction est cousine proche, mais pas identique, de la réécriture de référence qui se produit quand on insère ou supprime des lignes. Ce chemin a ses propres règles sur ce que devient une plage quand une modification la traverse, et il est décrit séparément dans l'article sur l'ajustement des références de formule lors d'insertion et de suppression. L'expansion partagée est plus simple : c'est un décalage pur depuis un ancrage connu, appliqué une seule fois, au moment de l'analyse
Quelles formes de référence le décaleur doit-il couvrir ?
Toutes, sinon l'expansion est un bogue de perte de données déguisé. Un décaleur naïf qui ne comprend que A1 et A1:B2 corrompra ou abandonnera les formes plus exotiques, et les classeurs réels en sont pleins. Le traducteur de formule partagée de HotXLS reconnaît toute la famille A1 avant de décider quoi déplacer. Les références de classeur externe telles que [Book.xlsx]Sheet1!A1 et les références 3D telles que Sheet1:Sheet3!A1 conservent leur préfixe intact tandis que la référence de cellule finale se décale. Les noms de feuille entre guillemets survivent, y compris le cas retors où la feuille est littéralement nommée A1, si bien que 'A1'!A1 ne décale que la partie après le point d'exclamation. La colonne entière A:A déplace sa dimension de colonne et rien d'autre ; la ligne entière 1:1 déplace sa dimension de ligne et rien d'autre ; $A:$A ne bouge pas du tout. Les références de tableau structurées comme Table[A1] sont laissées intactes, car la partie entre crochets est un nom de colonne, pas une coordonnée
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Les noms de fonction sont le piège discret ici. Un scanner de jetons qui saisit des lettres suivies de chiffres réécrira sans se poser de questions LOG10 en LOG11 une ligne plus bas. HotXLS exige une frontière de référence avant et après un jeton candidat, si bien qu'un identifiant qui se poursuit par une lettre, un chiffre, un tiret bas, un point, ou une parenthèse ouvrante n'est pas une référence de cellule. Si vous travaillez dans l'autre famille de notation, le même problème de frontière se manifeste différemment, et l'article sur la notation R1C1 couvre où les deux modèles divergent
Pourquoi un élément f auto-fermant avale-t-il la valeur suivante ?
Parce qu'un élément auto-fermant ne produit aucun événement de fin d'élément. C'est le bogue le plus coûteux de toute la fonctionnalité, et il n'est spécifique à aucun analyseur XML en particulier. Dans TXMLReader, <f t="shared" si="4"/> déclenche exactement un événement Element avec IsEmptyElement à True, et ne déclenche jamais l'EndElement correspondant. Un analyseur qui ne referme son état de capture de formule qu'à l'EndElement reste donc à l'intérieur de la formule, et le prochain texte qu'il voit, qui est le résultat mis en cache à l'intérieur de <v>, se retrouve ajouté au tampon de formule. Pire, l'état survit à la frontière de cellule, si bien que la cellule suivante qui possède un vrai <f> voit son texte de formule absorbé par la cellule précédente. La correction consiste à terminer l'état de formule dès l'événement Element lui-même chaque fois que IsEmptyElement vaut True, et à exécuter toute la résolution du suiveur à cet endroit plutôt que d'attendre. Cela signifie lire t, si, ref, aca et ca depuis les attributs, appliquer l'expansion partagée, écrire les attributs de recalcul sur la cellule, et effacer l'état partagé, le tout à l'intérieur de la branche qui gère l'élément vide. Notez que le format autorise les deux orthographes, <f t="shared" si="4"/> et <f t="shared" si="4"></f>, et la seconde déclenche bien un EndElement. Un lecteur correct doit traiter la paire de façon identique, ce qui explique pourquoi HotXLS couvre les deux orthographes dans le même fichier de non-régression
Valeurs si éparses, non ordonnées, et la file d'attente en suspens
L'attribut si est un entier non signé fourni par le fichier, pas une position de tableau que vous contrôlez. Rien dans le schéma n'exige que les index partagés soient denses, qu'ils commencent à zéro, ou qu'ils apparaissent en ordre croissant, et rien n'empêche un fichier hostile ou simplement étrange d'utiliser si="4294967290" sur la première cellule. Dimensionner un tableau de recherche à partir du plus grand si observé est donc un vecteur d'épuisement mémoire, pas une optimisation. HotXLS conserve à la place, sur le chemin d'ouverture du classeur, une table éparse triée : les groupes partagés sont enregistrés sous leur clé entière dans un TStringList trié, ce qui rend la recherche binaire sur le nombre de groupes réellement existant, sans rapport avec la taille numérique des index. L'ordre est la seconde moitié du problème. Un maître précède normalement ses suiveurs dans l'ordre du document, mais c'est une convention plutôt qu'une règle, si bien que tout suiveur qui ne peut pas résoudre son si au moment où il est analysé va dans une file d'attente en suspens. Quand la feuille se termine, la file est rejouée contre la table désormais complète, et les maîtres arrivés tardivement résolvent leurs orphelins. Les cellules qui ne trouvent jamais de maître conservent une formule vide, ce qui est le résultat honnête pour un fichier référençant un groupe qu'il n'a jamais défini
Développer les formules partagées sans charger le classeur
Les lecteurs en flux font face à la même exigence sous un budget mémoire bien plus serré, et ils la résolvent avec une table locale à la feuille. TXLSDirectReader et TXLSRowCursor développent tous deux les suiveurs en formules complètes par cellule tout en préservant leur comportement de mémoire bornée et de projection, si bien qu'une passe à sens unique sur une feuille de 300 Mo vous fournit quand même un vrai texte de formule
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Deux contraintes découlent de cette conception. Premièrement, la projection ne peut jamais sauter le maître. Un filtre de ligne défini avec FirstRow et LastRow, ou un filtre de colonne construit avec IncludeColumn, peut éviter d'émettre la cellule maître vers votre callback, mais l'analyseur doit quand même enregistrer son si, ses coordonnées d'ancrage, sa plage applicable et son texte de formule, sinon chaque suiveur à l'intérieur de la projection se résout à rien. Seul le travail côté suiveur, le décalage et le décodage de valeur, peut être sauté sans risque. Deuxièmement, la table est propre à chaque feuille et sa durée de vie doit être gérée explicitement : TXLSRowCursor conserve une instance pour la durée d'une passe sur une feuille et la vide au redémarrage, au changement de feuille, en fin de fichier, en cas d'exception, et à la fermeture, si bien qu'un groupe défini sur la feuille un ne peut jamais fuir vers la feuille deux. Comme le chemin en flux est une boucle critique, il utilise une table de hachage d'entiers à adressage ouvert plutôt que la table de chaînes triée, ce qui évite une conversion entier-vers-chaîne par cellule
Ce qui se passe à la sauvegarde, et où sont les limites
Une fois qu'un suiveur a été développé, c'est une formule ordinaire, et HotXLS la réécrit sous forme d'un élément <f> indépendant sans t="shared" ni si. L'aller-retour est stable et les résultats <v> mis en cache survivent, mais la sortie est plus volumineuse que l'entrée pour une feuille fortement partagée, et le regroupement créé par Excel n'est pas reconstruit à la sauvegarde. Si la fidélité au niveau des octets des groupes partagés compte plus pour vous qu'avoir du vrai texte de formule dans chaque cellule, c'est le compromis que vous acceptez. Le côté XLS est différent, en passant : l'enregistrement BIFF8 SHRFMLA a son propre encodage et son propre écrivain, avec un commutateur de groupe partagé sur le classeur
Deux choses apparentées ne sont explicitement pas des formules partagées, même si elles partagent l'élément <f>. Les formules matricielles CSE historiques utilisent t="array" avec un ref couvrant la plage ancrée, et les tableaux dynamiques utilisent la même orthographe t="array" mais sont identifiés par un attribut cm qui chaîne via cellMetadata jusqu'à un enregistrement XLDAPR. Traiter une cellule de débordement de tableau dynamique comme un suiveur partagé ou CSE est un véritable bogue de correction, et la séparation est couverte dans l'article sur les tableaux dynamiques et les formules de débordement. Lisez les trois cas comme trois analyseurs qui se trouvent partager un nom de balise, et le code reste honnête
L'expansion de formule partagée, les lecteurs en flux et le traducteur de référence décrits ici font partie du composant Excel HotXLS pour Delphi et C++Builder ; la page produit propose la référence complète de l'API de formules et de lecture directe, y compris les propriétés de projection utilisées ci-dessus