问题描述
我很难将行插入到现有的表对象中。这是我的代码段:
I have difficulties trying to insert rows into an existing table object. Here is my code snippet:
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\myExcelFile.xlsx" + ";Extended Properties=\"Excel 12.0;ReadOnly=False;HDR=Yes;\"";
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
string insertQuery = String.Format("Insert into [{0}$] (ID, Title,NTV_DB, Type ) values(7959, 8,'e','Type1')", TabDisplayName);
cmd.CommandText = insertQuery;
cmd.ExecuteNonQuery();
cmd = null;
conn.Close();
}
结果是将行插入到现成的表对象下面:
As a result I get my rows inserted below a ready-made table object:
我也尝试过在表对象中插入数据,如下所示:
I've also tried inserting data inside a table object like so:
string insertQuery = String.Format("Insert into [{0}$].[MyTable] (ID, Title,NTV_DB, Type ) values(7959, 8,'e','Type1')", TabDisplayName);
但是我得到一个错误:
名称 MyTable
确实存在。如果有人能阐明这个奥秘,我将不胜感激。
As you can see, table with a name MyTable
does exist. I would be very grateful if someone can shed some light on this mystery.
推荐答案
基于:
示例:名称 MyTable
是指 = $ A $ 1:$ E $ 3
。
因此,您可以为此使用 Range
( Sheet1 $ A:E
)。在这种情况下,您的Insert语句可能会变成这样:
Example: Name MyTable
refers to =$A$1:$E$3
.So you can use Range
for example (Sheet1$A:E
) for this purpose. In this case your Insert statement could change to something like this:
string insertQuery = "INSERT INTO [Sheet1$A:E] (ID, Title,NTV_DB, Type) VALUES (7959, 8,'e','Type1')";
其他限制:
使用的行:ADO将插入到最后使用的行下方。注意!最后使用的行可以为空。
Used Rows: ADO will insert below the last used row. NOTE! The last used row can be empty.
和 Tables
如前所述。
这篇关于如何在Excel工作表内的表格对象中插入行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!