Excel worksheets can read in Visual studio by using Office API’s. To get start working with Excel file we first need to understand the structure of Excel files. For programming Excel files we first creates an Excel Application object, read Excel file in workbook and read sheets from this work book.

To get start with Excel programming you first need to add Microsoft.Office.Interop.Excel reference in your project. Instead of writing the simple code for reading Excel file i have created one class for reading excel file and it also help you read workbook and worksheets. you can directly use this class in your project to read excel file.

Imports OfficeExcel = Microsoft.Office.Interop.Excel
Namespace MyExcel

    Public Class ExcelRead
        Dim _xlApp As OfficeExcel.Application
        Dim _xlWorkBook As OfficeExcel.Workbook
        Dim _xlWorkSheet As OfficeExcel.Worksheet
        Dim _XlFilePath As String
#Region "Constructor"
        Public Sub New(ByVal filepath As String)
            _XlFilePath = filepath
            OpenExel(_XlFilePath)
        End Sub
#End Region

#Region "Protected Method"
        Protected Sub OpenExel(ByVal fileName As String)
            _xlApp = New OfficeExcel.ApplicationClass
            _xlWorkBook = _xlApp.Workbooks.Open(fileName)
        End Sub
#End Region

#Region "Public Functions"
        Public Function GetWorkSheet(ByVal sheetname As String) As OfficeExcel.Worksheet
            For Each xlsheet As OfficeExcel.Worksheet In Me.XLWorkBook.Sheets
                If xlsheet.Name = sheetname Then
                    _xlWorkSheet = xlsheet
                    Return _xlWorkSheet
                End If
            Next
            Return _xlWorkSheet
        End Function
        Public Function getExcelWorkBookNames() As List(Of String)
            Dim workBookNames As New List(Of String)
            For Each wb As OfficeExcel.Worksheet In Me._xlApp.Worksheets
                workBookNames.Add(wb.Name)
            Next
            Return workBookNames
        End Function
        Public Function XLFilePath() As String
            Return _XlFilePath
        End Function
        Public Sub closeXL()
            releaseObject(Me._xlApp)
            releaseObject(Me._xlWorkBook)
            releaseObject(Me._xlWorkSheet)
        End Sub
#End Region

#Region "Read Only Property"
        Public ReadOnly Property XlApplication() As OfficeExcel.Application
            Get
                Return _xlApp
            End Get
        End Property
        Public ReadOnly Property XLWorkBook() As OfficeExcel.Workbook
            Get
                Return _xlWorkBook
            End Get
        End Property
        Public ReadOnly Property XLWorkSheet() As OfficeExcel.Worksheet
            Get
                Return _xlWorkSheet
            End Get
        End Property
#End Region

#Region "Private Functions"
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()

            End Try
        End Sub
#End Region
    End Class

End Namespace

My next post use the same class that today we created to learn how to Format excel cells and draw charts.