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

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

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.

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 WorkBooconStr = 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 DropDownListddlSheets.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;}