Open And Read Excel Spreadsheet In VB.Net

Here's an example showing how to open and read in an Excel spreadsheet in the VB.Net language. I'm sure you can manage to easily convert this code to other Visual Studio languages if you're using C#, for example.

Reading an Excel spreadsheet isn't hard to do but it's important to get the code right and clean up after yourself, otherwise you'll find loads of excel.exe processes laying around in memory after your program finishes. Use the following example as a basis for your code and you shouldn't get any memory leaks.

First, add a reference to your project:

I use three subroutines to read open the spreadsheet, read a worksheet range, and clean up the objects from memory, as follows:

    Private Sub importExcel(ByVal sFile As String)

        Dim xl As Excel.Application
        Dim wb As Excel.Workbook
        Dim ws As Excel.Worksheet

        Try
            xl = New Excel.Application

            Try
                wb = xl.Workbooks.Open(sFile)

                Try
                    ws = CType(wb.Worksheets(1), Excel.Worksheet)
                    Call importSheet(ws.UsedRange)

                Catch ex As Exception
                    Throw
                Finally
                    clearUp(ws)
                End Try

            Catch ex As Exception
                Throw
            Finally
                wb.Close()
                clearUp(wb)
                xl.Workbooks.Close()
                clearUp(xl.Workbooks)
            End Try

        Catch ex As Exception
            Throw

        Finally
            xl.Quit()
            xl = Nothing
            clearUp(xl)
            GC.Collect()

        End Try

    End Sub


    Private Sub importSheet(ByVal range As Excel.Range)

        Dim sString1 As String
        Dim sString2 As String
        Dim sString3 As String

        Try
            For iRow As Integer = 1 To range.Rows.Count
                sString1 = CStr(CType(range.Cells(iRow, 1), Excel.Range).Value)
                sString2 = CStr(CType(range.Cells(iRow, 2), Excel.Range).Value)
                sString3 = CStr(CType(range.Cells(iRow, 3), Excel.Range).Value)

                ' Add code to read required columns from the spreadsheet here



            Next

        Catch ex As Exception
            Throw ex

        End Try

    End Sub


    Private Sub clearUp(ByVal o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch ex As Exception
        Finally
            o = Nothing
        End Try
    End Sub