Paste Text in DataGridView Control (Bound with BindingSource)

DataGridView control does NOT have the built-in Paste functionality. Therefore we are going to create a Generic Function which will Paste Text in DataGridView control.

Level: Intermediate

Knowledge Required:

• DataGridView
• Clipboard
• String
• BindingSource
• DataTable

Description:
In the my earlier post:

DataGridView Control FAQs and Quick Tips

I have discussed how to Copy Data from DataGridView control to Clipboard. Now we will see how can we Paste Data from Clipboard to DataGridView control. The following function is Generic for DataGridView control which is bound with some BindingSource. Optionally user can provide the DataTable (which is DataGridView source). This parameter is used in the function to check a Column whether it is Read-Only or NOT.

Features:
1) Paste Data at the End: Set the current position at the last row (which creates the new row) and execute the function, new rows will be added
2) Paste Data in the middle rows: Set the current position anywhere in middle (not at end) and execute the function, middle rows will be overwritten by new data
3) Paste Data in any column: Set the current position in any column and execute function, function will start pasting data from that particular column

Public Sub PasteInBoundDataGridView(ByVal dgvToUse As DataGridView, ByVal bsDataGridView As BindingSource, ByVal tblSource As DataTable)
    Dim iCurrentRow As Integer = -1
    Dim bFullRowSelected As Boolean = False

    ' if some row is selected
    If dgvToUse.CurrentRow IsNot Nothing Then
        ' if it is NOT a new row
        If Not dgvToUse.CurrentRow.IsNewRow Then
        ' get the index of that row
            iCurrentRow = dgvToUse.CurrentRow.Index
        End If

        ' if current row is selected
        If dgvToUse.CurrentRow.Selected Then
            ' it means full row is selected
            bFullRowSelected = True
        End If
    End If

    ' cancel the current edit
    bsDataGridView.CancelEdit()

    Dim sText As String
    Dim sLines() As String
    Dim iCurCol As Integer

    ' if full row is selected
    If bFullRowSelected Then
        ' then set the initial column = 0
        iCurCol = 0
    Else ' else if full row is NOT selected
        ' set the initial column = current column
        iCurCol = dgvToUse.CurrentCell.ColumnIndex
    End If

    ' get the text from clipboard
    sText = My.Computer.Clipboard.GetText()
    ' split the text into lines
    sLines = sText.Split(New String() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)
    ' for each line in extracted lines
    For Each sLine As String In sLines
        Dim sColValues() As String
        ' split the line into columns
        sColValues = sLine.Split(vbTab)

        Dim c As Integer = iCurCol
        Dim rowEdit As DataRowView

        ' if currently some middle rows are selected and also
        ' selected row is NOT the last row
        If iCurrentRow >= 0 AndAlso iCurrentRow < dgvToUse.Rows.Count - 1 Then
            ' row is selected row
            rowEdit = CType(dgvToUse.Rows(iCurrentRow).DataBoundItem, DataRowView)
            ' now move to next row
            iCurrentRow += 1
        Else ' else it means we are at end then
            ' we will add the row
            rowEdit = bsDataGridView.AddNew
        End If

        ' for each column in extracted columns
        For Each sColValue As String In sColValues
            ' if this column is bound
            If dgvToUse.Columns(c).DataPropertyName <> "" Then
                ' if some table is mentioned and also
                ' the column in which we are going to paste is NOT read-only
                If tblSource Is Nothing OrElse _
                   Not tblSource.Columns(dgvToUse.Columns(c).DataPropertyName).ReadOnly Then
                    ' if extracted value is empty string
                    If sColValue = "" Then
                        ' then paste the DBNULL.value
                        rowEdit(dgvToUse.Columns(c).DataPropertyName) = DBNull.Value
                    Else ' else it means some value is mentioned
                        ' then paste that value
                        rowEdit(dgvToUse.Columns(c).DataPropertyName) = sColValue
                    End If
                End If
            End If
            ' increase the column count
            c += 1
            ' if reached at last column then stop setting values in columns
            If c >= dgvToUse.Columns.Count Then Exit For
        Next ' next column
    
        ' ok row edit is complete so end it
        bsDataGridView.EndEdit()
    Next 'next line
End Sub

Usage:

Private Sub Paste()
    Try
        If Not Me.DataGridView1.IsCurrentCellInEditMode Then
            Call PasteInBoundDataGridView(Me.DataGridView1, Me.BindingSource1, Me.DataSet1.DataTable1)
        End If
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Paste")
    End Try
End Sub

See Also:
DataGridView Control FAQs and Quick Tips