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.

Overview

@univerjs/engine-formula

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 | | moveFormulaRefOffset | Offsets the formula | | onCalculationEnd | Waits for the formula calculation to complete | | sequenceNodesBuilder | Resolves the formula string to a 'node' node | | setFormulaReturnDependencyTree | Enable or disable emitting formula dependency trees after each formula calculation | | setMaxIteration | When a formula contains a circular reference, set the maximum number of iterations for the formula calculation | | stopCalculation | Stop the calculation of the formula | | whenComputingCompleteAsync | - |

@univerjs/sheets-formula

APIs

Getters & Queries

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) — No description

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; } — No description
• timeout number (optional) — No description

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 — No description
• unitId string — No description

Returns

• any — 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 = 30_000): Promise<IFormulaDependencyTreeJson[]>

Parameters

• unitRanges IUnitRange[] — No description
• timeout number (optional) — No description

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[] — No description
• timeout number (optional) — No description

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[] — No description
• timeout number (optional) — No description

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 — No description

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

setInitialFormulaComputing

Signature

setInitialFormulaComputing(calculationMode: CalculationMode): void

Parameters

• calculationMode CalculationMode — No description

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 — No description

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

Signature

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

Parameters

• formulaString string — No description
• refOffsetX number — No description
• refOffsetY number — No description
• ignoreAbsolute boolean (optional) — No description

Returns

• string — The offset formula string

Examples

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

Events

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.

onCalculationResultApplied

Signature

onCalculationResultApplied(): Promise<void>

Returns

• Promise<void> — See signature above.

registerAsyncFunction

Signature

registerAsyncFunction(name: string, func: IRegisterAsyncFunction, options?: string | { locales?: ILocales; description?: string | IFunctionInfo }): IDisposable

Parameters

• name string — No description
• func IRegisterAsyncFunction — No description
• options string | { locales?: ILocales; description?: string | IFunctionInfo; } (optional) — No description

Returns

• IDisposable — See signature above.

registerFunction

Signature

registerFunction(name: string, func: IRegisterFunction, options?: string | { locales?: ILocales; description?: string | IFunctionInfo }): IDisposable

Parameters

• name string — No description
• func IRegisterFunction — No description
• options string | { locales?: ILocales; description?: string | IFunctionInfo; } (optional) — No description

Returns

• IDisposable — See signature above.

Miscellaneous

calculationEnd

Listening calculation ends.

Signature

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

Parameters

• callback (functionsExecutedState: FormulaExecutedStateType) => void — No description

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 — No description

Returns

• IDisposable — The disposable instance.

Examples

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

calculationResultApplied

Signature

calculationResultApplied(callback: (result: ISetFormulaCalculationResultMutation) => void): IDisposable

Parameters

• callback (result: ISetFormulaCalculationResultMutation) => void — No description

Returns

• IDisposable — See signature above.

calculationStart

Listening calculation starts.

Signature

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

Parameters

• callback (forceCalculation: boolean) => void — No description

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 — No description
• timeout number (optional) — No description

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 'node' node

Signature

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

Parameters

• formulaString string — No description

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();

whenComputingCompleteAsync

Signature

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

Parameters

• timeout number (optional) — No description

Returns

• Promise<boolean> — See signature above.