Sep 14

Written by: Steve Gray
9/14/2010 2:11 PM 

Here’s the class that I’m using to access Excel data

Imports Microsoft.Office.Interop.Excel

Public Class ExcelHelper
    Dim oExcel As New Microsoft.Office.Interop.Excel.Application
    Dim oBook As Microsoft.Office.Interop.Excel._Workbook
    Dim oSheet As _Worksheet

    Sub New(ByVal strFilePathAndName As String)
        oBook = oExcel.Workbooks.Open(strFilePathAndName)
        oSheet = oBook.Worksheets(1)
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
        Try
            oExcel.Quit()
        Catch ex As Exception

        End Try
    End Sub

    Function getNumberOfRows() As Int32
        'get the number of rows
        Dim intRows As Int32 = oSheet.Rows.CurrentRegion.Rows.Count
        Return intRows
    End Function

    Function getCellValue(ByVal strCell As String) As String
        'Read value in A2 cell   
        Dim cellValue As String = oSheet.Range(strCell).Value
        Return cellValue
    End Function

    Function getRangeValue(ByVal strCellRange As String) As System.Object(,)
        'get values from a range
        'the range is in the form of "A2:H2"
        Dim cellValue2 As System.Object(,) = oSheet.Range(strCellRange).Value
        Return cellValue2
    End Function

    Sub setCellValue(ByVal strCell As String, ByVal strValue As String)
        'Change value in A2 cell
        oSheet.Range(strCell).Value = strValue
    End Sub

    Sub saveWorkbook(ByVal strFileNameAndPath As String)
        'Save this Excel document
        oBook.SaveAs(strFileNameAndPath, True)
    End Sub

End Class