Optimización del rendimiento con la API de JavaScript de Excel

Hay varias maneras de realizar tareas comunes con la API de JavaScript de Excel. Encontrará diferencias importantes en el rendimiento entre varios métodos. En este artículo encontrará instrucciones y ejemplos de código que le muestran cómo realizar tareas comunes de forma eficaz con la API de JavaScript de Excel.

Importante

Muchos problemas de rendimiento se pueden solucionar a través del uso recomendado de load y sync las llamadas. Consulte la sección "Mejoras de rendimiento con las API específicas de la aplicación" de Límites de recursos y optimización de rendimiento para complementos de Office para obtener consejos sobre cómo trabajar con las API específicas de la aplicación de forma eficaz.

Suspender temporalmente los procesos de Excel

Excel tiene una serie de tareas en segundo plano que reaccionan a las entradas de los usuarios y del complemento. Algunos de estos procesos de Excel pueden controlarse para conducir a una mejora del rendimiento. Esto es especialmente útil cuando el complemento trabaja con grandes conjuntos de datos.

Suspender el cálculo temporalmente

Si intenta realizar una operación en un gran número de celdas (por ejemplo, establecer el valor de un objeto de intervalo enorme) y no le importa suspender el cálculo en Excel temporalmente mientras su operación se finaliza, le recomendamos que suspenda el cálculo hasta que se llame al siguiente context.sync().

Vea la documentación de referencia de Objeto Application para obtener información sobre cómo usar la API de suspendApiCalculationUntilNextSync() para suspender y reactivar cálculos de forma práctica. El siguiente código muestra cómo suspender el cálculo temporalmente.

Excel.run(async function(ctx) {
    var app = ctx.workbook.application;
    var sheet = ctx.workbook.worksheets.getItem("sheet1");
    var rangeToSet: Excel.Range;
    var rangeToGet: Excel.Range;
    app.load("calculationMode");
    await ctx.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 ctx.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 ctx.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 ctx.sync();
    // Range value should be [10, 20, 30] when we load the property, because calculation is resumed after last sync
    console.log(rangeToGet.values);
})

Tenga en cuenta que solo se suspenden los cálculos de fórmula. Las referencias modificadas aún se reconstruyen. Por ejemplo, cambiar el nombre de una hoja de cálculo sigue actualizando las referencias de las fórmulas a esa hoja de cálculo.

Suspender la actualización de la pantalla

Excel muestra los cambios realizados por el complemento casi al mismo tiempo que estos ocurren en el código. Para los conjuntos de datos iterativos de gran tamaño puede que no sea necesario ver el progreso de la pantalla en tiempo real. Application.suspendScreenUpdatingUntilNextSync() pausa las actualizaciones visuales de Excel hasta que el complemento llama a context.sync() o hasta que Excel.run termina (llamando a context.sync de manera implícita). Tenga en cuenta que Excel no mostrará signos de actividad hasta la siguiente sincronización. El complemento debe dar instrucciones a los usuarios para prepararlos para este retraso o proporcionar una barra de estado para mostrar la actividad.

Nota

No llames suspendScreenUpdatingUntilNextSync repetidamente (por ejemplo, en un bucle). Las llamadas repetidas harán que Excel ventana parpadee.

Habilitar y deshabilitar eventos

El rendimiento de un complemento puede mejorar mediante la deshabilitación de eventos. Para ver un código de ejemplo que muestra cómo habilitar y deshabilitar los eventos, vaya al artículo Trabajar con eventos.

Importar datos en tablas

Al intentar importar una gran cantidad de datos directamente en un objeto Table directamente (por ejemplo, con TableRowCollection.add()), es posible que note un descenso del rendimiento. Si está intentando agregar una nueva tabla, debe rellenar los datos primero estableciendo range.values y, a continuación, llamar a worksheet.tables.add() para crear una tabla en el rango. Si prefiere escribir los datos en una tabla existente, escriba los datos en un objeto de intervalo mediante table.getDataBodyRange() y la tabla se expandirá automáticamente.

Aquí tiene un ejemplo de este enfoque:

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

    // Create the table over the range
    var table = sheet.tables.add('A1:B3', true);
    table.name = "Example";
    await ctx.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 ctx.sync();
})

Nota

Puede convertir fácilmente un objeto Table en un objeto de intervalo utilizando el método Table.convertToRange().

Procedimientos recomendados de límite de tamaño de carga

La API Excel JavaScript tiene limitaciones de tamaño para las llamadas API. Excel en la Web tiene un límite de tamaño de carga para solicitudes y respuestas de 5 MB y una API devuelve un error si se supera RichAPI.Error este límite. En todas las plataformas, un rango está limitado a cinco millones de celdas para obtener operaciones. Los intervalos grandes suelen superar ambas limitaciones.

El tamaño de carga de una solicitud es una combinación de los tres componentes siguientes.

  • El número de llamadas API
  • Número de objetos, como Range objetos
  • Longitud del valor que se debe establecer u obtener

Si una API devuelve el error, use las estrategias de procedimiento recomendado documentadas en este artículo para optimizar el RequestPayloadSizeLimitExceeded script y evitar el error.

Estrategia 1: Mover valores sin cambios fuera de bucles

Limitar el número de procesos que se producen en bucles para mejorar el rendimiento. En el siguiente ejemplo de código, se puede mover fuera del bucle, porque context.workbook.worksheets.getActiveWorksheet() no cambia dentro de ese for bucle.

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

El ejemplo de código siguiente muestra una lógica similar al ejemplo de código anterior, pero con una estrategia de rendimiento mejorada. El valor se recupera antes del bucle, ya que este valor no necesita recuperarse context.workbook.worksheets.getActiveWorksheet() cada vez que se ejecuta el for for bucle. Solo los valores que cambian en el contexto de un bucle deben recuperarse dentro de ese bucle.

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

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

Estrategia 2: Crear menos objetos de intervalo

Cree menos objetos de rango para mejorar el rendimiento y minimizar el tamaño de la carga. En las siguientes secciones de artículo y ejemplos de código se describen dos métodos para crear menos objetos de intervalo.

Dividir cada matriz de rango en varias matrices

Una forma de crear menos objetos de rango es dividir cada matriz de rango en varias matrices y, a continuación, procesar cada nueva matriz con un bucle y una nueva context.sync() llamada.

Importante

Solo use esta estrategia si ha determinado por primera vez que está superando el límite de tamaño de la solicitud de carga. El uso de varios bucles puede reducir el tamaño de cada solicitud de carga para evitar superar el límite de 5 MB, pero el uso de varios bucles y varias llamadas también afecta negativamente context.sync() al rendimiento.

El ejemplo de código siguiente intenta procesar una gran matriz de intervalos en un solo bucle y, a continuación, una sola context.sync() llamada. El procesamiento de demasiados valores de intervalo en una llamada hace que el tamaño de la solicitud context.sync() de carga supere el límite 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) => {
    var worksheet = context.workbook.worksheets.getActiveWorksheet();
    
    // This sample attempts to process too many ranges at once. 
    for (let row = 1; row < 10000; row++) {
      var range = sheet.getRangeByIndexes(row, 1, 1, 1);
      range.values = [["1"]];
    }
    await context.sync(); 
  });
}

En el ejemplo de código siguiente se muestra una lógica similar al ejemplo de código anterior, pero con una estrategia que evita superar el límite de tamaño de solicitud de carga de 5 MB. En el siguiente ejemplo de código, los intervalos se procesan en dos bucles independientes y cada bucle va seguido de una context.sync() llamada.

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

Establecer valores de intervalo en una matriz

Otra forma de crear menos objetos de rango es crear una matriz, usar un bucle para establecer todos los datos de esa matriz y, a continuación, pasar los valores de la matriz a un rango. Esto beneficia tanto el rendimiento como el tamaño de la carga. En lugar de range.values llamar a cada intervalo de un bucle, se llama una vez fuera del range.values bucle.

En el ejemplo de código siguiente se muestra cómo crear una matriz, establecer los valores de esa matriz en un bucle y, a continuación, pasar los valores de matriz a un rango for fuera del bucle.

// 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 (var i = 0; i < 10000; i++) {
      array[i] = [1];
    }

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

Consulte también