FFormula

packages	`@univerjs/engine-formula`

This interface class provides methods to modify the behavior of the operation formula.

APIs

`calculationEnd`

Listening calculation ends.

Signature

calculationEnd(callback: (functionsExecutedState: FormulaExecutedStateType) => void): IDisposable

Parameters

callback ((functionsExecutedState: FormulaExecutedStateType) => void) — - The callback function to be called when the formula calculation ends.

Returns

(IDisposable) — The disposable instance.

Examples

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

`calculationProcessing`

Listening calculation processing.

Signature

calculationProcessing(callback: (stageInfo: IExecutionInProgressParams) => void): IDisposable

Parameters

callback ((stageInfo: IExecutionInProgressParams) => void) — - The callback function to be called when the formula calculation is in progress.

Returns

(IDisposable) — The disposable instance.

Examples

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

`calculationStart`

Listening calculation starts.

Signature

calculationStart(callback: (forceCalculation: boolean) => void): IDisposable

Parameters

callback ((forceCalculation: boolean) => void) — - The callback function to be called when the formula calculation starts.

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?: number = 30_000): Promise<IFormulaExecuteResultMap>

Parameters

formulas (IFormulaStringMap) — Nested structure (unit → sheet → row → column) describing formulas and their decomposed sub-expressions.
timeout (number) — Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

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)

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?: number = 30_000): Promise<IFormulaDependencyTreeJson[]>

Parameters

timeout (number) — Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

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?: number = 30_000): Promise<IFormulaDependencyTreeFullJson | undefined>

Parameters

param ({ unitId: string; sheetId: string; row: number; column: number }) — The target cell location: - unitId The workbook ID. - sheetId The sheet ID. - row The zero-based row index. - column The zero-based column index.
timeout (number) — Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

Returns

(Promise<IFormulaDependencyTreeFullJson | undefined>) — 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 =)

Returns

(IExprTreeNode | null) — A formula expression tree describing the hierarchical structure of the formula

Examples

const formulaEngine = univerAPI.getFormula()

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)

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?: number = 30_000): Promise<IFormulaDependencyTreeJson[]>

Parameters

unitRanges (IUnitRange[]) — An array of workbook/sheet ranges defining the lookup boundaries: - unitId The workbook ID. - sheetId The sheet ID. - range The zero-based grid range.
timeout (number) — Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

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?: number = 30_000): Promise<IFormulaDependencyTreeJson[]>

Parameters

unitRanges (IUnitRange[]) — An array of workbook/sheet ranges to query. Each range includes: - unitId The workbook ID. - sheetId The sheet ID. - range The row/column boundaries.
timeout (number) — Optional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

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:

the full dependency trees of all formulas that depend on the specified ranges, and
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?: number = 30_000): Promise<IFormulaDependentsAndInRangeResults>

Parameters

unitRanges (IUnitRange[]) — An array of workbook/sheet ranges to query. Each range includes: - unitId The workbook ID. - sheetId The sheet ID. - range The zero-based row/column boundaries.
timeout (number) — Optional timeout in milliseconds. If the dependency calculation does not complete within this period, the promise will be rejected.

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)

`lexerTreeBuilder`

The tree builder for formula string.

Signature

get lexerTreeBuilder(): LexerTreeBuilder

Tags

@type

`moveFormulaRefOffset`

Offsets the formula

Signature

moveFormulaRefOffset(formulaString: string, refOffsetX: number, refOffsetY: number, ignoreAbsolute?: boolean): string

Parameters

formulaString (string) — - The formula string to offset
refOffsetX (number) — - The offset column
refOffsetY (number) — - The offset row
ignoreAbsolute (boolean) — - Whether to ignore the absolute reference

Returns

(string) — The offset formula string

Examples

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

`onCalculationEnd`

Waits for the formula calculation to complete.

Signature

onCalculationEnd(): Promise<void>

Returns

(Promise<void>) — This method returns a promise that resolves when the calculation is complete.

`sequenceNodesBuilder`

Resolves the formula string to a 'node' node

Signature

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

Parameters

formulaString (string) — - The formula string to resolve

Returns

((string | ISequenceNode)[]) — The nodes of the formula string

Examples

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

`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 calculation. - true: Emit dependency trees after each calculation. - false: Do not emit dependency trees (default behavior).

Examples

const formulaEngine = univerAPI.getFormula()

// Enable dependency tree emission
formulaEngine.setFormulaReturnDependencyTree(true)

// Listen for dependency trees produced by formula calculation
const 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)

`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. The default value is 1.

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)

`stopCalculation`

Stop the calculation of the formula.

Signature

stopCalculation(): void

Examples

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

`whenComputingCompleteAsync`

Deprecated Use onCalculationEnd instead.

Signature

whenComputingCompleteAsync(timeout?: number): Promise<boolean>

FFormula

APIs

`calculationEnd`

`calculationProcessing`

`calculationStart`

`executeCalculation`

`executeFormulas`

`getAllDependencyTrees`

`getCellDependencyTree`

`getFormulaExpressTree`

`getInRangeFormulas`

`getRangeDependents`

`getRangeDependentsAndInRangeFormulas`

`lexerTreeBuilder`

`moveFormulaRefOffset`

`onCalculationEnd`

`sequenceNodesBuilder`

`setFormulaReturnDependencyTree`

`setMaxIteration`

`stopCalculation`

`whenComputingCompleteAsync`

Table of Contents