NPOI数据写入表格

Imports System.Reflection
Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel
Imports NPOI.HSSF.UserModel
Imports System.IO
Imports NPOI.SS.Util
Imports NPOI.XSSF.Util

Public Class NpoiEXCEL
    Private workbook As IWorkbook   '工作簿
    Private Shared suffixName As String = ".xls" '默认后缀名
    Private nonNumColName As New List(Of String)(New String() {"名字", "性别"}) '定义不是数值的列
    Sub New()
        ' TODO: Complete member initialization 
    End Sub
    Public Sub New(ByVal suffixName As String)
        If suffixName = ".xlsx" Then
            workbook = New XSSFWorkbook()
        ElseIf suffixName = ".xls" Then
            workbook = New HSSFWorkbook()
        End If
        suffixName = suffixName
    End Sub

    ''' <summary>
    ''' 写入表格
    ''' </summary>
    ''' <param name="dataTable">dataTable对象表</param>
    ''' <param name="sheet">要写入的sheet名字</param>
    ''' <param name="startRow">从哪一行开始写入</param>
    ''' <remarks></remarks>
    Public Sub writeIntoSheet(ByVal dataTable As DataTable, ByVal sheet As ISheet, Optional ByVal startRow As Integer = 0)
        If sheet Is Nothing Then
            MsgBox("ISheet的实例为nothing", , "错误")
            Return
        End If
        If dataTable Is Nothing Then
            MsgBox("DataTable的实例为nothing", , "错误")
            Return
        End If
        ''表头  
        Dim row As IRow = sheet.CreateRow(0)
        For j = 0 To dataTable.Columns.Count - 1
            Dim cell As ICell = row.CreateCell(j)
            cell.SetCellValue(dataTable.Columns(j).ColumnName.ToString)
        Next
        ''内容
        If startRow > 0 Then '填充表头下面和内容之间的行，可能会存在单元格合并之类的造成内容不从第1行开始
            For i = 0 To startRow
                row = sheet.CreateRow(i + 1)
                For j = 0 To dataTable.Columns.Count - 1
                    Dim cell As ICell = row.CreateCell(j)
                    cell.SetCellValue(dataTable.Rows(i).Item(j).ToString())
                Next
            Next
        End If
        For i = startRow To dataTable.Rows.Count - 1
            row = sheet.CreateRow(i + 1)
            For j = 0 To dataTable.Columns.Count - 1
                Dim cell As ICell = row.CreateCell(j)
                Dim cellValue As String = dataTable.Rows(i).Item(j).ToString()
                If isInNonNumCol(dataTable.Columns(j).ColumnName.ToString) Then
                    cell.SetCellValue(cellValue)
                Else
                    If cellValue = "" Then
                        cell.SetCellValue("")
                    Else
                        If Right(cellValue, 1) = "%" Then  '这里只考虑了百分比的情况
                            cell.SetCellValue(CDbl(Left(cellValue, Len(cellValue) - 1)) / 100)
                            Dim cellStyle As ICellStyle = workbook.CreateCellStyle
                            Dim dataFormat As IDataFormat = workbook.CreateDataFormat
                            cellStyle.DataFormat = dataFormat.GetFormat("0.00%")
                            cell.CellStyle = cellStyle
                        Else
                            cell.SetCellValue(CDbl(cellValue))
                        End If
                    End If
                End If

            Next
        Next
    End Sub
End Class