Paste from Excel to DataGridView

Question

I have this code that helps me to paste data from an Excel file to a Datagridview:

If e.Control AndAlso e.KeyCode = Keys.V Then
 DGVLookUP.Rows.Clear()
 DGVLookUP.Columns.Clear()
 Try
 Dim Generar As Boolean = False
 Dim N As Integer
 For Each line As String In Clipboard.GetText.Split(vbNewLine)
 If Not line.Trim.ToString = "" Then
 Dim item() As String = line.Trim.Split(vbTab)
 If Generar = False Then
 For C = 0 To UBound(item)
 DGVLookUP.Columns.Add("C" + CStr(N), CStr(N))
 N += 1
 Next
 Generar = True
 End If
 Me.DGVLookUP.Rows.Add(item)
 End If
 Next
 Catch ex As Exception
 MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
 End Try
End If

I am using VB.Net and I am pasting over 3k rows and it takes me between 5-10 mins to paste the info. I would like to optimize my code or if there is another way better to paste the data.

Answer 1

Before we look at how to improve performance, lets start by polishing your code.

---

Polishing

Dim Generar As Boolean = False
Dim N As Integer

Despite the fact that the .NET naming guidelines doesn't apply for internal and private fields the general consensus is to use English, descriptive and lower Camel case names.

I've renamed the variable and changed the initial value to True as "generate" (IMO) is referring to "columns".

The variable N is useless and should be removed as you already got a variable named c which you could use.

Dim generate As Boolean = True

---

For Each line As String In Clipboard.GetText.Split(vbNewLine)
 If Not line.Trim.ToString = "" Then
 Dim item() As String = line.Trim.Split(vbTab)

The first line "breaks" my compiler with the error BC30512 and correctly state that "Option Strict On disallows implicit conversions from 'String' to 'Char'". You should always make sure that the strict compiler option is set to On. I'm tempted to say that this is the most important thing you'll learn by reading this review. Oh, wait! I just did! ;)

The constants vbNewLine and vbTab (defined as String) are remnants from the past and should be regarded as obsolete. Many of the methods and classes inside the Microsoft.VisualBasic assembly were added to make the transition from VB6 to VB.NET more convenient. If there's a .NET equivalent, use it! However, in this case, we need a Char array so we create a few Char constants.

The Split method has an overload which allows us to pass an option to remove empty entries. We'll use this and remove the second line. But please note that a better alternative to your boolean expression would be to use the static method String.IsNullOrWhiteSpace. I've also renamed the variable item to values.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)
For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)
 Dim values() As String = line.Trim.Split({tab})

---

For C = 0 To UBound(item)
 DGVLookUP.Columns.Add("C" + CStr(N), CStr(N))

Again, you're using an obsolete method, UBound. Just read the Length property of the array. (If you need to read the length of a give dimension, invoke GetLength.)

It's advisable to use & for string concatenation. And you ought to take advantage of all of the static methods the string type has to offer. I've also renamed the variable C to cell.

For cell As Integer = 0 To (values.Length - 1)
 DGVLookUP.Columns.Add(String.Format("C{0}", cell), cell.ToString())

---

Performance

The complete code at this point looks like below. The great disadvantage with this code is that you add a new row in each iteration. Each call to Add will queue a bunch of messages all of which needs to be processed by the window. Do we really need to repaint the control 3000 times? The answer is of course NO. So we need to fix this.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)
Dim generate As Boolean = True
For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)
 Dim values() As String = line.Trim.Split({tab})
 If (generate) Then
 For cell As Integer = 0 To (values.Length - 1)
 Me.DGVLookUP.Columns.Add(String.Format("C{0}", cell), cell.ToString())
 Next
 generate = False
 End If
 Me.DGVLookUP.Rows.Add(values)
Next

---

A much better option would be to first read the data into a data structure (we'll be using a DataTable) and then update the grid.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)
Dim table As New DataTable()
Dim generate As Boolean = True
table.BeginLoadData()
For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)
 Dim values() As String = line.Trim.Split({tab})
 If (generate) Then
 For cell As Integer = 0 To (values.Length - 1)
 table.Columns.Add(String.Format("C{0}", cell))
 Next
 generate = False
 End If
 table.Rows.Add(values)
Next
table.EndLoadData()
Me.DGVLookUP.DataSource = table

---

To further increase the performance we'll need to write our own parser and scrap the split method. You might need to fine tune this if the data inside the excel cell contains a line feed and/or a carriage return.

Const tab As Char = ChrW(9)
Const lineFeed As Char = ChrW(10)
Const carriageReturn As Char = ChrW(13)
Dim data As String = Clipboard.GetText()
Dim table As New DataTable()
Dim generate As Boolean = True
Dim cursor As Integer = 0
Dim position As Integer = 0
Dim cell As Integer = 0
Dim row As DataRow = Nothing
table.BeginLoadData()
While (cursor < data.Length)
 Select Case data.Chars(cursor)
 Case tab, carriageReturn
 If (generate) Then
 table.Columns.Add(String.Format("C{0}", cell), GetType(String)).Caption = cell.ToString()
 End If
 If (cell = 0) Then
 row = table.NewRow()
 End If
 row.Item(cell) = data.Substring(position, (cursor - position))
 cell += 1
 cursor += 1
 position = cursor
 Continue While
 Case lineFeed
 If (Not row Is Nothing) Then
 table.Rows.Add(row)
 row = Nothing
 End If
 cell = 0
 cursor += 1
 position = cursor
 generate = False
 Continue While
 Case Else
 cursor += 1
 Continue While
 End Select
End While
table.EndLoadData()
Me.DGVLookUP.DataSource = table

Test

I've tested this with 3000 rows x 10 columns in release build and as you can see there's a distinct performance boost.

Paste #1: count= 10, average= 3181.4678 ms
Paste #2: count= 10, average= 1483.8070 ms
Paste #3: count= 10, average= 1058.3944 ms

Option Strict On
Imports System.Text
Public Class Form1
 'Copy and paste the text below into each cell of the range A1:J3000
 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas in convallis nunc, nec gravida sem. Praesent malesuada dignissim arcu, eu euismod enim. In non neque ut augue sodales dictum eu eget dui. Pellentesque dolor diam, vestibulum sit amet pulvinar et, sollicitudin nec mauris. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Cras sollicitudin metus vitae arcu faucibus, at posuere mauris dictum. Pellentesque in varius sem. Pellentesque molestie varius metus, a dignissim purus vulputate a. Cras venenatis orci et urna efficitur, vel fermentum dui eleifend. Pellentesque et malesuada mauris. Nulla eget nunc et turpis dictum pretium. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam porttitor diam nunc, ut dapibus felis porta at. Nunc non ante sit amet ipsum maximus imperdiet at vitae metus. Aliquam ut lacus in elit laoreet sodales et in eros.
 Private Class TestInfo
 Public Name As String
 Public Method As Action
 Public ReadOnly Results As New List(Of Double)
 End Class
 Private WithEvents input As New DataGridView With
 {
 .Dock = DockStyle.Fill
 }
 Private WithEvents output As New TextBox With
 {
 .Dock = DockStyle.Top,
 .Multiline = True,
 .Height = 100,
 .ReadOnly = True,
 .ScrollBars = ScrollBars.None,
 .Text = "Waiting for input..."
 }
 Private tests As TestInfo() = New TestInfo() _
 {
 New TestInfo With {.Name = "Paste #1", .Method = AddressOf Me.Paste1},
 New TestInfo With {.Name = "Paste #2", .Method = AddressOf Me.Paste2},
 New TestInfo With {.Name = "Paste #3", .Method = AddressOf Me.Paste3}
 }
 Private switch As Integer = 0
 Public Sub New()
 Me.Size = New Size(800, 600)
 Me.Text = "Test"
 Me.KeyPreview = True
 Me.Controls.AddRange({Me.input, Me.output})
 End Sub
 Private Sub HandleKeyDown(sender As Object, e As KeyEventArgs) Handles Me.KeyDown
 If (e.Control AndAlso (e.KeyCode = Keys.V)) Then
 If (Me.input.DataSource Is Nothing) Then
 Me.input.Rows.Clear()
 Me.input.Columns.Clear()
 Else
 Me.input.DataSource = Nothing
 End If
 Dim watch As New Stopwatch()
 Dim info As TestInfo = Me.tests(Me.switch)
 watch.Start()
 info.Method.Invoke()
 watch.Stop()
 info.Results.Add(watch.Elapsed.TotalMilliseconds)
 Dim builder As New StringBuilder()
 For Each info In Me.tests
 If (builder.Length > 0) Then
 builder.AppendLine()
 End If
 Dim average As Double = 0
 If (info.Results.Count > 0) Then
 average = info.Results.Average()
 End If
 builder.Append(String.Format("{0}: count= {1}, average= {2:F4} ms", info.Name, info.Results.Count, average))
 Next
 Me.output.Text = builder.ToString()
 Me.switch += 1
 If (Me.switch = Me.tests.Count) Then
 Me.switch = 0
 End If
 End If
 End Sub
 Private Sub Paste1()
 Const tab As Char = ChrW(9)
 Const lineFeed As Char = ChrW(10)
 Const carriageReturn As Char = ChrW(13)
 Dim generate As Boolean = True
 For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)
 Dim values() As String = line.Trim.Split({tab})
 If (generate) Then
 For cell As Integer = 0 To (values.Length - 1)
 Me.input.Columns.Add(String.Format("C{0}", cell), cell.ToString())
 Next
 generate = False
 End If
 Me.input.Rows.Add(values)
 Next
 End Sub
 Private Sub Paste2()
 Const tab As Char = ChrW(9)
 Const lineFeed As Char = ChrW(10)
 Const carriageReturn As Char = ChrW(13)
 Dim table As New DataTable()
 Dim generate As Boolean = True
 table.BeginLoadData()
 For Each line As String In Clipboard.GetText.Split({lineFeed, carriageReturn}, StringSplitOptions.RemoveEmptyEntries)
 Dim values() As String = line.Trim.Split({tab})
 If (generate) Then
 For cell As Integer = 0 To (values.Length - 1)
 table.Columns.Add(String.Format("C{0}", cell))
 Next
 generate = False
 End If
 table.Rows.Add(values)
 Next
 table.EndLoadData()
 Me.input.DataSource = table
 End Sub
 Private Sub Paste3()
 Const tab As Char = ChrW(9)
 Const lineFeed As Char = ChrW(10)
 Const carriageReturn As Char = ChrW(13)
 Dim data As String = Clipboard.GetText()
 Dim table As New DataTable()
 Dim generate As Boolean = True
 Dim cursor As Integer = 0
 Dim position As Integer = 0
 Dim cell As Integer = 0
 Dim row As DataRow = Nothing
 table.BeginLoadData()
 While (cursor < data.Length)
 Select Case data.Chars(cursor)
 Case tab, carriageReturn
 If (generate) Then
 table.Columns.Add(String.Format("C{0}", cell), GetType(String)).Caption = cell.ToString()
 End If
 If (cell = 0) Then
 row = table.NewRow()
 End If
 row.Item(cell) = data.Substring(position, (cursor - position))
 cell += 1
 cursor += 1
 position = cursor
 Continue While
 Case lineFeed
 If (Not row Is Nothing) Then
 table.Rows.Add(row)
 row = Nothing
 End If
 cell = 0
 cursor += 1
 position = cursor
 generate = False
 Continue While
 Case Else
 cursor += 1
 Continue While
 End Select
 End While
 table.EndLoadData()
 Me.input.DataSource = table
 End Sub
End Class

• Mathieu Guindon: Nitpick: PascalCase and camelCase ;-)
• Bjørn-Roger Kringsjå: @Mat's I know. But ever since I read the wiki page, which states that "Camel case may start with a capital or, especially in programming languages, with a lowercase letter", I started to include"lower". Thanks for the vote BTW. Highly appreciated.