Hi there,
I've got a reasonably complicated Excel spreadsheet that I want to automatically calculate a whole load of values for me.
I've got code similar to the following which does this:
var workbook = Workbook.Load(@"c:\temp\workbook.xlsx");
workbook.SuspendCalculations();
FillDataRow(workbook.Worksheets[0], dataReader);
workbook.CalculationMode = CalculationMode.Automatic;
workbook.ResumeCalculations();
workbook.Recalculate();
// code to extract calculated values
workbook.Save(@"c:\temp\workbook - results.xlsx);
Are you sure there are values not calculating correctly? It is possible MS Excel is updating some other field in the file, like the version of the program which saved the file, or the internal calculation order cache.
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.CalculationMode = CalculationMode.Manual;
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.
I am submitting this issue to our development team and you will receive more information through the support case that has been opened for you.
I have tested this myself and I am not seeing the behaviour you have described. Could you tell me in more detail what kind of calculations you are doing so that I can look into this further? Would it be possible for you to post here a simple sample application that reproduces this issue?
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?
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!
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?
Excellent news!
Thanks for your patience and perseverance with this!
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.