Utiliser les tableaux et les graphiques dans les classeurs Excel

  • 14 minutes

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.

Diagramme de vue d’ensemble de la plateforme Microsoft 365.

Anatomie du complément Office.js pour Microsoft Excel

Nous allons commencer par étudier l’anatomie de Office.js pour Microsoft Excel :

Contour de l’anatomie d’un complément Office.js.

  • 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 du context 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éthode load(). 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éthode load().
  • 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.

Diagramme montrant la hiérarchie d’objets Excel.

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.

Capture d’écran de l’exemple de classeur Excel.

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 valeur null 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 valeur 0, 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.

Capture d’écran d’Excel avec un tableau de données.

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.

Capture d’écran du classeur Excel avec un graphique.

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.