How to import excel to datagridview faster using vb.net

Question

Currently I have an excel file with 1500 rows and 9 columns. Every time when my winform loads, I want the records in the excel file to load into a datagridview. I was using Microsoft office interop to import the records to datagridview but it's too slow.

So my question is, what other ways can I speed up the process? and how can I do that?

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Open("C:\Install\Data.xlsx")
xlWorkSheet = xlWorkBook.Sheets("sheet1")

Dim countRows As Integer = xlWorkSheet.UsedRange.Rows.Count()

For i As Integer = 2 To countRows
    newVersionRow = VersionDBDataSet.IEVersion.NewIEVersionRow()
    newVersionRow.PC_Name = xlWorkSheet.Cells(i, 1).value.ToString()
    newVersionRow.PC_Status = xlWorkSheet.Cells(i, 2).value.ToString()
    newVersionRow.svcKBNumber = xlWorkSheet.Cells(i, 3).value.ToString()
    newVersionRow.svcVersion = xlWorkSheet.Cells(i, 4).value.ToString()
    newVersionRow.Last_Updated = DateTime.Parse(xlWorkSheet.Cells(i, 5).value.ToString())
    newVersionRow.Patches = Integer.Parse(xlWorkSheet.Cells(i, 6).value)
    newVersionRow.Uptime = Integer.Parse(xlWorkSheet.Cells(i, 7).value)
    newVersionRow.Count = Integer.Parse(xlWorkSheet.Cells(i, 8).value)
    newVersionRow.Offline = Integer.Parse(xlWorkSheet.Cells(i, 9).value)

    VersionDBDataSet.IEVersion.Rows.Add(newVersionRow)

    If newVersionRow.PC_Status = "Online" Then
        numofOnline += 1
        lblOnlinePC.Text = numofOnline
    Else
        numofOffline += 1
        lblOfflinePC.Text = numofOffline
    End If

Next

xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

Answer 1

One possible way is to read all used cells into an array, iterate the array and place cell data into rows of a DataTable. The following is setup to read three columns of data, if there were more columns in the sheet we ignore them in this demo. So for you, create nine data columns and use the same logic.

Note the last argument to the function HasHeader if passed as True the first row in the array will be considered as column names and passing False indicates data in the first row. With that said the code assume we are most likely starting at the first row but even so if we are starting at say row 4 and there are column headers on row four this can be a problem as the rows above are also collected and would throw off the magic here thus may not be right for you.

EDIT: Just saw you updated your question with code, the following may not be acceptable as it appears you are starting not from the first row but you could skip the first few elements in the array to get what you want.

Sample usage

Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx")
Dim sheetName As String = "Customers"
Dim dtCustomers As DataTable = UsedRowsToDataTable(fileName, sheetName, True)
DataGridView1.DataSource = dtCustomers

Code to read data into DataTable

Option Strict On
Option Infer Off

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module ExcelIterataingData_DataTable
    Public Function UsedRowsToDataTable(
        ByVal FileName As String,
        ByVal SheetName As String,
        ByVal HasHeader As Boolean) As DataTable

        Dim dtSheetData As New DataTable

        If IO.File.Exists(FileName) Then

            Dim Proceed As Boolean = False
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then

                dtSheetData.Columns.AddRange(
                    New DataColumn() _
                    {
                        New DataColumn With
                        {
                            .ColumnName = "CompanyName",
                            .DataType = GetType(String)
                        },
                        New DataColumn With
                        {
                            .ColumnName = "ContactName",
                            .DataType = GetType(String)
                        },
                        New DataColumn With
                        {
                            .ColumnName = "ContactTitle",
                            .DataType = GetType(String)
                        }
                    }
                )

                Dim xlUsedRange As Excel.Range = xlWorkSheet.UsedRange

                Try

                    Dim ExcelCells(,) As Object =
                        CType(xlUsedRange.Value(
                                Excel.XlRangeValueDataType.xlRangeValueDefault),
                            Object(,))

                    If ExcelCells IsNot Nothing Then
                        ' Get bounds of the array.
                        Dim RowCount As Integer = ExcelCells.GetUpperBound(0)

                        For row As Integer = 1 To RowCount

                            If (ExcelCells(row, 1) IsNot Nothing) AndAlso (ExcelCells(row, 2) IsNot Nothing) Then
                                dtSheetData.Rows.Add(New Object() _
                                    {
                                        ExcelCells(row, 1),
                                        ExcelCells(row, 2),
                                        ExcelCells(row, 3)
                                    }
                                )
                            End If
                        Next

                    End If
                Finally
                    Release(xlUsedRange)
                End Try

            End If

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            Release(xlCells)
            Release(xlWorkSheets)
            Release(xlWorkSheet)
            Release(xlWorkBook)
            Release(xlWorkBooks)
            Release(xlApp)

            If Not Proceed Then
                Throw New Exception("Failed to locate " & SheetName)
            End If
        Else
            Throw New Exception("Failed to locate " & FileName)
        End If

        If HasHeader Then
            If dtSheetData.Rows.Count > 0 Then
                dtSheetData.Rows(0).Delete()
            End If
        End If

        Return dtSheetData

    End Function
    Private 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 Module

Another option is to read via OleDb data provider were you can see examples in a MSDN code samples here, see project Demo1_VB for a simple example while the others are a tad more complex.