Excel.PivotTable class

Represents an Excel PivotTable. To learn more about the PivotTable object model, read Work with PivotTables using the Excel JavaScript API.

Extends

Remarks

[ API set: ExcelApi 1.3 ]

Properties

allowMultipleFiltersPerField

Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table.

columnHierarchies

The Column Pivot Hierarchies of the PivotTable.

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

dataHierarchies

The Data Pivot Hierarchies of the PivotTable.

enableDataValueEditing

Specifies if the PivotTable allows values in the data body to be edited by the user.

filterHierarchies

The Filter Pivot Hierarchies of the PivotTable.

hierarchies

The Pivot Hierarchies of the PivotTable.

id

ID of the PivotTable.

layout

The PivotLayout describing the layout and visual structure of the PivotTable.

name

Name of the PivotTable.

refreshOnOpen

Specifies whether the PivotTable refreshes when the workbook opens. Corresponds to "Refresh on load" setting in the UI.

rowHierarchies

The Row Pivot Hierarchies of the PivotTable.

useCustomSortLists

Specifies if the PivotTable uses custom lists when sorting.

worksheet

The worksheet containing the current PivotTable.

Methods

delete()

Deletes the PivotTable.

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

refresh()

Refreshes the PivotTable.

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.PivotTable object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.PivotTableData) that contains shallow copies of any loaded child properties from the original object.

Property Details

allowMultipleFiltersPerField

Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table.

allowMultipleFiltersPerField: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.12 ]

columnHierarchies

The Column Pivot Hierarchies of the PivotTable.

readonly columnHierarchies: Excel.RowColumnPivotHierarchyCollection;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-create-and-modify.yaml

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

    // Check if the PivotTable already has a column.
    const column = pivotTable.columnHierarchies.getItemOrNullObject("Farm");
    column.load("id");
    await context.sync();

    if (column.isNullObject) {
        // Adding the farm column to the column hierarchy automatically removes it from the row hierarchy.
        pivotTable.columnHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
    } else {
        pivotTable.columnHierarchies.remove(column);
    }

    await context.sync();
});

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

dataHierarchies

The Data Pivot Hierarchies of the PivotTable.

readonly dataHierarchies: Excel.DataPivotHierarchyCollection;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-create-and-modify.yaml

await Excel.run(async (context) => {
    const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
    pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

    await context.sync();
});

enableDataValueEditing

Specifies if the PivotTable allows values in the data body to be edited by the user.

enableDataValueEditing: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

filterHierarchies

The Filter Pivot Hierarchies of the PivotTable.

readonly filterHierarchies: Excel.FilterPivotHierarchyCollection;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-filters-and-summaries.yaml

async function filter(functionType: Excel.AggregationFunction) {
    await Excel.run(async (context) => {
        const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
        const filters = pivotTable.filterHierarchies;
        const filter = filters.getItemOrNullObject("Classification");
        filter.load();
        await context.sync();

        // Add the Classification hierarchy to the filter, if it's not already there.
        if (filter.isNullObject) {
            filters.add(pivotTable.hierarchies.getItem("Classification"));
            await context.sync();
        }
    });
}

hierarchies

The Pivot Hierarchies of the PivotTable.

readonly hierarchies: Excel.PivotHierarchyCollection;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

id

ID of the PivotTable.

readonly id: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.5 ]

layout

The PivotLayout describing the layout and visual structure of the PivotTable.

readonly layout: Excel.PivotLayout;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-create-and-modify.yaml

await Excel.run(async (context) => {
    const 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();
    console.log("Pivot layout is now " + pivotTable.layout.layoutType);
});

name

Name of the PivotTable.

name: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.3 ]

refreshOnOpen

Specifies whether the PivotTable refreshes when the workbook opens. Corresponds to "Refresh on load" setting in the UI.

refreshOnOpen: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.13 ]

rowHierarchies

The Row Pivot Hierarchies of the PivotTable.

readonly rowHierarchies: Excel.RowColumnPivotHierarchyCollection;

Property Value

Remarks

[ API set: ExcelApi 1.8 ]

useCustomSortLists

Specifies if the PivotTable uses custom lists when sorting.

useCustomSortLists: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

worksheet

The worksheet containing the current PivotTable.

readonly worksheet: Excel.Worksheet;

Property Value

Remarks

[ API set: ExcelApi 1.3 ]

Method Details

delete()

Deletes the PivotTable.

delete(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-create-and-modify.yaml

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

    await context.sync();
});

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(options?: Excel.Interfaces.PivotTableLoadOptions): Excel.PivotTable;

Parameters

options
Excel.Interfaces.PivotTableLoadOptions

Provides options for which properties of the object to load.

Returns

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.PivotTable;

Parameters

propertyNames

string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.PivotTable;

Parameters

propertyNamesAndPaths

{ select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

refresh()

Refreshes the PivotTable.

refresh(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.3 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-refresh.yaml

// This function refreshes the "Farm Sales" PivotTable,
// which updates the PivotTable with changes made to the source table.
await Excel.run(async (context) => {
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  pivotTable.refresh();
  await context.sync();
});

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.PivotTableUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties
Excel.Interfaces.PivotTableUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options
OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.PivotTable): void;

Parameters

properties
Excel.PivotTable

Returns

void

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.PivotTable object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.PivotTableData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.PivotTableData;

Returns