How I can read a value in a cell with formula from an existing excel file

Aspose.Cells Product Family
1

Hi

How I can read a value in a cell with formula (I’ve gat a1=4, b1=4 and c1=a1+b1) from an existing excel file.

I can’t read the value of cell c1.

Her is the code (asp.net,c#) that I’m using:



Excel excel = new Excel();

string path = MapPath(“.”);

path = path.Substring(0, path.LastIndexOf(“\”));

string designerFile = path + “\Designer\Book1.xls”;



excel.Open(designerFile);

Cells cellsSheets1 = excel.Worksheets[0].Cells;

Response.Write(“Count cells in af Worksheets :”+excel.Worksheets[0].Cells.Count);

//get value

Response.Write(" Get af value at a1 :“+cellsSheets1.GetAt(“a1”).IntValue);

Response.Write(” Get af value at b1 :“+cellsSheets1.GetAt(“b1”).IntValue);

Response.Write(” Get af value at b1 :"+cellsSheets1.GetAt(“c1”).IntValue);

I’m having problem with the last line.

Best regards

Daniel

2

Hi Daniel,

Would you send your Book1.xls file to excel@aspose.com?

You’re encouraged to post here further.

Thanks for your cooperation.

3

Dear Daniel,

I understand your problem.
Because it’s hard to do calculation based on Excel function. Aspose.Excel don’t calculate the formula and get the result value. To meet your need, I will try to some modification in Aspose.Excel.
But to keep the version consistency, you will get the hotfix till 10/15.
And the designer file should be created by Microsoft Excel.

4

Dear Daniel,
Excel library is now replaced by Aspose.Cells which has all the advanced features available in the latest spreadsheet processing software. Using this latest version you can calculate the formulas and read the values calculated by the formula. Give a try to the following sample code to get the calculated values:

// 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 more details have a look at the following article:
Ways to Calculate Formulas

Download the latest version of Aspose.Cells for .NET from the following link:
Aspose.Cells for .NET (Latest Version)

You can download the latest demos here.