Class: FPivotTable

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:7

Constructors

new FPivotTable()

new FPivotTable(
   unitId, 
   subUnitId, 
   pivotTableId, 
   injector): FPivotTable

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:13

Parameters

Parameter	Type
`unitId`	`string`
`subUnitId`	`string`
`pivotTableId`	`string`
`injector`	`Injector`

Returns

FPivotTable

Properties

Property	Type	Defined in
`pivotTableId`	`string`	packages/sheets-pivot/src/facade/f-pivot-table.ts:10
`subUnitId`	`string`	packages/sheets-pivot/src/facade/f-pivot-table.ts:9
`unitId`	`string`	packages/sheets-pivot/src/facade/f-pivot-table.ts:8

Methods

addField()

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

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:196

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();
const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
 
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(): Nullable<IPivotTableConfig>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:40

Returns

Nullable<IPivotTableConfig>

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
const pivotTableConfig = fPivotTable.getConfig();
const { targetCellInfo, sourceRangeInfo, isEmpty } = pivotTableConfig;
console.log(targetCellInfo, sourceRangeInfo, isEmpty);

getFieldIdsByArea()

getFieldIdsByArea(fieldArea): string[]

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:103

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
const fieldIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row);
console.log(fieldIds);

getFieldSetting()

getFieldSetting(fieldId): 
  | undefined
  | IPivotTableValueFieldJSON
  | IPivotTableLabelFieldJSON

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:78

Get the pivot table field setting by the field id.

Parameters

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

Returns

| undefined | IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON

The field setting.

Example

const fWorkbook = univerAPI.getActiveWorkbook();
const unitId = fWorkbook.getId();
const fSheet = fWorkbook.getActiveSheet();
const subUnitId = fSheet.getSheetId();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
const fieldSetting = pivotTable.getFieldSetting('fieldId');

getPivotTableId()

getPivotTableId(): string

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:60

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
const pivotTableId = fPivotTable.getPivotTableId();
console.log(pivotTableId);

getPivotTableRangeInfo()

getPivotTableRangeInfo(): 
  | undefined
  | IRange[]

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:141

Returns

| undefined | 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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if(fPivotTable) {
 const pivotTableRangeInfo = fPivotTable.getPivotTableRangeInfo();
 console.log(pivotTableRangeInfo);
}

move()

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

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:499

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 subUnitId = fSheet.getSheetId();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
 // move pivot table to row:100, col:1
 pivotTable.move(subUnitId, 100, 1);
}

remove()

remove(): Promise<boolean>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:166

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();
const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
 
if(fPivotTable) {
    fPivotTable.remove();
}

removeField()

removeField(fieldIds): Promise<boolean>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:247

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
    const rowIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row);
    if (rowIds.length > 0) {
        // remove all field in row.
        pivotTable.removeField(rowIds);
    }
}

renameField()

renameField(fieldId, name): Promise<boolean>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:464

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
  const valueIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value);
  if (valueIds.length > 0) {
    pivotTable.renameField(valueIds[0], 'newName');
  }
}

setLabelManualFilter()

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

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:427

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
    const rowIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row);
    if (rowIds.length > 0) {
        pivotTable.setLabelManualFilter(rowIds[0], ['item1', 'item2']);
    }
}

setLabelSort()

setLabelSort(tableFieldId, info): Promise<boolean>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:390

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
 const rowIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row);
 if (rowIds.length > 0) {
     pivotTable.setLabelSort(rowIds[0], { type: univerAPI.Enum.PivotDataFieldSortOperatorEnum.Ascending });
 }
}

setSubtotalType()

setSubtotalType(fieldId, subtotalType): Promise<boolean>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:352

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
  pivotTable.setSubtotalType('fieldId', univerAPI.Enum.PivotSubtotalTypeEnum.Average);
}

updateFieldPosition()

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

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:284

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if (pivotTable) {
    const rowIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row);
    if (rowIds.length > 0) {
        // move to column
        pivotTable.updateFieldPosition(rowIds[0], univerAPI.Enum.PivotTableFiledAreaEnum.Column, 0);
    }
}

updateValuePosition()

updateValuePosition(position, index): Promise<boolean>

Defined in: packages/sheets-pivot/src/facade/f-pivot-table.ts:319

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();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 1, 1);
if(pivotTable) {
// if there are more than one value field, a ΣValue will in row area.
  pivotTable.updateValuePosition(univerAPI.Enum.PivotTableValuePositionEnum.Row, 0);
}

Readme Fpivottableevent