CalculateFormula (Enterprise edition)

Aspose.Cells Product Family
1

Hi,
I read this response to an user who have the same problem:

"Your understanding is correct. While saving file in MS Excel, Excel will calculate formulas and save the result to file. Aspose.Excel can read those values.

However, if you use Aspose.Excel to set formula to an Excel file, it won’t calculate the result. So you cannot get those result in Professional edition. "


I need that Aspose.Excel calculate in run-time the formula, not after save.
But our company (Lafarge Canada) had the Enterprise version, and this method don’t work.

Please send us a clue,
Cristian

2

Do you call Excel.CalculateFormula method before you retrieve data from those cells with formula?

And what functions are you using? Now Aspose.Excel only support following functions: www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Excel.CalculateFormula.html.

And we are extending this list. Please let me know if you find some functions in your files are not supported.

3

Yes, I call CalculateFormula method, but don’t work. Is the MIN() function.
Why?

4

You can use Cell.Value to get result of that cell. Could you please post the whole function here? Or post your file? Thanks.

5

@crisef,
Aspose.Excel is discarded and no more under active development. It is replaced by Aspose.Cells that is much advanced and contains all the latest features of different versions of MS Excel. Enhancements are made along with the provision of using CalculateFormula such that you can work with calculation engine. Here is an example that demonstrates the formula calculation using this new product.

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

For trials purpose, download the latest version here:
Aspose.Cells for .NET (Latest Version)

Here is a ready to run solution which can be used to test the product features with minimal effort.