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类，哎，无聊啊，工作需要