Skip to Content
ClassesFFormula

类: FFormula

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

继承

  • FBase.IFFormulaSheetsMixin

访问器

lexerTreeBuilder

Getter 签名

get lexerTreeBuilder(): LexerTreeBuilder

The tree builder for formula string.

返回

LexerTreeBuilder

方法

calculationEnd()

calculationEnd(callback): IDisposable

Listening calculation ends.

参数

参数类型描述
callback(functionsExecutedState) => voidThe callback function to be called when the formula calculation ends.

返回

IDisposable

The disposable instance.

示例

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

calculationProcessing()

calculationProcessing(callback): IDisposable

Listening calculation processing.

参数

参数类型描述
callback(stageInfo) => voidThe callback function to be called when the formula calculation is in progress.

返回

IDisposable

The disposable instance.

示例

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

calculationResultApplied()

calculationResultApplied(callback): IDisposable

Listens for the moment when formula-calculation results are applied.

This event fires after the engine completes a calculation cycle and dispatches a SetFormulaCalculationResultMutation. The callback is invoked during an idle frame to avoid blocking UI updates.

参数

参数类型描述
callback(result) => voidA function called with the calculation result payload once the result-application mutation is emitted.

返回

IDisposable

A disposable used to unsubscribe from the event.

示例

const formulaEngine = univerAPI.getFormula();
 
const dispose = formulaEngine.calculationResultApplied((result) => {
  console.log('Calculation results applied:', result);
});
 
// Later…
dispose.dispose();

calculationStart()

calculationStart(callback): IDisposable

Listening calculation starts.

参数

参数类型描述
callback(forceCalculation) => voidThe callback function to be called when the formula calculation starts.

返回

IDisposable

The disposable instance.

示例

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

executeCalculation()

executeCalculation(): void

Start the calculation of the formula.

返回

void

示例

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

executeFormulas()

executeFormulas(formulas, timeout?): Promise<IFormulaExecuteResultMap>

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.

参数

参数类型描述
formulasIFormulaStringMapNested structure (unit → sheet → row → column) describing formulas and their decomposed sub-expressions.
timeout?numberOptional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

返回

Promise<IFormulaExecuteResultMap>

A promise that resolves with the computed value map mirroring the input structure.

示例

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

getAllDependencyTrees()

getAllDependencyTrees(timeout?): Promise<...[]>

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.

参数

参数类型描述
timeout?numberOptional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

返回

Promise<…[]>

A promise that resolves with the array of dependency trees.

示例

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

getCellDependencyTree(param, timeout?): Promise<any>

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.

参数

参数类型描述
param{ column: number; row: number; sheetId: string; unitId: string; }The target cell location: - unitId The workbook ID. - sheetId The sheet ID. - row The zero-based row index. - column The zero-based column index.
param.columnnumber-
param.row?number-
param.sheetId?string-
param.unitId?string-
timeout?numberOptional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

返回

Promise<any>

A promise that resolves with the dependency tree or undefined if no tree exists for that cell.

示例

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

getInRangeFormulas()

getInRangeFormulas(unitRanges, timeout?): Promise<...[]>

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.

参数

参数类型描述
unitRangesIUnitRange[]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?numberOptional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

返回

Promise<…[]>

A promise that resolves with an array of IFormulaDependencyTreeJson describing every formula found in the provided ranges along with their parent/child relationships.

示例

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

getRangeDependents(unitRanges, timeout?): Promise<...[]>

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.

参数

参数类型描述
unitRangesIUnitRange[]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?numberOptional timeout in milliseconds. If no result is received within this period, the promise will be rejected.

返回

Promise<…[]>

A promise that resolves with an array of IFormulaDependencyTreeJson representing formulas and their relationships within the dependency graph.

示例

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

moveFormulaRefOffset()

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

Offsets the formula

参数

参数类型描述
formulaStringstringThe formula string to offset
refOffsetXnumberThe offset column
refOffsetYnumberThe offset row
ignoreAbsolute?booleanWhether to ignore the absolute reference

返回

string

The offset formula string

示例

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

onCalculationEnd()

onCalculationEnd(): Promise<void>

返回

Promise<void>

This method returns a promise that resolves when the calculation is complete.

已被弃用

Use whenComputingCompleteAsync instead.

onCalculationResultApplied()

onCalculationResultApplied(): Promise<void>

Waits for formula-calculation results to be applied.

This method resolves under three conditions:

  1. A real calculation runs and the engine emits a “calculation started” signal, followed by a “calculation result applied” signal.
  2. No calculation actually starts within 500 ms — the method assumes there is nothing to wait for and resolves automatically.
  3. A global 30 s timeout triggers, in which case the promise rejects.

The API internally listens to both “calculation in progress” events and “calculation result applied” events, ensuring it behaves correctly whether formulas are recalculated or skipped due to cache/state.

返回

Promise<void>

A promise that resolves when calculation results are applied or when no calculation occurs within the start-detection window.

示例

const formulaEngine = univerAPI.getFormula();
 
// Wait for formula updates to apply before reading values.
await formulaEngine.onCalculationResultApplied();
 
const value = sheet.getRange("C24").getValue();
console.log("Updated value:", value);

registerAsyncFunction()

调用签名

registerAsyncFunction(
   name, 
   func, 
   description?): IDisposable

Register a custom asynchronous formula function.

参数
参数类型描述
namestringThe name of the function to register. This will be used in formulas (e.g., =ASYNCFUNC()).
funcIRegisterAsyncFunctionThe async implementation of the function.
description?stringA string describing the function’s purpose and usage.
返回

IDisposable

A disposable object that will unregister the function when disposed.

示例
const formulaEngine = univerAPI.getFormula();
formulaEngine.registerAsyncFunction(
  'RANDOM_DELAYED',
  async () => {
    await new Promise(resolve => setTimeout(resolve, 500));
    return Math.random();
  },
  'Mock a random number generation function'
);
 
// Use the function in a cell
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cellA1 = fWorksheet.getRange('A1');
cellA1.setValue({ f: '=RANDOM_DELAYED()' });
 
// After 0.5 second, A1 will display a random number

调用签名

registerAsyncFunction(
   name, 
   func, 
   options?): IDisposable

Register a custom asynchronous formula function with description.

参数
参数类型描述
namestringThe name of the function to register. This will be used in formulas (e.g., =ASYNCFUNC()).
funcIRegisterAsyncFunctionThe async implementation of the function.
options?{ description: any; locales: ILocales; }Object containing locales and description.
options.description?anyObject containing description.
options.locales?ILocalesObject containing locales.
返回

IDisposable

A disposable object that will unregister the function when disposed.

示例
// Mock a user score fetching function
const formulaEngine = univerAPI.getFormula();
formulaEngine.registerAsyncFunction(
  'FETCH_USER_SCORE',
  async (userId) => {
    await new Promise(resolve => setTimeout(resolve, 1000));
    // Mock fetching user score from database
    return userId * 10 + Math.floor(Math.random() * 20);
  },
  {
    description: 'customFunction.FETCH_USER_SCORE.description',
    locales: {
      'zhCN': {
        'customFunction': {
          'FETCH_USER_SCORE': {
            'description': '从数据库中获取用户分数'
          }
        }
      },
      'enUS': {
        'customFunction': {
          'FETCH_USER_SCORE': {
            'description': 'Mock fetching user score from database'
          }
        }
      }
    }
  }
);
 
// Use the function in a cell
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cellA1 = fWorksheet.getRange('A1');
cellA1.setValue({ f: '=FETCH_USER_SCORE(42)' });
 
// After 1 second, A1 will display a score

registerFunction()

调用签名

registerFunction(
   name, 
   func, 
   description?): IDisposable

Register a custom synchronous formula function.

参数
参数类型描述
namestringThe name of the function to register. This will be used in formulas (e.g., =MYFUNC()).
funcIRegisterFunctionThe implementation of the function.
description?stringA string describing the function’s purpose and usage.
返回

IDisposable

A disposable object that will unregister the function when disposed.

Examples
// Register a simple greeting function
const formulaEngine = univerAPI.getFormula();
formulaEngine.registerFunction(
  'HELLO',
  (name) => `Hello, ${name}!`,
  'A simple greeting function'
);
 
// Use the function in a cell
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cellA1 = fWorksheet.getRange('A1');
cellA1.setValue('World');
const cellA2 = fWorksheet.getRange('A2');
cellA2.setValue({ f: '=HELLO(A1)' });
 
// A2 will display: "Hello, World!"
formulaEngine.calculationEnd((functionsExecutedState) => {
  if (functionsExecutedState === 3) {
    console.log(cellA2.getValue()); // Hello, World!
  }
})
// Register a discount calculation function
const formulaEngine = univerAPI.getFormula();
formulaEngine.registerFunction(
  'DISCOUNT',
  (price, discountPercent) => price * (1 - discountPercent / 100),
  'Calculates final price after discount'
);
 
// Use the function in a cell
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cellA1 = fWorksheet.getRange('A1');
cellA1.setValue(100);
const cellA2 = fWorksheet.getRange('A2');
cellA2.setValue({ f: '=DISCOUNT(A1, 20)' });
 
// A2 will display: 80
formulaEngine.calculationEnd((functionsExecutedState) => {
  if (functionsExecutedState === 3) {
    console.log(cellA2.getValue()); // 80
  }
})
// Registered formulas support lambda functions
const formulaEngine = univerAPI.getFormula();
formulaEngine.registerFunction(
  'CUSTOMSUM',
  (...variants) => {
    let sum = 0;
    const last = variants[variants.length - 1];
 
    if (last.isLambda && last.isLambda()) {
      variants.pop();
      const variantsList = variants.map((variant) => Array.isArray(variant) ? variant[0][0]: variant);
      sum += last.executeCustom(...variantsList).getValue();
    }
 
    for (const variant of variants) {
      sum += Number(variant) || 0;
    }
 
    return sum;
  },
  'Adds its arguments'
);
 
// Use the function in a cell
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cellA1 = fWorksheet.getRange('A1');
cellA1.setValue(1);
const cellA2 = fWorksheet.getRange('A2');
cellA2.setValue(2);
const cellA3 = fWorksheet.getRange('A3');
cellA3.setValue({ f: '=CUSTOMSUM(A1,A2,LAMBDA(x,y,x*y))' });
 
// A3 will display: 5
formulaEngine.calculationEnd((functionsExecutedState) => {
  if (functionsExecutedState === 3) {
    console.log(cellA3.getValue()); // 5
  }
})

调用签名

registerFunction(
   name, 
   func, 
   options?): IDisposable

Register a custom synchronous formula function with localization support.

参数
参数类型描述
namestringThe name of the function to register. This will be used in formulas (e.g., =MYFUNC()).
funcIRegisterFunctionThe implementation of the function.
options?{ description: any; locales: ILocales; }Object containing locales and description.
options.description?anyObject containing description.
options.locales?ILocalesObject containing locales.
返回

IDisposable

A disposable object that will unregister the function when disposed.

示例
// Register a simple greeting function
const formulaEngine = univerAPI.getFormula();
formulaEngine.registerFunction(
  'HELLO',
  (name) => `Hello, ${name}!`,
  {
    description: 'customFunction.HELLO.description',
    locales: {
      'zhCN': {
        'customFunction': {
          'HELLO': {
            'description': '一个简单的问候函数'
          }
        }
      },
      'enUS': {
        'customFunction': {
          'HELLO': {
            'description': 'A simple greeting function'
          }
        }
      }
    }
  }
);
 
// Use the function in a cell
const fWorkbook = univerAPI.getActiveWorkbook();
const fWorksheet = fWorkbook.getActiveSheet();
const cellA1 = fWorksheet.getRange('A1');
cellA1.setValue('World');
const cellA2 = fWorksheet.getRange('A2');
cellA2.setValue({ f: '=HELLO(A1)' });
 
// A2 will display: "Hello, World!"
formulaEngine.calculationEnd((functionsExecutedState) => {
  if (functionsExecutedState === 3) {
    console.log(cellA2.getValue()); // Hello, World!
  }
})

sequenceNodesBuilder()

sequenceNodesBuilder(formulaString): any[]

Resolves the formula string to a ‘node’ node

参数

参数类型描述
formulaStringstringThe formula string to resolve

返回

any[]

The nodes of the formula string

示例

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

setInitialFormulaComputing()

setInitialFormulaComputing(calculationMode): void

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.

参数

参数类型描述
calculationModeCalculationModeThe calculation mode of the formula.

返回

void

示例

const formulaEngine = univerAPI.getFormula();
formulaEngine.setInitialFormulaComputing(0);

setMaxIteration()

setMaxIteration(maxIteration): void

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

参数

参数类型描述
maxIterationnumberThe maximum number of iterations. The default value is 1.

返回

void

示例

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

stopCalculation(): void

Stop the calculation of the formula.

返回

void

示例

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

whenComputingCompleteAsync()

whenComputingCompleteAsync(timeout?): Promise<boolean>

Wait for computing in the Univer instance to complete. Please note that this does not only include formula calculation, but also other computing tasks, e.g. pivot table calculation.

参数

参数类型描述
timeout?numberThe maximum time to wait for the computing to complete, in milliseconds. The default value is 30,000 milliseconds.

返回

Promise<boolean>

This method returns true if the computing is complete. If the timeout is reached, this method returns false.

示例

const formulaEngine = univerAPI.getFormula();
formulaEngine.whenComputingCompleteAsync(3000).then((isComplete) => {
  console.log('Computing complete:', isComplete);
});
FFilterFGenericPivotTable