HotXLS règle les modes "Show Values As" des tableaux croisés dynamiques Excel depuis Delphi via deux propriétés de TXLSPivotDataField : ShowDataAs pour les modes classiques comme le total cumulé, la différence et le pourcentage de ligne, et ExtendedShowDataAs pour le rang, le pourcentage du parent et le pourcentage de total cumulé. TXLSXWorkbook.MaterializeNativePivotTable écrit ensuite la grille calculée comme le ferait Excel. Les deux propriétés sont mutuellement exclusives, et les étendues dépendent d'un URI d'extension qu'Excel apparie en respectant la casse
Ce dernier détail est la raison d'être de ce billet. Un TCD avec une colonne de rang paraît une petite chose à générer, et pour la première ouverture c'est souvent vrai. L'ennui commence quand un utilisateur clique sur Actualiser dans Excel et que la colonne de rang redevient une simple somme sans aucun avertissement. Bien régler Show Values As signifie bien régler deux vocabulaires XML, plus une poignée de règles numériques qu'Excel ne documente jamais dans l'interface : comment les égalités prennent un rang, que signifie un pourcentage cumulé d'une moyenne, et quelle cellule vide devient un blanc plutôt qu'une erreur
Pourquoi deux propriétés pour un seul menu Excel ?
Excel montre un seul menu "Show Values As", mais le fichier le range à deux endroits différents, donc HotXLS expose deux propriétés. Les modes originaux d'Excel 2007 vivent dans l'attribut showDataAs de l'élément dataField au sein de pivotTableDefinition, avec l'énumération ECMA-376 ST_ShowDataAs. Les modes ajoutés par Excel 2010, le rang et les pourcentages parent et cumulé, ne tenaient pas dans cette énumération, donc ils sont partis dans un élément x14:dataField avec un attribut pivotShowAs, enveloppé dans un bloc d'extension extLst sur le même champ de données
TXLSPivotShowDataAs reflète le premier groupe : xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal et xlpsdaIndex. TXLSPivotExtendedShowDataAs couvre le second : xlpesdaPercentOfParent, xlpesdaPercentOfParentRow, xlpesdaPercentOfParentColumn, xlpesdaPercentOfRunningTotal, xlpesdaRankAscending et xlpesdaRankDescending, avec xlpesdaNone comme valeur neutre. Un champ de données utilise un groupe ou l'autre, jamais les deux. Si vous laissez ShowDataAs à une valeur non normale et posez aussi un mode étendu, MaterializeNativePivotTable renvoie -1 et laisse la sortie existante exactement en l'état, cellules et bornes de pivot comprises
BaseField et BaseItem portent la cible de comparaison. BaseField est un indice à base zéro dans la liste de champs du TCD, pas dans les noms d'en-tête des colonnes source, et il pilote les comparaisons, les totaux cumulés, le pourcentage d'un parent choisi et les rangs. BaseItem est une position d'élément à base zéro dans ce champ, ou l'une des constantes xlPivotBaseItemPrevious et xlPivotBaseItemNext pour "comparé au voisin". Les pourcentages de ligne parente et de colonne parente ignorent totalement BaseField ; ils prennent le champ le plus intérieur sur l'axe correspondant
Construire un TCD avec rang en Delphi
Le plus court chemin qui marche est de construire le pivot, de poser le mode d'affichage sur le champ de données, et de le matérialiser avant d'enregistrer. L'exemple ci-dessous classe les montants régionaux par catégorie en ordre décroissant, pour que le plus gros montant de chaque ligne soit rang 1
uses
SysUtils, lxHandleX, lxPivot;
var
Book: TXLSXWorkbook;
Data, Report: TXLSXWorksheet;
Pivot: TXLSPivotTable;
Measure: TXLSPivotDataField;
begin
Book := TXLSXWorkbook.Create;
try
Data := Book.Sheets.Add('Data');
Data.Cells[1, 1].Value := 'Category';
Data.Cells[1, 2].Value := 'Region';
Data.Cells[1, 3].Value := 'Amount';
// ... remplissez les lignes 2..7 ...
Report := Book.Sheets.Add('Report');
Pivot := Report.AddPivotTable('Data!A1:C7', 4, 3, 'RegionRank');
Pivot.AddRowField('Category'); // champ de pivot 0
Pivot.AddColumnField('Region'); // champ de pivot 1
Measure := Pivot.AddDataFieldByName('Amount', xlpaSum);
Measure.DisplayName := 'Rank by Region';
Measure.ShowDataAs := xlpsdaNormal; // le mode de base reste désactivé
Measure.ExtendedShowDataAs := xlpesdaRankDescending;
Measure.BaseField := 1; // rang à travers Region
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');
Book.SaveAs('region-rank.xlsx');
finally
Book.Free;
end;
end;Deux habitudes rapportent ici. Un, posez ShowDataAs := xlpsdaNormal explicitement quand vous choisissez un mode étendu, même sur un champ neuf, parce que le même code finit souvent réutilisé contre un pivot importé dont le champ de données porte déjà un mode de base. Deux, vérifiez la valeur de retour. Une matérialisation refusée est atomique, ce qui est bien, mais cela veut dire aussi qu'une vérification manquée expédie un fichier avec la grille précédente dedans
BaseField survit à l'enregistrement même si vous n'appelez jamais MaterializeNativePivotTable. HotXLS écrit l'attribut baseField pour le pourcentage du parent, le pourcentage de total cumulé et les deux modes de rang dès que le mode étendu est posé, si bien qu'un classeur enregistré juste après la configuration dit quand même à Excel quel champ traverser pour le rang lorsqu'il actualise
Qu'est-ce qui cloche avec l'URI d'extension ?
Le bloc d'extension qui porte pivotShowAs est identifié par l'URI {E15A36E0-9728-4e99-A89B-3F7291B0FE68}, et Excel compare cette chaîne en respectant la casse pendant l'actualisation. Notez le e minuscule dans 4e99. Les GUID sont normalement insensibles à la casse et beaucoup de rédacteurs XML les passent en majuscules, ce qui est exactement le mode de panne : un fichier avec 4E99 s'ouvre dans Excel avec toutes les cellules en cache correctes, parce qu'Excel se contente de peindre les valeurs stockées. Après RefreshTable, pourtant, Excel ne reconnaît plus l'extension, jette le mode étendu, et recalcule la colonne comme une somme ordinaire. Pas d'invite de réparation, pas d'erreur, juste une colonne de rang devenue discrètement des totaux
HotXLS écrit l'URI littéral comme Excel l'attend, donc cela ne vous mord que si quelque chose en aval réécrit le paquet, par exemple une étape de post-traitement qui normalise la casse des GUID ou reconstruit les éléments extLst. Le test qui l'attrape est bon marché : ouvrez le fichier généré dans Excel, actualisez le pivot, et confirmez que la colonne étendue montre toujours des rangs ou des pourcentages plutôt que des agrégats bruts. Un test qui ne lit que les cellules en cache passera sur un fichier cassé à chaque fois
Comment Excel classe les égalités et ordonne les totaux cumulés ?
Le rang de pivot d'Excel est un rang dense, et HotXLS le reproduit. Avec des montants décroissants de 30, 30 et 20, le résultat est 1, 1 et 2, pas le 1, 1 et 3 à la compétition que donne RANK.EQ sur une feuille. Une égalité compte comme une seule valeur distincte précédente. Si un rapport demande un rang à la compétition, calculez-le dans les données source ou dans une formule de feuille à côté du pivot, parce qu'aucun mode d'affichage de pivot ne le produit
Les totaux cumulés, les pourcentages de total cumulé et les comparaisons précédent ou suivant suivent l'ordre dans lequel les membres apparaissent réellement, pas l'ordre de la liste d'éléments du champ. Cette distinction compte dès qu'un appelant change le tri après la construction du cache
// Lignes triées en décroissant : le total cumulé s'accumule dans l'ordre d'affichage
Pivot.Fields[0].SortType := xlpfstDescending;
Measure.ExtendedShowDataAs := xlpesdaNone;
Measure.ShowDataAs := xlpsdaPercentDiff; // différence en % par rapport à...
Measure.BaseField := 0; // ...le champ Category
Measure.BaseItem := xlPivotBaseItemPrevious; // ...le membre visible précédent
if Book.MaterializeNativePivotTable(Pivot) <> 1 then
raise Exception.Create('Pivot display mode was rejected');Après passage de SortType à xlpfstDescending, "précédent" signifie le membre affiché au-dessus, et le total cumulé part du haut de la liste triée. Une implémentation qui parcourt la liste d'éléments stockée produit à la place des nombres plausibles qui désaccordent Excel dès que quiconque actualise. Les membres de bord demandent du soin aussi : au premier membre il n'y a pas de voisin précédent, et seul le mode pourcentage-de compare le membre de bord à lui-même ; les modes différence laissent cette cellule au comportement de bord d'Excel lui-même plutôt que d'inventer un zéro
Pourquoi un pourcentage cumulé peut-il dépasser 100 pour cent ?
Un pourcentage de total cumulé sur des moyennes peut légitimement dépasser 100 pour cent, parce qu'Excel accumule les agrégats affichés des membres et divise par un agrégat qui n'est pas leur somme. Pour des sommes, les deux sont la même chose, donc le dernier membre atterrit exactement à 100 pour cent. Pour des moyennes, additionner trois moyennes de catégorie et diviser par la moyenne d'ensemble ne fragmente rien, et la dernière ligne peut atterrir bien au-dessus de 100 pour cent. HotXLS imite Excel ici exprès plutôt que de pincer la valeur, parce qu'un nombre pincé désaccorderait le fichier actualisé
Le diviseur dépend aussi de la position du champ de base. Quand le champ de base est le champ le plus intérieur de son axe, le diviseur est l'agrégat original pour cette position d'axe. Quand le champ de base est un champ extérieur dans une imbrication, le diviseur est la somme à travers les membres correspondants de l'axe restant. Les deux positions ont été vérifiées indépendamment contre la sortie Excel, parce qu'une règle qui marche pour un niveau d'imbrication et échoue en silence sur l'autre est le bug de pivot classique
Les pourcentages simples se comportent autrement que le cumulés. Pourcentage de ligne, de colonne et de total général divisent par l'agrégat original à la portée pertinente, y compris pour des agrégations non additives comme les moyennes, donc un pourcentage du total sur des moyennes est la moyenne de la cellule divisée par la moyenne d'ensemble, pas une part d'un bassin sommé
Blancs, #NULL! et #DIV/0! dans les modes de comparaison
Les comparaisons pourcentage-de-base distinguent trois situations vides, et chacune produit une cellule différente. Quand l'intersection du numérateur n'a aucune ligne source, la cellule montre #NULL!. Quand l'élément de base n'a aucune donnée à cette intersection, la cellule est blanche. Quand l'élément de base existe et que son agrégat vaut zéro, la cellule montre #DIV/0!. Le code qui lit la grille matérialisée devrait tester avec VarIsEmpty et VarIsError plutôt que de supposer que chaque cellule de valeur porte un nombre
Les pourcentages simples sont plus indulgents : un numérateur absent compte pour zéro là. Les pourcentages de ligne parente et de colonne parente ajoutent un cas de plus, puisqu'un pivot sans champ sur l'axe correspondant n'a pas de parent par qui diviser, donc ces résultats sont blancs. Les totaux généraux suivent aussi la sémantique propre à chaque mode, ce qui inclut des totaux blancs le long de l'axe que parcourt une comparaison ou un total cumulé ; une ligne de totaux pleine de blancs sous un total cumulé est correcte, pas un échec de matérialisation
Changer de mode sur un pivot importé
Changer le mode d'affichage d'un pivot venu d'un classeur existant marche de la même façon, avec un drapeau en plus. Passez True comme AAdoptExisting pour que MaterializeNativePivotTable puisse prendre en charge les cellules qu'Excel a déjà écrites pour ce pivot
Pivot := Book.Sheets.SheetByName('Sales').PivotTables[0];
with Pivot.DataFields[0] do
begin
ShowDataAs := xlpsdaNormal; // effacez le mode de base
ExtendedShowDataAs := xlpesdaPercentOfParentRow; // pas de BaseField nécessaire
end;
if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
raise Exception.Create('Could not re-materialize the pivot');Quand le mode change, HotXLS retire les attributs obsolètes showDataAs, pivotShowAs et baseItem de la définition en attente, pour que le fichier enregistré ne porte jamais un mode de base périmé à côté d'un mode étendu neuf. La même étape de mise en attente garde les drapeaux compact et outline sur les champs de données et inactifs en phase avec la disposition du rapport, ce qui compte parce qu'Excel dérive les légendes de données actualisées de ces drapeaux et pas seulement des champs de ligne et de colonne. Les valeurs d'énumération invalides sont rejetées aussi : convertir un octet de 255 dans l'une ou l'autre énumération peut prolonger le signe vers -1 en Delphi, donc HotXLS vérifie la borne basse comme la haute et renvoie -1 sans toucher la grille
Si vous ajoutez aussi des champs calculés ou des éléments calculés au même rapport, lisez d'abord la construction et l'actualisation de tableaux croisés avec champs calculés dans HotXLS, et pour les règles XML au niveau des champs qui empêchent Excel de réparer le fichier, voir la validité du schéma des champs de pivot XLSX en Delphi
FAQ
Un champ de données HotXLS peut-il utiliser ShowDataAs et ExtendedShowDataAs ensemble ?
Non. TXLSPivotDataField.ShowDataAs et ExtendedShowDataAs sont mutuellement exclusives ; avec les deux posées, MaterializeNativePivotTable renvoie -1 et laisse la sortie précédente inchangée
Pourquoi mon rang de pivot est-il devenu une somme après actualisation dans Excel ?
L'URI d'extension a probablement été réécrit avec une autre casse. Excel apparie {E15A36E0-9728-4e99-A89B-3F7291B0FE68} en respectant la casse et retombe sur l'agrégation simple après RefreshTable quand il ne correspond pas
Le rang de pivot HotXLS saute-t-il des nombres après une égalité ?
Non. HotXLS suit le rang dense de pivot d'Excel, donc 30, 30 et 20 prennent les rangs 1, 1 et 2
La prise en charge Show Values As, la matérialisation de pivots natifs et le reste des fonctions XLSX et XLS sont livrés dans le composant tableur HotXLS Delphi pour Delphi et C++Builder