CODEWITHSUNDEEP
c#.netvb.netwinformsvisual-studio-2008
Farhan Mukadam
Farhan Mukadam

Reputation: 470

remove blank cells from datatable

I have following datatable:

enter image description here

As shown in pic, there are some blank cells in this data table.

I want to remove them and show only the cells with values.

I tried following code but it didnt worked:

For i=0 to dt.Rows.count-1

For j=0 to dt.Column.count-1


if dt.rows(i).tostring()="" then

dt.rows.remove(i)

end if

next

Next

This code does not works. Please help me.

Answer in c# can also help me.

Upvotes: 0

Views: 5007

Answers (2)

King King
King King

Reputation: 63327

You had better remove the columns first:

dt.Columns.OfType<DataColumn>()
          .Where(c=>!dt.Rows.OfType<DataRow>()
                            .Any(r=>r.Field<string>(c.ColumnName) != ""))
          .ToList()
          .ForEach(c=>dt.Columns.Remove(c));

//Remove rows
dt.Rows.OfType<DataRow>()
       .Where(r=>!dt.Columns.OfType<DataColumn>()
                            .Any(c=>r.Field<string>(c.ColumnName) != ""))
       .ToList()
       .ForEach(r=>dt.Rows.Remove(r));

Upvotes: 1

dotNET
dotNET

Reputation: 35400

Ideally you only want to remove a row when ALL of its cells are empty. The algo you have shown will delete the row even if a single cell in it is empty. You should change your code like this:

First remove empty rows:

For i = 0 To dt.Rows.Count - 1
    flag = True

    For j = 0 To dt.Columns.Count - 1
        If Not(dt.Rows(i).IsNull(j)) AndAlso dt.Rows(i)(j).ToString() <> "" Then
            flag = False
            Exit For
        End If
    Next

    If flag Then dt.Rows(i).Delete()
Next

dt.AcceptChanges()

Now remove empty columns:

For i = 0 To dt.Columns.Count - 1
    flag = True

    For j = 0 To dt.Rows.Count - 1
        If Not(dt.Rows(j).IsNull(i)) AndAlso dt.Rows(j)(i).ToString() <> "" Then
            flag = False
            Exit For
        End If
    Next

    If flag Then dt.Columns.RemoveAt(i)
Next

dt.AcceptChanges()

Upvotes: 2

Related Questions