Leistungsoptimierung mit der Excel-JavaScript-API
Es gibt mehrere Möglichkeiten, allgemeine Aufgaben mit der Excel-JavaScript-API auszuführen. Sie werden erhebliche Leistungsunterschiede zwischen verschiedenen Ansätzen feststellen. Dieser Artikel enthält Anleitungen und Codebeispiele, die Ihnen zeigen, wie Sie allgemeine Aufgaben mithilfe der Excel-JavaScript-API effizient ausführen.
Wichtig
Viele Leistungsprobleme können durch die empfohlene Verwendung von load - und sync -Aufrufen behoben werden. Informationen zum effizienten Arbeiten mit anwendungsspezifischen APIs finden Sie im Abschnitt "Leistungsverbesserungen mit den anwendungsspezifischen APIs" unter Ressourcenlimits und Leistungsoptimierung für Office-Add-Ins .
Vorübergehendes Anhalten von Excel-Prozessen
Excel verfügt über eine Reihe von Hintergrundaufgaben, die auf Eingaben sowohl von Benutzern als auch von Ihrem Add-In reagieren. Einige dieser Excel-Prozesse können so gesteuert werden, dass ein Leistungsvorteil erzielt wird. Dies ist besonders hilfreich, wenn Ihre Add-Ins mit großen Datenmengen arbeiten.
Berechnung vorübergehend unterbrechen
Wenn Sie versuchen, eine Operation mit einer gro?en Anzahl von Zellen durchzuf?hren (z.B. den Wert eines gro?en Bereichsobjekts setzen) und es Ihnen nichts ausmacht, die Berechnung in Excel vor?bergehend anzuhalten, w?hrend Ihre Operation beendet ist, empfehlen wir Ihnen, die Berechnung bis zum n?chsten Aufruf von context.sync() anzuhalten.
In der Referenzdokumentation zum Anwendungsobjekt finden Sie Informationen zur Verwendung der suspendApiCalculationUntilNextSync()-API zum Anhalten und erneuten Aktivieren von Berechnungen auf sehr praktische Weise. Der folgende Code veranschaulicht das vorübergehende Anhalten der Berechnung.
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);
});
Beachten Sie, dass nur Formelberechnungen angehalten werden. Alle geänderten Verweise werden weiterhin neu erstellt. Wenn Sie beispielsweise ein Arbeitsblatt umbenennen, werden alle Verweise in Formeln auf dieses Arbeitsblatt immer noch aktualisiert.
Anhalten der Bildschirmaktualisierung
Excel zeigt Änderungen, die das Add-In vornimmt, in etwa so an, wie sie im Code vorgenommen werden. Für große, iterative Datensätze müssen Sie diesen Fortschritt vielleicht nicht in Echtzeit auf dem Bildschirm anzeigen. Application.suspendScreenUpdatingUntilNextSync() hält visuelle Aktualisierungen für Excel an, bis das Add-In context.sync() aufruft oder bis Excel.run endet (wodurch context.sync implizit aufgerufen wird). Beachten Sie, dass in Excel bis zur nächsten Synchronisation keine Zeichen von Aktivität angezeigt werden. Das Add-In sollte Benutzern entweder Hilfestellung geben, um sie auf diese Verzögerung vorzubereiten, oder eine Statusleiste zum Anzeigen von Aktivität bereitstellen.
Hinweis
Rufen suspendScreenUpdatingUntilNextSync Sie nicht wiederholt auf (z. B. in einer Schleife). Wiederholte Aufrufe führen dazu, dass das Excel-Fenster flackert.
Aktivieren und Deaktivieren von Ereignissen
Die Leistung eines Add-Ins kann durch Deaktivieren von Ereignissen verbessert werden. Ein Codebeispiel, in dem gezeigt wird, wie Ereignisse aktiviert und deaktiviert werden, finden Sie im Artikel Arbeiten mit Ereignissen.
Daten in Tabellen importieren
Wenn Sie versuchen, eine große Datenmenge direkt in ein Table-Objekt zu importieren (z. B. mithilfe von TableRowCollection.add()), kann die Leistung langsam sein. Wenn Sie versuchen, eine neue Tabelle hinzuzufügen, sollten Sie zuerst die Daten eingeben, indem Sie festlegen range.valuesund dann aufrufen worksheet.tables.add() , um eine Tabelle über dem Bereich zu erstellen. Wenn Sie versuchen, Daten in eine vorhandene Tabelle zu schreiben, schreiben Sie die Daten über table.getDataBodyRange()in ein Bereichsobjekt, und die Tabelle wird automatisch erweitert.
Nachfolgend sehen Sie ein Beispiel dieses Ansatzes:
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();
});
Hinweis
Sie k?nnen ein Tabellenobjekt bequem in ein Bereichsobjekt konvertieren, indem Sie die Methode Table.convertToRange() verwenden.
Bewährte Methoden für die Begrenzung der Nutzlastgröße
Die Excel-JavaScript-API hat Größenbeschränkungen für API-Aufrufe. Excel im Web hat eine Nutzlastgrößenbeschränkung für Anforderungen und Antworten von 5 MB, und eine API gibt einen RichAPI.Error Fehler zurück, wenn dieser Grenzwert überschritten wird. Auf allen Plattformen ist ein Bereich für Get-Vorgänge auf fünf Millionen Zellen beschränkt. Große Bereiche überschreiten in der Regel beide Einschränkungen.
Die Nutzlastgröße einer Anforderung ist eine Kombination der folgenden drei Komponenten.
- Die Anzahl der API-Aufrufe
- Die Anzahl der Objekte, z
Range. B. Objekte - Die Länge des festzulegenden oder abzurufenden Werts
Wenn eine API den RequestPayloadSizeLimitExceeded Fehler zurückgibt, verwenden Sie die in diesem Artikel dokumentierten Bewährten Methoden, um Ihr Skript zu optimieren und den Fehler zu vermeiden.
Strategie 1: Verschieben unveränderter Werte aus Schleifen
Begrenzen Sie die Anzahl der Prozesse, die innerhalb von Schleifen auftreten, um die Leistung zu verbessern. Im folgenden Codebeispiel context.workbook.worksheets.getActiveWorksheet() kann aus der for Schleife verschoben werden, da es sich innerhalb dieser Schleife nicht ändert.
// 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();
});
}
Das folgende Codebeispiel zeigt Logik, die dem vorherigen Codebeispiel ähnelt, aber mit einer verbesserten Leistungsstrategie. Der Wert context.workbook.worksheets.getActiveWorksheet() wird vor der for Schleife abgerufen, da dieser Wert nicht bei jeder Ausführung der for Schleife abgerufen werden muss. Nur Werte, die sich im Kontext einer Schleife ändern, sollten innerhalb dieser Schleife abgerufen werden.
// 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();
});
}
Strategie 2: Erstellen weniger Bereichsobjekte
Erstellen Sie weniger Bereichsobjekte, um die Leistung zu verbessern und die Nutzlastgröße zu minimieren. Zwei Ansätze zum Erstellen von weniger Bereichsobjekten werden in den folgenden Artikelabschnitten und Codebeispielen beschrieben.
Aufteilen jedes Bereichsarrays in mehrere Arrays
Eine Möglichkeit, weniger Bereichsobjekte zu erstellen, besteht darin, jedes Bereichsarray in mehrere Arrays aufzuteilen und dann jedes neue Array mit einer Schleife und einem neuen context.sync() Aufruf zu verarbeiten.
Wichtig
Verwenden Sie diese Strategie nur, wenn Sie zuerst festgestellt haben, dass Sie die Größenbeschränkung für Nutzlastanforderungen überschreiten. Die Verwendung mehrerer Schleifen kann die Größe jeder Nutzlastanforderung verringern, um eine Überschreitung des Grenzwerts von 5 MB zu vermeiden. Die Verwendung mehrerer Schleifen und mehrerer context.sync() Aufrufe wirkt sich jedoch auch negativ auf die Leistung aus.
Im folgenden Codebeispiel wird versucht, ein großes Array von Bereichen in einer einzelnen Schleife und dann in einem einzelnen context.sync() Aufruf zu verarbeiten. Die Verarbeitung von zu vielen Bereichswerten in einem context.sync() Aufruf führt dazu, dass die Nutzlastanforderungsgröße den Grenzwert von 5 MB überschreitet.
// 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();
});
}
Das folgende Codebeispiel zeigt eine Logik, die dem vorherigen Codebeispiel ähnelt, jedoch mit einer Strategie, die eine Überschreitung des Grenzwerts für die Anforderung von 5 MB Nutzlast vermeidet. Im folgenden Codebeispiel werden die Bereiche in zwei separaten Schleifen verarbeitet, und auf jede Schleife folgt ein context.sync() Aufruf.
// 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();
});
}
Festlegen von Bereichswerten in einem Array
Eine weitere Möglichkeit, weniger Bereichsobjekte zu erstellen, besteht darin, ein Array zu erstellen, eine Schleife zum Festlegen aller Daten in diesem Array zu verwenden und dann die Arraywerte an einen Bereich zu übergeben. Dies profitiert sowohl von der Leistung als auch von der Nutzlastgröße. Anstatt für jeden Bereich in einer Schleife aufzurufen range.values , range.values wird einmal außerhalb der Schleife aufgerufen.
Im folgenden Codebeispiel wird gezeigt, wie Sie ein Array erstellen, die Werte dieses Arrays in einer for Schleife festlegen und dann die Arraywerte an einen Bereich außerhalb der Schleife übergeben.
// 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();
});
}
Siehe auch
Office Add-ins