Calculate Formulas

Adding Formulas & Calculating Results

Aspose.Cells has an embedded formula calculation engine. Not only it can re-calculate formulas imported from designer templates but also it supports to calculate the results of formulas added at runtime.

Aspose.Cells supports most of the formulas or functions that are part of Microsoft Excel(Read a list of the functions supported by the calculation engine). Those functions can be used through the APIs or designer spreadsheets. Aspose.Cells supports a huge set of mathematical, string, boolean, date/time, statistical, database, lookup, and reference formulas.

Use the Formula property or SetFormula(…) methods of the Cell class to add a formula to a cell. When applying a formula, always begin the string with an equal sign (=) as you do when creating a formula in Microsoft Excel and use a comma (,) to delimit function parameters.

To calculate the results of formulas, user may call the CalculateFormula method of the Workbook class which processes all formulas embedded in an Excel file. Or, user may call the CalculateFormula method of the Worsheet class which processes all formulas embedded in a sheet. Or, user also may call the Calculate method of the Cell class which processes the formula of one Cell:

Important to Know for Formulas

The Formula property and SetFormula(…) methods of the Cell class work differently from the Calculate methods of the Workbook, Worksheet and Cell classes. The Formula property and SetFormula(…) methods simply add the formula to a cell but do not calculate the result at runtime. To get the result of the formulas, please call Calculate methods.

Direct Calculation of Formula

Aspose.Cells has an embedded formula calculation engine. As well as calculating formulas imported from a designer file, Aspose.Cells can calculate formula results directly.

Sometimes, you need to calculate formula results directly without adding them into a worksheet. The values of the cells used in the formula already exist in a worksheet and all you need is to find the result of those values based on some Microsoft Excel formula without adding the formula in a worksheet.

You can use Aspose.Cells' formula calculation engine APIs for Worksheet to calculate the results of such formulas without adding them to the worksheet:

Above code produces the following output:

Value of A1: 20
Value of A2: 30
Result of Sum(A1:A2): 50.0

How to Calculate Formulas repeatedly

When there are lots of formulas in the workbook and user needs to calculate them repeatedly with modifying only a small part of them, it may be helpful for performance to enable the formula calculation chain: FormulaSettings.EnableCalculationChain.

Important to Know

By default the calculation chain is disabled. Because creating the chain also needs extra time, the first time of calculating formulas(Workbook.CalculateFormula(…)) may consume more CPU processing time and memory when comparing with calculating formulas without chain. If user does not need to calculate formulas repeatedly, the default behavior(calculating formula directly without creating calculation chain) should be the better way.

Advance topics

Manage formulas of Excel files Manage Comments and Notes