Otimização de desempenho usando a API JavaScript do Excel

Escreva suplementos do Excel mais rápidos e escalonáveis, minimizando processos, funções de envio em lote e reduzindo o tamanho da carga. Este artigo mostra padrões, antipadrões e exemplos de código para ajudá-lo a otimizar operações comuns.

Melhorias rápidas

Aplique essas estratégias primeiro para obter o maior impacto imediato.

• Cargas e gravações em lote: chamadas de propriedade load de grupo e, em seguida, faça um único context.sync()arquivo .
• Minimizar a criação de objetos: opere em intervalos de blocos em vez de vários intervalos de célula única.
• Grave dados em matrizes e atribua uma vez a um intervalo de destino.
• Suspenda a atualização da tela ou cálculos apenas em torno de grandes alterações.
• Evite por iteração Excel.run ou context.sync() loops internos.
• Reutilize objetos de planilha, tabela e intervalo em vez de repetir os loops internos.
• Mantenha os conteúdos abaixo dos limites de tamanho por divisão ou agregação antes da atribuição.

Suspender temporariamente os processos do Excel

O Excel executa tarefas em segundo plano que reagem à entrada do usuário e às ações do suplemento. Pausar processos selecionados pode melhorar o desempenho de operações grandes.

Suspender os cálculos temporariamente

Se você precisar atualizar um intervalo grande (como atribuir valores e recalcular fórmulas dependentes) e os resultados do recálculo provisório não forem necessários, suspenda o cálculo temporariamente até o próximo context.sync().

Ver a documentação de referência objeto de aplicativo para saber mais sobre como usar a APIsuspendApiCalculationUntilNextSync()para suspender e reativar cálculos de maneira muito fácil. O código a seguir demonstra como suspender o cálculo temporariamente.

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

Somente os cálculos de fórmulas são suspensos. Todas as referências alteradas ainda são reconstruídas. Por exemplo, renomear uma planilha ainda atualiza todas as referências em fórmulas a essa planilha.

Suspender a atualização da tela

O Excel exibe as alterações conforme elas ocorrem. Para atualizações grandes e iterativas, suprima as atualizações de tela intermediárias. Application.suspendScreenUpdatingUntilNextSync() Pausa as atualizações visuais até o próximo context.sync() ou o final de Excel.run. Forneça aos usuários comentários como texto de status ou uma barra de progresso, porque a interface do usuário parece ociosa durante a suspensão.

Habilitar e desabilitar eventos

Às vezes, você pode melhorar o desempenho desabilitando eventos. Um exemplo de código mostrando como habilitar e desabilitar os eventos está no artigo trabalhar com eventos.

Importar dados em tabelas

Quando você importa grandes conjuntos de dados diretamente para uma tabela, como chamar TableRowCollection.add()repetidamente, o desempenho pode ser degradado. Em vez disso, adote a seguinte abordagem:

1. Grave toda a matriz 2D em um intervalo com range.values.
2. Crie a tabela sobre esse intervalo preenchido (worksheet.tables.add()).

Para tabelas existentes, defina valores em table.getDataBodyRange() massa. A tabela se expande automaticamente.

Aqui está um exemplo dessa abordagem:

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

    // Create the table over the range
    const 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();
});

Práticas recomendadas de limite de tamanho de conteúdo

A API JavaScript do Excel tem limitações de tamanho para chamadas de API. O Excel na Web limita as solicitações e respostas a 5 MB. A API retornará um RichAPI.Error erro se esse limite for excedido. Em todas as plataformas, um intervalo é limitado a cinco milhões de células para operações get. Grandes intervalos geralmente excedem ambos os limites.

O tamanho da carga de uma solicitação combina:

• O número de chamadas à API.
• O número de objetos, como Range objetos.
• O comprimento do valor a ser definido ou obtido.

Se você obtiver RequestPayloadSizeLimitExceeded, aplique as seguintes estratégias para reduzir o tamanho antes de dividir as operações.

Estratégia 1: Mover valores inalterados para fora dos loops

Limite os processos dentro de loops para melhorar o desempenho. No exemplo de código a seguir, context.workbook.worksheets.getActiveWorksheet() pode ser movido para fora do for loop porque não muda dentro desse loop.

// DO NOT USE THIS CODE SAMPLE. This sample shows a poor performance strategy. 
async function run() {
  await Excel.run(async (context) => {
    const 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();
  });
}

O exemplo de código a seguir mostra uma lógica semelhante, mas com uma estratégia aprimorada. O valor context.workbook.worksheets.getActiveWorksheet() é recuperado antes do loop porque ele não é alterado. Somente os valores que variam devem ser recuperados dentro do loop.

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

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

Estratégia 2: Criar menos objetos de intervalo

Crie menos objetos de intervalo para melhorar o desempenho e reduzir o tamanho da carga. Seguem-se duas abordagens.

Dividir cada matriz de intervalo em várias matrizes

Uma maneira de criar menos objetos de intervalo é dividir cada matriz de intervalo em várias matrizes e, em seguida, processar cada nova matriz com um loop e uma nova context.sync() chamada.

O exemplo de código a seguir tenta processar uma grande matriz de intervalos em um único loop e, em seguida, em uma única context.sync() chamada. O processamento de muitos valores de intervalo em uma context.sync() chamada faz com que o tamanho da solicitação de conteúdo exceda o limite de 5 MB.

// 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) => {
    const sheet = 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(); 
  });
}

A amostra de código a seguir mostra uma lógica semelhante à amostra de código anterior, mas com uma estratégia que evita exceder o limite de tamanho de solicitação de conteúdo de 5 MB. No exemplo de código a seguir, os intervalos são processados em dois loops separados e cada loop é seguido por uma context.sync() chamada.

// 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) => {
    const sheet = 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 = sheet.getRangeByIndexes(row, 1, 1, 1);
      range.values = [["1"]];
    }
    // Sync after each loop. 
    await context.sync(); 
    
    for (let row = 5001; row < 10000; row++) {
      let range = sheet.getRangeByIndexes(row, 1, 1, 1);
      range.values = [["1"]];
    }
    await context.sync(); 
  });
}

Definir valores de intervalo em uma matriz

Outra maneira de criar menos objetos de intervalo é criar uma matriz, usar um loop para definir todos os dados dessa matriz e, em seguida, passar os valores da matriz para um intervalo. Isso beneficia o desempenho e o tamanho da carga. Em vez de chamar range.values cada intervalo em um loop, range.values é chamado uma vez fora do loop.

O exemplo de código a seguir mostra como criar uma matriz, definir os valores dessa matriz em um for loop e, em seguida, passar os valores da matriz para um intervalo fora do loop.

// This code sample shows a good performance strategy.
async function run() {
  await Excel.run(async (context) => {
    const sheet = 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. 
    const range = sheet.getRange("A1:A10000");
    range.values = array;
    await context.sync();
  });
}

Próximas etapas

• Examine os limites de recursos e a otimização de desempenho para restrições no nível do host.
• Explore o trabalho com vários intervalos para criar menos objetos.
• Adicione telemetria para dados como durações de operação e contagens de linhas para orientar a otimização de desempenho adicional.

Confira também

• Principais conceitos de modelo de objeto do Excel para Suplementos do Office
• Tratamento de erros com as APIs JavaScript específicas do aplicativo
• Objeto de funções de planilha (API JavaScript para Excel)