Importing an excel file to existing DataGridView in VB.Net

Question

I have a problem with my below code which freezes and does nothing while running.

INFRASTRUCTURE: Visual Studio 2017 .NET Framework 4.7.2 OS: Windows 7

GOAL: I have a DataGridView which has 3 columns, I'm listing some information in this DataGridView from another source to fill first and second row.

The first row is the parameter name and the second one is this parameter's current value.

The third column is not filling in this process, I will fill that 3rd column from my database Excel file to compare the parameter's current value with the database value stored in Excel. This is where my problems start.

I'm trying to use below code for filling DataGridView parameter value from Excel sheet which I'm using a database;

Some parameters not stored in an Excel sheet so in fact, I need a function like vlookup to map data with the parameter name.

In excel A column is my parameter name and B column is the parameter's database value.

I'm trying to import this excel and trying to match parameter names in DataGridView and Excel if the parameter name is same it should writing Excel parameter value to a 3rd column in DataGridView.

Public Class BuildImportExcel
    Public Shared Sub NewMethod(ByVal dgv As DataGridView)
        Dim ofd As OpenFileDialog = New OpenFileDialog With {
            .Filter = "Excel |*.xlsx",
            .Title = "Import Excel File"
        }
        ofd.ShowDialog()

        Try
            If ofd.FileName IsNot "" Then
                Dim xlApp As New Excel.Application
                If xlApp Is Nothing Then
                    MessageBox.Show("Excel is not properly installed!")
                Else
                    Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open(ofd.FileName)
                    Dim xlSheet As Excel.Worksheet = CType(xlBook.ActiveSheet, Excel.Worksheet)

                    For i = 0 To dgv.Rows.Count
                        If dgv.Rows(i).Cells(0).Value IsNot "" Then
                            Dim look As Boolean = True
                            Dim found As Boolean = False
                            Dim rowLook As Integer = 2
                            Dim rowFound As Integer = 0
                            While look = True
                                If xlSheet.Range("A" & rowLook).Value IsNot "" Then
                                    If xlSheet.Range("A" & rowLook).Text Is dgv.Rows(i).Cells(0).Value Then
                                        found = True
                                        rowFound = rowLook
                                    End If
                                Else
                                    look = False
                                End If
                                rowLook = rowLook + 1
                            End While
                            If found = True Then
                                dgv.Rows(i).Cells(2).Value = xlSheet.Range("B" & rowFound).Text
                            End If
                        End If
                    Next
                    xlApp.Quit()
                    Release(xlSheet)
                    Release(xlBook)
                    Release(xlApp)
                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Shared Sub Release(ByVal sender As Object)
        Try
            If sender IsNot Nothing Then
                Marshal.ReleaseComObject(sender)
                sender = Nothing
            End If
        Catch ex As Exception
            sender = Nothing
        End Try
    End Sub

End Class

But the problem is it's not working freezing I thought parameter list about 200 rows so it causes to freezing and try it with small portions like 5 parameters and still same. Seems something wrong and I couldn't find it.

Also is it logic way to match them with that method or do you suggest anything like OLEDB connection?

EDIT: I turn off Option Strict and then change IsNot to operator <> and then it start to work but i'd like to use Option Strict On how can i handle this operators?

Please review Comparing Objects. It is good that you want Option Strict On as the strict typing that it enforces prevents many logic errors. Also, be aware that the Excel Range.Text property returns the as displayed (formatted) text representation of the Range's value. If the cell width is insufficient to to display the value, the Text property will return either an empty string or a string of "#" characters that fit the width. — TnTinMn

JohnG JohnG · Accepted Answer · 2019-02-27T05:53:29

You are making this more difficult than it has to be. A questionable and problematic area is the While look = True loop. This assumes a lot and, in my test, will cause the code to freeze often. The main problem here is that the code is (somewhat) looping through the rows from the Excel file. The issue here is that you do not KNOW how may rows there are! There is no checking for this and the code will fail on the line…

  If xlSheet.Range("A" & rowLook).Value IsNot "" Then

When you reach the bottom of the given Excel file and it never finds a match.

Another issue is the line…

If xlSheet.Range("A" & rowLook).Text Is dgv.Rows(i).Cells(0).Value Then

This is ALWAYS going to fail and will never be true. My understanding…

The Is operator determines if two object references refer to the same object. However, it does not perform value comparisons.

Given this, I recommend you simplify things a bit. Most important are the loops through the Excel worksheet which is “expensive” and if there is a lot of rows, you may have a performance problem. When I say a lot of rows, I mean tens of thousands of rows…. 200 rows should be fine.

It would un-complicate things if we could tell how many rows from the worksheet are returned from….

 Dim xlSheet As Excel.Worksheet = CType(xlBook.ActiveSheet, Excel.Worksheet)

This is basically an Excel Range, and it is from this Range that we can get the number of rows in this excel range with…

   Dim totalExcelRows = xlSheet.UsedRange.Rows.Count

Now, instead of the complicated and problematic While loop, we can replace it with a simple for loop. This will eliminate some variables and will keep the loop index in row range in the excel file.

I hope this makes sense… below is an example of what is described above.

Public Shared Sub NewMethod(ByVal dgv As DataGridView)
    Dim ofd As OpenFileDialog = New OpenFileDialog With {
        .Filter = "Excel |*.xlsx",
        .Title = "Import Excel File",
        .InitialDirectory = "D:\\Test\\ExcelFiles"
    }
    ofd.ShowDialog()
    Try
        If ofd.FileName IsNot "" Then
            Dim xlApp As New Excel.Application
            If xlApp Is Nothing Then
                MessageBox.Show("Excel is not properly installed!")
            Else
                Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open(ofd.FileName)
                Dim xlSheet As Excel.Worksheet = CType(xlBook.ActiveSheet, Excel.Worksheet)
                Dim totalExcelRows = xlSheet.UsedRange.Rows.Count
                For i = 0 To dgv.Rows.Count
                    If dgv.Rows(i).Cells(0).Value IsNot Nothing Then
                        For excelRow = 1 To totalExcelRows
                            If xlSheet.Range("A" & excelRow).Text.ToString() = dgv.Rows(i).Cells(0).Value.ToString() Then
                                dgv.Rows(i).Cells(2).Value = xlSheet.Range("B" & excelRow).Text
                                Exit For
                            End If
                        Next
                    Else
                        Exit For
                    End If
                Next
                xlApp.Quit()
                Marshal.ReleaseComObject(xlSheet)
                Marshal.ReleaseComObject(xlBook)
                Marshal.ReleaseComObject(xlApp)
            End If
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Importing an excel file to existing DataGridView in VB.Net

1 Answers