ExcelScript.Slicer interface

Package:

ExcelScript

Represents a Slicer object in the workbook.

Remarks

Examples

/**
 * This script adds a slicer for an existing PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Pivot".
  const farmPivot = workbook.getPivotTable("Farm Pivot");

  // Create the slicer. 
  // Note that this assumes "Type" is already added as a hierarchy to the PivotTable.
  const fruitSlicer: ExcelScript.Slicer = workbook.addSlicer(
    farmPivot, /* The table or PivotTale to be sliced. */
    farmPivot.getHierarchy("Type").getFields()[0] /* What source field to use as the slicer options. */
  );

  // Select the items to display.
  fruitSlicer.selectItems(["Lemon", "Lime"]);

  // Set the left margin of the slicer.
  fruitSlicer.setLeft(400);
}

Methods

clearFilters()	Clears all the filters currently applied on the slicer.
delete()	Deletes the slicer.
getCaption()	Represents the caption of the slicer.
getHeight()	Represents the height, in points, of the slicer. Throws an InvalidArgument exception when set with a negative value or zero as an input.
getId()	Represents the unique ID of the slicer.
getIsFilterCleared()	Value is true if all filters currently applied on the slicer are cleared.
getLeft()	Represents the distance, in points, from the left side of the slicer to the left of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.
getName()	Represents the name of the slicer.
getSelectedItems()	Returns an array of selected items' keys.
getSlicerItem(key)	Gets a slicer item using its key or name. If the slicer item doesn't exist, then this method returns undefined.
getSlicerItems()	Represents the collection of slicer items that are part of the slicer.
getSortBy()	Represents the sort order of the items in the slicer. Possible values are: "DataSourceOrder", "Ascending", "Descending".
getStyle()	Constant value that represents the slicer style. Possible values are: "SlicerStyleLight1" through "SlicerStyleLight6", "TableStyleOther1" through "TableStyleOther2", "SlicerStyleDark1" through "SlicerStyleDark6". A custom user-defined style present in the workbook can also be specified.
getTop()	Represents the distance, in points, from the top edge of the slicer to the top of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.
getWidth()	Represents the width, in points, of the slicer. Throws an InvalidArgument error when set with a negative value or zero as an input.
getWorksheet()	Represents the worksheet containing the slicer.
selectItems(items)	Selects slicer items based on their keys. The previous selections are cleared. All items will be selected by default if the array is empty.
setCaption(caption)	Represents the caption of the slicer.
setHeight(height)	Represents the height, in points, of the slicer. Throws an InvalidArgument exception when set with a negative value or zero as an input.
setLeft(left)	Represents the distance, in points, from the left side of the slicer to the left of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.
setName(name)	Represents the name of the slicer.
setSortBy(sortBy)	Represents the sort order of the items in the slicer. Possible values are: "DataSourceOrder", "Ascending", "Descending".
setStyle(style)	Constant value that represents the slicer style. Possible values are: "SlicerStyleLight1" through "SlicerStyleLight6", "TableStyleOther1" through "TableStyleOther2", "SlicerStyleDark1" through "SlicerStyleDark6". A custom user-defined style present in the workbook can also be specified.
setTop(top)	Represents the distance, in points, from the top edge of the slicer to the top of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.
setWidth(width)	Represents the width, in points, of the slicer. Throws an InvalidArgument error when set with a negative value or zero as an input.

Method Details

clearFilters()

Clears all the filters currently applied on the slicer.

clearFilters(): void;

Returns

void

delete()

Deletes the slicer.

delete(): void;

Returns

void

getCaption()

Represents the caption of the slicer.

getCaption(): string;

Returns

string

getHeight()

Represents the height, in points, of the slicer. Throws an InvalidArgument exception when set with a negative value or zero as an input.

getHeight(): number;

Returns

number

getId()

Represents the unique ID of the slicer.

getId(): string;

Returns

string

getIsFilterCleared()

Value is true if all filters currently applied on the slicer are cleared.

getIsFilterCleared(): boolean;

Returns

boolean

getLeft()

Represents the distance, in points, from the left side of the slicer to the left of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.

getLeft(): number;

Returns

number

getName()

Represents the name of the slicer.

getName(): string;

Returns

string

getSelectedItems()

Returns an array of selected items' keys.

getSelectedItems(): string[];

Returns

string[]

getSlicerItem(key)

Gets a slicer item using its key or name. If the slicer item doesn't exist, then this method returns undefined.

getSlicerItem(key: string): SlicerItem | undefined;

Parameters

key

string

Key or name of the slicer to be retrieved.

Returns

ExcelScript.SlicerItem | undefined

getSlicerItems()

Represents the collection of slicer items that are part of the slicer.

getSlicerItems(): SlicerItem[];

Returns

ExcelScript.SlicerItem[]

getSortBy()

Represents the sort order of the items in the slicer. Possible values are: "DataSourceOrder", "Ascending", "Descending".

getSortBy(): SlicerSortType;

Returns

ExcelScript.SlicerSortType

getStyle()

Constant value that represents the slicer style. Possible values are: "SlicerStyleLight1" through "SlicerStyleLight6", "TableStyleOther1" through "TableStyleOther2", "SlicerStyleDark1" through "SlicerStyleDark6". A custom user-defined style present in the workbook can also be specified.

getStyle(): string;

Returns

string

getTop()

Represents the distance, in points, from the top edge of the slicer to the top of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.

getTop(): number;

Returns

number

getWidth()

Represents the width, in points, of the slicer. Throws an InvalidArgument error when set with a negative value or zero as an input.

getWidth(): number;

Returns

number

getWorksheet()

Represents the worksheet containing the slicer.

getWorksheet(): Worksheet;

Returns

ExcelScript.Worksheet

selectItems(items)

Selects slicer items based on their keys. The previous selections are cleared. All items will be selected by default if the array is empty.

selectItems(items?: string[]): void;

Parameters

items

string[]

Optional. The specified slicer item names to be selected.

Returns

void

setCaption(caption)

Represents the caption of the slicer.

setCaption(caption: string): void;

Parameters

caption

string

Returns

void

setHeight(height)

Represents the height, in points, of the slicer. Throws an InvalidArgument exception when set with a negative value or zero as an input.

setHeight(height: number): void;

Parameters

height

number

Returns

void

setLeft(left)

Represents the distance, in points, from the left side of the slicer to the left of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.

setLeft(left: number): void;

Parameters

left

number

Returns

void

setName(name)

Represents the name of the slicer.

setName(name: string): void;

Parameters

name

string

Returns

void

setSortBy(sortBy)

Represents the sort order of the items in the slicer. Possible values are: "DataSourceOrder", "Ascending", "Descending".

setSortBy(sortBy: SlicerSortType): void;

Parameters

sortBy

ExcelScript.SlicerSortType

Returns

void

setStyle(style)

Constant value that represents the slicer style. Possible values are: "SlicerStyleLight1" through "SlicerStyleLight6", "TableStyleOther1" through "TableStyleOther2", "SlicerStyleDark1" through "SlicerStyleDark6". A custom user-defined style present in the workbook can also be specified.

setStyle(style: string): void;

Parameters

style

string

Returns

void

setTop(top)

Represents the distance, in points, from the top edge of the slicer to the top of the worksheet. Throws an InvalidArgument error when set with a negative value as an input.

setTop(top: number): void;

Parameters

top

number

Returns

void

setWidth(width)

Represents the width, in points, of the slicer. Throws an InvalidArgument error when set with a negative value or zero as an input.

setWidth(width: number): void;

Parameters

width

number

Returns

void