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,
Just wanted to follow up – have you been able to update your project to use the more bulk approach to inserting rows?
Generally speaking, some penalty is to be expected for doing separate inserts as each requires some validation tasks to be performed (like making sure tables are not split, end of worksheet) as well as check and potentially adjust formula references – not even the actual calculation yet mind you, the latter also applies across all worksheets in case they have formulas that reference the affected area. The last part is what you saw even doing inserts in a separate worksheet, which should not be affected as much.We’ve discussed this internally and there are potential improvements we’ve set to investigate further, but in any case we can all agree it’s always advisable to insert large blocks of rows at once where possible.
Regards,Damyan PetevSoftware DeveloperInfragistics, Inc.
Hi Damyan,
I haven't had a chance to implement this yet; however, there is no reason that it shouldn't work. Thanks for discussing this internally. I've verified your answer.