Reading Forumula calculations from XLSX

Posted by: andrew.wilkowski on 6 July 2020, 3:04 am EST

    • Post Options:
    • Link

    Posted 6 July 2020, 3:04 am EST

    Hello. I’m trying to read a column of data from an XLSX file. If I enter text or numbers (ex. “2”), the value is there as expected in the “Value” property.

    However, if the file contains a formula like “=1+1” or “=B2/2” then I cannot read the formula or the calculated value (Cell.Value=null, Text=“”, Formula=“”)

    What else do I need to do to read formulas? I want to get “2” when the cell in the file contains “=1+1”.

    
    var s = new FpSpread();
     
    s.OpenExcel("test.xlsx", FarPoint.Excel.ExcelOpenFlags.DocumentCaching, null, "the password");
     
    value = s.Sheets["the sheet"].Cells[row, col].Value.ToString(); // Cell.Value has the expected value for numbers or text, but is null for formulas like "=1+1"
    
    
  • Posted 6 July 2020, 4:17 pm EST

    Hi Andrew,

    The formula can be get using the Cells[0,0].Formula. Please refer to the attached sample for reference.

    Regards,

    Manish Gupta

    SpreadFormula.zip

  • Posted 7 July 2020, 3:01 am EST

    Thanks, Manish! That helped me find my error. I’m now finding both the value and formula as expected.

Need extra support?

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

Learn More

Forum Channels