How to eleminate row from datagridview on specified condition

kau14 0 238 Views

I have filled the datagridview on the buttonclick

Now, The reuirement is, If the excel having null, or 0 or stringvalue datagridview should eleminate that row.

Please suggest.

Thanks & Regards
Kaustubh

private void btn_choose_excel_Click_1(object sender, EventArgs e)
        {
            string pathconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + txt_path.Text + ";extended properties=\"Excel 8.0; HDR=yes;\";";
            OleDbConnection cn = new OleDbConnection(pathconn);
            OleDbDataAdapter adp = new OleDbDataAdapter("select * from[" + txt_sheetname.Text + "$]", cn);
            DataTable dt = new DataTable();
            adp.Fill(dt);

            dataGridView1.DataSource = dt;

            List<DataGridViewRow> rowsToRemove = new List<DataGridViewRow>();

            //foreach (DataGridViewRow row in dataGridView1.Rows)
            //{
            //    if (!row.IsNewRow)
            //    {
            //        if (int.Parse(dataGridView1[1, row.Index].Value.ToString()) == 0)
            //        {
            //            rowsToRemove.Add(row);
            //        }


            //    }
            //}
            //foreach (DataGridViewRow row in rowsToRemove)
            //    dataGridView1.Rows.Remove(row);
            this.dataGridView1.Sort(this.dataGridView1.Columns["Name"], ListSortDirection.Ascending);
        }

Dani AI

Generated

— gave the right direction: don’t try to delete rows from the DataGridView UI, filter or remove them from the DataSource. Below are three practical ways to keep only “valid” rows (null / empty / zero / non-numeric removed). Pick the one that fits your scenario.

Option A — filter at read time (fastest)
Use a WHERE clause in the SELECT so invalid rows never get loaded. For example (adjust the column name to your sheet header):

string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_path.Text + 
    ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";
string sql = "SELECT * FROM [" + txt_sheetname.Text + "$] " +
    "WHERE [Name] IS NOT NULL AND [Name] <> '' AND [Name] <> '0'";

Option B — DataView filter (UI-only, reversible)
Bind a DataView and set RowFilter/Sort if you want to hide rows without changing the table:

var dv = new DataView(dt);
dv.RowFilter = "[Name] IS NOT NULL AND [Name] <> '' AND [Name] <> '0'";
dv.Sort = "Name ASC";
dataGridView1.DataSource = dv;

Option C — clean the DataTable (permanent removal)
If you really want to remove rows, remove them from the DataTable while iterating backwards and using TryParse so non-numeric strings get removed safely:

for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
    var obj = dt.Rows[i]["Name"];
    string s = obj == DBNull.Value ? "" : obj.ToString().Trim();
    if (s == "" || s == "0" || !decimal.TryParse(s, out decimal v) || v == 0m)
        dt.Rows.RemoveAt(i);
}
dataGridView1.DataSource = dt;

Notes and pitfalls:

  • OleDb/Excel can infer column types (mixed types → surprising nulls). Adding IMEX=1 helps; for .xlsx prefer the ACE provider.
  • Use the actual header name in brackets (e.g., [Name]) or adjust for numeric comparisons (no quotes around numbers).
  • If you bind a DataView, use its Sort property (or BindingSource) instead of DataGridView.Sort.
Member Avatar for SteveDotNet
SteveDotNet 0 Light Poster

You do NOT remove rows from the DataGrid. Remove them from the DataSource.

Member Avatar for kau14
kau14 0 Newbie Poster

Hi Steve,

Thanks for the reply.
I wont wish to eliminate through the excel,
As datagridview's only purpose to display the valid data.

Member Avatar for SteveDotNet
SteveDotNet 0 Light Poster

Consider:
1. adding a where clause to the query
2. using a DataView as the DataGrid's DataSource

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview