Formulas in Referenced Cells are not updating in newly created excel file in binary format (.xlsb)

Aspose.Cells Product Family
1

Hi

I am creating a excel file on the fly. To implement this functionality, i have a default excel in my application. I am populating that file with data from database and creating a new excel file in binary format.
But i am facing issue in refreshing the formulas after populating file with data.

For Eg. i am inserting the date in D12 CELL of excel sheet. In D14 CELL, i have a reference of D12 Cell, but D14 Cell is not updating even after updating D12 cell with data.

I have also used following lines but still formulas are not updating

excelWorkbook1.CalculateFormula(true);
excelWorkbook1.Settings.CalcMode = CalcModeType.Automatic;

Please help me to resolve this issue. Which property i need to set to update the referenced cells in the excel file on runtime?

For more info, I am able to update the formulas manually by using (Ctrl+Alt+Shift+F9)

Thanks

2

Hi,


Thanks for providing us some details.

Well, surely, it should update formula cell value (where the formula is referring to other cell(s) in the same spreadsheet) when you modify/update the source cell value as you are calling Workbook.CalculateFormula() method before retrieving updated cell value from the formula cell. We appreciate if you could provide us a simple console application, zip it and post us here to reproduce the issue on our end, we will check it soon. Also, provide your template files (default Excel file and output XLSB file, etc.). Please remove any interdependency of database in your project and use only dynamic DataSet or DataTable in your code (using .NET APIs). This will help us really to evaluate your issue precisely and quickly to consequently figure it out soon.

Thank you.
3

Hi Amjad,

Thank You so much for the reply.

As per your advise, i have created the console application. When you will run the application, it will create a new excel file named “New Template.xlsb”.

In this excel, A2 CELL is referencing the A1 CELL. On the generating of new excel file, A1 is getting updated but its reference in A2 CELL is not updating. When i am doing (ctrl+alt+shift+F9) manually, it is updating.

Please find the attached zip folder.

Thanks

4

Hi,


Thanks for providing us sample project with template XLSB file.

Please try our latest version/fix: Aspose.Cells for .NET v8.8.2.3
I have tested your project using your template file and it works fine with it. I got correct value at runtime and also the generated file has updated value against the formula cell (A2). I have attached the output Excel file against the following sample code:
e.g
Sample code:

var DefaultTemplatepath = @“E:\test2\formulas in referenced\ExcelConsoleApp\ExcelConsoleApp\Default Template.xlsb”;
var NewTemplatepath = @“E:\test2\formulas in referenced\ExcelConsoleApp\ExcelConsoleApp\NewTemplate1.xlsb”;
Workbook excelWorkbook1 = new Workbook(DefaultTemplatepath);
excelWorkbook1.Worksheets[0].Cells[“A1”].PutValue(“42369”, true);

excelWorkbook1.CalculateFormula(true);
MessageBox.Show(excelWorkbook1.Worksheets[0].Cells[“A2”].StringValue); //

31-Mar-16 - Ok
excelWorkbook1.Settings.CalcMode = CalcModeType.Automatic;
excelWorkbook1.Save(NewTemplatepath);

Since you are using some older version of the product (i.e., Aspose.Cells for .NET v8.2.1) which might have an issue with the "EOMONTH" formula/ function or so. so, kindly upgrade to and use our latest version/fix of the product which works fine.

Let us know if you still have any issue.

Thank you.
5

Hi Amjad,

Thank you so much for your help.
Issue gets resolved using the new version of Aspose.Cells.


6

Hi,


Good to know that your issue is resolved by the new version/fix. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.