blocks|key|2930550|text|我不确定Access+C#和Excel的功能是否相同，但它在电子表格上的功能对我来说是有效的。也许它能帮到你？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2930551|Table3.ListRows[1].Range.Insert(Excel.XlInsertShiftDirection.xlShiftDown);|code-block|syntax|javascript|2930552|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'm not sure Access C# works the same as Excel, but this worked on a spreadsheet for me. Maybe it could help you?

<pre><code>Table3.ListRows[1].Range.Insert(Excel.XlInsertShiftDirection.xlShiftDown);
</code></pre>

blocks|key|2718044|text|如果执行以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2718045|var+contents+=+new+DataTable();
using+(OleDbDataAdapter+adapter+=+new+OleDbDataAdapter(string.Format("Select+*+From+[{0}$]",+TabDisplayName),+conn))
{
++++adapter.Fill(contents);
}
Console.WriteLine(contents.Rows.Count);//7938|code-block|syntax|javascript|2718046|您将看到7938+(屏幕截图上的最后一行编号)。当您插入新行时，它插入到7939位置。(7929,7930，...)中的内容为空行被忽略，因为excel知道最后一个数字是7938。|2718047|解决方案：|2718048|2718049|您必须删除excel文件中7928之后的所有行。|ordered-list-item|2718050|您必须在特定位置插入。|2718051|2718052|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|W|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|Y|8|@]|9|@]|A|$]]|$1|O|3|P|5|N|7|Z|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|S|$]]

If you execute this code:

<pre><code>var contents = new DataTable();
using (OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("Select * From [{0}$]", TabDisplayName), conn))
{
 adapter.Fill(contents);
}
Console.WriteLine(contents.Rows.Count);//7938
</code></pre>

you will see 7938 (last row number on your screenshot). And when you insert new row, it inserted at 7939 position. Empty content in (7929, 7930, ...) rows are ignored, because excel knows that last number is 7938.

Solutions:

<ol>
<li>You must delete all rows after 7928 in excel file.</li>
<li>You must insert on specific position.</li>
</ol>

blocks|key|2930667|text|如果您使用的是Microsoft.ACE.OLEDB提供程序，请注意它不支持命名范围。您需要提供工作表[Sheet1$]的名称或工作表的名称，后跟范围[Sheet1$A1:P7928]。如果未提供范围，则会将表定义为已使用的范围，其中可能包含空行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2930668|处理空行的一种方法是删除它们，但是驱动程序不支持DELETE操作。|2930669|另一种方法是首先计算具有非空Id的行数，然后使用结果为INSERT语句定义表的范围：|2930670|using+(OleDbConnection+conn+=+new+OleDbConnection(connectionString))+{
++++conn.Open();

++++string+SheetName+=+"Sheet1";
++++string+TableRange+=+"A1:P{0}";

++++//+count+the+number+of+non+empty+rows
++++using+(var+cmd1+=+new+OleDbCommand(null,+conn))+{
++++++++cmd1.CommandText+=+String.Format(
++++++++++"SELECT+COUNT(*)+FROM+[{0}$]+WHERE+ID+IS+NOT+NULL;"
++++++++++,+SheetName);

++++++++TableRange+=+string.Format(TableRange,+(int)cmd1.ExecuteScalar()+%2B+1);
++++}

++++//+insert+a+new+record
++++using+(var+cmd2+=+new+OleDbCommand(null,+conn))+{
++++++++cmd2.CommandText+=+String.Format(
++++++++++++"INSERT+INTO+[{0}${1}]+(ID,+Title,+NTV_DB,+Type)+VALUES(7959,+8,'e','Type1');"
++++++++++++,+SheetName,+TableRange);

++++++++cmd2.ExecuteNonQuery();
++++}
}|code-block|syntax|javascript|2930671|entityMap^0|7|J|1F|9|23|H|0|O|6|0|E|2|R|6|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|15|8|@]|D|@]|E|$M|N]]|$1|O|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|P|$]]

If you are using the <code>Microsoft.ACE.OLEDB</code> provider, then be aware that it doesn't support a named range. You need to provide the name of the sheet <code>[Sheet1$]</code> or the name of the sheet followed by the range <code>[Sheet1$A1:P7928]</code>.
If the range is not provided, it will then define the table as the used range, which may contains empty rows.

One way to deal with empty rows would be to delete them, but the driver doesn't support the <code>DELETE</code> operation.

Another way is to first count the number of rows with a non empty <code>Id</code> and then use the result to define the range of the table for the <code>INSERT</code> statement:

<pre><code>using (OleDbConnection conn = new OleDbConnection(connectionString)) {
 conn.Open();

 string SheetName = "Sheet1";
 string TableRange = "A1:P{0}";

 // count the number of non empty rows
 using (var cmd1 = new OleDbCommand(null, conn)) {
 cmd1.CommandText = String.Format(
 "SELECT COUNT(*) FROM [{0}$] WHERE ID IS NOT NULL;"
 , SheetName);

 TableRange = string.Format(TableRange, (int)cmd1.ExecuteScalar() + 1);
 }

 // insert a new record
 using (var cmd2 = new OleDbCommand(null, conn)) {
 cmd2.CommandText = String.Format(
 "INSERT INTO [{0}${1}] (ID, Title, NTV_DB, Type) VALUES(7959, 8,'e','Type1');"
 , SheetName, TableRange);

 cmd2.ExecuteNonQuery();
 }
}
</code></pre>

blocks|key|143929|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|143930|private+void+GetExcelSheets(string+FilePath,+string+Extension,+string+isHDR)
{
string+conStr="";
switch+(Extension)
{
++++case+".xls":+//Excel+97-03
++++++++conStr+=+ConfigurationManager.ConnectionStrings["Excel03ConString"]
+++++++++++++++++.ConnectionString;
++++++++break;
++++case+".xlsx":+//Excel+07
++++++++conStr+=+ConfigurationManager.ConnectionStrings["Excel07ConString"]
+++++++++++++++++.ConnectionString;
++++++++break;
}

//Get+the+Sheets+in+Excel+WorkBoo
conStr+=+String.Format(conStr,+FilePath,+isHDR);
OleDbConnection+connExcel+=+new+OleDbConnection(conStr);
OleDbCommand+cmdExcel+=+new+OleDbCommand();
OleDbDataAdapter+oda+=+new+OleDbDataAdapter();
cmdExcel.Connection+=+connExcel;
connExcel.Open();

//Bind+the+Sheets+to+DropDownList
ddlSheets.Items.Clear();+
ddlSheets.Items.Add(new+ListItem("--Select+Sheet--",+""));++++
ddlSheets.DataSource=connExcel
+++++++++.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,+null);
ddlSheets.DataTextField+=+"TABLE_NAME";
ddlSheets.DataValueField+=+"TABLE_NAME";
ddlSheets.DataBind();
connExcel.Close();
txtTable.Text+=+"";
lblFileName.Text+=+Path.GetFileName(FilePath);
Panel2.Visible+=+true;
Panel1.Visible+=+false;
}|code-block|syntax|javascript|143931|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this

<pre><code>private void GetExcelSheets(string FilePath, string Extension, string isHDR)
{
string conStr="";
switch (Extension)
{
 case ".xls": //Excel 97-03
 conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
 .ConnectionString;
 break;
 case ".xlsx": //Excel 07
 conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
 .ConnectionString;
 break;
}

//Get the Sheets in Excel WorkBoo
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = connExcel;
connExcel.Open();

//Bind the Sheets to DropDownList
ddlSheets.Items.Clear(); 
ddlSheets.Items.Add(new ListItem("--Select Sheet--", "")); 
ddlSheets.DataSource=connExcel
 .GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
ddlSheets.DataTextField = "TABLE_NAME";
ddlSheets.DataValueField = "TABLE_NAME";
ddlSheets.DataBind();
connExcel.Close();
txtTable.Text = "";
lblFileName.Text = Path.GetFileName(FilePath);
Panel2.Visible = true;
Panel1.Visible = false;
}
</code></pre>

I have difficulties trying to insert rows into an existing table object. Here is my code snippet:

<pre><code>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();
}
</code></pre>

As a result I get my rows inserted below a ready-made table object:

<img src="https://i.stack.imgur.com/U7SGh.png" alt="enter image description here">

I've also tried inserting data inside a table object like so:

<pre><code> string insertQuery = String.Format("Insert into [{0}$].[MyTable] (ID, Title,NTV_DB, Type ) values(7959, 8,'e','Type1')", TabDisplayName);
</code></pre>

But I get an error:

<blockquote>
 The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly. If 'MyTable' is not a local object, check your network connection or contact the server administrator.
</blockquote>

As you can see, table with a name <code>MyTable</code> does exist. I would be very grateful if someone can shed some light on this mystery.

<img src="https://i.stack.imgur.com/zv9NJ.png" alt="enter image description here">

How to Insert Rows to Table Object Inside an Excel Sheet?

数据库

服务器

我在尝试向现有的表对象中插入行时遇到了困难。下面是我的代码片段：string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @"C:\myExcelFile.xlsx" + ";Extended Properties=\"Ex...

问如何在Excel表格中插入行到表格对象？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel表格中插入行到表格对象？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel表格中插入行到表格对象？
EN