# Formula > Language fallback: requested `zh-CN`; content is `en-US`. - Human documentation: [https://docs.univer.ai/zh-CN/reference/facade/formula](https://docs.univer.ai/zh-CN/reference/facade/formula) - Agent Markdown: [https://docs.univer.ai/zh-CN/reference/facade/formula.md](https://docs.univer.ai/zh-CN/reference/facade/formula.md) - Requested language: `zh-CN` - Content language: `en-US` - Documentation version: `1.0.0-rc.0` - Source: [facade/formula.mdx](https://github.com/dream-num/documentation/blob/dev/content/reference/facade/formula.mdx) --- | Packages | `@univerjs/engine-formula`, `@univerjs-pro/engine-formula`, `@univerjs/sheets-formula` | | -------- | -------------------------------------------------------------------------------------- | This interface class provides methods to modify the behavior of the operation formula. > This class should not be instantiated directly. Use factory methods on `univerAPI` instead. `@univerjs-pro/engine-formula/facade` extends the core formula Facade with cross-unit Sheet and Base reference authoring. ## Overview ### @univerjs/engine-formula | Method | Description | | ------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | | [`calculationEnd`](#calculationend) | Listen for calculation end events | | [`calculationProcessing`](#calculationprocessing) | Listen for calculation processing events | | [`calculationStart`](#calculationstart) | Listen for calculation start events | | [`executeCalculation`](#executecalculation) | Start the calculation of the formula | | [`executeFormulas`](#executeformulas) | Execute a batch of formulas asynchronously and receive computed results | | [`getAllDependencyTrees`](#getalldependencytrees) | Retrieve all formula dependency trees produced during the latest dependency-analysis run | | [`getCellDependencyTree`](#getcelldependencytree) | Retrieve the dependency tree of a specific cell | | [`getFormulaExpressTree`](#getformulaexpresstree) | Parse a formula string and return its formula expression tree | | [`getInRangeFormulas`](#getinrangeformulas) | Retrieve the dependency trees of all formulas inside the specified ranges | | [`getRangeDependents`](#getrangedependents) | Retrieve the full dependency trees for all formulas that depend on the specified ranges | | [`getRangeDependentsAndInRangeFormulas`](#getrangedependentsandinrangeformulas) | Retrieve both the dependency trees of formulas that depend on the specified ranges and those inside the ranges | | [`lexerTreeBuilder`](#lexertreebuilder) | Access the formula lexer tree builder | | [`moveFormulaRefOffset`](#moveformularefoffset) | Offsets the formula | | [`calculationResultApplied`](#calculationresultapplied) | Listen after calculation results have been applied to every affected model | | [`onCalculationResultApplied`](#oncalculationresultapplied) | Wait until the latest calculation results have been applied | | [`sequenceNodesBuilder`](#sequencenodesbuilder) | Resolves the formula string to a node tree | | [`setFormulaReturnDependencyTree`](#setformulareturndependencytree) | Enable or disable emitting formula dependency trees after each formula calculation | | [`setMaxIteration`](#setmaxiteration) | When a formula contains a circular reference, set the maximum number of iterations for the formula calculation | | [`stopCalculation`](#stopcalculation) | Stop the calculation of the formula | ### @univerjs-pro/engine-formula | Method | Description | | ----------------------------------------------------- | ------------------------------------------------------------------------------ | | [`buildReference`](#buildreference) | Build a Sheet-range or Base-table reference and persist its cross-unit binding | | [`upsertExternalReference`](#upsertexternalreference) | Create or update a host-owned external reference binding | | [`removeExternalReference`](#removeexternalreference) | Remove a host-owned external reference binding | ### @univerjs/sheets-formula | Method | Description | | ----------------------------------------------------------- | ----------------------------------------------- | | [`registerAsyncFunction`](#registerasyncfunction) | Register a custom asynchronous formula function | | [`registerFunction`](#registerfunction) | Register a custom synchronous formula function | | [`setInitialFormulaComputing`](#setinitialformulacomputing) | Update the calculation mode of the formula | ## Cross-unit reference authoring Import `@univerjs-pro/engine-formula/facade` to add these methods to `univerAPI.getFormula()`. ### `buildReference` ```typescript buildReference(options: IBuildFormulaReferenceOptions): string ``` The returned string is a reference fragment without a leading `=`. For a cross-unit source, the method also persists the stable source binding on the host unit. ```ts const host = univerAPI.getActiveWorkbook() const reference = univerAPI.getFormula().buildReference({ hostUnitId: host.getId(), unit: { unitId: 'sales-workbook', formulaQualifier: 'Sales Workbook', }, target: { kind: univerAPI.Enum.FormulaReferenceType.SHEET_RANGE, sheetName: 'Sales', range: { startRow: 1, endRow: 9, startColumn: 1, endColumn: 1 }, }, }) host.getActiveSheet().getRange('C1').setFormula(`=SUM(${reference})`) ``` ### `upsertExternalReference` ```typescript upsertExternalReference(options: IUpsertHostExternalReferenceCommandParams): boolean ``` Use this before writing hand-authored or imported cross-unit formula text. The options identify the host `unitId`, public `qualifier`, stable `sourceUnitId`, and `sourceUnitType`. ### `removeExternalReference` ```typescript removeExternalReference(options: IRemoveHostExternalReferenceCommandParams): boolean ``` Remove a host binding by `referenceId` or `qualifier`. Existing formula text is not rewritten. Source: `@univerjs-pro/engine-formula` ## APIs ### Getters & Queries ### `lexerTreeBuilder` Access the formula lexer tree builder. **Signature** ```typescript get lexerTreeBuilder(): LexerTreeBuilder ``` **Returns** * `LexerTreeBuilder` — The formula lexer tree builder instance. Source: `@univerjs/engine-formula` ### `getAllDependencyTrees` Retrieve all formula dependency trees that were produced during the latest dependency-analysis run. This triggers a local dependency-calculation command and returns the complete set of dependency trees once the calculation finishes. **Signature** ```typescript getAllDependencyTrees(timeout = 30_000): Promise ``` **Parameters** * `timeout` `number` *(optional)* — The maximum time to wait for the result, in milliseconds. Default is `30_000`. **Returns** * `Promise` — A promise that resolves with the array of dependency trees. **Examples** ```ts const formulaEngine = univerAPI.getFormula() // Fetch all dependency trees generated for the current workbook. const trees = await formulaEngine.getAllDependencyTrees() console.log('All dependency trees:', trees) ``` Source: `@univerjs/engine-formula` ### `getCellDependencyTree` Retrieve the dependency tree of a specific cell. This triggers a local dependency-calculation command for the given unit, sheet, and cell location, and returns the computed dependency tree when the calculation is completed. **Signature** ```typescript getCellDependencyTree(param: { unitId: string; sheetId: string; row: number; column: number }, timeout = 30_000): Promise ``` **Parameters** * `param` `{ unitId: string; sheetId: string; row: number; column: number; }` — The target cell coordinates and unit information. * `timeout` `number` *(optional)* — The maximum time to wait for the result, in milliseconds. Default is `30_000`. **Returns** * `Promise` — A promise that resolves with the dependency tree or `undefined` if no tree exists for that cell. **Examples** ```ts const formulaEngine = univerAPI.getFormula() // Query the dependency tree for cell B2 in a specific sheet. const tree = await formulaEngine.getCellDependencyTree({ unitId: 'workbook1', sheetId: 'sheet1', row: 1, column: 1, }) console.log('Cell dependency tree:', tree) ``` Source: `@univerjs/engine-formula` ### `getFormulaExpressTree` Parse a formula string and return its formula expression tree. This API analyzes the syntactic structure of a formula and builds an expression tree that reflects how the formula is composed (functions, operators, ranges, and nested expressions), without performing calculation or dependency evaluation. The returned tree is suitable for: * Formula structure visualization * Explaining complex formulas (e.g. LET / LAMBDA) * Debugging or inspecting formula composition * Building advanced formula tooling *** **Signature** ```typescript getFormulaExpressTree(formulaString: string, unitId: string): IExprTreeNode | null ``` **Parameters** * `formulaString` `string` — The formula string to parse (with or without leading `=`). * `unitId` `string` — The ID of the workbook unit. **Returns** * `any` — A formula expression tree describing the hierarchical structure of the formula **Examples** ```ts const formulaEngine = univerAPI.getFormula() const fWorkbook = univerAPI.getActiveWorkbook() const formula = '=LET(x,SUM(A1,B1,A1:B10),y,OFFSET(A1:B10,0,1),SUM(x,y)+x)+1' const exprTree = formulaEngine.getFormulaExpressTree(formula, fWorkbook.getId()) console.log(exprTree) ``` Example output (simplified): ```json { "value": "let(x,sum(A1,B1,A1:B10),y,offset(A1:B10,0,1),sum(x,y)+x)+1", "children": [ { "value": "let(x,sum(A1,B1,A1:B10),y,offset(A1:B10,0,1),sum(x,y)+x)", "children": [ { "value": "sum(A1,B1,A1:B10)", "children": [ { "value": "A1:B10", "children": [] } ] }, { "value": "offset(A1:B10,0,1)", "children": [ { "value": "A1:B10", "children": [] } ] } ] } ] } ``` Source: `@univerjs/engine-formula` ### `getInRangeFormulas` Retrieve the dependency trees of all formulas *inside* the specified ranges. Unlike `getRangeDependents`, this API only returns formulas whose definitions physically reside within the queried ranges. Internally this triggers the same dependency-calculation command but with `isInRange = true`, and the promise resolves when the results are ready. **Signature** ```typescript getInRangeFormulas(unitRanges: IUnitRange[], timeout = 30_000): Promise ``` **Parameters** * `unitRanges` `IUnitRange[]` — The ranges to query for formula dependencies. * `timeout` `number` *(optional)* — The maximum time to wait for the result, in milliseconds. Default is `30_000`. **Returns** * `Promise` — A promise that resolves with an array of `IFormulaDependencyTreeJson` describing every formula found in the provided ranges along with their parent/child relationships. **Examples** ```ts const formulaEngine = univerAPI.getFormula() // Query all formulas that lie within A1:D20 in Sheet1. const formulasInRange = await formulaEngine.getInRangeFormulas([ { unitId: 'workbook1', sheetId: 'sheet1', range: { startRow: 0, endRow: 19, startColumn: 0, endColumn: 3 } }, ]) console.log('Formulas inside range:', formulasInRange) ``` Source: `@univerjs/engine-formula` ### `getRangeDependents` Retrieve the full dependency trees for all formulas that *depend on* the specified ranges. This triggers a local dependency-calculation command and resolves once the calculation completes. **Signature** ```typescript getRangeDependents(unitRanges: IUnitRange[], timeout = 30_000): Promise ``` **Parameters** * `unitRanges` `IUnitRange[]` — The ranges to query for formula dependencies. * `timeout` `number` *(optional)* — The maximum time to wait for the result, in milliseconds. Default is `30_000`. **Returns** * `Promise` — A promise that resolves with an array of `IFormulaDependencyTreeJson` representing formulas and their relationships within the dependency graph. **Examples** ```ts const formulaEngine = univerAPI.getFormula() // Query all formulas that depend on A1:B10 in Sheet1. const dependents = await formulaEngine.getRangeDependents([ { unitId: 'workbook1', sheetId: 'sheet1', range: { startRow: 0, endRow: 9, startColumn: 0, endColumn: 1 } }, ]) console.log('Dependent formulas:', dependents) ``` Source: `@univerjs/engine-formula` ### `getRangeDependentsAndInRangeFormulas` Retrieve both: 1. the full dependency trees of all formulas that depend on the specified ranges, and 2. the dependency trees of all formulas that physically reside inside the specified ranges. This is a convenience API that combines the behaviors of `getRangeDependents` and `getInRangeFormulas` into a single call. Internally, it triggers a local dependency-calculation command once and resolves when both result sets are available, avoiding duplicate calculations and event listeners. **Signature** ```typescript getRangeDependentsAndInRangeFormulas(unitRanges: IUnitRange[], timeout = 30_000): Promise ``` **Parameters** * `unitRanges` `IUnitRange[]` — The ranges to query for formula dependencies. * `timeout` `number` *(optional)* — The maximum time to wait for the result, in milliseconds. Default is `30_000`. **Returns** * `Promise` — A promise that resolves with an object containing: * `dependents`: Dependency trees of all formulas that depend on the specified ranges (upstream consumers). * `inRanges`: Dependency trees of all formulas whose definitions are located inside the specified ranges. **Examples** ```ts const formulaEngine = univerAPI.getFormula() const result = await formulaEngine.getRangeDependentsAndInRangeFormulas([ { unitId: 'workbook1', sheetId: 'sheet1', range: { startRow: 0, endRow: 9, startColumn: 0, endColumn: 1 }, }, ]) console.log('Dependent formulas:', result.dependents) console.log('Formulas inside range:', result.inRanges) ``` Source: `@univerjs/engine-formula` ### Setters & Modifiers ### `setFormulaReturnDependencyTree` Enable or disable emitting formula dependency trees after each formula calculation. When enabled, the formula engine will emit the dependency trees produced by each completed formula calculation through the internal command system. Consumers can obtain the result by listening for the corresponding calculation-result command. When disabled, dependency trees will not be emitted. This option only controls whether dependency trees are exposed. It does not affect formula calculation behavior. **Signature** ```typescript setFormulaReturnDependencyTree(value: boolean): void ``` **Parameters** * `value` `boolean` — Whether to emit formula dependency trees after each formula calculation. **Examples** ```ts const formulaEngine = univerAPI.getFormula() // Enable dependency tree emission formulaEngine.setFormulaReturnDependencyTree(true) // Listen for dependency trees produced by formula calculation const trees = await new Promise((resolve, reject) => { const timer = setTimeout(() => { disposable.dispose() reject(new Error('Timeout waiting for formula dependency trees')) }, 30_000) const disposable = commandService.onCommandExecuted((command) => { if (command.id !== SetFormulaDependencyCalculationResultMutation.id) { return } clearTimeout(timer) disposable.dispose() const params = command.params as ISetFormulaDependencyCalculationResultMutation resolve(params.result ?? []) }) }) console.log('Dependency trees:', trees) ``` Source: `@univerjs/engine-formula` ### `setInitialFormulaComputing` Update the calculation mode of the formula. It will take effect the next time the Univer Sheet is constructed. The calculation mode only handles formulas data when the workbook initializes data. **Signature** ```typescript setInitialFormulaComputing(calculationMode: CalculationMode): void ``` **Parameters** * `calculationMode` `CalculationMode` — The calculation mode for formula initialization. See [`CalculationMode`](https://docs.univer.ai/zh-CN/reference/facade/enums.md#available-enum-names) for details. Source: `@univerjs/sheets-formula` ### `setMaxIteration` When a formula contains a circular reference, set the maximum number of iterations for the formula calculation. **Signature** ```typescript setMaxIteration(maxIteration: number): void ``` **Parameters** * `maxIteration` `number` — The maximum number of iterations for circular reference calculations. **Examples** ```ts // Set the maximum number of iterations for the formula calculation to 5. // The default value is 1. const formulaEngine = univerAPI.getFormula() formulaEngine.setMaxIteration(5) ``` Source: `@univerjs/engine-formula` ### Actions & Operations ### `moveFormulaRefOffset` Offsets the formula references by the specified row and column offsets. **Signature** ```typescript moveFormulaRefOffset(formulaString: string, refOffsetX: number, refOffsetY: number, ignoreAbsolute?: boolean): string ``` **Parameters** * `formulaString` `string` — The formula string to offset. * `refOffsetX` `number` — The number of columns to offset. * `refOffsetY` `number` — The number of rows to offset. * `ignoreAbsolute` `boolean` *(optional)* — Whether to ignore absolute references (e.g. `$A$1`). **Returns** * `string` — The offset formula string **Examples** ```ts const formulaEngine = univerAPI.getFormula() const result = formulaEngine.moveFormulaRefOffset('=SUM(A1,B2)', 1, 1) console.log(result) ``` Source: `@univerjs/engine-formula` ### Events ### `onCalculationResultApplied` Waits until the latest formula-calculation results have been applied. **Signature** ```typescript onCalculationResultApplied(timeout?: number): Promise ``` **Parameters** * `timeout` `number` *(optional)* — Maximum wait time in milliseconds. **Returns** * `Promise` — A promise that resolves when calculation results are applied. Source: `@univerjs/engine-formula` ### `registerAsyncFunction` Register a custom asynchronous formula function. **Signature** ```typescript registerAsyncFunction(name: string, func: IRegisterAsyncFunction, options?: string | { locales?: ILocales; description?: string | IFunctionInfo }): IDisposable ``` **Parameters** * `name` `string` — The name of the function to register. This will be used in formulas (e.g., `=MYFUNC()`). * `func` `IRegisterAsyncFunction` — The async implementation of the function. * `options` `string | { locales?: ILocales; description?: string | IFunctionInfo; }` *(optional)* — A description string, or an object containing `locales` and `description`. **Returns** * `IDisposable` — See signature above. Source: `@univerjs/sheets-formula` ### `registerFunction` Register a custom synchronous formula function. **Signature** ```typescript registerFunction(name: string, func: IRegisterFunction, options?: string | { locales?: ILocales; description?: string | IFunctionInfo }): IDisposable ``` **Parameters** * `name` `string` — The name of the function to register. This will be used in formulas (e.g., `=MYFUNC()`). * `func` `IRegisterFunction` — The implementation of the function. * `options` `string | { locales?: ILocales; description?: string | IFunctionInfo; }` *(optional)* — A description string, or an object containing `locales` and `description`. **Returns** * `IDisposable` — See signature above. Source: `@univerjs/sheets-formula` ### Miscellaneous ### `calculationEnd` Listen for calculation end events. **Signature** ```typescript calculationEnd(callback: (functionsExecutedState: FormulaExecutedStateType) => void): IDisposable ``` **Parameters** * `callback` `(functionsExecutedState: FormulaExecutedStateType) => void` — A callback that receives the execution state when calculation ends. **Returns** * `IDisposable` — The disposable instance. **Examples** ```ts const formulaEngine = univerAPI.getFormula() formulaEngine.calculationEnd((functionsExecutedState) => { console.log('Calculation end', functionsExecutedState) }) ``` Source: `@univerjs/engine-formula` ### `calculationProcessing` Listen for calculation processing events. **Signature** ```typescript calculationProcessing(callback: (stageInfo: IExecutionInProgressParams) => void): IDisposable ``` **Parameters** * `callback` `(stageInfo: IExecutionInProgressParams) => void` — A callback that receives the current calculation stage information. **Returns** * `IDisposable` — The disposable instance. **Examples** ```ts const formulaEngine = univerAPI.getFormula() formulaEngine.calculationProcessing((stageInfo) => { console.log('Calculation processing', stageInfo) }) ``` Source: `@univerjs/engine-formula` ### `calculationResultApplied` Listens after formula-calculation results have been applied to every affected model. **Signature** ```typescript calculationResultApplied(callback: (result: ISetFormulaCalculationResultMutation) => void): IDisposable ``` **Parameters** * `callback` `(result: ISetFormulaCalculationResultMutation) => void` — A function called with the calculation result payload. **Returns** * `IDisposable` — A disposable used to unsubscribe from the event. Source: `@univerjs/engine-formula` ### `calculationStart` Listen for calculation start events. **Signature** ```typescript calculationStart(callback: (forceCalculation: boolean) => void): IDisposable ``` **Parameters** * `callback` `(forceCalculation: boolean) => void` — A callback that receives whether the calculation is forced. **Returns** * `IDisposable` — The disposable instance. **Examples** ```ts const formulaEngine = univerAPI.getFormula() formulaEngine.calculationStart((forceCalculation) => { console.log('Calculation start', forceCalculation) }) ``` Source: `@univerjs/engine-formula` ### `executeCalculation` Start the calculation of the formula. **Signature** ```typescript executeCalculation(): void ``` **Examples** ```ts const formulaEngine = univerAPI.getFormula() formulaEngine.executeCalculation() ``` Source: `@univerjs/engine-formula` ### `executeFormulas` Execute a batch of formulas asynchronously and receive computed results. Each formula cell is represented as a string array: \[fullFormula, ...subFormulas] Where: * fullFormula (index 0) is the complete formula expression written in the cell. Example: "=SUM(A1:A10) + SQRT(D7)". * subFormulas (index 1+) are optional decomposed expressions extracted from the full formula. Each of them can be independently computed by the formula engine. These sub-expressions can include: * Single-cell references: "A2", "B2", "C5" * Range references: "A1:A10" * Function calls: "SQRT(D7)", "ABS(A2-B2)" * Any sub-formula that was parsed out of the original formula and can be evaluated on its own. The batch execution engine may use these sub-formulas for dependency resolution, incremental computation, or performance optimizations. **Signature** ```typescript executeFormulas(formulas: IFormulaStringMap, timeout = 30_000): Promise ``` **Parameters** * `formulas` `IFormulaStringMap` — A map of formulas to execute, keyed by unit ID, sheet ID, row, and column. * `timeout` `number` *(optional)* — The maximum time to wait for the calculation to complete, in milliseconds. Default is `30_000`. **Returns** * `Promise` — A promise that resolves with the computed value map mirroring the input structure. **Examples** ```ts const formulaEngine = univerAPI.getFormula() const formulas = { Book1: { Sheet1: { 2: { 3: [ // Full formula: '=SUM(A1:A10) + SQRT(D7)', // Decomposed sub-formulas (each one can be evaluated independently): 'SUM(A1:A10)', // sub-formula 1 'SQRT(D7)', // sub-formula 2 'A1:A10', // range reference 'D7', // single-cell reference ], }, 4: { 5: ['=A2 + B2 + SQRT(C5)', 'A2', 'B2', 'SQRT(C5)'], }, }, }, } const result = await formulaEngine.executeFormulas(formulas) console.log(result) ``` Source: `@univerjs/engine-formula` ### `sequenceNodesBuilder` Resolves the formula string to a sequence of nodes **Signature** ```typescript sequenceNodesBuilder(formulaString: string): (string | ISequenceNode)[] ``` **Parameters** * `formulaString` `string` — The formula string to resolve into nodes. **Returns** * `any[]` — The nodes of the formula string **Examples** ```ts const formulaEngine = univerAPI.getFormula() const nodes = formulaEngine.sequenceNodesBuilder('=SUM(A1,B2)') console.log(nodes) ``` Source: `@univerjs/engine-formula` ### `stopCalculation` Stop the calculation of the formula. **Signature** ```typescript stopCalculation(): void ``` **Examples** ```ts const formulaEngine = univerAPI.getFormula() formulaEngine.stopCalculation() ``` Source: `@univerjs/engine-formula`