Excel to Clipboard to DataGridView

[bretddog]

Hi,

I need some advice on the following:

1. Copy Excel 2D range to the clipboard
2. Paste clipboard data into the datagridview in same layout as Excel
3. Populate an array of objects with this data, where each row is sent to one object, with the value in each column/cell as a member of the object class.

Example:

Public Class Point
Public X as Integer
Public Y as Integer
End Class

Grid would look like:
X1 Y1
X2 Y2
X3 Y3

Number of columns will be fixed, however rows will vary. Would anyone have some code snippets that could help me forward with this?

I guess I can get the clipboard content as a string, and then split it up, but are there more clever ways to go about this?

Also, some excel cells may be empty.. still it must keep its location..

 

[MattP]

Is there a specific reason you need to use the clipboard?

I would recommend loading the data into a DataSet and working with it from there.

This link should get you started on the right track: CodeProject: Excel Connectivity in VB.NET. Free source code and programming help

 

[bretddog]

Is there a specific reason you need to use the clipboard?

I would recommend loading the data into a DataSet and working with it from there.

This link should get you started on the right track: CodeProject: Excel Connectivity in VB.NET. Free source code and programming help

Actually I want to be able to paste data from various sources, including CSV file and excel file, but both existing files as well as recently opened, and unsaved files. Also the number of rows will vary.

For this I believe I need to go by the clipboard..
Dataset is probably a good way to go, if I can load the clipboard into a data set.. ?

 

[bretddog]

I guess this is pretty basic, but it took me some time.. At least it works with copy from excel.. Should probably extend it with some format checks too.

    'Paste copied cells from clipboard (Excel) to DataGridView with a fixed number of columns
    Private Sub btnPasteFromExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPasteFromExcel.Click
        Dim s As String = Clipboard.GetText()                   'Get clipboard data as a string
        Dim rows() As String = s.Split(ControlChars.NewLine)    'Split into rows
        Dim i, j As Integer                                     'Counters

        'Delete current grid
        For i = 0 To DataGridView2.Rows.Count - 1
            DataGridView2.Rows.Remove(DataGridView2.Rows(i))
        Next

        Try
            For i = 0 To rows.Length - 1
                'Add new row
                If DataGridView2.Rows.Count < rows.Length Then
                    DataGridView2.Rows.Add()
                End If
                'Split row into cells
                Dim bufferCell() As String = rows(i).Split(ControlChars.Tab)
                'Copy to grid, cell by cell
                For j = 0 To bufferCell.Length - 1
                    'Remove line feed characters 
                    If bufferCell(j).ToString.Contains(ControlChars.Lf) Then
                        bufferCell(j) = bufferCell(j).ToString.Replace(ControlChars.Lf, "")
                    End If
                    DataGridView2.Item(j, i).Value = bufferCell(j)
                Next
            Next
        Catch
            MessageBox.Show("INPUT ERROR" & ControlChars.NewLine & _
            "Number of columns shall be max " & DataGridView2.ColumnCount)
        End Try
    End Sub