Skip to Content
ClassesFGenericPivotTable

类: FGenericPivotTable

Pivot table class (not dependent on workbook)

方法

addFieldWithName()

addFieldWithName(name, area): any

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

参数

参数类型描述
namestringThe display name of the field to be added to the pivot table.
areaPivotTableFiledAreaEnumThe target area in the pivot table where the field should be added.

返回

any

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

示例

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.

参数

参数类型描述
namestringThe display name of the field to be added to the pivot table.
optionsIFGenericPivotFilterOptionsThe filter configuration

返回

PivotTableLabelField

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

示例

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.

参数

参数类型描述
namestringThe display name of the field to be added to the pivot table.
options?IPivotTableValueOptionsThe value configuration

返回

PivotTableValueField

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

示例

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

返回

IDimensionInfo

The dimension information of the pivot table.

示例

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

参数

参数类型描述
indexnumberThe column number

返回

any

The data type of the field corresponding to the column number

示例

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.

参数

参数类型描述
namestringThe display name of the field.

返回

any

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

示例

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

参数

参数类型描述
indexnumberThe column number

返回

string

The table header name corresponding to the column number

getPivotSourceData()

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

Returns the source data used to generate the pivot table

返回

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

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.

参数

参数类型描述
config?IPivotTableCubeConfigThe configuration of the pivot table cube.

返回

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

The result of the pivot table calculation.

名称类型
dataArr…[]
dataArrWithSplit…[]

示例

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

返回

void

removeFieldWithName()

removeFieldWithName(name): void

Removes the field from the pivot table by its name.

参数

参数类型描述
namestringThe display name of the field to be removed from the pivot table.

返回

void

示例

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

返回

void

示例

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

参数

参数类型描述
areaPivotTableFiledAreaEnumThe target area in the pivot table where be reset

返回

void

示例

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.

参数

参数类型描述
optionsIPivotTableOptionsThe options to be set.

返回

void

示例

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