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
Properties
allow |
Specifies if the PivotTable allows the application of multiple PivotFilters on a given PivotField in the table. |
column |
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. |
data |
The Data Pivot Hierarchies of the PivotTable. |
enable |
Specifies if the PivotTable allows values in the data body to be edited by the user. |
filter |
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. |
refresh |
Specifies whether the PivotTable refreshes when the workbook opens. Corresponds to "Refresh on load" setting in the UI. |
row |
The Row Pivot Hierarchies of the PivotTable. |
use |
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 |
load(property |
Queues up a command to load the specified properties of the object. You must call |
load(property |
Queues up a command to load the specified properties of the object. You must call |
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 |
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
columnHierarchies
The Column Pivot Hierarchies of the PivotTable.
readonly columnHierarchies: Excel.RowColumnPivotHierarchyCollection;
Property Value
Remarks
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
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
filterHierarchies
The Filter Pivot Hierarchies of the PivotTable.
readonly filterHierarchies: Excel.FilterPivotHierarchyCollection;
Property Value
Remarks
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
id
layout
The PivotLayout describing the layout and visual structure of the PivotTable.
readonly layout: Excel.PivotLayout;
Property Value
Remarks
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
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
rowHierarchies
The Row Pivot Hierarchies of the PivotTable.
readonly rowHierarchies: Excel.RowColumnPivotHierarchyCollection;
Property Value
Remarks
useCustomSortLists
Specifies if the PivotTable uses custom lists when sorting.
useCustomSortLists: boolean;
Property Value
boolean
Remarks
worksheet
The worksheet containing the current PivotTable.
readonly worksheet: Excel.Worksheet;
Property Value
Remarks
Method Details
delete()
Deletes the PivotTable.
delete(): void;
Returns
void
Remarks
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
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
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
Office Add-ins