Zerg00s Zerg00s - 3 months ago 11
C# Question

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

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:

enter image description here

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);


But I get an error:


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.


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.

enter image description here

Answer

Based on here:

ADO.NET cannot access XL's Table objects (aka Lists) nor can it access range names that directly or indirectly reference the table's name or structured references. ADO.NET can access named ranges over tables that reference the table's cell range.

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

Other restrictions:

Workbook Protection: ADO cannot access password protected workbooks.

Used Rows: ADO will insert below the last used row. NOTE! The last used row can be empty.

And Tables as mentioned already.