calcualte the whole workbook like F9 in excel

[Adi Hirtzel]

Hi,

I'm looking for a way to calculate the whole workbook like press 'F9' in Excel. Do you have any suggestions or samples?

Many Thanks

[Geetha M]

Hi Adi,

Thank you for your interest in Syncfusion products.

It is possible to refresh the whole workbook as mentioned in the sample located below:

My Documents\Syncfusion\EssentialStudio\7.2.0.20\Web\XlsIO.Web\Samples\3.5\Data Management\ComputeAllFormulas

Please try this and let me know if you have any questions.

Regards,
Geetha

[Bill Korchinski]

Hi Geetha,

I am trying to do this too. I have a large worksheet that I need to re-calculate about 5000 times, so speed is an issue. When I open the workbook in Excel & press F9, the result appears to be instantaneous. However when I use the CalculateAll example, a single recalculation takes about 7 seconds. I need to speed this up by a factor of about 500-1000. Is there a trick I can use to do this?

Thanks,

Bill Korchinski

[Bill Korchinski]

Hi Geetha,

Will this recalculate all of the cells?

calcWB.Engine.RefreshRange(Syncfusion.Calculate.RangeInfo.Cells(1, 1, 33, 338))

Thanks again,

Bill

[Sridhar]

Hi Bill,

Thanks for the update.

Yes. The below mentioned code in your last update will recalculate all of the cells.

Code:
calcWB.Engine.RefreshRange(Syncfusion.Calculate.RangeInfo.Cells(1, 1, 33, 338))


It recalculates every cell in the range passed in.

Speed up the calcualtion:

It takes every cell to recalculate in the given range and it takes some execution time to finish. Could you please get back to us with the input excel file to check with recalculation formulas you are trying at your side to analyze further on this.

Please let me know if you require any further clarifications.

Thanks,
Sridhar.S

[Bill Korchinski]

Hi Sridhar,

Thanks for the help.One more question- when I run the code below, and examine dependent cells in calcWB before and after the RefreshRange method, I see no change from the original values in wbook. Is there some additional method that I need to run?

Thanks,

Bill

' Open existing Excel workbook.
Dim wbook As IWorkbook = application.Workbooks.Open("C:\ExcelTest.xls")

' Modify a cell in the wbook.
wBook.ActiveSheet.Rows(13).Columns(9).Value = 0.01408

' Create ExceRWCalcWorkbook object from an IWorkbook.
calcWB = XlsIOSamples.XlsIOCalcWorkbook.CreateFromIWorkbook(wBook)

calcWB.Engine.LockDependencies = False

calcWB.Engine.RefreshRange(Syncfusion.Calculate.RangeInfo.Cells(1, 1, 33, 338))

calcWB.Engine.LockDependencies = True

[Sridhar]

Hi Bill,

Thank you so much for the update.

From the above update we are unable to create the issue reproducing sample at our side. So,we not able to reproduce the reported issue and the actual cause of the issue at our side.

Could you please share with us the input template and the sample you have used at your side so proceed further on the above reported issue.

Please let me know if you require any further clarifications.

Thanks,
Sridhar.S