Koogra 操作 Excel2007 VB.NET 版

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data
Imports Net.SourceForge.Koogra.Excel2007
'Excel function library writed by wangli on 3/2/2011
Public Class ExcelHelper
    Private book As Workbook
    Public Sub New(ByVal path As String)
        Me.book = New Workbook(path)
    End Sub
    Public Sub New(ByVal stream As System.IO.Stream)
        Me.book = New Workbook(stream)
    End Sub
    Protected Function SaveAsDataTable(ByVal sheet As Worksheet) As DataTable
        Dim dt As New DataTable()
        Dim minRow As UInteger = sheet.CellMap.FirstRow
        Dim maxRow As UInteger = sheet.CellMap.LastRow
        Dim firstRow As Row = sheet.GetRow(minRow)
        Dim minCol As UInteger = sheet.CellMap.FirstCol
        Dim maxCol As UInteger = sheet.CellMap.LastCol
        For i As UInteger = minCol To maxCol Step 1
            dt.Columns.Add(firstRow.GetCell(i).GetFormattedValue())
        Next
        For i As UInteger = minRow + 1 To maxRow Step 1
            Dim row As Row = sheet.GetRow(i)
            If row IsNot Nothing Then
                Dim dr As DataRow = dt.NewRow()
                For j As UInteger = minCol To maxCol Step 1
                    Dim cell As Cell = row.GetCell(j)
                    If cell IsNot Nothing Then
                        dr(CInt(j)) = IIf(cell.Value IsNot Nothing, cell.Value.ToString(), String.Empty)
                    End If
                Next
                dt.Rows.Add(dr)
            End If
        Next
        Return dt
    End Function
    Public Function ToDataTable(ByVal index As Integer) As DataTable
        Dim sheet As Worksheet = Me.book.GetWorksheet(index)
        If sheet Is Nothing Then
            Throw New ApplicationException(String.Format("Index[{0}] that indicate table is not exisit!", index))
        End If
        Return Me.SaveAsDataTable(sheet)
    End Function
    Public Function ToDataTable(ByVal sheetName As String) As DataTable
        Dim sheet As Worksheet = Me.book.GetWorksheetByName(sheetName)
        If sheet Is Nothing Then
            Throw New ApplicationException(String.Format("Name[{0}] that indicate table is not exisit!", sheetName))
        End If
        Return Me.SaveAsDataTable(sheet)
    End Function
    'below is static method----------------------------------------------------------------------------------------------
    Public Shared Function ParseDateTime(ByVal cellValue As String) As DateTime
        Dim [date] As DateTime = Nothing
        Dim value As Double = 0
        If Double.TryParse(cellValue, value) Then
            [date] = DateTime.FromOADate(value)
        Else
            DateTime.TryParse(cellValue, [date])
        End If
        Return [date]
    End Function
    Public Shared Function ParseDataTimeFormat(ByVal cellValue As String, ByVal formatStyle As String) As String
        Dim [date] As DateTime = Nothing
        Dim value As Double = 0
        If Double.TryParse(cellValue, value) Then
            Return DateTime.FromOADate(value).ToString(formatStyle)
        Else
            Return String.Format("{0:" & formatStyle & "}", cellValue)
        End If
    End Function
    Public Shared Function TranslateToTable(ByVal path As String, ByVal sheetName As String) As DataTable
        Dim utils As New ExcelHelper(path)
        Return utils.ToDataTable(sheetName)
    End Function
    Public Shared Function TranslateToTable(ByVal path As String, ByVal sheetIndex As Integer) As DataTable
        Dim utils As New ExcelHelper(path)
        Return utils.ToDataTable(sheetIndex)
    End Function
    Public Shared Function TranslateToTable(ByVal path As String) As DataTable
        Dim utils As New ExcelHelper(path)
        Return utils.ToDataTable(0)
    End Function
    Public Shared Function TranslateToTable(ByVal stream As System.IO.Stream, ByVal sheetName As String) As DataTable
        Dim utils As New ExcelHelper(stream)
        Return utils.ToDataTable(sheetName)
    End Function
    Public Shared Function TranslateToTable(ByVal stream As System.IO.Stream, ByVal sheetIndex As Integer) As DataTable
        Dim utils As New ExcelHelper(stream)
        Return utils.ToDataTable(sheetIndex)
    End Function
    Public Shared Function TranslateToTable(ByVal stream As System.IO.Stream) As DataTable
        Dim utils As New ExcelHelper(stream)
        Return utils.ToDataTable(0)
    End Function
End Class

上面是我根据一个C#封装类改写成的vb.net类，哎，无聊啊，工作需要