# FGenericPivotTable

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

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

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

- Requested language: `zh-CN`

- Content language: `en-US`

- Documentation version: `1.0.0-rc.0`

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

---

Pivot table class (not dependent on workbook)

## Access

Access through:

* [`FUniver.generatePivotTable()`](https://docs.univer.ai/zh-CN/reference/facade/univer.md#generatepivottable)

## Setup

Register [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) or a preset that includes it. In plugin mode, import `@univerjs-pro/sheets-pivot/facade`. Additional methods below require their listed plugin packages. See [Facade setup](https://docs.univer.ai/zh-CN/guides/sheets/getting-started/facade.md).

## `@univerjs-pro/sheets-pivot`

### `FGenericPivotTable.addFieldWithName`

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

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

**Parameters**

* `name` — Required. The display name of the field to be added to the pivot table.
* `area` — Required. The target area in the pivot table where the field should be added.

**Returns**

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('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
// The returned valueField can be used to set the summary mode, display mode, etc.
const valueField = pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)
```

**Types:** [`PivotTableLabelField`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/pivot/table-field.d.ts) · [`PivotTableValueField`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/pivot/table-field.d.ts) · [`PivotTableFiledAreaEnum`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/enum.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.addFilterFieldWithName`

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

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

**Parameters**

* `name` — Required. The display name of the field to be added to the pivot table.
* `options` — Required. The filter configuration

**Returns**

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: univerAPI.Enum.PivotFilterTypeEnum.CustomFilter,
  operator: univerAPI.Enum.PivotFilterOperatorEnum.valueEqual,
  expected: 38,
})
pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
pivot.addFieldWithName('数量', univerAPI.Enum.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('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)
// There will only be two pieces of data at this time
const resNew = pivot.getResultByCalculate().dataArr
console.log('debugger res new', resNew)
```

**Types:** [`PivotTableLabelField`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/pivot/table-field.d.ts) · [`IFGenericPivotFilterOptions`](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/type.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.addValueFieldWithName`

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

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

**Parameters**

* `name` — Required. The display name of the field to be added to the pivot table.
* `options` — Optional. The value configuration

**Returns**

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('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
pivot.addValueFieldWithName('数量', { subtotal: univerAPI.Enum.PivotSubtotalTypeEnum.average })
const res = pivot.getResultByCalculate().dataArr
console.log('debugger res', res)
```

**Types:** [`PivotTableValueField`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/pivot/table-field.d.ts) · [`IPivotTableValueOptions`](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/type.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getDimensionInfo`

Get the dimension information of the current pivot table

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

**Returns**

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('商品', univerAPI.Enum.PivotTableFiledAreaEnum.Column)
pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
const newDimensionInfo = pivot.getDimensionInfo()
console.log('debugger', newDimensionInfo)
```

**Types:** [`IDimensionInfo`](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/type.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getFieldDataTypeByColumnIndex`

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

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

**Parameters**

* `index` — Required. The column number

**Returns**

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) // univerAPI.Enum.PivotDataFieldDataTypeEnum.number
```

**Types:** [`PivotDataFieldDataTypeEnum`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/enum.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getFieldDataTypeByFieldName`

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

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

**Parameters**

* `name` — Required. The display name of the field.

**Returns**

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) // univerAPI.Enum.PivotDataFieldDataTypeEnum.number
```

**Types:** [`PivotDataFieldDataTypeEnum`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/enum.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getLayout`

Get the layout type of the pivot table.

```typescript
getLayout(): PivotLayoutTypeEnum
```

**Returns**

The layout type of the pivot table.

**Examples**

```ts
const pivot = univerAPI.generatePivotTable(sourceData)
const layout = pivot.getLayout()
console.log(layout === univerAPI.Enum.PivotLayoutTypeEnum.tabular)
```

**Types:** [`PivotLayoutTypeEnum`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/enum.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getNameWithColumnIndex`

Returns the table header name corresponding to the column number

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

**Parameters**

* `index` — Required. The column number

**Returns**

The table header name corresponding to the column number

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getPivotSourceData`

Returns the source data used to generate the pivot table

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

**Returns**

The source data used to generate the pivot table.

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

**Types:** [`IDataFieldDataArray`](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/type.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.getResultByCalculate`

Gets the result of the pivot table calculation.
The return value contains the calculated two-dimensional array and the split calculation result.
You can configure whether to display subTotal, grandTotal, etc. according to the input config.

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

**Parameters**

* `config` — Optional. Default: `{}`. The configuration of the pivot table cube.

**Returns**

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('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
const columnField = pivot.addFieldWithName('省份', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
const valueField = pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)
const result = pivot.getResultByCalculate({
  showRowGrandTotal: true,
  showRowSubTotal: true,
}).dataArr
console.log('debugger', result)
```

**Types:** [`IPivotViewValueType`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/layout-type.d.ts) · [`IPivotTableCubeConfig`](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/type.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.remove`

Dispose the pivot table

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

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.removeFieldWithName`

Removes the field from the pivot table by its name.

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

**Parameters**

* `name` — Required. The display name of the field to be removed from the pivot table.

**Examples**

```ts
const sourceData = [
  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],
  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],
  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],
]
const pivot = univerAPI.generatePivotTable(sourceData)
const labelField = pivot.addFieldWithName('区域', univerAPI.Enum.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()
```

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.reset`

Reset all configurations of the pivot table

```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('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
pivot.reset()
// The dimension information returns empty because it is reset.
const newDimensionInfo = pivot.getDimensionInfo()
console.log('debugger', newDimensionInfo)
```

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.resetDimension`

Reset the pivot table configuration for a dimension

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

**Parameters**

* `area` — Required. The target area in the pivot table where be reset

**Examples**

```ts
const sourceData = [
  ['区域', '省份', '城市', '类别', '商品', '数量', '销售日期'],
  ['西部', '河南', '洛阳', 'fruit', '葡萄', 38, '2021-06-30'],
  ['北部', '辽宁', '沈阳', 'fruit', '葡萄', 45, '2023-08-31'],
]
const pivot = univerAPI.generatePivotTable(sourceData)
const rowField = pivot.addFieldWithName('区域', univerAPI.Enum.PivotTableFiledAreaEnum.Row)
const valueField = pivot.addFieldWithName('数量', univerAPI.Enum.PivotTableFiledAreaEnum.Value)
pivot.reset(univerAPI.Enum.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)
```

**Types:** [`PivotTableFiledAreaEnum`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/enum.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.setLayout`

Set the layout type of the pivot table.

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

**Parameters**

* `layout` — Required. The layout type to be set.

**Examples**

```ts
const pivot = univerAPI.generatePivotTable(sourceData)
pivot.setLayout(univerAPI.Enum.PivotLayoutTypeEnum.compact)
```

**Types:** [`PivotLayoutTypeEnum`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/enum.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)

### `FGenericPivotTable.setOptions`

Set the options of the pivot table.

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

**Parameters**

* `options` — Required. The options to be set.

**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, repeatColLabels: true })
```

**Types:** [`IPivotTableOptions`](https://unpkg.com/@univerjs-pro/engine-pivot@1.0.0-rc.0/lib/types/types/layout-type.d.ts)

**Package:** [`@univerjs-pro/sheets-pivot`](https://docs.univer.ai/zh-CN/reference/packages/plugins/univerjs-pro/sheets-pivot.md) · [Type definitions](https://unpkg.com/@univerjs-pro/sheets-pivot@1.0.0-rc.0/lib/types/facade/f-generic-pivot-table.d.ts)