C# resolving Excel table column names of which some are dates
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("SELE开发者_Go百科CT * 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.
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
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.
精彩评论