Ways to Calculate Formulas

Introduction

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

Adding Formulas & Calculating Results

Aspose.Cells supports most of the formulas or functions that are the part of Microsoft Excel. they can be used through the API or using designer spreadsheets. Aspose.Cells supports a huge set of mathematical, string, boolean, date/time, statistical, lookup and reference formulas.

Use the Cell.Formula method to add a formula to a cell. When applying a formula to a cell, always begin the string with an equal sign (=) as you do when creating a formula in Microsoft Excel. Use a comma (,) to delimit function parameters.

To calculate the results of formulas, call the Workbook.CalculateFormula() method which processes all the formulas embedded in an Excel file. Please see the following sample code that adds the formula and calculates its results. Please check the output excel file generated with this code.

Sample Code

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Output directory path
StringPtr outPath = new String("..\\Data\\Output\\");

//Path of output excel file
StringPtr outputAddingFormulasAndCalculatingResults = outPath->StringAppend(new String("outputAddingFormulasAndCalculatingResults.xlsx"));

//Create workbook
intrusive_ptr<IWorkbook> wb = Factory::CreateIWorkbook();

//Access first worksheet in the workbook
intrusive_ptr<IWorksheet> ws = wb->GetIWorksheets()->GetObjectByIndex(0);

//Adding integer values to cells A1, A2 and A3
ws->GetICells()->GetObjectByIndex(new String("A1"))->PutValue(10);
ws->GetICells()->GetObjectByIndex(new String("A2"))->PutValue(20);
ws->GetICells()->GetObjectByIndex(new String("A3"))->PutValue(70);

//Adding a SUM formula to "A4" cell
ws->GetICells()->GetObjectByIndex(new String("A4"))->SetFormula(new String("=SUM(A1:A3)"));

//Calculating the results of formulas
wb->CalculateFormula();

//Get the calculated value of the cell
intrusive_ptr<String> strVal = ws->GetICells()->GetObjectByIndex(new String("A4"))->GetStringValue();

//Print the calculated value on console
StringPtr str1 = new String("Calculated Result: ");
Console::WriteLine(str1->StringAppend(strVal));

//Saving the workbook
wb->Save(outputAddingFormulasAndCalculatingResults);

Direct Calculation of Formula

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 Worksheet.CalculateFormula(String formula) method to calculate the results of such formulas without adding them to worksheet.

The code below produces the following output.

 Value of A1: 20

Value of A2: 30

Result of Sum(A1:A2): 50

Sample Code

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Create workbook
intrusive_ptr<IWorkbook> wb = Factory::CreateIWorkbook();

//Access first worksheet in the workbook
intrusive_ptr<IWorksheet> ws = wb->GetIWorksheets()->GetObjectByIndex(0);

//Put 20 in cell A1
intrusive_ptr<ICell> cellA1 = ws->GetICells()->GetObjectByIndex(new String("A1"));
cellA1->PutValue(20);

//Put 30 in cell A2
intrusive_ptr<ICell> cellA2 = ws->GetICells()->GetObjectByIndex(new String("A2"));
cellA2->PutValue(30);

//Calculate the Sum of A1 and A2
intrusive_ptr<Aspose::Cells::System::Object> results = ws->CalculateFormula(new String("=Sum(A1:A2)"));

//Print the output
StringPtr str1 = new String("Value of A1: ");
Console::WriteLine(str1->StringAppend(cellA1->GetStringValue()));
StringPtr str2 = new String("Value of A2: ");
Console::WriteLine(str2->StringAppend(cellA2->GetStringValue()));
StringPtr str3 = new String("Result of Sum(A1:A2): ");
Console::WriteLine(str3->StringAppend(results->ToString()));

Calculating Formulas Once Only

When Workbook.CalculateFormula() is called to calculate the values of formulas in a workbook template, Aspose.Cells creates a calculating chain. It increases performance when formulas are calculated for the second or third time.

However, if the template contains lots of formulas, the first time the formula is calculated can consume a lot of CPU processing time and memory.

Aspose.Cells allows you to turn off creating a calculating chain which is useful when you want to calculate formulas only once.

Please call Workbook.GetISettings().SetCreateCalcChain() with false parameter. You can use the provided excel file to test this code.

Sample Code

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Source directory path
StringPtr dirPath = new String("..\\Data\\Formulas\\");

//Path of input excel file
StringPtr sampleCalculatingFormulasOnceOnly = dirPath->StringAppend(new String("sampleCalculatingFormulasOnceOnly.xlsx"));

//Create workbook
intrusive_ptr<IWorkbook> wb = Factory::CreateIWorkbook(sampleCalculatingFormulasOnceOnly);

//Set the CreateCalcChain as false
wb->GetISettings()->SetCreateCalcChain(false);

//Get the time in milliseconds before formula calculation
int before_miliseconds = Aspose::Cells::System::DateTime::GetNow()->GetMillisecond();

//Calculate the workbook formulas
wb->CalculateFormula();

//Get the time in milliseconds after formula calculation
int after_miliseconds = Aspose::Cells::System::DateTime::GetNow()->GetMillisecond();

//Print the difference in milliseconds
StringPtr str1 = new String("Workbook Formula Calculation Elapsed Time in Milliseconds: ");
Console::WriteLine(str1->StringAppend(Int32Helper::ToString(after_miliseconds - before_miliseconds)));