数据透视表

univer.registerPlugin(UniverSheetsPivotTablePlugin)
univer.registerPlugin(UniverSheetsPivotTableUIPlugin)

// 浏览器主进程插件注册文件
// notExecuteFormula 为 true 表示主进程不执行计算，只负责数据的分发、同步和渲染
univer.registerPlugin(UniverSheetsPivotTablePlugin, { notExecuteFormula: true })
univer.registerPlugin(UniverSheetsPivotTableUIPlugin)

// web worker 进程插件注册文件
// notExecuteFormula 为 false 表示 worker 进程执行计算
univer.registerPlugin(UniverSheetsPivotTablePlugin, { notExecuteFormula: false })

// 需要确保工作表范围 A1:G9 是非空的
const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.getSheetId()
const sheetName = fSheet.getSheetName()
const sourceInfo = {
  unitId,
  subUnitId,
  sheetName,
  range: {
    startRow: 0,
    startColumn: 0,
    endRow: 8,
    endColumn: 6,
  },
}
const anchorCellInfo = {
  unitId,
  subUnitId,
  row: 20,
  col: 8,
}
const fPivotTable = await fWorkbook.addPivotTable(sourceInfo, univerAPI.Enum.PositionTypeEnum.Existing, anchorCellInfo)
const pivotTableId = fPivotTable.getPivotTableId()
// 设置状态，避免重复添加维度
let hasAdded = false
// 透视表的创建是异步的，因此需要等待透视表渲染完成后再添加维度
univerAPI.addEvent(univerAPI.Event.PivotTableRendered, (params) => {
  if (!hasAdded && params.pivotTableId === pivotTableId) {
    fPivotTable.addField(1, univerAPI.Enum.PivotTableFiledAreaEnum.Row, 0)
    fPivotTable.addField(1, univerAPI.Enum.PivotTableFiledAreaEnum.Value, 0)
    hasAdded = true
  }
})

const fWorkbook = univerAPI.getActiveWorkbook()
const unitId = fWorkbook.getId()
const fSheet = fWorkbook.getActiveSheet()
const subUnitId = fSheet.getSheetId()

// 从 FWorkbook 中获取透视表实例
const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8)

// 从 FSheet 中获取透视表实例
const pivotTable2 = fSheet.getPivotTableByCell(0, 8)

class FPivotTable {
/**
 * @description Get the pivot table config by the pivot table id.
 * @typedef PivotTableConfig
 * @property {TargetInfo} targetCellInfo  The target cell info of the pivot table.
 * @property {SourceInfo} sourceRangeInfo The source data range info of the pivot table.
 * @property {boolean} isEmpty The pivot table is empty or not.
 * @property {object} fieldsConfig The snapshot of the pivot table fields config.
 * @returns {PivotTableConfig|undefined} The pivot table config or undefined.
 */
  getConfig(): IPivotTableConfig
  /**
   * @description Remove a pivot table from the workbook by pivot table id
   */
  async remove(): void
  /**
   *@description Add a pivot field to the pivot table.
   * @param {string|number} dataFieldIdOrIndex The data field id.
   * @param {PivotTableFiledAreaEnum} fieldArea The area of the field.
   * @param {number} index The index of the field in the target area.
   * @returns {boolean} Whether the pivot field is added successfully.
   */
  async addField(dataFieldIdOrIndex: string | number, fieldArea: PivotTableFiledAreaEnum, index: number): Promise<boolean>
  /**
   * @description Remove a pivot field from the pivot table
   * @param {string[]} fieldIds The deleted field ids.
   * @returns {boolean} Whether the pivot field is removed successfully.
   */
  async removeField(fieldIds: string[]): Promise<boolean>
  /**
   * @description Update the pivot table field position.
   * @param {string} fieldId - The moved field id.
   * @param {PivotTableFiledAreaEnum} area - The target area of the field.
   * @param {number} index - The target index of the field, if the index is bigger than the field count in the target area, the field will be moved to the last, if the index is smaller than 0, the field will be moved to the first.
   * @returns {boolean} Whether the pivot field is moved successfully.
   */
  async updateFieldPosition(fieldId: string, area: PivotTableFiledAreaEnum, index: number): Promise<boolean>
  /**
   * @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.
   * @param {PivotTableValuePositionEnum} position - The position of the value field.
   * @param {number} index - The index of the value field.
   * @returns {boolean} Whether the pivot value field is moved successfully.
   */
  async updateValuePosition(position: PivotTableValuePositionEnum, index: number): Promise<boolean>
  /**
   * @description Set the pivot table subtotal type for value field, it only works for the value field.
   * @param {string} fieldId - The field id.
   * @param {PivotSubtotalTypeEnum} subtotalType - The subtotal type of the field.
   * @returns {boolean} Whether the pivot table subtotal type is set successfully.
   */
  async setSubtotalType(fieldId: string, subtotalType: PivotSubtotalTypeEnum): Promise<boolean>
  /**
   * @description Set the pivot table sort info.
   * @param {string} tableFieldId - The field id of the sort.
   * @param {PivotTableSortInfo} info - The sort info.
   * @typedef PivotTableSortInfo
   * @property {PivotDataFieldSortOperatorEnum} type The sort operator of the field items.
   * @returns {boolean} Whether the pivot table sort info is set successfully.
   */
  async setLabelSort(tableFieldId: string, info: IPivotTableSortInfo): Promise<boolean>
  /**
   * @description Set the pivot table filter.
   * @param {string} tableFieldId - The field id of the filter.
   * @param {string[]} items - The items of the filter.
   * @returns {boolean} Whether the pivot table filter is set successfully.
   */
  async setLabelFilter(tableFieldId: string, items: string[], isAll?: boolean): Promise<boolean>
  /**
   * @description Rename the pivot table field.
   * @param {string} fieldId - The field id.
   * @param {string} name - The new name of the field.
   * @returns {boolean} Whether the pivot table field is renamed successfully.
   */
  async renameField(fieldId: string, name: string): Promise<boolean>
  /**
   * @description Set the pivot table value filter. A value filter is used to filter the data based on the value of a field.
   * @param {string} fieldId - The field id of the filter. Only one value filer can be set for a field.
   * @param {Omit<IPivotTableValueFilter, 'type'>} filterInfo - The filter info. The undefined value will be removed from the old filter.
   * @typedef filterInfo
   * @property {valueGreaterThan} operator - The filter operator is used to compare the value of the field with the expected value.Currently, The following operators are supported: valueBetween, valueEqual, valueGreaterThan, valueGreaterThanOrEqual, valueLessThan, valueLessThanOrEqual,valueNotBetween,valueNotEqual.
   * @property {number} expected - The expected value.
   * @property {string} valueFieldId - The value field id.
   * @returns {boolean} Whether the pivot table value filter is set successfully.
   */
  async setValueFilter(fieldId: string, filterInfo: Omit<IPivotTableValueFilter, 'type'>): Promise<boolean>
  /**
   * @description Clear the fields by provided field area or clear all fields.
   * @param {PivotTableFiledAreaEnum} [resetArea] The area of the field to reset or undefined to reset all fields.
   * @returns {Promise<boolean>} Whether the pivot table fields are reset successfully.
   */
  reset(resetArea?: PivotTableFiledAreaEnum): Promise<boolean>
  /**
   * @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.
   * @param {IPivotTableConfig['fieldsConfig']} config The pivot table fields config.
   * @returns {Promise<boolean>} Whether the pivot table fields config is set successfully.
   */
  setFieldsConfig(config: IPivotTableConfig['fieldsConfig']): Promise<boolean>
  /**
   * 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.
   * @returns {IValueFilterInfoItem[]} The value filter info list.
   */
  getValueFilters(): IValueFilterInfoItem[]
}

const disposable = univerAPI.addEvent(univerAPI.Event.BeforePivotTableAdd, (params) => {
  const { positionType, targetCellInfo } = params
  if (positionType === univerAPI.Enum.PositionTypeEnum.Existing && targetCellInfo.sheetName === 'Sheet 1') {
    // 取消添加透视表操作
    params.cancel = true
    console.log(`无法将透视表添加到 ${targetCellInfo.sheetName} 工作表`)
  }
})

// 移除事件监听器，使用 `disposable.dispose()`

const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableAdded, (params) => {
  const { positionType, targetCellInfo } = params
  if (positionType === univerAPI.Enum.PositionTypeEnum.Existing) {
    console.log('在现有工作表中创建了一个透视表')
    console.log(`
      目标工作表名称为 ${targetCellInfo.sheetName},
      目标行为 ${targetCellInfo.row}, 目标列为 ${targetCellInfo.column}
    `)
  } else {
    console.log('在新工作表中创建了一个透视表')
  }
})

// 移除事件监听器，使用 `disposable.dispose()`

const disposable = univerAPI.addEvent(univerAPI.Event.BeforePivotTableMove, (params) => {
  const { pivotTableId, targetCellInfo } = params
  if (pivotTableId === 'pivotTable1' && targetCellInfo.sheetName === 'Sheet 1') {
    // 取消移动透视表操作
    params.cancel = true
    console.log(`无法将透视表移动到 ${targetCellInfo.sheetName} 工作表`)
  }
})

// 移除事件监听器，使用 `disposable.dispose()`

const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableMoved, (params) => {
  const { pivotTableId, targetCellInfo, originTargetInfo } = params
  if (pivotTableId === 'pivotTable1') {
    console.log(`
      透视表从行: ${originTargetInfo.row} & 列: ${originTargetInfo.column}
      移动到工作表 ${targetCellInfo.sheetName} 行: ${targetCellInfo.row} & 列: ${targetCellInfo.column}
    `)
  }
})

// 移除事件监听器，使用 `disposable.dispose()`

// import { unionPivotViewRange } from '@univerjs-pro/sheets-pivot';
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableRendered, (params) => {
  const { pivotTableId, rangeInfo } = params
  console.log(`透视表 ${pivotTableId} 已渲染`)
  if (rangeInfo) {
    console.log(unionPivotViewRange(rangeInfo))
  }
})

// 移除事件监听器，使用 `disposable.dispose()`

const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableRemoved, (params) => {
  const { pivotTableId } = params
  console.log(`透视表 ${pivotTableId} 已被删除`)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldAdded, (params) => {
  const { pivotTableId, fieldId, fieldArea, fieldIndex } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting('fieldId')
  console.log(fieldSetting)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldRemoved, (params) => {
  const { pivotTableId, fieldId } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting(fieldId)
  console.log(fieldSetting)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldMoved, (params) => {
  const { pivotTableId, fieldId, fieldArea, fieldIndex } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting(fieldId)
  console.log(`源名称 ${fieldSetting.sourceName} 显示名称 ${fieldSetting.displayName}`)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldCollapseChanged, (params) => {
  const { pivotTableId, fieldId, isCollapsed } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting(fieldId)
  console.log(`源名称 ${fieldSetting.sourceName} 显示名称 ${fieldSetting.displayName}`)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldFilterChanged, (params) => {
  const { pivotTableId, fieldId, filter } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting(fieldId)
  console.log(`检查列表筛选值为 ${fieldSetting.filterInfo.checklist}`)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldSortChanged, (params) => {
  const { pivotTableId, fieldId, sort } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting(fieldId)
  console.log(`排序信息为 ${fieldSetting.sortInfo}`)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableFieldSettingChanged, (params) => {
  const { pivotTableId, fieldId, setting } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const fieldSetting = pivotTable.getFieldSetting(fieldId)
  console.log(`源名称 ${fieldSetting.sourceName} 显示名称 ${fieldSetting.displayName}`)
})

// 移除事件监听器，使用 `disposable.dispose()`

const fWorkbook = univerAPI.getActiveWorkbook()
const disposable = univerAPI.addEvent(univerAPI.Event.PivotTableValuePositionChanged, (params) => {
  const { pivotTableId, valueId, position } = params
  const pivotTable = fWorkbook.getPivotTableById(pivotTableId)
  const valueSetting = pivotTable.getValueSetting(valueId)
  console.log(`源名称 ${valueSetting.sourceName} 显示名称 ${valueSetting.displayName}`)
})

// 移除事件监听器，使用 `disposable.dispose()`