In short it is null because it is supposed to be for numeric and date types.
OpenXML documentation on msdn
The value of the DataType property is null for numeric and date types.
It contains the value CellValues.SharedString for strings, and
CellValues.Boolean for Boolean values.
There is a way though to distinguish between date and number cell formats using the NumberFormatId on the CellFormat. The trick is finding what id maps to what format. You can find out what format to use by creating new excel file and set a cell to the format in question (i.e. date):
![enter image description here]()
Then extract excel file using 7zip and look inside xl/styles.xml file:
![enter image description here]()
In the image above you can see this formatId 14 translates to short date. For a complete list of formats please refer to ECMA-376 documentation for Office Open XML formats (number format table should be buried somewhere inside part 4. They moved it to part 1 in section 18.8.30).
I created an enumeration for the most common formatIds:
private enum Formats
{
General = 0,
Number = 1,
Decimal = 2,
Currency = 164,
Accounting = 44,
DateShort = 14,
DateLong = 165,
Time = 166,
Percentage = 10,
Fraction = 12,
Scientific = 11,
Text = 49
}
You could then create a helper function that will get you the formatted value the way you would like:
private static string GetFormattedCellValue(WorkbookPart workbookPart, Cell cell)
{
if (cell == null)
{
return null;
}
string value = "";
if (cell.DataType == null) // number & dates
{
int styleIndex = (int)cell.StyleIndex.Value;
CellFormat cellFormat = (CellFormat)workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(styleIndex);
uint formatId = cellFormat.NumberFormatId.Value;
if (formatId == (uint)Formats.DateShort || formatId == (uint)Formats.DateLong)
{
double oaDate;
if (double.TryParse(cell.InnerText, out oaDate))
{
value = DateTime.FromOADate(oaDate).ToShortDateString();
}
}
else
{
value = cell.InnerText;
}
}
else // Shared string or boolean
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(int.Parse(cell.CellValue.InnerText));
value = ssi.Text.Text;
break;
case CellValues.Boolean:
value = cell.CellValue.InnerText == "0" ? "false" : "true";
break;
default:
value = cell.CellValue.InnerText;
break;
}
}
return value;
}
