使用OleDb更新Excel 2007 [英] Update Excel 2007 with OleDb

问题描述

尝试对Excel 2007文件执行更新命令会出现错误: 操作必须使用可更新的查询. 我正在将System.Data.OleDb与这样的连接字符串一起使用:

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"""

我尝试设置ReadOnly = false，但这会导致找不到可安装的ISAM.我还尝试设置了Mode = ReadWrite和IMEX = 1，这似乎没有任何效果.我的更新命令是这样的:

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'"

其中sheetName是通过查询excel模式获得的.有可能做我想做的事吗?我在哪里弄错了?

where sheetName was obtained from querying the excel schema. Is it possible to do what I am trying to? Where have I gone wrong?

推荐答案

通常在工作表名称后需要一个$符号.试试:

You usually need a $ sign after the worksheet name. Try:

cmd.CommandText = "UPDATE [" + sheetName + "$] SET [Quantity Error] = 'test' WHERE [Full Name] = 'Mr. Brown White'"

没有$符号的名称将被解释为命名范围，而不是工作表名称

Names without $ signs are interpreted as named ranges and not as worksheet names

这篇关于使用OleDb更新Excel 2007的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！