none
How to delete empty cells of datatable

    Question

  • I have a DataTable in C#. I fill with my data. It looks like

    Class-1  Class-2

    1               -

    2               - 

    -               3    

    -               4

    What I want is deleting empty cells and. It should been displayed after deleting  

     

    Class-1  Class-2

    1               3

    2               4

     

    How can I delete specific cell in datatable ? 

    Monday, December 05, 2011 5:46 AM

Answers

  • Hi cokoder2,

    Welcome to the MSDN forum.

    You can try the sample below.

        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                int x = 1;
                int y = 1;
                string a = "class-1";
                string b = "class-2";
                DataTable datable = new DataTable();
                datable.Columns.Add(a);
                datable.Columns.Add(b);
                if (x > 0)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        DataRow dr;
                        dr = datable.NewRow();
                        dr[a] = i;
                        datable.Rows.Add(dr);
                    }
    
                }
    
                for (int i = 0; i < 3; i++)
                {
                    DataRow dr;
                    dr = datable.NewRow();
                    dr[b] = i;
                    datable.Rows.Add(dr);
                }
                ClearSpace(datable);
                dataGridView1.DataSource = datable;
                for (int i = 0; i < datable.Columns.Count; i++)
                    for (int j = 0; j < datable.Rows.Count; j++)
                        listView1.Items.Add(">" + datable.Rows[j][i].ToString());
    
            }
            private void ClearSpace(DataTable datable)
            {
                List<List<object>> listTemp = new List<List<object>>();
    
                for (int i = 0; i < datable.Columns.Count; i++)
                {
                    List<object> stack = new List<object>();
                    for (int j = 0; j < datable.Rows.Count; j++)
                    {
                        object obj = datable.Rows[j][i];
                        if (obj.ToString() != "")
                        {
                            stack.Add(obj);
                        }
                    }
                    listTemp.Add(stack);
                }
    
                datable.Clear();
    
                while (hasData(listTemp) == true)
                {
                    for (int i = 0; i < datable.Columns.Count; i++)
                    {
                        DataRow dr = datable.NewRow();
    
                        for (int j = 0; j < listTemp.Count; j++)
                        {
                            if (listTemp[j].Count != 0)
                            {
                                dr[j] = listTemp[j][0];
    
                                listTemp[j].RemoveAt(0);
                            }
                        }
    
                        datable.Rows.Add(dr);
                    }
    
                }
    
            }
    
            private bool hasData(List<List<object>> listTemp)
            {
                foreach (var item in listTemp)
                {
                    if (item.Count != 0)
                    {
                        return true;
                    }
                }
                return false;
            }
        }
    


    Have a nice day.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 07, 2011 2:21 AM

All replies

  • Hi,

    I am not sure, if I understood you correctly.

    What I understood is:

    You have a DataTable with 2 Columns: Class-1 and Class-2

    And your DataTable has 4 rows:
    1 -
    2 -
    - 3
    - 4

    But the result you want is not reasonable!

    In a DataTable you have rows. You can remove rows but you do not want to remove rows. You want a complete new set of rows! And when you see a row as a dataset, then your requirement also makes no sense for me.

    Maybe you want to split the DataTable up into 2 DataTables? So you get one DataTable only containing Class-1 and one DataTable only Containing Class-2?
    So the result would be:
    Class-1
    1
    2

    Class-2
    3
    4

    Is this your requirement? if so: How do you get the data into the datatable? Maybe a redesign would make sense in case these columns are not conected to each other.

    With kind regards,

    Konrad

    Monday, December 05, 2011 7:10 AM
  • > I have a DataTable in C#. [...] What I want is deleting empty cells
     

    so if you need shrink columns, then below is an example. it works for any number of columns.
    it uses Parallel Class for processing each columns. also it uses Barrier for synchronization.
    (the shrinker2 below is a refactored and faster version of the shrinker1).
      
     

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics;
    using System.Linq;
    using System.Threading;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace WindowsFormsApplication9
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                var dt = LoadData();
    
                this.Size = new System.Drawing.Size(800, 400);
                var dg = new DataGridView { 
                    AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
                    Parent = this, Dock = DockStyle.Fill, DataSource = dt };
                new DataGridView { 
                    AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
                    Parent = this, Dock = DockStyle.Right, Width = 250, DataSource = ((dynamic)Shrinker1.Run(dt)).Data };
                new DataGridView { 
                    AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill,
                    Parent = this, Dock = DockStyle.Right, Width = 250, DataSource = ((dynamic)Shrinker2.Run(dt)).Data };
                this.Shown += (s, e) => dg.FirstDisplayedScrollingRowIndex = 10000;
            }
            DataTable LoadData()
            {
                var dt = new DataTable();
                dt.Columns.Add("Col1", typeof(int));
                dt.Columns.Add("Col2", typeof(int));
                dt.Columns.Add("Col3", typeof(string));
                for (int i = 0; i < 10000; i++) dt.Rows.Add(null, null, null);
                dt.Rows.Add(1, null, "a");
                dt.Rows.Add(2, null, "b");
                dt.Rows.Add(null, 3, null);
                dt.Rows.Add(null, 4, null);
                dt.Rows.Add(5, 6, "c");
                dt.Rows.Add(7, null, "d");
                dt.Rows.Add(null, null, null);
                dt.Rows.Add(null, 8, null);
                dt.Rows.Add(null, 9, null);
                dt.Rows.Add(null, null, 10);
                for (int i = 0; i < 10000; i++) dt.Rows.Add(null, null, null);
                return dt;
            }
        }
    
    public static class Shrinker1
        {
            public static object Run(DataTable dt)
            {
                var sw = Stopwatch.StartNew();
                var infos = new Info[dt.Columns.Count];
                foreach (DataColumn dc in dt.Columns)
                    infos[dc.Ordinal] = new Info { Ordinal = dc.Ordinal, Table = dt };
    
                var ret = dt.Clone();
                var mb = new Barrier(infos.Length, b =>
                    ret.Rows.Add(infos.Select(inf => inf.Complete ? null : inf.Result).ToArray()));
    
                Parallel.ForEach(infos, info =>
                {
                    while (info.Run()) mb.SignalAndWait();
                    mb.RemoveParticipant();
                });
                sw.Stop();
                return new { Data = ret, ElapsedMilliseconds = sw.ElapsedMilliseconds };
            }
            class Info
            {
                public Info() { this.Position = -1; }
                public int Ordinal { get; set; }
                public DataTable Table { get; set; }
                public object Result { get; private set; }
                public int Position { get; private set; }
                public bool Complete { get; private set; }
                public bool Run()
                {
                    if (this.Complete) return false;
                    while (++this.Position < Table.Rows.Count)
                    {
                        var val = Table.Rows[this.Position][Ordinal];
                        if (val != DBNull.Value)
                        {
                            this.Result = val;
                            return true;
                        }
                    }
                    return !(this.Complete = true);
                }
            }
        }
    
        public static class Shrinker2
        {
            public static object Run(DataTable dt)
            {
                var sw = Stopwatch.StartNew();
                var ret = dt.Clone();
                var result = new object[dt.Columns.Count];
                var mb = new Barrier(dt.Columns.Count, b =>
                    {
                        ret.Rows.Add(result);
                        result = new object[dt.Columns.Count];
                    });
                Parallel.ForEach(dt.Columns.OfType<DataColumn>(), dc =>
                {
                    foreach (var val in Run(dt, dc.Ordinal))
                    {
                        result[dc.Ordinal] = val;
                        mb.SignalAndWait();
                    }
                    mb.RemoveParticipant();
                });
                sw.Stop();
                return new { Data = ret, ElapsedMilliseconds = sw.ElapsedMilliseconds };
            }
            static IEnumerable<object> Run(DataTable dt, int ordinal)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var val = dt.Rows[i][ordinal];
                    if (val != DBNull.Value)
                        yield return val;
                }
            }
        }
    }
    
    
     

    • Proposed as answer by Malobukv Wednesday, January 04, 2012 11:41 PM
    Monday, December 05, 2011 5:16 PM
  • I have added my code below. I just simplified my code to convey basic idea to you. I think ,There are 2 possibilities to solve that. One is adding data table without empty rows, another is deleting empty cells. Could you help me for one of them ? 

     

    private void button1_Click(object sender, EventArgs e)
            {
                int x = 1;
                int y = 1;
                string a = "class-1";
                string b = "class-2";
                DataTable datable = new DataTable();
                datable.Columns.Add(a);
                datable.Columns.Add(b);
                if (x > 0)
                {
                    for (int i = 0; i < 5; i++)
                    {
                     DataRow dr;
                    dr = datable.NewRow();
                    dr[a] = i;
                    datable.Rows.Add(dr);   
                    }
                    
                }
                
                    for (int i = 0; i < 3; i++)
                    {
                        DataRow dr;
                        dr = datable.NewRow();
                        dr[b] = i;
                        datable.Rows.Add(dr);
                    }
                
                dataGridView1.DataSource = datable;
                for (int i = 0; i < datable.Columns.Count; i++)
                    for (int j = 0; j < datable.Rows.Count; j++)
                listView1.Items.Add(">"+datable.Rows[j][i].ToString());
    
            }
    
    

    Monday, December 05, 2011 6:28 PM
  • I think Malobukv has resolved your problem.
    Tuesday, December 06, 2011 8:01 AM
  • Hi cokoder2,

    Welcome to the MSDN forum.

    You can try the sample below.

        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                int x = 1;
                int y = 1;
                string a = "class-1";
                string b = "class-2";
                DataTable datable = new DataTable();
                datable.Columns.Add(a);
                datable.Columns.Add(b);
                if (x > 0)
                {
                    for (int i = 0; i < 5; i++)
                    {
                        DataRow dr;
                        dr = datable.NewRow();
                        dr[a] = i;
                        datable.Rows.Add(dr);
                    }
    
                }
    
                for (int i = 0; i < 3; i++)
                {
                    DataRow dr;
                    dr = datable.NewRow();
                    dr[b] = i;
                    datable.Rows.Add(dr);
                }
                ClearSpace(datable);
                dataGridView1.DataSource = datable;
                for (int i = 0; i < datable.Columns.Count; i++)
                    for (int j = 0; j < datable.Rows.Count; j++)
                        listView1.Items.Add(">" + datable.Rows[j][i].ToString());
    
            }
            private void ClearSpace(DataTable datable)
            {
                List<List<object>> listTemp = new List<List<object>>();
    
                for (int i = 0; i < datable.Columns.Count; i++)
                {
                    List<object> stack = new List<object>();
                    for (int j = 0; j < datable.Rows.Count; j++)
                    {
                        object obj = datable.Rows[j][i];
                        if (obj.ToString() != "")
                        {
                            stack.Add(obj);
                        }
                    }
                    listTemp.Add(stack);
                }
    
                datable.Clear();
    
                while (hasData(listTemp) == true)
                {
                    for (int i = 0; i < datable.Columns.Count; i++)
                    {
                        DataRow dr = datable.NewRow();
    
                        for (int j = 0; j < listTemp.Count; j++)
                        {
                            if (listTemp[j].Count != 0)
                            {
                                dr[j] = listTemp[j][0];
    
                                listTemp[j].RemoveAt(0);
                            }
                        }
    
                        datable.Rows.Add(dr);
                    }
    
                }
    
            }
    
            private bool hasData(List<List<object>> listTemp)
            {
                foreach (var item in listTemp)
                {
                    if (item.Count != 0)
                    {
                        return true;
                    }
                }
                return false;
            }
        }
    


    Have a nice day.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 07, 2011 2:21 AM
  • Thanks.....
    Wednesday, December 07, 2011 6:25 AM
  • Hi cokoder2,

    Did this help? If you have any further questions, please feel free to let us know.


    Bob Shen [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 6:34 AM