FGenericPivotTable

packages	`@univerjs-pro/sheets-pivot`

Pivot table class (not dependent on workbook)

APIs

`addFieldWithName`

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

Signature

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

Parameters

name (string) — - The display name of the field to be added to the pivot table.
area (PivotTableFiledAreaEnum) — - The target area in the pivot table where the field should be added.

Returns

(PivotTableLabelField | PivotTableValueField) — The field instance that has been added to the pivot table.

Examples

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('区域', PivotTableFiledAreaEnum.Row)
// The returned valueField can be used to set the summary mode, display mode, etc.
const valueField = pivot.addFieldWithName('数量', PivotTableFiledAreaEnum.Value)

`addFilterFieldWithName`

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

Signature

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

Parameters

name (string) — - The display name of the field to be added to the pivot table.
options (IFGenericPivotFilterOptions) — The filter configuration

Returns

(PivotTableLabelField) — The field instance that has been added to the pivot table.

Examples

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

pivot.reset()
pivot.addFieldWithName('区域', PivotTableFiledAreaEnum.Row)
pivot.addFieldWithName('数量', PivotTableFiledAreaEnum.Value)
// There will only be two pieces of data at this time
const resNew = pivot.getResultByCalculate().dataArr
console.log('debugger res new', resNew)

`addValueFieldWithName`

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

Signature

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

Parameters

name (string) — - The display name of the field to be added to the pivot table.
options (IPivotTableValueOptions) — The value configuration

Returns

(PivotTableValueField) — The field instance that has been added to the pivot table.

Examples

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

`getDimensionInfo`

Get the dimension information of the current pivot table

Signature

getDimensionInfo(): IDimensionInfo | undefined

Returns

(IDimensionInfo | undefined) — The dimension information of the pivot table.

Examples

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

`getFieldDataTypeByColumnIndex`

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

Signature

getFieldDataTypeByColumnIndex(index: number): PivotDataFieldDataTypeEnum | undefined

Parameters

index (number) — The column number

Returns

(PivotDataFieldDataTypeEnum | undefined) — The data type of the field corresponding to the column number

Examples

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) // PivotDataFieldDataTypeEnum.number

`getFieldDataTypeByFieldName`

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

Signature

getFieldDataTypeByFieldName(name: string): PivotDataFieldDataTypeEnum | undefined

Parameters

name (string) — - The display name of the field.

Returns

(PivotDataFieldDataTypeEnum | undefined) — The data type of the field corresponding to the field name.

Examples

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) // PivotDataFieldDataTypeEnum.number

`getNameWithColumnIndex`

Returns the table header name corresponding to the column number

Signature

getNameWithColumnIndex(index: number): string

Parameters

index (number) — - The column number

Returns

(string) — The table header name corresponding to the column number

`getPivotSourceData`

Returns the source data used to generate the pivot table

Signature

getPivotSourceData(): IDataFieldDataArray

Returns

(IDataFieldDataArray) — The source data used to generate the pivot table.

Examples

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)

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

Signature

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

Parameters

config (IPivotTableCubeConfig) — - The configuration of the pivot table cube.

Returns

({ dataArr: IPivotViewValueType[][]; dataArrWithSplit: IPivotViewValueType[][][] }) — The result of the pivot table calculation.

Examples

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

`remove`

Dispose the pivot table

Signature

remove(): void

`removeFieldWithName`

Removes the field from the pivot table by its name.

Signature

removeFieldWithName(name: string): void

Parameters

name (string) — - The display name of the field to be removed from the pivot table.

Returns

Examples

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

`reset`

Reset all configurations of the pivot table

Signature

reset(): void

Examples

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

`resetDimension`

Reset the pivot table configuration for a dimension

Signature

resetDimension(area: PivotTableFiledAreaEnum): void

Parameters

area (PivotTableFiledAreaEnum) — - The target area in the pivot table where be reset

Examples

const sourceData = [
  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],
  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],
  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],
]
const pivot = univerAPI.generatePivotTable(sourceData)
const rowField = pivot.addFieldWithName('区域', PivotTableFiledAreaEnum.Row)
const valueField = pivot.addFieldWithName('数量', PivotTableFiledAreaEnum.Value)
pivot.reset(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)

`setOptions`

Set the options of the pivot table.

Signature

setOptions(options: IPivotTableOptions): void

Parameters

options (IPivotTableOptions) — - The options to be set.

Returns

Examples

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

FGenericPivotTable

APIs

`addFieldWithName`

`addFilterFieldWithName`

`addValueFieldWithName`

`getDimensionInfo`

`getFieldDataTypeByColumnIndex`

`getFieldDataTypeByFieldName`

`getNameWithColumnIndex`

`getPivotSourceData`

`getResultByCalculate`

`remove`

`removeFieldWithName`

Returns

`reset`

`resetDimension`

`setOptions`

Returns

Table of Contents