Hi all, I have a workbook that I open that is very large. I write data to a row in that workbook. The cells above that cell where I write to have formulas in them. If I just import that workbook the values of the formula cells are either nothing or the older value that was in the cell where the formulas are.
So unless I open the workbook which will recalculate and actually save the file after looking at the new numbers if the user closes the file the import will not have the correct data. I tried using the Calculate workbook but that seems to take a huge amount of time and was running for over 10 minutes and just crashed as the workbook is large about 30 meg. If I open the file and then save or even calculate now or sheet in the excel file itself it is super fast. How can I workaround this issue to make sure the values are updated even if the user does not save the file after looking at the values and closes it?
I thought of using a Save Macro on open but that is also something I would rather not do.
Thanks all
Hi,
Thanks for the details.
Actually, in the latest version (4.5.1.x), Aspose.Cells Formula Calculation engine calculates all the formulas in the workbook when calling Workbook.CalculateFormula() method. And we are evaluating on the feature that defines how to calculate only those cells which are needed to be re-calculated after certain updates/modifications for the data.
If you do not want to call Workbook.CalculateFormula method for the performance account, you should set Workbook.ReCalcOnOpen attribute to set it to true to force MS Excel to re-calculate all the formulas on opening the resultant file into it. Check the following codes:
Workbook workbook = new Workbook();
workbook.ReCalcOnOpen = true;
workbook.Save(@"C:\Book1.xls");
Thank you.
I run the ReCalcOnOpen and response stream the file. I save the file to disk, if I open the file I the changes I made to the file dynamically. If I just close the file though without saving it the same issue happens and when I loop through the row cells the values are not there unless the file is saved after I open it and it recalcs.