从Excel工作表导入DateTime格式不匹配 [英] DateTime format mismatch on importing from Excel Sheet

问题描述

我是从一个Excel工作表上的数据导入到一个数据表使用下面的代码：

I'm importing data from an Excel sheet on to a DataTable using the following code:

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0");
con.Open();
_myDataSet = new DataSet();
OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + "Sheet1" + "$]", con);
myCommand.Fill(_myDataSet);
con.Close();

我有一个日期在Excel中列表格式 DD / MM / YYYY 。当日期是上面的代码失败 DD / MM / YYYY （如 27/12/2009 ）。 ？如何指​​定日期格式

I have a Date column in the Excel sheet in the format dd/MM/yyyy. The above code is failing when the date is dd/MM/yyyy (eg. 27/12/2009). How to specify the date format?

编辑（添加详细信息）：

EDIT (adding more details):

这是不扔任何异常。数据导入到的DataSet ，直到在那里遇到一个无效的日期格式的行。我有日期 DD在Excel工作表/ MM / YYYY 。当我导入使用 OleDbDataAdapter的，它期待在Excel工作表的日期是在 MM / DD / YYYY 。没有当它遇到一个日期，如 27/2/2009自然停止进口的过程中，虽然没有错误/异常。所以，我在在数据表只有部分结果。

It is not throwing any exception. Data is imported to the DataSet until the row where an invalid Date format is encountered. I have the date as dd/MM/yyyy in Excel sheet. When I import using OleDbDataAdapter, it is expecting the date in the Excel sheet to be in MM/dd/yyyy. No naturally when it encounters a date such as 27/2/2009 it stops the process of importing, though no error/exception is thrown. So I'm having only partial results in DataTable.

请帮忙。

推荐答案

在过去的日期或其它混合数据列项拉已经咬了我用Excel。

Pulling in Date or other mixed-data column items in the past has bit me with Excel.

看来，他们的供应商偷窥超前XX行（取决于提供商的版本）来确定列类型是什么样的在运行时的。适用属性的连接现有的答案帮助我过去，但是做的不可以帮助时，你必须在列本身BLANK日期和/或不同的价值观 - 它混淆Excel驱动程序

It seems that their provider "peeks" ahead XX rows (depending on Provider version) to determine what the column type is at runtime. The prior answers that apply properties to your connection have helped me in the past, but do not help when you have BLANK dates and/or different values in the column itself - it confuses the Excel driver.

我建议什么是您使用 FileHelpers 或另外三分之一三方库，而不是。 Infragistics的Excel的组件已对我非常好，而FileHelpers是开源的。

What I recommend is that you use FileHelpers or another third-party library instead. Infragistics Excel component has treated me very well, while FileHelpers is opensource.

这篇关于从Excel工作表导入DateTime格式不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！