Excel.PivotLayout class

Reference

Package:: excel

Represents the visual layout of the PivotTable.

Extends: OfficeExtension.ClientObject

Remarks

Properties

autoFormat	Specifies if formatting will be automatically formatted when it's refreshed or when fields are moved.
context	The request context associated with the object. This connects the add-in's process to the Office host application's process.
enableFieldList	Specifies if the field list can be shown in the UI.
layoutType	This property indicates the PivotLayoutType of all fields on the PivotTable. If fields have different states, this will be null.
preserveFormatting	Specifies if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.
showColumnGrandTotals	Specifies if the PivotTable report shows grand totals for columns.
showRowGrandTotals	Specifies if the PivotTable report shows grand totals for rows.
subtotalLocation	This property indicates the `SubtotalLocationType` of all fields on the PivotTable. If fields have different states, this will be `null`.

Methods

getColumnLabelRange()	Returns the range where the PivotTable's column labels reside.
getDataBodyRange()	Returns the range where the PivotTable's data values reside.
getDataHierarchy(cell)	Gets the DataHierarchy that is used to calculate the value in a specified range within the PivotTable.
getFilterAxisRange()	Returns the range of the PivotTable's filter area.
getPivotItems(axis, cell)	Gets the PivotItems from an axis that make up the value in a specified range within the PivotTable.
getPivotItems(axisString, cell)	Gets the PivotItems from an axis that make up the value in a specified range within the PivotTable.
getRange()	Returns the range the PivotTable exists on, excluding the filter area.
getRowLabelRange()	Returns the range where the PivotTable's row labels reside.
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.
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.
setAutoSortOnCell(cell, sortBy)	Sets the PivotTable to automatically sort using the specified cell to automatically select all necessary criteria and context. This behaves identically to applying an autosort from the UI.
setAutoSortOnCell(cell, sortByString)	Sets the PivotTable to automatically sort using the specified cell to automatically select all necessary criteria and context. This behaves identically to applying an autosort from the UI.
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.PivotLayout` object is an API object, the `toJSON` method returns a plain JavaScript object (typed as `Excel.Interfaces.PivotLayoutData`) that contains shallow copies of any loaded child properties from the original object.

Property Details

autoFormat

Specifies if formatting will be automatically formatted when it's refreshed or when fields are moved.

autoFormat: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

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

Excel.RequestContext

enableFieldList

Specifies if the field list can be shown in the UI.

enableFieldList: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.10 ]

layoutType

This property indicates the PivotLayoutType of all fields on the PivotTable. If fields have different states, this will be null.

layoutType: Excel.PivotLayoutType | "Compact" | "Tabular" | "Outline";

Property Value

Excel.PivotLayoutType | "Compact" | "Tabular" | "Outline"

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-pivotlayout.yaml

await Excel.run(async (context) => {
  // Change the PivotLayout.type to a new type.
  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);
});

preserveFormatting

Specifies if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.

preserveFormatting: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

Examples

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

await Excel.run(async (context) => {
  // Set whether the PivotTable keeps the established format after it is refreshed and recalculated.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.load("preserveFormatting");
  await context.sync();

  let preserveFormattingToSet = !pivotLayout.preserveFormatting;
  console.log(`Preserve the formatting PivotTable after a refresh? - ${preserveFormattingToSet}`);

  pivotLayout.preserveFormatting = preserveFormattingToSet;
  await context.sync();
});

showColumnGrandTotals

Specifies if the PivotTable report shows grand totals for columns.

showColumnGrandTotals: boolean;

Property Value

boolean

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-pivotlayout.yaml

await Excel.run(async (context) => {
  // Turn the grand totals on and off for the rows and columns.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.load(["showRowGrandTotals", "showColumnGrandTotals"]);
  await context.sync();

  let showColumnTotals = !pivotLayout.showColumnGrandTotals;
  let showRowTotals = !pivotLayout.showRowGrandTotals;
  console.log(`Show column grand totals? - ${showColumnTotals}`);
  console.log(`Show row grand totals? - ${showRowTotals}`);

  pivotLayout.showColumnGrandTotals = showColumnTotals;
  pivotLayout.showRowGrandTotals = showRowTotals;

  await context.sync();
});

showRowGrandTotals

Specifies if the PivotTable report shows grand totals for rows.

showRowGrandTotals: boolean;

Property Value

boolean

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-pivotlayout.yaml

await Excel.run(async (context) => {
  // Turn the grand totals on and off for the rows and columns.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.load(["showRowGrandTotals", "showColumnGrandTotals"]);
  await context.sync();

  let showColumnTotals = !pivotLayout.showColumnGrandTotals;
  let showRowTotals = !pivotLayout.showRowGrandTotals;
  console.log(`Show column grand totals? - ${showColumnTotals}`);
  console.log(`Show row grand totals? - ${showRowTotals}`);

  pivotLayout.showColumnGrandTotals = showColumnTotals;
  pivotLayout.showRowGrandTotals = showRowTotals;

  await context.sync();
});

subtotalLocation

This property indicates the SubtotalLocationType of all fields on the PivotTable. If fields have different states, this will be null.

subtotalLocation: Excel.SubtotalLocationType | "AtTop" | "AtBottom" | "Off";

Property Value

Excel.SubtotalLocationType | "AtTop" | "AtBottom" | "Off"

Remarks

[ API set: ExcelApi 1.8 ]

Method Details

getColumnLabelRange()

Returns the range where the PivotTable's column labels reside.

getColumnLabelRange(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.8 ]

getDataBodyRange()

Returns the range where the PivotTable's data values reside.

getDataBodyRange(): Excel.Range;

Returns

Excel.Range

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

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

    // The layout controls the ranges used by the PivotTable.
    const range = pivotTable.layout.getDataBodyRange();

    // Get all the data hierarchy totals.
    const grandTotalRange = range.getLastRow();
    grandTotalRange.load("address");
    await context.sync();
    
    // Use the wholesale and farm sale totals to make a final sum.
    const masterTotalRange = context.workbook.worksheets.getActiveWorksheet().getRange("B27:C27");
    masterTotalRange.formulas = [["All Crates", "=SUM(" + grandTotalRange.address + ")"]];
    await context.sync();
});

getDataHierarchy(cell)

Gets the DataHierarchy that is used to calculate the value in a specified range within the PivotTable.

getDataHierarchy(cell: Range | string): Excel.DataPivotHierarchy;

Parameters

cell: Excel.Range | string

A single cell within the PivotTable data body.

Returns

Excel.DataPivotHierarchy

The DataPivotHierarchy object used to calculate the value in the specified cell.

Remarks

[ API set: ExcelApi 1.9 ]

getFilterAxisRange()

Returns the range of the PivotTable's filter area.

getFilterAxisRange(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.8 ]

getPivotItems(axis, cell)

Gets the PivotItems from an axis that make up the value in a specified range within the PivotTable.

getPivotItems(axis: Excel.PivotAxis, cell: Range | string): Excel.PivotItemCollection;

Parameters

axis: Excel.PivotAxis

The axis from which to get the PivotItems. Must be either "row" or "column."

cell: Excel.Range | string

A single cell within the PivotTable's data body.

Returns

Excel.PivotItemCollection

A PivotItemCollection of the PivotItems that are used to calculate the values in the specified row.

Remarks

[ API set: ExcelApi 1.9 ]

getPivotItems(axisString, cell)

Gets the PivotItems from an axis that make up the value in a specified range within the PivotTable.

getPivotItems(axisString: "Unknown" | "Row" | "Column" | "Data" | "Filter", cell: Range | string): Excel.PivotItemCollection;

Parameters

axisString: "Unknown" | "Row" | "Column" | "Data" | "Filter"

The axis from which to get the PivotItems. Must be either "row" or "column."

cell: Excel.Range | string

A single cell within the PivotTable's data body.

Returns

Excel.PivotItemCollection

A PivotItemCollection of the PivotItems that are used to calculate the values in the specified row.

Remarks

[ API set: ExcelApi 1.9 ]

getRange()

Returns the range the PivotTable exists on, excluding the filter area.

getRange(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.8 ]

getRowLabelRange()

Returns the range where the PivotTable's row labels reside.

getRowLabelRange(): Excel.Range;

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.8 ]

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.PivotLayoutLoadOptions): Excel.PivotLayout;

Parameters

options: Excel.Interfaces.PivotLayoutLoadOptions

Provides options for which properties of the object to load.

Returns

Excel.PivotLayout

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.PivotLayout;

Parameters

propertyNames: string | string[]

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

Returns

Excel.PivotLayout

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.PivotLayout;

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

Excel.PivotLayout

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.PivotLayoutUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties: Excel.Interfaces.PivotLayoutUpdateData

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.PivotLayout): void;

Parameters

properties: Excel.PivotLayout

Returns

void

setAutoSortOnCell(cell, sortBy)

Sets the PivotTable to automatically sort using the specified cell to automatically select all necessary criteria and context. This behaves identically to applying an autosort from the UI.

setAutoSortOnCell(cell: Range | string, sortBy: Excel.SortBy): void;

Parameters

cell: Excel.Range | string

A single cell to use get the criteria from for applying the autosort.

sortBy: Excel.SortBy

The direction of the sort.

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

setAutoSortOnCell(cell, sortByString)

Sets the PivotTable to automatically sort using the specified cell to automatically select all necessary criteria and context. This behaves identically to applying an autosort from the UI.

setAutoSortOnCell(cell: Range | string, sortByString: "Ascending" | "Descending"): void;

Parameters

cell: Excel.Range | string

A single cell to use get the criteria from for applying the autosort.

sortByString: "Ascending" | "Descending"

The direction of the sort.

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

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.PivotLayout object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.PivotLayoutData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.PivotLayoutData;

Returns

Excel.Interfaces.PivotLayoutData

Share via

Excel.PivotLayout class

Remarks

Properties

Methods

Property Details

autoFormat

Property Value

Remarks

context

Property Value

enableFieldList

Property Value

Remarks

layoutType

Property Value

Remarks

Examples

preserveFormatting

Property Value

Remarks

Examples

showColumnGrandTotals

Property Value

Remarks

Examples

showRowGrandTotals

Property Value

Remarks

Examples

subtotalLocation

Property Value

Remarks

Method Details

getColumnLabelRange()

Returns

Remarks

getDataBodyRange()

Returns

Remarks

Examples

getDataHierarchy(cell)

Parameters

Returns

Remarks

getFilterAxisRange()

Returns

Remarks

getPivotItems(axis, cell)

Parameters

Returns

Remarks

getPivotItems(axisString, cell)

Parameters

Returns

Remarks

getRange()

Returns

Remarks

getRowLabelRange()

Returns

Remarks

load(options)

Parameters

Returns

load(propertyNames)

Parameters

Returns

load(propertyNamesAndPaths)

Parameters

Returns

set(properties, options)

Parameters

Returns

set(properties)

Parameters

Returns

setAutoSortOnCell(cell, sortBy)

Parameters

Returns