Introduction
Today I worked in the openxml for reading excel and I got all values as expected but i couldn't find the value of the Cell which contains the formula. I searched lot in Google and found the following solution finally .Here Sharing the same for others.
public string Getcurrentcellvalue(Cell thecurrentcell, WorkbookPart wbPart)
{
string result = string.Empty;
//start of if
if (thecurrentcell != null)
{
if (thecurrentcell.DataType != null)
{
switch (thecurrentcell.DataType.Value)
{
case CellValues.SharedString:
{
int id;
if (Int32.TryParse(thecurrentcell.InnerText, out id))
{
SharedStringItem item = GetSharedStringItemById(wbPart, id);
if (item.Text != null)
{
result = item.Text.Text.Replace("'", "''");
} else if (item.InnerText != null)
{
result = item.InnerText;
} else if (item.InnerXml != null)
{
result = item.InnerXml;
}
} //end of if (Int32.TryParse ( thecurrentcell.InnerText, out id )
break;
}
case CellValues.Boolean:
{
result = thecurrentcell.InnerText;
break;
}
case CellValues.Date:
{
result = thecurrentcell.InnerText;
break;
}
case CellValues.Error:
{
result = thecurrentcell.InnerText;
break;
}
case CellValues.InlineString:
{
result = thecurrentcell.InnerText;
break;
}
case CellValues.Number:
{
result = thecurrentcell.InnerText;
break;
}
case CellValues.String:
{
result = thecurrentcell.InnerText;
break;
}
default:
result = thecurrentcell.InnerText;
break;
} // end of switch(thecurrentcell.DataType.Value)
} //end of (thecurrentcell.DataType != null)
else if (thecurrentcell.DataType == null)
{
if (thecurrentcell.CellFormula != null)
{
result = thecurrentcell.CellValue.InnerText;
} else
{
result = thecurrentcell.InnerText;
}
}
} //end of if(thecurrentcell!=null)
return result;
} //end of public
Hope the above code will be useful ,kindly let me know your thougths.