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

@univerjs-pro/engine-formula

@univerjs/sheets-formula

Cross-unit reference authoring

Import @univerjs-pro/engine-formula/facade to add these methods to univerAPI.getFormula().

buildReference

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.

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

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

removeExternalReference(options: IRemoveHostExternalReferenceCommandParams): boolean

Remove a host binding by referenceId or qualifier. Existing formula text is not rewritten.

APIs

Getters & Queries

lexerTreeBuilder

Access the formula lexer tree builder.

Signature

get lexerTreeBuilder(): LexerTreeBuilder

Returns

• LexerTreeBuilder — The formula lexer tree builder instance.

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

getAllDependencyTrees(timeout = 30_000): Promise<IFormulaDependencyTreeJson[]>

Parameters

• timeout number (optional) — The maximum time to wait for the result, in milliseconds. Default is 30_000.

Returns

• Promise<IFormulaDependencyTreeJson[]> — A promise that resolves with the array of dependency trees.

Examples

const formulaEngine = univerAPI.getFormula()// Fetch all dependency trees generated for the current workbook.const trees = await formulaEngine.getAllDependencyTrees()console.log('All dependency trees:', trees)

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

getCellDependencyTree(param: { unitId: string; sheetId: string; row: number; column: number }, timeout = 30_000): Promise<IFormulaDependencyTreeFullJson | undefined>

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<any> — A promise that resolves with the dependency tree or undefined if no tree exists for that cell.

Examples

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)

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

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

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

{  "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": []            }          ]        }      ]    }  ]}

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

getInRangeFormulas(unitRanges: IUnitRange[], timeout = 30_000): Promise<IFormulaDependencyTreeJson[]>

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<IFormulaDependencyTreeJson[]> — A promise that resolves with an array of IFormulaDependencyTreeJson describing every formula found in the provided ranges along with their parent/child relationships.

Examples

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)

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

getRangeDependents(unitRanges: IUnitRange[], timeout = 30_000): Promise<IFormulaDependencyTreeJson[]>

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<IFormulaDependencyTreeJson[]> — A promise that resolves with an array of IFormulaDependencyTreeJson representing formulas and their relationships within the dependency graph.

Examples

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)

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

getRangeDependentsAndInRangeFormulas(unitRanges: IUnitRange[], timeout = 30_000): Promise<IFormulaDependentsAndInRangeResults>

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<IFormulaDependentsAndInRangeResults> — 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

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)

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

setFormulaReturnDependencyTree(value: boolean): void

Parameters

• value boolean — Whether to emit formula dependency trees after each formula calculation.

Examples

const formulaEngine = univerAPI.getFormula()// Enable dependency tree emissionformulaEngine.setFormulaReturnDependencyTree(true)// Listen for dependency trees produced by formula calculationconst trees = await new Promise<IFormulaDependencyTreeJson[]>((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)

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

setInitialFormulaComputing(calculationMode: CalculationMode): void

Parameters

• calculationMode CalculationMode — The calculation mode for formula initialization. See CalculationMode for details.

setMaxIteration

When a formula contains a circular reference, set the maximum number of iterations for the formula calculation.

Signature

setMaxIteration(maxIteration: number): void

Parameters

• maxIteration number — The maximum number of iterations for circular reference calculations.

Examples

// Set the maximum number of iterations for the formula calculation to 5.// The default value is 1.const formulaEngine = univerAPI.getFormula()formulaEngine.setMaxIteration(5)

Actions & Operations

moveFormulaRefOffset

Offsets the formula references by the specified row and column offsets.

Signature

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

const formulaEngine = univerAPI.getFormula()const result = formulaEngine.moveFormulaRefOffset('=SUM(A1,B2)', 1, 1)console.log(result)

Events

onCalculationResultApplied

Waits until the latest formula-calculation results have been applied.

Signature

onCalculationResultApplied(timeout?: number): Promise<void>

Parameters

• timeout number (optional) — Maximum wait time in milliseconds.

Returns

• Promise<void> — A promise that resolves when calculation results are applied.

registerAsyncFunction

Register a custom asynchronous formula function.

Signature

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.

registerFunction

Register a custom synchronous formula function.

Signature

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.

Miscellaneous

calculationEnd

Listen for calculation end events.

Signature

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

const formulaEngine = univerAPI.getFormula()formulaEngine.calculationEnd((functionsExecutedState) => {  console.log('Calculation end', functionsExecutedState)})

calculationProcessing

Listen for calculation processing events.

Signature

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

const formulaEngine = univerAPI.getFormula()formulaEngine.calculationProcessing((stageInfo) => {  console.log('Calculation processing', stageInfo)})

calculationResultApplied

Listens after formula-calculation results have been applied to every affected model.

Signature

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.

calculationStart

Listen for calculation start events.

Signature

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

const formulaEngine = univerAPI.getFormula()formulaEngine.calculationStart((forceCalculation) => {  console.log('Calculation start', forceCalculation)})

executeCalculation

Start the calculation of the formula.

Signature

executeCalculation(): void

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.executeCalculation()

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

executeFormulas(formulas: IFormulaStringMap, timeout = 30_000): Promise<IFormulaExecuteResultMap>

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<IFormulaExecuteResultMap> — A promise that resolves with the computed value map mirroring the input structure.

Examples

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)

sequenceNodesBuilder

Resolves the formula string to a sequence of nodes

Signature

sequenceNodesBuilder(formulaString: string): (string | ISequenceNode)[]

Parameters

• formulaString string — The formula string to resolve into nodes.

Returns

• any[] — The nodes of the formula string

Examples

const formulaEngine = univerAPI.getFormula()const nodes = formulaEngine.sequenceNodesBuilder('=SUM(A1,B2)')console.log(nodes)

stopCalculation

Stop the calculation of the formula.

Signature

stopCalculation(): void

Examples

const formulaEngine = univerAPI.getFormula()formulaEngine.stopCalculation()