Pivot Table

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

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

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

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

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

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

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

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

getConfig(): Nullable<IPivotTableConfig>

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

getFieldDataTypeByColumnIndex(index: number): PivotDataFieldDataTypeEnum | undefined

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

getFieldDataTypeByFieldName(name: string): PivotDataFieldDataTypeEnum | undefined

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

getFieldIdsByArea(fieldArea: PivotTableFiledAreaEnum): string[]

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(fieldId: string): IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON | undefined

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

getSourceFieldsInfo(): IPivotTableDataFieldInfo[]

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

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

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

removeFieldWithName(name: string): void

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

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

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

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

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

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

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

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

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

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

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

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

getValueFilter(fieldId: string): IPivotTableValueFilter | undefined

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(): IValueFilterInfoItem[]

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

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

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

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

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

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

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

getDimensionInfo(): IDimensionInfo | undefined

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

getNameWithColumnIndex(index: number): string

getPivotSourceData(): IDataFieldDataArray

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

getPivotTableId(): string

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(): IRange[] | undefined

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

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

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

pivotTableId: string

reset(): void

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

resetDimension(area: PivotTableFiledAreaEnum): void

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)

setOptions(options: IPivotTableOptions): void

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

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

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

subUnitId: string

unitId: string