VB.NET Excel导入

最近上班发现一定要注意两点：
1.前台的字段类型一定要与数据库保持一致！！！
2.一定要做非空判断！！！

下面进入正题，最近两天在做excel表格批量导入，从前台程序直接打开EXCEL，批量导入到数据库中

思路是：
1.先把EXCEL表格转换成Datatable,
2.然后datatable转成list,
3.把list保存到数据库中

1.首先在NPOI类中写四个方法，导入的，两个导入进入转换成datatable,（一个xls,一个xlsx），两个读取格式的（*就是第一步，转换成datatable）

  Public Shared Function ExcelToTableForXLS(ByVal filePath As String) As DataTable
        Dim dt As DataTable = New DataTable
        Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
        Dim hssfworkbook As HSSFWorkbook = New HSSFWorkbook(fs)
        Dim sheet As ISheet = hssfworkbook.GetSheetAt(0)
        '表头
        Dim header As IRow = sheet.GetRow(sheet.FirstRowNum)
        Dim columns As List(Of Integer) = New List(Of Integer)
        Dim i As Integer = 0
        Do While (i < header.LastCellNum)
            Dim obj As Object = GetValueTypeForXLS(CType(header.GetCell(i), HSSFCell))
            If ((obj Is Nothing) OrElse (obj.ToString = String.Empty)) Then
                dt.Columns.Add(New DataColumn(("Columns" + i.ToString)))
                'continue;
            Else
                dt.Columns.Add(New DataColumn(obj.ToString))
            End If

            columns.Add(i)
            i = (i + 1)
        Loop

        '数据
        Dim m As Integer = (sheet.FirstRowNum + 1)
        Do While (m <= sheet.LastRowNum)
            Dim dr As DataRow = dt.NewRow
            Dim hasValue As Boolean = False
            For Each j As Integer In columns
                dr(j) = GetValueTypeForXLS(CType(sheet.GetRow(m).GetCell(j), HSSFCell))
                If ((Not (dr(j)) Is Nothing) _
                            AndAlso (dr(j).ToString <> String.Empty)) Then
                    hasValue = True
                End If

            Next
            If hasValue Then
                dt.Rows.Add(dr)
            End If

            m = (m + 1)
        Loop

        Return dt
    End Function


    Private Shared Function GetValueTypeForXLS(ByVal cell As HSSFCell) As Object
        If (cell Is Nothing) Then
            Return Nothing
        End If

        Select Case (cell.CellTy