All posts in API JavaScript

Pour travailler avec des dates et des heures, vous pouvez passer les fonctions natives de JavaScript ou utiliser la bibliothèque Moment.js. Nous allons voir ces deux alternatives.

Ce code est disponible en cliquant ici

Affichage de la date courante dans la cellule A1

Ici, on instancie la classe JavaScript Date et on utilise les méthodes getDate(), getMonth() et getFullYear() pour récupérer la date courante :

async function dateA1() {

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

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

    let d = new Date();

    let date = d.getDate() + "/" + (d.getMonth() + 1) + "/" + d.getFullYear();

    feuille.getRange("A1").values = [[date]];

    await context.sync();

  });

}

Voici un exemple d’exécution :

De nombreuses fonctions sont accessibles dans Excel. Ces fonctions sont attachées à l’objet context.workbook.functions. Pour y accéder, vous utiliserez leur nom anglais en minuscules :

context.workbook.functions.nom()

Où nom est le nom de la fonction à utiliser.

Cette section va vous montrer comment utiliser la fonction VLOOKUP() en JavaScript/TypeScript. Nous allons travailler avec ces données :

Ce code est disponible en cliquant ici

A titre d’exemple, nous allons rechercher le prix TTC correspondant à un achat de référence C42 en utilisant la fonction VLOOKUP() d’Excel :

VLOOKUP(valeur, plage, index, type)

Où :

• valeur est la valeur recherchée.
• plage est la plage dans laquelle se fait la recherche.
• index est le décalage pour atteindre la colonne recherchée.
• type indique si la recherche est approximative (true) ou exacte (false).

Voici le code utilisé :

async function run() {

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

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

    const range = feuille.getRange("A1:D6");

    const ttc = context.workbook.functions.vlookup('C42',range,4,false);

    ttc.load('value');

    await context.sync();

    console.log(`Prix TTC de la commande C42 : ${ttc.value}`)

  });

}

Les premières instructions récupèrent la feuille courante et la plage A1:D6 et les stockent dans les constantes feuille et range :

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

const range = feuille.getRange("A1:D6");

L’instruction suivante applique la fonction VLOOKUP() à la plage range et récupère la cellule correspondante sur la quatrième colonne de cette plage :

const ttc = context.workbook.functions.vlookup('C42',range,4,false);

La valeur située dans cette cellule est alors chargée et synchronise l’état entre les objets proxy de JavaScript et les objets réels dans Excel, ce qui provoque la mise à disposition de la valeur recherchée :

ttc.load('value');

await context.sync();

Il ne reste plus qu’à afficher la valeur TTC dans la console :

console.log(`Prix TTC de la commande C42 : ${ttc.value}`)

Voici le résultat :

Vous savez maintenant utiliser la fonction VLOOKUP(). Toutes les autres fonctions d’Excel s’utilisent de la même manière.

Un dernier exemple concernant la mise en forme conditionnelle. Vous pouvez ajouter des icônes dans les cellules numériques pour indiquer si les valeurs sont dans une plage inférieure, supérieure ou médiane.

Voici le code utilisé :

async function icones() {

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

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

    const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.iconSet);

    condFormat.iconSet.style = Excel.IconSet.threeTriangles;

  });

}

Après avoir récupéré la plage sur laquelle sera appliquée la mise en forme conditionnelle :

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

On lui ajoute une mise en forme conditionnelle de type iconSet :

const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.iconSet);

La dernière instruction définit le type des icônes affichées dans les cellules :

condFormat.iconSet.style = Excel.IconSet.threeTriangles;

Voici le résultat :

De nombreux autres types d’icônes peuvent être utilisées. Pour en savoir plus, utilisez l’intellisense après IconSet :

Les données d’une plage peuvent être représentées par des barres horizontales, de plus ou moins grande ampleur selon leurs valeurs.

async function dataBar() {

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

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

    const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.dataBar);

  });

}

Après avoir récupéré la plage sur laquelle sera appliquée la mise en forme conditionnelle :

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

On lui ajoute une mise en forme conditionnelle de type dataBar :

const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.dataBar);

Voici le résultat :

Il est également possible d’affecter des couleurs d’arrière-plan aux cellules en fonction de leurs valeurs. Voici le code utilisé :

async function echelle() {

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

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

    const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.colorScale);

    const critere = {

      minimum: {

        formula: null,

        type: Excel.ConditionalFormatColorCriterionType.lowestValue,

        color: "green"

      },

      midpoint: {

        formula: 50,

        type: Excel.ConditionalFormatColorCriterionType.percent,

        color: "yellow"

      },

      maximum: {

        formula: null,

        type: Excel.ConditionalFormatColorCriterionType.highestValue,

        color: "red"

      }

    };

    condFormat.colorScale.criteria = critere;

  });

}

Après avoir récupéré la plage sur laquelle la mise en forme conditionnelle doit être appliquée :

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

On lui ajoute une mise en forme conditionnelle de type colorScale :

const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.colorScale);

Le bloc de code suivant définit les critères de mise en forme.

La constante utilisée est un objet JSON qui définit les clés minimum, midpoint et maximum :

const critere = {

  minimum: {},

  midpoint: {},

  maximum: {}

}

La clé minimum correspond au point minimal du critère d’échelle de couleurs. La clé maximum correspond au point maximal du critère d’échelle de couleurs et la clé midpoint au point intermédiaire.

Ces trois clés sont des objets JSON qui définissent les clés formula, type et color :

• formula est une formule du type 0, <15, >=100 ou la valeur null si le critère ne nécessite aucune formule.
• type est un des types suivants : invalid, lowestValue, highestValue, number, percent, formula ou percentile.
• color est la couleur d’arrière-plan exprimée sous la forme d’une chaîne.

Dans cet exemple :

• Les valeurs minimales (type lowestValue) seront affichées en vert.
• Les valeurs maximales (type highestValue) seront affichées en rouge.
• Les valeurs médianes (type percent, formula 50) seront affichées en jaune.

Une fois que le critère a été défini, il est appliqué à la plage :

condFormat.colorScale.criteria = critere;

Voici le résultat :

Voyons maintenant comment afficher les nombres négatifs en rouge. Voici le code utilisé :

async function negatifEnRouge() {

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

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

    const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.cellValue);

    condFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };

    condFormat.cellValue.format.font.color = "red";

  });

}

Après avoir récupéré la plage sur laquelle on désire appliquer la mise en forme conditionnelle :

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

On ajoute une mise en forme conditionnelle en fonction de la valeur des cellules (cellValue) sur la plage :

const condFormat = plage.conditionalFormats.add(Excel.ConditionalFormatType.cellValue);

Puis on définit la règle de mise en forme dans la propriété rule :

condFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };

Cette propriété est un objet JSON composé de deux ou trois couples clé/valeur :

{

  formula1: 'form1',

  formula2: 'form2',

  operator: 'op'

}

Les formules sont du type =0, >=15, <0, etc..

L’opérateur est un des suivants : Invalid, Between, NotBetween, EqualTo, NotEqualTo, GreaterThan, LessThan, GreaterThanOrEqual ou LessThanOrEqual.

Dans cet exemple, on comprend que la mise en forme conditionnelle concerne les cellules dont la valeur est inférieure à zéro :

{ formula1: "=0", operator: "LessThan" }

La dernière instruction définit la couleur à affecter aux cellules lorsque la condition est vérifiée :

condFormat.cellValue.format.font.color = "red";

Voici le résultat :

Nous allons travailler avec ces données. La plage de cellules examinée sera B2:C5 :

Commençons par la suppression d’une mise en forme conditionnelle. Voici le code utilisé :

async function effacerLesRegles() {

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

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

    const condFormat = plage.conditionalFormats.clearAll();

  });

}

Après avoir récupéré la plage sur laquelle on désire annuler la mise en forme conditionnelle :

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

La fonction conditionalFormats().clearAll() est appliquée sur cette plage pour supprimer la mise en forme conditionnelle :

const condFormat = plage.conditionalFormats.clearAll();