We are using the Infragistics.Documents.Excel.Workbook class to create an Excel workbook with 10,000+ rows. This works fine without formulas (takes about 1 minute), but seriously slows down when adding 5 formula columns (about 26 minutes). The formula columns don't contain anything complicated, so I don't think it has to do with the complexity of the formulas (we are just doing addition and multiplication mostly).
We are using the ApplyFormulas method to add the columns. E.g.:
worksheet.Rows[rowIndex].ApplyCellFormula(columnIndex, newFormula, CellReferenceMode.R1C1); (where newFormula equals "=RC[-1]*RC[-7]")
We've also tried using the SuspendCalculations() and ResumeCalculations() methods; however, these don't appear to help much. The same goes when programmatically setting the Workbook.CalculationMode to manual. This also does not help. It appears that writing the data gets slower and slower as more rows are added, so I'm pretty sure the formulas are being re-calculated. Is there something we are doing wrong when applying the formulas?
Thanks,
Paul
Hello Paul,
Thank you for contacting Infragistics support!
I’ve created a code sample in order to investigate your case. I’m creating a worksheet with 10000 rows. There are five formulas depending on each other. The workbook has been created for less than 1 minute. Please test it on your side and feel free to modify it in order to represent the issue.
Furthermore, if you don’t need the values of the cells with formulas, you could use SuspendCalculations without resuming them.
You are mentioning that you tried CalculationMode to be set to manual. Actually, this option is for the UI of Excel. It will not help you with the calculations of formulas.
Please let me know if you have any more questions regarding this topic.
Regards,
Aneta Gicheva,
Infragistics
Hi Aneta,
Thank you for the code sample. It seems that combining the ApplyCellFormula method with calling Rows.Insert is producing the slow results. I've attached an updated code sample with using the Rows.Insert method. Rows 1- 1000 takes 10 seconds, Rows 1001 - 2000 takes 32 seconds, Rows 2001 - 3000 takes 55s.
The results were pretty similar when using SuspendCalculations. Rows 1- 1000 takes 10 seconds, Rows 1001 - 2000 takes 32 seconds, Rows 2001 - 3000 takes 53s.
If I remove the ApplyCellFormula calls, but still use Rows.Insert, then 3000 rows takes about a second.
So I believe this is caused when combining calls to ApplyCellFormula and Rows.Insert. I do need to use Rows.Insert because we are using the Infragistics Excel classes to insert data into an existing Excel workbook.