import type { IPivotTableLabelFieldJSON, IPivotTableOptions, IPivotTableShowDataAsInfo, IPivotTableSortInfo, IPivotTableValueFieldJSON, IPivotTableValueFilter, IValueFilterInfoItem, PivotDataFieldDataTypeEnum, PivotDateGroupFieldDateTypeEnum, PivotSubtotalTypeEnum, PivotTableValuePositionEnum } from '@univerjs-pro/engine-pivot'; import type { IPivotTableConfig, IPivotTableMatrixInfo, IUnitRangeNameWithSubUnitId } from '@univerjs-pro/sheets-pivot'; import type { Injector, IRange, Nullable } from '@univerjs/core'; import { PivotLayoutTypeEnum, PivotTableFiledAreaEnum } from '@univerjs-pro/engine-pivot'; import { FBase } from '@univerjs/core/facade'; interface IPivotTableDataFieldInfo { id: string; name: string; fieldDataType: PivotDataFieldDataTypeEnum; displayName?: string; } export interface IPivotTableFieldSettingOptions { /** * The display name of the pivot table field. Leave it undefined to keep the current display name. */ displayName?: string; /** * The number format code of the pivot table field. Leave it undefined to keep the current format. */ format?: string; /** * The subtotal type of the value field. Leave it undefined to keep the current subtotal type. */ subtotalType?: PivotSubtotalTypeEnum; /** * The Show Values As configuration of the value field. Leave it undefined to keep the current configuration. */ showDataAs?: IPivotTableShowDataAsInfo; } /** * The facade class for the pivot table.Which uses to setting the pivot table fields configs. * @hideconstructor */ export declare class FPivotTable extends FBase { /** * @ignore */ unitId: string; /** * @ignore */ subUnitId: string; /** * @ignore */ pivotTableId: string; protected readonly _injector: Injector; constructor(unitId: string, subUnitId: string, pivotTableId: string, injector: Injector); /** * @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. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * ``` */ getConfig(): Nullable; /** * @description Get the pivot table id. * @returns {string} The pivot table id. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * const pivotTableId = fPivotTable?.getId(); * console.log(pivotTableId); * ``` */ getId(): string; /** * @description Get the source range info of the pivot table. * @typedef IUnitRangeNameWithSubUnitId * @property {string} unitId The unit id of the source workbook. * @property {string} subUnitId The sheet id of the source worksheet. * @property {string} sheetName The source worksheet name. * @property {IRange} range The source data range. * @returns {IUnitRangeNameWithSubUnitId | undefined} The source range info or undefined if the pivot table no longer exists. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * const sourceRangeInfo = fPivotTable?.getSourceRangeInfo(); * console.log(sourceRangeInfo?.range); * ``` */ getSourceRangeInfo(): IUnitRangeNameWithSubUnitId | undefined; /** * @description Get the target cell info of the pivot table. * @typedef PivotTableTargetCellInfo * @property {string} unitId The unit id of the target workbook. * @property {string} subUnitId The sheet id of the target worksheet. * @property {number} row The target row index. * @property {number} col The target column index. * @property {string} [sheetName] The target worksheet name if it is stored in the config. * @returns {IPivotTableConfig['targetCellInfo'] | undefined} The target cell info or undefined if the pivot table no longer exists. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * const targetCellInfo = fPivotTable?.getTargetCellInfo(); * console.log(targetCellInfo?.row, targetCellInfo?.col); * ``` */ getTargetCellInfo(): IPivotTableConfig['targetCellInfo'] | undefined; /** * @description Get the display options of the pivot table. * @typedef PivotTableOptions * @property {number} [pageWrap] The max page fields count before wrapping to the next line. * @property {boolean} [pageOverThenDown] Whether page fields are laid out over then down. * @property {boolean} [showColHeaders] Whether to show column headers. * @property {boolean} [showColStripes] Whether to show column stripes. * @property {boolean} [showLastColumn] Whether to highlight the last column. * @property {boolean} [showRowHeaders] Whether to show row headers. * @property {boolean} [showRowStripes] Whether to show row stripes. * @property {boolean} [mergeItems] Whether to merge repeated header items. * @property {boolean} [repeatRowLabels] Whether to repeat row labels. * @property {boolean} [repeatColLabels] Whether to repeat column labels. * @property {boolean} [showRowSubtotal] Whether to show row subtotals. * @property {PivotSubtotalPositionEnum} [rowSubtotalPosition] Where row subtotals are displayed in outline layout. * @property {boolean} [showRowGrandTotal] Whether to show row grand totals. * @property {boolean} [showColSubtotal] Whether to show column subtotals. * @property {boolean} [showColGrandTotal] Whether to show column grand totals. * @returns {PivotTableOptions|undefined} The display options or undefined if the pivot table no longer exists. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * const options = fPivotTable?.getOptions(); * console.log(options?.showRowGrandTotal); * ``` */ getOptions(): IPivotTableOptions | undefined; /** * @description Get the pivot table layout type. * @returns {PivotLayoutTypeEnum|undefined} The layout type, or undefined if the pivot table no longer exists. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fSheet = fWorkbook.getActiveSheet(); * const subUnitId = fSheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * const layout = fPivotTable?.getLayout(); * console.log(layout === univerAPI.Enum.PivotLayoutTypeEnum.tabular); * ``` */ getLayout(): PivotLayoutTypeEnum | undefined; /** * @description Get the pivot table matrix cache generated by the last render. * @returns {IPivotTableMatrixInfo|undefined} The matrix cache or undefined if the pivot table has not rendered. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * const matrixInfo = fPivotTable?.getPivotTableMatrixInfo(); * console.log(matrixInfo?.matrix); * ``` */ getPivotTableMatrixInfo(): IPivotTableMatrixInfo | undefined; /** * @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} Whether the pivot table fields config is set successfully. */ setFieldsConfig(config: IPivotTableConfig['fieldsConfig']): Promise; /** * @description Set the display options of the pivot table through command. It keeps undo/redo, collaboration and render update paths consistent with panel operations. * @param {PivotTableOptions} options The display options to set. * @typedef PivotTableOptions * @property {number} [pageWrap] The max page fields count before wrapping to the next line. * @property {boolean} [pageOverThenDown] Whether page fields are laid out over then down. * @property {boolean} [showColHeaders] Whether to show column headers. * @property {boolean} [showColStripes] Whether to show column stripes. * @property {boolean} [showLastColumn] Whether to highlight the last column. * @property {boolean} [showRowHeaders] Whether to show row headers. * @property {boolean} [showRowStripes] Whether to show row stripes. * @property {boolean} [mergeItems] Whether to merge repeated header items. * @property {boolean} [repeatRowLabels] Whether to repeat row labels. * @property {boolean} [repeatColLabels] Whether to repeat column labels. * @property {boolean} [showRowSubtotal] Whether to show row subtotals. * @property {PivotSubtotalPositionEnum} [rowSubtotalPosition] Where row subtotals are displayed in outline layout. * @property {boolean} [showRowGrandTotal] Whether to show row grand totals. * @property {boolean} [showColSubtotal] Whether to show column subtotals. * @property {boolean} [showColGrandTotal] Whether to show column grand totals. * @returns {Promise} Whether the pivot table options are set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * await fPivotTable?.setOptions({ * repeatRowLabels: true, * repeatColLabels: true, * showRowGrandTotal: false, * showColGrandTotal: true * }); * ``` */ setOptions(options: IPivotTableOptions): Promise; /** * @description Set the pivot table layout type through command. It keeps undo/redo, collaboration and render update paths consistent with panel operations. * @param {PivotLayoutTypeEnum} layout The layout type to set. * @returns {Promise} Whether the pivot table layout is set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fSheet = fWorkbook.getActiveSheet(); * const subUnitId = fSheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), subUnitId, 0, 8); * await fPivotTable?.setLayout(univerAPI.Enum.PivotLayoutTypeEnum.compact); * ``` */ setLayout(layout: PivotLayoutTypeEnum): Promise; /** * @description Update the source range of the pivot table through command. * @param {SourceInfo} dataRangeInfo The new source data range info. * @typedef SourceInfo * @property {string} unitId The unit id of the source workbook. * @property {string} subUnitId The sheet id of the source worksheet. * @property {string} sheetName The source worksheet name. * @property {IRange} range The new source data range. * @returns {Promise} Whether the source range is updated successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const unitId = fWorkbook.getId(); * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8); * await fPivotTable?.setSourceRange({ * unitId, * subUnitId, * sheetName: fWorksheet.getSheetName(), * range: { * startRow: 0, * endRow: 100, * startColumn: 0, * endColumn: 6 * } * }); * ``` */ setSourceRange(dataRangeInfo: IUnitRangeNameWithSubUnitId): Promise; /** * @description Update the source range of the pivot table through command. This is an alias of setSourceRange. * @param {SourceInfo} dataRangeInfo The new source data range info. * @returns {Promise} Whether the source range is updated successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const fPivotTable = fWorkbook.getPivotTableByCell(fWorkbook.getId(), fWorksheet.getSheetId(), 0, 8); * await fPivotTable?.updateSourceRange({ * unitId: fWorkbook.getId(), * subUnitId: fWorksheet.getSheetId(), * sheetName: fWorksheet.getSheetName(), * range: { startRow: 0, endRow: 100, startColumn: 0, endColumn: 6 } * }); * ``` */ updateSourceRange(dataRangeInfo: IUnitRangeNameWithSubUnitId): Promise; /** * @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} Whether the pivot table fields are reset successfully. */ reset(resetArea?: PivotTableFiledAreaEnum): Promise; /** * @description Get the pivot table id. * @returns {string} The pivot table id. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * ``` */ getPivotTableId(): string; /** * Get the pivot table field setting by the field id. * @param {string} fieldId The table field id. * @returns { IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON | undefined} The field setting. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * } * ``` */ getFieldSetting(fieldId: string): IPivotTableValueFieldJSON | IPivotTableLabelFieldJSON | undefined; /** * Get the pivot table field info list. * @typedef IPivotTableDataFieldInfo * @property {string} id The field id. * @property {string} name The field name. * @property {PivotDataFieldDataTypeEnum} fieldDataType The field data type. * @property {string} [displayName] The field display name if it is set. * @returns {IPivotTableDataFieldInfo[]} The field info list. * * @example * ```typescript * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * // 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); * } * ``` */ getSourceFieldsInfo(): IPivotTableDataFieldInfo[]; /** * @description Get the value filter owned by a row or column field. * @param {string} fieldId - The row or column field id. * @returns {IPivotTableValueFilter | undefined} A copy of the value filter, or `undefined` when the field has no value filter. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * } * ``` */ getValueFilter(fieldId: string): IPivotTableValueFilter | undefined; /** * @description Get all value filters in their evaluation order. A later rule evaluates only the items retained by earlier rules. * @returns {IValueFilterInfoItem[]} Copies of the ordered value-filter rules. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * } * ``` */ getValueFilters(): IValueFilterInfoItem[]; /** * @description Get the field ids by the field area. * @param {PivotTableFiledAreaEnum} fieldArea The area of the field. * @returns {string[]} The field ids in the target area. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * ``` */ getFieldIdsByArea(fieldArea: PivotTableFiledAreaEnum): string[]; /** * @description Get the pivot table range info in worksheet. * @returns {IRange[]|undefined} The pivot table range list. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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); * } * ``` */ getPivotTableRangeInfo(): IRange[] | undefined; /** * @description Remove a pivot table from the workbook by pivot table id * @returns {Promise} Whether the pivot table is removed successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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(); * } * ``` */ remove(): Promise; /** * @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 {Promise} Whether the pivot field is added successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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. * await fPivotTable.addField(1, univerAPI.Enum.PivotTableFiledAreaEnum.Row, 0); * } * ``` */ addField(dataFieldIdOrIndex: string | number, fieldArea: PivotTableFiledAreaEnum, index: number): Promise; /** * @description Remove a pivot field from the pivot table * @param {string[]} fieldIds The deleted field ids. * @returns {Promise} Whether the pivot field is removed successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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. * await fPivotTable.removeField(rowIds); * } * } * ``` */ removeField(fieldIds: string[]): Promise; /** * @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 {Promise} Whether the pivot field is moved successfully. * * @example * ```typescript * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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 * await fPivotTable.updateFieldPosition(rowIds[0], univerAPI.Enum.PivotTableFiledAreaEnum.Column, 0); * } * } * ``` */ updateFieldPosition(fieldId: string, area: PivotTableFiledAreaEnum, index: number): Promise; /** * @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 {Promise} Whether the pivot value field is moved successfully. * * @example * ```typescript * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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 * await fPivotTable.updateValuePosition(univerAPI.Enum.PivotTableValuePositionEnum.Row, 0); * } * ``` */ updateValuePosition(position: PivotTableValuePositionEnum, index: number): Promise; /** * @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 {Promise} Whether the pivot table subtotal type is set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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) { * await fPivotTable.setSubtotalType(valueId, univerAPI.Enum.PivotSubtotalTypeEnum.average); * } * } * ``` */ setSubtotalType(fieldId: string, subtotalType: PivotSubtotalTypeEnum): Promise; /** * @description Set the pivot table field display name, number format, subtotal type, or Show Values As configuration through command. * @param {string} tableFieldId - The pivot table field id. * @param {PivotTableFieldSettingOptions} setting - The field setting options. * @typedef PivotTableFieldSettingOptions * @property {string} [displayName] The display name of the pivot table field. Leave it undefined to keep the current display name. * @property {string} [format] The number format code of the pivot table field. Leave it undefined to keep the current format. * @property {PivotSubtotalTypeEnum} [subtotalType] The subtotal type of the value field. Leave it undefined to keep the current subtotal type. * @property {IPivotTableShowDataAsInfo} [showDataAs] The Show Values As configuration of the value field. Leave it undefined to keep the current configuration. * @returns {Promise} Whether the pivot table field setting is updated successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8); * const valueId = fPivotTable?.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]; * if (fPivotTable && valueId) { * await fPivotTable.setFieldSetting(valueId, { * displayName: 'Revenue', * format: '$#,##0', * subtotalType: univerAPI.Enum.PivotSubtotalTypeEnum.sum * }); * } * ``` */ setFieldSetting(tableFieldId: string, setting: IPivotTableFieldSettingOptions): Promise; /** * @description Get the Show Values As configuration of a pivot table value field. * @param {string} fieldId - The value field id. * @returns {IPivotTableShowDataAsInfo | undefined} The Show Values As configuration, or `undefined` when the field is not a value field. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getActiveSheet(); * if (!fWorksheet) return; * const fPivotTable = fWorksheet.getPivotTableByCell(0, 8); * if (!fPivotTable) return; * const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]; * if (valueId) { * const showDataAs = fPivotTable.getShowDataAs(valueId); * console.log(showDataAs); * } * ``` */ getShowDataAs(fieldId: string): IPivotTableShowDataAsInfo | undefined; /** * @description Set the Show Values As configuration of a pivot table value field. * @param {string} fieldId - The value field id. * @param {IPivotTableShowDataAsInfo} showDataAs - The Show Values As configuration. * @returns {Promise} Whether the Show Values As configuration is set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getActiveSheet(); * if (!fWorksheet) return; * const fPivotTable = fWorksheet.getPivotTableByCell(0, 8); * if (!fPivotTable) return; * const rowId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)[0]; * const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]; * if (rowId && valueId) { * await fPivotTable.setShowDataAs(valueId, { * type: univerAPI.Enum.PivotShowAsTypeEnum.differenceFrom, * baseFieldId: rowId, * baseItem: '', * baseItemType: univerAPI.Enum.PivotShowAsBaseItemTypeEnum.previous, * }); * } * ``` */ setShowDataAs(fieldId: string, showDataAs: IPivotTableShowDataAsInfo): Promise; /** * @description Reset the Show Values As configuration of a pivot table value field to the normal calculation. * @param {string} fieldId - The value field id. * @returns {Promise} Whether the Show Values As configuration is reset successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getActiveSheet(); * if (!fWorksheet) return; * const fPivotTable = fWorksheet.getPivotTableByCell(0, 8); * if (!fPivotTable) return; * const valueId = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Value)[0]; * if (valueId) { * await fPivotTable.resetShowDataAs(valueId); * } * ``` */ resetShowDataAs(fieldId: string): Promise; /** * @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. * @property {string[]} [customOrder] The explicit label order when `type` is `PivotDataFieldSortOperatorEnum.custom`. Labels absent from the array are placed after the configured labels. * @returns {Promise} Whether the pivot table sort info is set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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) { * await fPivotTable.setLabelSort(rowIds[0], { type: univerAPI.Enum.PivotDataFieldSortOperatorEnum.ascending }); * // Advanced usage: persist an explicit label order. The same order is used by browser and Node.js calculations. * await fPivotTable.setLabelSort(rowIds[0], { * type: univerAPI.Enum.PivotDataFieldSortOperatorEnum.custom, * customOrder: ['North', 'East', 'South', 'West'], * }); * } * } * ``` */ setLabelSort(tableFieldId: string, info: IPivotTableSortInfo): Promise; /** * @description Clear the pivot table label sort. The field returns to the configured default label ordering. * @param {string} tableFieldId - The field id of the sort. * @returns {Promise} Whether the pivot table label sort is cleared successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const fWorksheet = fWorkbook.getActiveSheet(); * if (!fWorksheet) return; * const fPivotTable = fWorksheet.getPivotTableByCell(0, 8); * if (!fPivotTable) return; * const rowIds = fPivotTable.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row); * if (rowIds.length > 0) { * await fPivotTable.clearLabelSort(rowIds[0]); * } * ``` */ clearLabelSort(tableFieldId: string): Promise; /** * @description Set the pivot table manual filter. * @param {string} tableFieldId - The field id of the filter. * @param {string[]} items - The items of the filter. * @param {boolean} [isAll] - Whether the filter is all.If true, the filter will be all items, the items will be ignored. * @returns {Promise} Whether the pivot table filter is set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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) { * await fPivotTable.setLabelManualFilter(rowIds[0], ['item1', 'item2']); * } * } * ``` */ setLabelManualFilter(tableFieldId: string, items: string[], isAll?: boolean): Promise; /** * @description Set or replace the value filter owned by a row or column field. Each label field can own at most one rule. New rules are appended to the evaluation order, while replacing a rule keeps its current position. Use `count` or `percent` with `isBottom` to configure Top/Bottom filters. * @param {string} fieldId - The row or column field id that owns the rule. * @param {Omit} [filterInfo] - The filter configuration. Pass `undefined` to remove the current rule. `valueFieldId` must reference an existing value field. Between operators use a two-number `expected` array; other operators use a number. * @returns {Promise} Whether the pivot table value filter is set successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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) { * await fPivotTable.setValueFilter(rowIds[0], { * operator: univerAPI.Enum.PivotFilterOperatorEnum.valueGreaterThan, * expected: 10, * valueFieldId: valueIds[0], * }); * } * // Remove the value filter while keeping other ordered rules unchanged. * // await fPivotTable.clearValueFilter(rowIds[0]); * } * ``` */ setValueFilter(fieldId: string, filterInfo?: Omit): Promise; /** * @description Clear the value filter of the pivot table field through command. * @param {string} fieldId - The field id of the value filter. * @returns {Promise} Whether the value filter is cleared successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8); * const rowId = fPivotTable?.getFieldIdsByArea(univerAPI.Enum.PivotTableFiledAreaEnum.Row)[0]; * if (fPivotTable && rowId) { * await fPivotTable.clearValueFilter(rowId); * } * ``` */ clearValueFilter(fieldId: string): Promise; /** * @description Rename the pivot table field. * @param {string} fieldId - The field id. * @param {string} name - The new name of the field. * @returns {Promise} Whether the pivot table field is renamed successfully. * * @example * ```typescript * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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) { * await fPivotTable.renameField(valueIds[0], 'newName'); * } * } * ``` */ renameField(fieldId: string, name: string): Promise; /** * @description Move the pivot table to the target cell. * @param {string} sheetName - The target sheet name. * @param {number} row - The target row index. * @param {number} col - The target column index. * @returns {Promise} Whether the pivot table is moved successfully. * @example * ```typescript * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subSheetName = fWorksheet.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 * await fPivotTable.move(subSheetName, 100, 1); * } * ``` */ move(sheetName: string, row: number, col: number): Promise; /** * @description Set the date group type for the date group field. * @param {string} tableFieldId - The field id of the group label field. * @param {PivotDateGroupFieldDateTypeEnum} dateType - The date group type. * @returns {Promise} Whether the date group type is set successfully. * @example * ```typescript * // 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 fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.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 * await pivotTable.setDateGroupType(rowIds[1], univerAPI.Enum.PivotDateGroupFieldDateTypeEnum.YearMonthDate); * } * ``` */ setDateGroupType(tableFieldId: string, dateType: PivotDateGroupFieldDateTypeEnum): Promise; /** * @description Collapse or expand a pivot table item by the cell position through command. * @param {number} row - The row index of the pivot item cell. * @param {number} col - The column index of the pivot item cell. * @param {boolean} collapse - Whether to collapse the pivot item. True means collapse, false means expand. * @returns {Promise} Whether the pivot item collapse state is updated successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8); * // the cell must be a cell has collapse or expand button, otherwise the command will fail, and the cell (2,8) is just an example, you can pass any cell in the pivot table which has collapse or expand button. * await fPivotTable?.setCellCollapse(2, 8, true); * ``` */ setCellCollapse(row: number, col: number, collapse: boolean): Promise; /** * @description Drill down a pivot value cell through command and create a sheet with the related source records. * @param {number} row - The row index of the pivot value cell. * @param {number} col - The column index of the pivot value cell. * @returns {Promise} Whether the drill down sheet is created successfully. * * @example * ```ts * const fWorkbook = univerAPI.getActiveWorkbook(); * const unitId = fWorkbook.getId(); * const fWorksheet = fWorkbook.getSheetByName('Sheet1'); * if (!fWorksheet) return; * const subUnitId = fWorksheet.getSheetId(); * const fPivotTable = fWorkbook.getPivotTableByCell(unitId, subUnitId, 0, 8); * await fPivotTable?.drillDown(5, 10); * ``` */ drillDown(row: number, col: number): Promise; private _getPivotPanelTableFieldInfo; private _getPivotPanelSourceFieldInfo; private _isDataFieldUsed; private _isCellInCurrentPivotTable; } export {};