C# resolving Excel table column names of which some are dates.

Question

I am trying to return the list of columns names from a DataSet in C# based on an Excel Query I have performed. I am ok doing this (well I think), see code below. However, three of my column headings are Dates and the column name is displayed as F4, F5, F6. I have looked further and found that the Type (.getType) report System.Double but I am unable to convert them.

Sample file is:

UID | FirstName | Surname | 31/07/2010 | 31/08/2010

100 | test | test | 8.8 | 9.9

200 | test2 | test2 | 7.7 | 6.6

My output is:

UID

FirstName

System.String

Surname

System.String

F4

System.Double

F5

System.Double

F6

System.Double

Could you shed any light on this?

conn = new OleDbConnection(conStr);
conn.Open();

dbCmd = new OleDbCommand("SELECT * FROM [" + worksheetName + "$]", conn);
dbCmd.CommandType = CommandType.Text;

dbAd = new OleDbDataAdapter(dbCmd);

dbAd.Fill(ds);

foreach (DataTable table in ds.Tables)
{
    foreach (DataColumn col in table.Columns)
        {
            Console.WriteLine(col.ColumnName);
                Console.WriteLine(col.ToString());
                Console.WriteLine(col.DataType);
                //object t = col;
                //Double ttd = (Double)t;
                //Console.WriteLine(t);
        }
}

I have searched high and low, but cannot find how to do this. I really don't want to read the file in use Excel Object Model as this is so slow.

Answer 1

I believe what is happening is that the Excel OLE data provider is guessing the type of these cells and it's guessing wrong. By default, the JET engine looks at the first 8 rows to determine datatypes for all columns. You can change this setting by modifying the registry before filling the dataset. Here's a response I had to a similar question a while back:

---

You must update this registry key before parsing the Excel spreadsheet:

// Excel 2007
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\

// Excel 2003
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\

Change TypeGuessRows to 0 and ImportMixedTypes to Text under this key. You'll also need to update your connection string to include IMEX=1 in the extended properties:

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");

---

References

http://blogs.vertigo.com/personal/aanttila/Blog/archive/2008/03/28/excel-and-csv-reference.aspx

http://msdn.microsoft.com/en-us/library/ms141683.aspx

Answer 2

Isn't this working for you?

double dblValue = 39456; // <--- your input double value

DateTime dt = DateTime.FromOADate(dblValue);

//dt <- Your DateTime value

Read here and here.

Excel stores the date/time as double format numbers. DateTime class already provides FromOADate function to parse (valid :)) double value to right date and time.