Copy Paste and Insert in Datagridview (Winforms)
5.00/5 (4 votes)
Copy and insert data at any postion in your datagridview
Introduction
Well, CodeProject already has some copy paste Datagridview articles, then why a new one? You may ask this as a question, the answer is, in those articles there are no insert options like in Excel, but this article would show how to do it if your grid is data bounded or objects bounded by Binding source.
Background
Before getting into the tip, please get some views on this link Activator.CreateInstance because some of us didn't know how to use it or even didn't hear about it.
Let's Start
It's time to work out on the copy and paste when a gridview is binded with Datatable or Dataset or Dataview or BindingSource, the workaround is as follows:
Data From Excel
In this entire tip, consider the data is copied from Excel, and we are going to paste it into a DatagridView.
Collect Data from ClipBoard
Get the data from clipboard using the following line:
Dim ClipBoardData As String = Clipboard.GetText()
Since we are going to process only the Text data, I use GetText. You can implement any logic here, an array of classes or integer or string whatever.
Now it's time to split the data. Let us consider our Text data row separators are linefeed (in most of the cases, it is perfect work).
Dim lines As String() = ClipBoardData.Split(New Char(1) _
{ControlChars.Cr, ControlChars.Lf}, StringSplitOptions.RemoveEmptyEntries)
Well, I am already familiar with split function what is special with that, nothing but a normal split well when we insert data we don't want to insert the empty lines, if we still need to insert the empty line removing the split options.
Pasting Data
Is the above code enough to copy and paste the data in our grid? Well NO, we need one more step, we need to parse the data as cell values, for that, we need further split the lines into Tab delimited.
Dim Datas As String() = line.Split(ControlChars.Tab)
'Fill the data by looping through the values
For j = 0 To Math.Min(Datas.Length - 1, Dgv.Columns.GetColumnCount(DataGridViewElementStates.Visible) - 1)
Dgv.Item(j, RowIndex).Value = Datas(j)
Next
In the above code, line is a string variable got from the lines array.
What about Inserting?
Well, now how can we insert it? Simply add the data to the underlying datasource of the Datagridview, let's say if I want to insert a record in the 4th position, the following syntax is helpful:
Dim Tbl AS Datatable
obj=tbl.NewRow
tbl.Rows.InsertAt(obj, 4)
Now paste the data again in the grid with the same manner of pasting, now you may ask a question - "it is only for a Datatable, what would I do if it is a dataset or dataview or TypedDataset".
For that, I have just a little tweak:
Dim T As Type
Dim Typename As String
'Get the typename from the binding source
Try
Typename = bs.DataSource.GetType.FullName()
Catch ex As Exception
Typename = ""
End Try
T = Type.GetType(Typename)
Since the Typed Dataset has a different type, we need to find the Base type of the Typed Dataset. I use like hits, now using the TypeName we are going to handle this scenario:
If Typename.Contains("View") Then 'For DataView
tbl = CType(.DataSource, DataView).Table
ElseIf Typename.Contains("DataSet") Then 'For DataSet
tbl = CType(.DataSource, DataSet).Tables(Dgv.DataMember)
ElseIf Typename.Contains("Table") OrElse _
T.BaseType.FullName.Contains("DataRow") Then 'For DataTable or TypeDataset
tbl = CType(.DataSource, DataTable)
Else
If bs.AllowNew = False Then
MessageBox.Show("Not able to insert new records")
Exit Sub
End If
obj = .AddNew
RowIndex = .Count - 1
End If
'Tbl is nothing then it is not possible to be add a Datarow to underlying Datasource
If tbl IsNot Nothing Then
obj = tbl.NewRow
tbl.Rows.InsertAt(obj, RowIndex)
End If
What is Different?
Now you may ask, why do we need to know the detail for Activator.CreateInstance? The answer is sometimes we bind the datagrid using list of objects at that time, paste and insert a clipboard data is almost impossible. To avoid that, I made a little workaround, we have already got the Type of the class in T we can create an object from that type using Activator.CreateInstance.
'if it is an object source
'since we are uing BindingSource if we insert a object in the binding source it will
'Automatically insert a row in the Grid
With CType(Dgv.DataSource, BindingSource)
'Activator.CreateInstance create a new object for the given type
.Insert(RowIndex, Activator.CreateInstance(T))
End With
As we are using the Binding source, it is possible to insert an object in a particular row index, what we need to do is create an object of the underlying datasource is the key here. It is accomplished by Activator.CreateInstance.
That's it, we are done. Put some additional logic into it. The final method would be like this:
Sub PasteTextDataFromClipBoard(Dgv As DataGridView, Optional ShowMessage As Boolean = True)
Try
'Get the Data from the clipBoard as Text
Dim ClipBoardData As String = Clipboard.GetText()
If ClipBoardData.Trim.Length = 0 Then
MessageBox.Show("No data to Paste")
End If
'This is where we do our data split logic(i.e) how the data will paste in to the Grid
'since we get the data from from clipboard as text
'we consider each data is separated by Tab and each row is separated by new line character
'Get separate lines
Dim lines As String() = ClipBoardData.Split(New Char(1)
_{ControlChars.Cr, ControlChars.Lf}, StringSplitOptions.RemoveEmptyEntries)
Dim bs As BindingSource
If TypeOf Dgv.DataSource Is BindingSource Then
bs = CType(Dgv.DataSource, BindingSource)
Else
bs = New BindingSource
bs.DataSource = Dgv.DataSource
End If
Dim T As Type
Dim Typename As String
'Get the typename from the binding source
Try
Typename = bs.DataSource.FullName()
Catch ex As Exception
Try
Typename = bs.DataSource(0).GetType.FullName
Catch ex1 As Exception
Try
Typename = bs.DataSource.GetType.FullName
Catch ex2 As Exception
Typename = ""
End Try
End Try
End Try
'Create a Type from the TypeName
If Typename = "" Then
MessageBox.Show("problem in Data type ")
Exit Sub
End If
T = Type.GetType(Typename)
'Get the current row index where we need to paste the data
'The data is pasted from the selected row not from selected column
Dim RowIndex As Integer = Dgv.CurrentCell.RowIndex
Dim ColIndex As Integer = Dgv.CurrentCell.ColumnIndex
Dim Lcount As Integer = lines.Length
Dim overwrite As DialogResult
'Ask the user to overwrite the Existing Data or just paste in as new row
'you may extend or remove this logic here?
If Not (Dgv.CurrentCell.Value Is Nothing Or _
Dgv.CurrentCell.Value.ToString = String.Empty) And ShowMessage Then
overwrite = MessageBox.Show("Are you sure _
to overwrite the current value", Dgv.Parent.Text, MessageBoxButtons.YesNo)
Else
overwrite = Windows.Forms.DialogResult.No
End If
Dim counter As Integer = 0
'Loop through all the pasted lines
While counter < Lcount
'Check whether a new row is needed to insert inside a gridview
If overwrite = Windows.Forms.DialogResult.No OrElse (Dgv.Rows.Count - 1) <= RowIndex Then
'Check whether the data is binded by Datatable or Not able to find the base type
If T Is Nothing OrElse Typename.Contains("System.Data") _
OrElse T.BaseType.FullName.Contains("System.Data") Then
Dim obj As Object
With bs
Dim tbl As DataTable
If Typename.Contains("View") Then 'For DataView
tbl = CType(.DataSource, DataView).Table
ElseIf Typename.Contains("DataSet") Then 'For DataSet
tbl = CType(.DataSource, DataSet).Tables(Dgv.DataMember)
ElseIf Typename.Contains("Table") OrElse _
T.BaseType.FullName.Contains("DataRow") Then 'For DataTable or TypeDataset
tbl = CType(.DataSource, DataTable)
Else
If bs.AllowNew = False Then
MessageBox.Show("Not able to insert new records")
Exit Sub
End If
obj = .AddNew
RowIndex = .Count - 1
End If
'Tbl is nothing then it is not possible to be add a Datarow to underlying Datasource
If tbl IsNot Nothing Then
obj = tbl.NewRow
tbl.Rows.InsertAt(obj, RowIndex)
End If
End With
Else
'if it is an object source
'since we are uing BindingSource if we insert a object in the binding source it will
'Automatically insert a row in the Grid
With CType(Dgv.DataSource, BindingSource)
'Activator.CreateInstance create a new object for the given type
.Insert(RowIndex, Activator.CreateInstance(T))
End With
End If
End If
'choose the Relative line
Dim line As String = lines(counter)
'split Data in a single line and iterate it
Dim Datas As String() = line.Split(ControlChars.Tab)
'Fill the data by looping through the values
For j = 0 To Math.Min(Datas.Length - 1, _
Dgv.Columns.GetColumnCount(DataGridViewElementStates.Visible) - 1)
Dgv.Item(j, RowIndex).Value = Datas(j)
Next
RowIndex += 1
counter += 1
End While
Catch Pasteex As Exception
MessageBox.Show(Pasteex.Message)
End Try
End Sub
Well expect more, download the class file and use it as follows:
Dim BindGrid As New clsDgvCopyPasteEx(DataGridView1)
This will automatically handle your keyboard event and add a contextmenu (if your gridview already has a context menu strip).
Points of Interest
The tricky part is how we create an object and where we need to insert it is the key.
Expecting Future Enhancement
So far, paste operation starts from the 0th column, soon I will try to update it paste from the selected column like Excel.
History
- 1.1 Copy paste insert datagridview