Excel Jet OLE DB：插入日期时间值
OLEDB 可用于读取和写入 Excel 工作表。考虑以下代码示例：
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
cmd.Parameters.AddWithValue("@mydate", DateTime.Now.Date);
cmd.ExecuteNonQuery();
}
这工作得很好。插入数字、文本等也效果很好。但是，插入带有时间组件的值会失败：
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
cmd.Parameters.AddWithValue("@mydate", DateTime.Now); // <-- note the difference here
cmd.ExecuteNonQuery();
}
执行此 INSERT 失败并出现 OleDbException：条件表达式中的数据类型不匹配。
这是一个已知错误吗？如果是，可以采取什么措施来解决这个问题？我找到了一种有效的解决方法：
cmd = new OleDbCommand(String.Format(@"INSERT INTO Sheet1 VALUES (#{0:dd\/MM\/yyyy HH:mm:ss}#)", DateTime.Now), conn);
它基本上创建了一个如下所示的 SQL 语句：INSERT INTO Sheet1 VALUES (#05/29/2011 13:12:01#)。当然，我不必告诉你这有多丑陋。我更愿意有一个带有参数化查询的解决方案。
OLEDB can be used to read and write Excel sheets. Consider the following code example:
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
cmd.Parameters.AddWithValue("@mydate", DateTime.Now.Date);
cmd.ExecuteNonQuery();
}
This works perfectly fine. Inserting numbers, text, etc. also works well. However, inserting a value with a time component fails:
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\my\\excel\\file.xls;Extended Properties='Excel 8.0;HDR=Yes'")) {
conn.Open();
OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] datetime)", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("INSERT INTO Sheet1 VALUES (@mydate)", conn);
cmd.Parameters.AddWithValue("@mydate", DateTime.Now); // <-- note the difference here
cmd.ExecuteNonQuery();
}
Executing this INSERT fails with an OleDbException: Data type mismatch in criteria expression.
Is this a known bug? If yes, what can be done to workaround it? I've found one workaround that works:
cmd = new OleDbCommand(String.Format(@"INSERT INTO Sheet1 VALUES (#{0:dd\/MM\/yyyy HH:mm:ss}#)", DateTime.Now), conn);
It basically creates an SQL statement that looks like this: INSERT INTO Sheet1 VALUES (#05/29/2011 13:12:01#). Of course, I don't have to tell you how ugly this is. I'd much rather have a solution with a parameterized query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这似乎是一个已知错误 https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression
您可能想要截断毫秒，如下所示，它似乎适用于 OleDbParameter：
并将其而不是 DateTime.Now 添加到您的参数值中。
It appears to be a known bug https://connect.microsoft.com/VisualStudio/feedback/details/94377/oledbparameter-with-dbtype-datetime-throws-data-type-mismatch-in-criteria-expression
You might want to truncate the milisecond like this it appear to work for OleDbParameter:
And add this instead of the DateTime.Now into your parameter value.
问题是包含日期时间值的单元格无法直接放入 Excel 列中。您必须插入日期部分或时间部分。失败的原因是Excel单元格的默认属性是“值”而不是Excel中的“日期时间”。
The problem is the cell containing datetime value cannot be directly put into excel' column. You have to either insert the date component or the time component. The reason for failure is the default property of excel' cell is "values" instead of "datetime" in excel.