Question add columns dynamically into datagridview to fit clipboard lines

karthik82.vk

Member
Joined
Oct 31, 2012
Messages
8
Programming Experience
Beginner
Hi,

I am creating a spreadsheet comparison tool in vb.net. The tool will allow user to paste contents copied from the clipboard into two datagridview in the form and will compare the data.

I want to generate the columns based on the clipboard contents dynamically. When i paste the data in the datagridview, the rows are getting generated based on the clipboard contents but the columns are not. I am manually specifying, the number of columns and i dont want to do so. the columns in the clipboard must also get automatically created like the rows.

Here is the code i am using.

VB.NET:
Sub pastefromclipboardtodatagridview(ByVal dgv As DataGridView)

        Dim rowSplitter As Char() = {vbCr, vbLf}

        Dim columnSplitter As Char() = {vbTab}



        'get the text from clipboard



        Dim dataInClipboard As IDataObject = Clipboard.GetDataObject()

        Dim stringInClipboard As String = CStr(dataInClipboard.GetData(DataFormats.Text))



        'split it into lines

        Dim rowsInClipboard As String() = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)


        'get the row and column of selected cell in grid

        Dim r As Integer = dgv.SelectedCells(0).RowIndex
        Dim c As Integer = dgv.SelectedCells(0).ColumnIndex


        'add rows into grid to fit clipboard lines

        If (dgv.Rows.Count < (r + rowsInClipboard.Length)) Then

            dgv.Rows.Add(r + rowsInClipboard.Length - dgv.Rows.Count)

        End If


        ' loop through the lines, split them into cells and place the values in the corresponding cell.

        Dim iRow As Integer = 0

        While iRow < rowsInClipboard.Length

            'split row into cell values

            Dim valuesInRow As String() = rowsInClipboard(iRow).Split(columnSplitter)

            'cycle through cell values

            Dim iCol As Integer = 0

            While iCol < valuesInRow.Length

                'assign cell value, only if it within columns of the grid

                If (dgv.ColumnCount - 1 >= c + iCol) Then

                    dgv.Rows(r + iRow).Cells(c + iCol).Value = valuesInRow(iCol)

                End If

                iCol += 1

            End While

            iRow += 1

        End While
    End Sub

The above code generates only the rows and not the columns. I worked on the above code and modified it so that it could generate the columns.

VB.NET:
Sub pastefromclipboardtodatagridview(ByVal dgv As DataGridView)

        Dim rowSplitter As Char() = {vbCr, vbLf}

        Dim columnSplitter As Char() = {vbTab}



        'get the text from clipboard



        Dim dataInClipboard As IDataObject = Clipboard.GetDataObject()

        Dim stringInClipboard As String = CStr(dataInClipboard.GetData(DataFormats.Text))



        'split it into lines

        Dim rowsInClipboard As String() = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)
        Dim colsInClipboard As String() = stringInClipboard.Split(columnSplitter, StringSplitOptions.RemoveEmptyEntries)


        'get the row and column of selected cell in grid

        Dim r As Integer = dgv.SelectedCells(0).RowIndex
        Dim c As Integer = dgv.SelectedCells(0).ColumnIndex


        'add rows into grid to fit clipboard lines

        If (dgv.Rows.Count < (r + rowsInClipboard.Length)) Then

            dgv.Rows.Add(r + rowsInClipboard.Length - dgv.Rows.Count)

        End If


        'add columns into grid to fit clipboard lines

        If (dgv.ColumnCount < (c + colsInClipboard.Length)) Then

            dgv.Columns.Add(c + colsInClipboard.Length - dgv.ColumnCount)

        End If


        ' loop through the lines, split them into cells and place the values in the corresponding cell.

        Dim iRow As Integer = 0

        While iRow < rowsInClipboard.Length

            'split row into cell values

            Dim valuesInRow As String() = rowsInClipboard(iRow).Split(columnSplitter)

            'cycle through cell values

            Dim iCol As Integer = 0

            While iCol < valuesInRow.Length

                'assign cell value, only if it within columns of the grid

                If (dgv.ColumnCount - 1 >= c + iCol) Then

                    dgv.Rows(r + iRow).Cells(c + iCol).Value = valuesInRow(iCol)

                End If

                iCol += 1

            End While

            iRow += 1

        End While
    End Sub

But i am getting the error "Value of type 'Integer' cannot be converted to 'System.Windows.Forms.DataGridViewColumn'." on the line

"dgv.Columns.Add(c + colsInClipboard.Length - dgv.ColumnCount)".

Can you please help me in this?

Thanks in advance...
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
Hi,

You need to create a new DataGridView Column object before you can add it to the DGV. Try this:-

VB.NET:
Dim dgvCol As New DataGridViewColumn()
dgvCol.HeaderText = "My New Column"
DataGridView1.Columns.Add(dgvCol)
Cheers,

Ian
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
Of course you're getting that error message. You can't just make up methods that don't exist. Have you read the documentation for the DataGridViewColumnCollection.Add method? It has two overloads: one that takes a single DataGridViewColumn and one that takes two Strings. You can just pass it an Integer and expect it to know what to do with it. If you want to add a number of columns then you have to create that many columns and add them. You either call Add that many times or else you create that many DataGridViewColumn objects and call AddRange.

Also, this doesn't make sense:
VB.NET:
Dim rowsInClipboard As String() = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)
[B][U]Dim colsInClipboard As String() = stringInClipboard.Split(columnSplitter, StringSplitOptions.RemoveEmptyEntries)[/U][/B]
That's going to split the entire contents of the clipboard into a single array on the column delimiter. That's not what you want. You need to split a single row to get the column values for that row. If you want to know how many columns you need then just split the first row, assuming that all rows have the same number of fields.
 

karthik82.vk

Member
Joined
Oct 31, 2012
Messages
8
Programming Experience
Beginner
Of course you're getting that error message. You can't just make up methods that don't exist. Have you read the documentation for the DataGridViewColumnCollection.Add method? It has two overloads: one that takes a single DataGridViewColumn and one that takes two Strings. You can just pass it an Integer and expect it to know what to do with it. If you want to add a number of columns then you have to create that many columns and add them. You either call Add that many times or else you create that many DataGridViewColumn objects and call AddRange.

Also, this doesn't make sense:
VB.NET:
Dim rowsInClipboard As String() = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)
[B][U]Dim colsInClipboard As String() = stringInClipboard.Split(columnSplitter, StringSplitOptions.RemoveEmptyEntries)[/U][/B]
That's going to split the entire contents of the clipboard into a single array on the column delimiter. That's not what you want. You need to split a single row to get the column values for that row. If you want to know how many columns you need then just split the first row, assuming that all rows have the same number of fields.


Hi,

Thanks for your reply...

Can you help me on how to split the values and get the column values if possible....
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,723
Location
Sydney, Australia
Programming Experience
10+
You've got an array of rows by splitting the entire contents. Now loop through that array and, for each one, split the row to get the field values. On the first row, use the number of field value to determine how many columns to add to the grid.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,604
Location
Norway
Programming Experience
10+
Note that if you RemoveEmptyEntries when you split each row the tabular data will change form if there is a cell without value, for example you could have this data in spreadsheet:
1 2 3
_ 2 _
When pasted with that code you would get this table in datagridview:
1 2 3
2 _ _

DataGridView has a ColumnCount property that can be set.
There is also a RowCount property, but it is simpler in code to use the Rows.Add method that takes an array of values.
This shows the simplified code:
    Private Sub ClipdataToGrid(grid As DataGridView)
        grid.Rows.Clear()
        Dim rows = Clipboard.GetText.Split(New String() {vbNewLine}, StringSplitOptions.RemoveEmptyEntries)
        grid.ColumnCount = rows(0).Split(vbTab).Length
        For Each row In rows
            grid.Rows.Add(row.Split(vbTab))
        Next
    End Sub

I would also add a 'data' parameter to the method and use the same method for both copy/paste and drag/drop operations.
 
Top Bottom