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 Table Objekten und TableCollection unterstützt werden, finden Sie unter Table Object (JavaScript-API für Excel) und TableCollection-Objekt (JavaScript-API für 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 AnforderungssatzExcelApi 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 entsprechen.

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

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 ist auf nullfestgelegt, wodurch angegeben wird, dass die Zeilen nach den vorhandenen Zeilen in der Tabelle hinzugefügt werden. Der alwaysInsert Parameter ist auf truefestgelegt, was angibt, dass die neuen Zeilen in die Tabelle und nicht unterhalb der Tabelle eingefügt werden. Die Breite der Spalten und die Höhe der Zeilen werden dann so festgelegt, dass sie den aktuellen Daten in der Tabelle am besten entsprechen.

// 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

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.

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 den aktuellen Daten in der Tabelle am besten entsprechen.

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

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 den aktuellen Daten in der Tabelle am besten entsprechen.

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

Ä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. Um die Größe einer Tabelle zu ändern, verwenden Sie die Table.resize-Methode . Im folgenden Codebeispiel wird gezeigt, 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();
});

Tabelle nach Größenänderung

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 den aktuellen Daten in der Tabelle am besten entsprechen.

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

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

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)

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

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

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

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 den aktuellen Daten in der Tabelle am besten entsprechen.

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 in der Tabelle (nach der Konvertierung des Bereichs in eine Tabelle)

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 den aktuellen Daten in der Tabelle am besten entsprechen.

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

Siehe auch

• JavaScript-Objektmodell für Excel in Office-Add-Ins