C# delete row excel using OLEDB

Question

0.00/5 (No votes)

See more:

How to delete rows from a excel using OLEDB? I tried with Delete from rows 2 [Sheet1$] but it's not working.
Excel ex:
STT Name
1 A
2 B
3 C

private void button1_Click(object sender, EventArgs e)
        {
            string a1 = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + textLoadFile.Text + "';Extended Properties=\"Excel 12.0;HDR=YES;\"";
            using (OleDbConnection conn = new OleDbConnection(a1))
            {
                try
                {
                    //MessageBox.Show(dataGrid["STT", dataGrid.CurrentRow.Index].Value.ToString());
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "DELETE FROM [Sheet1$] WHERE STT = 2";
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error !");
                }
            }
            ReadExceltoDataGrid("Sheet1");
        }

What I have tried:

Thank you ! https://i.stack.imgur.com/RMCQd.png

Posted 28-Sep-20 0:03am

Member 14939365

Updated 28-Sep-20 2:26am

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2020-09-28T00:23:00

Solution 1

You can't delete rows in Excel with OleDb. You can insert and update rows, but deleting is not allowed. If your application is not running on a server, you could always use Excel interop to delete the row, but not through OleDb.

Posted 28-Sep-20 0:23am

Pete O'Hanlon

Maciej Los · Answer 2 · 2020-09-28T02:26:00

In addition to Pete O'Hanlon solution, i'd suggest to read this: Unable to update or delete data in a linked table - Office | Microsoft Docs[^]

The old version of MSDN documentation had got this info:

DELETE
You are more restricted in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:

Delete an entire record at once or you receive the following error message:
Deleting data in a linked table is not supported by this ISAM.
You can only delete a record by blanking out the contents of each individual field.
Delete the value in a cell containing an Excel formula or you receive the following error message:
Operation is not allowed in this context.
You cannot delete the empty spreadsheet row(s) in which the deleted data was located, and your recordset will continue to display empty records corresponding to these empty rows.

The new one - does not. I have no idea why MS decided to remove such of content.

To workaround of this is to update your model by adding new field holding boolean values (true/false or Yes/No or 0/1). For example:

STT
Name
Deleted (or Active)

Then, instead of deleting row, you can mark it as deleted:

SQL

UPDATE [Sheet1$] SET Deleted = True

;)

Good luck!

C# delete row excel using OLEDB

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

C# delete row excel using OLEDB

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us