Class: FGenericPivotTable

Pivot table class (not dependent on workbook)

Methods

addFieldWithName()


addFieldWithName(name, area): any

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

Parameters

Parameter	Type	Description
`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

any

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

Example


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()


addFilterFieldWithName(name, options): PivotTableLabelField

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

Parameters

Parameter	Type	Description
`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.

Example


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()


addValueFieldWithName(name, options?): PivotTableValueField

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

Parameters

Parameter	Type	Description
`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.

Example


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()


getDimensionInfo(): IDimensionInfo

Get the dimension information of the current pivot table

Returns

IDimensionInfo

The dimension information of the pivot table.

Example


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()


getFieldDataTypeByColumnIndex(index): any

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

Parameters

Parameter	Type	Description
`index`	`number`	The column number

Returns

any

The data type of the field corresponding to the column number

Example


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()


getFieldDataTypeByFieldName(name): any

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

Parameters

Parameter	Type	Description
`name`	`string`	The display name of the field.

Returns

any

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

Example


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()


getNameWithColumnIndex(index): string

Returns the table header name corresponding to the column number

Parameters

Parameter	Type	Description
`index`	`number`	The column number

Returns

string

The table header name corresponding to the column number

getPivotSourceData()


getPivotSourceData(): [...[], ...(...)[][]]

Returns the source data used to generate the pivot table

Returns

[…[], ...(...)[][]]

The source data used to generate the pivot table.


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);

getResultByCalculate()


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

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.

Parameters

Parameter	Type	Description
`config`?	`IPivotTableCubeConfig`	The configuration of the pivot table cube.

Returns


{
  dataArr: ...[];
  dataArrWithSplit: ...[];
}

The result of the pivot table calculation.

Name	Type
`dataArr`	…[]
`dataArrWithSplit`	…[]

Example


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()


remove(): void

Dispose the pivot table

Returns

void

removeFieldWithName()


removeFieldWithName(name): void

Removes the field from the pivot table by its name.

Parameters

Parameter	Type	Description
`name`	`string`	The display name of the field to be removed from the pivot table.

Returns

void

Example


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(): void

Reset all configurations of the pivot table

Returns

void

Example


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()


resetDimension(area): void

Reset the pivot table configuration for a dimension

Parameters

Parameter	Type	Description
`area`	`PivotTableFiledAreaEnum`	The target area in the pivot table where be reset

Returns

void

Example


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()


setOptions(options): void

Set the options of the pivot table.

Parameters

Parameter	Type	Description
`options`	`IPivotTableOptions`	The options to be set.

Returns

void

Example


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});