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.CellType)            Case CellType.BLANK                'BLANK:                Return Nothing            Case CellType.BOOLEAN                'BOOLEAN:                Return cell.BooleanCellValue            Case CellType.NUMERIC                'NUMERIC:                Return cell.NumericCellValue            Case CellType.STRING                'STRING:                Return cell.StringCellValue            Case CellType.ERROR                'ERROR:                Return cell.ErrorCellValue            Case CellType.FORMULA                'FORMULA:                Select Case (cell.CellType)                    Case CellType.BLANK                        Return DBNull.Value                    Case CellType.BOOLEAN                        Return cell.BooleanCellValue                    Case CellType.NUMERIC                        Return cell.NumericCellValue                    Case CellType.STRING                        Return cell.StringCellValue                    Case CellType.ERROR                        Return cell.ErrorCellValue                    Case Else                        Return cell.CellType                End Select            Case Else                Return ("=" + cell.CellFormula)        End Select    End Function    Private Shared Function GetValueTypeForXLSX(ByVal cell As XSSFCell) As Object        If (cell Is Nothing) Then            Return DBNull.Value        End If        Select Case (cell.CellType)            Case CellType.BLANK                'BLANK:                   Return DBNull.Value            Case CellType.BOOLEAN                'BOOLEAN:                   Return cell.BooleanCellValue            Case CellType.NUMERIC                'NUMERIC:                   Return cell.NumericCellValue            Case CellType.STRING                'STRING:                   Return cell.StringCellValue            Case CellType.ERROR                'ERROR:                   Return cell.ErrorCellValue            Case CellType.FORMULA                'FORMULA:                 Select Case (cell.CachedFormulaResultType)                    Case CellType.BLANK                        'BLANK:                        Return DBNull.Value                    Case CellType.BOOLEAN                        'BOOLEAN:                        Return cell.BooleanCellValue                    Case CellType.NUMERIC                        'NUMERIC:                        Return cell.NumericCellValue                    Case CellType.STRING                        'STRING:                        Return cell.StringCellValue                    Case CellType.ERROR                        'ERROR:                        Return cell.ErrorCellValue                    Case Else                        Return cell.CellType                End Select            Case Else                Return ("=" + cell.CellFormula)        End Select    End Function    Public Shared Function ExcelToTableForXLSX(ByVal filePath As String) As DataTable        Dim dt As DataTable = New DataTable        Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)        Dim xssfworkbook As XSSFWorkbook = New XSSFWorkbook(fs)        Dim sheet As ISheet = xssfworkbook.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 = GetValueTypeForXLSX(CType(header.GetCell(i), XSSFCell))            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) = GetValueTypeForXLSX(CType(sheet.GetRow(m).GetCell(j), XSSFCell))                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

其中，引用了四个包，

然后在这个批量导入按钮内写入的内容：（*就是第二步，datatable转换成List）

 Dim OpenFileDialog1 As New OpenFileDialog        OpenFileDialog1.Filter = "Excel files(*.xls)|*.xls"        OpenFileDialog1.ShowDialog()        Dim strPath As String = OpenFileDialog1.FileName.ToString        If OpenFileDialog1.FileName = "" Then            MessageBox.Show("请选择文件")            Exit Sub        End If        Dim dtin = NPOIHelper.ExcelToTableForXLS(strPath)        Dim bllo As New BllM_MaterialManagementT        ' Dim oo As New M_MaterialManagementT        Dim listMm As New List(Of M_MaterialManagementT)        For Each uu In dtin.Rows            '共21个字段，除去Id自增列为20,（除去NUMBER,date,IsState）-17            Dim oo As New M_MaterialManagementT            'MaterialNumber            'oo.MaterialNumber = uu("MaterialNumber")            'MaterialName            If Not uu("MaterialName") Is DBNull.Value Then                oo.MaterialName = uu("MaterialName")            End If            'MerchantID            If Not uu("MerchantID") Is DBNull.Value Then                oo.MerchantID = uu("MerchantID")            End If            'ClassA            If Not uu("ClassA") Is DBNull.Value Then                oo.ClassA = uu("ClassA")            End If            listMm.Add(oo)            'Dim m = bllo.Insert(oo)            'If m > 0 Then            'Else            '    CommonMsg.ShowMsg(Me.Text, Constant.ENU_MSGID.MSGID67)            '    Return            'End If        Next        Dim y = bllo.Insertlist(listMm)        If y > 0 Then            CommonMsg.ShowMsg(Me.Text, Constant.ENU_MSGID.MSGID62)        Else            CommonMsg.ShowMsg(Me.Text, Constant.ENU_MSGID.MSGID67)        End If    End Sub

其中注释掉的代码是之前写的，作为datatable接收，直接用datatable插入，没有问题，后来想写的规范点，转一下list

3.下面是bll层和dal层，作为List实现插入数据库的方法：
bll层：

#Region " InsertlistEXCEL "    '插入数据    Public Function Insertlist(ByVal listPp As List(Of M_MaterialManagementT)) As Integer        Dim objDalM_MaterialManagementT As New DalM_MaterialManagementT        Dim ObjDBConn As New DbHelper        Dim strSql As String = String.Empty        Dim listSql As New List(Of String)        strErrorName = "BllM_MaterialManagement.Insertlist"        Dim i As Integer = 0        Try            listSql = objDalM_MaterialManagementT.Savelist(listPp)            For Each uu In listSql                Dim sql As String = String.Empty                sql = uu                intRows = ObjDBConn.ExcuteScalar(sql)            Next            ObjDBConn.Dispose()            Return intRows        Catch ex As Exception            ExHelper.ProcessDBHelper(ObjDBConn)            Throw New Exception(strErrorName, ex)        End Try    End Function#End Region

dal层1：

Public Shared ReadOnly MNumber = "SELECT isnull(RIGHT('000000' + convert(nvarchar(6),cast (MAX(Id) as int) + 1),6),'000001') FROM M_MaterialManagement"    Public Function Savelist(ByVal listPp As List(Of M_MaterialManagementT)) As List(Of String)        Dim listSql As New List(Of String)        For Each pp In listPp            Dim sql As String = String.Empty            pp.MaterialNumber = MNumber            sql = Insertexcel(pp)            listSql.Add(sql.ToString)        Next        Return listSql    End Function

dal层2：

#Region " Insert "    '数据插入操作    Public Function Insertexcel(ByVal clsM_MaterialManagementT As M_MaterialManagementT) As String        Dim strSql As New StringBuilder(System.String.Empty)    'Sql文        Try            strSql.Append(" INSERT INTO M_MaterialManagement")         '原材料信息管理表            strSql.Append(" (MaterialNumber")         '产品编号            strSql.Append(" ,MaterialName")           '产品名称            strSql.Append(" ,MerchantID")         '商戸编号               strSql.Append(")")            strSql.Append(" VALUES ")            '产品编号            strSql.Append(" ((" + clsM_MaterialManagementT.MaterialNumber + ")")            '产品名称            strSql.Append(" ,'" & Utility.ChangeQuotation(clsM_MaterialManagementT.MaterialName) & "'")            '商戸编号            If clsM_MaterialManagementT.MerchantID = "" Then                strSql.Append(" ,NULL ")            Else                strSql.Append(" ,'" & Utility.ChangeQuotation(clsM_MaterialManagementT.MerchantID) & "'")            End If            strSql.Append(") select @@IDENTITY")            Return strSql.ToString        Catch ex As Exception            Return String.Empty            MessageBox.Show(ex.ToString)        End Try    End Function

在UI层循环那里，循环前先定义个泛型集合，model的list，
循环excel转换成的datatable，将他每一列一一赋值给list，每次循环结束后listadd()
循环全部结束，得到一个List，把这个List带到BLL层里，再从BLL层进入dal层，然后把这个List循环，每行都走插入语句。