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:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Instantiating a Workbook object | |
Workbook workbook = new Workbook(); | |
// Adding a new worksheet to the Excel object | |
int sheetIndex = workbook.Worksheets.Add(); | |
// Obtaining the reference of the newly added worksheet by passing its sheet index | |
Worksheet worksheet = workbook.Worksheets[sheetIndex]; | |
// Adding a value to "A1" cell | |
worksheet.Cells["A1"].PutValue(1); | |
// Adding a value to "A2" cell | |
worksheet.Cells["A2"].PutValue(2); | |
// Adding a value to "A3" cell | |
worksheet.Cells["A3"].PutValue(3); | |
// Adding a SUM formula to "A4" cell | |
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)"; | |
// Calculating the results of formulas | |
workbook.CalculateFormula(); | |
// Get the calculated value of the cell | |
string value = worksheet.Cells["A4"].Value.ToString(); | |
// Saving the Excel file | |
workbook.Save(dataDir + "output.xls"); |
Important to Know for Formulas
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:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create directory if it is not already present. | |
bool IsExists = System.IO.Directory.Exists(dataDir); | |
if (!IsExists) | |
System.IO.Directory.CreateDirectory(dataDir); | |
// Create a workbook | |
Workbook workbook = new Workbook(); | |
// Access first worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
// Put 20 in cell A1 | |
Cell cellA1 = worksheet.Cells["A1"]; | |
cellA1.PutValue(20); | |
// Put 30 in cell A2 | |
Cell cellA2 = worksheet.Cells["A2"]; | |
cellA2.PutValue(30); | |
// Calculate the Sum of A1 and A2 | |
var results = worksheet.CalculateFormula("=Sum(A1:A2)"); | |
// Print the output | |
System.Console.WriteLine("Value of A1: " + cellA1.StringValue); | |
System.Console.WriteLine("Value of A2: " + cellA2.StringValue); | |
System.Console.WriteLine("Result of Sum(A1:A2): " + results.ToString()); |
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.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Load the template workbook | |
Workbook workbook = new Workbook(dataDir + "book1.xls"); | |
// Print the time before formula calculation | |
Console.WriteLine(DateTime.Now); | |
// Set the CreateCalcChain as tue | |
workbook.Settings.FormulaSettings.EnableCalculationChain = true; | |
// Calculate the workbook formulas | |
workbook.CalculateFormula(); | |
// Print the time after formula calculation | |
Console.WriteLine(DateTime.Now); | |
//change the value of one cell | |
workbook.Worksheets[0].Cells["A1"].PutValue("newvalue"); | |
//re-calculate those formulas which depend on cell A1 | |
workbook.CalculateFormula(); |
Important to Know
Advance topics
- Add Cells to Microsoft Excel Formula Watch Window
- Calculating IFNA function using Aspose.Cells
- Calculation of Array Formula of Data Tables
- Calculation of Excel 2016 MINIFS and MAXIFS functions
- Decrease the Calculation Time of Cell.Calculate method
- Detecting Circular Reference
- Direct calculation of custom function without writing it in a worksheet
- Implement Custom Calculation Engine to extend the Default Calculation Engine of Aspose.Cells
- Interrupt or Cancel the Formula Calculation of Workbook
- Returning a Range of Values using AbstractCalculationEngine
- Returning a Range of Values using ICustomFunction
- Setting Formula Calculation Mode of Workbook
- Using FormulaText function in Aspose.Cells
- Using ICustomFunction Feature