Arbeiten mit Tabellen und Diagrammen in Excel-Arbeitsmappen

  • 14 Minuten

Die Excel-JavaScript-API ermöglicht die programmgesteuerte Kontrolle von Text, Tabellen und Diagrammen in Excel-Arbeitsmappen. In dieser Lerneinheit erfahren Sie, wie Sie mit Tabellen arbeiten können, einschließlich Formatierungsoptionen, Filtern von Daten und Sortieren der Daten in der Tabelle. Außerdem erfahren Sie, wie Sie Ihren Arbeitsblättern Diagramme hinzufügen und diese anpassen können.

Übersicht über die Office-Entwicklerplattform

Die Microsoft 365-Entwicklerplattform, die Office umfasst, bietet Entwicklern viel Spielraum zum Einbetten von Anpassungen. Office-Add-Ins gehören zu einem dieser drei Spielräume: Dokumente, Unterhaltungen und Seiten.

Microsoft Teams führt Unterhaltungen zwischen Benutzern durch und ermöglicht es Entwicklern, die Erfahrung mit Messaging-Erweiterungen, Unterhaltungs-Bots und anderen Anpassungsoptionen zu erweitern.

Entwickler passen Seiten in SharePoint-Server und SharePoint-Online mit dem SharePoint-Framework an.

Office-Clients wie Word, Excel, PowerPoint, OneNote und Outlook können ebenfalls erweitert werden, um benutzerdefinierte Aufgabenbereiche, Aktionen und andere Anpassungen mit Hilfe von Add-Ins zu implementieren.

Übersichtsdiagramm der Microsoft 365-Plattform.

Anatomie des Office.js-Add-Ins für Microsoft Excel

Zuerst wird die Anatomie eines Office.js für Microsoft Excel betrachtet:

Überblick über die Anatomie eines Office.js-Add-Ins.

  • Alle Office-Add-Ins müssen beim ersten Laden einer Seite die Office.initialize()-Methode deaktivieren.
  • Wenn Sie eine neuere Office.js-Funktion in Ihrem Add-In verwenden, ist es wichtig zu prüfen, ob der Client diese Erweiterungen über die requirements-API unterstützt.
  • Für die JavaScript-APIs für Excel verwenden Sie die Excel.run()-Methode, um eine Instanz des aktuellen Arbeitsmappen-context zu erhalten.
  • Sobald Sie einen Verweis auf den context der aktuellen Excel-Arbeitsmappe haben, können Sie mit der load()-Methode beliebige Eigenschaften in den Kontext laden. Mit dieser Methode wird die Anfrage-Warteschlange hinzugefügt, so dass Sie mehrere Anfragen aus Performance-Gründen miteinander verketten können.
  • Wenn Sie bereit sind, die Eigenschaften, die Sie in die Warteschlange gestellt haben, abzurufen oder beliebige Aktionen in der Warteschlange durchzuführen, verwenden Sie die context.sync()-Methode, um den mit der load()-Methode definierten Stapel von Operationen in der Warteschlange auszuführen.
  • Die context.sync()-Methode gibt ein JavaScript-Versprechen zurück, das verwendet werden kann, um Ergebnisse oder einen vorherigen Vorgang zu erhalten und neue Vorgänge durchzuführen.
  • Als bewährte Methode sollten Sie auf Fehler, die bei der Arbeit mit den JavaScript-APIs für Excel auftreten können, achten, sie abfangen und beheben.

Excel-Objekthierarchie

Entwickler von Excel-Add-Ins sollten die Hierarchie einer Excel-Arbeitsmappe und deren Beziehung zu den Objekten in Office.js verstehen.

Hierarchie einer Arbeitsmappe

Office.js liefert Kontext für eine Excel-Arbeitsmappe über Excel.run() und die context.workbook-Eigenschaft.

Diagramm der Excel-Objekthierarchie.

Die Arbeitsmappe enthält Arbeitsblätter, die wiederum viele Sammlungen enthalten. Zu diesen Sammlungen gehören Elemente wie Diagramme, Tabellen und PivotTables.

Auf viele Objekte auf einem Arbeitsblatt, einschließlich Tabellen und PivotTables, kann direkt über das Arbeitsmappenobjekt zugegriffen werden.

Arbeitsblätter

Arbeitsblätter erkennen ihnen gleichgeordnete Elemente mithilfe von getNext()- und getPrevious()-Methoden.

Sie können das aktive Arbeitsblatt mithilfe der workbook.worksheets.getActiveWorksheet()-Methode ermitteln und das aktive Arbeitsblatt mit derworksheet.activate()-Methode festlegen.

Office.js bietet außerdem viele Arbeitsblattereignissen wie onActivated, onDeactivated und onSelectionChanged, die Entwickler in ihren benutzerdefinierten Add-Ins verwenden können.

Tabellen und Überschriften

Tabellen bilden das Herzstück einer Kalkulationstabelle. Excel unterstützt das Definieren eines Datenbereichs, der die Grundlage einer Datentabelle bildet.

Screenshot eines Beispiels für eine Excel-Arbeitsmappe.

Bereiche

Ein Bereich stellt einen Satz einer oder mehrerer zusammenhängender Zellen wie z. B. eine Zelle, eine Zeile oder eine Spalte, ein Block von Zellen usw. dar.

Sie können ein Bereichsobjekt mit Office.js über ein Arbeitsblatt und eine Adresse abrufen. Der Bereich "A1:D4" stellt beispielsweise einen Bereich von Zellen von oben links nach unten rechts in diesem Screenshot dar.

Tabellen

Eine Tabelle wird basierend auf einem Datenbereich eingerichtet.

Die Funktion tables.add() akzeptiert einen Datenbereich mit einer Kennzeichnung, um anzugeben, ob die Tabelle Überschriften enthält.

Vorhandene Tabellen können anhand ihres benannten Bereichs oder ihrer ID abgerufen werden. Entwickler können auch mehrere Tabellen auf dem Arbeitsblatt durchlaufen.

Nachdem die Tabelle hinzugefügt wurde, können Überschriften und Tabellenzeilen mithilfe von zweidimensionalen Arrays hinzugefügt werden.

Überschriften

Eine Tabelle, die mit einer Überschriftenkennzeichnung erstellt wird, verwendet die erste Zeile im Datenbereich für die Überschriften.

Sie können Überschriftenwerte auch mit der getHeaderRowRange().values-Eigenschaft mit einem zweidimensionalen Array festlegen.

Die folgenden Codeschnipsel veranschaulichen Arbeitsbeispiele für Arbeitstext und Absätze unter Verwendung der JavaScript-API für Excel:

  • Abrufen eines Datenbereichs aus einem Arbeitsblatt:

    const range = currWorksheet.getRange('A1:D1');

  • Einfügen einer Tabelle in das Arbeitsblatt basierend auf dem angegebenen Bereich

    const table = currWorksheet.tables.add(range, true);
table.name = "ExpensesTable";

  • Abrufen einer Tabelle anhand des Namens aus einer Arbeitsmappe oder einem Arbeitsblatt

    const table = workbook.tables.getItem("ExpensesTable");

  • Hinzufügen einer Überschrift zur angegebenen Tabelle

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

  • Fixieren der Überschriftenzeile durch Fixieren der obersten Zeile

    currWorksheet.freezePanes.freezeRows(1);

  • Löschen der zweiten Zeile in einer Tabelle

    const row = context.workbook.tables.getItem("ExpensesTable").rows.getItemAt(1);
row.delete();

  • Aktualisieren der zweiten Zeile in einer Tabelle

    const row = context.workbook.tables.getItem("ExpensesTable").rows.getItemAt(1);
row.values = [["1/15/2017", "Best For You Organics Company", "Groceries", "97.8"]];

  • Hinzufügen von Zeilen am Ende einer Tabelle

    table.rows.add(null, [
  ["1/1/2017", "The Phone Company", "Communications", "120"]
]);

    Hinweis

    Der erste Parameter der add()-Methode gibt den Index an, an dem die Daten hinzugefügt werden sollen. Bei Wert null oder -1 wird die Zeile am Ende der Tabelle hinzugefügt.

  • Hinzufügen einer Zeile

    table.rows.add(0, [
  ["1/10/2017", "Coho Vineyard", "Restaurant", "33"]
]);

    Hinweis

    Der erste Parameter der add()-Methode gibt den Index an, an dem die Daten hinzugefügt werden sollen. Der Wert 0 fügt die Daten am Anfang des Index ein.

Filtern von Tabellen

Jede Spalte in einer Tabelle kann mithilfe von Office.js gefiltert werden.

Screenshot von Excel mit einer Tabelle mit Daten.

Filtern Sie eine Spalte, indem Sie zuerst einen Verweis auf die Spalte angeben, und dann die applyValuesFilter()-Methode verwenden, um nach bestimmten Werten zu filtern.

Der folgende Code filtert die Kategorie-Spalte einer Tabelle anhand der Werte Bildung und Lebensmittel:

const categoryFilter = table.columns.getItem('Category').filter;
categoryFilter.applyValuesFilter(["Education", "Groceries"]);

Mithilfe der Tabellenfunktionen reapplyFilters() bzw. clearFilters() können Sie auch programmgesteuert Filter erneut anwenden bzw. Filter für eine Tabelle löschen.

Der folgende Code veranschaulicht das erneute Anwenden und Löschen von Filtern:

// re-apply filters
table.reapplyFilters();

// clear filters
table.clearFilters();

Sortieren von Tabellen

Entwickler können Tabellendaten mithilfe der Office.js-API über Excel-Add-Ins sortieren. Rufen Sie zum Sortieren die table.sort.apply()-Methode auf, und schließen Sie das SortFields-Argument ein, um die Felder anzugeben, nach denen sortiert werden soll.

Der folgende Code definiert ein Array von Sortierfeldern. Die Eigenschaft key gibt den Spaltenindex in der Tabelle an, und die boolesche Eigenschaft ascending gibt an, ob die Sortierung in aufsteigender oder absteigender Reihenfolge erfolgen soll:

const sortFields = [
  { key: 1, ascending: false },
  { key: 2, ascending: true }
];
table.sort.apply(sortFields);

Sie können Tabellensortierungen mithilfe der table.sort.reapply()- und table.sort.clear()-Methoden erneut anwenden und löschen.

// re-apply sort
table.sort.reapply();

// clear filters
table.sort.clear();

Diagramme

Microsoft Excel hat sich zu einer Spielwiese für die Datenbearbeitung und -visualisierung entwickelt. Es überrascht also nicht, dass die Excel-JavaScript-APIs Entwicklern das Hinzufügen und Bearbeiten von Diagrammen ermöglichen.

Diagramme befinden sich in Arbeitsblättern, können jedoch auch direkt über das Arbeitsmappenobjekt aufgerufen werden.

Screenshot einer Excel-Arbeitsmappe mit einem Diagramm.

Diagramme weisen viele komplexe relationale Eigenschaften auf, die verwendet werden können, um das Aussehen eines Diagramms zu optimieren. Dazu gehören Titel, Legenden, Achsen, Reihen, Beschriftungen und Formate.

Benutzer und Entwickler können Diagramme erstellen, die auf Datenbereichen basieren und häufig mit Tabellen erstellt werden.

Die Funktion worksheet.charts.add() wird verwendet, um ein Diagramm zu erstellen, das einen Diagramm-type, einen Daten-range und seriesBy zulässt. Das seriesBy-Argument unterstützt folgende Werte:

  • Auto
  • Scalar
  • Matrix

Excel unterstützt viele verschiedene Arten von Diagrammen. Entwickler können einem Arbeitsblatt mithilfe der worksheet.carts.add("{REPLACE_WITH_CHARTTYPE_ENUM}", range, "{REPLACE_WITH_CHARTSERIESBY_ENUM}")-Methode ein Diagramm hinzufügen.

Das ChartType-Argument gibt den Typ des zu verwendenden Diagramms an. Im SDK finden Sie die verfügbaren Optionen für Excel.ChartType enum.

Das ChartSeriesBy-Argument gibt an, ob die Datenreihen nach Zeilen oder Spalten geordnet sind. Die verfügbaren Optionen für Excel.ChartSeriesBy enum finden Sie im SDK.

Diagrammeigenschaften

Das Excel-Diagramm-Objekt enthält mehrere Eigenschaften, die Entwickler zum Anpassen von Diagrammen in Excel-Arbeitsblättern verwenden können. In der folgenden Tabelle sind einige der im Office.js-Diagramm-Objekt häufig verwendeten Eigenschaften aufgeführt:

Eigenschaft Beschreibung
chartType Gibt die Art des Diagramms an (mögliche Werte auf der vorherigen Folie).
height Gibt die Höhe des Diagrammobjekts an (in Punkten).
id Die eindeutige ID des Diagramms.
left Der Abstand von der linken Seite des Diagramms zu dem Ursprung des Arbeitsblatts (in Punkten).
name Gibt den Namen eines Diagrammobjekts an.
showAllFieldButtons Gibt an, ob alle Feldschaltflächen in einem PivotChart angezeigt werden sollen.
top Der Abstand (in Punkten) von dem oberen Rand des Objekts zum oberen Rand von Zeile 1 (auf einem Arbeitsblatt) oder zum oberen Diagrammbereich (in einem Diagramm).
width Die Breite des Diagrammobjekts (in Punkten).

Zusätzlich zu den Eigenschaften in der obigen Tabelle enthält das Diagrammobjekt mehrere Beziehungseigenschaften. Diese beeinflussen die Anzeige des Diagramms in Excel:

Beziehung Beschreibung
axes Die Achsen des Diagramms. Schreibgeschützt.
dataLabels Stellt die Datenbeschriftungen im Diagramm dar. Schreibgeschützt.
Format Kapselt die Formateigenschaften für den Diagrammbereich. Schreibgeschützt.
legend Die Legende für das Diagramm. Schreibgeschützt.
series Eine einzelne Datenreihe oder eine Sammlung von Datenreihen im Diagramm. Schreibgeschützt.
title Der Titel des angegebenen Diagramms, einschließlich Text, Sichtbarkeit, Position und Formatierung des Titels. Schreibgeschützt.
Arbeitsblatt Das Arbeitsblatt, das das aktuelle Diagramm enthält. Schreibgeschützt.

Tipp

In der Excel Office.js-Dokumentation finden Sie alle Eigenschaften, Methoden und Ereignisse für das Excel.Chart-Objekt.

Die folgenden Codebeispiele veranschaulichen, wie Sie die Office.js-API zum Erstellen und Bearbeiten von Diagrammen in Excel verwenden können:

  • Hinzufügen eines gruppierten Säulendiagramms mithilfe von Tabellendaten

    const dataRange = table.getDataBodyRange();
let chart = currWorksheet.charts.add("ColumnClustered", dataRange, "auto");

  • Abrufen eines vorhandenes Diagramms anhand des Namens

    let chart = workbook.charts.getItem("MyChart");

  • Ändern von Diagrammeigenschaften wie Position, Titel, Farben und Schriftgrade

    chart.setPosition("A15", "F30");
chart.title.text = "Expenses";
chart.legend.position = "right"
chart.legend.format.fill.setSolidColor("white");
chart.dataLabels.format.font.size = 15;
chart.dataLabels.format.font.color = "black";
chart.series.getItemAt(0).name = "Value in €";

Zusammenfassung

Die Excel-JavaScript-API ermöglicht die programmgesteuerte Kontrolle von Text, Tabellen und Diagrammen in Excel-Arbeitsmappen. In dieser Lerneinheit haben Sie erfahren, wie Sie mit Tabellen arbeiten können, einschließlich Formatierungsoptionen, Filtern von Daten und Sortieren der Daten in der Tabelle. Außerdem haben Sie erfahren, wie Sie Ihren Arbeitsblättern Diagramme hinzufügen und diese anpassen können.