Utiliser des tableaux croisés dynamiques à l’aide de l’API JavaScript Excel
Les tableaux croisés dynamiques simplifient les jeux de données plus volumineux. Ils permettent la manipulation rapide des données groupées. L’API JavaScript Excel permet à votre complément de créer des tableaux croisés dynamiques et d’interagir avec leurs composants. Cet article décrit comment les tableaux croisés dynamiques sont représentés par l’API JavaScript Office et fournit des exemples de code pour les scénarios clés.
Si vous n’êtes pas familiarisé avec les fonctionnalités des tableaux croisés dynamiques, envisagez de les explorer en tant qu’utilisateur final. Consultez Créer un tableau croisé dynamique pour analyser les données de feuille de calcul pour obtenir une bonne introduction à ces outils.
Importante
Les tableaux croisés dynamiques créés avec OLAP ne sont actuellement pas pris en charge. Power Pivot n’est pas non plus pris en charge.
Modèle d’objet
Le tableau croisé dynamique est l’objet central des tableaux croisés dynamiques dans l’API JavaScript Office.
-
Workbook.pivotTablesetWorksheet.pivotTablessont des PivotTableCollections qui contiennent les tableaux croisés dynamiques dans le classeur et la feuille de calcul, respectivement. - Un tableau croisé dynamique contient un PivotHierarchyCollection qui a plusieurs pivotHierarchies.
- Ces pivotHierarchies peuvent être ajoutées à des collections de hiérarchies spécifiques pour définir la façon dont le tableau croisé dynamique pivote les données (comme expliqué dans la section suivante).
- Une pivotHierarchy contient un PivotFieldCollection qui a exactement un champ croisé dynamique. Si la conception se développe pour inclure des tableaux croisés dynamiques OLAP, cela peut changer.
- Un champ de tableau croisé dynamique peut avoir un ou plusieurs pivotfilters appliqués , à condition que la hiérarchie PivotHierarchy du champ soit affectée à une catégorie de hiérarchie.
- Un champ de tableau croisé dynamique contient un PivotItemCollection qui comporte plusieurs éléments croisés dynamiques.
- Un tableau croisé dynamique contient un PivotLayout qui définit l’emplacement où les pivotfields et lespivotitems sont affichés dans la feuille de calcul. La disposition contrôle également certains paramètres d’affichage du tableau croisé dynamique.
Examinons comment ces relations s’appliquent à certains exemples de données. Les données suivantes décrivent les ventes de fruits provenant de différentes fermes. Ce sera l’exemple tout au long de cet article.
Ces données de ventes de ferme fruitière seront utilisées pour créer un tableau croisé dynamique. Chaque colonne, telle que Types, est un PivotHierarchy. La hiérarchie Types contient le champ Types . Le champ Types contient les éléments Apple, Kiwi, Lemon, Lime et Orange.
Hierarchies
Les tableaux croisés dynamiques sont organisés en fonction de quatre catégories de hiérarchie : ligne, colonne, données et filtre.
Les données de la batterie de serveurs présentées précédemment ont cinq hiérarchies : Fermes, Type, Classification, Caisses vendues à la ferme et Caisses vendues en gros. Chaque hiérarchie ne peut exister que dans l’une des quatre catégories. Si Type est ajouté aux hiérarchies de colonnes, il ne peut pas être également dans les hiérarchies de ligne, de données ou de filtre. Si Type est ajouté par la suite aux hiérarchies de lignes, il est supprimé des hiérarchies de colonnes. Ce comportement est le même que l’attribution de hiérarchie soit effectuée via l’interface utilisateur Excel ou les API JavaScript Excel.
Les hiérarchies de lignes et de colonnes définissent la façon dont les données seront regroupées. Par exemple, une hiérarchie de lignes de Batteries de serveurs regroupe tous les jeux de données de la même batterie de serveurs. Le choix entre la hiérarchie de lignes et de colonnes définit l’orientation du tableau croisé dynamique.
Les hiérarchies de données sont les valeurs à agréger en fonction des hiérarchies de lignes et de colonnes. Un tableau croisé dynamique avec une hiérarchie de lignes de batteries et une hiérarchie de données de Crates Sold Wholesale affiche la somme totale (par défaut) de tous les différents fruits pour chaque ferme.
Les hiérarchies de filtre incluent ou excluent des données du tableau croisé dynamique en fonction des valeurs de ce type filtré. Une hiérarchie de filtre de Classification avec le type Organique sélectionné affiche uniquement les données relatives aux fruits organiques.
Voici à nouveau les données de la batterie de serveurs, ainsi qu’un tableau croisé dynamique. Le tableau croisé dynamique utilise Farm et Type comme hiérarchies de lignes, Crates Sold at Farm et Crates Sold Wholesale comme hiérarchies de données (avec la fonction d’agrégation par défaut sum) et Classification en tant que hiérarchie de filtre (avec Organic sélectionné).
Ce tableau croisé dynamique peut être généré via l’API JavaScript ou via l’interface utilisateur Excel. Les deux options permettent d’effectuer d’autres manipulations via des compléments.
Créer un tableau croisé dynamique
Les tableaux croisés dynamiques ont besoin d’un nom, d’une source et d’une destination. La source peut être une adresse de plage ou un nom de table (passé en tant que Rangetype , stringou Table ). La destination est une adresse de plage (donnée sous la forme d’un Range ou string).
Les exemples suivants illustrent différentes techniques de création de tableaux croisés dynamiques.
Créer un tableau croisé dynamique avec des adresses de plage
await Excel.run(async (context) => {
// Create a PivotTable named "Farm Sales" on the current worksheet at cell
// A22 with data from the range A1:E21.
context.workbook.worksheets.getActiveWorksheet().pivotTables.add(
"Farm Sales", "A1:E21", "A22");
await context.sync();
});
Créer un tableau croisé dynamique avec des objets Range
await Excel.run(async (context) => {
// Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
// the data comes from the worksheet "DataWorksheet" across the range A1:E21.
let rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
let rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
context.workbook.worksheets.getItem("PivotWorksheet").pivotTables.add(
"Farm Sales", rangeToAnalyze, rangeToPlacePivot);
await context.sync();
});
Créer un tableau croisé dynamique au niveau du classeur
await Excel.run(async (context) => {
// Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
// the data is from the worksheet "DataWorksheet" across the range A1:E21.
context.workbook.pivotTables.add(
"Farm Sales", "DataWorksheet!A1:E21", "PivotWorksheet!A2");
await context.sync();
});
Utiliser un tableau croisé dynamique existant
Les tableaux croisés dynamiques créés manuellement sont également accessibles via la collection de tableaux croisés dynamiques du classeur ou de feuilles de calcul individuelles. Le code suivant obtient un tableau croisé dynamique nommé Mon tableau croisé dynamique à partir du classeur.
await Excel.run(async (context) => {
let pivotTable = context.workbook.pivotTables.getItem("My Pivot");
await context.sync();
});
Ajouter des lignes et des colonnes à un tableau croisé dynamique
Les lignes et les colonnes pivotent les données autour des valeurs de ces champs.
L’ajout de la colonne Batterie de serveurs fait pivoter toutes les ventes autour de chaque batterie de serveurs. L’ajout des lignes Type et Classification décompose davantage les données en fonction du fruit vendu et s’il était biologique ou non.
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));
pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
await context.sync();
});
Vous pouvez également avoir un tableau croisé dynamique avec uniquement des lignes ou des colonnes.
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));
await context.sync();
});
Ajouter des hiérarchies de données au tableau croisé dynamique
Les hiérarchies de données remplissent le tableau croisé dynamique avec des informations à combiner en fonction des lignes et des colonnes. L’ajout des hiérarchies de données des caisses vendues à la ferme et des caisses vendues en gros donne des sommes de ces chiffres pour chaque ligne et colonne.
Dans l’exemple, Farm et Type sont des lignes, avec les ventes de caisses comme données.
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
// "Farm" and "Type" are the hierarchies on which the aggregation is based.
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
// "Crates Sold at Farm" and "Crates Sold Wholesale" are the hierarchies
// that will have their data aggregated (summed in this case).
pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));
await context.sync();
});
Dispositions de tableau croisé dynamique et obtention de données pivotées
Un PivotLayout définit l’emplacement des hiérarchies et leurs données. Vous accédez à la disposition pour déterminer les plages où les données sont stockées.
Le diagramme suivant montre les appels de fonction de disposition qui correspondent aux plages du tableau croisé dynamique.
Obtenir des données à partir du tableau croisé dynamique
La disposition définit la façon dont le tableau croisé dynamique est affiché dans la feuille de calcul. Cela signifie que l’objet PivotLayout contrôle les plages utilisées pour les éléments de tableau croisé dynamique. Utilisez les plages fournies par la disposition pour obtenir les données collectées et agrégées par le tableau croisé dynamique. En particulier, utilisez PivotLayout.getDataBodyRange pour accéder aux données produites par le tableau croisé dynamique.
Le code suivant montre comment obtenir la dernière ligne des données de tableau croisé dynamique en parcourant la disposition (total général des colonnes Somme des caisses vendues à la ferme et Somme des caisses vendues en gros dans l’exemple précédent). Ces valeurs sont ensuite additionnées pour obtenir un total final, qui est affiché dans la cellule E30 (en dehors du tableau croisé dynamique).
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
// Get the totals for each data hierarchy from the layout.
let range = pivotTable.layout.getDataBodyRange();
let grandTotalRange = range.getLastRow();
grandTotalRange.load("address");
await context.sync();
// Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
let masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
await context.sync();
});
Types de disposition
Les tableaux croisés dynamiques ont trois styles de disposition : Compact, Plan et Tabulaire. Nous avons vu le style compact dans les exemples précédents.
Les exemples suivants utilisent les styles plan et tabulaire, respectivement. L’exemple de code montre comment passer d’une disposition à l’autre.
Disposition du plan
Disposition tabulaire
Exemple de code de commutateur de type PivotLayout
await Excel.run(async (context) => {
// Change the PivotLayout.type to a new type.
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
pivotTable.layout.load("layoutType");
await context.sync();
// Cycle between the three layout types.
if (pivotTable.layout.layoutType === "Compact") {
pivotTable.layout.layoutType = "Outline";
} else if (pivotTable.layout.layoutType === "Outline") {
pivotTable.layout.layoutType = "Tabular";
} else {
pivotTable.layout.layoutType = "Compact";
}
await context.sync();
});
Autres fonctions PivotLayout
Par défaut, les tableaux croisés dynamiques ajustent la taille des lignes et des colonnes en fonction des besoins. Cette opération est effectuée lorsque le tableau croisé dynamique est actualisé.
PivotLayout.autoFormat spécifie ce comportement. Toutes les modifications de taille de ligne ou de colonne apportées par votre complément sont conservées lorsque autoFormat a la valeur false. En outre, les paramètres par défaut d’un tableau croisé dynamique conservent toute mise en forme personnalisée dans le tableau croisé dynamique (par exemple, les remplissages et les modifications de police). Définissez PivotLayout.preserveFormatting sur false pour appliquer le format par défaut lors de l’actualisation.
Un PivotLayout contrôle également les paramètres d’en-tête et de ligne totale, la façon dont les cellules de données vides sont affichées et les options de texte de remplacement . La référence PivotLayout fournit une liste complète de ces fonctionnalités.
L’exemple de code suivant permet aux cellules de données vides d’afficher la chaîne "--", de mettre en forme la plage de corps sur un alignement horizontal cohérent et de garantir que les modifications de mise en forme restent même après l’actualisation du tableau croisé dynamique.
await Excel.run(async (context) => {
let pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
let pivotLayout = pivotTable.layout;
// Set a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
pivotLayout.emptyCellText = "--";
// Set the text alignment to match the rest of the PivotTable.
pivotLayout.getDataBodyRange().format.horizontalAlignment = Excel.HorizontalAlignment.right;
// Ensure empty cells are filled with a default value.
pivotLayout.fillEmptyCells = true;
// Ensure that the format settings persist, even after the PivotTable is refreshed and recalculated.
pivotLayout.preserveFormatting = true;
await context.sync();
});
Supprimer un tableau croisé dynamique
Les tableaux croisés dynamiques sont supprimés à l’aide de leur nom.
await Excel.run(async (context) => {
context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
await context.sync();
});
Filtrer un tableau croisé dynamique
La méthode principale de filtrage des données de tableau croisé dynamique est avec PivotFilters. Les segments offrent une autre méthode de filtrage moins flexible.
Les filtres croisés dynamiques filtrent les données en fonction des quatre catégories hiérarchiques d’un tableau croisé dynamique (filtres, colonnes, lignes et valeurs). Il existe quatre types de filtres croisés dynamiques, qui autorisent le filtrage basé sur la date du calendrier, l’analyse de chaînes, la comparaison de nombres et le filtrage en fonction d’une entrée personnalisée.
Les segments peuvent être appliqués à la fois aux tableaux croisés dynamiques et aux tableaux Excel standard. Lorsqu’ils sont appliqués à un tableau croisé dynamique, les segments fonctionnent comme un PivotManualFilter et autorisent le filtrage en fonction d’une entrée personnalisée. Contrairement aux PivotFilters, les segments ont un composant d’interface utilisateur Excel. Avec la Slicer classe , vous créez ce composant d’interface utilisateur, gérez le filtrage et contrôlez son apparence visuelle.
Filtrer avec des filtres croisés dynamiques
Les filtres croisés dynamiques vous permettent de filtrer les données de tableau croisé dynamique en fonction des quatre catégories hiérarchiques (filtres, colonnes, lignes et valeurs). Dans le modèle objet Tableau croisé dynamique, PivotFilters sont appliqués à un champ de tableau croisé dynamique, et un PivotField ou plusieurs peuvent être affectés à PivotFilterschacun d’eux. Pour appliquer pivotFilters à un champ de tableau croisé dynamique, la pivotHierarchy correspondante du champ doit être affectée à une catégorie de hiérarchie.
Types de filtres croisés dynamiques
| Type de filtre | Objectif du filtre | Référence de l’API JavaScript pour Excel |
|---|---|---|
| DateFilter | Filtrage basé sur la date du calendrier. | PivotDateFilter |
| LabelFilter | Filtrage de comparaison de texte. | PivotLabelFilter |
| ManualFilter | Filtrage d’entrée personnalisé. | PivotManualFilter |
| ValueFilter | Filtrage de comparaison de nombres. | PivotValueFilter |
Créer un filtre croisé dynamique
Pour filtrer des données de tableau croisé dynamique avec un Pivot*Filter (par exemple, un PivotDateFilter), appliquez le filtre à un champ de tableau croisé dynamique. Les quatre exemples de code suivants montrent comment utiliser chacun des quatre types de filtres croisés dynamiques.
PivotDateFilter
Le premier exemple de code applique un PivotDateFilter au Champ croisé dynamique date mise à jour , masquant toutes les données antérieures au 01-08-2020.
Importante
Un Pivot*Filter ne peut pas être appliqué à un champ de tableau croisé dynamique, sauf si la pivotHierarchy de ce champ est affectée à une catégorie de hiérarchie. Dans l’exemple de code suivant, le dateHierarchy doit être ajouté à la catégorie du rowHierarchies tableau croisé dynamique avant de pouvoir être utilisé pour le filtrage.
await Excel.run(async (context) => {
// Get the PivotTable and the date hierarchy.
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
let dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
await context.sync();
// PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
// If it's not already there, add "Date Updated" to the hierarchies.
if (dateHierarchy.isNullObject) {
dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));
}
// Apply a date filter to filter out anything logged before August.
let filterField = dateHierarchy.fields.getItem("Date Updated");
let dateFilter = {
condition: Excel.DateFilterCondition.afterOrEqualTo,
comparator: {
date: "2020-08-01",
specificity: Excel.FilterDatetimeSpecificity.month
}
};
filterField.applyFilter({ dateFilter: dateFilter });
await context.sync();
});
Remarque
Les trois extraits de code suivants affichent uniquement des extraits spécifiques au filtre, au lieu d’appels complets Excel.run .
PivotLabelFilter
Le deuxième extrait de code montre comment appliquer un PivotLabelFilter au Type PivotField, en utilisant la LabelFilterCondition.beginsWith propriété pour exclure les étiquettes qui commencent par la lettre L.
// Get the "Type" field.
let filterField = pivotTable.hierarchies.getItem("Type").fields.getItem("Type");
// Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
let filter: Excel.PivotLabelFilter = {
condition: Excel.LabelFilterCondition.beginsWith,
substring: "L",
exclusive: true
};
// Apply the label filter to the field.
filterField.applyFilter({ labelFilter: filter });
PivotManualFilter
Le troisième extrait de code applique un filtre manuel avec PivotManualFilter au champ Classification , en filtrant les données qui n’incluent pas la classification Organic.
// Apply a manual filter to include only a specific PivotItem (the string "Organic").
let filterField = classHierarchy.fields.getItem("Classification");
let manualFilter = { selectedItems: ["Organic"] };
filterField.applyFilter({ manualFilter: manualFilter });
PivotValueFilter
Pour comparer des nombres, utilisez un filtre de valeur avec PivotValueFilter, comme indiqué dans l’extrait de code final. compare PivotValueFilter les données du champ pivot de la ferme aux données du champ pivot Crates Sold Wholesale , y compris uniquement les batteries dont la somme des caisses vendues dépasse la valeur 500.
// Get the "Farm" field.
let filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
// Filter to only include rows with more than 500 wholesale crates sold.
let filter: Excel.PivotValueFilter = {
condition: Excel.ValueFilterCondition.greaterThan,
comparator: 500,
value: "Sum of Crates Sold Wholesale"
};
// Apply the value filter to the field.
filterField.applyFilter({ valueFilter: filter });
Supprimer les filtres croisés dynamiques
Pour supprimer tous les PivotFilters, appliquez la clearAllFilters méthode à chaque champ croisé dynamique, comme indiqué dans l’exemple de code suivant.
await Excel.run(async (context) => {
// Get the PivotTable.
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
pivotTable.hierarchies.load("name");
await context.sync();
// Clear the filters on each PivotField.
pivotTable.hierarchies.items.forEach(function (hierarchy) {
hierarchy.fields.getItem(hierarchy.name).clearAllFilters();
});
await context.sync();
});
Filtrer avec des segments
Les segments permettent de filtrer les données à partir d’un tableau croisé dynamique Excel ou d’un tableau croisé dynamique. Un segment utilise les valeurs d’une colonne ou d’un champ de tableau croisé dynamique spécifié pour filtrer les lignes correspondantes. Ces valeurs sont stockées en tant qu’objets SlicerItem dans .Slicer Votre complément peut ajuster ces filtres, tout comme les utilisateurs (via l’interface utilisateur Excel). Le segment se trouve au-dessus de la feuille de calcul dans la couche de dessin, comme illustré dans la capture d’écran suivante.
Remarque
Les techniques décrites dans cette section se concentrent sur l’utilisation des segments connectés aux tableaux croisés dynamiques. Les mêmes techniques s’appliquent également à l’utilisation de segments connectés à des tables.
Créer un segment
Vous pouvez créer un segment dans un classeur ou une feuille de calcul à l’aide de la méthode ou Worksheet.slicers.add de la Workbook.slicers.add méthode . Cela ajoute un segment à la SlicerCollection de l’objet ou Worksheet spécifiéWorkbook. La SlicerCollection.add méthode a trois paramètres :
-
slicerSource: source de données sur laquelle le nouveau segment est basé. Il peut s’agir d’unePivotTablechaîne ,Tableou représentant le nom ou l’ID d’unPivotTableouTable. -
sourceField: champ dans la source de données par lequel filtrer. Il peut s’agir d’unePivotFieldchaîne ,TableColumnou représentant le nom ou l’ID d’unPivotFieldouTableColumn. -
slicerDestination: feuille de calcul dans laquelle le nouveau segment sera créé. Il peut s’agir d’unWorksheetobjet ou du nom ou de l’ID d’unWorksheet. Ce paramètre n’est pas nécessaire lorsque leSlicerCollectionest accessible viaWorksheet.slicers. Dans ce cas, la feuille de calcul de la collection est utilisée comme destination.
L’exemple de code suivant ajoute un nouveau segment à la feuille de calcul Pivot . La source du segment est le tableau croisé dynamique Ventes de batteries de serveurs et filtre à l’aide des données de type . Le segment est également nommé Segment de fruits pour référence ultérieure.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Pivot");
let slicer = sheet.slicers.add(
"Farm Sales" /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */,
"Type" /* The field in the data to filter by. For PivotTables, this can be a PivotField object reference or ID. */
);
slicer.name = "Fruit Slicer";
await context.sync();
});
Filtrer des éléments avec un segment
Le segment filtre le tableau croisé dynamique avec les éléments du sourceField. La Slicer.selectItems méthode définit les éléments qui restent dans le segment. Ces éléments sont passés à la méthode en tant que string[], représentant les clés des éléments. Toutes les lignes contenant ces éléments restent dans l’agrégation du tableau croisé dynamique. Appels suivants pour selectItems définir la liste sur les clés spécifiées dans ces appels.
Remarque
Si Slicer.selectItems un élément qui n’est pas dans la source de données est transmis, une InvalidArgument erreur est générée. Le contenu peut être vérifié via la Slicer.slicerItems propriété , qui est une SlicerItemCollection.
L’exemple de code suivant montre trois éléments sélectionnés pour le segment : Citron, Lime et Orange.
await Excel.run(async (context) => {
let slicer = context.workbook.slicers.getItem("Fruit Slicer");
// Anything other than the following three values will be filtered out of the PivotTable for display and aggregation.
slicer.selectItems(["Lemon", "Lime", "Orange"]);
await context.sync();
});
Pour supprimer tous les filtres du segment, utilisez la Slicer.clearFilters méthode , comme illustré dans l’exemple suivant.
await Excel.run(async (context) => {
let slicer = context.workbook.slicers.getItem("Fruit Slicer");
slicer.clearFilters();
await context.sync();
});
Appliquer un style et mettre en forme un segment
Vous pouvez ajuster les paramètres d’affichage d’un segment via Slicer les propriétés. L’exemple de code suivant définit le style sur SlicerStyleLight6, définit le texte en haut du segment sur Types de fruits, place le segment à la position (395, 15) sur la couche de dessin et définit la taille du segment sur 135 x 150 pixels.
await Excel.run(async (context) => {
let slicer = context.workbook.slicers.getItem("Fruit Slicer");
slicer.caption = "Fruit Types";
slicer.left = 395;
slicer.top = 15;
slicer.height = 135;
slicer.width = 150;
slicer.style = "SlicerStyleLight6";
await context.sync();
});
Supprimer un segment
Pour supprimer un segment, appelez la Slicer.delete méthode . L’exemple de code suivant supprime le premier segment de la feuille de calcul active.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.slicers.getItemAt(0).delete();
await context.sync();
});
Modifier la fonction d’agrégation
Les valeurs des hiérarchies de données sont agrégées. Pour les jeux de données de nombres, il s’agit d’une somme par défaut. La summarizeBy propriété définit ce comportement en fonction d’un type AggregationFunction .
Les types de fonction d’agrégation actuellement pris en charge sont Sum, Count, AverageMax, Min, Product, CountNumbers, StandardDeviationPStandardDeviation, Variance, , VarianceP, et Automatic (valeur par défaut).
Les exemples de code suivants modifient l’agrégation en moyenne des données.
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
pivotTable.dataHierarchies.load("no-properties-needed");
await context.sync();
// Change the aggregation from the default sum to an average of all the values in the hierarchy.
pivotTable.dataHierarchies.items[0].summarizeBy = Excel.AggregationFunction.average;
pivotTable.dataHierarchies.items[1].summarizeBy = Excel.AggregationFunction.average;
await context.sync();
});
Modifier les calculs avec un ShowAsRule
Par défaut, les tableaux croisés dynamiques agrègent les données de leurs hiérarchies de lignes et de colonnes indépendamment. Un showAsRule modifie la hiérarchie de données en valeurs de sortie en fonction d’autres éléments du tableau croisé dynamique.
L’objet ShowAsRule a trois propriétés :
-
calculation: type de calcul relatif à appliquer à la hiérarchie de données (la valeur par défaut estnone). -
baseField: PivotField dans la hiérarchie contenant les données de base avant l’application du calcul. Étant donné que les tableaux croisés dynamiques Excel ont un mappage un-à-un de la hiérarchie au champ, vous utiliserez le même nom pour accéder à la fois à la hiérarchie et au champ. -
baseItem: PivotItem individuel comparé aux valeurs des champs de base en fonction du type de calcul. Tous les calculs ne nécessitent pas ce champ.
L’exemple suivant définit le calcul sur la hiérarchie de données Sum of Crates Sold at Farm sur un pourcentage du total des colonnes. Nous voulons toujours que la granularité s’étende au niveau du type de fruit. Nous allons donc utiliser la hiérarchie de lignes de type et son champ sous-jacent. L’exemple a également Farm comme première hiérarchie de lignes, de sorte que les entrées du total de la batterie affichent également le pourcentage que chaque batterie est responsable de produire.
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");
farmDataHierarchy.load("showAs");
await context.sync();
// Show the crates of each fruit type sold at the farm as a percentage of the column's total.
let farmShowAs = farmDataHierarchy.showAs;
farmShowAs.calculation = Excel.ShowAsCalculation.percentOfColumnTotal;
farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Type").fields.getItem("Type");
farmDataHierarchy.showAs = farmShowAs;
farmDataHierarchy.name = "Percentage of Total Farm Sales";
});
L’exemple précédent définit le calcul sur la colonne, par rapport au champ d’une hiérarchie de lignes individuelle. Lorsque le calcul concerne un élément individuel, utilisez la baseItem propriété .
L’exemple suivant montre le differenceFrom calcul. Il affiche la différence entre les entrées de la hiérarchie de données de ventes de la batterie de serveurs par rapport à celles de A Farms.
étant baseFieldFarm, nous voyons les différences entre les autres batteries, ainsi que les répartitions pour chaque type de fruit semblable (Type est également une hiérarchie de lignes dans cet exemple).
await Excel.run(async (context) => {
let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");
farmDataHierarchy.load("showAs");
await context.sync();
// Show the difference between crate sales of the "A Farms" and the other farms.
// This difference is both aggregated and shown for individual fruit types (where applicable).
let farmShowAs = farmDataHierarchy.showAs;
farmShowAs.calculation = Excel.ShowAsCalculation.differenceFrom;
farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm");
farmShowAs.baseItem = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm").items.getItem("A Farms");
farmDataHierarchy.showAs = farmShowAs;
farmDataHierarchy.name = "Difference from A Farms";
});
Modifier les noms de hiérarchie
Les champs de hiérarchie sont modifiables. Le code suivant montre comment modifier les noms affichés de deux hiérarchies de données.
await Excel.run(async (context) => {
let dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
.pivotTables.getItem("Farm Sales").dataHierarchies;
dataHierarchies.load("no-properties-needed");
await context.sync();
// Changing the displayed names of these entries.
dataHierarchies.items[0].name = "Farm Sales";
dataHierarchies.items[1].name = "Wholesale";
});