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 significativas de rendimiento entre varios enfoques. En este artículo se proporcionan instrucciones y ejemplos de código para mostrar cómo realizar tareas comunes de forma eficaz mediante la API de JavaScript de Excel.
Importante
Muchos problemas de rendimiento se pueden solucionar mediante el uso recomendado de load llamadas y sync . Consulte la sección "Mejoras de rendimiento con las API específicas de la aplicación" de Límites de recursos y optimización del 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. En el código siguiente se muestra cómo suspender el cálculo temporalmente.
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);
});
Tenga en cuenta que solo se suspenden los cálculos de fórmulas. Las referencias modificadas se siguen reconstruyendo. Por ejemplo, al cambiar el nombre de una hoja de cálculo, se siguen 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 llame a suspendScreenUpdatingUntilNextSync repetidamente (por ejemplo, en un bucle). Las llamadas repetidas harán que la ventana de Excel parpade.
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, mediante TableRowCollection.add()), es posible que experimente un rendimiento lento. Si intenta agregar una nueva tabla, primero debe rellenar los datos estableciendo range.valuesy, a continuación, llame worksheet.tables.add() a para crear una tabla en el intervalo. Si intenta escribir datos en una tabla existente, escriba los datos en un objeto de rango a través table.getDataBodyRange()de y la tabla se expandirá automáticamente.
Aquí tiene un ejemplo de este enfoque:
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();
});
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 de JavaScript de Excel 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 RichAPI.Error error si se supera 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.
- Número de llamadas API
- Número de objetos, como
Rangeobjetos - Longitud del valor que se va a establecer o obtener
Si una API devuelve el RequestPayloadSizeLimitExceeded error, use las estrategias de procedimientos recomendados documentadas en este artículo para optimizar el script y evitar el error.
Estrategia 1: Mover los valores sin cambios fuera de bucles
Limite el número de procesos que se producen dentro de bucles para mejorar el rendimiento. En el ejemplo de código siguiente, context.workbook.worksheets.getActiveWorksheet() se puede mover fuera del for bucle, porque no cambia dentro de ese bucle.
// 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();
});
}
En el ejemplo de código siguiente se muestra una lógica similar al ejemplo de código anterior, pero con una estrategia de rendimiento mejorada. El valor context.workbook.worksheets.getActiveWorksheet() se recupera antes del for bucle, ya que no es necesario recuperar este valor cada vez que se ejecuta el 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) => {
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();
});
}
Estrategia 2: Crear menos objetos de intervalo
Cree menos objetos de intervalo para mejorar el rendimiento y minimizar el tamaño de carga. En las secciones de artículos siguientes y ejemplos de código se describen dos enfoques para crear menos objetos de intervalo.
Dividir cada matriz de rango en varias matrices
Una manera 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
Use esta estrategia solo si ha determinado por primera vez que está superando el límite de tamaño de solicitud de carga útil. El uso de varios bucles puede reducir el tamaño de cada solicitud de carga útil para evitar superar el límite de 5 MB, pero el uso de varios bucles y varias context.sync() llamadas también afecta negativamente al rendimiento.
En el ejemplo de código siguiente se intenta procesar una gran matriz de intervalos en un único bucle y, a continuación, una sola context.sync() llamada. El procesamiento de demasiados valores de intervalo en una context.sync() llamada hace que el tamaño de la solicitud 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) => {
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();
});
}
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 ejemplo de código siguiente, 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) => {
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();
});
}
Establecimiento de valores de rango en una matriz
Otra manera 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 matriz a un rango. Esto beneficia tanto el rendimiento como el tamaño de carga. En lugar de llamar a range.values para cada intervalo de un bucle, range.values se llama una vez fuera del bucle.
En el ejemplo de código siguiente se muestra cómo crear una matriz, establecer los valores de esa matriz en un for bucle y, a continuación, pasar los valores de matriz a un intervalo 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 (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();
});
}