We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

calcualte the whole workbook like F9 in excel

Thread ID:

Created:

Updated:

Platform:

Replies:

81037 May 9,2009 09:28 PM UTC Nov 15,2011 10:56 AM UTC ASP.NET Web Forms (Classic) 6
loading
Tags: XlsIO
Adi Hirtzel
Asked On May 9, 2009 09:28 PM UTC

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 [Syncfusion]
Replied On May 11, 2009 09:08 AM UTC

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
Replied On November 11, 2011 12:12 AM UTC

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
Replied On November 11, 2011 01:34 AM UTC

Hi Geetha,

Will this recalculate all of the cells?

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

Thanks again,

Bill


Sridhar [Syncfusion]
Replied On November 11, 2011 08:39 AM UTC

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
Replied On November 11, 2011 05:42 PM UTC

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 [Syncfusion]
Replied On November 15, 2011 10:56 AM UTC

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


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;