Optimisation des performances à l’aide de l’API JavaScript d’Excel

Écrivez des compléments Excel plus rapides et plus évolutifs en réduisant les processus, les fonctions de traitement par lot et la taille de la charge utile. Cet article présente des modèles, des anti-modèles et des exemples de code pour vous aider à optimiser les opérations courantes.

Améliorations rapides

Appliquez d’abord ces stratégies pour obtenir l’impact immédiat le plus important.

• Chargements et écritures par lots : appel de propriétés load de groupe, puis créez un seul context.sync().
• Réduire la création d’objets : utiliser des plages de blocs au lieu de nombreuses plages à cellule unique.
• Écrivez des données dans des tableaux, puis affectez une seule fois à une plage cible.
• Suspendre la mise à jour ou le calcul de l’écran uniquement en cas de modifications importantes.
• Évitez les boucles par itération Excel.run ou context.sync() internes.
• Réutilisez les objets de feuille de calcul, de table et de plage au lieu d’interroger à nouveau les boucles.
• Conservez les charges utiles en dessous des limites de taille en les segmentant ou en agrégeant avant l’affectation.

Suspendre temporairement les processus Excel

Excel effectue des tâches en arrière-plan qui réagissent aux entrées utilisateur et aux actions de complément. La suspension des processus sélectionnés peut améliorer les performances pour les opérations volumineuses.

Suspendre temporairement les calculs

Si vous devez mettre à jour une grande plage (par exemple, pour affecter des valeurs, puis recalculer des formules dépendantes) et que les résultats de recalcul intermédiaire ne sont pas nécessaires, suspendez temporairement le calcul jusqu’au prochain context.sync().

Reportez-vous à la documentation de référence Objet Application pour plus d’informations sur l’utilisation de l’API suspendApiCalculationUntilNextSync() pour suspendre et réactiver les calculs de manière très pratique. Le code suivant montre comment suspendre temporairement le calcul.

await Excel.run(async (context) => {
    let app = context.workbook.application;
    let sheet = context.workbook.worksheets.getItem("sheet1");
    let rangeToSet: Excel.Range;
    let rangeToGet: Excel.Range;
    app.load("calculationMode");
    await context.sync();
    // Calculation mode should be "Automatic" by default
    console.log(app.calculationMode);

    rangeToSet = sheet.getRange("A1:C1");
    rangeToSet.values = [[1, 2, "=SUM(A1:B1)"]];
    rangeToGet = sheet.getRange("A1:C1");
    rangeToGet.load("values");
    await context.sync();
    // Range value should be [1, 2, 3] now
    console.log(rangeToGet.values);

    // Suspending recalculation
    app.suspendApiCalculationUntilNextSync();
    rangeToSet = sheet.getRange("A1:B1");
    rangeToSet.values = [[10, 20]];
    rangeToGet = sheet.getRange("A1:C1");
    rangeToGet.load("values");
    app.load("calculationMode");
    await context.sync();
    // Range value should be [10, 20, 3] when we load the property, because calculation is suspended at that point
    console.log(rangeToGet.values);
    // Calculation mode should still be "Automatic" even with suspend recalculation
    console.log(app.calculationMode);

    rangeToGet.load("values");
    await context.sync();
    // Range value should be [10, 20, 30] when we load the property, because calculation is resumed after last sync
    console.log(rangeToGet.values);
});

Seuls les calculs de formule sont suspendus. Toutes les références modifiées sont toujours reconstruites. Par exemple, le changement de nom d’une feuille de calcul met toujours à jour toutes les références dans les formules à cette feuille de calcul.

Suspendre la mise à jour de l’écran

Excel affiche les modifications à mesure qu’elles se produisent. Pour les mises à jour itératives volumineuses, supprimez les mises à jour d’écran intermédiaires. Application.suspendScreenUpdatingUntilNextSync() suspend les mises à jour visuelles jusqu’à la prochaine context.sync() ou la fin de Excel.run. Fournissez à vos utilisateurs des commentaires tels que status texte ou une barre de progression, car l’interface utilisateur semble inactive pendant la suspension.

Activation et désactivation d’événements

Vous pouvez parfois améliorer les performances en désactivant les événements. Un exemple de code montrant comment activer et désactiver les événements dans l’articlemanipuler les événements.

Importation de données dans des tableaux

Lorsque vous importez des jeux de données volumineux directement dans une table, par exemple en appelant TableRowCollection.add()à plusieurs reprises , les performances peuvent se dégrader. Au lieu de cela, utilisez l’approche suivante :

1. Écrivez l’intégralité du tableau 2D dans une plage avec range.values.
2. Créez la table sur cette plage remplie (worksheet.tables.add()).

Pour les tables existantes, définissez des valeurs sur table.getDataBodyRange() en bloc. La table se développe automatiquement.

Voici un exemple de cette approche :

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sheet1");
    // Write the data into the range first.
    let range = sheet.getRange("A1:B3");
    range.values = [["Key", "Value"], ["A", 1], ["B", 2]];

    // Create the table over the range
    let table = sheet.tables.add('A1:B3', true);
    table.name = "Example";
    await context.sync();


    // Insert a new row to the table
    table.getDataBodyRange().getRowsBelow(1).values = [["C", 3]];
    // Change a existing row value
    table.getDataBodyRange().getRow(1).values = [["D", 4]];
    await context.sync();
});

Meilleures pratiques en matière de limite de taille de charge utile

L’API JavaScript Excel présente des limitations de taille pour les appels d’API. Excel sur le Web limite les demandes et les réponses à 5 Mo. L’API retourne une RichAPI.Error erreur si cette limite est dépassée. Sur toutes les plateformes, une plage est limitée à cinq millions de cellules pour les opérations d’obtention. Les grandes plages dépassent souvent les deux limites.

La taille de charge utile d’une requête combine :

• Nombre d’appels d’API.
• Nombre d’objets, tels que Range des objets.
• Longueur de la valeur à définir ou à obtenir.

Si vous obtenez RequestPayloadSizeLimitExceeded, appliquez les stratégies suivantes pour réduire la taille avant de fractionner les opérations.

Stratégie 1 : Déplacer des valeurs inchangées hors des boucles

Limitez les processus à l’intérieur des boucles pour améliorer les performances. Dans l’exemple de code suivant, context.workbook.worksheets.getActiveWorksheet() peut être déplacé hors de la for boucle, car il ne change pas au sein de cette boucle.

// DO NOT USE THIS CODE SAMPLE. This sample shows a poor performance strategy. 
async function run() {
  await Excel.run(async (context) => {
    let ranges = [];
    
    // This sample retrieves the worksheet every time the loop runs, which is bad for performance.
    for (let i = 0; i < 7500; i++) {
      let rangeByIndex = context.workbook.worksheets.getActiveWorksheet().getRangeByIndexes(i, 1, 1, 1);
    }    
    await context.sync();
  });
}

L’exemple de code suivant montre une logique similaire, mais avec une stratégie améliorée. La valeur context.workbook.worksheets.getActiveWorksheet() est récupérée avant la boucle, car elle ne change pas. Seules les valeurs qui varient doivent être récupérées à l’intérieur de la boucle.

// This code sample shows a good performance strategy.
async function run() {
  await Excel.run(async (context) => {
    let ranges = [];
    // Retrieve the worksheet outside the loop.
    let worksheet = context.workbook.worksheets.getActiveWorksheet(); 

    // Only process the necessary values inside the loop.
    for (let i = 0; i < 7500; i++) {
      let rangeByIndex = worksheet.getRangeByIndexes(i, 1, 1, 1);
    }    
    await context.sync();
  });
}

Stratégie 2 : Créer moins d’objets de plage

Créez moins d’objets de plage pour améliorer les performances et réduire la taille de la charge utile. Deux approches s’en suivent.

Fractionner chaque tableau de plages en plusieurs tableaux

Une façon de créer moins d’objets de plage consiste à fractionner chaque tableau de plages en plusieurs tableaux, puis à traiter chaque nouveau tableau avec une boucle et un nouvel context.sync() appel.

L’exemple de code suivant tente de traiter un grand tableau de plages dans une seule boucle, puis un seul context.sync() appel. En traitant un trop grand nombre de valeurs de plage dans un context.sync() appel, la taille de la demande de charge utile dépasse la limite de 5 Mo.

// This code sample does not show a recommended strategy.
// Calling 10,000 rows would likely exceed the 5MB payload size limit in a real-world situation.
async function run() {
  await Excel.run(async (context) => {
    let worksheet = context.workbook.worksheets.getActiveWorksheet();
    
    // This sample attempts to process too many ranges at once. 
    for (let row = 1; row < 10000; row++) {
      let range = sheet.getRangeByIndexes(row, 1, 1, 1);
      range.values = [["1"]];
    }
    await context.sync(); 
  });
}

L’exemple de code suivant montre une logique similaire à l’exemple de code précédent, mais avec une stratégie qui évite de dépasser la limite de taille de demande de charge utile de 5 Mo. Dans l’exemple de code suivant, les plages sont traitées dans deux boucles distinctes, et chaque boucle est suivie d’un context.sync() appel.

// This code sample shows a strategy for reducing payload request size.
// However, using multiple loops and `context.sync()` calls negatively impacts performance.
// Only use this strategy if you've determined that you're exceeding the payload request limit.
async function run() {
  await Excel.run(async (context) => {
    let worksheet = context.workbook.worksheets.getActiveWorksheet();

    // Split the ranges into two loops, rows 1-5000 and then 5001-10000.
    for (let row = 1; row < 5000; row++) {
      let range = worksheet.getRangeByIndexes(row, 1, 1, 1);
      range.values = [["1"]];
    }
    // Sync after each loop. 
    await context.sync(); 
    
    for (let row = 5001; row < 10000; row++) {
      let range = worksheet.getRangeByIndexes(row, 1, 1, 1);
      range.values = [["1"]];
    }
    await context.sync(); 
  });
}

Définir des valeurs de plage dans un tableau

Une autre façon de créer moins d’objets de plage consiste à créer un tableau, à utiliser une boucle pour définir toutes les données de ce tableau, puis à transmettre les valeurs du tableau à une plage. Cela permet d’améliorer les performances et la taille de la charge utile. Au lieu d’appeler range.values pour chaque plage d’une boucle, range.values est appelé une fois en dehors de la boucle.

L’exemple de code suivant montre comment créer un tableau, définir les valeurs de ce tableau dans une for boucle, puis transmettre les valeurs du tableau à une plage en dehors de la boucle.

// This code sample shows a good performance strategy.
async function run() {
  await Excel.run(async (context) => {
    const worksheet = context.workbook.worksheets.getActiveWorksheet();    
    // Create an array.
    const array = new Array(10000);

    // Set the values of the array inside the loop.
    for (let i = 0; i < 10000; i++) {
      array[i] = [1];
    }

    // Pass the array values to a range outside the loop. 
    let range = worksheet.getRange("A1:A10000");
    range.values = array;
    await context.sync();
  });
}

Étapes suivantes

• Passez en revue les limites de ressources et l’optimisation des performances pour connaître les contraintes au niveau de l’hôte.
• Explorez l’utilisation de plusieurs plages pour créer moins d’objets.
• Ajoutez des données de télémétrie telles que les durées d’opération et le nombre de lignes pour guider l’optimisation des performances.

Voir aussi

• Modèle d’objet JavaScript Excel dans les compléments Office
• Gestion des erreurs avec les API JavaScript spécifiques à l’application
• Objet de fonctions de feuille de calcul (API JavaScript pour Excel)