C# 从 Excel 工作表导入时日期时间格式不匹配

DateTime format mismatch on importing from Excel Sheet

提问by devnull

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

我正在DataTable使用以下代码将数据从 Excel 工作表导入到 a 中：

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();

I have a Datecolumn 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?

我Date在 Excel 工作表中有一个格式为dd/MM/yyyy. 当日期为dd/MM/yyyy（例如27/12/2009）时，上述代码失败。如何指定日期格式？

EDIT (adding more details):

编辑（添加更多细节）：

It is not throwing any exception. Data is imported to the DataSetuntil the row where an invalid Dateformat is encountered. I have the date as dd/MM/yyyyin 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/2009it stops the process of importing, though no error/exception is thrown. So I'm having only partial results in DataTable.

它没有抛出任何异常。数据被导入到遇到DataSet无效Date格式的行。我有dd/MM/yyyyExcel 表中的日期。当我使用 导入时OleDbDataAdapter，它期望 Excel 工作表中的日期为MM/dd/yyyy. 不会在遇到日期时自然而然地27/2/2009停止导入过程，尽管不会引发错误/异常。所以我在DataTable.

Please help.

请帮忙。

采纳答案by Brett Veenstra

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

过去拉入日期或其他混合数据列项目让我对 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 nothelp when you have BLANK dates and/or different values in the column itself - it confuses the Excel driver.

似乎他们的提供者“窥视”了 XX 行（取决于提供者版本）以确定运行时的列类型。适用属性您连接现有的答案帮助我过去，但做的不是当你有空白日期和/或在列本身不同的值帮助-它混淆Excel驱动程序。

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

我建议您改用FileHelpers或其他第三方库。Infragistics Excel 组件对我很好，而 FileHelpers 是开源的。

回答by John Weldon

How many rows do you have, and are all the cells in the date column valid dates? Sometimes the OleDB routines will incorrectly identify a column as text if there is some inconsistency in the cells in the first 8 rows. (8 is the default number of rows that is read to determine data types)

您有多少行，日期列中的所有单元格都是有效日期吗？有时，如果前 8 行的单元格中存在不一致，OleDB 例程会将列错误地标识为文本。（8 是读取以确定数据类型的默认行数）

回答by Stephen Binns

You could use the British Culture info to parse the date in British Format dd/MM/YYYY

您可以使用英国文化信息来解析英国格式 dd/MM/YYYY 的日期

CultureInfo culture = new CultureInfo("en-GB");
DateTime date = DateTime.Parse("27/12/2009", culture);

where "27/12/2009" is your date field

其中“27/12/2009”是您的日期字段

回答by iceangel89

maybe try formatting the date column in excel then import?

也许尝试在excel中格式化日期列然后导入？

回答by Matthew Pelser

You may want to check that the current culture you are running supports UK style dates.Here is a link on how to check and change this at the thread level http://support.microsoft.com/kb/306162

您可能想要检查您正在运行的当前文化是否支持英国风格的日期。这是有关如何在线程级别进行检查和更改的链接http://support.microsoft.com/kb/306162

The OleDb connection string seems to support ";Locale Identifier=". I think the eb-GB identifier is 2057 (Don't quote me ;)), you could also give that a try.

OleDb 连接字符串似乎支持“;Locale Identifier=”。我认为 eb-GB 标识符是 2057（不要引用我 ;)），您也可以尝试一下。

As for the exceptions not being thrown i think during the fill you may want to look into the RowUpdated events as per this link http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.rowupdated(VS.80).aspx.

至于未抛出的异常，我认为在填充过程中，您可能希望按照此链接查看 RowUpdated 事件http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.rowupdated (VS.80).aspx。

This is how the Fill method of the DataSet works it will quit on the row where there is a problem, using the above events you may have some options on ignoring the row or reformatting.

这就是 DataSet 的 Fill 方法的工作方式，它会在出现问题的行上退出，使用上述事件，您可能有一些忽略该行或重新格式化的选项。

回答by AareP

One thing you should always specify in your connection string is IMEX=1:

您应该始终在连接字符串中指定的一件事是 IMEX=1：

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;IMEX=1\"

It helps with parsing columns that contain both numbers and strings. Might help with date parsing as well, but then you would have to manually convert all dates with:

它有助于解析包含数字和字符串的列。也可能有助于日期解析，但是您必须手动转换所有日期：

System.IFormatProvider format = new System.Globalization.CultureInfo("en-US", true);
DateTime d = DateTime.Parse(dataSet.Tables[0].Rows[i]["MyDate"] as string,format);

回答by Maslow

How about filling the dataset programmatically instead of via a data adapter?

如何以编程方式而不是通过数据适配器填充数据集？

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0");

Dim _myDataSet As New DataSet
                Dim con As New OleDb.OleDbConnection
                con.Open()
                Dim cmd As New OleDb.OleDbCommand(" SELECT * FROM [" + "Sheet1" + "$]", con)
                Using dr = cmd.ExecuteReader()
                    While dr.Read
                        Dim row = _myDataSet.Tables(0).NewRow()
                        With dr.Item("excel date column").ToString
                            Dim dt As New Date(CInt(.Substring(6)), CInt(.Substring(3, 2)), CInt(.Substring(0, 2)))
                            row.Item("dataset datecolumn") = dt
                        End With

                        \*'populate other columns here' *\
                       _myDataSet.Tables(0).Rows.Add(row)
                    End While


                End Using

回答by Maslow

You can use this function to format whatever date format you received to the format you need.

您可以使用此功能将收到的任何日期格式格式化为您需要的格式。

Here is the code:

这是代码：

Public Shared Function ConvertToDate(ByVal dateString As String, ByRef result As DateTime) As Boolean
Try

'Here is the date format you desire to use
Dim supportedFormats() As String = New String() {”dd/MM/yyyy”}

'Now it will be converted to what the machine supports
result = DateTime.ParseExact(dateString, supportedFormats,System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.None)

Return True
Catch ex As Exception
Return False
End Try
End Function

回答by Brett Veenstra

When linking Excel spreadsheets in MS Access, a problem arises when a column has mixed data types. For example, if the first row in the column is "Text" and the remaining are Numeric, the Numeric fields will be formatted as Text and #Error out. Likewise with Dates and non-Dates mixed in the same column.

在 MS Access 中链接 Excel 电子表格时，当列具有混合数据类型时会出现问题。例如，如果列中的第一行是“文本”，其余是数字，则数字字段将被格式化为文本和#Error 输出。同样，日期和非日期混合在同一列中。

There are some nice attempts at answers up there, but here is the simplesolution to read these Mixed data types without a data type mismatch error:

那里有一些很好的答案尝试，但这里是读取这些混合数据类型而没有数据类型不匹配错误的简单解决方案：

Try adding "IMEX=1" to your MS Access SQL, such as:

尝试将“ IMEX=1”添加到您的 MS Access SQL，例如：

SELECT Type, Width, Weight

选择类型、宽度、重量

FROM [Excel 8.0;IMEX=1;DATABASE=C:\TEMP\MySpreadsheet.xls].MyExcelTable

来自 [Excel 8.0; IMEX=1;DATABASE=C:\TEMP\MySpreadsheet.xls].MyExcelTable

Thanks, Brian Jasmer

谢谢，布赖恩·贾斯默

回答by user2588099

I also faced the same Issue. The Date column in the Excel sheet is dd/MM/yyyy. But as per my system settings the data format is MM/dd/yyyy. Therefore if the date is 31/07/2013 for example , the Date field displayed as empty string.

我也面临同样的问题。Excel 工作表中的日期列是 dd/MM/yyyy。但根据我的系统设置，数据格式为 MM/dd/yyyy。因此，例如，如果日期是 31/07/2013，则日期字段显示为空字符串。

Using IMEX1 along with "Microsoft.ACE.OLEDB.12.0" Driver Solved the Problem.

使用 IMEX1 和“Microsoft.ACE.OLEDB.12.0”驱动程序解决了问题。

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"

But "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + "; Extended Properties=Excel 8.0;IMEX=1" doesn't work.

但是“Provider=Microsoft.Jet.OLEDB.4.0;” + "Data Source=" + FilePath + "; Extended Properties=Excel 8.0; IMEX=1" 不起作用。