how can i merge cell of a gridview having same value at runtime in vb.net .-Collection of common programming errors

11 years ago

admin

13 minutes

msdn

Hello,

i want to show the schedule in a grid.In that grid there is a column ‘Time’ with the cells like 8am,8:30:am……9pm. and other column have employee name so if an employee has task from 8 am to 10 am then i make those cells in red color with employee name. now whats happening is, from 8am to 10 am all the cells are showing that employee name but i want to merge them into one and make the employee name at center.

Please help me out.

Thanks & Regards,

Sonal
25 Answers

msdn1

Hi,

i recreated the scenario in VB.NET language with DataTable as datasource. Please follow the code.It is working fine.

Imports System.Data

Public Class Form1
    Dim dt As New DataTable

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()
        dt.Columns.Add(New DataColumn("Column1"))
        dt.Columns.Add(New DataColumn("Column2"))

        Dim row1 As DataRow = dt.NewRow()
        row1("Column1") = "A"
        row1("Column2") = "Green"
        dt.Rows.Add(row1)

        Dim row2 As DataRow = dt.NewRow()
        row2("Column1") = "A"
        row2("Column2") = "Pink"
        dt.Rows.Add(row2)

        Dim row3 As DataRow = dt.NewRow()
        row3("Column1") = "B"
        row3("Column2") = "Red"
        dt.Rows.Add(row3)

        Dim row4 As DataRow = dt.NewRow()
        row4("Column1") = "C"
        row4("Column2") = "Blue"
        dt.Rows.Add(row4)

        Dim row5 As DataRow = dt.NewRow()
        row5("Column1") = "D"
        row5("Column2") = "Green"
        dt.Rows.Add(row5)

        Dim row6 As DataRow = dt.NewRow()
        row6("Column1") = "D"
        row6("Column2") = "Blue"
        dt.Rows.Add(row6)

        Dim row7 As DataRow = dt.NewRow()
        row7("Column1") = "E"
        row7("Column2") = "Green"
        dt.Rows.Add(row7)

        DataGridView1.DataSource = dt

    End Sub


    Private Sub DataGridView1_CellPainting(sender As System.Object, e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting

        If e.RowIndex = 0 Then

            If (DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString() = DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString()) Then
                If (e.ColumnIndex = 0) Then
                    e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                End If
            End If

        End If

        If e.RowIndex > 0 Then
            If e.RowIndex < DataGridView1.Rows.Count - 2 Then
                If (DataGridView1.Rows(e.RowIndex - 1).Cells(0).Value.ToString() = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString()) Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If

                If (DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString() = DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString()) Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If
            End If

        End If


    End Sub
End Class

Let me know if your issue persists. As you said you can remove duplicate text , so just let me know if you were able to do that!

msdn2

Hi Sonal,

Thanks for your patience.

I thought it will be a better way to remove repeated entry in the DataSource itself and with some little modifications you can achieve your requirement.

So, before binding i modified the data table like this :

 Dim prev As String = dt.Rows(0)(0).ToString()

        For i = 1 To dt.Rows.Count - 1

            If dt.Rows(i)(0).ToString() = prev Then
                dt.Rows(i)(0) = ""
            Else
                prev = dt.Rows(i)(0).ToString()
            End If

        Next
        DataGridView1.DataSource = dt

After modification, i have done some changes in the CellPainting event, so please follow the code, you will achieve your requirement:

 Private Sub DataGridView1_CellPainting(sender As System.Object, e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting

        If e.RowIndex > 0 Then
            If e.RowIndex < DataGridView1.Rows.Count - 2 Then
                If DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString() = "" Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If

                If DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString() = "" Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If
            End If
        End If

        If e.RowIndex = 0 Then
            If DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString() = "" Then
                If (e.ColumnIndex = 0) Then
                    e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                End If
            End If
        End If
    End Sub

Hope this helps. Let me know if you are still in problem.

One good question is equivalent to ten best answers.

msdn3

Hello,

Not completely clear but is this what you want i.e. remove repeating data?

If so then add the following DataGridView to your project, use it as any DataGridView i.e. DataGridView1.DataSource = SomeDataTable.

''' 
''' Original author
''' http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/a44622c0-74e1-463b-97b9-27b87513747e#faq8
''' 
''' 
''' Original code was in C Sharp, I converted and tweaked some code
''' which did not compile under VB.NET
''' 
Public Class GroupByGrid
    Inherits DataGridView
    Protected Overrides Sub OnCellFormatting(ByVal args As DataGridViewCellFormattingEventArgs)
        MyBase.OnCellFormatting(args)
        ' First row always displays
        If args.RowIndex = 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.Value = String.Empty
            args.FormattingApplied = True
        End If
    End Sub
    Private Function IsRepeatedCellValue(ByVal rowIndex As Integer, ByVal colIndex As Integer) As Boolean
        Dim currCell As DataGridViewCell = Rows(rowIndex).Cells(colIndex)
        Dim prevCell As DataGridViewCell = Rows(rowIndex - 1).Cells(colIndex)
        If (currCell.Value Is prevCell.Value) OrElse (currCell.Value IsNot Nothing AndAlso prevCell.Value IsNot Nothing AndAlso currCell.Value.ToString() = prevCell.Value.ToString()) Then
            Return True
        Else
            Return False
        End If
    End Function
    Protected Overrides Sub OnCellPainting(ByVal args As DataGridViewCellPaintingEventArgs)
        MyBase.OnCellPainting(args)
        args.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
        ' Ignore column and row headers and first row
        If args.RowIndex < 1 OrElse args.ColumnIndex < 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
        Else
            args.AdvancedBorderStyle.Top = AdvancedCellBorderStyle.Top
        End If
    End Sub
End Class

msdn4

Hi Shyam,

It is a window form Desktop Project.
msdn5
msdn6

Yes, I have gone through the below link, but i didnt find it helpful.

http://www.dotnetspider.com/resources/4644-Merge-columns-DataGridView-Excel-Spread.aspx

msdn7

Hi, follow the code to get your resolution.

dataGridView1 is added in designer.

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        List li = new List();
        public Form1()
        {
            InitializeComponent();
           
            li.Add(new X() { Column1 = "A", Column2 ="Green" });
            li.Add(new X() { Column1 = "A", Column2 = "Red" });
            li.Add(new X() { Column1 = "B", Column2 = "Green" });
            li.Add(new X() { Column1 = "C", Column2 = "Blue" });
            li.Add(new X() { Column1 = "E", Column2 = "Pink" });
            li.Add(new X() { Column1 = "E", Column2 = "Green" });
            li.Add(new X() { Column1 = "F", Column2 = "Black" });


            dataGridView1.DataSource = li;
            dataGridView1.CellPainting += new DataGridViewCellPaintingEventHandler(dataGridView1_CellPainting);
        }

       

        void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.RowIndex == 0)
            {
                if (dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == dataGridView1.Rows[e.RowIndex +1].Cells[0].Value.ToString())
                {
                    if (e.ColumnIndex == 0)
                    {
                        e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None;
                        
                    }
                }
            }
            
            if (e.RowIndex >0)
            {
                if (dataGridView1.Rows[e.RowIndex - 1].Cells[0].Value.ToString() == dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString())
                {
                    if (e.ColumnIndex == 0)
                    {
                        e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None;
                       
                    }
                }

                if (e.RowIndex < dataGridView1.Rows.Count - 1)
                {
                    if (dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString() == dataGridView1.Rows[e.RowIndex + 1].Cells[0].Value.ToString())
                    {
                        if (e.ColumnIndex == 0)
                        {
                            e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None;
                           
                        }
                    }
                }
            }
        }
    }

    public class X
    {
        public string Column1 { get; set; }
        public string Column2 { get; set; }
    }
}

Hope this may help.

One good question is equivalent to ten best answers.

msdn8

Thanks Shyam.

But i have used same code before but it didnt work.datagridview cells border style remain same.
msdn9 Oh yea. I only thought to merge the borders. I left to take only one data per merged cell.Ok let me try.
Did the borders are merged from your side for repeated data?

One good question is equivalent to ten best answers.
msdn10

Nope, data i can handle but the issue is with the border only. its nt getting merge because i guess border style isnt getting change.
msdn11 Hi, have you copy-paste my whole code and taken DataGridView in design ?

Here from my side everything works fine. All matching cells border are merged from my side.
msdn12

I copied what i needed like i copied the whole code from datagridview CellPainting event and pasted it into Datagridview cellpainting event in my code.
msdn13

I am binding Datagridview to a datatable. i am using the above code in the DataGridView_CellPainting event :

For i As Integer = 1 To dgvScheduler.Columns.Count – 1 If e.RowIndex = 0 Then If dgvScheduler.Rows(e.RowIndex).Cells(i).Value.ToString() = dgvScheduler.Rows(e.RowIndex + 1).Cells(i).Value.ToString() Then If e.ColumnIndex = 0 Then e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None End If End If End If If e.RowIndex > 0 Then If dgvScheduler.Rows(e.RowIndex – 1).Cells(i).Value.ToString() = dgvScheduler.Rows(e.RowIndex).Cells(i).Value.ToString() Then If e.ColumnIndex = 0 Then e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None End If End If If e.RowIndex < dgvScheduler.Rows.Count – 1 Then If dgvScheduler.Rows(e.RowIndex).Cells(i).Value.ToString() = dgvScheduler.Rows(e.RowIndex + 1).Cells(i).Value.ToString() Then If e.ColumnIndex = 0 Then e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None End If End If End If End If

Next
msdn14

i created a separate application and used your code it worked absolutely fine. then why not in my code.

msdn15

Hi,

i recreated the scenario in VB.NET language with DataTable as datasource. Please follow the code.It is working fine.

Imports System.Data

Public Class Form1
    Dim dt As New DataTable

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()
        dt.Columns.Add(New DataColumn("Column1"))
        dt.Columns.Add(New DataColumn("Column2"))

        Dim row1 As DataRow = dt.NewRow()
        row1("Column1") = "A"
        row1("Column2") = "Green"
        dt.Rows.Add(row1)

        Dim row2 As DataRow = dt.NewRow()
        row2("Column1") = "A"
        row2("Column2") = "Pink"
        dt.Rows.Add(row2)

        Dim row3 As DataRow = dt.NewRow()
        row3("Column1") = "B"
        row3("Column2") = "Red"
        dt.Rows.Add(row3)

        Dim row4 As DataRow = dt.NewRow()
        row4("Column1") = "C"
        row4("Column2") = "Blue"
        dt.Rows.Add(row4)

        Dim row5 As DataRow = dt.NewRow()
        row5("Column1") = "D"
        row5("Column2") = "Green"
        dt.Rows.Add(row5)

        Dim row6 As DataRow = dt.NewRow()
        row6("Column1") = "D"
        row6("Column2") = "Blue"
        dt.Rows.Add(row6)

        Dim row7 As DataRow = dt.NewRow()
        row7("Column1") = "E"
        row7("Column2") = "Green"
        dt.Rows.Add(row7)

        DataGridView1.DataSource = dt

    End Sub


    Private Sub DataGridView1_CellPainting(sender As System.Object, e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting

        If e.RowIndex = 0 Then

            If (DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString() = DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString()) Then
                If (e.ColumnIndex = 0) Then
                    e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                End If
            End If

        End If

        If e.RowIndex > 0 Then
            If e.RowIndex < DataGridView1.Rows.Count - 2 Then
                If (DataGridView1.Rows(e.RowIndex - 1).Cells(0).Value.ToString() = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString()) Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If

                If (DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString() = DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString()) Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If
            End If

        End If


    End Sub
End Class

Let me know if your issue persists. As you said you can remove duplicate text , so just let me know if you were able to do that!

msdn16

HI,

you need to modify second If condition. Get the code with appropiate If condition as per the Code.

Different languages different constructs!
msdn17

Seems its working. Can u pls tell me how to merge data now?
msdn18

Seems its working. Can u pls tell me how to merge data now?

Even if we remove the duplicate Text, i fear is it possible to Show the Text Vertically Center ?

Since unlike HTML table, here we did not have CellSpan or ColumnSpan ? So how your cells Text will be vertically center aligned ?
msdn19

yeah true. but its no use if i don’t remove the duplicate Text. Can we show the text at TOP ?
msdn20

yeah true. but its no use if i don’t remove the duplicate Text. Can we show the text at TOP ?

Yes, that we can try 🙂
msdn21

ok, then lets try 🙂

msdn22

Hi Sonal,

Thanks for your patience.

I thought it will be a better way to remove repeated entry in the DataSource itself and with some little modifications you can achieve your requirement.

So, before binding i modified the data table like this :

 Dim prev As String = dt.Rows(0)(0).ToString()

        For i = 1 To dt.Rows.Count - 1

            If dt.Rows(i)(0).ToString() = prev Then
                dt.Rows(i)(0) = ""
            Else
                prev = dt.Rows(i)(0).ToString()
            End If

        Next
        DataGridView1.DataSource = dt

After modification, i have done some changes in the CellPainting event, so please follow the code, you will achieve your requirement:

 Private Sub DataGridView1_CellPainting(sender As System.Object, e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting

        If e.RowIndex > 0 Then
            If e.RowIndex < DataGridView1.Rows.Count - 2 Then
                If DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString() = "" Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If

                If DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString() = "" Then
                    If (e.ColumnIndex = 0) Then
                        e.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
                    End If
                End If
            End If
        End If

        If e.RowIndex = 0 Then
            If DataGridView1.Rows(e.RowIndex + 1).Cells(0).Value.ToString() = "" Then
                If (e.ColumnIndex = 0) Then
                    e.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
                End If
            End If
        End If
    End Sub

Hope this helps. Let me know if you are still in problem.

One good question is equivalent to ten best answers.

msdn23

Hello,

Not completely clear but is this what you want i.e. remove repeating data?

If so then add the following DataGridView to your project, use it as any DataGridView i.e. DataGridView1.DataSource = SomeDataTable.

''' 
''' Original author
''' http://social.msdn.microsoft.com/Forums/en-US/winformsdatacontrols/thread/a44622c0-74e1-463b-97b9-27b87513747e#faq8
''' 
''' 
''' Original code was in C Sharp, I converted and tweaked some code
''' which did not compile under VB.NET
''' 
Public Class GroupByGrid
    Inherits DataGridView
    Protected Overrides Sub OnCellFormatting(ByVal args As DataGridViewCellFormattingEventArgs)
        MyBase.OnCellFormatting(args)
        ' First row always displays
        If args.RowIndex = 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.Value = String.Empty
            args.FormattingApplied = True
        End If
    End Sub
    Private Function IsRepeatedCellValue(ByVal rowIndex As Integer, ByVal colIndex As Integer) As Boolean
        Dim currCell As DataGridViewCell = Rows(rowIndex).Cells(colIndex)
        Dim prevCell As DataGridViewCell = Rows(rowIndex - 1).Cells(colIndex)
        If (currCell.Value Is prevCell.Value) OrElse (currCell.Value IsNot Nothing AndAlso prevCell.Value IsNot Nothing AndAlso currCell.Value.ToString() = prevCell.Value.ToString()) Then
            Return True
        Else
            Return False
        End If
    End Function
    Protected Overrides Sub OnCellPainting(ByVal args As DataGridViewCellPaintingEventArgs)
        MyBase.OnCellPainting(args)
        args.AdvancedBorderStyle.Bottom = DataGridViewAdvancedCellBorderStyle.None
        ' Ignore column and row headers and first row
        If args.RowIndex < 1 OrElse args.ColumnIndex < 0 Then
            Return
        End If
        If IsRepeatedCellValue(args.RowIndex, args.ColumnIndex) Then
            args.AdvancedBorderStyle.Top = DataGridViewAdvancedCellBorderStyle.None
        Else
            args.AdvancedBorderStyle.Top = AdvancedCellBorderStyle.Top
        End If
    End Sub
End Class

msdn24

Hi Shyam,

Thank you so much.

I really appreciated the way u helped me out and understood what i wanted.Finally i achieved exactly what i wanted 🙂
msdn25

Hello Kevin,

Thanks alot.

your code is working perfectly and it helped me 🙂

25 Answers