Exercice – Travailler avec des tableaux et des graphiques dans des compléments Excel

Effectué

Dans cet exercice, vous allez découvrir comment utiliser les tableaux et les graphiques à l’aide de l’API JavaScript pour Excel d’Office.js. Après avoir créé un tableau, vous découvrirez comment appliquer des actions de tri et de filtrage sur les données contenues dans ce tableau. Vous allez découvrir également comment créer et modifier des graphiques dans le classeur à l’aide de votre complément Excel personnalisé.

Conditions préalables

Le développement de compléments Office pour Microsoft Excel nécessite Excel 2016, version 1711 (build 8730.1000 Démarrer en un clic) ou version ultérieure. Vous devrez peut-être participer au programme Office Insider pour obtenir cette version. Pour plus d’informations, reportez-vous à Participez au programme Office Insider.

Vous allez utiliser Node.js pour créer le complément Excel personnalisé dans ce module. Les exercices de ce module partent du principe que vous avez installé les outils suivants sur votre station de travail de développeur.

Importante

Dans la plupart des cas, installer la dernière version des outils suivants est la meilleure option. Les versions répertoriées ici ont été utilisées lors de la publication et du dernier test de ce module.

• Node.js – (la version active LTS)
• npm (installé avec Node.js)
• Yeoman - v4.x (ou version ultérieure)
• Yeoman Generator pour Microsoft Office - v1.9.5
• Visual Studio Code

Créer votre projet de complément

Exécutez la commande suivante pour créer un projet de complément à l’aide du générateur Yeoman :

yo office

Remarque

Lorsque vous exécutez la commande yo office, il est possible que vous receviez des messages d’invite sur les règles de collecte de données de Yeoman et les outils CLI de complément Office. Utilisez les informations fournies pour répondre aux invites comme vous l’entendez.

Lorsque vous y êtes invité, fournissez les informations suivantes pour créer votre projet de complément :

• Choisissez un type de projet : projet du volet Office du complément Office
• Choisissez un type de script : JavaScript
• Comment souhaitez-vous nommer votre complément ? Mon complément Office
• Quelle application client Office voulez-vous prendre en charge ? Excel

Une fois l’Assistant terminé, accédez au dossier de projet créé par le générateur (Complément Mon Office) et installez les modules npm en exécutant npm install.

Conseil

Vous pouvez ignorer les avertissements ou erreurs lors de l’installation des dépendances et les Félicitations ! Votre complément a été créé. Les étapes suivantes : instructions du générateur Yeoman. Le reste de cette unité inclut toutes les étapes que vous devrez suivre.

Créer un tableau

Dans cette section, vous aller vérifier à l’aide de programme que votre complément prend en charge la version actuelle d’Excel de l’utilisateur, ajouter un tableau à une feuille de calcul, remplir le tableau avec des données et le mettre en forme.

Codage du complément

1. Ouvrez le projet dans votre éditeur de code.

2. Ouvrez le fichier ./src/taskpane/taskpane.html. Ce fichier contient la balise HTML du volet des tâches.

3. Recherchez l’élément <main> et supprimez toutes les lignes qui apparaissent après la balise <main> d’ouverture et avant la balise </main> de fermeture.

4. Ajoutez la balise suivante juste après la balise <main> d’ouverture :

   <button class="ms-Button" id="create-table">Create Table</button><br/><br/>
   

5. Ouvrez le fichier ./src/taskpane/taskpane.js. Ce fichier contient le code de l’API JavaScript pour Office qui assure l’interaction entre le volet des tâches et l’application hôte Office.

6. Supprimez toutes les références au bouton Exécuter et à la fonction run() par la procédure suivante :

   • Recherchez et supprimez la ligne document.getElementById("run").onclick = run;.
   • Recherchez et supprimez la fonction run() entière.

7. Au sein de l’appel de méthode Office.onReady(), recherchez la ligne if (info.host === Office.HostType.Excel) { et ajoutez le code suivant immédiatement après cette ligne:

   // Determine if the user's version of Office supports all the Office.js APIs that are used in the tutorial.
   if (!Office.context.requirements.isSetSupported('ExcelApi', '1.7')) {
     console.log('Sorry. The tutorial add-in uses Excel.js APIs that are not available in your version of Office.');
   }
   
   // Assign event handlers and other initialization logic.
   document.getElementById("create-table").onclick = createTable;
   

   Remarque

   • La première partie de ce code détermine si la version Excel de l’utilisateur prend en charge une version d’Excel.js qui inclut toutes les API utilisées dans cette série de didacticiels. Dans un complément de production, utilisez le corps du bloc conditionnel pour masquer ou désactiver l’interface utilisateur appelant des API non prises en charge. Cela permet à l’utilisateur de toujours utiliser les parties du complément prises en charge par leur version d’Excel.
   • La deuxième partie de ce code ajoute un gestionnaire d’événements pour le bouton create-table.

8. Ajoutez la fonction suivante à la fin du fichier :

   async function createTable() {
     await Excel.run(async (context) => {
   
       // TODO1: Queue table creation logic here.
   
       // TODO2: Queue commands to populate the table with data.
   
       // TODO3: Queue commands to format the table.
   
       await context.sync();
     })
     .catch(function (error) {
       console.log("Error: " + error);
       if (error instanceof OfficeExtension.Error) {
         console.log("Debug info: " + JSON.stringify(error.debugInfo));
       }
     });
   }
   

   Remarque

   • Votre logique métier Excel.js est ajoutée à la fonction qui est transmise à Excel.run(). Cette logique ne s’exécute pas immédiatement. Au lieu de cela, il est ajouté à une file d’attente de commandes en attente.
   • La méthode context.sync() envoie toutes les commandes en file d’attente vers Excel pour exécution.
   • L’élément Excel.run() est suivi par un bloc catch. Il s’agit d’une meilleure pratique que vous devez toujours suivre.

9. À l’intérieur de la fonction createTable(), remplacez TODO1 par le code suivant:

   const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
   const expensesTable = currentWorksheet.tables.add("A1:D1", true /*hasHeaders*/);
   expensesTable.name = "ExpensesTable";
   

   Remarque

   • Le code crée une table à l’aide add() de la méthode de la collection de tables d’une feuille de calcul, qui existe toujours même si elle est vide. Il s’agit de la méthode standard de création d’objets Excel.js. Il n’existe aucune API pour le constructeur de classe API. De plus, vous n’utilisez jamais d’opérateur new pour créer un objet Excel. Au lieu de cela, vous l’ajoutez à un objet de la collection parent.
   • Le premier paramètre de la méthode add() est la plage comprenant uniquement la ligne supérieure du tableau, et non la plage entière utilisée en fin de compte par le tableau. La raison est que lorsque le complément remplit les lignes de données (dans l’étape suivante), il ajoute de nouvelles lignes au tableau au lieu d’écrire des valeurs dans les cellules des lignes existantes. Il s’agit d’un modèle plus courant, car le nombre de lignes contenues dans un tableau est souvent inconnu lorsque le tableau est créé.
   • Les noms de tableau doivent être uniques dans l’ensemble du classeur, pas uniquement dans la feuille de calcul.

10. À l’intérieur de la fonction createTable(), remplacez TODO2 par le code suivant:

    expensesTable.getHeaderRowRange().values =
      [["Date", "Merchant", "Category", "Amount"]];
    
    expensesTable.rows.add(null /*add at the end*/, [
      ["1/1/2017", "The Phone Company", "Communications", "120"],
      ["1/2/2017", "Northwind Electric Cars", "Transportation", "142.33"],
      ["1/5/2017", "Best For You Organics Company", "Groceries", "27.9"],
      ["1/10/2017", "Coho Vineyard", "Restaurant", "33"],
      ["1/11/2017", "Bellows College", "Education", "350.1"],
      ["1/15/2017", "Trey Research", "Other", "135"],
      ["1/15/2017", "Best For You Organics Company", "Groceries", "97.88"]
    ]);
    

    Remarque

    • Les valeurs de cellule d’une plage sont définies avec un tableau de tableaux.
    • Les nouvelles lignes sont créées dans un tableau en appelant la méthode add() de collection de ligne du tableau. Vous pouvez ajouter plusieurs lignes dans un seul appel de add() en incluant plusieurs tableaux de valeurs de cellule dans le tableau parent transmis en tant que deuxième paramètre.

11. À l’intérieur de la fonction createTable(), remplacez TODO3 par le code suivant:

    expensesTable.columns.getItemAt(3).getRange().numberFormat = [['\u20AC#,##0.00']];
    expensesTable.getRange().format.autofitColumns();
    expensesTable.getRange().format.autofitRows();
    

    Remarque

    • Le code recherche une référence à la colonne Amount en transmettant son index de base zéro à la méthode getItemAt() de collection de colonnes du tableau.
    • Les objets de collection Excel.js, tels que TableCollection, WorksheetCollection et TableColumnCollection ont une propriété items qui correspond à un tableau de types d’objet enfant, comme Table ou Worksheet ou TableColumn ; mais un objet *Collection n’est pas lui-même un tableau.
    • Le code définit ensuite la plage de la colonne Amount sous la forme Euros à la deuxième décimale.
    • Enfin, il s’assure que la largeur des colonnes et la hauteur des lignes sont assez grandes pour contenir l’élément de données le plus long (ou le plus haut). Notez que le code doit rechercher des objets Range à mettre en forme. Les objets TableColumn et TableRow n’ont pas de propriétés de mise en forme.

12. Vérifiez que vous avez enregistré toutes les modifications que vous avez apportées au projet.

Test du complément

1. Pour démarrer le serveur web local et charger indépendamment votre complément, procédez comme suit.

   Remarque

   Les compléments Office doivent utiliser le protocole HTTPS, et non HTTP, même lorsque vous développez. Si vous êtes invité à installer un certificat après avoir exécuté une des commandes suivantes, acceptez d’installer le certificat fourni par le générateur Yeoman.

   Conseil

   Si vous testez votre complément sur macOS, exécutez la commande suivante dans le répertoire racine de votre projet avant de continuer. Lorsque vous exécutez cette commande, le serveur web local démarre.

   npm run dev-server
   

   • Pour tester votre complément dans Excel, exécutez la commande suivante dans le répertoire racine de votre projet. Cela a pour effet de démarrer le serveur web local (s’il n’est pas déjà en cours d’exécution) et d’ouvrir Excel avec votre complément chargé.

     npm start
     

   • Pour tester votre complément dans Excel sur le web, exécutez la commande suivante dans le répertoire racine de votre projet. Lorsque vous exécutez cette commande, le serveur web local démarre (s’il n’est pas déjà en cours d’exécution).

     npm run start:web
     

     Pour utiliser votre complément, ouvrez un nouveau document dans Excel sur le web, puis chargez la version test de votre complément en suivant les instructions de l’article relatif au chargement de version test des compléments Office dans Office sur le web.

2. Dans Excel, sélectionnez l’onglet Accueil, puis le bouton Afficher le volet Office du ruban pour ouvrir le volet Office du complément.

   

3. Dans le volet Office, sélectionnez le bouton Créer un tableau.

   

Filtrer et trier un tableau

Dans cette section, vous allez filtrer et trier le tableau que vous avez créé précédemment.

Filtrage du tableau

1. Ouvrez le fichier ./src/taskpane/taskpane.html.

2. Recherchez l’élément <button> du bouton create-table, puis ajoutez la balise suivante après cette ligne :

   <button class="ms-Button" id="filter-table">Filter Table</button><br/><br/>
   

3. Ouvrez le fichier ./src/taskpane/taskpane.js.

4. Dans l’appel de méthode Office.onReady(), recherchez la ligne suivante dans la méthode Office.onRead() :

   document.getElementById("create-table").onclick = createTable;
   

   Ajoutez le code suivant juste après :

   document.getElementById("filter-table").onclick = filterTable;
   

5. Ajoutez la fonction suivante à la fin du fichier :

   async function filterTable() {
     await Excel.run(async (context) => {
   
       // TODO1: Queue commands to filter out all expense categories except
       //        Groceries and Education.
   
       await context.sync();
     })
     .catch(function (error) {
       console.log("Error: " + error);
       if (error instanceof OfficeExtension.Error) {
         console.log("Debug info: " + JSON.stringify(error.debugInfo));
       }
     });
   }
   

6. À l’intérieur de la fonction filterTable(), remplacez TODO1 par le code suivant:

   const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
   const expensesTable = currentWorksheet.tables.getItem('ExpensesTable');
   const categoryFilter = expensesTable.columns.getItem('Category').filter;
   categoryFilter.applyValuesFilter(['Education', 'Groceries']);
   

   Remarque

   • Le code obtient tout d’abord une référence à la colonne à filtrer en transférant le nom de la colonne à la méthode getItem(), au lieu de transmettre son index à la méthode getItemAt() comme le fait la méthode createTable(). Puisque les utilisateurs peuvent déplacer des colonnes de tableau, la colonne d’un index donné peut être modifiée après la création du tableau. Il est plus sûr d’utiliser le nom de la colonne pour obtenir une référence à la colonne. Dans le didacticiel précédent, nous avons utilisé getItemAt en toute sécurité, car nous l’avons utilisée dans la même méthode que celle qui crée le tableau, il n’y a donc aucune chance qu’un utilisateur ait déplacé la colonne.
   • La méthode applyValuesFilter() est l’une des nombreuses méthodes de filtrage sur l’objet Filter.

Tri du tableau

1. Ouvrez le fichier ./src/taskpane/taskpane.html.

2. Recherchez l’élément <button> du bouton filter-table, puis ajoutez la balise suivante après cette ligne :

   <button class="ms-Button" id="sort-table">Sort Table</button><br/><br/>
   

3. Ouvrez le fichier ./src/taskpane/taskpane.js.

4. Dans l’appel de méthode Office.onReady(), recherchez la ligne suivante dans la méthode Office.onRead() :

   document.getElementById("filter-table").onclick = filterTable;
   

   Ajoutez le code suivant juste après :

   document.getElementById("sort-table").onclick = sortTable;
   

5. Ajoutez la fonction suivante à la fin du fichier :

   async function sortTable() {
     await Excel.run(async (context) => {
   
       // TODO1: Queue commands to sort the table by Merchant name.
   
       await context.sync();
     })
     .catch(function (error) {
       console.log("Error: " + error);
       if (error instanceof OfficeExtension.Error) {
         console.log("Debug info: " + JSON.stringify(error.debugInfo));
       }
     });
   }
   

6. À l’intérieur de la fonction sortTable(), remplacez TODO1 par le code suivant:

   const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
   const expensesTable = currentWorksheet.tables.getItem('ExpensesTable');
   const sortFields = [
     {
       key: 1,            // Merchant column
       ascending: false,
     }
   ];
   
   expensesTable.sort.apply(sortFields);
   

   Remarque

   • Le code crée un tableau d’objets SortField qui ne comporte qu’un seul membre puisque le complément ne trie que la colonne Merchant.
   • La propriété key d’un objet SortField est l’index de la colonne à trier qui part de zéro.
   • Le membre sort d’un objet Table est un objet TableSort, et non une méthode. Les objets SortField sont transmis à la méthode apply() de l’objet TableSort.

7. Vérifiez que vous avez enregistré toutes les modifications que vous avez apportées au projet.

Test du complément

1. Si le serveur web local est déjà en cours d’exécution et que votre complément est déjà chargé dans Excel, passez à l’étape 2. Sinon, démarrez le serveur web local et chargez la version test de votre complément :

   • Pour tester votre complément dans Excel, exécutez la commande suivante dans le répertoire racine de votre projet. Cela a pour effet de démarrer le serveur web local (s’il n’est pas déjà en cours d’exécution) et d’ouvrir Excel avec votre complément chargé.

     npm start
     

   • Pour tester votre complément dans Excel sur le web, exécutez la commande suivante dans le répertoire racine de votre projet. Lorsque vous exécutez cette commande, le serveur web local démarre (s’il n’est pas déjà en cours d’exécution).

     npm run start:web
     

     Pour utiliser votre complément, ouvrez un nouveau document dans Excel sur le web, puis chargez la version test de votre complément en suivant les instructions de l’article relatif au chargement de version test des compléments Office dans Office sur le web.

2. Si le volet Office du complément n’est pas déjà ouvert dans Excel, accédez à l’onglet Accueil , puis sélectionnez le bouton Afficher le volet Tâches dans le ruban pour l’ouvrir.

3. Si la tableau que vous avez ajoutée précédemment dans ce didacticiel ne figure pas dans la feuille de calcul ouverte, sélectionnez le bouton Créer un tableau dans le volet Office.

4. Sélectionnez le bouton Filtrer le tableau et le bouton Trier le tableau dans n’importe quel ordre.

   

Création d’un graphique (chart)

Dans cette section, vous allez créer un graphique à l’aide de données provenant du tableau précédemment créé, puis vous allez mettre en forme le graphique.

Un graphique à l’aide de données du tableau de graphique (chart)

1. Ouvrez le fichier ./src/taskpane/taskpane.html.

2. Recherchez l’élément <button> du bouton sort-table, puis ajoutez la balise suivante après cette ligne :

   <button class="ms-Button" id="create-chart">Create Chart</button><br/><br/>
   

3. Ouvrez le fichier ./src/taskpane/taskpane.js.

4. Dans l’appel de méthode Office.onReady(), recherchez la ligne suivante dans la méthode Office.onRead() :

   document.getElementById("sort-table").onclick = sortTable;
   

   Ajoutez le code suivant juste après :

   document.getElementById("create-chart").onclick = createChart;
   

5. Ajoutez la fonction suivante à la fin du fichier :

   async function createChart() {
     await Excel.run(async (context) => {
   
       // TODO1: Queue commands to get the range of data to be charted.
   
       // TODO2: Queue command to create the chart and define its type.
   
       // TODO3: Queue commands to position and format the chart.
   
       await context.sync();
     })
     .catch(function (error) {
       console.log("Error: " + error);
       if (error instanceof OfficeExtension.Error) {
         console.log("Debug info: " + JSON.stringify(error.debugInfo));
       }
     });
   }
   

6. À l’intérieur de la fonction createChart(), remplacez TODO1 par le code suivant. Pour exclure la ligne d’en-tête, le code utilise la méthode Table.getDataBodyRange() pour obtenir la plage de données que vous souhaiter représenter sous forme de graphique à la place de la méthode getRange().

   const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
   const expensesTable = currentWorksheet.tables.getItem('ExpensesTable');
   const dataRange = expensesTable.getDataBodyRange();
   

7. À l’intérieur de la fonction createChart(), remplacez TODO2 par le code suivant:

   const chart = currentWorksheet.charts.add('ColumnClustered', dataRange, 'auto');
   

   Remarque

   • Le premier paramètre transmis à la méthode add() spécifie le type de graphique. Il en existe plusieurs dizaines de types.
   • Le deuxième paramètre spécifie la plage de données à inclure dans le graphique.
   • Le troisième paramètre détermine si une série de points de données provenant du tableau doit être représentée sous forme de graphique par ligne ou par colonne. L’option auto demande à Excel de déterminer la meilleure méthode.

8. À l’intérieur de la fonction createChart(), remplacez TODO3 par le code suivant. La majeure partie du code est explicite :

   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 €';
   

   Remarque

   • Les paramètres de la méthode setPosition() spécifient les cellules situées en haut à gauche et en bas à droite de la zone de feuille de calcul devant contenir le graphique. Excel peut ajuster des éléments, tels que la largeur de ligne pour que le graphique s’affiche correctement dans l’espace attribué.
   • Une « série » est un ensemble de points de données dans une colonne du tableau. Étant donné qu’il n’existe qu’une seule colonne de type sans chaîne dans le tableau, Excel déduit que la colonne est la seule colonne de points de données pour le graphique. Il interprète les autres colonnes comme des étiquettes de graphique. Par conséquent, il y aura simplement une série dans le graphique et un index 0. Il s’agit de celle à étiqueter avec « Valeur en € ».

9. Vérifiez que vous avez enregistré toutes les modifications que vous avez apportées au projet.

Test du complément

1. Si le serveur web local est déjà en cours d’exécution et que votre complément est déjà chargé dans Excel, passez à l’étape 2. Sinon, démarrez le serveur web local et chargez la version test de votre complément :

   • Pour tester votre complément dans Excel, exécutez la commande suivante dans le répertoire racine de votre projet. Cela a pour effet de démarrer le serveur web local (s’il n’est pas déjà en cours d’exécution) et d’ouvrir Excel avec votre complément chargé.

     npm start
     

   • Pour tester votre complément dans Excel sur le web, exécutez la commande suivante dans le répertoire racine de votre projet. Lorsque vous exécutez cette commande, le serveur web local démarre (s’il n’est pas déjà en cours d’exécution).

     npm run start:web
     

     Pour utiliser votre complément, ouvrez un nouveau document dans Excel sur le web, puis chargez la version test de votre complément en suivant les instructions de l’article relatif au chargement de version test des compléments Office dans Office sur le web.

2. Si le volet Office du complément n’est pas déjà ouvert dans Excel, accédez à l’onglet Accueil , puis sélectionnez le bouton Afficher le volet Tâches dans le ruban pour l’ouvrir.

3. Si le tableau que vous avez ajouté précédemment dans ce didacticiel ne figure pas dans la feuille de calcul ouverte, sélectionnez le bouton Créer un tableau, puis le bouton Filtrer un tableau et le bouton Trier le tableau dans n’importe quel ordre.

4. Sélectionnez le bouton Créer un graphique . Un graphique est créé dans lequel seules les données provenant des lignes filtrées sont incluses. Les étiquettes sur les points de données en bas sont organisées selon l’ordre de tri du graphique, à savoir les noms de marchand par ordre alphabétique inversé.

Résumé

Dans cet exercice, vous avez découvert comment utiliser les tableaux et les graphiques à l’aide de l’API JavaScript pour Excel d’Office.js. Après la création d'un tableau, vous avez découvert comment appliquer des actions de tri et de filtrage sur les données contenues dans le tableau. Vous avez également découvert comment créer et modifier des graphiques dans le classeur à l’aide de votre complément Excel personnalisé.

Testez vos connaissances

1.

Comment obtenir une instance du contexte Excel actuel à partir de l’API JavaScript pour Excel ?

Il faut appeler la méthode Excel.run((context) => {..}) qui renverra le contexte Excel actuel.

Il est possible d’obtenir une référence au contexte Excel à l’aide de l’API this.context.current.

Il faut utiliser l’API Excel.getContext() pour obtenir une instance du contexte Excel actuel.

2.

Comment un complément peut-il détecter la version de l’API JavaScript pour Excel prise en charge dans le composant actuel ?

Microsoft Excel utilise toujours la dernière version de l’API JavaScript pour Excel disponible. Cette vérification est donc superflue.

Il faut utiliser la méthode Office.context.requirements.isSetSupported('ExcelApi','#.#').

Il faut utiliser la méthode Office.context.getCurrentVersion('ExcelApi').

Vous devez répondre à toutes les questions avant de vérifier votre travail.

Continuer