Reading Forumula calculations from XLSX

Reading Forumula calculations from XLSX

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

• 

  andrew.wilkowski

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  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"
  
  

• 

  manish.gupta

  

  • Post Options:
  • Link
    Copy

  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

• 

  andrew.wilkowski

  • Post Options:
  • Link
    Copy

  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.