Utiliser les tableaux et les graphiques dans les classeurs Excel
L’API JavaScript pour Excel permet de contrôler par programme le texte, les tableaux et les graphiques dans les classeurs Excel. Dans cette section, vous allez découvrir comment utiliser des tableaux, notamment des options de mise en forme, comment filtrer des données et trier les données dans le tableau. Vous découvrirez également comment ajouter des graphiques dans vos feuilles de calcul et comment les personnaliser.
Vue d’ensemble de la plateforme de développement Office
La plateforme de développement Microsoft 365, qui inclut Office, propose de nombreux canevas aux développeurs pour intégrer des personnalisations et les compléments Office sont l'un des trois canevas : documents, conversations et pages.
Microsoft Teams crée des conversations entre les utilisateurs et permet aux développeurs d’étendre l’expérience à l’aide d’extensions de messagerie, de bots de conversation et d’autres options de personnalisation.
Les développeurs personnalisent les pages dans SharePoint Server et SharePoint Online à l’aide de SharePoint Framework.
Les clients Office, tels que Word, Excel, PowerPoint, OneNote et Outlook, peuvent également être étendus pour implémenter des volets Office personnalisés, des actions et d’autres personnalisations à l’aide de compléments.
Anatomie du complément Office.js pour Microsoft Excel
Nous allons commencer par étudier l’anatomie de Office.js pour Microsoft Excel :
- Tous les compléments Office doivent annuler la méthode
Office.initialize()
lorsqu’une page charge d’abord le complément. - Si vous utilisez une fonctionnalité Office.js plus récente dans votre complément, il est important de vérifier si le client prend en charge ces extensions à l’aide de l’API
requirements
. - Pour les API JavaScript pour Excel, vous devez utiliser la méthode
Excel.run()
pour obtenir une instance ducontext
document actif. - Une fois que vous disposez d’une référence de
context
pour le document Excel actif, vous pouvez charger toutes les propriétés du contexte en utilisant la méthodeload()
. Cette méthode ajoute la file d’attente des demandes, ce qui vous permet de regrouper plusieurs demandes pour des raisons de performances. - Lorsque vous êtes prêt à récupérer les propriétés que vous avez mises en file d’attente ou à effectuer d’autres actions en file d’attente, utilisez la méthode
context.sync()
pour exécuter le lot d’opérations en file d’attente défini à l’aide de la méthodeload()
. - La méthode
context.sync()
renvoie une promesse JavaScript permettant d’obtenir des résultats ou une opération précédente et d’effectuer de nouvelles opérations. - Nous vous conseillons d’écouter, d’intercepter et de gérer les erreurs qui peuvent se produire lorsque vous utilisez les API JavaScript pour Excel.
Hiérarchie des objets Excel
Les développeurs de compléments Excel doivent comprendre la hiérarchie d’un classeur Excel et leur relation avec les objets dans Office.js.
Hiérarchie d’un classeur
Office.js fournit le contexte d’un classeur Excel via Excel.run()
et la propriété context.workbook
.
Le classeur contient des feuilles de calcul qui contiennent de nombreuses collections. Ces collections incluent des éléments comme des graphiques, des tableaux et des tableaux croisés dynamiques.
De nombreux objets d’une feuille de calcul sont accessibles directement à partir de l’objet classeur, notamment les tableaux et les tableaux croisés dynamiques.
Feuilles de calcul
Les feuilles de calcul connaissent les feuilles de calcul précédentes et suivantes grâce aux méthodes getNext()
et getPrevious()
.
Vous pouvez obtenir la feuille de calcul active en utilisant la méthode workbook.worksheets.getActiveWorksheet()
et la configurer avec la méthode worksheet.activate()
.
Office.js propose également de nombreux événements de feuille de calcul comme onActivated
, onDeactivated
, et onSelectionChanged
que les développeurs peuvent utiliser dans leurs compléments personnalisés.
Tableaux et en-têtes
Les tableaux sont au centre d’une feuille de calcul. Excel prend en charge la définition d’une plage de données qui constitue la base d’un tableau de données.
Plages
Une plage représente un ensemble, constitué d’une ou de plusieurs cellules contiguës, comme une cellule, une ligne, une colonne, un bloc de cellules, etc.
Vous pouvez obtenir un objet plage avec Office.js à l’aide d’une feuille de calcul et d’une adresse. Par exemple, la plage « A1:D4 » représente une plage de cellules allant des cellules en haut à gauche jusqu’aux cellules en bas à droite, sur cette capture d’écran .
Tableaux
Un tableau est établi à partir d’une plage de données.
La fonction tables.add()
accepte une plage de données avec un indicateur pour montrer si le tableau comporte des en-têtes ou non.
Les tableaux existants peuvent être récupérés à l’aide de leur ID ou de leur plage nommée. Les développeurs peuvent également parcourir une collection de tableaux dans la feuille de calcul.
Une fois le tableau ajouté, vous pouvez ajouter des en-têtes et des lignes de tableau à l’aide de tableaux à deux dimensions.
En-têtes
Un tableau créée avec un indicateur d’en-tête utilise la première ligne de la plage de données comme en-têtes.
Vous pouvez également établir des valeurs d’en-tête en utilisant la propriété getHeaderRowRange().values
avec un tableau à deux dimensions.
Les extraits de code suivants fournissent des exemples fonctionnels de texte et de paragraphes fonctionnant avec l’API JavaScript Excel :
Obtenir une plage de données à partir d’une feuille de calcul :
const range = currWorksheet.getRange('A1:D1');
Insérer un tableau dans la feuille de calcul en se basant sur la plage spécifiée
const table = currWorksheet.tables.add(range, true); table.name = "ExpensesTable";
Obtenir un tableau par son nom dans un classeur ou une feuille de calcul
const table = workbook.tables.getItem("ExpensesTable");
Ajouter une ligne d’en-tête au tableau spécifié
table.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];
Figer la ligne d’en-tête en figeant la ligne supérieure
currWorksheet.freezePanes.freezeRows(1);
Supprimer la deuxième ligne d’un tableau
const row = context.workbook.tables.getItem("ExpensesTable").rows.getItemAt(1); row.delete();
Mettre à jour la deuxième ligne d’un tableau
const row = context.workbook.tables.getItem("ExpensesTable").rows.getItemAt(1); row.values = [["1/15/2017", "Best For You Organics Company", "Groceries", "97.8"]];
Ajouter des lignes à la fin d’un tableau
table.rows.add(null, [ ["1/1/2017", "The Phone Company", "Communications", "120"] ]);
Remarque
Le premier paramètre de la méthode
add()
indique l’index dans lequel les données doivent être ajoutées. Avec une valeurnull
ou-1
, la ligne est ajoutée à la fin du tableau de fin.Ajouter une ligne
table.rows.add(0, [ ["1/10/2017", "Coho Vineyard", "Restaurant", "33"] ]);
Remarque
Le premier paramètre de la méthode
add()
indique l’index dans lequel les données doivent être ajoutées. Avec une valeur0
, les données sont ajoutées au début de l’index.
Filtrage des tableaux
Vous pouvez filtrer les colonnes d’un tableau en utilisant Office.js.
Filtrez une colonne en obtenant d'abord une référence à la colonne, puis utilisez la applyValuesFilter()
méthode pour filtrer sur des valeurs spécifiques.
Le code suivant filtre la colonne Catégorie par les valeurs Éducation et courses :
const categoryFilter = table.columns.getItem('Category').filter;
categoryFilter.applyValuesFilter(["Education", "Groceries"]);
Vous pouvez également réappliquer et effacer les filtres d’un tableau, par programmation, en utilisant les fonctions reapplyFilters()
et clearFilters()
, respectivement.
Le code suivant illustre la réapplication et la suppression des filtres :
// re-apply filters
table.reapplyFilters();
// clear filters
table.clearFilters();
Tri des tableaux
Les développeurs peuvent trier les données d’un tableau à l’aide de l’API Office.js à partir de compléments Excel. Pour trier, appelez la méthode table.sort.apply()
et incluez l’argument SortFields
pour spécifier les champs à trier.
Le code suivant définit un tableau avec les champs triés. La propriété key
spécifie l’index de colonne dans le tableau, tandis que la propriété ascending
Boolean indique si le tri doit être dans l’ordre croissant ou décroissant :
const sortFields = [
{ key: 1, ascending: false },
{ key: 2, ascending: true }
];
table.sort.apply(sortFields);
Vous pouvez réappliquer et effacer les tris du tableau en utilisant les méthodes table.sort.reapply()
et table.sort.clear()
.
// re-apply sort
table.sort.reapply();
// clear filters
table.sort.clear();
Graphiques
Microsoft Excel est devenu un terrain de jeu pour la manipulation et la visualisation des données. Il n’est donc pas surprenant que les API JavaScript Excel permettent aux développeurs d’ajouter et de manipuler des graphiques.
Les graphiques existent dans les feuilles de calcul, mais ils sont également accessibles directement à partir de l’objet classeur.
Les graphiques ont de nombreuses propriétés relationnelles complexes qui peuvent être utilisées pour affiner leur apparence. Il s’agit notamment des titres, des légendes, des axes, des séries, des étiquettes et le format.
Les utilisateurs et les développeurs peuvent créer un graphique basé sur une plage de données. Ces graphiques sont souvent créés avec des tableaux.
La fonction worksheet.charts.add()
est utilisée pour créer un graphique qui accepte un type
de graphique, une range
de données et des seriesBy
. L’argument seriesBy
prend en charge les valeurs suivantes :
- Auto
- Scalaire
- Matrice
Excel prend en charge de nombreux types de graphiques différents. Les développeurs peuvent ajouter un graphique à une feuille de calcul en utilisant la méthode worksheet.carts.add("{REPLACE_WITH_CHARTTYPE_ENUM}", range, "{REPLACE_WITH_CHARTSERIESBY_ENUM}")
.
L’argument ChartType
spécifie le type de graphique à utiliser. Consultez le kit de développement de logiciel (SDK) pour accéder aux options disponibles sur l’énumération Excel.ChartType.
L’argument ChartSeriesBy
indique si les séries s’affichent par ligne ou par colonne. Reportez-vous au kit de développement de logiciel pour consulter les options disponibles sur l’énumération Excel.ChartSeriesBy.
Propriétés du graphique
L’objet graphique Excel contient plusieurs propriétés que les développeurs peuvent utiliser pour personnaliser les graphiques dans les feuilles de calcul Excel. Le tableau suivant répertorie certaines des propriétés fréquemment utilisées dans l’objet graphique Office.js :
Property | Description |
---|---|
chartType |
Représente le type du graphique (valeurs possibles sur la diapositive précédente) |
height |
Représente la hauteur, exprimée en points, de l’objet graphique. |
id |
L’ID unique du graphique. |
left |
La distance, en points, entre le côté gauche du graphique et l’origine de la feuille de calcul. |
name |
Représente le nom de l’objet graphique. |
showAllFieldButtons |
Représente l’affichage de tous les boutons de champ dans un graphique croisé dynamique. |
top |
Représente la distance, en points, entre le bord supérieur de l’objet et la partie supérieure de la ligne 1 (sur une feuille de calcul) ou le haut de la zone de graphique (sur un graphique). |
width |
Représente la largeur, en points, de l’objet graphique. |
En plus des propriétés du tableau ci-dessus, l’objet graphique inclut plusieurs propriétés de relation. Les informations suivantes ont une incidence sur l’affichage du graphique dans Excel :
Relation | Description |
---|---|
axes | Représente les axes du graphique. En lecture seule. |
dataLabels | Représente les étiquettes des données sur le graphique. En lecture seule. |
format | Regroupe les propriétés de format de la zone de graphique. En lecture seule. |
legend | Représente la légende du graphique. En lecture seule. |
series | Représente une série ou une collection de séries dans le graphique. En lecture seule. |
title | Représente le titre du graphique indiqué et comprend le texte, la visibilité, la position et la mise en forme du titre. En lecture seule. |
worksheet | Feuille de calcul contenant le graphique actuel. En lecture seule. |
Conseil
Si vous souhaitez en savoir plus sur les propriétés, méthodes et événements dans l’objet Excel.Chart, consultez la documentation Office.js pour Excel.
Les exemples de code suivants démontrent comment utiliser l’API Office.js pour créer et manipuler des graphiques dans Excel :
Ajouter un histogramme groupé en utilisant les données du tableau
const dataRange = table.getDataBodyRange(); let chart = currWorksheet.charts.add("ColumnClustered", dataRange, "auto");
Obtenir un graphique existant par son nom
let chart = workbook.charts.getItem("MyChart");
Modifier les propriétés du graphique comme la position, les titres, les couleurs et les tailles de police
chart.setPosition("A15", "F30"); chart.title.text = "Expenses"; chart.legend.position = "right" chart.legend.format.fill.setSolidColor("white"); chart.dataLabels.format.font.size = 15; chart.dataLabels.format.font.color = "black"; chart.series.getItemAt(0).name = "Value in €";
Résumé
L’API JavaScript pour Excel permet de contrôler par programmation le texte, les tableaux et les graphiques dans les classeurs Excel. Dans cette section, vous avez découvert comment utiliser des tableaux, notamment des options de mise en forme, comment filtrer des données et trier les données dans le tableau. Vous avez également appris à ajouter et personnaliser des graphiques dans vos feuilles de calcul.