Class: FPivotTable

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

Methods

addField()


addField(
   dataFieldIdOrIndex, 
   fieldArea, 
index): Promise<boolean>

Parameters

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

Description

Add a pivot field to the pivot table.

Example


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


getConfig(): any

Returns

any

The pivot table config or undefined.

Description

Get the pivot table config by the pivot table id.

Example


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


getFieldIdsByArea(fieldArea): string[]

Parameters

Parameter	Type	Description
`fieldArea`	`PivotTableFiledAreaEnum`	The area of the field.

Returns

string[]

The field ids in the target area.

Description

Get the field ids by the field area.

Example


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


getFieldSetting(fieldId): any

Get the pivot table field setting by the field id.

Parameters

Parameter	Type	Description
`fieldId`	`string`	The table field id.

Returns

any

The field setting.

Example


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


getPivotTableId(): string

Returns

string

The pivot table id.

Description

Get the pivot table id.

Example


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


getPivotTableRangeInfo(): IRange[]

Returns

IRange[]

The pivot table range list.

Description

Get the pivot table range info in worksheet.

Example


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


getSourceFieldsInfo(): IPivotTableDataFieldInfo[]

Get the pivot table field info list.

Returns

IPivotTableDataFieldInfo[]

The field info list.

Example


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


getValueFilter(fieldId): any

Get value filter info by the field id.

Parameters

Parameter	Type	Description
`fieldId`	`string`	The field id.

Returns

any

The value filter info.

Example


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


getValueFilters(): IValueFilterInfoItem[]

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.

Returns

IValueFilterInfoItem[]

The value filter info list.

Example


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


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

Parameters

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

Description

Move the pivot table to the target cell.

Example


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


remove(): Promise<boolean>

Returns

Promise<boolean>

Whether the pivot table is removed successfully.

Description

Remove a pivot table from the workbook by pivot table id

Example


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


removeField(fieldIds): Promise<boolean>

Parameters

Parameter	Type	Description
`fieldIds`	`string`[]	The deleted field ids.

Returns

Promise<boolean>

Whether the pivot field is removed successfully.

Description

Remove a pivot field from the pivot table

Example


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


renameField(fieldId, name): Promise<boolean>

Parameters

Parameter	Type	Description
`fieldId`	`string`	The field id.
`name`	`string`	The new name of the field.

Returns

Promise<boolean>

Whether the pivot table field is renamed successfully.

Description

Rename the pivot table field.

Example


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


reset(resetArea?): Promise<boolean>

Parameters

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

Description

Clear the fields by provided field area or clear all fields.

setFieldsConfig()


setFieldsConfig(config): Promise<boolean>

Parameters

Parameter	Type	Description
`config`	`IPivotTableConfig`	The pivot table fields config.

Returns

Promise<boolean>

Whether the pivot table fields config is set successfully.

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


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

Parameters

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

Description

Set the pivot table manual filter.

Example


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


setLabelSort(tableFieldId, info): Promise<boolean>

Parameters

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

Description

Set the pivot table sort info.

Example


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


setSubtotalType(fieldId, subtotalType): Promise<boolean>

Parameters

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

Description

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

Example


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


setValueFilter(fieldId, filterInfo): Promise<boolean>

Parameters

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

Description

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

Example


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


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

Parameters

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

Description

Update the pivot table field position.

Example


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


updateValuePosition(position, index): Promise<boolean>

Parameters

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

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.

Example


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