FPivotTable

The facade class for the pivot table.Which uses to setting the pivot table fields configs.

Register @univerjs-pro/sheets-pivot or a preset that includes it. In plugin mode, import @univerjs-pro/sheets-pivot/facade. Additional methods below require their listed plugin packages. See Facade setup.

`@univerjs-pro/sheets-pivot`

`FPivotTable.addField`

Add a pivot field to the pivot table.

TypeScript

addField(dataFieldIdOrIndex: string | number, fieldArea: PivotTableFiledAreaEnum, index: number): Promise<boolean>

Parameters

dataFieldIdOrIndex — Required. The data field id.
fieldArea — Required. The area of the field.
index — Required. The index of the field in the target area.

Returns

Whether the pivot field is added successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  // 1 means the source range index , 0 means the index in the target area.  await fPivotTable.addField(1, univerAPI.Enum.PivotTableFiledAreaEnum.Row, 0)}

Types: Promise · PivotTableFiledAreaEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.clearLabelSort`

Clear the pivot table label sort. The field returns to the configured default label ordering.

TypeScript

clearLabelSort(tableFieldId: string): Promise<boolean>

Parameters

tableFieldId — Required. The field id of the sort.

Returns

Whether the pivot table label sort is cleared successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()if (!fWorksheet) throw new Error('fWorksheet is not available')const fPivotTable = fWorksheet.getPivotTableByCell(0, 8)if (!fPivotTable) throw new Error('fPivotTable is not available')const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)if (rowIds.length > 0) {  await fPivotTable.clearLabelSort(rowIds[0])}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.clearValueFilter`

Clear the value filter of the pivot table field through command.

TypeScript

clearValueFilter(fieldId: string): Promise<boolean>

Parameters

fieldId — Required. The field id of the value filter.

Returns

Whether the value filter is cleared successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)const rowId = fPivotTable?.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)[0]if (fPivotTable && rowId) {  await fPivotTable.clearValueFilter(rowId)}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.drillDown`

Drill down a pivot value cell through command and create a sheet with the related source records.

TypeScript

drillDown(row: number, col: number): Promise<boolean>

Parameters

row — Required. The row index of the pivot value cell.
col — Required. The column index of the pivot value cell.

Returns

Whether the drill down sheet is created successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)await fPivotTable?.drillDown(5, 10)

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getConfig`

Get the pivot table config by the pivot table id.

TypeScript

getConfig(): Nullable<IPivotTableConfig>

Returns

The pivot table config or undefined.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)const pivotTableConfig = fPivotTable.getConfig()const { targetCellInfo, sourceRangeInfo, isEmpty } = pivotTableConfigconsole.log(targetCellInfo, sourceRangeInfo, isEmpty)

Types: IPivotTableConfig · Nullable

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getFieldIdsByArea`

Get the field ids by the field area.

TypeScript

getFieldIdsByArea(fieldArea: PivotTableFiledAreaEnum): string[]

Parameters

fieldArea — Required. The area of the field.

Returns

The field ids in the target area.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)const fieldIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)console.log(fieldIds)

Types: PivotTableFiledAreaEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getFieldSetting`

Get the pivot table field setting by the field id.

TypeScript

getFieldSetting(fieldId: string): IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON | undefined

Parameters

fieldId — Required. The table field id.

Returns

The field setting.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)const fieldId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)[0]if (filedId) {  const fieldSetting = fPivotTable.getFieldSetting(fieldId)}

Types: IPivotTableValueFieldJSON · IPivotTableLabelFieldJSON

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getId`

Get the pivot table id.

TypeScript

getId(): string

Returns

The pivot table id.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)const pivotTableId = fPivotTable?.getId()console.log(pivotTableId)

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getLayout`

Get the pivot table layout type.

TypeScript

getLayout(): PivotLayoutTypeEnum | undefined

Returns

The layout type, or undefined if the pivot table no longer exists.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fSheet = fWorkbook.getActiveSheet()const subUnitId = fSheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)const layout = fPivotTable?.getLayout()console.log(layout === univerAPI.Enum.PivotLayoutTypeEnum.tabular)

Types: PivotLayoutTypeEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getOptions`

Get the display options of the pivot table.

TypeScript

getOptions(): IPivotTableOptions | undefined

Returns

The display options or undefined if the pivot table no longer exists.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)const options = fPivotTable?.getOptions()console.log(options?.showRowGrandTotal)

Types: IPivotTableOptions

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getPivotTableId`

Get the pivot table id.

TypeScript

getPivotTableId(): string

Returns

The pivot table id.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)const pivotTableId = fPivotTable.getPivotTableId()console.log(pivotTableId)

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getPivotTableMatrixInfo`

Get the pivot table matrix cache generated by the last render.

TypeScript

getPivotTableMatrixInfo(): IPivotTableMatrixInfo | undefined

Returns

The matrix cache or undefined if the pivot table has not rendered.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)const matrixInfo = fPivotTable?.getPivotTableMatrixInfo()console.log(matrixInfo?.matrix)

Types: IPivotTableMatrixInfo

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getPivotTableRangeInfo`

Get the pivot table range info in worksheet.

TypeScript

getPivotTableRangeInfo(): IRange[] | undefined

Returns

The pivot table range list.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const pivotTableRangeInfo = fPivotTable.getPivotTableRangeInfo()  console.log(pivotTableRangeInfo)}

Types: IRange

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getShowDataAs`

Get the Show Values As configuration of a pivot table value field.

TypeScript

getShowDataAs(fieldId: string): IPivotTableShowDataAsInfo | undefined

Parameters

fieldId — Required. The value field id.

Returns

The Show Values As configuration, or undefined when the field is not a value field.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()if (!fWorksheet) throw new Error('fWorksheet is not available')const fPivotTable = fWorksheet.getPivotTableByCell(0, 8)if (!fPivotTable) throw new Error('fPivotTable is not available')const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]if (valueId) {  const showDataAs = fPivotTable.getShowDataAs(valueId)  console.log(showDataAs)}

Types: IPivotTableShowDataAsInfo

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getSourceFieldsInfo`

Get the pivot table field info list.

TypeScript

getSourceFieldsInfo(): IPivotTableDataFieldInfo[]

Returns

The field info list.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const fieldInfos = fPivotTable.getSourceFieldsInfo()  console.log(fieldInfos)}

Types: IPivotTableDataFieldInfo

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getSourceRangeInfo`

Get the source range info of the pivot table.

TypeScript

getSourceRangeInfo(): IUnitRangeNameWithSubUnitId | undefined

Returns

The source range info or undefined if the pivot table no longer exists.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)const sourceRangeInfo = fPivotTable?.getSourceRangeInfo()console.log(sourceRangeInfo?.range)

Types: IUnitRangeNameWithSubUnitId

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getTargetCellInfo`

Get the target cell info of the pivot table.

TypeScript

getTargetCellInfo(): IPivotTableConfig['targetCellInfo'] | undefined

Returns

The target cell info or undefined if the pivot table no longer exists.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)const targetCellInfo = fPivotTable?.getTargetCellInfo()console.log(targetCellInfo?.row, targetCellInfo?.col)

Types: IPivotTableConfig

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getValueFilter`

Get the value filter owned by a row or column field.

TypeScript

getValueFilter(fieldId: string): IPivotTableValueFilter | undefined

Parameters

fieldId — Required. The row or column field id.

Returns

A copy of the value filter, or undefined when the field has no value filter.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)  const valueFilter = fPivotTable.getValueFilter(rowIds[0])  console.log(valueFilter)}

Types: IPivotTableValueFilter

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.getValueFilters`

Get all value filters in their evaluation order. A later rule evaluates only the items retained by earlier rules.

TypeScript

getValueFilters(): IValueFilterInfoItem[]

Returns

Copies of the ordered value-filter rules.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const valueFilters = fPivotTable.getValueFilters()  console.log(valueFilters)}

Types: IValueFilterInfoItem

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.move`

Move the pivot table to the target cell.

TypeScript

move(sheetName: string, row: number, col: number): Promise<boolean>

Parameters

sheetName — Required. The target sheet name.
row — Required. The target row index.
col — Required. The target column index.

Returns

Whether the pivot table is moved successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subSheetName = fWorksheet.getSheetName()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  // move pivot table to row:100, col:1  await fPivotTable.move(subSheetName, 100, 1)}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.remove`

Remove a pivot table from the workbook by pivot table id

TypeScript

remove(): Promise<boolean>

Returns

Whether the pivot table is removed successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  fPivotTable.remove()}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.removeField`

Remove a pivot field from the pivot table

TypeScript

removeField(fieldIds: string[]): Promise<boolean>

Parameters

fieldIds — Required. The deleted field ids.

Returns

Whether the pivot field is removed successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)  if (rowIds.length > 0) {    // remove all field in row.    await fPivotTable.removeField(rowIds)  }}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.renameField`

Rename the pivot table field.

TypeScript

renameField(fieldId: string, name: string): Promise<boolean>

Parameters

fieldId — Required. The field id.
name — Required. The new name of the field.

Returns

Whether the pivot table field is renamed successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const valueIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)  if (valueIds.length > 0) {    await fPivotTable.renameField(valueIds[0], 'newName')  }}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.reset`

Clear the fields by provided field area or clear all fields.

TypeScript

reset(resetArea?: PivotTableFiledAreaEnum): Promise<boolean>

Parameters

resetArea — Optional. The area of the field to reset or undefined to reset all fields.

Returns

Whether the pivot table fields are reset successfully.

Types: Promise · PivotTableFiledAreaEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.resetShowDataAs`

Reset the Show Values As configuration of a pivot table value field to the normal calculation.

TypeScript

resetShowDataAs(fieldId: string): Promise<boolean>

Parameters

fieldId — Required. The value field id.

Returns

Whether the Show Values As configuration is reset successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()if (!fWorksheet) throw new Error('fWorksheet is not available')const fPivotTable = fWorksheet.getPivotTableByCell(0, 8)if (!fPivotTable) throw new Error('fPivotTable is not available')const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]if (valueId) {  await fPivotTable.resetShowDataAs(valueId)}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setCellCollapse`

Collapse or expand a pivot table item by the cell position through command.

TypeScript

setCellCollapse(row: number, col: number, collapse: boolean): Promise<boolean>

Parameters

row — Required. The row index of the pivot item cell.
col — Required. The column index of the pivot item cell.
collapse — Required. Whether to collapse the pivot item. True means collapse, false means expand.

Returns

Whether the pivot item collapse state is updated successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)// the cell must be a cell has collapse or expand button, otherwise the command will fail, and the cell (2,8) is just an example, you can pass any cell in the pivot table which has collapse or expand button.await fPivotTable?.setCellCollapse(2, 8, true)

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setDateGroupType`

Set the date group type for the date group field.

TypeScript

setDateGroupType(tableFieldId: string, dateType: PivotDateGroupFieldDateTypeEnum): Promise<boolean>

Parameters

tableFieldId — Required. The field id of the group label field.
dateType — Required. The date group type.

Returns

Whether the date group type is set successfully.

Examples

TypeScript

// To use this demo, you need to build a pivot table first, and make sure that the entire column in the pivot table source data is a date,// and drag the date dimension to the row or columnconst fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 0)// Here we assume that the field index of the date type is 0await pivotTable.addField(0, univerAPI.Enum.PivotTableFiledAreaEnum.Row, 0)const rowIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)if (rowIds.length > 0) {  // Because the index is added to the position of 0 above, the index of the automatically derived date grouping dimension is 1  await pivotTable.setDateGroupType(    rowIds[1],    univerAPI.Enum.PivotDateGroupFieldDateTypeEnum.YearMonthDate,  )}

Types: Promise · PivotDateGroupFieldDateTypeEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setFieldsConfig`

Set the pivot table fields config.It will add fields to the pivot table from provided config.Before setting the fields config, you should ensure the pivot table is empty to avoid the conflict.

TypeScript

setFieldsConfig(config: IPivotTableConfig['fieldsConfig']): Promise<boolean>

Parameters

config — Required. The pivot table fields config.

Returns

Whether the pivot table fields config is set successfully.

Types: Promise · IPivotTableConfig

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setFieldSetting`

Set the pivot table field display name, number format, subtotal type, or Show Values As configuration through command.

TypeScript

setFieldSetting(tableFieldId: string, setting: IPivotTableFieldSettingOptions): Promise<boolean>

Parameters

tableFieldId — Required. The pivot table field id.
setting — Required. The field setting options.

Returns

Whether the pivot table field setting is updated successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)const valueId = fPivotTable?.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]if (fPivotTable && valueId) {  await fPivotTable.setFieldSetting(valueId, {    displayName: 'Revenue',    format: '$#,##0',    subtotalType: univerAPI.Enum.PivotSubtotalTypeEnum.sum,  })}

Types: Promise · IPivotTableFieldSettingOptions

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setLabelManualFilter`

Set the pivot table manual filter.

TypeScript

setLabelManualFilter(tableFieldId: string, items: string[], isAll?: boolean): Promise<boolean>

Parameters

tableFieldId — Required. The field id of the filter.
items — Required. The items of the filter.
isAll — Optional. Whether the filter is all.If true, the filter will be all items, the items will be ignored.

Returns

Whether the pivot table filter is set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)  if (rowIds.length > 0) {    await fPivotTable.setLabelManualFilter(rowIds[0], ['item1', 'item2'])  }}

Types: Promise

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setLabelSort`

Set the pivot table sort info.

TypeScript

setLabelSort(tableFieldId: string, info: IPivotTableSortInfo): Promise<boolean>

Parameters

tableFieldId — Required. The field id of the sort.
info — Required. The sort info.

Returns

Whether the pivot table sort info is set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)  if (rowIds.length > 0) {    await fPivotTable.setLabelSort(rowIds[0], {      type: univerAPI.Enum.PivotDataFieldSortOperatorEnum.ascending,    })    // Advanced usage: persist an explicit label order. The same order is used by browser and Node.js calculations.    await fPivotTable.setLabelSort(rowIds[0], {      type: univerAPI.Enum.PivotDataFieldSortOperatorEnum.custom,      customOrder: ['North', 'East', 'South', 'West'],    })  }}

Types: Promise · IPivotTableSortInfo

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setLayout`

Set the pivot table layout type through command. It keeps undo/redo, collaboration and render update paths consistent with panel operations.

TypeScript

setLayout(layout: PivotLayoutTypeEnum): Promise<boolean>

Parameters

layout — Required. The layout type to set.

Returns

Whether the pivot table layout is set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fSheet = fWorkbook.getActiveSheet()const subUnitId = fSheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)await fPivotTable?.setLayout(univerAPI.Enum.PivotLayoutTypeEnum.compact)

Types: Promise · PivotLayoutTypeEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setOptions`

Set the display options of the pivot table through command. It keeps undo/redo, collaboration and render update paths consistent with panel operations.

TypeScript

setOptions(options: IPivotTableOptions): Promise<boolean>

Parameters

options — Required. The display options to set.

Returns

Whether the pivot table options are set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8)await fPivotTable?.setOptions({  repeatRowLabels: true,  repeatColLabels: true,  showRowGrandTotal: false,  showColGrandTotal: true,})

Types: Promise · IPivotTableOptions

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setShowDataAs`

Set the Show Values As configuration of a pivot table value field.

TypeScript

setShowDataAs(fieldId: string, showDataAs: IPivotTableShowDataAsInfo): Promise<boolean>

Parameters

fieldId — Required. The value field id.
showDataAs — Required. The Show Values As configuration.

Returns

Whether the Show Values As configuration is set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()if (!fWorksheet) throw new Error('fWorksheet is not available')const fPivotTable = fWorksheet.getPivotTableByCell(0, 8)if (!fPivotTable) throw new Error('fPivotTable is not available')const rowId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)[0]const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]if (rowId && valueId) {  await fPivotTable.setShowDataAs(valueId, {    type: univerAPI.Enum.PivotShowAsTypeEnum.differenceFrom,    baseFieldId: rowId,    baseItem: '',    baseItemType: univerAPI.Enum.PivotShowAsBaseItemTypeEnum.previous,  })}

Types: Promise · IPivotTableShowDataAsInfo

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setSourceRange`

Update the source range of the pivot table through command.

TypeScript

setSourceRange(dataRangeInfo: IUnitRangeNameWithSubUnitId): Promise<boolean>

Parameters

dataRangeInfo — Required. The new source data range info.

Returns

Whether the source range is updated successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const unitId = fWorkbook.getId()const subUnitId = fWorksheet.getSheetId()const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)await fPivotTable?.setSourceRange({  unitId,  subUnitId,  sheetName: fWorksheet.getSheetName(),  range: {    startRow: 0,    endRow: 100,    startColumn: 0,    endColumn: 6,  },})

Types: Promise · IUnitRangeNameWithSubUnitId

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setSubtotalType`

Set the pivot table subtotal type for value field, it only works for the value field.

TypeScript

setSubtotalType(fieldId: string, subtotalType: PivotSubtotalTypeEnum): Promise<boolean>

Parameters

fieldId — Required. The field id.
subtotalType — Required. The subtotal type of the field.

Returns

Whether the pivot table subtotal type is set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]  if (valueId) {    await fPivotTable.setSubtotalType(valueId, univerAPI.Enum.PivotSubtotalTypeEnum.average)  }}

Types: Promise · PivotSubtotalTypeEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.setValueFilter`

Set or replace the value filter owned by a row or column field. Each label field can own at most one rule. New rules are appended to the evaluation order, while replacing a rule keeps its current position. Use count or percent with isBottom to configure Top/Bottom filters.

TypeScript

setValueFilter(fieldId: string, filterInfo?: Omit<IPivotTableValueFilter, 'type'>): Promise<boolean>

Parameters

fieldId — Required. The row or column field id that owns the rule.
filterInfo — Optional. The filter configuration. Pass undefined to remove the current rule. valueFieldId must reference an existing value field. Between operators use a two-number expected array; other operators use a number.

Returns

Whether the pivot table value filter is set successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)  const valueIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)  if (valueIds.length > 0 && rowIds.length > 0) {    await fPivotTable.setValueFilter(rowIds[0], {      operator: univerAPI.Enum.PivotFilterOperatorEnum.valueGreaterThan,      expected: 10,      valueFieldId: valueIds[0],    })  }  // Remove the value filter while keeping other ordered rules unchanged.  // await fPivotTable.clearValueFilter(rowIds[0]);}

Types: Promise · Omit · IPivotTableValueFilter

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.updateFieldPosition`

Update the pivot table field position.

TypeScript

updateFieldPosition(fieldId: string, area: PivotTableFiledAreaEnum, index: number): Promise<boolean>

Parameters

fieldId — Required. The moved field id.
area — Required. The target area of the field.
index — Required. The target index of the field, if the index is bigger than the field count in the target area, the field will be moved to the last, if the index is smaller than 0, the field will be moved to the first.

Returns

Whether the pivot field is moved successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)  if (rowIds.length > 0) {    // move to column    await fPivotTable.updateFieldPosition(      rowIds[0],      univerAPI.Enum.PivotTableFiledAreaEnum.Column,      0,    )  }}

Types: Promise · PivotTableFiledAreaEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.updateSourceRange`

Update the source range of the pivot table through command. This is an alias of setSourceRange.

TypeScript

updateSourceRange(dataRangeInfo: IUnitRangeNameWithSubUnitId): Promise<boolean>

Parameters

dataRangeInfo — Required. The new source data range info.

Returns

Whether the source range is updated successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), fWorksheet.getSheetId(), 0, 8)await fPivotTable?.updateSourceRange({  unitId: fWorkbook.getId(),  subUnitId: fWorksheet.getSheetId(),  sheetName: fWorksheet.getSheetName(),  range: { startRow: 0, endRow: 100, startColumn: 0, endColumn: 6 },})

Types: Promise · IUnitRangeNameWithSubUnitId

Package: @univerjs-pro/sheets-pivot · Type definitions

`FPivotTable.updateValuePosition`

If there are multiple value fields in the pivot table, you can update the position of the value field, which only can be position in row or column.

TypeScript

updateValuePosition(position: PivotTableValuePositionEnum, index: number): Promise<boolean>

Parameters

position — Required. The position of the value field.
index — Required. The index of the value field.

Returns

Whether the pivot value field is moved successfully.

Examples

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const unitId = fWorkbook.getId()const fWorksheet = fWorkbook.getSheetByName('Sheet1')if (!fWorksheet) throw new Error('fWorksheet is not available')const subUnitId = fWorksheet.getSheetId()// Here we assume that you already have a pivot table in cell A8 of the table.// If not, please call fWorkbook.addPivotTable() according to the documentation.const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)if (fPivotTable) {  // The prerequisite here is that the value dimension has more than one item  await fPivotTable.updateValuePosition(univerAPI.Enum.PivotTableValuePositionEnum.Row, 0)}

Types: Promise · PivotTableValuePositionEnum

Package: @univerjs-pro/sheets-pivot · Type definitions

`@univerjs-pro/sheets-pivot-chart`

`FPivotTable.newChart`

Creates a detached PivotChart Builder permanently linked to this PivotTable.

Any Pivot report edit made through the table or one of its linked charts updates the one shared PivotModel. Chart visuals and Drawing placement remain independent per chart.

TypeScript

newChart<T extends PivotChartTypeString>(type: T): FLinkedSheetPivotChartBuilderOf<T>

Parameters

type — Required. The supported Chart type.

Returns

A prelinked, source-locked Builder.

Examples

TypeScript

import '@univerjs-pro/sheets-pivot-chart/facade'const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getSheetByName('Dashboard')const fPivotTable = fWorkbook.getPivotTableById('pivot-table-1')if (!fWorksheet || !fPivotTable) throw new Error('Pivot source is unavailable.')const first = await fWorksheet.insertPivotChart(  fPivotTable.newChart(univerAPI.Enum.ChartTypeString.Column).setPosition('A1').build(),)const second = await fWorksheet.insertPivotChart(  fPivotTable.newChart(univerAPI.Enum.ChartTypeString.Line).setPosition('J1').build(),)console.log(first.getPivotTableReference(), second.getPivotTableReference())

Types: FLinkedSheetPivotChartBuilderOf

Package: @univerjs-pro/sheets-pivot-chart · Type definitions

How is this guide?