FGenericPivotTable

Pivot table class (not dependent on workbook)

Access

Access through:

FUniver.generatePivotTable()

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`

`FGenericPivotTable.addFieldWithName`

Adds a field to the pivot table by its name and assigns it to the specified area.

TypeScript

addFieldWithName(name: string, area: PivotTableFiledAreaEnum): PivotTableLabelField | PivotTableValueField

Parameters

name — Required. The display name of the field to be added to the pivot table.
area — Required. The target area in the pivot table where the field should be added.

Returns

The field instance that has been added to the pivot table.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)// The returned labelField can be used to call settings for filtering, sorting, etc.const labelField = pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)// The returned valueField can be used to set the summary mode, display mode, etc.const valueField = pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)

Types: PivotTableLabelField · PivotTableValueField · PivotTableFiledAreaEnum

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

`FGenericPivotTable.addFilterFieldWithName`

Adds a field to the pivot table by its name and assigns it to the filter dimension.

TypeScript

addFilterFieldWithName(name: string, options: IFGenericPivotFilterOptions): PivotTableLabelField

Parameters

name — Required. The display name of the field to be added to the pivot table.
options — Required. The filter configuration

Returns

The field instance that has been added to the pivot table.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)pivot.addFilterFieldWithName('数量', {  type: univerAPI.Enum.PivotFilterTypeEnum.CustomFilter,  operator: univerAPI.Enum.PivotFilterOperatorEnum.valueEqual,  expected: 38,})pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)// At this time, there will only be one data with a value equal to 38const res = pivot.getResultByCalculate().dataArrconsole.log('debugger res', res)pivot.reset()pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)// There will only be two pieces of data at this timeconst resNew = pivot.getResultByCalculate().dataArrconsole.log('debugger res new', resNew)

Types: PivotTableLabelField · IFGenericPivotFilterOptions

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

`FGenericPivotTable.addValueFieldWithName`

Adds a field to the pivot table by its name and assigns it to the value measure.

TypeScript

addValueFieldWithName(name: string, options?: IPivotTableValueOptions): PivotTableValueField

Parameters

name — Required. The display name of the field to be added to the pivot table.
options — Optional. The value configuration

Returns

The field instance that has been added to the pivot table.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)pivot.addValueFieldWithName('数量', { subtotal: univerAPI.Enum.PivotSubtotalTypeEnum.average })const res = pivot.getResultByCalculate().dataArrconsole.log('debugger res', res)

Types: PivotTableValueField · IPivotTableValueOptions

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

`FGenericPivotTable.getDimensionInfo`

Get the dimension information of the current pivot table

TypeScript

getDimensionInfo(): IDimensionInfo | undefined

Returns

The dimension information of the pivot table.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)pivot.addFieldWithName('商品', univerAPI.Enum.PivotTableFiledAreaEnum.Column)pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)const newDimensionInfo = pivot.getDimensionInfo()console.log('debugger', newDimensionInfo)

Types: IDimensionInfo

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

`FGenericPivotTable.getFieldDataTypeByColumnIndex`

Get the data type of the field corresponding to the column number

TypeScript

getFieldDataTypeByColumnIndex(index: number): PivotDataFieldDataTypeEnum | undefined

Parameters

index — Required. The column number

Returns

The data type of the field corresponding to the column number

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const dataType = pivot.getFieldDataTypeByColumnIndex(4)console.log('debugger', dataType) // univerAPI.Enum.PivotDataFieldDataTypeEnum.number

Types: PivotDataFieldDataTypeEnum

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

`FGenericPivotTable.getFieldDataTypeByFieldName`

Get the data type of the field corresponding to the field name.

TypeScript

getFieldDataTypeByFieldName(name: string): PivotDataFieldDataTypeEnum | undefined

Parameters

name — Required. The display name of the field.

Returns

The data type of the field corresponding to the field name.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const dataType = pivot.getFieldDataTypeByFieldName('数量')console.log('debugger', dataType) // univerAPI.Enum.PivotDataFieldDataTypeEnum.number

Types: PivotDataFieldDataTypeEnum

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

`FGenericPivotTable.getLayout`

Get the layout type of the pivot table.

TypeScript

getLayout(): PivotLayoutTypeEnum

Returns

The layout type of the pivot table.

Examples

TypeScript

const pivot = univerAPI.generatePivotTable(sourceData)const layout = pivot.getLayout()console.log(layout === univerAPI.Enum.PivotLayoutTypeEnum.tabular)

Types: PivotLayoutTypeEnum

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

`FGenericPivotTable.getNameWithColumnIndex`

Returns the table header name corresponding to the column number

TypeScript

getNameWithColumnIndex(index: number): string

Parameters

index — Required. The column number

Returns

The table header name corresponding to the column number

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

`FGenericPivotTable.getPivotSourceData`

Returns the source data used to generate the pivot table

TypeScript

getPivotSourceData(): IDataFieldDataArray

Returns

The source data used to generate the pivot table.

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const originData = pivot.getPivotSourceData()console.log('debugger', originData === sourceData)

Types: IDataFieldDataArray

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

`FGenericPivotTable.getResultByCalculate`

Gets the result of the pivot table calculation. The return value contains the calculated two-dimensional array and the split calculation result. You can configure whether to display subTotal, grandTotal, etc. according to the input config.

TypeScript

getResultByCalculate(config?: IPivotTableCubeConfig): { dataArr: IPivotViewValueType[][]; dataArrWithSplit: IPivotViewValueType[][][]; }

Parameters

config — Optional. Default: {}. The configuration of the pivot table cube.

Returns

The result of the pivot table calculation.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const rowField = pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)const columnField = pivot.addFieldWithName('省份', univerAPI.Enum.PivotTableFiledAreaEnum.Row)const valueField = pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)const result = pivot.getResultByCalculate({  showRowGrandTotal: true,  showRowSubTotal: true,}).dataArrconsole.log('debugger', result)

Types: IPivotViewValueType · IPivotTableCubeConfig

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

`FGenericPivotTable.remove`

Dispose the pivot table

TypeScript

remove(): void

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

`FGenericPivotTable.removeFieldWithName`

Removes the field from the pivot table by its name.

TypeScript

removeFieldWithName(name: string): void

Parameters

name — Required. The display name of the field to be removed from the pivot table.

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const labelField = pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)// There is a `区域` in the row dimension of the pivot tableconst dimensionInfo = pivot.getDimensionInfo()pivot.removeFieldWithName('区域')// The new dimension information is returned as undefined.const newDimensionInfo = pivot.getDimensionInfo()

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

`FGenericPivotTable.reset`

Reset all configurations of the pivot table

TypeScript

reset(): void

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const rowField = pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)pivot.reset()// The dimension information returns empty because it is reset.const newDimensionInfo = pivot.getDimensionInfo()console.log('debugger', newDimensionInfo)

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

`FGenericPivotTable.resetDimension`

Reset the pivot table configuration for a dimension

TypeScript

resetDimension(area: PivotTableFiledAreaEnum): void

Parameters

area — Required. The target area in the pivot table where be reset

Examples

TypeScript

const sourceData = [  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],]const pivot = univerAPI.generatePivotTable(sourceData)const rowField = pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)const valueField = pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)pivot.reset(univerAPI.Enum.PivotTableFiledAreaEnum.Row)// The returned dimension information only contains the value dimension because the row dimension is reset.const newDimensionInfo = pivot.getDimensionInfo()console.log('debugger', newDimensionInfo)

Types: PivotTableFiledAreaEnum

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

`FGenericPivotTable.setLayout`

Set the layout type of the pivot table.

TypeScript

setLayout(layout: PivotLayoutTypeEnum): void

Parameters

layout — Required. The layout type to be set.

Examples

TypeScript

const pivot = univerAPI.generatePivotTable(sourceData)pivot.setLayout(univerAPI.Enum.PivotLayoutTypeEnum.compact)

Types: PivotLayoutTypeEnum

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

`FGenericPivotTable.setOptions`

Set the options of the pivot table.

TypeScript

setOptions(options: IPivotTableOptions): void

Parameters

options — Required. The options to be set.

Examples

TypeScript

const pivot = univerAPI.generatePivotTable(sourceData)// With this setting, the pivot table will fill in the cell values of the row dimensionpivot.setOptions({ repeatRowLabels: true, repeatColLabels: true })

Types: IPivotTableOptions

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

How is this guide?