Click here to Skip to main content
Click here to Skip to main content

Copy Paste and Insert in Datagridview (Winforms)

, 28 May 2014
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Thava Rajan
Software Developer (Senior)
India India
No Biography provided
Follow on   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140821.2 | Last Updated 29 May 2014
Article Copyright 2014 by Thava Rajan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid