Imports Excel
Public Class ExcelMgr2
Private _app As ApplicationClass
Private _workBook As Workbook
Private _currentSheet As Worksheet
#Region " Property "
Public Property CurrentSheet() As Worksheet
Get
Return _currentSheet
End Get
Set(ByVal value As Worksheet)
If value Is Nothing Then
Return
End If
_currentSheet = value
End Set
End Property
#End Region
#Region " Method "
Public Sub New(ByVal FileName As String)
Open(FileName)
End Sub
Private Sub Open(ByVal FileName As String)
_app = New ApplicationClass()
_workBook = _app.Workbooks.Open(FileName)
_currentSheet = CType(_workBook.Worksheets(1), Worksheet)
End Sub
Public Function GetCellValue(ByVal SheetNo As Integer, ByVal RowNo As Integer, ByVal ColNo As Integer) _
As String
Dim sheet As Worksheet
Dim value As String
If SheetNo <= 0 OrElse SheetNo > _workBook.Worksheets.Count Then
Throw New ExcelException(ExcelException.ERR_NUM_SHEET1)
End If
value = ""
sheet = CType(_workBook.Worksheets(SheetNo), Worksheet)
value = GetCellValue(sheet, RowNo, ColNo)
Return value
End Function
Public Function GetCellValue(ByVal SheetName As String, ByVal RowNo As Integer, ByVal ColNo As Integer) _
As String
Dim sheet As Worksheet
Dim value As String
sheet = Nothing
value = ""
For Each s As Worksheet In _workBook.Worksheets
If Equals(s.Name, SheetName) = True Then
sheet = s
Exit For
End If
Next s
If sheet Is Nothing Then
Throw New ExcelException(ExcelException.ERR_NUM_SHEET1)
End If
value = GetCellValue(sheet, RowNo, ColNo)
Return value
End Function
Public Function GetCellValue(ByVal sheet As Worksheet, ByVal RowNo As Integer, ByVal ColNo As Integer) _
As String
Dim cell As Range
Dim value As String
cell = CType(sheet.Cells.Item(RowNo, ColNo), Range)
value = CStr(cell.Value)
If value Is Nothing Then
value = ""
End If
Return value
End Function
Public Function GetCellValue(ByVal RowNo As Integer, ByVal ColNo As Integer) As String
Dim value As String
value = GetCellValue(_currentSheet, RowNo, ColNo)
Return value
End Function
Public Sub Close()
If _workBook IsNot Nothing Then
_workBook.Close()
End If
If _app IsNot Nothing Then
_app.Quit()
End If
_workBook = Nothing
_app = Nothing
GC.Collect()
End Sub
#End Region
End Class
Public Class ExcelException
Inherits Exception
Public Const ERR_NUM_SHEET1 As Integer = -8800301 'sheet not exist
Public Const ERR_NUM_CELL1 As Integer = -8800501
Private _errNumber As Integer
Private _ex As Exception
Public Sub New(ByVal Message As String)
_ex = New Exception(Message)
End Sub
Public Sub New(ByVal ErrorNumber As Integer)
_errNumber = ErrorNumber
Select Case _errNumber
Case ERR_NUM_SHEET1
_ex = New Exception(Message)
Case ERR_NUM_CELL1
_ex = New Exception(Message)
End Select
End Sub
Public Sub New(ByVal ErrorNumber As Integer, ByVal Message As String)
_errNumber = ErrorNumber
_ex = New Exception(Message)
End Sub
Public Sub New(ByVal Message As String, ByVal ex As Exception)
_ex = New Exception(Message, ex)
End Sub
Public Sub New(ByVal ErrorNumber As Integer, ByVal ex As Exception)
_errNumber = ErrorNumber
_ex = New Exception(Message, ex)
End Sub
Public ReadOnly Property Number() As Integer
Get
Return _errNumber
End Get
End Property
Public Overrides ReadOnly Property Message() As String
Get
If _ex IsNot Nothing Then
Return _ex.Message
Else
Return ""
End If
End Get
End Property
End Class