Excel kept blank cells as an used cell

Question

I tried to insert rows in excel with OLEDB and the problem is :

• : I insert a couple of rows at the end of my sheet.
• : I open my excel document and see my inserted rows.
• : I delete all the inserted rows and restart my application.
• : The rows continue to be inserted at the last row index

ex :

• row inserted at row 3020 in excel
• delete this row and save
• restart the app and insert again
• row will be at row 3021 in excel

public void Insert (string text, Excel.Worksheet ws, string column){

        OleDbCommand cmd1 = new OleDbCommand("INSERT INTO ["+ws.Name+"$] " +
                           "([" + column + "]) VALUES(' " + text + " ')", _oleConn);

        cmd1.ExecuteNonQuery();
    }

Answer 1

It could be that the Worksheet is keeping the XML of those columns behind the scenes. Your current code places the row at the end of the worksheet. The code would have to be modified to delete the empty row(s) before the row you want to put in, and then add. An example would be this:

public void Insert (string text, Excel.Worksheet ws, string column){
    OleDbCommand cmd0 = new OleDbCommand("DELETE FROM ["+ws.Name+"$] where /*column1*/ = '' AND /*column2*/ = '' /*...*/ AND /*columnN*/ = ''", _oleConn);

    cmd0.ExecuteNonQuery();

    OleDbCommand cmd1 = new OleDbCommand("INSERT INTO ["+ws.Name+"$] " +
                       "([" + column + "]) VALUES(' " + text + " ')", _oleConn);

    cmd1.ExecuteNonQuery();
}