Get cell value not working as expected with workbook calculation mode

Posted by: yyoshimura on 4 November 2021, 4:00 pm EST

  • Posted 4 November 2021, 4:00 pm EST

    Hi,

    We just encountered a weird behavior regarding getting value from cell using formula TEXT. Please refer attachment for reproduction.

    If we get the cell value of A1 from test file [CPSETC.xlsx] using the following code, we can get the correct value FY16.APR. But if we new workbook with EnableCalcuation = false, the value will be FY20160405!/00!/01.APR which is incorrect even if we set EnableCalculation to true before getting the cell value.

    We expect the behavior should be the same no matter EnableCalculation is true or false when creating new Workbook instance.
    Please help to investigate this issue. Thank you.

    Regards


    var options = new XlsxOpenOptions {DoNotRecalculateAfterOpened = true};
    var workbook1 = new Workbook {EnableCalculation = true};
    // If we create new Workbook instance with EnableCalculation = false,
    // result will be incorrect event we manually set EnableCalculation to true before getting value from range.
    //var workbook1 = new Workbook {EnableCalculation = false};
    workbook1.Open(@"..\..\..\CPSETC.xlsx", options);

    workbook1.EnableCalculation = true;
    var range = workbook1.Worksheets["SETC"].Range["A1"];
    Console.WriteLine("Value:" + range.Value);
    gcexcelperformance.zip
  • Replied 7 November 2021, 9:58 pm EST

    Hello,

    We too can observe the issue at our end and escalate this to the developers for investigation.
    [Internal Tracking ID: DOCXLS-5103]

    Regards,
    Prabhat Sharma.
  • Marked as Answer

    Replied 8 November 2021, 5:19 pm EST

    Hello,

    As per the developers, the input format "0000!/00!/00" is incorrect.
    Please change the the formula of A1 to
    ="FY"& MID(TEXT($C2,"0"),3,2)&".APR"

    And let us know if it resolves the issue at your end.

    Regards,
    Prabhat Sharma.

  • Replied 9 November 2021, 2:06 pm EST

    Thank you for your swift response.
    We can confirm that the issue was resolved by changing the formula.
    Cheers.
Need extra support?

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

Learn More

Forum Channels