I believe so!! I've spent a whole day trying to get things to work without success... and I've now had to move code over to Excel COM Automation for the time being to keep the business happy :O(

So if I set the calculation mode to manual just prior to saving the workbook...

            workbook.Recalculate();

            workbook.CalculationMode = CalculationMode.Manual;

            workbook.Save(@"c:\temp\workbook - results.xlsx);

And then open up in Excel, the values in the workbook are incorrect - I've got #N/A and in loads of places.

If I then force Excel to recalculate the workbook, everything updates to what I expect.

Hi Michael,

The spreadsheet contains VLookup, DGet and formulae that use named ranges. They may be the cause of the problem.

Unfortunately much of the spreadsheet contains confidential information, and as such I am currently unable to post anything as is on a public forum.

Furthermore, I have had to completely re-engineer the code to use Excel Automation via COM as I have been under tight deadlines to get it operational.

However, I will do my best to recreate a sample for you over the next few days.

Cheers,

James

Are you going to be able to post a sample here?

Hi Michael,

Yes I will, but I've been snowed under due to a) having spent far too long trying to get the Infragistics stuff to work, and then b) far too long rewriting (successfully) using the Excel interop.

Coupled with the fact that the information within the spreadsheet his highly sensitive and needs obfuscating - meaning a sample may take a while.

Sorry, but I've got much higher priorities right now - but I will post something!

Cheers,

James

Hi Michael,

I've done a bit of investigation and can confirm that the problem definitely exists with named ranges. A single named cell works fine, so if A2 is named as Tax_Rate, Infragistics will calculate = A1 * Tax_Rate quite happily.

However, you can name a whole range rather than a cell e.g. A1..Z2  as Phonetic_Alphabet. But Infragistics doesn't like = VLookUp(H1, Phonetic_Alphabet, 2, False) - it simply returns #N/A

This is at the route of the problem. Fixing this should solve this part of my problem.

There are also other problems whereby it removes huge amounts of formatting - particularly foreground colours and conditional formatting for no apparent reason. And I also have huge issues with exporting large UltraGrids to Excel. But I guess I will raise those as separate concerns elsewhere.

James,

I have reproduced the issue that you are describing.  I shall submit this to our development team for further review and you will receive more information on this from the support case that has been opened for you.

I have a very simple workbook to reproduce the recalculation performance issue

In Cell B1, put "A"

In Cell D1, put "=OFFSET(A1,B1,C1)"

This generates a #value error since the second and third args to OFFSET should have been integers. This in turn generates not one, but TWO exceptions in Infragistics libraries upon Recalculate(true)

Now, to see how utterly TERRIBLE the performance is with just these few exceptions being handled, copy A1:D1 down to A100:D100 and call Recalculate(true)

On my test environment, this takes 17 seconds just to handle just 100 formulas!!! This just doesn't work.

Hi, I'm encountering MAJOR bugs surrounding the workbook calculations. I tried Recalculate(true) to work around the bug where the workbook is not automatically recalculating, however when there are errors in formulas the performance becomes unbearable and the application grinds to a halt. A report that in Excel/com takes 5 seconds, takes hours to run, and spits out a LONG stream of exceptions based in the Infragistics library to the debugger when Recalculate() is called

Exception thrown: 'System.FormatException' in Infragistics4.Documents.Excel.v16.1.dll ("Input string was not in a correct format.")

Please let me know if I can provide any more details in this. I really want this to work for us, but it's not looking likely with all these calculation issues.

Thanks.

Hi, we're evaluating the latest release, and it appears this is not fixed.

This is a huge problem for our reports we're evaluating this for.. We were expecting the calculation engine to be able to at least do simple tasks, but it never recalculates.

e.g. create a simple spreadsheet a1="test", b1 =a1

then set worksheetcell (a1) = "foo" 

wb.CalculationMode = CalculationMode.Automatic
wb.ResumeCalculations()
wb.Recalculate()

b1 still = "test" until I open the spreadsheet in a viewer and then edit cell b1 to force a recalc.

Are we doing something wrong, or Is there an ETA on the fix?

Thanks.

Excellent news!

Thanks for your patience and perseverance with this!