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