Grundlagen für Office-Skripts in Excel

  • Artikel

In diesem Artikel werden die technischen Aspekte von Office-Skripts vorgestellt. Sie lernen die wichtigen Teile des TypeScript-basierten Skriptcodes kennen und erfahren, wie die Excel-Objekte und -APIs zusammenarbeiten.

Wenn Sie mit einer interaktiven Benutzeroberfläche beginnen möchten, probieren Sie Tutorial: Erstellen und Formatieren einer Excel-Tabelle aus, oder besuchen Sie unsere Beispiele.

TypeScript: Die Sprache von Office-Skripts

Office-Skripts werden in TypeScript geschrieben, einer Obermenge von JavaScript-. Wenn Sie mit JavaScript vertraut sind, können Sie dieses Wissen nutzen, da ein Teil des Codes in beiden Sprachen identisch ist. Es empfiehlt sich, über Programmierkenntnisse auf Anfängerniveau zu verfügen, bevor Sie mit dem Codieren von Office Scripts beginnen. Die folgenden Ressourcen können Ihnen dabei helfen, das Coding von Office-Skripts zu verstehen.

main-Funktion: Ausgangspunkt des Skripts

Jedes Skript muss eine main-Funktion mit dem ExcelScript.Workbook-Typ als ersten Parameter enthalten. Wenn die Funktion ausgeführt wird, ruft die Excel-Anwendung diese main-Funktion auf, indem sie die Arbeitsmappe als ersten Parameter bereitstellt. Ein ExcelScript.Workbook sollte immer der erste Parameter sein.

function main(workbook: ExcelScript.Workbook) {
  // Your code goes here
}

Der Code innerhalb der main-Funktion wird beim Ausführen des Skripts ausgeführt. main kann andere Funktionen in Ihrem Skript aufrufen, Code, der nicht in einer Funktion enthalten ist, wird jedoch nicht ausgeführt. Skripts können keine anderen Office-Skripts aufrufen.

Power Automate ermöglicht es Ihnen, Skripts in Flüssen zu verbinden. Daten werden zwischen den Skripts und dem Flow durch die Parameter und Rückgaben der main-Funktion übergeben. Die Integration von Office-Skripts mit Power Automate wird im Detail unter Ausführen von Office-Skripts mit Power Automate behandelt.

Übersicht über das Objektmodell

Wenn Sie ein Skript schreiben möchten, müssen Sie verstehen, wie die Office-Scripts-APIs zusammenpassen. Die Komponenten einer Arbeitsmappe haben bestimmte Beziehungen zueinander. Auf vielerlei Weise entsprechen diese Beziehungen denen der Excel-Benutzeroberfläche.

  • Eine Arbeitsmappe enthält mindestens ein Arbeitsblatt.
  • Ein Arbeitsblatt ermöglicht den Zugriff auf Zellen über Bereichsobjekte.
  • Ein Bereich besteht aus einer Gruppe zusammenhängender Zellen.
  • Bereiche werden verwendet, um Tabellen, Diagramme, Formen sowie andere Objekte für die Datenvisualisierung oder -organisation zu erstellen und zu platzieren.
  • Ein Arbeitsblatt enthält Sammlungen dieser Datenobjekte, die auf dem jeweiligen Blatt vorhanden sind.
  • Arbeitsmappen enthalten Sammlungen einiger dieser Datenobjekte (z. B. Tabellen) für die gesamte Arbeitsmappe.

Die vollständige Liste der Office-Skript-API-Objekte ist im ExcelScript-Paket aufgeführt.

Arbeitsmappe

Jedes Skript wird von der main-Funktion als workbook-Objekt vom Typ Workbook bereitgestellt. Damit wird das Objekt der obersten Ebene dargestellt, durch das das Skript mit der Excel-Arbeitsmappe interagiert.

Das folgende Skript ruft das aktive Arbeitsblatt aus der Arbeitsmappe ab und protokolliert den Namen.

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Display the current worksheet's name.
    console.log(sheet.getName());
}

Bereiche

Ein Bereich ist eine Gruppe zusammenhängender Zellen in der Arbeitsmappe. In Skripts wird in der Regel eine Notation im A1-Format verwendet (z. B. B3 für die einzelne Zelle in Spalte B und Zeile 3 oder C2:F4 für die Zellen in den Spalten C bis F und den Zeilen 2 bis 4), um Bereiche zu definieren.

Bereiche besitzen drei Haupteigenschaften: Werte, Formeln und Format. Durch diese Eigenschaften können die Zellwerte, die zu prüfenden Formeln sowie die visuelle Formatierung der Zellen abgerufen oder festgelegt werden. Sie können über getValues, getFormulas und getFormatauf sie zugreifen. Werte und Formeln können mit setValues und setFormulasgeändert werden, wohingegen das Format ein RangeFormat-Objekt ist, das aus mehreren kleineren Objekten besteht, die einzeln festgelegt werden.

Bereiche verwenden zweidimensionale Arrays zum Verwalten von Informationen. Weitere Informationen zum Umgang mit Arrays im Office Scripts-Framework finden Sie unter Arbeiten mit Bereichen.

Beispiel für einen Bereich

Das folgende Beispiel zeigt, wie Sie Verkaufsdatensätze erstellen können. In diesem Skript werden Range-Objekte zum Festlegen der Werte, Formeln und Teilen des Formats verwendet.

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Create the headers and format them to stand out.
    let headers = [["Product", "Quantity", "Unit Price", "Totals"]];
    let headerRange = sheet.getRange("B2:E2");
    headerRange.setValues(headers);
    headerRange.getFormat().getFill().setColor("#4472C4");
    headerRange.getFormat().getFont().setColor("white");

    // Create the product data rows.
    let productData = [
        ["Almonds", 6, 7.5],
        ["Coffee", 20, 34.5],
        ["Chocolate", 10, 9.54],
    ];
    let dataRange = sheet.getRange("B3:D5");
    dataRange.setValues(productData);

    // Create the formulas to total the amounts sold.
    let totalFormulas = [
        ["=C3 * D3"],
        ["=C4 * D4"],
        ["=C5 * D5"],
        ["=SUM(E3:E5)"],
    ];
    let totalRange = sheet.getRange("E3:E6");
    totalRange.setFormulas(totalFormulas);
    totalRange.getFormat().getFont().setBold(true);

    // Display the totals as US dollar amounts.
    totalRange.setNumberFormat("$0.00");
}

Wenn Sie dieses Skript ausführen, werden die folgenden Daten im aktuellen Arbeitsblatt erstellt:

Ein Arbeitsblatt mit einem Verkaufsdatensatz, der aus Zeilen mit Werten, einer Spalte mit Formeln und formatierten Überschriften besteht.

Typen von Bereichswerten

Jede Zelle verfügt über einen Wert. Dieser Wert ist der in die Zelle eingegebene zugrunde liegende Wert, der sich von dem in Excel angezeigten Text unterscheiden kann. Beispielsweise könnte "02.05.2021" in der Zelle als Datum angezeigt werden, aber der tatsächliche Wert ist "44318". Diese Darstellung kann über das Zahlenformat geändert werden, aber der tatsächliche Wert und Typ in der Zelle ändern sich nur, wenn ein neuer Wert festgelegt wird.

Wenn Sie den Zellwert verwenden, ist es wichtig, TypeScript mitzuteilen, welchen Wert Sie von einer Zelle oder einem Bereich erhalten möchten. Eine Zelle enthält einen der folgenden Typen: string, number oder boolean. Damit die zurückgegebenen Werte vom Skript als einer dieser Typen behandelt werden, müssen Sie den Typ deklarieren.

Das folgende Skript ruft den Durchschnittspreis aus der Tabelle aus dem vorherigen Beispiel ab. Beachten Sie den Code priceRange.getValues() as number[][]. Dieser legt für die Bereichswerte den Typ number[][] fest. Alle Werte in diesem Array können dann im Skript als Zahlen behandelt werden.

function main(workbook: ExcelScript.Workbook) {
  // Get the active worksheet.
  let sheet = workbook.getActiveWorksheet();

  // Get the "Unit Price" column. 
  // The result of calling getValues is declared to be a number[][] so that we can perform arithmetic operations.
  let priceRange = sheet.getRange("D3:D5");
  let prices = priceRange.getValues() as number[][];

  // Get the average price.
  let totalPrices = 0;
  prices.forEach((price) => totalPrices += price[0]);
  let averagePrice = totalPrices / prices.length;
  console.log(averagePrice);
}

Diagramme, Tabellen und andere Datenobjekte

Skripts können die Datenstrukturen und -visualisierungen in Excel erstellen und ändern. Tabellen und Diagramme sind zwei der am häufigsten verwendeten Objekte, die APIs unterstützen aber auch PivotTables, Formen, Bilder und vieles mehr. Diese werden in Sammlungen gespeichert, die weiter unten in diesem Artikel erläutert werden.

Erstellen einer Tabelle

Erstellen Sie Tabellen mithilfe von mit Daten ausgefüllten Bereichen. Auf den Bereich werden automatisch Formatierungs- und Tabellen-Steuerelemente (wie z. B. Filter) angewendet.

Durch das folgende Skript wird eine Tabelle auf Grundlage der Bereiche aus dem vorherigen Beispiel erstellt.

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Add a table that has headers using the data from B2:E5.
    sheet.addTable("B2:E5", true);
}

Wenn Sie dieses Skript auf das Arbeitsblatt mit den vorherigen Daten anwenden, wird die folgende Tabelle erstellt:

Ein Arbeitsblatt, das eine Tabelle enthält, die aus dem vorherigen Verkaufsdatensatz erstellt wurde.

Erstellen eines Diagramms

Erstellen Sie Diagramme, um die Daten in einem Bereich darzustellen. In Skripts sind Dutzende von Diagrammvarianten zulässig, die jeweils an Ihre Anforderungen angepasst werden können.

Mit dem folgenden Skript wird ein einfaches Säulendiagramm für drei Elemente erstellt und 100 Pixel unterhalb des oberen Rands des Arbeitsblatts platziert.

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet.
    let sheet = workbook.getActiveWorksheet();

    // Create a column chart using the data from B3:C5.
    let chart = sheet.addChart(
        ExcelScript.ChartType.columnStacked,
        sheet.getRange("B3:C5")
    );

    // Set the margin of the chart to be 100 pixels from the top of the screen.
    chart.setTop(100);
}

Wenn Sie dieses Skript auf das Arbeitsblatt mit der vorherigen Tabelle anwenden, wird das folgende Diagramm erstellt:

Ein Säulendiagramm mit den Mengen von drei Elementen aus dem vorherigen Verkaufsdatensatz.

Sammlungen

Wenn ein Excel-Objekt eine Sammlung von mindestens einem Objekt desselben Typs enthält, werden diese in einem Array gespeichert. Beispielsweise enthält ein Workbook-Objekt ein Worksheet[]. Auf dieses Array wird über die Workbook.getWorksheets()-Methode zugegriffen. get-Methoden im Plural (z. B. Worksheet.getCharts()) geben die gesamte Objektsammlung als Array zurück. Dieses Muster gilt für alle Office Scripts-APIs: Das Worksheet-Objekt beinhaltet eine getTables()-Methode, die ein Table[] zurückgibt, das Table-Objekt beinhaltet eine getColumns()-Methode, die ein TableColumn[] zurückgibt, und so weiter.

Das zurückgegebene Array ist ein normales Array, daher stehen alle normalen Arrayoperationen für Ihr Skript zur Verfügung. Sie können auch auf einzelne Objekte innerhalb der Sammlung zugreifen, indem Sie den Arrayindexwert verwenden. workbook.getTables()[0] gibt beispielsweise die erste Tabelle in der Sammlung zurück. Weitere Informationen zur Verwendung der integrierten Arrayfunktionen mit dem Office Scripts-Framework finden Sie unter Arbeiten mit Sammlungen.

Der Zugriff auf einzelne Objekte über die Sammlung erfolgt auch über eine get-Methode. get-Methoden im Singular (z. B. Worksheet.getTable(name)) geben ein einzelnes Objekt zurück und benötigen eine ID oder einen Namen für das jeweilige Objekt. Diese ID oder dieser Name wird normalerweise durch das Skript oder über die Excel-Benutzeroberfläche festgelegt.

Das folgende Skript ruft alle Tabellen in der Arbeitsmappe ab. Dann wird sichergestellt, dass die Kopfzeilen angezeigt werden, die Filterschaltflächen sichtbar sind und das Tabellenformat auf „TableStyleLight1“ festgelegt ist.

function main(workbook: ExcelScript.Workbook) {
  // Get the table collection.
  let tables = workbook.getTables();

  // Set the table formatting properties for every table.
  tables.forEach(table => {
    table.setShowHeaders(true);
    table.setShowFilterButton(true);
    table.setPredefinedTableStyle("TableStyleLight1");
  })
}

Hinzufügen von Excel-Objekten mit einem Skript

Sie können Dokumentobjekte, z. B. Tabellen oder Diagramme, programmgesteuert hinzufügen, indem Sie die entsprechende add-Methode aufrufen, die für das übergeordnete Objekt verfügbar ist.

Wichtig

Fügen Sie keine Objekte manuell zu Sammlungsarrays hinzu. Verwenden Sie die add-Methoden in den übergeordneten Objekten. Fügen Sie z. B. Table mit der Worksheet.addTable-Methode zu Worksheet hinzu.

Mit dem folgenden Skript wird eine Tabelle in Excel auf dem ersten Arbeitsblatt in der Arbeitsmappe erstellt. Beachten Sie, dass die erstellte Tabelle von der addTable-Methode zurückgegeben wird.

function main(workbook: ExcelScript.Workbook) {
    // Get the first worksheet.
    let sheet = workbook.getWorksheets()[0];

    // Add a table that uses the data in A1:G10.
    let table = sheet.addTable(
      "A1:G10",
       true /* True because the table has headers. */
    );
    
    // Give the table a name for easy reference in other scripts.
    table.setName("MyTable");
}

Tipp

Die meisten Excel-Objekte besitzen eine setName-Methode. Dies bietet Ihnen eine einfache Möglichkeit, später im Skript oder in anderen Skripts für dieselbe Arbeitsmappe auf Excel-Objekte zuzugreifen.

Überprüfen, ob ein Objekt in der Sammlung vorhanden ist

Skripts müssen häufig überprüfen, ob eine Tabelle oder ein ähnliches Objekt vorhanden ist, bevor sie fortfahren. Verwenden Sie die Namen, die in Skripts oder über die Excel-Benutzeroberfläche angegeben sind, um erforderliche Objekte zu identifizieren und entsprechend zu handeln. get-Methoden geben undefined zurück, wenn sich das angeforderte Objekt nicht in der Sammlung befindet.

Das folgende Skript fordert eine Tabelle namens "MyTable" an und verwendet eine if...else-Anweisung, um zu überprüfen, ob die Tabelle gefunden wurde.

function main(workbook: ExcelScript.Workbook) {
  // Get the table named "MyTable".
  let myTable = workbook.getTable("MyTable");

  // If the table is in the workbook, myTable will have a value.
  // Otherwise, the variable will be undefined and go to the else clause.
  if (myTable) {
    let worksheetName = myTable.getWorksheet().getName();
    console.log(`MyTable is on the ${worksheetName} worksheet`);
  } else {
    console.log(`MyTable is not in the workbook.`);
  }
}

Ein gängiges Muster in Office-Skripts besteht in der Neuerstellung einer Tabelle, eines Diagramms oder eines anderen Objekts bei jeder Ausführung des Skripts. Wenn Sie die alten Daten nicht benötigen, ist es am besten, das alte Objekt zu löschen, bevor Sie das neue erstellen. Dadurch werden Namenskonflikte oder andere Abweichungen vermieden, die evtl. durch andere Benutzern eingeführt wurden.

Das folgende Skript entfernt die Tabelle mit dem Namen "MyTable", wenn sie vorhanden ist, und fügt dann eine neue Tabelle mit demselben Namen hinzu.

function main(workbook: ExcelScript.Workbook) {
  // Get the table named "MyTable" from the first worksheet.
  let sheet = workbook.getWorksheets()[0];
  let tableName = "MyTable";
  let oldTable = sheet.getTable(tableName);

  // If the table exists, remove it.
  if (oldTable) {
    oldTable.delete();
  }

  // Add a new table with the same name.
  let newTable = sheet.addTable("A1:G10", true);
  newTable.setName(tableName);
}

Entfernen von Excel-Objekten mit einem Skript

Wenn Sie ein Objekt löschen möchten, rufen Sie die delete-Methode des Objekts auf.

Hinweis

Wie beim Hinzufügen von Objekten dürfen Sie keine Objekte manuell aus Sammlungsarrays entfernen. Verwenden Sie die delete-Methoden in den Sammlungstypobjekten. Entfernen Sie beispielsweise Table mit Table.delete aus Worksheet.

Mit dem folgenden Skript wird das erste Arbeitsblatt in der Arbeitsmappe entfernt.

function main(workbook: ExcelScript.Workbook) {
    // Get first worksheet.
    let sheet = workbook.getWorksheets()[0];

    // Remove that worksheet from the workbook.
    sheet.delete();
}

Weitere Informationen zum Objektmodell

Die Referenzdokumentation zur Office Scripts-API besteht aus einer umfassender Liste der Objekte, die in Office-Skripts verwendet werden. Dort können Sie über das Inhaltsverzeichnis zu jedem Thema navigieren, über das Sie mehr erfahren möchten. Nachstehend finden Sie einige häufig besuchte Seiten.

Spezifische Informationen zum PivotTable-Objektmodell finden Sie unter Arbeiten mit PivotTables in Office-Skripts.

Siehe auch