使用OleDb更新Excel表 [英] Updating Excel Sheet Using OleDb

问题描述

我正在尝试更新Excel表格的单个单元格。请注意，在我下面的示例代码中，我使用列和行的变量，因为这将根据我的程序的其他部分而改变，但为了调试目的，我将它们分别设置为A和1。

I am trying to update a single cell of an Excel sheet. Note, in my example code below I am using variables for the column and row because this will change according to other parts of my program, but for debugging purposes I have set them to A and 1 respectively.

现在我在查询表达式'06：31 PM'中收到错误语法错误（缺少运算符）。

Right now I am getting an error saying "Syntax error (missing operator) in query expression '06:31 PM'.

请不要'我只是建议我参数化，因为我知道这不会解决我的问题，我不担心注射。

Please don't simply suggest I parameterize because I know this will not fix my problem and I am not worried about injection.

提前感谢每个人。

        string newColumn = "A";
        string newRow = "1";
        string worksheet2 = strMonth;

        var cnnStr2 = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", textBox1.Text);
        var cnn2 = new OleDbConnection(cnnStr2);

        cnn2.Open();

        string sql2 = String.Format("UPDATE [{0}$] SET {1}{2}={3}", worksheet2, newColumn, newRow, dtpTime.Text.Substring(0, 8));
        OleDbCommand objCmdSelect = new OleDbCommand(sql2, cnn2);
        objCmdSelect.ExecuteNonQuery();

        cnn2.Close();

编辑：根据Gerhard建议的链接，我将命令文本更改为以下内容： p>

Based on the link Gerhard suggested, I have changed my command text to the following:

string sql2 = String.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F1='{3}'", worksheet2, newColumn, newRow, dtpTime.Text.Substring(0, 8));

现在会产生错误操作必须使用可更新的查询。不知道该怎么做{1} {2}：{1} {2}给出了单页{0} $单个单元格范围，如果没有在我的连接字符串中指定的列名称，则为F1 =没有。最后，{3}是我正在使用单引号更新单元格的子字符串，只是因为这是每个人都似乎做的。如果我删除单引号，它会产生一个不同的错误。

This now produces the error "Operation must use an updateable query." Not sure what to do now. The {1}{2}:{1}{2} gives the single cell range on sheet {0}$ and F1 is the default column name given if there are no column names which I have specified in my connection string with HDR=NO. Lastly the {3} is the substring which I am updating the cell with and is in single quotes simply because that's what everyone seems to do. If I remove the single quotes it just produces a different error.

推荐答案

我编辑的命令字符串是正确的。但是为了摆脱新的错误，我不得不在连接字符串中取出IMEX = 1。不是100％确定为什么这会使它工作，但它是。

My edited command string is correct. However to get rid of the new error, I had to take out "IMEX = 1" in the connection string. Not 100% sure why this makes it work, but it does.

在查看有关使用oledb命令的事情时，我注意到的一件事是，像我一样，似乎与命令的每个部分真正在做什么混淆。我仍然不完全理解你可以添加的一些特殊的东西，如WHERE，但我想补充一点，以帮助任何人谁发现这个问题，并有类似的问题。

One thing I've noticed while looking up things about using oledb commands is that everyone, like me, seems to be confused with what each part of the command is really doing. I still don't fully understand some of the special things you can add to it like "WHERE", but I'd like to add a bit here to help anyone who finds this question and is having similar problems.

在SELECT语句中，如果要选择单个单元格，可以使用以下内容：

In a SELECT statement, if you wanted to select a single cell, you could use something like this:

string commandString = String.Format("SELECT * FROM [{0}${1}{2}:{1}{2}]", worksheetName, column, row);

SELECT * FROM将从worksheetName中选择所有内容。然后，您必须使用$来跟踪工作表的名称。接下来，{1} {2}：{1} {2}是要选择的单元格范围。这里，通过使冒号两边的列和行相同，您可以选择单个单元格的范围。例如，E3：E3将仅选择在单元格E3中的内容。

"SELECT * FROM" will select everything from "worksheetName". You then have to follow the name of your worksheet with "$". Next, "{1}{2}:{1}{2}" is the range of cells which you are selecting. Here, by making the column and row on both sides of the colon the same, you are selecting a range of a single cell. For example, "E3:E3" would select only what is in cell E3.

在UPDATE语句中，如果要更新单个单元格，可以使用以下内容：

In an UPDATE statement, if you wanted to update a single cell, you could use something like:

string commandString = String.Format("UPDATE [{0}${1}{2}:{1}{2}] SET F1='{3}'",worksheetName, column, row, data);

此处注意的唯一区别是SET F1 ={3}。这将将您在范围中指定的单元格设置为数据变量中的内容。注意使用F1。这只是在连接字符串中指定HDR = NO时使用的默认值，换句话说，没有列标题或名称。如果您有列标题，您将使用它代替F1。在这里了解F1是非常重要的，F1不以任何方式对应于F1单元格。

The only difference to note here is the "SET F1='{3}'". This will set the cell you specified in your range to what's in the data variable. Note the use of "F1". This is simply a default that is used when "HDR=NO" is specified in your connection string, in other words, there are no column headers or names. If you had column headers you would use that in place of F1. It is important to understand here that F1 does NOT correspond to cell F1 in any way.

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