0 Replies - 3986 Views - Last Post: 09 August 2015 - 08:40 AM

#1 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6444
  • View blog
  • Posts: 26,066
  • Joined: 12-December 12

Paste Data as New DataGridView Rows

Posted 09 August 2015 - 08:40 AM

This snippet enables copying and pasting data from, for example, a text or Excel file as new DataGridView rows. Click into the new, blank, row at the end of the DGV before using Ctrl-V to paste.

Check for Ctrl-V:
    Private Sub DataGridView1_KeyDown(sender As Object, e As KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.V And Keys.ControlKey Then
        End If
    End Sub

The following code assumes the data is tab-delimited, which also applies for Excel data. It can be modified for a different delimiter.
    Private Sub PasteClipboard()
        Dim sClip As String

        sClip = Clipboard.GetText()
        If String.IsNullOrWhiteSpace(sClip) Then
            MessageBox.Show("Nothing in clipboard.")
            Exit Sub
        End If

        Dim sLines() As String = sClip.Split(ControlChars.NewLine)
        Dim sCols() As String

        If DataGridView1.CurrentRow.Index <> DataGridView1.NewRowIndex Then
            MessageBox.Show("Must be in the new row.")
            Exit Sub
        End If
        For Each line As String In sLines
            sCols = line.Split(ControlChars.Tab)

            If sCols.Length = 1 AndAlso sCols(0).Trim().Length = 0 Then
                MessageBox.Show("Empty or single newline not pasted.")
                Exit Sub
            End If
            'exit if columns unmatched
            If sCols.Length <> DataGridView1.Columns.Count Then
                MessageBox.Show("Number of columns don't match.")
                Exit Sub
            End If


            ' or create a new object instance to match the DGV's data source
            'Dim row As New DataGridViewRow()
            'row.SetValues(New Object() {sCols(0), Decimal.Parse(sCols(1)), Integer.Parse(sCols(2))})
    End Sub

Each row is inserted as a string array. The comments indicate how formatted (typed) rows can be inserted. Instead of New Object() you would create a new instance of the class that your DGV is bound to.

The check If sCols.Length = 1 .. is because copied Excel data comes with a additional row containing only a newline character. (This could also be the case if copying data from a text file.)

This post has been edited by andrewsw: 09 August 2015 - 08:45 AM

Is This A Good Question/Topic? 0
  • +

Page 1 of 1