Get default data type for Excel column using VSTO
You cannot test the data type of the entire column because Excel does not restrict the data types of all cells in the column to be the same. You will need to check the data type stored in the individual cells.
As long as the cell is not empty, you can check the data type stored in the cell by calling the method Object.GetType()
on the value stored in the cell:
// Using C# 4.0
Type type = worksheet.Cells[1,1].Value.GetType();
Using C # 3.0 would be a little more cumbersome:
// Using C# 3.0
Type type = (worksheet.Cells[1, 1] as Excel.Range).get_Value(Type.Missing).GetType();
If the cell might be empty, you will have to test it, because the Range.Value
returned cell is empty null
and you cannot call Object.GetType()
on null
. Therefore, you must explicitly check null
:
// Testing Cell Data Type via C# 4.0
object value = worksheet.Cells[1,1].Value;
string typeName;
if (value == null)
{
typeName = "null";
}
else
{
typeName = value.GetType().ToString();
}
MessageBox.Show("The value held by the cell is a '" + typeName + "'");
When using C # 3.0, the code is similar:
// Testing Cell Data Type via C# 3.0
object value = (worksheet.Cells[1, 1] as Excel.Range).get_Value(Type.Missing);
string typeName;
if (value == null)
{
typeName = "null";
}
else
{
typeName = value.GetType().ToString();
}
MessageBox.Show("The value held by the cell is a '" + typeName + "'");
Hope it helps ...
- Mike
a source to share
Create ADO.NET DataTable, SqlConnection and SqlDataAdapter.
Populate the DataTable with data from an Excel worksheet.
Then get the underlying datatype from the datatable using the DataTable-> Columns [the column number] -> DataType property.
The code below may help you understand better. However, this may not be complete.
DataTable dtSourceData = new DataTable(); //ADO.NET
string Con_Str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ <Excel file path> + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
OleDbConnection con = new OleDbConnection(Con_Str);//ADO.NET
con.Open();
String qry = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter odp = new OleDbDataAdapter(qry, con);//ADO.NET
odp.Fill(dtSourceData);
con.Close();
a source to share
To get the data type of the whole column, you can use the following code
// Using C# 4.0
for (int i = 0; i <= cells.LastColIndex; i++) //looping all columns of row
{
Type type = worksheet.Cells[0,i].Format.FormatType;
}
The above code reads the data type of all columns in the first row.
But remember that Excel does not restrict the data types of all cells in a column to be the same .
a source to share