Get Data Type of an Excel Cell in C#

Question

I am reading in values from an XLSX Worksheet and putting them into a Data table.

My problem is that I cannot seem to infer the Datatype of the cell so that I can set the Data table's Column type.

Answer 1

Excel has a much looser approach to typing data than C#. For example, numeric values are always doubles. If you use the Value property of the range object (get_Value() in interop, if I recall correctly), Excel will possibly return a (boxed) decimal or DateTime if the cell's contents and formatting are consistent with those data types.

If you use the Value2 property, money and date values will be returned as boxed doubles. (The numbers are all stored internally as doubles, so, because Excel doesn't need to test for whether to convert them, Value2 is slightly faster.)

So, you could check the type of each value in a column, and choose your data column's type accordingly. If you want to test whether a column could have a more specific numeric type (float, int, short, byte, etc.), then you'll also need to check the range of values in that column, and test whether they are integers.

Answer 2

You could also use the NumberFormat property of Excel.Range, and use this to see check the format. Allthough if you only need to find out for excample what is string and what is not - the solution with tryParse is more effective (and prettier I think!).

Here is a plan B:

 string format = range.NumberFormat.ToString();
 if(format == "#,##0.00")
 {
     //Type is Number
 }

I think this solution might be better if you know for sure what type the values can vary from though. From this one you could check if the format contains percent and so on also.

You can also see if the entire column you are getting from Excel has the same format in its cells,

 //If this is "" the column has different types in it
 string checkFormat = range.EntireColumn.NumberFormat.ToString();