Arbeiten mit PivotTables mithilfe der Excel-JavaScript-API

PivotTables optimieren größere Datasets. Sie ermöglichen die schnelle Bearbeitung gruppierter Daten. Die Excel-JavaScript-API ermöglicht es Ihrem Add-In, PivotTables zu erstellen und mit deren Komponenten zu interagieren. In diesem Artikel wird beschrieben, wie PivotTables durch die Office JavaScript-API dargestellt werden, und es werden Codebeispiele für wichtige Szenarien bereitgestellt.

Wenn Sie mit der Funktionalität von PivotTables nicht vertraut sind, sollten Sie sie als Endbenutzer untersuchen. Eine gute Einführung zu diesen Tools finden Sie unter Erstellen einer PivotTable zum Analysieren von Arbeitsblattdaten .

Wichtig

PivotTables, die mit OLAP erstellt wurden, werden derzeit nicht unterstützt. Es gibt auch keine Unterstützung für Power Pivot.

Objektmodell

Die PivotTable ist das zentrale Objekt für PivotTables in der Office JavaScript-API.

• Workbook.pivotTables und Worksheet.pivotTables sind PivotTableCollections , die die PivotTables in der Arbeitsmappe bzw. dem Arbeitsblatt enthalten.
• Eine PivotTable enthält eine PivotHierarchyCollection mit mehreren PivotHierarchies.
• Diese PivotHierarchies können bestimmten Hierarchieauflistungen hinzugefügt werden, um zu definieren, wie die PivotTable Daten pivotiert (wie im folgenden Abschnitt erläutert).
• Eine PivotHierarchy enthält eine PivotFieldCollection , die genau ein PivotField enthält. Wenn der Entwurf erweitert wird, um OLAP-PivotTables einzuschließen, kann sich dies ändern.
• Auf ein PivotField kann mindestens ein PivotFilter angewendet werden, solange die PivotHierarchy des Felds einer Hierarchiekategorie zugewiesen ist.
• Ein PivotField enthält eine PivotItemCollection mit mehreren PivotItems.
• Eine PivotTable enthält ein PivotLayout , das definiert, wo die PivotFields und PivotItems im Arbeitsblatt angezeigt werden. Das Layout steuert auch einige Anzeigeeinstellungen für die PivotTable.

Sehen wir uns an, wie diese Beziehungen auf einige Beispieldaten angewendet werden. In den folgenden Daten werden die Obstverkäufe verschiedener landwirtschaftlicher Betriebe beschrieben. Es wird das Beispiel in diesem Artikel sein.

Diese Verkaufsdaten der Obstfarm werden verwendet, um eine PivotTable zu erstellen. Jede Spalte, z. B . Types, ist eine PivotHierarchy. Die Hierarchie Typen enthält das Feld Typen . Das Feld Typen enthält die Elemente Apfel, Kiwi, Zitrone, Limette und Orange.

Hierarchien

PivotTables sind basierend auf vier Hierarchiekategorien organisiert: Zeile, Spalte, Daten und Filter.

Die weiter oben gezeigten Betriebsdaten haben fünf Hierarchien: Farmen, Typ, Klassifizierung, Crates Sold at Farm und Crates Sold Wholesale. Jede Hierarchie kann nur in einer der vier Kategorien vorhanden sein. Wenn Type zu Spaltenhierarchien hinzugefügt wird, kann er nicht auch in den Zeilen-, Daten- oder Filterhierarchien enthalten sein. Wenn Type anschließend Zeilenhierarchien hinzugefügt wird, wird er aus den Spaltenhierarchien entfernt. Dieses Verhalten ist identisch, unabhängig davon, ob die Hierarchiezuweisung über die Excel-Benutzeroberfläche oder die Excel-JavaScript-APIs erfolgt.

Zeilen- und Spaltenhierarchien definieren, wie Daten gruppiert werden. Beispielsweise gruppiert eine Zeilenhierarchie von Farmen alle Datasets aus derselben Farm. Die Auswahl zwischen Zeilen- und Spaltenhierarchie definiert die Ausrichtung der PivotTable.

Datenhierarchien sind die Werte, die basierend auf den Zeilen- und Spaltenhierarchien aggregiert werden sollen. Eine PivotTable mit einer Zeilenhierarchie von Farmen und einer Datenhierarchie von Crates Sold Wholesale zeigt die Summe (standardmäßig) aller verschiedenen Früchte für jeden Betrieb an.

Filterhierarchien schließen Daten basierend auf Werten innerhalb dieses gefilterten Typs ein oder aus dem Pivot aus. Eine Filterhierarchie der Klassifizierung , bei der der Typ Organisch ausgewählt ist, zeigt nur Daten für organisches Obst an.

Hier sind wieder die Farmdaten neben einer PivotTable aufgeführt. Die PivotTable verwendet Farm und Type als Zeilenhierarchien, Crates Sold at Farm und Crates Sold Wholesale als Datenhierarchien (mit der Standardaggregationsfunktion summe) und Klassifizierung als Filterhierarchie (mit ausgewählter Option Organ).

Diese PivotTable kann über die JavaScript-API oder über die Excel-Benutzeroberfläche generiert werden. Beide Optionen ermöglichen eine weitere Bearbeitung durch Add-Ins.

Erstellen einer PivotTable

PivotTables benötigen einen Namen, eine Quelle und ein Ziel. Die Quelle kann eine Bereichsadresse oder ein Tabellenname sein (übergeben als Range, stringoder Table Typ). Das Ziel ist eine Bereichsadresse (entweder als oder Rangestringangegeben). Die folgenden Beispiele zeigen verschiedene PivotTable-Erstellungstechniken.

Erstellen einer PivotTable mit Bereichsadressen

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on the current worksheet at cell
    // A22 with data from the range A1:E21.
    context.workbook.worksheets.getActiveWorksheet().pivotTables.add(
      "Farm Sales", "A1:E21", "A22");

    await context.sync();
});

Erstellen einer PivotTable mit Range-Objekten

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data comes from the worksheet "DataWorksheet" across the range A1:E21.
    let rangeToAnalyze = context.workbook.worksheets.getItem("DataWorksheet").getRange("A1:E21");
    let rangeToPlacePivot = context.workbook.worksheets.getItem("PivotWorksheet").getRange("A2");
    context.workbook.worksheets.getItem("PivotWorksheet").pivotTables.add(
      "Farm Sales", rangeToAnalyze, rangeToPlacePivot);

    await context.sync();
});

Erstellen einer PivotTable auf Arbeitsmappenebene

await Excel.run(async (context) => {
    // Create a PivotTable named "Farm Sales" on a worksheet called "PivotWorksheet" at cell A2
    // the data is from the worksheet "DataWorksheet" across the range A1:E21.
    context.workbook.pivotTables.add(
        "Farm Sales", "DataWorksheet!A1:E21", "PivotWorksheet!A2");

    await context.sync();
});

Verwenden einer vorhandenen PivotTable

Auf manuell erstellte PivotTables kann auch über die PivotTable-Auflistung der Arbeitsmappe oder einzelner Arbeitsblätter zugegriffen werden. Der folgende Code ruft eine PivotTable mit dem Namen My Pivot aus der Arbeitsmappe ab.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("My Pivot");
    await context.sync();
});

Hinzufügen von Zeilen und Spalten zu einer PivotTable

Zeilen und Spalten pivotieren die Daten um die Werte dieser Felder.

Wenn Sie die Spalte Farm hinzufügen, werden alle Umsätze um die einzelnen Farmen pivotiert. Wenn Sie die Zeilen Typ und Klassifizierung hinzufügen, werden die Daten basierend darauf, welche Frucht verkauft wurde und ob sie organisch war oder nicht, weiter unterteilt.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Farm"));

    await context.sync();
});

Sie können auch eine PivotTable mit nur Zeilen oder Spalten verwenden.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Classification"));

    await context.sync();
});

Hinzufügen von Datenhierarchien zur PivotTable

Datenhierarchien füllen die PivotTable mit Informationen, die basierend auf den Zeilen und Spalten kombiniert werden sollen. Das Hinzufügen der Datenhierarchien von Crates Sold at Farm und Crates Sold Wholesale ergibt Summen dieser Zahlen für jede Zeile und Spalte.

Im Beispiel sind sowohl Farm als auch Type Zeilen, wobei die Crate sales als Daten verwendet werden.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // "Farm" and "Type" are the hierarchies on which the aggregation is based.
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Type"));

    // "Crates Sold at Farm" and "Crates Sold Wholesale" are the hierarchies
    // that will have their data aggregated (summed in this case).
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

    await context.sync();
});

PivotTable-Layouts und Abrufen von pivotierten Daten

Ein PivotLayout definiert die Platzierung von Hierarchien und deren Daten. Sie greifen auf das Layout zu, um die Bereiche zu bestimmen, in denen Daten gespeichert werden.

Das folgende Diagramm zeigt, welche Layoutfunktionsaufrufe welchen Bereichen der PivotTable entsprechen.

Abrufen von Daten aus der PivotTable

Das Layout definiert, wie die PivotTable im Arbeitsblatt angezeigt wird. Dies bedeutet, dass das PivotLayout -Objekt die Bereiche steuert, die für PivotTable-Elemente verwendet werden. Verwenden Sie die vom Layout bereitgestellten Bereiche, um Daten abzurufen, die von der PivotTable gesammelt und aggregiert werden. Verwenden Sie PivotLayout.getDataBodyRange insbesondere , um auf die von der PivotTable erzeugten Daten zuzugreifen.

Der folgende Code veranschaulicht, wie sie die letzte Zeile der PivotTable-Daten abrufen, indem Sie das Layout durchlaufen (die Gesamtsumme der Spalten Summe der in der Farm verkauften Kisten und Summe der verkauften Kisten im vorherigen Beispiel). Diese Werte werden dann zu einem Endergebnis zusammengefasst, das in Zelle E30 (außerhalb der PivotTable) angezeigt wird.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");

    // Get the totals for each data hierarchy from the layout.
    let range = pivotTable.layout.getDataBodyRange();
    let grandTotalRange = range.getLastRow();
    grandTotalRange.load("address");
    await context.sync();

    // Sum the totals from the PivotTable data hierarchies and place them in a new range, outside of the PivotTable.
    let masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("E30");
    masterTotalRange.formulas = [["=SUM(" + grandTotalRange.address + ")"]];
    await context.sync();
});

Layouttypen

PivotTables weisen drei Layoutstile auf: Kompakt, Gliederung und Tabellarisch. Wir haben den kompakten Stil in den vorherigen Beispielen gesehen.

In den folgenden Beispielen werden die Gliederungs- bzw. tabellarischen Formatvorlagen verwendet. Das Codebeispiel zeigt, wie zwischen den verschiedenen Layouts gezyklust wird.

Gliederungslayout

Tabellarisches Layout

Codebeispiel für PivotLayout-Typwechsel

await Excel.run(async (context) => {
    // Change the PivotLayout.type to a new type.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.layout.load("layoutType");
    await context.sync();

    // Cycle between the three layout types.
    if (pivotTable.layout.layoutType === "Compact") {
        pivotTable.layout.layoutType = "Outline";
    } else if (pivotTable.layout.layoutType === "Outline") {
        pivotTable.layout.layoutType = "Tabular";
    } else {
        pivotTable.layout.layoutType = "Compact";
    }

    await context.sync();
});

Andere PivotLayout-Funktionen

Standardmäßig passen PivotTables die Zeilen- und Spaltengrößen nach Bedarf an. Dies erfolgt, wenn die PivotTable aktualisiert wird. PivotLayout.autoFormat gibt dieses Verhalten an. Alle Zeilen- oder Spaltengrößenänderungen, die vom Add-In vorgenommen werden, bleiben erhalten, wenn autoFormat ist false. Darüber hinaus behalten die Standardeinstellungen einer PivotTable alle benutzerdefinierten Formatierungen in der PivotTable bei (z. B. Füllungen und Schriftartänderungen). Legen Sie auf fest PivotLayout.preserveFormattingfalse , um das Standardformat bei der Aktualisierung anzuwenden.

Ein PivotLayout steuert auch Die Einstellungen für Kopfzeilen und Zeilensumme, die Anzeige leerer Datenzellen und alternativtextoptionen . Die PivotLayout-Referenz enthält eine vollständige Liste dieser Features.

Im folgenden Codebeispiel wird in leeren Datenzellen die Zeichenfolge "--"angezeigt, der Textbereich wird in eine konsistente horizontale Ausrichtung formatiert und sichergestellt, dass die Formatierungsänderungen auch nach dem Aktualisieren der PivotTable verbleiben.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    let pivotLayout = pivotTable.layout;

    // Set a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
    pivotLayout.emptyCellText = "--";

    // Set the text alignment to match the rest of the PivotTable.
    pivotLayout.getDataBodyRange().format.horizontalAlignment = Excel.HorizontalAlignment.right;

    // Ensure empty cells are filled with a default value.
    pivotLayout.fillEmptyCells = true;

    // Ensure that the format settings persist, even after the PivotTable is refreshed and recalculated.
    pivotLayout.preserveFormatting = true;
    await context.sync();
});

Löschen einer PivotTable

PivotTables werden mithilfe ihres Namens gelöscht.

await Excel.run(async (context) => {
    context.workbook.worksheets.getItem("Pivot").pivotTables.getItem("Farm Sales").delete();
    await context.sync();
});

Filtern einer PivotTable

Die primäre Methode zum Filtern von PivotTable-Daten ist PivotFilter. Slicer bieten eine alternative, weniger flexible Filtermethode.

PivotFilter filtert Daten basierend auf den vier Hierarchiekategorien einer PivotTable (Filter, Spalten, Zeilen und Werte). Es gibt vier Arten von PivotFiltern, die kalenderdatumsbasierte Filterung, Zeichenfolgenanalyse, Zahlenvergleich und Filterung basierend auf einer benutzerdefinierten Eingabe ermöglichen.

Slicer können sowohl auf PivotTables als auch auf reguläre Excel-Tabellen angewendet werden. Wenn sie auf eine PivotTable angewendet werden, funktionieren Slicer wie ein PivotManualFilter und ermöglichen die Filterung basierend auf einer benutzerdefinierten Eingabe. Im Gegensatz zu PivotFiltern verfügen Slicer über eine Excel-Ui-Komponente. Mit der Slicer -Klasse erstellen Sie diese UI-Komponente, verwalten die Filterung und steuern die visuelle Darstellung.

Filtern mit PivotFiltern

Mit PivotFilters können Sie PivotTable-Daten basierend auf den vier Hierarchiekategorien (Filter, Spalten, Zeilen und Werte) filtern. Im PivotTable-Objektmodell PivotFilters werden auf ein PivotField angewendet, und jedem PivotField kann mindestens ein zugewiesenes zugewiesen PivotFilterswerden. Um PivotFilter auf ein PivotField anzuwenden, muss die entsprechende PivotHierarchy des Felds einer Hierarchiekategorie zugewiesen werden.

Typen von PivotFiltern

Filtertyp	Filterzweck	JavaScript-API-Referenz für Excel
DateFilter	Kalenderdatumsbasierte Filterung.	PivotDateFilter
LabelFilter	Textvergleichsfilterung.	PivotLabelFilter
ManualFilter	Benutzerdefinierte Eingabefilterung.	PivotManualFilter
ValueFilter	Zahlenvergleichsfilterung.	PivotValueFilter

Erstellen eines PivotFilters

Um PivotTable-Daten mit einem Pivot*Filter (z. B. einem PivotDateFilter) zu filtern, wenden Sie den Filter auf ein PivotField an. Die folgenden vier Codebeispiele zeigen, wie jeder der vier PivotFilter-Typen verwendet wird.

PivotDateFilter

Im ersten Codebeispiel wird ein PivotDateFilter auf das PivotField "Datum aktualisiert " angewendet, wobei alle Daten vor dem 01.08.2020 ausgeblendet werden.

Wichtig

Ein Pivot*Filter kann nicht auf ein PivotField angewendet werden, es sei denn, die PivotHierarchy dieses Felds ist einer Hierarchiekategorie zugewiesen. Im folgenden Codebeispiel muss der dateHierarchy Kategorie der PivotTable rowHierarchies hinzugefügt werden, bevor sie zum Filtern verwendet werden kann.

await Excel.run(async (context) => {
    // Get the PivotTable and the date hierarchy.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let dateHierarchy = pivotTable.rowHierarchies.getItemOrNullObject("Date Updated");
    await context.sync();

    // PivotFilters can only be applied to PivotHierarchies that are being used for pivoting.
    // If it's not already there, add "Date Updated" to the hierarchies.
    if (dateHierarchy.isNullObject) {
        dateHierarchy = pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Date Updated"));
    }

    // Apply a date filter to filter out anything logged before August.
    let filterField = dateHierarchy.fields.getItem("Date Updated");
    let dateFilter = {
        condition: Excel.DateFilterCondition.afterOrEqualTo,
        comparator: {
        date: "2020-08-01",
        specificity: Excel.FilterDatetimeSpecificity.month
        }
    };
    filterField.applyFilter({ dateFilter: dateFilter });
    
    await context.sync();
});

Hinweis

Die folgenden drei Codeausschnitte zeigen nur filterspezifische Auszüge anstelle vollständiger Excel.run Aufrufe an.

PivotLabelFilter

Der zweite Codeausschnitt veranschaulicht, wie ein PivotLabelFilter auf das Type PivotField angewendet wird, wobei die LabelFilterCondition.beginsWith -Eigenschaft verwendet wird, um Bezeichnungen auszuschließen, die mit dem Buchstaben L beginnen.

    // Get the "Type" field.
    let filterField = pivotTable.hierarchies.getItem("Type").fields.getItem("Type");

    // Filter out any types that start with "L" ("Lemons" and "Limes" in this case).
    let filter: Excel.PivotLabelFilter = {
      condition: Excel.LabelFilterCondition.beginsWith,
      substring: "L",
      exclusive: true
    };

    // Apply the label filter to the field.
    filterField.applyFilter({ labelFilter: filter });

PivotManualFilter

Der dritte Codeausschnitt wendet einen manuellen Filter mit PivotManualFilter auf das Feld Klassifizierung an und filtert Daten aus, die die Klassifizierung Organic nicht enthalten.

    // Apply a manual filter to include only a specific PivotItem (the string "Organic").
    let filterField = classHierarchy.fields.getItem("Classification");
    let manualFilter = { selectedItems: ["Organic"] };
    filterField.applyFilter({ manualFilter: manualFilter });

PivotValueFilter

Verwenden Sie zum Vergleichen von Zahlen einen Wertfilter mit PivotValueFilter, wie im endgültigen Codeausschnitt gezeigt. Vergleicht PivotValueFilter die Daten im PivotField farm mit den Daten im PivotField Crates Sold Wholesale PivotField, einschließlich nur Farmen, deren Summe der verkauften Kisten den Wert 500 überschreitet.

    // Get the "Farm" field.
    let filterField = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
    
    // Filter to only include rows with more than 500 wholesale crates sold.
    let filter: Excel.PivotValueFilter = {
      condition: Excel.ValueFilterCondition.greaterThan,
      comparator: 500,
      value: "Sum of Crates Sold Wholesale"
    };
    
    // Apply the value filter to the field.
    filterField.applyFilter({ valueFilter: filter });

Entfernen von PivotFiltern

Um alle PivotFilter zu entfernen, wenden Sie die clearAllFilters -Methode auf jedes PivotField an, wie im folgenden Codebeispiel gezeigt.

await Excel.run(async (context) => {
    // Get the PivotTable.
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.hierarchies.load("name");
    await context.sync();

    // Clear the filters on each PivotField.
    pivotTable.hierarchies.items.forEach(function (hierarchy) {
        hierarchy.fields.getItem(hierarchy.name).clearAllFilters();
    });
    await context.sync();
});

Filtern mit Datenschnitten

Datenschnitte ermöglichen das Filtern von Daten aus einer Excel-PivotTable oder -Tabelle. Ein Datenschnitt verwendet Werte aus einer angegebenen Spalte oder PivotField, um entsprechende Zeilen zu filtern. Diese Werte werden als SlicerItem-Objekte im Slicergespeichert. Ihr Add-In kann diese Filter anpassen, ebenso wie Benutzer (über die Excel-Benutzeroberfläche). Der Slicer befindet sich auf dem Arbeitsblatt in der Zeichnungsebene, wie im folgenden Screenshot gezeigt.

Hinweis

Die in diesem Abschnitt beschriebenen Techniken konzentrieren sich auf die Verwendung von Slicern, die mit PivotTables verbunden sind. Die gleichen Techniken gelten auch für die Verwendung von Slicern, die mit Tabellen verbunden sind.

Erstellen eines Datenschnitts

Sie können einen Datenschnitt in einer Arbeitsmappe oder einem Arbeitsblatt erstellen, indem Sie die -Methode oder Worksheet.slicers.add -Workbook.slicers.addMethode verwenden. Dadurch wird der SlicerCollection des angegebenen Workbook - oder Worksheet -Objekts ein Slicer hinzugefügt. Die SlicerCollection.add -Methode verfügt über drei Parameter:

• slicerSource: Die Datenquelle, auf der der neue Slicer basiert. Dabei kann es sich um eine PivotTable- , Table- oder -Zeichenfolge handeln, die den Namen oder die ID eines oder TabledarstelltPivotTable.
• sourceField: Das Feld in der Datenquelle, nach dem gefiltert werden soll. Dabei kann es sich um eine PivotField- , TableColumn- oder -Zeichenfolge handeln, die den Namen oder die ID eines oder TableColumndarstelltPivotField.
• slicerDestination: Das Arbeitsblatt, in dem der neue Slicer erstellt wird. Dabei kann es sich um ein Worksheet -Objekt oder den Namen oder die ID eines handeln Worksheet. Dieser Parameter ist nicht erforderlich, wenn SlicerCollection über Worksheet.slicersauf zugegriffen wird. In diesem Fall wird das Arbeitsblatt der Sammlung als Ziel verwendet.

Im folgenden Codebeispiel wird dem Pivot-Arbeitsblatt ein neuer Slicer hinzugefügt. Die Quelle des Datenschnitts ist die PivotTable "Farm Sales" und filtert mithilfe der Typdaten . Der Slicer wird zur späteren Referenz auch als Fruchtslicer bezeichnet.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Pivot");
    let slicer = sheet.slicers.add(
        "Farm Sales" /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */,
        "Type" /* The field in the data to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    );
    slicer.name = "Fruit Slicer";
    await context.sync();
});

Filtern von Elementen mit einem Slicer

Der Slicer filtert die PivotTable mit Elementen aus .sourceField Die Slicer.selectItems -Methode legt die Elemente fest, die im Slicer verbleiben. Diese Elemente werden an die -Methode als string[]übergeben, das die Schlüssel der Elemente darstellt. Alle Zeilen, die diese Elemente enthalten, verbleiben in der Aggregation der PivotTable. Nachfolgende Aufrufe, um selectItems die Liste auf die in diesen Aufrufen angegebenen Schlüssel festzulegen.

Hinweis

Wenn Slicer.selectItems ein Element übergeben wird, das sich nicht in der Datenquelle befindet, wird ein InvalidArgument Fehler ausgelöst. Der Inhalt kann über die -Eigenschaft überprüft werden, bei der Slicer.slicerItems es sich um eine SlicerItemCollection handelt.

Das folgende Codebeispiel zeigt drei Elemente, die für den Slicer ausgewählt werden: Zitrone, Limette und Orange.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    // Anything other than the following three values will be filtered out of the PivotTable for display and aggregation.
    slicer.selectItems(["Lemon", "Lime", "Orange"]);
    await context.sync();
});

Um alle Filter aus dem Slicer zu entfernen, verwenden Sie die Slicer.clearFilters -Methode, wie im folgenden Beispiel gezeigt.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.clearFilters();
    await context.sync();
});

Formatieren und Formatieren eines Datenschnitts

Das Add-In kann die Anzeigeeinstellungen eines Slicers über Slicer Eigenschaften anpassen. Im folgenden Codebeispiel wird die Formatvorlage auf SlicerStyleLight6 festgelegt, der Text am oberen Rand des Datenschnitts auf Fruchttypen festgelegt, der Slicer an der Position (395, 15) auf der Zeichnungsebene und die Größe des Datenschnitts auf 135 x 150 Pixel festgelegt.

await Excel.run(async (context) => {
    let slicer = context.workbook.slicers.getItem("Fruit Slicer");
    slicer.caption = "Fruit Types";
    slicer.left = 395;
    slicer.top = 15;
    slicer.height = 135;
    slicer.width = 150;
    slicer.style = "SlicerStyleLight6";
    await context.sync();
});

Löschen eines Slicers

Um einen Datenschnitt zu löschen, rufen Sie die -Methode auf Slicer.delete . Im folgenden Codebeispiel wird der erste Slicer aus dem aktuellen Arbeitsblatt gelöscht.

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.slicers.getItemAt(0).delete();
    await context.sync();
});

Ändern der Aggregationsfunktion

Datenhierarchien verfügen über aggregierte Werte. Bei Datasets mit Zahlen ist dies standardmäßig eine Summe. Die summarizeBy -Eigenschaft definiert dieses Verhalten basierend auf einem AggregationFunction-Typ .

Die derzeit unterstützten Aggregationsfunktionstypen sind Sum, Count, , AverageMaxMin, Product, , CountNumbersStandardDeviation, VarianceStandardDeviationPVariancePund Automatic (Standard).

In den folgenden Codebeispielen wird die Aggregation in Durchschnittswerte der Daten geändert.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Change the aggregation from the default sum to an average of all the values in the hierarchy.
    pivotTable.dataHierarchies.items[0].summarizeBy = Excel.AggregationFunction.average;
    pivotTable.dataHierarchies.items[1].summarizeBy = Excel.AggregationFunction.average;
    await context.sync();
});

Ändern von Berechnungen mit einer ShowAsRule

PivotTables aggregieren standardmäßig die Daten ihrer Zeilen- und Spaltenhierarchien unabhängig voneinander. Eine ShowAsRule ändert die Datenhierarchie in Ausgabewerte basierend auf anderen Elementen in der PivotTable.

Das ShowAsRule -Objekt verfügt über drei Eigenschaften:

• calculation: Der Typ der relativen Berechnung, die auf die Datenhierarchie angewendet werden soll (der Standardwert ist none).
• baseField: Das PivotField in der Hierarchie, das die Basisdaten enthält, bevor die Berechnung angewendet wird. Da Excel-PivotTables eine 1:1-Zuordnung von Hierarchie zu Feld aufweisen, verwenden Sie denselben Namen, um sowohl auf die Hierarchie als auch auf das Feld zuzugreifen.
• baseItem: Das einzelne PivotItem-Objekt , das anhand des Berechnungstyps mit den Werten der Basisfelder verglichen wird. Dieses Feld ist nicht für alle Berechnungen erforderlich.

Im folgenden Beispiel wird die Berechnung für die Datenhierarchie Summe der verkauften Crates in farm auf einen Prozentsatz der Spaltensumme festgelegt. Wir möchten weiterhin, dass die Granularität auf die Fruchttypebene erweitert wird, daher verwenden wir die Typzeilenhierarchie und das zugrunde liegende Feld. Das Beispiel enthält auch Farm als erste Zeilenhierarchie, sodass in den Einträgen für die Farmsumme auch der Prozentsatz angezeigt wird, für den jede Farm für die Produktion verantwortlich ist.

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();

    // Show the crates of each fruit type sold at the farm as a percentage of the column's total.
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.percentOfColumnTotal;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Type").fields.getItem("Type");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Percentage of Total Farm Sales";
});

Im vorherigen Beispiel wurde die Berechnung auf die Spalte relativ zum Feld einer einzelnen Zeilenhierarchie festgelegt. Wenn sich die Berechnung auf ein einzelnes Element bezieht, verwenden Sie die baseItem -Eigenschaft.

Das folgende Beispiel zeigt die differenceFrom Berechnung. Es zeigt die Differenz der Hierarchieeinträge der Verkaufsdaten der Farm crate im Verhältnis zu den Einträgen von A Farms an. Der baseField ist Farm, daher sehen wir die Unterschiede zwischen den anderen Farmen sowie Aufschlüsselungen für jeden Typ von ähnlichen Früchten (Typ ist auch eine Zeilenhierarchie in diesem Beispiel).

await Excel.run(async (context) => {
    let pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    let farmDataHierarchy = pivotTable.dataHierarchies.getItem("Sum of Crates Sold at Farm");

    farmDataHierarchy.load("showAs");
    await context.sync();
        
    // Show the difference between crate sales of the "A Farms" and the other farms.
    // This difference is both aggregated and shown for individual fruit types (where applicable).
    let farmShowAs = farmDataHierarchy.showAs;
    farmShowAs.calculation = Excel.ShowAsCalculation.differenceFrom;
    farmShowAs.baseField = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm");
    farmShowAs.baseItem = pivotTable.rowHierarchies.getItem("Farm").fields.getItem("Farm").items.getItem("A Farms");
    farmDataHierarchy.showAs = farmShowAs;
    farmDataHierarchy.name = "Difference from A Farms";
});

Ändern von Hierarchienamen

Hierarchiefelder können bearbeitet werden. Der folgende Code veranschaulicht, wie die angezeigten Namen von zwei Datenhierarchien geändert werden.

await Excel.run(async (context) => {
    let dataHierarchies = context.workbook.worksheets.getActiveWorksheet()
        .pivotTables.getItem("Farm Sales").dataHierarchies;
    dataHierarchies.load("no-properties-needed");
    await context.sync();

    // Changing the displayed names of these entries.
    dataHierarchies.items[0].name = "Farm Sales";
    dataHierarchies.items[1].name = "Wholesale";
});

Siehe auch

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