Presque chaque élément du format binaire Excel historique est un enregistrement unique possédant un type sur deux octets et une longueur sur deux octets. Une cellule est un LABELSST ou un NUMBER. Une zone fusionnée est un MERGEDCELLS. Il est possible de lire la majeure partie d'une feuille en parcourant les enregistrements un à un et en effectuant un aiguillage selon leur type. Les tableaux croisés dynamiques (PivotTables) rompent ce rythme. Un unique tableau croisé dynamique n'est pas un simple enregistrement, c'est un petit programme constitué de dizaines d'enregistrements interdépendants répartis à deux endroits différents du même flux de document composé OLE. Les relations qui les unissent sont basées sur leur position, leur codage au niveau des bits et s'avèrent très strictes. C'est la structure que la plupart des lecteurs BIFF8 ignorent complètement ou conservent sous forme d'octets opaques, car en écrire un à partir de zéro exige de reproduire chaque référence croisée gérée par Excel lui-même.
La raison pour laquelle un tableau croisé dynamique est complexe est qu'il est en réalité constitué de deux éléments liés entre eux. Il y a le cache de pivotement (pivot cache), une capture autonome des données sources avec son propre sous-flux, et il y a la vue du tableau (table view), la disposition indiquant quels champs se trouvent sur quel axe. Le cache et la vue se référencent mutuellement par index. Si un seul de ces index est incorrect, le fichier s'ouvre sur une erreur de rafraîchissement ou une grille désespérément vide.
Le cache de pivotement possède son propre sous-flux
Le cache réside dans le flux global du classeur sous la forme d'un sous-flux BIFF complet, encadré par un enregistrement BOF dont le type de document est 0x0006 (la valeur désignant un cache de pivotement, par opposition à 0x0005 pour le classeur ou 0x0010 pour une feuille de calcul) et fermé par l'enregistrement EOF correspondant. Dans ce cadre, la structure est fixe. Un enregistrement SXDB constitue l'en-tête du cache. Il contient le nombre d'enregistrements, le nombre de champs du cache et l'identifiant de flux que la vue du tableau citera pour s'associer à ce cache. Chaque colonne source fournit ensuite un enregistrement de définition de champ SXFDB suivi d'un SXFDBType qui le classe, puis les valeurs uniques prises par cette colonne, émises sous la forme d'un enregistrement d'élément typé pour chaque valeur distincte.
Les enregistrements d'éléments constituent l'intérêt principal du cache. Une valeur textuelle devient un SXSTRING, une valeur numérique un SXNUM, une valeur logique un SXBOOLEAN et une formule en erreur un SXERR. Le cache ne stocke pas la grille source elle-même, il conserve les valeurs distinctes de chaque champ ainsi qu'une table d'index spécifiant, pour un enregistrement n, quel élément distinct a été pris par chaque champ. C'est pourquoi générer par code un tableau croisé dynamique ne se résume pas à copier des cellules. Vous devez parcourir la plage source, déduire le type de chaque champ selon ses valeurs, éliminer les doublons pour obtenir une liste d'éléments typés et enregistrer chaque ligne sous forme d'un n-uplet d'index d'éléments. HotXLS fait exactement cela : une colonne entièrement numérique produit des éléments SXNUM, une colonne textuelle mixte produit des éléments SXSTRING et les dates sont traitées sous forme de valeurs de série via le même chemin numérique.
Le format SXDBB et le codage des bits qui le rend intéressant
La table d'index par enregistrement est la partie technique la plus originale de toute la structure, et elle se trouve dans l'enregistrement SXDBB. Un encodage rudimentaire stockerait chaque index d'élément de champ sous la forme d'un mot de 16 bits. Excel procède autrement. Il compacte l'index de chaque champ dans le nombre exact de bits requis pour adresser ses éléments, pas plus. La largeur est de ceil(log2(itemCount + 1)) bits. Le + 1 est important : la valeur supplémentaire est une sentinelle signifiant "vide, aucune valeur pour ce champ dans cet enregistrement". Ainsi, un champ ayant trois éléments distincts doit représenter quatre états et occupe donc deux bits, et non l'unique bit que trois éléments seuls pourraient suggérer. Un champ ne contenant aucun élément n'occupe aucun bit et se voit ignoré lors du compactage.
Les bits d'un enregistrement sont concaténés sur tous les champs, puis l'enregistrement suivant commence sur une nouvelle limite d'octet. Les enregistrements sont alignés sur les octets et non compactés en bits bout à bout, ce qui rend l'accès direct à la table gérable au prix de quelques bits de remplissage (padding) par ligne. Le compactage dans un octet s'effectue en commençant par le bit de poids faible (LSB). Une fois ces deux règles admises, l'encodeur est un simple gestionnaire de flux de bits, et le décodeur est son reflet exact.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;La raison pour laquelle ce détail ne peut pas être négligé est la limite de 8224 octets pour un unique enregistrement BIFF. Chaque enregistrement de ce format, y compris les enregistrements de pivotement, doit stocker ses données dans un maximum de 8224 octets. Un cache de pivotement actif comportant des milliers de lignes sources dépassera ce seuil bien avant d'avoir émis chaque ligne. C'est pourquoi la table d'index est fractionnée. HotXLS limite le corps d'un unique SXDBB à 8220 octets (la limite de 8224 octets moins les quatre octets de l'en-tête de type et de longueur), divise ce total par la largeur en octets d'un enregistrement compacté pour connaître le nombre de lignes entières acceptées, puis émet autant d'enregistrements SXDBB de continuation que le nombre de lignes l'exige. Chaque continuation commence proprement sur une limite d'enregistrement, de sorte qu'aucune ligne n'est jamais tronquée entre deux enregistrements. Un lecteur connaissant la largeur en bits par enregistrement peut parcourir chaque SXDBB séquentiellement comme s'il s'agissait d'un unique tableau de bits continu.
La disposition de la vue : SXLI pour le corps, SXPI pour la page
Une fois le cache construit, la vue du tableau constitue la seconde partie. Son cœur est constitué des éléments de ligne d'axe (axis line items), c'est-à-dire les lignes du corps du pivot qui énumèrent chaque combinaison de valeurs de champs de ligne et de colonne dessinées par le tableau. Ceux-ci figurent dans les enregistrements SXLI (type d'enregistrement 0x00B5, décrit dans la note [MS-XLS] §2.4.275). Un enregistrement SXLI contient plusieurs lignes, à nouveau jusqu'à ce que la limite de 8224 octets impose un nouvel enregistrement, et emploie une astuce de compression : chaque ligne stocke uniquement sa différence par rapport à la ligne supérieure, exprimée sous forme de nombre de préfixes communs. Ainsi, un axe imbriqué ne répète pas les valeurs des champs externes sur chaque ligne. La ligne de total général et la première ligne de chaque enregistrement réinitialisent toujours ce compteur à zéro pour qu'un lecteur n'ait jamais à analyser l'enregistrement précédent pour reconstituer une ligne.
L'axe de page (les listes déroulantes de filtre situées au-dessus du tableau croisé dynamique) constitue un enregistrement distinct. SXPI (type d'enregistrement 0x00B6, [MS-XLS] §2.4.276) transporte une entrée de dix octets par champ de page : l'index de champ de pivotement isxvd, l'élément de cache sélectionné iCache, un mot de position ipos et un identifiant d'objet historique objId. La valeur iCache est celle à surveiller. Un champ de page affichant "(Tous)", ne filtrant rien, stocke la sentinelle 0x7FFD plutôt qu'un index réel d'élément. Un pivot généré par code s'ouvre avec chaque champ de page défini à "(Tous)" jusqu'à ce que l'appelant présélectionne un élément. L'index de cache de cet élément remplace alors la sentinelle et Excel s'ouvre avec le filtre appliqué. À côté de cela se trouvent les enregistrements de support décrivant les champs individuels et leur mise en forme : SXVD et SXVDEx pour les définitions de vues de champs, SXIVD pour les listes d'index de champs ordonnant chaque axe, et SXFormat pour le format des nombres, chacun référençant le cache auquel renvoient les lignes du corps.
Deux rédacteurs en un : les blocs bruts et le modèle typé
Il y a une raison structurelle pour laquelle HotXLS conserve deux chemins séparés pour écrire un tableau croisé dynamique, liée directement aux exigences de fidélité. Lorsqu'un classeur est lu depuis le disque, ses enregistrements de pivotement ont été générés par Excel ou un autre outil, et peuvent utiliser des variantes d'enregistrements, des particularités d'ordonnancement ou des enregistrements d'extension qu'un rédacteur tiers ne modélise entièrement. La seule opération sûre pour ces octets consiste à les restituer sans modification. Ainsi, un tableau croisé dynamique issu d'un fichier est marqué avec l'indicateur FromRawBlobs = True, et lors de l'enregistrement, le rédacteur réécrit les blocs d'enregistrements conservés à l'identique. Rien n'est régénéré ni réinterprété, et l'aller-retour d'ouverture et d'enregistrement reste identique à l'octet près.
Un tableau croisé dynamique créé par le programme correspond au cas de figure inverse. Il n'y a pas d'octets d'origine à préserver, seulement le modèle d'objets typés : un TXLSPivotCache avec ses champs et listes d'éléments, et un TXLSPivotTable avec ses affectations d'axes. Ce tableau est marqué par l'indicateur FromRawBlobs = False, et le rédacteur le sérialise intégralement en émettant un nouveau sous-flux de cache BOF = 0x0006, en compactant la table d'index SXDBB à partir des index d'éléments du modèle typé, et en disposant les enregistrements SXLI et SXPI selon la configuration des axes. Cet indicateur permet aux deux variantes de coexister dans un même classeur. Sans cela, le rédacteur devrait soit sacrifier l'intégrité des tableaux existants lus, soit refuser d'en générer de nouveaux. Les enregistrements d'extension spécifiques à un éditeur que comportait un tableau lu sont conservés sous forme d'enregistrements supplémentaires, accessibles via la liste SupplementalRecords du tableau, garantissant qu'un tableau inspecté via le modèle typé ne perde pas les éléments non décrits par le modèle.
Générer un tableau croisé dynamique en code
Tous les mécanismes décrits ci-dessus sont regroupés derrière un seul appel. AddPivotTable prend la plage source en notation A1, la cellule de destination où s'ancre le coin supérieur gauche du tableau, et un nom. Elle analyse la plage, l'examine pour déduire les types de champs et construire le cache (en réutilisant un cache existant si un autre tableau cible déjà la même plage), et renvoie un TXLSPivotTable typé avec un champ par colonne source, chaque champ étant initialement hors axe. Vous placez ensuite les champs sur les axes et choisissez une agrégation. La signature est exactement celle-ci, et le cache, le compactage SXDBB et les enregistrements de vue sont tous générés pour vous lors de l'enregistrement.
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;La première ligne de la plage source est lue comme l'en-tête qui nomme les champs du cache, de sorte que AddRowField('Region') cible une colonne par son en-tête plutôt que par sa position. Étant donné que le tableau renvoyé est un modèle typé avec FromRawBlobs = False, le rédacteur choisit la création à partir de zéro : il construit un cache autonome qui ne dépend pas de la présence continue de la plage source lors d'un rafraîchissement, ce qui est idéal si le tableau croisé dynamique doit être transmis à un destinataire susceptible de déplacer ou supprimer les données sources.
La lecture et la validation des enregistrements de pivotement et de cache d'un fichier que vous n'avez pas produit, y compris le chemin de conservation des blocs bruts, sont abordées dans le guide sur l'audit de classeurs et la conversion. Lorsque la plage source compte des dizaines de milliers de lignes et que le flux SXDBB s'étend sur de nombreuses extensions d'enregistrements, les techniques de nos notes sur les performances de classeurs volumineux évitent que la génération du cache ne monopolise le temps d'exécution. Les deux méthodes s'associent au rédacteur de pivot fourni avec le composant de feuille de calcul HotXLS pour Delphi et C++Builder, aux côtés des API de cellules, formules, graphiques et mises en forme présentées par échange ailleurs sur ce blog.