All posts in API JavaScript

Cette section va vous montrer comment sélectionner la ligne de titre dans le tableau.

async function selTitres() {

  await Excel.run(async function(context) {

    let feuille = context.workbook.worksheets.getActiveWorksheet();

    let t = feuille.tables.getItem("ventes");

    t.getHeaderRowRange().select();

  });

}

Après avoir récupéré le tableau ventes dans la feuille courante :

let feuille = context.workbook.worksheets.getActiveWorksheet();

let t = feuille.tables.getItem("ventes");

La méthode getHeaderRowRange().select() est appliquée au tableau :

t.getHeaderRowRange().select();

Cette section s’intéresse au tri d’un tableau. Ici, nous allons effectuer un tri croissant sur la première colonne :

async function trier() {

  await Excel.run(async function (context) {

    let feuille = context.workbook.worksheets.getActiveWorksheet();

    let t = feuille.tables.getItem("ventes");

    t.sort.apply([

      {

        key: 0,

        ascending: true

      },

    ], true);

  });

}

Après avoir récupéré la feuille courante et le tableau ventes :

let feuille = context.workbook.worksheets.getActiveWorksheet();

let t = feuille.tables.getItem("ventes");

La méthode sort.apply() est appliquée au tableau. Cette méthode admet deux paramètres :

• Un tableau d’objets JSON qui indique la ou les colonnes à trier (key) et l’ordre de tri (ascending).
• Un booléen qui indique si la casse des caractère doit (true) ou ne doit pas (false) être un critère de tri.

Ici, le tri se fait sur la première colonne (key: 0). Les données sont classées par ordre alphabétique (ascending: true) et on tient compte de la casse des caractères (true) :

t.sort.apply([

{

key: 0,

ascending: true

},

], true);

Voici le résultat :

Pour appliquer un tri décroissant sur la première colonne puis un tri croissant sur la deuxième colonne, vous auriez utilisé ce code :

t.sort.apply(

[

{

key: 0,

ascending: false

},

{

key: 1,

ascending: true

}

],

true

);

Voici le résultat :

Cette section va vous montrer comment supprimer le filtre mis en place dans la section précédente. Voici le code à utiliser :

async function stopFiltre() {

  await Excel.run(async function(context) {

    let feuille = context.workbook.worksheets.getActiveWorksheet();

    let t = feuille.tables.getItem("ventes");

    t.clearFilters();

  });

}

Comme vous pouvez le voir, il n’y a rien de plus simple : il suffit d’appliquer la méthode clearFilters() au tableau.

Pour illustrer le filtrage des données, nous allons travailler sur le tableau ventes :

Ce code est disponible en cliquant ici

A titre d’exemple, nous allons définir un filtre pour n’afficher que les lignes dont la cellule Nombre vaut 1 ou 2. Voici le code utilisé :

async function filtre() {

await Excel.run(async function(context) {

let feuille = context.workbook.worksheets.getActiveWorksheet();

let t = feuille.tables.getItem("ventes");

let filtreNombre = t.columns.getItem("Nombre").filter;

filtreNombre.apply({

filterOn: Excel.FilterOn.values,

values: ["1", "2"]

});

});

}

Après avoir récupéré la feuille active, puis le tableau ventes :

let feuille = context.workbook.worksheets.getActiveWorksheet();

let t = feuille.tables.getItem("ventes");

Un filtre est défini sur la colonne Nombre :

let filtreNombre = t.columns.getItem("Nombre").filter;

La méthode apply() définit la nature du filtre. Ici, le filtre porte sur les valeurs des cellules. Seules les lignes dont la cellule Nombre vaut 1 ou 2 sont retenues :

filtreNombre.apply({

filterOn: Excel.FilterOn.values,

values: ["1", "2"]

});

Cette section va vous montrer comment insérer des données JSON dans un tableau Excel. Avant d’aller plus loin, nous allons faire deux petits apartés sur les fonctions fléchées (arrow functions) et sur la fonction JavaScript map().

Un petit rappel sur les fonctions fléchées

Ce code :

var hello = function() {

  return 'Hello World!';

}

Est équivalent à :

let hello = () => 'Hello World!';

En effet, les accolades ne sont pas nécessaires car la fonction contient une seule instruction qui effectue un return :

Allons un peu plus loin en ajoutant un paramètre à la fonction anonyme. Ce code :

var hello = function(nom) {

  return 'Hello ' + nom;

}

Est équivalent à :

let hello = (nom) => 'Hello ' + nom;

Ici encore, les accolades ne sont pas nécessaires car la fonction contient une seule instruction qui effectue un return :

Un second rappel sur la fonction JavaScript map()

Ces instructions stockent dans le tableau x[] la racine carrée de chacune des valeurs du tableau numbers[] :

var numbers = [4, 9, 16, 25];

var x = numbers.map(Math.sqrt);

La fonction map() :

• Parcourt le tableau numbers[] sur lequel elle est appliquée.
• Crée un tableau de même taille (ici, x[]).
• Applique la fonction passée en argument (sqrt()) à chacun des éléments du tableau numbers[] et stocke les résultats dans le tableau x[].

En tenant compte de ce qui a été dit dans ces deux apartés, nous allons définir ce tableau dans la feuille de calcul courante à partir de données JSON :

Voici le code utilisé :

Ce code est disponible en cliquant ici

async function ajoutJSON() {

  await Excel.run(async function(context) {

    let feuille = context.workbook.worksheets.getActiveWorksheet();

    // Ajout de la ligne de titre

    let tableauJSON = feuille.tables.add("A13:D13", true);

    tableauJSON.name = "tableaujson";

    tableauJSON.getHeaderRowRange().values = [["Formation", "Nombre", "Prix", "Total"]];

    // Définition du tableau de données en JSON

    var data = [

      {

        FORMATION: "Windows 10",

        NOMBRE: "1",

        PRIX: "950",

        TOTAL: "950"

      },

      {

        FORMATION: "Office 2019",

        NOMBRE: "2",

        PRIX: "1400",

        TOTAL: "2800"

      }

    ];

    // Injection des données JSON à la suite de la ligne de titre

    var newData = data.map((item) => [item.FORMATION, item.NOMBRE, item.PRIX, item.TOTAL]);

    tableauJSON.rows.add(null, newData);

  });

}

Les premières instructions sont très classiques. La feuille courante est placée dans la variable feuille :

let feuille = context.workbook.worksheets.getActiveWorksheet();

La ligne de titre est définie des cellules A13 à D13 :

let tableauJSON = feuille.tables.add("A13:D13", true);

tableauJSON.name = "tableaujson";

tableauJSON.getHeaderRowRange().values = [["Formation", "Nombre", "Prix", "Total"]];

Les données sont alors définies dans un tableau JSON et stockées dans la variable data :

var data = [

  {

    FORMATION: "Windows 10",

    NOMBRE: "1",

    PRIX: "950",

    TOTAL: "950"

  },

  {

    FORMATION: "Office 2019",

    NOMBRE: "2",

    PRIX: "1400",

    TOTAL: "2800"

  }

];

La fonction map() passe en revue les données contenues dans le tableau data[] sur lequel elle est appliquée. Elle parse les clés FORMATION, NOMBRE, PRIX et TOTAL et stocke les nouvelles données dans le tableau newData[] :

var newData = data.map((item) => [item.FORMATION, item.NOMBRE, item.PRIX, item.TOTAL]);

Le tableau newData[] contient deux tableaux (un par ligne) :

[

    ["Windows 10", "1", "950", "950"],

    ["Office 2019", "2", "1400", "2800"]

]

C’est exactement ce qu’il faut à la fonction rows.add() pour injecter ces données à la suite de la ligne de titre (null en premier argument) :

tableauJSON.rows.add(null, newData);

La fonction ajoutJSON() est définie en tant que gestionnaire évènementiel attaché au bouton d’id ajout de l’interface :

$("#ajout").click(() => tryCatch(ajoutJSON));

Voici le code HTML de l'interface :

<button id="ajout" class="ms-Button">

    <span class="ms-Button-label">Créer un tableau avec des données JSON</span>

</button>

Pour terminer, nous allons ajouter une ligne au tableau précédent pour calculer le total de la colonne B, la moyenne de la colonne C et le total de la ligne D :

Voici le code utilisé :

async function somme() {

  await Excel.run(async function(context) {

    let feuille = context.workbook.worksheets.getActiveWorksheet();

    let t = feuille.tables.getItem("ventes");

    let nouveau = [["", "=SUM(B2:B9)", "=AVERAGE(C2:C9)", "=SUM(D2:D9)"]];

    t.rows.add(null, nouveau);

  });

}

Comme dans la section précédente, la feuille courante est récupérée et placée dans la variable feuille :

let feuille = context.workbook.worksheets.getActiveWorksheet();

Puis le tableau ventes est placé dans la variable t :

let t = feuille.tables.getItem("ventes");

Il suffit maintenant de définir le tableau de tableau nouveau qui contient les formules adéquates (remarquez l’utilisation des fonctions prédéfinies d’Excel SUM() et AVERAGE()) :

let nouveau = [["", "=SUM(B2:B9)", "=AVERAGE(C2:C9)", "=SUM(D2:D9)"]];

Et de l’ajouter à la fin (premier paramètre initialisé à null) du tableau :

t.rows.add(null, nouveau);

A titre d’exemple, nous allons ajouter une ligne sous les lignes existantes et une colonne qui calcule le montant total en multipliant les cellules Nombre et Prix :

Voici le code utilisé :

async function ajoutDonnees() {

  await Excel.run(async function(context) {

    let feuille = context.workbook.worksheets.getActiveWorksheet();

    let t = feuille.tables.getItem("ventes");

    let nouveau = [["Office 2019",13,1400]];

    t.rows.add(null, nouveau);

    t.columns.add(null, [

      ["Total"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"],

      ["=[Nombre]*[Prix]"]

    ]);

  });

}

Ce code est assez simple à comprendre.

Après avoir récupéré la feuille active :

let feuille = context.workbook.worksheets.getActiveWorksheet();

Le tableau ventes est copié dans la variable t :

let t = feuille.tables.getItem("ventes");

Une nouvelle ligne est définie dans un tableau de tableau et affectée à la variable nouveau. Elle est ensuite ajoutée à la fin du tableau (premier paramètre initialisé à null) :

let nouveau = [["Office 2019",13,1400]];

t.rows.add(null, nouveau);

La deuxième partie du code ajoute une colonne à la fin du tableau (premier paramètre initialisé à null) avec la fonction columns.add(). Les données sont passées dans un tableau de tableaux. Remarquez la façon dont on fait référence aux colonnes nommées Nombre et Prix :

t.columns.add(null, [

  ["Total"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"],

  ["=[Nombre]*[Prix]"]

]);