How do I evaluate a formula?

Aspose.Cells Product Family
1

Given the following code:

using Aspose.Cells
// {...}
Workbook workbook = new Workbook();
Worksheet virtualWorksheet = workbook.Worksheets[0];
virtualWorksheet.Cells[0, 0].Formula ="=1<2";

How do I evaluate the formula in Cell [0,0]?

bool isCellTrue = virtualWorksheet.Cells[0, 0].BoolValue; // will generate an exception at run-time

2

Hi,

Please call Workbook.CalculateFormula() method to calculate all the formulas in the workbook before getting/retrieving the calculated value at runtime, e.g.

Workbook workbook = new Workbook();
Worksheet virtualWorksheet = workbook.Worksheets[0];
virtualWorksheet.Cells[0, 0].Formula ="=1<2";
workbook.CalculateFormula();
bool isCellTrue = virtualWorksheet.Cells[0, 0].BoolValue;


Thank you.

3

That works. Many thanks.

4

Hi,


For me the CalculateFormula does not works with an External Liks formulat (formuat refer to another Excel file)

Please Help.

5

Hi,


Well, Aspose.Cells may not accurately update the formulas to get the calculated results in the template having external links or datasource etc., but, I think you may try to use Workbook.UpdateLinkedDataSource () method and try opening your underlying source workbook in it, it may sort your issue out.

Sample code:

//this workbook has an external reference to “e:\test\source.xlsx”
Workbook wb = new Workbook(“e:\test\dest.xlsx”);
Workbook wbSrc = new Workbook(“e:\test\source.xlsx”);
wbSrc.Worksheets[0].Cells[“C8”].PutValue(10);
wbSrc.CalculateFormula();
MessageBox.Show(wbSrc.Worksheets[0].Cells[“C9”].StringValue);
//wb.Initialize();
wbSrc = new Workbook(“e:\test\source.xlsx”);
wb.UpdateLinkedDataSource(new Workbook[] { wbSrc });
wb.CalculateFormula();
MessageBox.Show(wb.Worksheets[0].Cells[“C10”].StringValue);
wb.Save(“e:\test\outdest.xlsx”);