Copy Paste and Insert in Datagridview (Winforms)

• Download source - 2.3 KB

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 4^th 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 0^th column, soon I will try to update it paste from the selected column like Excel.

History

• 1.1 Copy paste insert datagridview