Skip to Content
ClassesFPivotTable

类: FPivotTable

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

方法

addField()

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

参数

参数类型描述
dataFieldIdOrIndexstring | numberThe data field id.
fieldAreaPivotTableFiledAreaEnumThe area of the field.
indexnumberThe index of the field in the target area.

返回

Promise<boolean>

Whether the pivot field is added successfully.

Description

Add a pivot field to the pivot table.

示例

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

返回

any

The pivot table config or undefined.

Description

Get the pivot table config by the pivot table id.

示例

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[]

参数

参数类型描述
fieldAreaPivotTableFiledAreaEnumThe area of the field.

返回

string[]

The field ids in the target area.

Description

Get the field ids by the field area.

示例

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.

参数

参数类型描述
fieldIdstringThe table field id.

返回

any

The field setting.

示例

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

返回

string

The pivot table id.

Description

Get the pivot table id.

示例

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[]

返回

IRange[]

The pivot table range list.

Description

Get the pivot table range info in worksheet.

示例

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.

返回

IPivotTableDataFieldInfo[]

The field info list.

示例

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.

参数

参数类型描述
fieldIdstringThe field id.

返回

any

The value filter info.

示例

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.

返回

IValueFilterInfoItem[]

The value filter info list.

示例

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>

参数

参数类型描述
sheetNamestringThe target sheet name.
rownumberThe target row index.
colnumberThe target column index.

返回

Promise<boolean>

Whether the pivot table field is moved successfully.

Description

Move the pivot table to the target cell.

示例

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>

返回

Promise<boolean>

Whether the pivot table is removed successfully.

Description

Remove a pivot table from the workbook by pivot table id

示例

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>

参数

参数类型描述
fieldIdsstring[]The deleted field ids.

返回

Promise<boolean>

Whether the pivot field is removed successfully.

Description

Remove a pivot field from the pivot table

示例

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>

参数

参数类型描述
fieldIdstringThe field id.
namestringThe new name of the field.

返回

Promise<boolean>

Whether the pivot table field is renamed successfully.

Description

Rename the pivot table field.

示例

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>

参数

参数类型描述
resetArea?PivotTableFiledAreaEnumThe area of the field to reset or undefined to reset all fields.

返回

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>

参数

参数类型描述
configIPivotTableConfigThe pivot table fields config.

返回

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>

参数

参数类型描述
tableFieldIdstringThe field id of the filter.
itemsstring[]The items of the filter.
isAll?booleanWhether the filter is all.If true, the filter will be all items, the items will be ignored.

返回

Promise<boolean>

Whether the pivot table filter is set successfully.

Description

Set the pivot table manual filter.

示例

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>

参数

参数类型描述
tableFieldIdstringThe field id of the sort.
infoIPivotTableSortInfoThe sort info.

返回

Promise<boolean>

Whether the pivot table sort info is set successfully.

Description

Set the pivot table sort info.

示例

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>

参数

参数类型描述
fieldIdstringThe field id.
subtotalTypePivotSubtotalTypeEnumThe subtotal type of the field.

返回

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.

示例

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>

参数

参数类型描述
fieldIdstringThe field id of the filter. Only one value filer can be set for a field.
filterInfoOmit<IPivotTableValueFilter, "type">The filter info. The undefined value will be removed from the old filter.

返回

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.

示例

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>

参数

参数类型描述
fieldIdstringThe moved field id.
areaPivotTableFiledAreaEnumThe target area of the field.
indexnumberThe 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.

返回

Promise<boolean>

Whether the pivot field is moved successfully.

Description

Update the pivot table field position.

示例

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>

参数

参数类型描述
positionPivotTableValuePositionEnumThe position of the value field.
indexnumberThe index of the value field.

返回

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.

示例

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