Arbeiten mit Tabellen mithilfe der Excel-JavaScript-API

In diesem Artikel wird anhand einiger Codebeispiele erklärt, wie Sie allgemeine Aufgaben mit Tabellen mithilfe der Excel-JavaScript-API ausführen. Eine vollständige Liste der Eigenschaften und Methoden, die von den Objekten unterstützt werden, finden Sie unter Table-Objekt (JavaScript-API für Excel) und TableCollection-Objekt (JavaScript-API für Excel).For the complete list of properties and methods that the Table and TableCollection objects support, see Table Object (JavaScript API for Excel) and TableCollection Object (JavaScript API for Excel).

Erstellen einer Tabelle

Im folgenden Codebeispiel wird eine Tabelle im Arbeitsblatt mit der Bezeichnung Beispiel erstellt. Die Tabelle enthält Überschriften, vier Spalten und sieben Zeilen mit Daten. Wenn die Excel Anwendung, in der der Code ausgeführt wird, den Anforderungssatz ExcelApi 1.2 unterstützt, werden die Breite der Spalten und die Höhe der Zeilen so festgelegt, dass sie den aktuellen Daten in der Tabelle am besten entspricht.

Hinweis

Um einen Namen für eine Tabelle anzugeben, müssen Sie zuerst die Tabelle erstellen und dann ihre name Eigenschaft festlegen, wie im folgenden Beispiel gezeigt.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";

    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    expensesTable.rows.add(null /*add rows to the end of the table*/, [
        ["1/1/2017", "The Phone Company", "Communications", "$120"],
        ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
        ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
        ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
        ["1/11/2017", "Bellows College", "Education", "$350"],
        ["1/15/2017", "Trey Research", "Other", "$135"],
        ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
    ]);

    if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
        sheet.getUsedRange().format.autofitColumns();
        sheet.getUsedRange().format.autofitRows();
    }

    sheet.activate();

    await context.sync();
});

Neue Tabelle

Neue Tabelle in Excel.

Hinzufügen von Zeilen zu einer Tabelle

Im folgenden Codebeispiel werden sieben neue Zeilen zu der Tabelle mit der Bezeichnung ExpensesTable im Arbeitsblatt mit der Bezeichnung Beispiel hinzugefügt. Der index Parameter der add Methode wird auf nullfestgelegt, der angibt, dass die Zeilen nach den vorhandenen Zeilen in der Tabelle hinzugefügt werden. Der alwaysInsert Parameter wird auf truefestgelegt, was angibt, dass die neuen Zeilen in die Tabelle eingefügt werden, nicht unterhalb der Tabelle. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie am besten an die aktuellen Daten in der Tabelle angepasst werden.

Hinweis

Die index Eigenschaft eines TableRow-Objekts gibt die Indexnummer der Zeile in der Zeilenauflistung der Tabelle an. Ein TableRow Objekt enthält id keine Eigenschaft, die als eindeutiger Schlüssel zum Identifizieren der Zeile verwendet werden kann.

// This code sample shows how to add rows to a table that already exists 
// on a worksheet named Sample.
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.rows.add(
        null, // index, Adds rows to the end of the table.
        [
            ["1/16/2017", "THE PHONE COMPANY", "Communications", "$120"],
            ["1/20/2017", "NORTHWIND ELECTRIC CARS", "Transportation", "$142"],
            ["1/20/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$27"],
            ["1/21/2017", "COHO VINEYARD", "Restaurant", "$33"],
            ["1/25/2017", "BELLOWS COLLEGE", "Education", "$350"],
            ["1/28/2017", "TREY RESEARCH", "Other", "$135"],
            ["1/31/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$97"]
        ], 
        true, // alwaysInsert, Specifies that the new rows be inserted into the table.
    );

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    await context.sync();
});

Tabelle mit neuen Zeilen

Tabelle mit neuen Zeilen in Excel.

Hinzufügen einer Spalte zu einer Tabelle

Dieses Beispiel zeigt, wie eine Spalte zu einer Tabelle hinzufügen. Im ersten Beispiel wird die neue Spalte mit statischen Werten gefüllt; im zweiten Beispiel wird die neue Spalte mit Formeln gefüllt.

Hinweis

Die index-Eigenschaft eines TableColumn-Objekts gibt die Indexnummer der Spalte in der Spaltenauflistung der Tabelle an. Die ID-Eigenschaft eines TableColumn-Objekts enthält einen eindeutigen Schlüssel, der die Spalte angibt.

Hinzufügen einer Spalte mit statischen Werten

Im folgenden Codebeispiel wird eine neue Spalte zu der Tabelle mit der Bezeichnung ExpensesTable im Arbeitsblatt mit der Bezeichnung Beispiel hinzugefügt. Die neue Spalte wird nach allen vorhandenen Spalten in die Tabelle eingefügt und verfügt über eine Kopfzeile ("Wochentag") sowie Daten, mit denen die Zellen in der Spalte gefüllt werden. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie am besten an die aktuellen Daten in der Tabelle angepasst werden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.columns.add(null /*add columns to the end of the table*/, [
        ["Day of the Week"],
        ["Saturday"],
        ["Friday"],
        ["Monday"],
        ["Thursday"],
        ["Sunday"],
        ["Saturday"],
        ["Monday"]
    ]);

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    await context.sync();
});

Tabelle mit neuer Spalte

Tabelle mit neuer Spalte in Excel.

Hinzufügen einer Spalte mit Formeln

Im folgenden Codebeispiel wird eine neue Spalte zu der Tabelle mit der Bezeichnung ExpensesTable im Arbeitsblatt mit der Bezeichnung Beispiel hinzugefügt. Die neue Spalte wird am Ende der Tabelle eingefügt. Sie verfügt über die Kopfzeile ("Typ des Tages") und verwendet eine Formel, um alle Datenzelle der Spalte zu füllen. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie am besten an die aktuellen Daten in der Tabelle angepasst werden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.columns.add(null /*add columns to the end of the table*/, [
        ["Type of the Day"],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")'],
        ['=IF(OR((TEXT([DATE], "dddd") = "Saturday"), (TEXT([DATE], "dddd") = "Sunday")), "Weekend", "Weekday")']
    ]);

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    await context.sync();
});

Tabelle mit neu berechneter Spalte

Tabelle mit neuer berechneter Spalte in Excel.

Ändern der Größe einer Tabelle

Das Add-In kann die Größe einer Tabelle ändern, ohne der Tabelle Daten hinzuzufügen oder Zellwerte zu ändern. Verwenden Sie die Table.resize-Methode , um die Größe einer Tabelle zu ändern. Das folgende Codebeispiel zeigt, wie Sie die Größe einer Tabelle ändern. In diesem Codebeispiel wird die ExpensesTable aus dem Abschnitt "Erstellen einer Tabelle " weiter oben in diesem Artikel verwendet und der neue Bereich der Tabelle auf A1:D20 festgelegt.

await Excel.run(async (context) => {
    // Retrieve the worksheet and a table on that worksheet.
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    // Resize the table.
    expensesTable.resize("A1:D20");

    await context.sync();
});

Wichtig

Der neue Bereich der Tabelle muss sich mit dem ursprünglichen Bereich überlappen, und die Kopfzeilen (oder der obere Rand der Tabelle) müssen sich in derselben Zeile befinden.

Tabelle nach Größenanpassung

Tabelle mit mehreren leeren Zeilen in Excel.

Aktualisieren des Spaltennamens

Im folgenden Codebeispiel wird der Name der ersten Spalte in der Tabelle zu Kaufdatum geändert. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie am besten an die aktuellen Daten in der Tabelle angepasst werden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");

    let expensesTable = sheet.tables.getItem("ExpensesTable");
    expensesTable.columns.load("items");

    await context.sync();
        
    expensesTable.columns.items[0].name = "Purchase date";

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    await context.sync();
});

Tabelle mit neuem Spaltennamen

Tabelle mit neuem Spaltennamen in Excel.

Abrufen von Daten aus einer Tabelle

Im folgenden Codebeispiel werden Daten aus einer Tabelle mit dem Namen ExpensesTable im Arbeitsblatt mit der Bezeichnung Beispiel gelesen. Anschließend werden die Daten unter der Tabelle auf dem gleichen Arbeitsblatt ausgegeben.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    // Get data from the header row.
    let headerRange = expensesTable.getHeaderRowRange().load("values");

    // Get data from the table.
    let bodyRange = expensesTable.getDataBodyRange().load("values");

    // Get data from a single column.
    let columnRange = expensesTable.columns.getItem("Merchant").getDataBodyRange().load("values");

    // Get data from a single row.
    let rowRange = expensesTable.rows.getItemAt(1).load("values");

    // Sync to populate proxy objects with data from Excel.
    await context.sync();

    let headerValues = headerRange.values;
    let bodyValues = bodyRange.values;
    let merchantColumnValues = columnRange.values;
    let secondRowValues = rowRange.values;

    // Write data from table back to the sheet
    sheet.getRange("A11:A11").values = [["Results"]];
    sheet.getRange("A13:D13").values = headerValues;
    sheet.getRange("A14:D20").values = bodyValues;
    sheet.getRange("B23:B29").values = merchantColumnValues;
    sheet.getRange("A32:D32").values = secondRowValues;

    // Sync to update the sheet in Excel.
    await context.sync();
});

Tabellen und Datenausgabe

Tabellendaten in Excel.

Erkennen von Datenänderungen

Das Add-In muss möglicherweise auf Datenänderungen von Benutzern in einer Tabelle reagieren. Um diese Änderungen zu erkennen, können Sie für das onChanged-Ereignis einer Tabelle einen Ereignishandler registrieren. Ereignishandler für das onChanged-Ereignis empfangen ein TableChangedEventArgs-Objekt, wenn das Ereignis ausgelöst wird.

Das TableChangedEventArgs-Objekt liefert Informationen zu den Änderungen und zur Quelle. Da onChanged ausgelöst wird, wenn sich entweder das Format oder der Wert der Daten ändert, kann es hilfreich sein, dass das Add-In prüft, ob die Werte tatsächlich geändert wurden. Die details-Eigenschaft kapselt diese Informationen als ChangedEventDetail. Das folgende Codebeispiel zeigt, wie die Vorher- und Nachher-Werte sowie die Typen einer Zelle angezeigt werden, die geändert wurde.

// This function would be used as an event handler for the Table.onChanged event.
async function onTableChanged(eventArgs) {
    await Excel.run(async (context) => {
        let details = eventArgs.details;
        let address = eventArgs.address;

        // Print the before and after types and values to the console.
        console.log(`Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),`
            + ` now is ${details.valueAfter}(${details.valueTypeAfter})`);
        await context.sync();
    });
}

Sortieren von Daten in einer Tabelle

Im folgenden Codebeispiel werden die Tabellendaten in absteigender Reihenfolge nach den Werten in den vier Spalten der Tabelle sortiert.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    // Queue a command to sort data by the fourth column of the table (descending).
    let sortRange = expensesTable.getDataBodyRange();
    sortRange.sort.apply([
        {
            key: 3,
            ascending: false,
        },
    ]);

    // Sync to run the queued command in Excel.
    await context.sync();
});

Tabellendaten sortiert nach Betrag (absteigend)

Sortierte Tabellendaten in Excel.

Wenn Daten in einem Arbeitsblatt sortiert werden, wird eine Ereignisbenachrichtigung ausgelöst. Weitere Informationen zu sortierungsbezogenen Ereignissen und dazu, wie Ihr Add-in Ereignishandler registrieren kann, um auf solche Ereignisse zu reagieren, finden Sie unter Behandeln von Sortierungsereignissen.

Anwenden von Filtern auf eine Tabelle

Im folgenden Codebeispiel werden Filter auf die Spalte Betrag und die Spalte Kategorie einer Tabelle angewandt. Dieser Filter bewirkt, dass nur Zeilen, in denen die Kategorie als Wert angegeben wurde und der Betrag unterhalb unter dem Durchschnittswert aller Zeilen liegt, angezeigt werden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    // Queue a command to apply a filter on the Category column.
    let categoryFilter = expensesTable.columns.getItem("Category").filter;
    categoryFilter.apply({
      filterOn: Excel.FilterOn.values,
      values: ["Restaurant", "Groceries"]
    });

    // Queue a command to apply a filter on the Amount column.
    let amountFilter = expensesTable.columns.getItem("Amount").filter;
    amountFilter.apply({
      filterOn: Excel.FilterOn.dynamic,
      dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
    });

    // Sync to run the queued commands in Excel.
    await context.sync();
});

Tabellendaten mit angewandten Filtern für Kategorie und Betrag

In Excel gefilterte Tabellendaten.

Filter zum Löschen der Tabelle

Im folgenden Codebeispiel werden alle aktuell auf die Tabelle angewandten Filter gelöscht.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.clearFilters();

    await context.sync();
});

Tabellendaten ohne angewandte Filter

Nicht gefilterte Tabellendaten in Excel.

Abrufen des sichtbaren Bereichs einer gefilterten Tabelle

Im folgenden Codebeispiel wird ein Bereich abgerufen, der nur Daten für Zellen enthält, die aktuell in der angegebenen Tabelle sichtbar sind. Die Werte dieses Bereichs werden dann auf die Konsole übertragen. Sie können die getVisibleView() Methode wie unten gezeigt verwenden, um den sichtbaren Inhalt einer Tabelle abzurufen, wenn Spaltenfilter angewendet wurden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    let visibleRange = expensesTable.getDataBodyRange().getVisibleView();
    visibleRange.load("values");

    await context.sync();
    console.log(visibleRange.values);
});

AutoFilter

Ein Add-In kann das AutoFilter-Objekt der Tabelle verwenden, um Daten zu filtern. Ein AutoFilter-Objekt stellt die gesamte Filterstruktur einer Tabelle oder eines Bereichs dar. Alle weiter oben in diesem Artikel beschriebenen Filtervorgänge sind mit dem Auto-Filter kompatibel. Der einzelne Zugriffspunkt erleichtert das Auswählen und Verwalten mehrerer Filter.

Das folgende Codebeispiel zeigt den gleichen Vorgang zum Filtern von Daten wie das vorherige Codebeispiel, er wird jedoch komplett durch den Auto-Filter ausgeführt.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.autoFilter.apply(expensesTable.getRange(), 2, {
        filterOn: Excel.FilterOn.values,
        values: ["Restaurant", "Groceries"]
    });
    expensesTable.autoFilter.apply(expensesTable.getRange(), 3, {
        filterOn: Excel.FilterOn.dynamic,
        dynamicCriteria: Excel.DynamicFilterCriteria.belowAverage
    });

    await context.sync();
});

AutoFilter kann auch auf einen Bereich auf Arbeitsblattebene angewendet werden. Weitere Informationen finden Sie unter Arbeiten mit Arbeitsblättern mithilfe der Excel-JavaScript-API.

Formatieren einer Tabelle

Im folgenden Beispiel wird eine Tabelle formatiert. Es gibt verschiedene Füllfarben für die Überschriftenzeile, den Hauptteil, die zweite Zeile und die erste Spalte der Tabelle. Weitere Informationen zu den Eigenschaften, die für die Formatierung verwendet werden können, finden Sie unter RangeFormat-Objekt (JavaScript-API für Excel).

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let expensesTable = sheet.tables.getItem("ExpensesTable");

    expensesTable.getHeaderRowRange().format.fill.color = "#C70039";
    expensesTable.getDataBodyRange().format.fill.color = "#DAF7A6";
    expensesTable.rows.getItemAt(1).getRange().format.fill.color = "#FFC300";
    expensesTable.columns.getItemAt(0).getDataBodyRange().format.fill.color = "#FFA07A";

    await context.sync();
});

Tabelle nach der Formatierung

Tabelle nach dem Anwenden der Formatierung in Excel.

Konvertieren eines Bereichs in eine Tabelle

Im folgenden Codebeispiel wird ein Bereich von Daten erstellt und dann in eine Tabelle konvertiert. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie am besten an die aktuellen Daten in der Tabelle angepasst werden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");

    // Define values for the range.
    let values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"],
    ["Frames", 5000, 7000, 6544, 4377],
    ["Saddles", 400, 323, 276, 651],
    ["Brake levers", 12000, 8766, 8456, 9812],
    ["Chains", 1550, 1088, 692, 853],
    ["Mirrors", 225, 600, 923, 544],
    ["Spokes", 6005, 7634, 4589, 8765]];

    // Create the range.
    let range = sheet.getRange("A1:E7");
    range.values = values;

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    sheet.activate();

    // Convert the range to a table.
    let expensesTable = sheet.tables.add('A1:E7', true);
    expensesTable.name = "ExpensesTable";

    await context.sync();
});

Daten im Bereich (vor der Konvertierung in eine Tabelle)

Daten im Bereich in Excel.

Daten in der Tabelle (nach der Konvertierung des Bereichs in eine Tabelle)

Daten in tabelle in Excel.

Importieren von JSON-Daten in eine Tabelle

Im folgenden Codebeispiel wird eine Tabelle auf dem Arbeitsblatt mit der Bezeichnung Beispiel erstellt. Anschließend wird die Tabelle mithilfe eines JSON-Objekts, das zwei Datenzeilen definiert, gefüllt. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie am besten an die aktuellen Daten in der Tabelle angepasst werden.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");

    let expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    expensesTable.name = "ExpensesTable";
    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

    let transactions = [
      {
        "DATE": "1/1/2017",
        "MERCHANT": "The Phone Company",
        "CATEGORY": "Communications",
        "AMOUNT": "$120"
      },
      {
        "DATE": "1/1/2017",
        "MERCHANT": "Southridge Video",
        "CATEGORY": "Entertainment",
        "AMOUNT": "$40"
      }
    ];

    let newData = transactions.map(item =>
        [item.DATE, item.MERCHANT, item.CATEGORY, item.AMOUNT]);

    expensesTable.rows.add(null, newData);

    sheet.getUsedRange().format.autofitColumns();
    sheet.getUsedRange().format.autofitRows();

    sheet.activate();

    await context.sync();
});

Neue Tabelle

Neue Tabelle aus importierten JSON-Daten in Excel.

Siehe auch