Monday, June 23, 2008

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
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.

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

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

Next 'next line
End Sub

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

See Also:
DataGridView Control FAQs and Quick Tips


Anonymous said...

Fantastic Code. Easy to follow, easy to understand. Right now you are my Einstein. Well done.

Doug said...

I receive an object reference not set to an instance of an object error...

I assume I did something wrong when I bound a button click even to Paste().

How do I properly bind this to a control?