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:

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.

Answers


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.


If you execute this 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

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:

  1. You must delete all rows after 7928 in excel file.
  2. You must insert on specific position.

If you are using the Microsoft.ACE.OLEDB provider, then be aware that it doesn't support a named range. You need to provide the name of the sheet [Sheet1$] or the name of the sheet followed by the range [Sheet1$A1:P7928]. 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 DELETE operation.

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

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

Try this

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

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

Table3.ListRows[1].Range.Insert(Excel.XlInsertShiftDirection.xlShiftDown);

Need Your Help

How can I instantiate a Mock Kafka Topic for junit tests?

junit mocking apache-kafka

I have some JUnit tests on code that uses a kafka topic. The mock kafka topics I've tried do not work and the examples found online are very old so they also do not work with 0.8.2.1. How do I crea...

Codeigniter Session returning 310 redirect

php codeigniter session redirect

I have a callback function that checks my login details are correct - If they are wrong it returns an error (this is working fine). If the details are correct it should set the session $this->s...