Set cell value performance problem with IRange.HasFormula check

Posted by: yyoshimura on 28 September 2020, 4:34 pm EST

  • Posted 28 September 2020, 4:34 pm EST

    Hi

    We are facing a new performance issue with the combo of checking IRange.HasFormula and setting the cell value with IRange.Value. Please refer to the attachment for reproduction(see comment in GcExcel.cs file, line 95).
    If we comment either line in SetValue method, the code will run much faster. Please have a look at this issue. Thanks in advance.

    Regards

    Here is the spec of my development box.


    Reproduction sample program
    gcexcelperformance.zip
  • Replied 30 September 2020, 8:40 am EST

    Hello,

    Thank you for sharing the application. We can observe the degradation in performance using both the properties.
    Hence we are discussing it with the developers [ID:3164] and will get back to you with an update soon.

    Regards,
  • Marked as Answer

    Replied 22 October 2020, 7:35 am EST

    Hello,

    The team has confirmed that this is an issue related to the GcExcel calculation engine. They are working over it and trying to fix it in v4.0 release, planned for mid December.
    Meanwhile, you can greatly improve the performance by implementing a workaround wherein, you can disable the calculation engine before getting and setting the values frequently [workbook.EnableCalculation = true/false]. With this, the performance at our end improves greatly (from 8 sec to 0.5 sec).
    The workaround will however have a disadvantage that the value you will get might be old/out of date value as the calculation engine is disabled. So, you set cell value, all its dependents will not be re-calculated.

    We regret for the inconvenience caused due to this performance issue.

    Kind Regards,
  • Replied 22 October 2020, 11:13 am EST

    Hi

    Thank you very much for your kind support.
    We confirmed that the performance is greatly improved by using the workaround you provided.
    Looking forward to V4 release and appreciate your great product!

    Regards
  • Replied 29 April 2021, 5:37 pm EST

    Hello,

    Your issue has been resolved with the latest build of GcExcel v4.1.0 which can be downloaded from this link:-
    https://cdn.grapecity.com/GcDocs/GcExcelNet/4.1/gcexcel-net-4.1.0.zip

    Please check your issue against the same build wherein we have public a property “DeferUpdateDirtyState” which fixes this issue.

    Let us know if it works for you.

    Thanks,
    Reeva
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels