# Pivot Table

> Language fallback: requested `zh-CN`; content is `en-US`.

- Human documentation: [https://docs.univer.ai/zh-CN/reference/facade/pivot-table](https://docs.univer.ai/zh-CN/reference/facade/pivot-table)

- Agent Markdown: [https://docs.univer.ai/zh-CN/reference/facade/pivot-table.md](https://docs.univer.ai/zh-CN/reference/facade/pivot-table.md)

- Requested language: `zh-CN`

- Content language: `en-US`

- Documentation version: `1.0.0-rc.0`

- Source: [facade/pivot-table.mdx](https://github.com/dream-num/documentation/blob/dev/content/reference/facade/pivot-table.mdx)

---

| Packages | `@univerjs-pro/sheets-pivot` |
| -------- | ---------------------------- |

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

> This class should not be instantiated directly. Use factory methods on `univerAPI` instead.

## Overview

### @univerjs-pro/sheets-pivot

| Method                                                            | Description                                                                        |
| ----------------------------------------------------------------- | ---------------------------------------------------------------------------------- |
| [`addField`](#addfield)                                           | -                                                                                  |
| [`addFieldWithName`](#addfieldwithname)                           | Adds a field to the pivot table by its name and assigns it to the specified area   |
| [`addFilterFieldWithName`](#addfilterfieldwithname)               | Adds a field to the pivot table by its name and assigns it to the filter dimension |
| [`addValueFieldWithName`](#addvaluefieldwithname)                 | Adds a field to the pivot table by its name and assigns it to the value measure    |
| [`getConfig`](#getconfig)                                         | -                                                                                  |
| [`getDimensionInfo`](#getdimensioninfo)                           | Get the dimension information of the current pivot table                           |
| [`getFieldDataTypeByColumnIndex`](#getfielddatatypebycolumnindex) | Get the data type of the field corresponding to the column number                  |
| [`getFieldDataTypeByFieldName`](#getfielddatatypebyfieldname)     | Get the data type of the field corresponding to the field name                     |
| [`getFieldIdsByArea`](#getfieldidsbyarea)                         | -                                                                                  |
| [`getFieldSetting`](#getfieldsetting)                             | Get the pivot table field setting by the field id                                  |
| `getLayout`                                                       | Get the pivot table layout type                                                    |
| [`getNameWithColumnIndex`](#getnamewithcolumnindex)               | Returns the table header name corresponding to the column number                   |
| `getOptions`                                                      | Get the display options of the pivot table                                         |
| `getPivotTableMatrixInfo`                                         | Get the pivot table matrix information                                             |
| [`getPivotSourceData`](#getpivotsourcedata)                       | Returns the source data used to generate the pivot table                           |
| [`getPivotTableId`](#getpivottableid)                             | -                                                                                  |
| [`getPivotTableRangeInfo`](#getpivottablerangeinfo)               | -                                                                                  |
| [`getResultByCalculate`](#getresultbycalculate)                   | Gets the result of the pivot table calculation                                     |
| `getSourceRangeInfo`                                              | Get the source range information                                                   |
| [`getSourceFieldsInfo`](#getsourcefieldsinfo)                     | Get the pivot table field info list                                                |
| `getTargetCellInfo`                                               | Get the target cell information                                                    |
| [`getValueFilter`](#getvaluefilter)                               | Get value filter info by the field id                                              |
| [`getValueFilters`](#getvaluefilters)                             | Get all value filters of the pivot table                                           |
| [`move`](#move)                                                   | -                                                                                  |
| [`pivotTableId`](#pivottableid)                                   | -                                                                                  |
| [`remove`](#remove)                                               | Dispose the pivot table                                                            |
| [`removeField`](#removefield)                                     | -                                                                                  |
| [`removeFieldWithName`](#removefieldwithname)                     | Removes the field from the pivot table by its name                                 |
| [`renameField`](#renamefield)                                     | -                                                                                  |
| [`reset`](#reset)                                                 | Reset all configurations of the pivot table                                        |
| [`resetDimension`](#resetdimension)                               | Reset the pivot table configuration for a dimension                                |
| [`setDateGroupType`](#setdategrouptype)                           | -                                                                                  |
| [`setFieldsConfig`](#setfieldsconfig)                             | -                                                                                  |
| [`setLabelManualFilter`](#setlabelmanualfilter)                   | -                                                                                  |
| [`setLabelSort`](#setlabelsort)                                   | -                                                                                  |
| `setLayout`                                                       | Set the pivot table layout type                                                    |
| [`setOptions`](#setoptions)                                       | Set the options of the pivot table                                                 |
| `setSourceRange`                                                  | Set the source range of the pivot table                                            |
| [`setSubtotalType`](#setsubtotaltype)                             | -                                                                                  |
| [`setValueFilter`](#setvaluefilter)                               | -                                                                                  |
| `clearValueFilter`                                                | Clear the value filter for a field                                                 |
| `drillDown`                                                       | Drill down from a pivot table cell                                                 |
| `setCellCollapse`                                                 | Collapse or expand a pivot table cell                                              |
| `setFieldSetting`                                                 | Update display name, number format, subtotal, or Show Values As settings           |
| [`subUnitId`](#subunitid)                                         | -                                                                                  |
| [`unitId`](#unitid)                                               | -                                                                                  |
| `updateSourceRange`                                               | Alias of `setSourceRange`                                                          |
| [`updateFieldPosition`](#updatefieldposition)                     | -                                                                                  |
| [`updateValuePosition`](#updatevalueposition)                     | -                                                                                  |

## APIs

### Config & Fields

### Additional Pivot APIs

The following APIs are available on `FPivotTable` instances returned from workbook or worksheet pivot table lookup APIs.

```typescript
getSourceRangeInfo(): IUnitRangeNameWithSubUnitId | undefined
getTargetCellInfo(): IPivotTableConfig['targetCellInfo'] | undefined
getOptions(): IPivotTableOptions | undefined
getLayout(): PivotLayoutTypeEnum | undefined
getPivotTableMatrixInfo(): IPivotTableMatrixInfo | undefined
setLayout(layout: PivotLayoutTypeEnum): Promise<boolean>
setSourceRange(dataRangeInfo: IUnitRangeNameWithSubUnitId): Promise<boolean>
updateSourceRange(dataRangeInfo: IUnitRangeNameWithSubUnitId): Promise<boolean>
setFieldSetting(tableFieldId: string, setting: IPivotTableFieldSettingOptions): Promise<boolean>
getShowDataAs(fieldId: string): IPivotTableShowDataAsInfo | undefined
setShowDataAs(fieldId: string, showDataAs: IPivotTableShowDataAsInfo): Promise<boolean>
resetShowDataAs(fieldId: string): Promise<boolean>
clearValueFilter(fieldId: string): Promise<boolean>
setCellCollapse(row: number, col: number, collapse: boolean): Promise<boolean>
drillDown(row: number, col: number): Promise<boolean>
```

`setFieldSetting()` also accepts `showDataAs`. These settings apply only to value fields. See [Show values as](https://docs.univer.ai/zh-CN/guides/sheets/features/pivot-table.md#show-values-as) for an example.

`FGenericPivotTable`, returned by `univerAPI.generatePivotTable`, also supports direct layout reads and updates:

```typescript
getLayout(): PivotLayoutTypeEnum
setLayout(layout: PivotLayoutTypeEnum): void
```

Source: 

`@univerjs-pro/sheets-pivot`

### `addField`

**Signature**

```typescript
async addField(dataFieldIdOrIndex: string | number, fieldArea: PivotTableFiledAreaEnum, index: number): Promise<boolean>
```

**Parameters**

* `dataFieldIdOrIndex` `string | number` — *No description*
* `fieldArea` `PivotTableFiledAreaEnum` — *No description*
* `index` `number` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot field is added successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `addFieldWithName`

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

**Signature**

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

**Parameters**

* `name` `string` — *No description*
* `area` `PivotTableFiledAreaEnum` — *No description*

**Returns**

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

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `addFilterFieldWithName`

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

**Signature**

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

**Parameters**

* `name` `string` — *No description*
* `options` `IFGenericPivotFilterOptions` — *No description*

**Returns**

* `PivotTableLabelField` — The field instance that has been added to the pivot table.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `addValueFieldWithName`

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

**Signature**

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

**Parameters**

* `name` `string` — *No description*
* `options` `IPivotTableValueOptions` *(optional)* — *No description*

**Returns**

* `PivotTableValueField` — The field instance that has been added to the pivot table.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getConfig`

**Signature**

```typescript
getConfig(): Nullable<IPivotTableConfig>
```

**Returns**

* `Nullable<IPivotTableConfig>` — The pivot table config or undefined.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getFieldDataTypeByColumnIndex`

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

**Signature**

```typescript
getFieldDataTypeByColumnIndex(index: number): PivotDataFieldDataTypeEnum | undefined
```

**Parameters**

* `index` `number` — *No description*

**Returns**

* `PivotDataFieldDataTypeEnum` — The data type of the field corresponding to the column number

**Examples**

```ts
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
```

Source: 

`@univerjs-pro/sheets-pivot`

### `getFieldDataTypeByFieldName`

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

**Signature**

```typescript
getFieldDataTypeByFieldName(name: string): PivotDataFieldDataTypeEnum | undefined
```

**Parameters**

* `name` `string` — *No description*

**Returns**

* `PivotDataFieldDataTypeEnum` — The data type of the field corresponding to the field name.

**Examples**

```ts
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
```

Source: 

`@univerjs-pro/sheets-pivot`

### `getFieldIdsByArea`

**Signature**

```typescript
getFieldIdsByArea(fieldArea: PivotTableFiledAreaEnum): string[]
```

**Parameters**

* `fieldArea` `PivotTableFiledAreaEnum` — *No description*

**Returns**

* `string[]` — The field ids in the target area.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getFieldSetting`

Get the pivot table field setting by the field id.

**Signature**

```typescript
getFieldSetting(fieldId: string): IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON | undefined
```

**Parameters**

* `fieldId` `string` — *No description*

**Returns**

* `IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON` — The field setting.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getSourceFieldsInfo`

Get the pivot table field info list.

**Signature**

```typescript
getSourceFieldsInfo(): IPivotTableDataFieldInfo[]
```

**Returns**

* `IPivotTableDataFieldInfo[]` — The field info list.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `removeField`

**Signature**

```typescript
async removeField(fieldIds: string[]): Promise<boolean>
```

**Parameters**

* `fieldIds` `string[]` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot field is removed successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `removeFieldWithName`

Removes the field from the pivot table by its name.

**Signature**

```typescript
removeFieldWithName(name: string): void
```

**Parameters**

* `name` `string` — *No description*

**Examples**

```ts
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();
```

Source: 

`@univerjs-pro/sheets-pivot`

### `renameField`

**Signature**

```typescript
async renameField(fieldId: string, name: string): Promise<boolean>
```

**Parameters**

* `fieldId` `string` — *No description*
* `name` `string` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table field is renamed successfully.

**Examples**

```ts
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');
  }
}
```

Source: 

`@univerjs-pro/sheets-pivot`

### `setFieldsConfig`

**Signature**

```typescript
setFieldsConfig(config: IPivotTableConfig['fieldsConfig']): Promise<boolean>
```

**Parameters**

* `config` `IPivotTableConfig` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table fields config is set successfully.

Source: 

`@univerjs-pro/sheets-pivot`

### `updateFieldPosition`

**Signature**

```typescript
async updateFieldPosition(fieldId: string, area: PivotTableFiledAreaEnum, index: number): Promise<boolean>
```

**Parameters**

* `fieldId` `string` — *No description*
* `area` `PivotTableFiledAreaEnum` — *No description*
* `index` `number` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot field is moved successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### Layout

### `move`

**Signature**

```typescript
async move(sheetName: string, row: number, col: number): Promise<boolean>
```

**Parameters**

* `sheetName` `string` — *No description*
* `row` `number` — *No description*
* `col` `number` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table field is moved successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `remove`

Dispose the pivot table

**Signature**

```typescript
remove(): void
```

Source: 

`@univerjs-pro/sheets-pivot`

### `setDateGroupType`

**Signature**

```typescript
async setDateGroupType(tableFieldId: string, dateType: PivotDateGroupFieldDateTypeEnum): Promise<boolean>
```

**Parameters**

* `tableFieldId` `string` — *No description*
* `dateType` `PivotDateGroupFieldDateTypeEnum` — *No description*

**Returns**

* `Promise<boolean>` — Whether the date group type is set successfully.

**Examples**

```ts
// To use this demo, you need to build a pivot table first, and make sure that the entire column in the pivot table source data is a date,
// and drag the date dimension to the row or column
const fWorkbook = univerAPI.getActiveWorkbook();
const unitId = fWorkbook.getId();
const fSheet = fWorkbook.getActiveSheet();
const subUnitId = fSheet.getSheetId();
const pivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 0);
// Here we assume that the field index of the date type is 0
await pivotTable.addField(0, univerAPI.Enum.PivotTableFiledAreaEnum.Row, 0);
const rowIds = pivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row);
if (rowIds.length > 0) {
  // Because the index is added to the position of 0 above, the index of the automatically derived date grouping dimension is 1
  pivotTable.setDateGroupType(rowIds[1], univerAPI.Enum.PivotDateGroupFieldDateTypeEnum.YearMonthDate);
}
```

Source: 

`@univerjs-pro/sheets-pivot`

### `updateValuePosition`

**Signature**

```typescript
async updateValuePosition(position: PivotTableValuePositionEnum, index: number): Promise<boolean>
```

**Parameters**

* `position` `PivotTableValuePositionEnum` — *No description*
* `index` `number` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot value field is moved successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### Filter & Sort

### `getValueFilter`

Get value filter info by the field id.

**Signature**

```typescript
getValueFilter(fieldId: string): IPivotTableValueFilter | undefined
```

**Parameters**

* `fieldId` `string` — *No description*

**Returns**

* `IPivotTableValueFilter` — The value filter info.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getValueFilters`

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.

**Signature**

```typescript
getValueFilters(): IValueFilterInfoItem[]
```

**Returns**

* `IValueFilterInfoItem[]` — The value filter info list.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `setLabelManualFilter`

**Signature**

```typescript
async setLabelManualFilter(tableFieldId: string, items: string[], isAll?: boolean): Promise<boolean>
```

**Parameters**

* `tableFieldId` `string` — *No description*
* `items` `string[]` — *No description*
* `isAll` `boolean` *(optional)* — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table filter is set successfully.

**Examples**

```ts
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']);
    }
}
```

Source: 

`@univerjs-pro/sheets-pivot`

### `setLabelSort`

**Signature**

```typescript
async setLabelSort(tableFieldId: string, info: IPivotTableSortInfo): Promise<boolean>
```

**Parameters**

* `tableFieldId` `string` — *No description*
* `info` `IPivotTableSortInfo` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table sort info is set successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `setValueFilter`

**Signature**

```typescript
async setValueFilter(fieldId: string, filterInfo: Omit<IPivotTableValueFilter, 'type'>): Promise<boolean>
```

**Parameters**

* `fieldId` `string` — *No description*
* `filterInfo` `Omit<IPivotTableValueFilter, "type">` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table value filter is set successfully.

**Examples**

```ts
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)
}
```

Source: 

`@univerjs-pro/sheets-pivot`

### Miscellaneous

### `getDimensionInfo`

Get the dimension information of the current pivot table

**Signature**

```typescript
getDimensionInfo(): IDimensionInfo | undefined
```

**Returns**

* `IDimensionInfo` — The dimension information of the pivot table.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getNameWithColumnIndex`

Returns the table header name corresponding to the column number

**Signature**

```typescript
getNameWithColumnIndex(index: number): string
```

**Parameters**

* `index` `number` — *No description*

**Returns**

* `string` — The table header name corresponding to the column number

Source: 

`@univerjs-pro/sheets-pivot`

### `getPivotSourceData`

Returns the source data used to generate the pivot table

**Signature**

```typescript
getPivotSourceData(): IDataFieldDataArray
```

**Returns**

* `[string[], ...(IDataFieldValue & Date)[][]]` — The source data used to generate the pivot table.

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getPivotTableId`

**Signature**

```typescript
getPivotTableId(): string
```

**Returns**

* `string` — The pivot table id.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getPivotTableRangeInfo`

**Signature**

```typescript
getPivotTableRangeInfo(): IRange[] | undefined
```

**Returns**

* `IRange[]` — The pivot table range list.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `getResultByCalculate`

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**

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

**Parameters**

* `config` `IPivotTableCubeConfig` *(optional)* — *No description*

**Returns**

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

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `pivotTableId`

**Signature**

```typescript
pivotTableId: string
```

**Returns**

* `string` — See signature above.

Source: 

`@univerjs-pro/sheets-pivot`

### `reset`

Reset all configurations of the pivot table

**Signature**

```typescript
reset(): void
```

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `resetDimension`

Reset the pivot table configuration for a dimension

**Signature**

```typescript
resetDimension(area: PivotTableFiledAreaEnum): void
```

**Parameters**

* `area` `PivotTableFiledAreaEnum` — *No description*

**Examples**

```ts
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)
```

Source: 

`@univerjs-pro/sheets-pivot`

### `setOptions`

Set the options of the pivot table.

**Signature**

```typescript
setOptions(options: IPivotTableOptions): void
```

**Parameters**

* `options` `IPivotTableOptions` — *No description*

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `setSubtotalType`

**Signature**

```typescript
async setSubtotalType(fieldId: string, subtotalType: PivotSubtotalTypeEnum): Promise<boolean>
```

**Parameters**

* `fieldId` `string` — *No description*
* `subtotalType` `PivotSubtotalTypeEnum` — *No description*

**Returns**

* `Promise<boolean>` — Whether the pivot table subtotal type is set successfully.

**Examples**

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

Source: 

`@univerjs-pro/sheets-pivot`

### `subUnitId`

**Signature**

```typescript
subUnitId: string
```

**Returns**

* `string` — See signature above.

Source: 

`@univerjs-pro/sheets-pivot`

### `unitId`

**Signature**

```typescript
unitId: string
```

**Returns**

* `string` — See signature above.

Source: 

`@univerjs-pro/sheets-pivot`