FPivotTable

packages	`@univerjs-pro/sheets-pivot`

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

APIs

`addField`

Signature

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

Parameters

dataFieldIdOrIndex (string | number) — The data field id.
fieldArea (PivotTableFiledAreaEnum) — The area of the field.
index (number) — The index of the field in the target area.

Returns

(Promise<boolean>) — Whether the pivot field is added successfully.

Tags

@description — Add a pivot field to the pivot table.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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.
  fPivotTable.addField(1, univerAPI.Enum.PivotTableFiledAreaEnum.Row, 0)
}

`getConfig`

Signature

getConfig(): Nullable<IPivotTableConfig>

Returns

(Nullable<IPivotTableConfig>) — The pivot table config or undefined.

Tags

@description — Get the pivot table config by the pivot table id.
@typedef

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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 } = pivotTableConfig
console.log(targetCellInfo, sourceRangeInfo, isEmpty)

`getFieldIdsByArea`

Signature

getFieldIdsByArea(fieldArea: PivotTableFiledAreaEnum): string[]

Parameters

fieldArea (PivotTableFiledAreaEnum) — The area of the field.

Returns

(string[]) — The field ids in the target area.

Tags

@description — Get the field ids by the field area.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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)

`getFieldSetting`

Get the pivot table field setting by the field id.

Signature

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

Parameters

fieldId (string) — The table field id.

Returns

(IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON | undefined) — The field setting.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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)
}

`getPivotTableId`

Signature

getPivotTableId(): string

Returns

(string) — The pivot table id.

Tags

@description — Get the pivot table id.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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)

`getPivotTableRangeInfo`

Signature

getPivotTableRangeInfo(): IRange[] | undefined

Returns

(IRange[] | undefined) — The pivot table range list.

Tags

@description — Get the pivot table range info in worksheet.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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)
}

`getSourceFieldsInfo`

Get the pivot table field info list.

Signature

getSourceFieldsInfo(): IPivotTableDataFieldInfo[]

Returns

(IPivotTableDataFieldInfo[]) — The field info list.

Tags

@typedef

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
// 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)
}

`getValueFilter`

Get value filter info by the field id.

Signature

getValueFilter(fieldId: string): IPivotTableValueFilter | undefined

Parameters

fieldId (string) — The field id.

Returns

(IPivotTableValueFilter | undefined) — The value filter info.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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)
}

`getValueFilters`

Get all value filters of the pivot table. In pivot table, the value filter must be applied in order.So the order of the value filter is important.

Signature

getValueFilters(): IValueFilterInfoItem[]

Returns

(IValueFilterInfoItem[]) — The value filter info list.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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)
}

`move`

Signature

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

Parameters

sheetName (string) — - The target sheet name.
row (number) — - The target row index.
col (number) — - The target column index.

Returns

(Promise<boolean>) — Whether the pivot table field is moved successfully.

Tags

@description — Move the pivot table to the target cell.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subSheetName = fSheet.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
  fPivotTable.move(subSheetName, 100, 1)
}

`remove`

Signature

remove(): Promise<boolean>

Returns

(Promise<boolean>) — Whether the pivot table is removed successfully.

Tags

@description — Remove a pivot table from the workbook by pivot table id

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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()
}

`removeField`

Signature

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

Parameters

fieldIds (string[]) — The deleted field ids.

Returns

(Promise<boolean>) — Whether the pivot field is removed successfully.

Tags

@description — Remove a pivot field from the pivot table

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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.
    fPivotTable.removeField(rowIds)
  }
}

`renameField`

Signature

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

Parameters

fieldId (string) — - The field id.
name (string) — - The new name of the field.

Returns

(Promise<boolean>) — Whether the pivot table field is renamed successfully.

Tags

@description — Rename the pivot table field.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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) {
    fPivotTable.renameField(valueIds[0], 'newName')
  }
}

`reset`

Signature

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

Parameters

resetArea (PivotTableFiledAreaEnum) — The area of the field to reset or undefined to reset all fields.

Returns

(Promise<boolean>) — Whether the pivot table fields are reset successfully.

Tags

@description — Clear the fields by provided field area or clear all fields.

`setDateGroupType`

Signature

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

Parameters

tableFieldId (string) — - The field id of the group label field.
dateType (PivotDateGroupFieldDateTypeEnum) — - The date group type.

Returns

(Promise<boolean>) — Whether the date group type is set successfully.

Tags

@description — Set the date group type for the date group field.

Examples

// 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 column
const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.getSheetId()
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 0)
// Here we assume that the field index of the date type is 0
await 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
  pivotTable.setDateGroupType(rowIds[1], univerAPI.Enum.PivotDateGroupFieldDateTypeEnum.YearMonthDate)
}

`setFieldsConfig`

Signature

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

Parameters

config (IPivotTableConfig['fieldsConfig']) — The pivot table fields config.

Returns

(Promise<boolean>) — Whether the pivot table fields config is set successfully.

Tags

@description — 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.

`setLabelManualFilter`

Signature

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

Parameters

tableFieldId (string) — - The field id of the filter.
items (string[]) — - The items of the filter.
isAll (boolean) — - Whether the filter is all.If true, the filter will be all items, the items will be ignored.

Returns

(Promise<boolean>) — Whether the pivot table filter is set successfully.

Tags

@description — Set the pivot table manual filter.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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) {
    fPivotTable.setLabelManualFilter(rowIds[0], ['item1', 'item2'])
  }
}

`setLabelSort`

Signature

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

Parameters

tableFieldId (string) — - The field id of the sort.
info (IPivotTableSortInfo) — - The sort info.

Returns

(Promise<boolean>) — Whether the pivot table sort info is set successfully.

Tags

@description — Set the pivot table sort info.
@typedef

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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) {
    fPivotTable.setLabelSort(rowIds[0], { type: univerAPI.Enum.PivotDataFieldSortOperatorEnum.ascending })
  }
}

`setSubtotalType`

Signature

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

Parameters

fieldId (string) — - The field id.
subtotalType (PivotSubtotalTypeEnum) — - The subtotal type of the field.

Returns

(Promise<boolean>) — Whether the pivot table subtotal type is set successfully.

Tags

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

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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) {
    fPivotTable.setSubtotalType(valueId, univerAPI.Enum.PivotSubtotalTypeEnum.average)
  }
}

`setValueFilter`

Signature

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

Parameters

fieldId (string) — - The field id of the filter. Only one value filer can be set for a field.
filterInfo (Omit<IPivotTableValueFilter, 'type'>) — - The filter info. The undefined value will be removed from the old filter.

Returns

(Promise<boolean>) — Whether the pivot table value filter is set successfully.

Tags

@description — Set the pivot table value filter. A value filter is used to filter the data based on the value of a field.
@typedef

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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) {
    fPivotTable.setValueFilter(rowIds[0], {
      operator: univerAPI.Enum.PivotFilterOperatorEnum.valueGreaterThan,
      expected: 10,
      valueFieldId: valueIds[0],
    })
  }
  // remove the value filter
  // fPivotTable.setValueFilter(rowIds[0], undefined)
}

`updateFieldPosition`

Signature

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

Parameters

fieldId (string) — - The moved field id.
area (PivotTableFiledAreaEnum) — - The target area of the field.
index (number) — - 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

(Promise<boolean>) — Whether the pivot field is moved successfully.

Tags

@description — Update the pivot table field position.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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
    fPivotTable.updateFieldPosition(rowIds[0], univerAPI.Enum.PivotTableFiledAreaEnum.Column, 0)
  }
}

`updateValuePosition`

Signature

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

Parameters

position (PivotTableValuePositionEnum) — - The position of the value field.
index (number) — - The index of the value field.

Returns

(Promise<boolean>) — Whether the pivot value field is moved successfully.

Tags

@description — 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.

Examples

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.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
  fPivotTable.updateValuePosition(univerAPI.Enum.PivotTableValuePositionEnum.Row, 0)
}

Table of Contents