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

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

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.

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 fSheet = fWorkbook.getActiveSheet();
const cellA1 = fSheet.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 fSheet = fWorkbook.getActiveSheet();
const cellA1 = fSheet.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 fSheet = fWorkbook.getActiveSheet();
const cellA1 = fSheet.getRange('A1');
cellA1.setValue('World');
const cellA2 = fSheet.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 fSheet = fWorkbook.getActiveSheet();
const cellA1 = fSheet.getRange('A1');
cellA1.setValue(100);
const cellA2 = fSheet.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 fSheet = fWorkbook.getActiveSheet();
const cellA1 = fSheet.getRange('A1');
cellA1.setValue(1);
const cellA2 = fSheet.getRange('A2');
cellA2.setValue(2);
const cellA3 = fSheet.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 fSheet = fWorkbook.getActiveSheet();
const cellA1 = fSheet.getRange('A1');
cellA1.setValue('World');
const cellA2 = fSheet.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