Leistungsoptimierung mit der Excel-JavaScript-API

Schreiben Sie schnellere, besser skalierbare Excel-Add-Ins durch Minimieren von Prozessen, Batchverarbeitungsfunktionen und Reduzieren der Nutzlastgröße. In diesem Artikel werden Muster, Antimuster und Codebeispiele erläutert, mit denen Sie allgemeine Vorgänge optimieren können.

Schnelle Verbesserungen

Wenden Sie diese Strategien zuerst für die größten unmittelbaren Auswirkungen an.

• Batchladevorgänge und -schreibvorgänge: Gruppeneigenschaftsaufrufe load und anschließendes Erstellen eines einzelnen context.sync().
• Minimieren sie die Objekterstellung: Arbeiten Sie mit Blockbereichen statt mit vielen Einzelzellenbereichen.
• Schreiben Sie Daten in Arrays, und weisen Sie dann einmal einem Zielbereich zu.
• Bildschirmaktualisierung oder Berechnung nur bei großen Änderungen anhalten.
• Vermeiden Sie iterations- Excel.run oder context.sync() innerhalb von Schleifen.
• Verwenden Sie Arbeitsblatt-, Tabellen- und Bereichsobjekte wieder, anstatt innerhalb von Schleifen erneut abfragen zu müssen.
• Halten Sie Nutzlasten unter den Größenbeschränkungen, indem Sie vor der Zuweisung segmentieren oder aggregieren.

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 führt Hintergrundaufgaben aus, die auf Benutzereingaben und Add-In-Aktionen reagieren. Das Anhalten ausgewählter Prozesse kann die Leistung bei großen Vorgängen verbessern.

Berechnung vorübergehend unterbrechen

Wenn Sie einen großen Bereich aktualisieren müssen (z. B. um Werte zuzuweisen und dann abhängige Formeln neu zu berechnen) und zwischengespeicherte Neuberechnungsergebnisse nicht erforderlich sind, setzen Sie die Berechnung vorübergehend bis zum nächsten context.sync()aus.

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

Nur Formelberechnungen werden angehalten. 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 an, sobald sie auftreten. Bei großen, iterativen Updates unterdrücken Sie Zwischenbildschirmupdates. Application.suspendScreenUpdatingUntilNextSync() hält visuelle Updates bis zum nächsten context.sync() oder ende von Excel.runan. Geben Sie Ihren Benutzern Feedback, z. B. status Text oder eine Statusanzeige, da die Benutzeroberfläche während des Anhaltens im Leerlauf angezeigt wird.

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

Manchmal können Sie die Leistung verbessern, indem Sie Ereignisse deaktivieren. 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 große Datasets direkt in eine Tabelle importieren, z. B. wiederholt aufrufen TableRowCollection.add(), kann die Leistung beeinträchtigt werden. Verwenden Sie stattdessen den folgenden Ansatz:

1. Schreiben Sie das gesamte 2D-Array mit range.valuesin einen Bereich.
2. Erstellen Sie die Tabelle über dem aufgefüllten Bereich (worksheet.tables.add()).

Legen Sie werte für vorhandene Tabellen massenweise fest table.getDataBodyRange() . 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 beschränkt Anforderungen und Antworten auf 5 MB. Die 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 häufig beide Grenzwerte.

Die Nutzlastgröße einer Anforderung umfasst Folgendes:

• Die Anzahl der API-Aufrufe.
• Die Anzahl der Objekte, z Range . B. -Objekte.
• Die Länge des festzulegenden oder abzurufenden Werts.

Wenn Sie erhalten RequestPayloadSizeLimitExceeded, wenden Sie die folgenden Strategien an, um die Größe zu reduzieren, bevor Sie Vorgänge aufteilen.

Strategie 1: Verschieben unveränderter Werte aus Schleifen

Beschränken Sie die Prozesse innerhalb von Schleifen, um die Leistung zu verbessern. Im folgenden Codebeispiel kann aus der for Schleife verschoben werden, context.workbook.worksheets.getActiveWorksheet() 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 eine ähnliche Logik, aber mit einer verbesserten Strategie. Der Wert context.workbook.worksheets.getActiveWorksheet() wird vor der Schleife abgerufen, da er sich nicht ändert. Nur Werte, die variieren, sollten innerhalb der 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 reduzieren. Es folgen zwei Ansätze.

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, nachdem Sie bestätigt haben, dass Sie das Limit für die Nutzlastgröße überschritten haben. Mehrere Schleifen reduzieren die Größe jeder Nutzlastanforderung, fügen aber auch zusätzliche context.sync() Aufrufe hinzu und können die Leistung beeinträchtigen.

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

Nächste Schritte

• Überprüfen Sie Ressourcenlimits und Leistungsoptimierung auf Einschränkungen auf Hostebene.
• Erkunden Sie das Arbeiten mit mehreren Bereichen , um weniger Objekte zu erstellen.
• Fügen Sie Telemetriedaten für Daten wie Vorgangsdauer und Zeilenanzahl hinzu, um eine weitere Leistungsoptimierung zu unterstützen.

Siehe auch

• JavaScript-Objektmodell für Excel in Office-Add-Ins
• Fehlerbehandlung mit den anwendungsspezifischen JavaScript-APIs
• Worksheet-Funktionsobjekt (JavaScript-API für Excel)