Using excel file for calculation.

[Attila Kenyhercz]

Hi,
I want to implement a code, which writes some values into an excel file, than reads a cell which is a formula calculates values from previously entered data.

I create an excel file like this:
A1 = "1"
A2 = "2"
A3 = A1 + A2 // = "3"

then I open it from my application, and I set the new values:

A1 = 4
A2 = 6

after this I read the value of A3 and it is still "3".
It appears that the value of the A3 cell is not recalculated.

My question would be, how to force Syncfusion.XlsIO to recalculate the formulas.

The code I have is:
#using Syncfusion.XlsIO;

ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;

application.DefaultVersion = ExcelVersion.Excel2007;

IWorkbook workbook = excelEngine.Excel.Workbooks.Open("test.xls", ExcelOpenType.Automatic);

IWorksheet sheet = workbook.Worksheets[0];

sheet.Range["A1"].Value = "4";
sheet.Range["A2"].Value = "6";
MessageBox.Show(sheet.Range["A3"].FormulaNumberValue.ToString());

I have referenced the
Core, Compression.Base, XlsIO.Windows, XsIO.Base

Thank you for your help...
Attila

[Sathish Babu R]

Hi Attila,

Thank you for your interest in Syncfusion products.

In our XlsIO, We can calculate the formula at runtime using Essential Calculate which is already integrated with the Essential XlsIO. We have created the sample with custom class (XlsIOCalcWorkbook) to calculate the formula at runtime. Could you please try the sample and let us know if this helps you.

Sample:
http://help.syncfusion.com/samples/xlsio.windows/supports/F97021.zip

Please, let us know if this helps you.

Regards,
Sathish

[Riccardo De Agostini]

I'm having a very similar problem. Unfortunately, the link to the sample is dead. Can you make the sample available again?

Thanks and best regards

Riccardo De Agostini

[Sudha Ramachandran]

Hi Riccardo,

 

The scenario is similar to the query reported by you in the forum 117677, for which we have provided a sample. Please have a follow-up in the mentioned forum and get back to us in that if you need any clarification.

 

Regards,

Sudha Ramachandran