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

Parents
No Data
Reply
  • 435
    posted

    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

    ExcelFormula.zip
Children