Оптимизация производительности с использованием API JavaScript для Excel

Статья
03/10/2023

Существует несколько способов выполнения стандартных задач с помощью API JavaScript для Excel. Вы обнаружите существенные различия в производительности между разными подходами. В этой статье приведены инструкции и примеры кода, показывающие, как эффективно выполнять стандартные задачи, используя API JavaScript для Excel.

Важно!

Многие проблемы с производительностью можно устранить с помощью рекомендуемого load использования и sync вызовов. Рекомендации по эффективной работе с API-интерфейсами приложений см. в разделе "Улучшения производительности с помощью API для конкретного приложения" статьи Ограничения ресурсов и оптимизация производительности для надстроек Office .

Временная приостановка процессов Excel

В Excel есть несколько фоновых задач, которые реагируют на ввод, выполняемый как пользователями, так и надстройкой. Для повышения производительности можно управлять некоторыми из этих процессов Excel. Это особенно полезно, если ваша надстройка работает с большими наборами данных.

Временная приостановка вычисления

Если вы пытаетесь выполнить операцию с большим количеством ячеек (например, установка значения огромного объекта range) и не возражаете временно приостановить расчеты в Excel до завершения операции, рекомендуется приостановить вычисление до следующего вызова context.sync().

Дополнительные сведения об использовании API suspendApiCalculationUntilNextSync() для приостановки и повторного включения вычислений удобным способом см. в справочном документе Объект Application. В следующем коде показано, как временно приостановить вычисление.

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);
});

Обратите внимание, что приостанавливаются только вычисления формул. Все измененные ссылки по-прежнему перестраиваются. Например, переименование листа по-прежнему обновляет все ссылки в формулах на этот лист.

Приостановка обновления экрана

Excel отображает изменения, производимые вашей надстройкой, примерно по мере их выполнения в коде. Для больших циклических наборов данных может не требоваться просмотр хода выполнения на экране в режиме реального времени. Параметр Application.suspendScreenUpdatingUntilNextSync() приостанавливает визуальные обновления для Excel до вызова надстройкой метода context.sync() или завершения метода Excel.run (неявно вызывающего context.sync). Необходимо учитывать, что Excel не будет проявлять признаков работы до следующей синхронизации. Ваша надстройка должна либо предоставить пользователям инструкции, оповещающие их об этой задержке, либо отобразить строку состояния, демонстрирующую активность.

Примечание.

Не вызывайте suspendScreenUpdatingUntilNextSync повторно (например, в цикле). При повторных вызовах окно Excel будет мерцать.

Включение и отключение событий

Производительность надстройки можно повысить с помощью отключения событий. Пример кода, в котором показано, как включить и отключить события, см. в статье Работа с событиями.

Импорт данных в таблицы

При попытке импортировать огромное количество данных непосредственно в объект Table (например, с помощью TableRowCollection.add()) можно столкнуться с низкой производительностью. Если вы пытаетесь добавить новую таблицу, сначала необходимо заполнить данные, установив range.values, а затем выполнить вызов worksheet.tables.add() для создания таблицы по диапазону. Если вы пытаетесь записать данные в существующую таблицу, запишите данные в объект range с помощью table.getDataBodyRange(), и таблица расширится автоматически.

Ниже приведен пример такого способа.

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();
});

Примечание.

Можно легко преобразовать объект Table в объект Range, используя метод Table.convertToRange().

Рекомендации по ограничению размера полезных данных

Api JavaScript для Excel имеет ограничения по размеру для вызовов API. Excel в Интернете имеет ограничение на размер полезных данных для запросов и ответов в 5 МБ, и API возвращает ошибкуRichAPI.Error, если это ограничение превышено. На всех платформах диапазон ограничен пятью миллионами ячеек для операций получения. Большие диапазоны обычно превышают оба этих ограничения.

Размер полезных данных запроса представляет собой сочетание следующих трех компонентов.

Количество вызовов API
Количество объектов, таких как Range объекты
Длина устанавливаемого или получаемого значения

Если API возвращает ошибку RequestPayloadSizeLimitExceeded , используйте стратегии, описанные в этой статье, чтобы оптимизировать скрипт и избежать ошибки.

Стратегия 1. Перемещение без изменений значений из циклов

Ограничьте количество процессов, выполняемых в циклах для повышения производительности. В следующем примере context.workbook.worksheets.getActiveWorksheet() кода можно переместить из for цикла, так как он не изменяется в этом цикле.

// 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();
  });
}

В следующем примере кода показана логика, аналогичная предыдущему примеру кода, но с улучшенной стратегией производительности. Значение context.workbook.worksheets.getActiveWorksheet() извлекается перед циклом for , так как это значение не требуется извлекать при каждом for запуске цикла. В этом цикле должны извлекаться только значения, изменяющиеся в контексте цикла.

// 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();
  });
}

Стратегия 2. Создание меньшего количества объектов диапазона

Создание меньшего количества объектов диапазона для повышения производительности и минимизации размера полезных данных. Два подхода к созданию меньшего количества объектов диапазона описаны в следующих разделах статьи и примерах кода.

Разделение каждого массива диапазона на несколько массивов

Один из способов создания меньшего количества объектов диапазона — разделить каждый массив диапазона на несколько массивов, а затем обработать каждый новый массив с помощью цикла и нового context.sync() вызова.

Важно!

Используйте эту стратегию, только если вы сначала определили, что превышен предельный размер запроса полезных данных. Использование нескольких циклов может уменьшить размер каждого запроса полезных данных, чтобы избежать превышения ограничения в 5 МБ, но использование нескольких циклов и нескольких context.sync() вызовов также негативно влияет на производительность.

Следующий пример кода пытается обработать большой массив диапазонов в одном цикле, а затем в одном context.sync() вызове. Обработка слишком большого количества значений диапазона в одном context.sync() вызове приводит к тому, что размер запроса полезных данных превысит ограничение в 5 МБ.

// 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(); 
  });
}

В следующем примере кода показана логика, аналогичная предыдущему примеру кода, но со стратегией, которая позволяет избежать превышения предельного размера запроса полезных данных в 5 МБ. В следующем примере кода диапазоны обрабатываются в два отдельных цикла, за каждым из которых следует context.sync() вызов.

// 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(); 
  });
}

Установка значений диапазона в массиве

Другим способом создания меньшего количества объектов диапазона является создание массива, использование цикла для задания всех данных в этом массиве, а затем передача значений массива в диапазон. Это дает преимущества как производительности, так и размера полезных данных. Вместо вызова range.values для каждого диапазона в цикле range.values вызывается один раз за пределами цикла.

В следующем примере кода показано, как создать массив, задать значения этого массива в for цикле, а затем передать значения массива в диапазон за пределами цикла.

// 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();
  });
}