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 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.