All posts in API JavaScript

Il est parfois nécessaire de fusionner et de centrer une cellule de titre sur plusieurs colonnes. Cette opération est très simple en JavaScript.

Ici par exemple, la cellule E15 est fusionnée avec les cellules F15 et G15 et centrée sur ces trois colonnes :

async function fusionnerEtCentrer() {

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

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

    feuille1.getRange("E15").values = [["Paris 2024 JO d'été"]];

    feuille1.getRange("E15:G15").merge();

    feuille1.getRange("E15").format.horizontalAlignment = "Center";

  });

}

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

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

La cellule E15 est initialisée :

feuille1.getRange("E15").values = [["Paris 2024 JO d'été"]];

Elle est ensuite fusionnée avec la plage E15:G15 :

feuille1.getRange("E15:G15").merge();

Puis son contenu est centré sur ces trois cellules :

feuille1.getRange("E15").format.horizontalAlignment = "Center";

Voici le résultat. La cellule E15 occupe maintenant trois cellules en largeur et son contenu est centré :

Inversement, pour arrêter la fusion d’une cellule, vous utiliserez la fonction unmerge() :

feuille1.getRange("E15:G15").unmerge();

Appliquée à une plage de cellules quelconque, la fonction format.autofitColumns() s’assure que le contenu des cellules de cette plage est entièrement visible.

Ici par exemple, la fonction format.autofitColumns() est appliquée à la colonne B :

async function autofitB() {

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

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

    feuille1.getRange("B:B").format.autofitColumns();

  });

}

Vous vous demandez peut-être comment appliquer la fonction format.autofitColumns() sur tout le classeur ? Eh bien, il suffit de ne rien préciser en paramètre de la fonction getRange() pour que toute la feuille soit concernée :

async function autofit() {

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

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

    feuille1.getRange().format.autofitColumns();

  });

}

Pour modifier le format numérique d’une plage de cellules, vous utiliserez la propriété numberFormat. Ici par exemple, les colonnes B et D utiliseront le format 0.00 (2 chiffres après la virgule) :

async function colonnesBD() {

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

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

    feuille1.getRange("B:B").numberFormat = <any>"0.00";

    feuille1.getRange("D:D").numberFormat = <any>"0.00";

  });

}

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

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

La propriété numberFormat des colonnes B et D est modifiée. Remarquez l’utilisation de l’opérateur <any> pour désigner toutes les cellules de la plage :

feuille1.getRange("B:B").numberFormat = <any>"0.00";

feuille1.getRange("D:D").numberFormat = <any>"0.00";

Si vous voulez affecter un format particulier à une plage de cellules, vous devez affecter un tableau de tableaux à la propriété numberFormat. Ce tableau contiendra les divers formats souhaités exprimés sous la forme de chaînes de caractères. Ici par exemple, nous allons modifier le format de la plage C2:D4. Les cellules de la colonne C de cette plage auront le format 0.00 (2 chiffres après la virgule) et les cellules de la colonne D de cette plage auront le format 0.000 (3 chiffres après la virgule) :

async function plageC2D4() {

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

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

    feuille1.getRange("C2:D4").numberFormat = [["0.00", "0.000"], ["0.00", "0.000"], ["0.00", "0.000"]];

  });

}

Remarquez le tableau de tableaux affecté à la propriété numberFormat. La plage C2:D4 contient trois lignes de deux colonnes, ce qui explique les données de format passées à la propriété numberFormat :

[["0.00", "0.000"], ["0.00", "0.000"], ["0.00", "0.000"]]

Un autre exemple concernant la modification de l’apparence des feuilles de calcul Excel avec l’API JavaScript. Ici, nous allons modifier la couleur d’arrière-plan et l’alignement de la colonne A. Pour cela, nous agissons sur les propriétés format.horizontalAlignment et format.fill.color de la plage concernée :

async function colonneA() {

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

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

    feuille1.getRange("A:A").format.horizontalAlignment = "Right";

    feuille1.getRange("A:A").format.horizontalAlignment = "Right";

    feuille1.getRange("A:A").format.fill.color = "yellow";

  });

}

Vous pouvez agir sur l’apparence d’une plage de cellules en modifiant les propriétés de l’objet format.font : police, couleur, attributs. Pour en savoir plus sur les propriétés utilisables, consultez la page https://bit.ly/2MvXSQz.

Ce code est disponible en cliquant ici

A titre d’exemple, nous allons affecter la police Arial corps 16 gras italique rouge à ligne 1 de la feuille de calcul courante. Voici le code utilisé :

async function ligne1() {

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

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

    feuille1.getRange("1:1").format.font.name = "Arial";

    feuille1.getRange("1:1").format.font.size = 16;

    feuille1.getRange("1:1").format.font.italic = true;

    feuille1.getRange("1:1").format.font.bold = true;

    feuille1.getRange("1:1").format.font.color = "red";

    feuille1.getRange("1:1").format.font.color = "#ff0000";

  });

}

Ce code est très simple. Après avoir récupéré la feuille en cours :

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

Plusieurs propriétés de la ligne 1 (getRange(« 1 :1 »)) sont modifiées. Par exemple, la police :

feuille1.getRange("1:1").format.font.name = "Arial";

Dans les feuilles de calcul de grande taille, il est souvent pratique de figer une ou plusieurs lignes et colonnes. On dit que les volets ont été figés. La zone figée restera visible lorsque vous ferez défiler la feuille de calcul vers le bas ou vers la droite.

Il est possible de figer et de libérer les volets avec quelques instructions JavaScript/TypeScript.

Dans cet exemple, si les volets ne sont pas figés, la première ligne et la première colonne sont figées. Dans le cas contraire, la ligne et la colonne figées sont libérées.

async function figer() {

  await Excel.run(async function (context) {

    let copie = context.workbook.worksheets.getItem("copie");

    let panes = copie.freezePanes;

    const frozen = panes.getLocationOrNullObject();

    frozen.load("getRow");

    await context.sync();

    if (frozen.isNullObject) {

      panes.freezeRows(1);

      panes.freezeColumns(1);

    }

    else panes.unfreeze();

  });

}

Après avoir stocké la feuille concernée dans la variable copie :

let copie = context.workbook.worksheets.getItem("copie");

On récupère l’objet freezePanes qui permettra de figer des lignes et des colonnes :

let panes = copie.freezePanes;

Puis la zone figée avec la méthode getLocationOrNullObject() :

const frozen = panes.getLocationOrNullObject();

Pour que cette méthode retourne la zone figée ou un objet null, vous devez lire une propriété de l’objet frozen (getRow par exemple) :

frozen.load("getRow");

await context.sync();

Si aucune ligne n’est figée dans la feuille, frozen est un objet null. Dans ce cas, la première ligne et la première colonne sont figées à l’aide des méthodes freezeRows() et freezeColumns() :

if (frozen.isNullObject) {

  panes.freezeRows(1);

  panes.freezeColumns(1);

}

Dans le cas contraire, les lignes et colonnes figées sont libérées :

else panes.unfreeze();

Certaines feuilles comportent un très grand nombre de cellules calculées. Lorsque c’est le cas, la modification d’une cellule peut entrainer de longs calculs pour mettre à jour toute la feuille. Excel permet de désactiver le calcul automatique dans une feuille.

Basculez sur l’onglet Fichier et cliquez sur Options. La boîte de dialogue Options Excel s’affiche. Basculez sur l’onglet Formules et sélectionnez l’option Manuel, sous Mode de calcul :

L’activation ou la désactivation des calculs automatiques peut également se faire à l’aide de quelques lignes de code. Vous agirez pour cela sur la propriété enableCalculation. Lorsque cette propriété est initialisée à true, Excel met à jour automatiquement les calculs dans la feuille. Lorsqu’elle est initialisée à false, les calculs sont figés.

Voici un exemple de code :

async function desactiverCalcul() {

  await Excel.run(async function(context) {

    let copie = context.workbook.worksheets.getItem("copie");

    copie.load("enableCalculation");

    await context.sync();

    if (copie.enableCalculation)

      $("#desactivercalcul span").html('Activer les calculs');

    else

      $("#desactivercalcul span").html('Désactiver les calculs');

    copie.enableCalculation = !copie.enableCalculation;

    }

  });

}

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

let copie = context.workbook.worksheets.getItem("copie");

La propriété enableCalculation est lue et le proxy de JavaScript est synchronisé pour assurer la disponibilité de cette propriété :

copie.load("enableCalculation");

await context.sync();

Selon la valeur de cette propriété, le label du bouton est modifié :

if (copie.enableCalculation)

  $("#desactivercalcul span").html('Activer les calculs');

else

  $("#desactivercalcul span").html('Désactiver les calculs');

Puis la propriété est inversée :

copie.enableCalculation = !copie.enableCalculation;