Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
2350
Excel ApplyCellFormula method is slow
posted

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