6
\$\begingroup\$

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.

\$\endgroup\$
3
\$\begingroup\$

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
|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Nitpick: PascalCase and camelCase ;-) \$\endgroup\$ – Mathieu Guindon Jan 9 '16 at 17:00
  • \$\begingroup\$ @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. \$\endgroup\$ – Bjørn-Roger Kringsjå Jan 9 '16 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.