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


    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.


    Here is the spec of my development box.

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


    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.

  • Marked as Answer

    Replied 22 October 2020, 7:35 am EST


    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


    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!

  • Replied 29 April 2021, 5:37 pm EST


    Your issue has been resolved with the latest build of GcExcel v4.1.0 which can be downloaded from this link:-

    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.

  • Replied 9 May 2021, 7:22 pm EST

    Hi Reeva,

    We can confirm that this issue is solved using the latest build when "DeferUpdateDirtyState" property is set to true.

    We thought that we should set this property to false or recalculate manually, but we found that even if this property is true, we can still get the latest calculated value of a range with formula when the referenced range in the formula was changed. Is this by design and the value of a formula is guaranteed to be correct when fetched even with "DeferUpdateDirtyState = true"?
  • Replied 11 May 2021, 9:38 pm EST


    We are discussing this with the devs and will confirm the same soon.
    Is there anything that is creating the issue at your end with this behavior?

    Prabhat Sharma.
  • Replied 12 May 2021, 10:59 am EST

    No, if this is the expected behavior, we want to use this feature to prevent manual recalculation.
  • Replied 16 May 2021, 1:40 pm EST


    Yes, as per the devs the current behavior is expected because the calcengine calculates automatically.

    Prabhat Sharma.
  • Replied 17 May 2021, 10:56 am EST


    Thank you for your information. It really helps.

Need extra support?

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

Learn More

Forum Channels