DataGridView Copy Paste option like Excel

Question

_{Monday, January 26, 2015 2:05 PM}

Need help in DataGridView Copy Paste option like Excel.

Manually if i Copy in DataGridView Selecting Multiple Cells and Paste it in Multiple Cells not working

Input :

Output Required:

Need the Output in the above Format...Thank You

All replies (5)

_{Monday, January 26, 2015 3:39 PM ✅Answered}

Paste is not working properly.

Private Sub PasteClipboard()

        Dim s As String
        Try


            s = Clipboard.GetText()
            Dim i, ii As Integer

            Dim tArr() As String = s.Split(ControlChars.NewLine)
            Dim arT() As String
            Dim cc, iRow, iCol As Integer

            iRow = DataGridView1.SelectedCells(0).RowIndex
            iCol = DataGridView1.SelectedCells(0).ColumnIndex
            For i = 0 To tArr.Length - 1
                If tArr(i) <> "" Then
                    arT = tArr(i).Split(vbTab)
                    cc = iCol
                    For ii = 0 To arT.Length - 1
                        If cc > DataGridView1.ColumnCount - 1 Then Exit For
                        If iRow > DataGridView1.Rows.Count - 1 Then Exit Sub
                        With DataGridView1.Item(cc, iRow)
                            .Value = arT(ii).TrimStart

                        End With
                        cc = cc + 1
                    Next
                    iRow = iRow + 1
                End If

            Next

        Catch ex As Exception
            MsgBox("Please redo Copy and Click on cell")
        End Try
    End Sub

Output Required : After Selecting the Cells and Paste

That's not how copy and paste works.

If you copy one selected row then you paste one selected row, if you copy multiple selected rows then you past multiple selected rows. Anything other than that is custom for copy and paste.

Therefore paste is working properly, normally, as it should. Just not magically like you want it to because no code is written for that. And even if it was written it would only work with another DGV on your Form most likely if it could work at all.

La vida loca

_{Wednesday, January 28, 2015 10:40 AM ✅Answered | 1 vote}

This does what you ask for

Private Sub DataGridView1_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        If DataGridView1.SelectedCells.Count > 0 Then
            DataGridView1.ContextMenuStrip = ContextMenuStrip1
        End If
    End Sub

    Private Sub CutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CutToolStripMenuItem.Click
        CopyToClipboard()
        For counter As Integer = 0 To DataGridView1.SelectedCells.Count - 1
            DataGridView1.SelectedCells(counter).Value = String.Empty
        Next
    End Sub

    Private Sub CopyToClipboard()
        Dim dataObj As DataObject = DataGridView1.GetClipboardContent
        If Not IsNothing(dataObj) Then
            Clipboard.SetDataObject(dataObj)
        End If
    End Sub

    Private Sub CopyToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CopyToolStripMenuItem.Click
        CopyToClipboard()
    End Sub

    Private Sub PasteToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles PasteToolStripMenuItem.Click
        PasteClipboardValue()
    End Sub

    Private Sub PasteClipboardValue()
        If DataGridView1.SelectedCells.Count = 0 Then
            MessageBox.Show("No Cell selected", "Paste")
            Exit Sub
        End If

        Dim StartingCell As DataGridViewCell = GetStartingCell(DataGridView1)
        Dim rowCount = DataGridView1.SelectedCells.OfType(Of DataGridViewCell)().Select(Function(x) x.RowIndex).Distinct().Count()
        Dim cbvalue As Dictionary(Of Integer, Dictionary(Of Integer, String)) = ClipboardValues(Clipboard.GetText)
        Dim repeat As Integer = 0
        If rowCount > cbvalue.Keys.Count Then
            If rowCount Mod cbvalue.Keys.Count <> 0 Then
                MessageBox.Show("Selected destination doesn't match")
                Exit Sub
            Else
                repeat = CInt(rowCount / cbvalue.Keys.Count)
            End If
        End If


        Dim irowindex = StartingCell.RowIndex
        For x As Integer = 1 To repeat
            For Each rowkey As Integer In cbvalue.Keys
                Dim icolindex As Integer = StartingCell.ColumnIndex
                For Each cellkey As Integer In cbvalue(rowkey).Keys
                    If icolindex <= DataGridView1.Columns.Count - 1 And irowindex <= DataGridView1.Rows.Count - 1 Then
                        Dim cell As DataGridViewCell = DataGridView1(icolindex, irowindex)
                        cell.Value = cbvalue(rowkey)(cellkey)
                    End If
                    icolindex += 1
                Next
                irowindex += 1
            Next
        Next

    End Sub

    Private Function GetStartingCell(dgView As DataGridView) As DataGridViewCell
        If dgView.SelectedCells.Count = 0 Then Return Nothing

        Dim rowIndex As Integer = dgView.Rows.Count - 1
        Dim ColIndex As Integer = dgView.Columns.Count - 1


        For Each dgvcell As DataGridViewCell In dgView.SelectedCells

            If dgvcell.RowIndex < rowIndex Then rowIndex = dgvcell.RowIndex
            If dgvcell.ColumnIndex < ColIndex Then ColIndex = dgvcell.ColumnIndex
        Next

        Return dgView(ColIndex, rowIndex)
    End Function


    Private Function ClipboardValues(clipboardvalue As String) As Dictionary(Of Integer, Dictionary(Of Integer, String))
        Dim lines() As String = clipboardvalue.Split(CChar(Environment.NewLine))
        Dim copyValues As Dictionary(Of Integer, Dictionary(Of Integer, String)) = New Dictionary(Of Integer, Dictionary(Of Integer, String))
        For i As Integer = 0 To lines.Length - 1
            copyValues.Item(i) = New Dictionary(Of Integer, String)
            Dim linecontent() As String = lines(i).Split(ChrW(Keys.Tab))
            If linecontent.Length = 0 Then
                copyValues(i)(0) = String.Empty
            Else
                For j As Integer = 0 To linecontent.Length - 1
                    copyValues(i)(j) = linecontent(j)
                Next
            End If
        Next
        Return copyValues
    End Function

_{Monday, January 26, 2015 2:15 PM}

Pretty well explained by microsoft here

https://msdn.microsoft.com/en-us/library/dec5efh4(v=vs.90).aspx

_{Monday, January 26, 2015 3:08 PM}

Paste is not working properly.

Private Sub PasteClipboard()

        Dim s As String
        Try


            s = Clipboard.GetText()
            Dim i, ii As Integer

            Dim tArr() As String = s.Split(ControlChars.NewLine)
            Dim arT() As String
            Dim cc, iRow, iCol As Integer

            iRow = DataGridView1.SelectedCells(0).RowIndex
            iCol = DataGridView1.SelectedCells(0).ColumnIndex
            For i = 0 To tArr.Length - 1
                If tArr(i) <> "" Then
                    arT = tArr(i).Split(vbTab)
                    cc = iCol
                    For ii = 0 To arT.Length - 1
                        If cc > DataGridView1.ColumnCount - 1 Then Exit For
                        If iRow > DataGridView1.Rows.Count - 1 Then Exit Sub
                        With DataGridView1.Item(cc, iRow)
                            .Value = arT(ii).TrimStart

                        End With
                        cc = cc + 1
                    Next
                    iRow = iRow + 1
                End If

            Next

        Catch ex As Exception
            MsgBox("Please redo Copy and Click on cell")
        End Try
    End Sub

Output Required : After Selecting the Cells and Paste

_{Tuesday, February 3, 2015 12:53 PM}

This does what you ask for

Private Sub DataGridView1_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        If DataGridView1.SelectedCells.Count > 0 Then
            DataGridView1.ContextMenuStrip = ContextMenuStrip1
        End If
    End Sub

    Private Sub CutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CutToolStripMenuItem.Click
        CopyToClipboard()
        For counter As Integer = 0 To DataGridView1.SelectedCells.Count - 1
            DataGridView1.SelectedCells(counter).Value = String.Empty
        Next
    End Sub

    Private Sub CopyToClipboard()
        Dim dataObj As DataObject = DataGridView1.GetClipboardContent
        If Not IsNothing(dataObj) Then
            Clipboard.SetDataObject(dataObj)
        End If
    End Sub

    Private Sub CopyToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CopyToolStripMenuItem.Click
        CopyToClipboard()
    End Sub

    Private Sub PasteToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles PasteToolStripMenuItem.Click
        PasteClipboardValue()
    End Sub

    Private Sub PasteClipboardValue()
        If DataGridView1.SelectedCells.Count = 0 Then
            MessageBox.Show("No Cell selected", "Paste")
            Exit Sub
        End If

        Dim StartingCell As DataGridViewCell = GetStartingCell(DataGridView1)
        Dim rowCount = DataGridView1.SelectedCells.OfType(Of DataGridViewCell)().Select(Function(x) x.RowIndex).Distinct().Count()
        Dim cbvalue As Dictionary(Of Integer, Dictionary(Of Integer, String)) = ClipboardValues(Clipboard.GetText)
        Dim repeat As Integer = 0
        If rowCount > cbvalue.Keys.Count Then
            If rowCount Mod cbvalue.Keys.Count <> 0 Then
                MessageBox.Show("Selected destination doesn't match")
                Exit Sub
            Else
                repeat = CInt(rowCount / cbvalue.Keys.Count)
            End If
        End If


        Dim irowindex = StartingCell.RowIndex
        For x As Integer = 1 To repeat
            For Each rowkey As Integer In cbvalue.Keys
                Dim icolindex As Integer = StartingCell.ColumnIndex
                For Each cellkey As Integer In cbvalue(rowkey).Keys
                    If icolindex <= DataGridView1.Columns.Count - 1 And irowindex <= DataGridView1.Rows.Count - 1 Then
                        Dim cell As DataGridViewCell = DataGridView1(icolindex, irowindex)
                        cell.Value = cbvalue(rowkey)(cellkey)
                    End If
                    icolindex += 1
                Next
                irowindex += 1
            Next
        Next

    End Sub

    Private Function GetStartingCell(dgView As DataGridView) As DataGridViewCell
        If dgView.SelectedCells.Count = 0 Then Return Nothing

        Dim rowIndex As Integer = dgView.Rows.Count - 1
        Dim ColIndex As Integer = dgView.Columns.Count - 1


        For Each dgvcell As DataGridViewCell In dgView.SelectedCells

            If dgvcell.RowIndex < rowIndex Then rowIndex = dgvcell.RowIndex
            If dgvcell.ColumnIndex < ColIndex Then ColIndex = dgvcell.ColumnIndex
        Next

        Return dgView(ColIndex, rowIndex)
    End Function


    Private Function ClipboardValues(clipboardvalue As String) As Dictionary(Of Integer, Dictionary(Of Integer, String))
        Dim lines() As String = clipboardvalue.Split(CChar(Environment.NewLine))
        Dim copyValues As Dictionary(Of Integer, Dictionary(Of Integer, String)) = New Dictionary(Of Integer, Dictionary(Of Integer, String))
        For i As Integer = 0 To lines.Length - 1
            copyValues.Item(i) = New Dictionary(Of Integer, String)
            Dim linecontent() As String = lines(i).Split(ChrW(Keys.Tab))
            If linecontent.Length = 0 Then
                copyValues(i)(0) = String.Empty
            Else
                For j As Integer = 0 To linecontent.Length - 1
                    copyValues(i)(j) = linecontent(j)
                Next
            End If
        Next
        Return copyValues
    End Function

Yep...Thanx bro. this code is working fine

Last updated on 2015-01-26

Share via

DataGridView Copy Paste option like Excel

Question

All replies (5)

Additional resources