Slicers de table Excel en Delphi : l'aller-retour HotXLS

HotXLS crée des slicers de table Excel depuis Delphi et C++Builder avec un seul appel — Worksheet.Slicers.Add(Table, ColumnName) — et écrit les huit emplacements du paquet dont un slicer a besoin : les parties slicer et slicerCache, les deux overrides de type de contenu, les relations de feuille de calcul et de classeur avec leurs blocs extLst correspondants, et l'ancre de dessin

Un slicer ressemble à un contrôle que vous posez sur une feuille. Dans le fichier, ce n'est rien de tel. C'est un enregistrement distribué étalé sur deux nouvelles parties, deux graphes de relations, trois blocs d'extension et une ancre de dessin, et l'ensemble est validé comme une unité quand Excel ouvre le paquet. Manquez une pièce et vous n'obtenez pas un slicer dégradé ; vous obtenez la boîte de dialogue de réparation, suivie d'un classeur avec le slicer silencieusement retiré. Ce mode d'échec est ce qui rend les slicers pénibles à émettre à la main, et c'est pourquoi l'ingénierie intéressante ici est de la comptabilité plutôt que du rendu

Pourquoi un slicer touche-t-il huit endroits du paquet ?

Parce qu'un slicer est trois concepts séparés qu'OPC force dans trois foyers séparés : l'état de filtre (à l'échelle du classeur), la vue (à l'échelle de la feuille) et la forme (à l'échelle du dessin). HotXLS écrit exactement huit points d'atterrissage par slicer de table, et chacun d'eux porte une charge

• xl/slicers/slicerN.xml — une partie par feuille de calcul, contenant chaque vue <slicer> de cette feuille dans l'espace de noms http://schemas.microsoft.com/office/spreadsheetml/2009/9/main
• xl/slicerCaches/slicerCacheN.xml — au niveau du classeur, une par colonne de table tranchée, portant la liaison réelle
• un <Override> dans [Content_Types].xml pour chacune de ces deux parties, utilisant application/vnd.ms-excel.slicer+xml et application/vnd.ms-excel.slicerCache+xml (ECMA-376 Partie 2 exige un type de contenu pour chaque partie, et Excel refuse le paquet sans eux)
• une relation de feuille de calcul du type .../office/2007/relationships/slicer, plus une extension <x14:slicerList> dans le extLst de la feuille qui pointe vers ce rid
• une relation de classeur du type .../office/2007/relationships/slicerCache, plus une extension <x15:slicerCaches> dans le extLst du classeur listant les mêmes rids
• une ancre mc:AlternateContent dans le dessin de la feuille qui donne au slicer sa position et sa taille
• un bloc <x15:tableSlicerCache> dans la partie de cache qui le lie à un id de table et un id de colonne de table

Les blocs d'extension utilisent le mécanisme extLst standard d'ECMA-376 Partie 1 §18.2.10 : chaque <ext> porte un attribut uri qui identifie la fonctionnalité, et les lecteurs qui ne reconnaissent pas le GUID sautent le bloc. HotXLS distingue les slicers de table des slicers de pivot par ce GUID seul — {3A4CF648-6AED-40f4-86FF-DC5316D8AED3} pour le slicerList de table, {A8765BA9-456A-4dab-B4F3-ACF838C121DE} pour celui de pivot, et en correspondance {46BE6895-7355-4a93-B00E-2C351335B9C9} et {BBE1A952-AA13-448e-AADC-164F8A28A991} pour les deux extensions slicerCaches du classeur. La liaison de table elle-même vit sous {2F2917AC-EB37-4324-AD4E-5DD8C200BD13}. Ces constantes sont exposées dans l'unité comme XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 et XlsxExtUriTableSlicerCache, donc rien dans le chemin d'écriture ne porte de chaîne magique

Quelles parties peuvent rejouer à l'identique, et lesquelles doivent être reconstruites ?

HotXLS répond avec une seule règle : une partie ne peut rejouer ses octets d'origine que si rien de ce qu'elle référence ne peut être renuméroté pendant la sauvegarde. Les caches de slicer de table échouent à ce test, donc HotXLS les reconstruit depuis le modèle à chaque sauvegarde, sans condition — même quand l'utilisateur n'a jamais touché au slicer. La raison, c'est tableId : l'élément <x15:tableSlicerCache> identifie sa table par l'attribut id de <table>, et HotXLS renumérote les ids de tables globalement au classeur quand il écrit. Une implémentation précoce stockait la partie de cache en octets bruts à l'ouverture et les repoussait à la sauvegarde, ce qui est la bonne stratégie pour la plupart des parties opaques. Elle produisait des fichiers qui s'ouvraient proprement dans le cas courant et déclenchaient une réparation dans le cas intéressant : ajoutez une table sur une feuille antérieure, sauvegardez, et le cache pointe maintenant vers un id de table qui appartient à autre chose, ou à rien du tout. Le bug est invisible dans une régression simple d'ouverture-sauvegarde parce que les ids ne bougent pas par hasard

Les caches en forme de pivot prennent la route opposée. Leur contenu est <pivotTables> plus un bloc <x14:data> décrivant les éléments en cache, l'état de tri et les indices d'éléments — une structure sans renumérotation côté HotXLS, et chère à modéliser fidèlement. Ceux-là sont gardés en octets bruts et rejoués exactement, avec TXLSXSlicerCache.IsPivot et IsTableCache pour distinguer les deux. Les vues de slicer suivent une troisième politique, suivie par salissure : un élément <slicer> intact et son ancre de dessin rejouent à l'identique, et dès qu'une vue de la feuille est modifiée, toute la partie de feuille est régénérée depuis le modèle. C'est la forme générale du jugement — rejeu à l'identique quand les références sont stables, reconstruction depuis le modèle quand l'écrivain réassigne l'identité, suivi de salissure quand les deux sont acceptables — et elle s'applique bien au-delà des slicers, comme le montrent en profondeur les notes d'aller-retour sans perte sur les thèmes, les blocs extLst et calcChain

Créer un slicer de table depuis Delphi

TXLSXSlicers.Add est toute l'API de création. Étant donnés un TXLSXTable et un nom de colonne, il matérialise les ids de colonnes de table, trouve ou crée le TXLSXSlicerCache de support, réserve le nom défini correspondant, et ancre une vue à droite de la table. La surcharge à deux arguments choisit le placement ; celle à six arguments prend une boîte inclusive explicite en base 1

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Slicer: TXLSXSlicer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[0];

    // Placement par défaut : deux colonnes à l'écart de la table,
    // 13 rangées de haut à la hauteur de rangée par défaut de 19 pt
    Slicer := Sheet.Slicers.Add(Sheet.Tables[0], 'Region');
    Slicer.Caption   := 'Sales region';
    Slicer.StyleName := 'SlicerStyleLight1';
    Slicer.ColumnCount := 2;

    Book.SaveAs('sales-sliced.xlsx');
  finally
    Book.Free;
  end;
end;

Deux règles de nommage valent la peine d'être connues parce que ce sont des conventions Excel plutôt que des inventions de HotXLS. Le cache est nommé Slicer_<column> avec les caractères non identifiants repliés en tirets bas, et les collisions ajoutent un suffixe numérique — Slicer_Region, puis Slicer_Region1, et ainsi de suite. Et chaque nom de cache est aussi réservé comme nom défini du classeur dont la formule est #N/A, ce que fait TXLSXSlicers.ReserveCacheDefinedName. Excel crée ce nom lui-même, et cela compte : les noms de caches et les noms définis partagent un seul espace de noms, donc un classeur qui a déjà un nom appelé Slicer_Region entrerait sinon en collision à la prochaine sauvegarde. Si vous construisez des tables et des validations ensemble, les interactions de noms définis dans l'article sur la validation des données, AutoFilter et les tables couvrent les cas voisins. Le partage suit la même règle qu'Excel. TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) est consulté avant la création d'un nouveau cache, donc deux vues de slicer sur la même colonne de table — une sur la feuille de tableau de bord, une sur la feuille de détail — partagent une seule partie de cache et restent synchronisées en filtres. Supprimer une vue exécute la vérification inverse à travers chaque feuille du classeur et laisse tomber le cache plus son nom #N/A réservé seulement quand la dernière vue qui le référence a disparu

Comment les ids de relations restent-ils stables aux deux bouts ?

Ils restent stables parce que HotXLS calcule le rid du slicer en rejouant l'ordre exact dans lequel il émet chaque autre relation, et ajoute les nouvelles relations à la queue. Rien de déjà présent dans le fichier ne bouge. Cela sonne comme de la sur-ingénierie jusqu'à ce que vous ayez débuggé l'alternative. Les ids de relations dans un .rels de feuille de calcul sont référencés depuis l'intérieur de la partie de feuille — par les hyperliens, le dessin, les commentaires, les parties de tables — et ECMA-376 Partie 2 les résout positionnellement dans le graphe de relations, pas par une identité stable. Si l'écrivain insère la relation du slicer au milieu, chaque référence après elle se décale d'un, et la feuille se met tranquillement à pointer vers la mauvaise partie. Donc XlsxWorksheetSlicerRelNumber parcourt toute la chaîne déterministe — hyperliens externes, puis le VML des commentaires, les commentaires, les images d'en-tête/pied, les commentaires en fil, le dessin, le fond de feuille, les paramètres d'imprimante, les tables, les tableaux croisés dynamiques, les tables de requêtes, les cellules uniques de tables — et seulement ensuite revendique le numéro suivant pour le slicer. XlsxWorkbookSlicerCacheFirstRid fait pareil côté classeur : feuilles, sharedStrings, styles, VBA, liens externes, thème, connexions, caches de pivot, person, calcChain, métadonnées de feuilles, plans XML, XML personnalisé, puis la série slicerCache. Le problème général est traité dans le morceau sur la résolution des relations OPC. L'extension côté classeur est ensuite construite à partir de ces nombres par XlsxBuildWorkbookSlicerCachesExtXml, et si le fichier source avait déjà des blocs d'extension slicerCaches, ils sont d'abord retirés du extLst brut, pour que le modèle les ré-émette contre les rids courants au lieu de laisser une copie périmée

L'ancre AlternateContent et son repli hérité

L'ancre de dessin est le seul endroit où le balisage du slicer devient franchement étrange, et l'étrangeté est délibérée. HotXLS émet la forme dans un élément mc:AlternateContent selon ECMA-376 Partie 3, avec une branche Choice qu'Excel 2013 et ultérieur prend et une branche Fallback pour tout ce qui est plus ancien

// Ce que XlsxSlicerAnchorPayloadXml produit, structurellement :
//
//   <mc:AlternateContent>
//     <mc:Choice xmlns:sle15=".../drawing/2012/slicer" Requires="sle15">
//       <xdr:graphicFrame>
//         <a:graphicData uri=".../drawing/2010/slicer">
//           <sle:slicer xmlns:sle=".../drawing/2010/slicer" name="Region"/>
//         </a:graphicData>
//       </xdr:graphicFrame>
//     </mc:Choice>
//     <mc:Fallback><xdr:sp>...notice shape...</xdr:sp></mc:Fallback>
//   </mc:AlternateContent>

Regardez les deux espaces de noms. Le jeton Requires="sle15" se résout vers l'espace de noms de slicer de 2012, qui est la porte de version — un lecteur qui ne comprend pas les slicers de 2012 doit sauter toute la branche Choice. Mais le graphicData/@uri et l'élément sle:slicer dedans utilisent l'espace de noms 2010, parce que c'est l'identifiant côté dessin que le cadre graphique a toujours utilisé. Les prendre à l'envers produit un fichier qu'Excel ouvre sans se plaindre puis rend comme un cadre vide, un symptôme mémorablement peu utile. La branche Fallback contient un rectangle simple avec un corps de texte expliquant que les slicers de table ne sont pas supportés dans cette version — c'est ce qu'un lecteur de l'ère 2007 dessine à la place du contrôle, et c'est pourquoi un vieil Excel peut faire l'aller-retour du classeur sans rien détruire. TXLSXSlicer expose la géométrie comme une boîte inclusive en base 1 via SetBounds(ARow1, ACol1, ARow2, ACol2) et les propriétés individuelles FromRow / ToCol, plus AnchorType pour le comportement habituel de déplacement et redimensionnement avec les cellules. HasAnchor vaut False pour un cas précis : un slicer imbriqué dans une forme de groupe. Ceux-là rejouent à l'identique avec leur ancre de groupe, et HotXLS ne synthétise délibérément pas de seconde ancre pour eux, puisque cela émettrait la forme deux fois

Modifier un slicer existant

var
  Slicer: TXLSXSlicer;
  Cache: TXLSXSlicerCache;
begin
  Slicer := Sheet.Slicers.FindByName('Region');
  if Slicer <> nil then
  begin
    Slicer.SetBounds(2, 8, 16, 11);      // base 1, inclusif
    Slicer.ShowCaption := 1;             // -1 = attribut omis
    Slicer.LockedPosition := 1;
    Slicer.RowHeightEmu := 19 * XlsxEmuPerPoint;   // 241300 EMU

    Cache := Slicer.Cache;
    if Cache <> nil then
    begin
      Cache.SortOrder   := 'descending';
      Cache.CrossFilter := 'showItemsWithNoData';
    end;
  end;

  // Retirer la dernière vue sur une colonne laisse tomber son cache
  // et le nom défini #N/A réservé avec lui
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Les propriétés entières à trois états méritent une note, puisqu'elles ressemblent à un accident de modélisation et n'en sont pas un. ShowCaption, LockedPosition, ColumnCount, StartItem et CustomListSort utilisent toutes -1 pour dire que l'attribut était absent dans le fichier source et doit rester absent à l'écriture, avec 0 et 1 comme valeurs explicites. Excel traite l'absence et le défaut explicite différemment à quelques endroits, et préserver cette distinction est ce qui garde propre un diff d'octets d'une sauvegarde intacte. RowHeight et ColumnWidth sont gardées comme chaînes brutes pour une raison voisine : Excel écrit des décimales dans ces attributs EMU, et les reparser à travers un entier perdrait de la précision à l'aller-retour. RowHeightEmu est l'accesseur entier de commodité sur le même stockage, avec XlsxSlicerDefaultRowHeight à 241300 EMU — 19 points au ratio XlsxEmuPerPoint de 12700

Où cela s'arrête

Deux limites honnêtes. D'abord, HotXLS modélise complètement le slicer de table mais ne modélise pas la création de slicers de pivot — vous pouvez ouvrir un classeur contenant des slicers de pivot, lire leur identité, les garder intacts à travers une sauvegarde et les supprimer, mais le côté cache rejoue en octets et il n'y a pas d'API pour en construire un de zéro. Ensuite, un slicer ne filtre rien tout seul. C'est une UI sur une colonne de table, et l'état de rangées visibles qu'il pilote est du côté d'Excel ; HotXLS écrit le contrôle et la liaison, pas un résultat de filtre calculé. Si vous avez besoin de rangées réellement filtrées dans le fichier sauvegardé, réglez aussi l'AutoFilter de la table. Tout attribut que HotXLS ne modélise pas sur <slicer> ou <x15:tableSlicerCache> est transporté inchangé, donc un slicer venu d'une version plus récente d'Excel se re-sérialise quand même avec ses attributs inconnus intacts, même après l'avoir déplacé. C'est la propriété qui rend sûr de prendre le chemin de reconstruction par salissure : reconstruire depuis le modèle n'est acceptable que si le modèle est un sur-ensemble de ce qu'il a analysé. Le mécanisme d'extension balisé par GUID d'ECMA-376 Partie 1 §18.2.10, et les définitions des parties de slicer dans [MS-XLSX], sont ce qui rend cette garantie exprimable en premier lieu

Les slicers de table sont livrés dans le HotXLS Delphi Component, qui lit et écrit les classeurs XLSX, XLSM et BIFF hérités de Delphi 5 jusqu'à la version actuelle, plus C++Builder et Free Pascal, sans installation d'Excel et sans OLE. La référence complète de l'API et une version d'essai sont sur la page produit du HotXLS Delphi Component