HotXLS crée et rafraîchit des tableaux croisés dynamiques XLSX natifs depuis Delphi et C++Builder, sans nécessiter d'installation d'Excel ni d'automatisation COM sur la machine. Vous appelez AddPivotTable avec une plage source, déposez des champs sur les axes des lignes, des colonnes, des pages et des données, ajoutez des éléments calculés ou un affichage en pourcentage du total, et le composant écrit les parties pivotCacheDefinition et pivotTableDefinition qu'Excel ouvre sous la forme d'un tableau croisé dynamique actif et rafraîchissable
Le scénario qui justifie cette mise en œuvre est celui d'un serveur de rapports. Vous générez des centaines de classeurs par nuit, chacun comportant un tableau croisé dynamique qui résume un compte, et le mois suivant, les chiffres changent et chaque fichier doit refléter les nouvelles lignes sources. Piloter Excel depuis un service Windows est une solution fragile et non autorisée pour un usage serveur, et écrire manuellement le XML du tableau croisé dynamique s'apparente à un projet d'archéologie des spécifications sans fin. HotXLS se situe entre ces deux impasses : un modèle d'objet typé sur les parties du tableau croisé dynamique OOXML, de sorte que le même code Pascal qui remplit les cellules déclare également le tableau croisé dynamique et reconstruit son cache au cours du même processus
Comment créer un tableau croisé dynamique dans Delphi sans Excel ?
Vous en créez un d'un simple appel pour ensuite placer des champs sur les axes. AddPivotTable prend la plage source en notation A1, la cellule de destination où s'ancre le tableau et un nom ; elle analyse la plage, parcourt chaque colonne pour en déduire le type de données, construit un cache de tableau croisé dynamique (ou réutilise un cache déjà lié à la même plage) et renvoie un TXLSPivotTable dont tous les champs commencent hors axe. À partir de là, les méthodes pratiques AddRowField, AddColumnField, AddPageField et AddDataFieldByName configurent la disposition, et chaque champ de données reçoit l'une des onze agrégations de l'énumération TXLSPivotAggregation (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)
uses
lxHandleX, lxPivot;
var
Book : TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('sales.xlsx');
Sheet := Book.Sheets[1]; // report sheet (1-based in the XLSX engine)
// Source A1:E500 on the 'Data' sheet; 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.AddPageField('Year');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
Pivot.AddDataFieldByName('Units', xlpaAverage);
Book.SaveAs('sales-pivot.xlsx');
end;
finally
Book.Free;
end;
end;Pourquoi le cache et the tableau sont deux parties distinctes
Un tableau croisé dynamique est en réalité composé de deux éléments qui se référencent mutuellement, et comprendre cette séparation permet de clarifier le reste du sujet. La pivotCacheDefinition est l'instantané des données : une worksheetSource pointant vers la plage source, et un cacheField par colonne contenant les valeurs distinctes de cette colonne (ses sharedItems) ainsi que les limites dérivées. La pivotTableDefinition est la vue : quel champ de cache se trouve sur quel axe, les champs de données et leurs agrégations, les commutateurs de disposition. La vue se lie au cache par un cacheId via la relation pivotCaches du classeur, exactement comme le décrivent la norme ECMA-376 Partie 1 §18.10 et le document [MS-XLSX]
Cette indirection n'est pas de la bureaucratie, elle apporte deux avantages. Un unique cache peut alimenter plusieurs tableaux, de sorte qu'un seul rafraîchissement du cache met à jour chaque vue qui en dépend. De plus, le cache stocke les valeurs de chaque colonne après élimination des doublons à l'aide d'une table d'indexation par enregistrement plutôt que de la grille brute, ce qu'il convient de noter car c'est pourquoi créer un tableau croisé dynamique consiste à analyser la source et non à copier des cellules. HotXLS modélise cela de la même façon pour les deux moteurs, le code ci-dessus étant presque identique au chemin classique documenté avec le schéma d'enregistrement SX binaire derrière les pivots .xls classiques. Si votre source réside sur une autre feuille ou si vous y accédez via un nom, le préfixe de plage ainsi que les noms définis et références croisées de feuilles respectent les règles habituelles de la notation A1, les noms de feuilles entre guillemets étant autorisés pour les noms contenant des espaces
Les éléments calculés ne sont pas des champs calculés
Ces trois termes désignent trois notions distinctes, et les confondre est l'erreur classique des tableaux croisés dynamiques. Un élément calculé réside au sein d'un champ unique et combine par nom les éléments propres à ce champ, ainsi, dans un champ Region, vous pouvez définir une ligne synthétique CoreMarkets égale à North plus South ; HotXLS l'expose sous la forme TXLSPivotField.AddCalculatedItem et écrit un <calculatedItem> sous le nœud <calculatedItems> de ce champ. Un champ calculé est différent : il s'agit d'une nouvelle valeur dérivée d'autres colonnes, comme Margin à partir de Revenue et Cost, et elle est portée sous forme de formule sur un champ de cache via TXLSPivotCacheField.Formula. Un membre calculé, ajouté avec TXLSPivotTable.AddCalculatedMember, est un membre personnalisé au niveau du tableau qui peut faire office de mesure (type de membre data) ou de membre de dimension, ce qui présente un intérêt principalement pour les tableaux croisés dynamiques de type OLAP
Une limite stricte s'applique aux trois cas. HotXLS écrit le texte de la formule dans le XML de définition ; il ne l'évalue pas. Excel calcule l'élément, le champ ou le membre calculé lorsqu'il ouvre le fichier, de la même façon qu'il calcule chaque agrégat dans la grille. HotXLS écrit les instructions, non les résultats, de sorte que les formules que vous fournissez doivent être des formules de tableau croisé dynamique valides dans le dialecte propre à Excel, en faisant référence aux noms de champs et d'éléments comme Excel le ferait
Comment afficher des valeurs en pourcentage du total ?
Vous définissez le mode d'affichage de la valeur sur le champ de données plutôt que de transformer vous-même les chiffres. TXLSPivotDataField.ShowDataAs prend l'énumération TXLSPivotShowDataAs, qui reflète les valeurs OOXML de ST_ShowDataAs : xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal et xlpsdaIndex. Une astuce courante consiste à placer deux fois la même colonne source sur l'axe des données, une fois sous forme de somme brute et une autre en tant que part du total général, afin que le rapport affiche à la fois le chiffre et sa pondération
xlpsdaPercentOfTotal ne requiert aucune base car il est relatif au total général, mais ce n'est pas le cas des modes relatifs aux éléments. xlpsdaDifference, xlpsdaPercentDiff et xlpsdaRunTotal nécessitent BaseField (l'index du champ de cache sur lequel s'applique la comparaison) et, pour les formes ancrées aux éléments, un index BaseItem, où $7FFD représente la sentinelle (All). HotXLS écrit ces données sous forme d'attributs <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> et, comme pour les formules calculées, laisse l'arithmétique à la charge d'Excel
Regroupement des dates et des nombres, et filtres de champs de pages
Le regroupement se configure sur le champ de cache, et non sur le champ du tableau croisé dynamique, car il modifie la sectorisation du domaine source. Définissez HasGroup := True sur le champ de cache renvoyé par Cache.FindFieldByName, puis choisissez soit les paramètres de plage numérique (GroupStartNum, GroupEndNum, GroupInterval), soit les indicateurs de hiérarchie de date (GroupByDate avec GroupMonths, GroupQuarters, GroupYears, et un intervalle GroupStartDate / GroupEndDate). HotXLS émet l'élément <fieldGroup><rangePr groupBy="months"/> correspondant afin qu'un champ regroupé par mois ou par une tranche numérique de largeur 1000 s'ouvre comme Excel l'aurait regroupé
Les champs de page correspondent aux listes déroulantes de filtres au-dessus du tableau. AddPageField place un champ sur l'axe des pages, et PageItemIndex présélectionne un élément de cache unique, avec la valeur par défaut xlPageItemAll ($7FFD, signifiant (All)). Pour permettre à un lecteur de cocher plusieurs éléments à la fois, définissez MultipleItemSelectionAllowed := True, ce que HotXLS écrit sous la forme <pivotField multipleItemSelectionAllowed="1"/>. Pour les critères allant au-delà d'une sélection manuelle, chaque champ intègre une collection typée Filters couvrant les familles ST_FilterType d'OOXML — filtres de nombre, de pourcentage, de somme, de légende, de valeur et de date —, chaque entrée associant un type de filtre à ses valeurs de comparaison
Comment rafraîchir un cache de tableau croisé dynamique lorsque la source change ?
RefreshPivotCache sur TXLSXWorkbook analyse à nouveau la plage source et reconstruit le cache sur place, ce qui s'avère indispensable pour un pipeline par lots après modification des lignes sous-jacentes. Transmettez l'ID du cache, et la méthode relit chaque cellule de la plage source (l'en-tête sur la première ligne, les données à partir de la seconde), reconstruit le domaine des éléments partagés de chaque champ en éliminant à nouveau les doublons et en recalculant les limites par type, puis réécrit les index d'éléments par enregistrement. Elle renvoie 1 en cas de réussite et -1 si l'ID du cache est inconnu ou si la feuille source est manquante
La limite sémantique mérite d'être énoncée clairement. Avant la création de cette méthode, HotXLS s'appuyait sur l'indicateur refreshOnLoad="1" flag et laissait l'intégralité du traitement de rafraîchissement à la charge d'Excel lors de la prochaine ouverture. Cela convient lorsqu'un humain ouvre le fichier, mais s'avère inutile dans un pipeline sans tête (headless) devant transmettre des données exactes. RefreshPivotCache actualise le cache enregistré en cours de processus, et comme les tableaux se lient à un cache par ID, chaque tableau croisé dynamique basé sur ce cache bénéficie de la mise à jour. En revanche, cette fonction ne gère pas la disposition ni l'agrégation de la grille visible — Excel recalculant toujours le tableau affiché à partir du cache rafraîchi lors de l'ouverture du fichier
Ce que écrit HotXLS et ce que calcule Excel
Gardez à l'esprit la répartition des rôles et vous n'aurez pas de surprise. HotXLS se charge de l'écriture des définitions : il produit la pivotCacheDefinition, les enregistrements de cache et la pivotTableDefinition, avec les axes, agrégations, éléments et membres calculés, affichages de valeurs, regroupements et filtres. Excel fait office de calculateur : à l'ouverture, il matérialise les tranches de regroupement, évalue les formules calculées, applique les transformations showDataAs et agrège le corps. Les valeurs affichées pour l'utilisateur sont celles d'Excel, générées à partir des instructions écrites par HotXLS, d'où l'importance de s'assurer de l'exactitude de chaque formule et index de base lors de l'écriture plutôt que lors du rendu
Deux limites méritent d'être signalées avant de concevoir votre architecture autour de cette fonctionnalité. AddPivotTable analyse une plage A1 rectangulaire avec un préfixe de feuille facultatif ; les sources de plages nommées et de classeurs externes sortent du cadre de ce que le constructeur résout, bien que les caches lus à partir d'un fichier créé par Excel préservent leur source de plage nommée lors du transfert. De plus, les pivots créés par Excel que HotXLS lit sur le disque sont réécrits au bit près lors de l'enregistrement, ainsi, les modifications typées présentées ici s'appliquent proprement aux pivots que vous considérez en code, tandis que les pivots existants restent inchangés. Concernant la partie saisie d'un rapport — les cellules validées et les tableaux filtrés résumés par le pivot —, consultez l'article sur la validation des données, AutoFilter et les tableaux structurés
Le modèle de tableau croisé dynamique présenté ici fait partie du composant HotXLS Delphi Excel standard pour Delphi et C++Builder, qui lit et écrit à la fois les parties typées de pivots XLSX et les enregistrements BIFF8 classiques à partir du même modèle d'objet