使用 OleDb 更新 Excel 2007
尝试对 Excel 2007 文件执行更新命令会出现错误： 操作必须使用可更新的查询。 我使用 System.Data.OleDb 和这样的连接字符串：
Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=""" & pathToFile & """;" & _
"Extended Properties=""Excel 12.0;HDR=YES"""
我尝试设置 ReadOnly=false 但这给出 无法找到可安装的 ISAM。 我还尝试设置 Mode=ReadWrite 和 IMEX= 1 好像没有什么效果。 我的更新命令是这样的：
Dim cmd As OleDbCommand = con.CreateCommand()
cmd.CommandText = "UPDATE [" + sheetName + "] SET [Quantity Error] = 'test' WHERE [Full Name] = 'Mr. Brown White'"
其中sheetName是通过查询excel模式获得的。 有可能做我想做的事吗？ 我哪里出错了？
Attempting to execute an update command against an Excel 2007 file gives the error:
Operation must use an updateable query.
I'm using System.Data.OleDb with a connection string like this:
Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=""" & pathToFile & """;" & _
"Extended Properties=""Excel 12.0;HDR=YES"""
I have tried setting ReadOnly=false but that gives Could not find installable ISAM. I have also tried setting Mode=ReadWrite and IMEX=1 which didn't seem to have any effect. My update command is like this:
Dim cmd As OleDbCommand = con.CreateCommand()
cmd.CommandText = "UPDATE [" + sheetName + "] SET [Quantity Error] = 'test' WHERE [Full Name] = 'Mr. Brown White'"
where sheetName was obtained from querying the excel schema. Is it possible to do what I am trying to? Where have I gone wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
工作表名称后通常需要一个 $ 符号。 尝试：
没有 $ 符号的名称被解释为命名范围而不是工作表名称
You usually need a $ sign after the worksheet name. Try:
Names without $ signs are interpreted as named ranges and not as worksheet names
我相信这通常是权限问题。 如果在 Excel 中打开该文件，可以读取/写入该文件吗？
I believe this is usually a permissions issue. Can you read/write to the file if you open it in Excel?
使用此连接字符串，这将起作用
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reportFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\";此外检查 Excel 文件的权限，验证连接字符串的扩展属性是否包含
IMEX=1表达式。 如果是，请将其删除。Use this connection string this will work
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reportFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\";Besides checking the permissions to your Excel file verify if the Extended Properties of the connection string contains the
IMEX=1expression. If yes, remove it.