类: FFormula

get lexerTreeBuilder(): LexerTreeBuilder

calculationEnd(callback): IDisposable

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

calculationProcessing(callback): IDisposable

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

calculationResultApplied(callback): IDisposable

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

calculationStart(callback): IDisposable

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

executeCalculation(): void

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

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

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(timeout?): Promise<...[]>

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(param, timeout?): Promise<any>

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(formulaString, unitId): any

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

{
  "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(unitRanges, timeout?): Promise<...[]>

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(unitRanges, timeout?): Promise<...[]>

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(unitRanges, timeout?): Promise<IFormulaDependentsAndInRangeResults>

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

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

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

onCalculationEnd(): Promise<void>

onCalculationResultApplied(): Promise<void>

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(
   name, 
   func, 
   description?): IDisposable

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

// 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(
   name, 
   func, 
   description?): IDisposable

// 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 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(formulaString): any[]

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

setFormulaReturnDependencyTree(value): void

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(calculationMode): void

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

setMaxIteration(maxIteration): 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(): void

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

whenComputingCompleteAsync(timeout?): Promise<boolean>

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