Recalculating the whole workbook

[Goran Valmont]

Hello,

I'm having problem with recalculating the whole workbook and am running out of options.

I have a workbook with 11 worksheets:
5 worksheets data only,
5 worksheets formulas (referencing data worksheets),
1 cover worksheet to sum up the errors from 5 formula worksheets.

I'm opening an empty template with all those worksheets,
and filling in the 5 worksheets with data.

After I fill in all the data, I need to recalculate all the worksheets and read 1 value from cover worsheet that counts the errors from other worksheets. And this value is always False. (no errors)

I have tried refreshing all worksheets after I inserted the data (didn't work) like this :

For Each calcWorksheet As IWorksheet In workBook.Worksheets
              calcWorksheet.EnableSheetCalculations()
              calcWorksheet.CalcEngine.RefreshRange(Syncfusion.Calculate.RangeInfo.Cells(1, 1, 500, 50))
Next


I tried to update values as I go (also didn't work):

workBook.CalculationOptions.CalculationMode = ExcelCalculationMode.Automatic
workBook.CalculationOptions.IsIterationEnabled = True
for each worksheet as IWorksheet in workbook.Worksheets
    worksheet.EnableSheetCalculations()
    for each item in data
        worksheet.Range(item.ExcelRow, item.ExcelColumn).Value = item.ExcelValue
        Dim cell As String = CommonFunctions.GetExcelColumnName(item.ExcelColumn).ToString() + item.ExcelRow.ToString()
        worksheet.CalcEngine.UpdateDependenciesAndCell(cell)
        worksheet.CalcEngine.Refresh(cell)
    Next
Next

I'm running out of ideas.
Is there some way or setting that I'm missing?

Kind regards,
Goran

[Prakash Kumar D]

Hi Goran,

 Thank you for using Syncfusion products.

In order to achieve your requirement you should make use of calculated value instead of refresh range property. Calculated value property returns the updated value. We have shared the code snippets for your reference.

Code Snippets:

workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Automatic

workbook.CalculationOptions.IsIterationEnabled = True

   For Each worksheet As IWorksheet In workbook.Worksheets

       worksheet.EnableSheetCalculations()

       For Each item In Data

           worksheet.Range(item.ExcelRow, item.ExcelColumn).Value = item.ExcelValue

           Dim cell As String = CommonFunctions.GetExcelColumnName(item.ExcelColumn).ToString()          + item.ExcelRow.ToString()

           Dim calculatedvalue As String = worksheet.Range(cell).CalculatedValue()

        Next

  Next

Please let us know if you need any clarification.

Regards,
Prakash Kumar

[Goran Valmont]

Hello,

Thank you for your response.
I've actually figured it out that CalculatedValue is the one that trigges it.

But I've come across of another problem, if you can help out.
In one of the sheets I have a formula =INT(C1),
and C1 value is a negative integer number like -12500.
Now, when I trigger CalculatedValue I get -12501,
but when I open the excel (2010) file, excel recalculates to -12500.

I don't really understand how can I get the diferent values for the same formulas.
I'm thinking that there must be something with number formatting in cell C1 ?

Regards,
Goran

[Prakash Kumar D]

Hi Goran,

We are able to reproduce this issue and we have logged an issue report on this. As per our forum regulation, we will not be able to share the fix for the issue through forum. However, if you are interested in receiving this fix then, we would request you to use our Direct-Trac Support system to get the fix for this issue and resolve all your technical queries. For more details, please login into http://www.syncfusion.com/Account/ and we will be happy to help you. Please let us know in case of any queries.

 

Regards,
Prakash Kumar

[Goran Valmont]

Hello,

Regarding my first issue with recalculating I'm finding the problems with performance speed.

It happened that I have 10 data only worksheets and 15 more of formulas.
I'm recalculating everything (after I fill in the data) and it takes some time (around 5 minutes) and it shoots up the processor to 100%.

The code I'm using is:

For Each worksheet As IWorksheet In workBook.Worksheets
    For Each range As IRange In worksheet.UsedRange.Cells
        If range.HasFormula Then
            Dim calculatedValue As String = range.CalculatedValue
        End If          
    Next
Next

I am aware that it might take some time as the UsedRange can be pretty big (some worksheets have 3000 rows and 8 columns).

Is there a better (faster) way of doing this?
Like close, save to disk and reopen (read somewhere that this might be the approch to take)?

I have .dll-s version 10.202.0.75. When I tried using the latest one (12.something) it was even slower (got from 4 minutes to 20).

Thank you in advance,
Kind regards,
Goran

[Vikas Sekar]

Hi Goran,

We would request you to use our Direct-Trac Support system to resolve all your technical queries and also please provide as the sample file for this scenario. For more details, please login into http://www.syncfusion.com/Account/ and we will be happy to help you. Please let us know in case of any queries.

Thanks,

Vikas