All posts in API JavaScript

Pour gérer et analyser les données contenues dans une plage de cellules, indépendamment des autres données de la feuille de calcul, le plus simple consiste à utiliser un tableau. Cette section va vous montrer comment créer un tableau, y ajouter des données, le filtrer, y sélectionner des données et le transformer en un objet JSON.

Dans un premier temps, nous allons créer ce tableau :

Création du tableau

Ce code est disponible en cliquant ici

Voici le code utilisé :

$("#creation").click(() => tryCatch(creation));

async function creation() {

  await Excel.run(async function(context) {

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

    // Création du tableau avec en-tête

    let t = feuille.tables.add("A1:C1", true);

    t.name = "ventes"; // Nom du tableau

    t.getHeaderRowRange().values = [["Formation", "Nombre", "Prix"]]; // En-têtes

    // Ajout de lignes à la fin du tableau ou index

    t.rows.add(null, [

      ["Office 2016", 2, 1200],

      ["Office 2019", 3, 1400],

      ["Office 2016", 1, 1200],

      ["Windows 10", 1, 950],

      ["Office 2019", 3, 1400],

      ["Windows 10", 9, 950],

      ["Office 2019", 2, 1400]

    ]);

  });

}

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

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

La méthode tables.add() est utilisée pour créer la ligne d’en-tête du tableau. La valeur true du deuxième paramètre indique qu’il s’agit d’une ligne d’en-tête :

let t = feuille.tables.add("A1:C1", true);

La propriété name donne un nom au tableau :

t.name = "ventes";

Enfin, les valeurs sont affectées aux cellules avec la méthode getHeaderRowRange() :

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

Les lignes de données sont ajoutées avec la méthode rows.add(). Le premier paramètre initialisé à null provoque l’ajout des données après la ligne de titre. Les données sont passées à la fonction sous la forme d’un tableau de tableaux :

t.rows.add(null, [

  ["Office 2016", 2, 1200],

  ["Office 2019", 3, 1400],

  ["Office 2016", 1, 1200],

  ["Windows 10", 1, 950],

  ["Office 2019", 3, 1400],

  ["Windows 10", 9, 950],

  ["Office 2019", 2, 1400]

]);

La fonction removeDuplicates() permet de supprimer les doublons dans la plage de cellules sur laquelle elle est appliquée. Si la plage comporte plusieurs colonnes les doublons sont recherchés indépendamment dans chaque colonne. Voici la syntaxe de cette fonction :

let resultat = plage.removeDuplicates(colonne, entete);

Où :

• plage est la plage de cellules concernée;
• colonne représente la colonne où chercher les doublons;
• entête indique si un en-tête est présent (true) ou s’il n’y a pas d’en-tête (false) dans la plage;
• resultat est un objet qui spécifie le nombre de doublons retirés (propriété removed) et le nombre de lignes restantes (propriété uniqueRemaining).

Voici un exemple de code :

async function supprimerDoublons() {

  await Excel.run(async (context) => {

    const plage = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C5");

    plage.removeDuplicates([0], false);

  });

}

Après avoir défini la plage dans laquelle les doublons seront recherchés :

const plage = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C5");

La fonction removeDuplicates() est appliquée sur cette plage. Ici, la recherche des doublons se fait dans la première colonne ([0]) et les données ne comportent pas d’en-tête (false) :

plage.removeDuplicates([0], false);

Selon la colonne choisie, les résultats peuvent être très différents :

Données originales

removeDuplicates([0],false)

removeDuplicates([1],false)

Cette section va vous montrer comment copier une plage de cellules dans une autre.

A titre d’exemple, la plage A1:D6 va être copiée dans la plage A10:D15 :

Pour recopier les valeurs, utilisez ce code :

async function run() {

  await Excel.run(async function (context) {

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

    let plage = feuille.getRange('A1:D6');

    plage.load('values');

    await context.sync();

    let valeurs = plage.values;

    let plage2 = feuille.getRange('A10:D15');

    plage2.values = valeurs;

  });

}

Je pense que vous comprenez sans problème ce code. Juste au cas où :

La plage A1:D6 est obtenue avec la fonction getRange() et placée dans la variable plage :

let plage = feuille.getRange('A1:D6');

La propriété values des cellules de la plage est lue dans la feuille de calcul. Leur contenu est disponible après l’exécution du await :

plage.load('values');

await context.sync();

Les valeurs de la plage sont alors placées dans la variable valeurs :

let valeurs = plage.values;

Puis elles sont recopiées dans la plage A10:D15 :

let plage2 = feuille.getRange('A10:D15');

plage2.values = valeurs;

Ici, ce sont bien les valeurs et non les formules qui sont recopiées dans la plage A10:D15. Vous pouvez le vérifier en cliquant sur (par exemple) la cellule D11 :

Pour recopier les formules en les adaptant à la nouvelle plage, remplacez ces instructions :

plage.load('values');

let valeurs = plage1.values;

plage2.values = valeurs;

Par celles-ci :

plage.load(formulasR1C1');

let formules = plage1.formulasR1C1;

plage2.formulasR1C1 = formules;

C’est aussi simple que cela. Maintenant, les formules sont recopiées et elles s’adaptent à la nouvelle plage de cellules :

Nous allons raisonner sur le même tableau que dans l’exemple précédent :

Pour lire les cellules une par une et les afficher dans la console, on commence par récupérer la plage avec la fonction getRange() qui retourne un array de array.

Pour parcourir ces éléments, vous pouvez utiliser une boucle for of ou une boucle forEach.

Avec une boucle for of

Voici le code à utiliser avec une boucle for of :

async function run() {

  await Excel.run(async function (context) {

    const ws = context.workbook.worksheets.getActiveWorksheet();

    let plage = ws.getRange('A1:D6');

    plage.load('values');

    await context.sync();

    for (let i of plage.values) // Récupère les lignes

      for (let j of i) // Récupère les cellules

        console.log(j);

  });

}

La boucle for (a of b) parcourt un a un tous les éléments de b. A chaque étape de la boucle, a contient un des éléments de b. Ici, les données parcourues se trouvent dans un tableau de tableau :

[

    ["Référence", "HT", "Quantité", "TTC"],

    ["A34", 120.5, 12, "=R[-1]C[-2]*R[-1]C[-1]"],

    ["B18", 45.6, 45, "=R[-1]C[-2]*R[-1]C[-1]"],

    ["A66", 12.25, 120, "=R[-1]C[-2]*R[-1]C[-1]"],

    ["C42", 146.65, 26, "=R[-1]C[-2]*R[-1]C[-1]"],

    ["G29", 10, 84, "=R[-1]C[-2]*R[-1]C[-1]"]

]

La première boucle for of récupère un à un chacun des tableaux internes, c’est-à-dire chacune des lignes de la plage. La seconde boucle parcourt chacun des éléments extraits par la première boucle et extrait une à une les valeurs, c’est-à-dire le contenu des cellules de la ligne examinée.

Voici ce qui s’affiche dans la console :

Référence

HT

Quantité

TTC

34

120.5

12

1446

B18

45.6

45

2052

A66

12.25

120

1470

C42

146.65

26

3812.9

G29

10

84

840

Avec une boucle forEach()

Ce code est disponible en cliquant ici

Une deuxième possibilité s’offre à vous : l’utilisation de la boucle forEach(). Voici la syntaxe de la fonction forEach() pour un tableau à une dimension :

tableau.forEach((item,index)=>{

  // Traitement

})

Voici la syntaxe de la fonction forEach() pour un tableau à deux dimensions :

tableau.forEach((ligne,index1)=>{

  ligne.forEach((cellule,index2)=>{

    // Traitement

  });

})

plage.values contient un tableau de tableaux qui représente les cellules A1 à D6. Pour le parcourir, il faut donc utiliser deux boucles forEach() imbriquées :

async function run() {

  await Excel.run(async function (context) {

    const ws = context.workbook.worksheets.getActiveWorksheet();

    let plage = ws.getRange('A1:D6');

    plage.load('values');

    await context.sync();

    plage.values.forEach((ligne, index1)=>{

      ligne.forEach(function (cellule, index2) {

        console.log(cellule);

      });

    });

  });

}

Les données affichées dans la console sont les mêmes qu’avec les boucles for of.

Vous savez maintenant sélectionner toutes sortes de plages de cellules. Voyons comment lire le contenu des cellules sélectionnées.

Nous allons travailler avec ces données :

Le but du jeu va être de récupérer les valeurs et les formules contenues dans ce tableau, sachant que la colonne D contient des formules du type =B2*C2 (pour la cellule D3).

Pour cela, vous chargerez tour à tour les propriétés values, text, formulas et formulasR1C1 :

• values : Valeurs brutes des cellules.
• text : valeurs textuelles des cellules.
• formulas : formules en notation A1 (lettre pour la colonne, chiffre pour la ligne).
• formulasR1C1 : formules en notation R1C1 (index relatifs de la ligne et de la colonne).

Voici le code utilisé :

async function run() {

  await Excel.run(async function(context) {

    const ws = context.workbook.worksheets.getActiveWorksheet(); // feuille active

    let plage = ws.getRange('A1:D6');

    plage.load('values');

    await context.sync();

    console.log(plage.values); //Toutes les données de la plage sous la forme array de array

    console.log(plage.values[1]); // La deuxième ligne (basé 0) sous la forme d'un array

    console.log(plage.values[1][0]); // La première cellule de la deuxième ligne (basé 0)

  });

}

Et voici le résultat dans la console :

[

    ["Référence", "HT", "Quantité", "TTC"],

    ["A34", 120.5, 12, 1446],

    ["B18", 45.6, 45, 2052],

    ["A66", 12.25, 120, 1470],

    ["C42", 146.65, 26, 3812.9],

    ["G29", 10, 84, 840]

]




["A34", 120.5, 12, 1446]




A34

Comme vous le voyez :

• values retourne toutes les données de la plage sous la forme d’un tableau de tableaux.
• values[1] retourne la deuxième ligne du tableau.
• values[1][0] retourne la première cellule de la deuxième ligne du tableau.

Remplacez values par text dans la méthode load() et dans les console.log() et vous obtiendrez ce résultat :

[

    ["Référence", "HT", "Quantité", "TTC"],

    ["A34", "120,50", "12", "1446,00"],

    ["B18", "45,60", "45", "2052,00"],

    ["A66", "12,25", "120", "1470,00"],

    ["C42", "146,65", "26", "3812,90"],

    ["G29", "10,00", "84", "840,00"]

]




["A34", "120,50", "12", "1446,00"]




A34

Remplacez text par formulas dans la méthode load() et dans les console.log() et vous obtiendrez ce résultat :

[

    ["Référence", "HT", "Quantité", "TTC"],

    ["A34", 120.5, 12, "=B2*C2"],

    ["B18", 45.6, 45, "=B3*C3"],

    ["A66", 12.25, 120, "=B4*C4"],

    ["C42", 146.65, 26, "=B5*C5"],

    ["G29", 10, 84, "=B6*C6"]

]




["A34", 120.5, 12, "=B2*C2"]




A34

Enfin, remplacez formulas par formulasR1C1 dans la méthode load() et dans les console.log() et vous obtiendrez ce résultat :

[

    ["Référence", "HT", "Quantité", "TTC"],

    ["A34", 120.5, 12, "=RC[-2]*RC[-1]"],

    ["B18", 45.6, 45, "=RC[-2]*RC[-1]"],

    ["A66", 12.25, 120, "=RC[-2]*RC[-1]"],

    ["C42", 146.65, 26, "=RC[-2]*RC[-1]"],

    ["G29", 10, 84, "=RC[-2]*RC[-1]"]

]




["A34", 120.5, 12, "=RC[-2]*RC[-1]"]




A34

Remarque

Petite précision sur la notation RC[-1] : cela signifie la cellule située sur la même ligne (R) et sur la colonne précédente (C[-1]).

Le tableau ci-après vous permettra de comparer plus facilement les données contenues dans les propriétés values, text, formulas et formulasR1C1 :

Voyons comment sélectionner une plage de cellules dans la feuille active. Nous allons par exemple sélectionner la plage A1:B8 dans cette feuille de calcul :

Voici le code utilisé :

async function run() {

  await Excel.run(async function(context) {

    const ws = context.workbook.worksheets.getActiveWorksheet();

    ws.getRange('A1:D6').select();

  });

}

Et voici le résultat :

Supposons que la plage A1:D6 ait été nommée Plage1. Pour la sélectionner, vous pouvez utiliser l’instruction :

ws.getRange('Plage1').select();

A la place de :

ws.getRange('A1:D6').select();

En utilisant la fonction getRangeByIndexes(), vous pouvez sélectionner les cellules par leurs emplacements dans la feuille :

getRangeByIndexes(ld, cd, nl, nc)

Où ld représente la ligne de départ, cd la colonne de départ, nl le nombre de lignes et nc le nombre de colonnes.

Par exemple, pour sélectionner la plage A1:D6, vous utiliserez cette instruction :

ws.getRangeByIndexes(0, 0, 6, 4).select();

Pour sélectionner de la plage qui contient des données dans la feuille, vous utiliserez la fonction getUsedRange() :

ws.getUsedRange().select();

Enfin, pour sélectionner la totalité de la feuille, vous utiliserez la fonction getRange() :

ws.getRange().select();

Voici quelques autres instructions intéressantes :

let plage = ws.getRange('A1:D1');

plage.getEntireColumn().select(); // Sélection des colonnes A à D

let plage = ws.getRange('A1:A6');

plage.getEntireRow().select();// Sélection des lignes 1 à 6

plage.getColumnsAfter(3).select(); //Sélection des 3 colonnes après la plage (même nb de lignes)

plage.getRowsBelow(3).select(); //Sélection des 3 lignes après la plage (même nb de colonnes)

plage.getRow(2).select(); //2ème ligne de la plage (basé 0)

plage.getCell(2,3).select(); //2ème ligne 3ème colonne de la sélection (basé 0)

let plage = ws.getRange('A1:D6');

plage.load('cellCount');

await context.sync();

console.log(plage.cellCount); // Nombre de cellules sélectionnées (ici, 24)

Voyons comment récupérer les propriétés du graphique actif (si aucun graphique n’est actif lorsque vous exécutez le code, une erreur sera générée). Nous allons raisonner sur ce graphique :

Voici le code utilisé :

async function run() {

  await Excel.run(async (context) => {

    const wb = context.workbook;

    const graphique = wb.getActiveChart();

    graphique.load(['chartType', 'height', 'title', 'width']);

    await context.sync();

    console.log(`Titre du graphique : ${graphique.title.text}`);

    console.log(`Type du graphique : ${graphique.chartType}`);

    console.log(`Dimensions : ${graphique.width} x ${graphique.height}`);

  });

}

Et voici le résultat :

Le code est assez simple.

La première instruction récupère le classeur et le stocke dans la constante wb :

const wb = context.workbook;

La deuxième instruction récupère le graphique actif et le stocke dans la constante graphique :

const graphique = wb.getActiveChart();

Les propriétés chartType, height, title et with de l’objet graphique sont lues avec la fonction load() :

graphique.load(['chartType', 'height', 'title', 'width']);

L’instruction suivante synchronise l’état entre les objets proxy de JavaScript et les objets réels dans Excel. Après son exécution, les propriétés chartType, height, title et with reflètent les caractéristiques du graphique :

await context.sync();

Les instructions console.log() suivantes affichent ces propriétés dans la console en utilisant des chaînes de caractères avec des expressions incluses :

console.log(`Titre du graphique : ${graphique.title.text}`);

console.log(`Type du graphique : ${graphique.chartType}`);

console.log(`Dimensions : ${graphique.width} x ${graphique.height}`);

Ce code est disponible en cliquant ici