Excel. Pivot Layout class

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 the alt text for the displayed PivotTable.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.altTextTitle = "Farm Sales PivotTable";
  pivotLayout.altTextDescription = "A summary of fruit sales. It is pivoted on farm name, and fruit type. The aggregated data is both the sums of crates sold at the farms and the sums of crates sold wholesale.";
  console.log("Adding alt text. Check the PivotTable settings to see the changes.");

  await context.sync();
});

altTextTitle

The alt text title of the PivotTable.

altTextTitle: string;

Property Value

string

Remarks

autoFormat

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

autoFormat: boolean;

Property Value

boolean

Remarks

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

emptyCellText

The text that is automatically filled into any empty cell in the PivotTable if fillEmptyCells == true. Note that this value persists if fillEmptyCells is set to false, and that setting this value does not set that property to true. By default, this is an empty string.

emptyCellText: string;

Property Value

string

Remarks

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 a default value for an empty cell in the PivotTable. This doesn't include cells left blank by the layout.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.emptyCellText = "--";

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

enableFieldList

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

enableFieldList: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.10 ]

fillEmptyCells

Specifies whether empty cells in the PivotTable should be populated with the emptyCellText. Default is false. Note that the value of emptyCellText persists when this property is set to false.

fillEmptyCells: boolean;

Property Value

boolean

Remarks

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) => {
  // Toggle whether empty cells are filled with a default value.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

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

  let fillToSet = !pivotLayout.fillEmptyCells;
  console.log(`Filling empty cells? - ${fillToSet}`);

  pivotLayout.fillEmptyCells = fillToSet;
  await context.sync();
});

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 BETA (PREVIEW ONLY) ]

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);
});

pivotStyle

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

The style applied to the PivotTable.

readonly pivotStyle: Excel.PivotTableStyle;

Property Value

Excel.PivotTableStyle

Remarks

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

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

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();
});

showFieldHeaders

Specifies whether the PivotTable displays field headers (field captions and filter drop-downs).

showFieldHeaders: boolean;

Property Value

boolean

Remarks

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 field headers on and off for the row and column hierarchies.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;
  pivotLayout.load("showFieldHeaders");
  await context.sync();

  let showHeaders = !pivotLayout.showFieldHeaders;
  console.log(`Show field headers? - ${showHeaders}`);
  pivotLayout.showFieldHeaders = showHeaders;
  await context.sync();
});

showRowGrandTotals

Specifies if the PivotTable report shows grand totals for rows.

showRowGrandTotals: boolean;

Property Value

boolean

Remarks

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

Method Details

displayBlankLineAfterEachItem(display)

Sets whether or not to display a blank line after each item. This is set at the global level for the PivotTable and applied to individual PivotFields. This function overwrites the setting for all fields in the PivotTable to the value of display parameter.

displayBlankLineAfterEachItem(display: boolean): void;

Parameters

display: boolean

True turns on the blank-line display setting. False turns it off.

Returns

void

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

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) => {
  // Add a blank row after each PivotItem in the row hierarchy.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.displayBlankLineAfterEachItem(true);
  console.log("Setting `PivotLayout.displayBlankLineAfterEachItem` to true.");

  await context.sync();
});

getCell(dataHierarchy, rowItems, columnItems)

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Gets a unique cell in the PivotTable based on a data hierarchy and the row and column items of their respective hierarchies. The returned cell is the intersection of the given row and column that contains the data from the given hierarchy. This method is the inverse of calling getPivotItems and getDataHierarchy on a particular cell.

getCell(dataHierarchy: DataPivotHierarchy | string, rowItems: Array<PivotItem | string>, columnItems: Array<PivotItem | string>): Excel.Range;

Parameters

dataHierarchy: Excel.DataPivotHierarchy | string

The dataHierarchy that provides the data item to find.

rowItems: Array<Excel.PivotItem | string>

The PivotItems from the row axis that make up the value to find.

columnItems: Array<Excel.PivotItem | string>

The PivotItems from the column axis that make up the value to find.

Returns

Excel.Range

A range specifying a single cell that contains the value specified.

Remarks

getColumnLabelRange()

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

getColumnLabelRange(): Excel.Range;

Returns

Excel.Range

Remarks

getDataBodyRange()

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

getDataBodyRange(): Excel.Range;

Returns

Excel.Range

Remarks

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

getFilterAxisRange()

Returns the range of the PivotTable's filter area.

getFilterAxisRange(): Excel.Range;

Returns

Excel.Range

Remarks

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

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

getRange()

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

getRange(): Excel.Range;

Returns

Excel.Range

Remarks

getRowLabelRange()

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

getRowLabelRange(): Excel.Range;

Returns

Excel.Range

Remarks

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

repeatAllItemLabels(repeatLabels)

Sets the "repeat all item labels" setting across all fields in the PivotTable.

repeatAllItemLabels(repeatLabels: boolean): void;

Parameters

repeatLabels: boolean

True turns on the label-repetition display setting. False turns it off.

Returns

void

Remarks

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) => {
  // Repeat the PivotItem labels for each row used by another level of the row hierarchy.
  const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
  const pivotLayout = pivotTable.layout;

  pivotLayout.repeatAllItemLabels(true);
  console.log("Setting `PivotLayout.repeatAllItemLabels` to true.");

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

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