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
  • 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

    Hi,

    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?

    Regards,
    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

    Hello,

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

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

    Hi,

    Thank you for your information. It really helps.

    Regards
Need extra support?

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

Learn More

Forum Channels