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()
        '表頭
        Dim header As IRow = sheet.GetRow(sheet.FirstRowNum)
        Dim columns As List(Of Integer) = New List(Of Integer)
        Dim i As Integer = 
        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 + )
        Loop

        '資料
        Dim m As Integer = (sheet.FirstRowNum + )
        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 + )
        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()
        '表頭   
        Dim header As IRow = sheet.GetRow(sheet.FirstRowNum)
        Dim columns As List(Of Integer) = New List(Of Integer)
        Dim i As Integer = 
        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 + )
        Loop

        '資料
        Dim m As Integer = (sheet.FirstRowNum + )
        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 + )
        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 >  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 = 
        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循環，每行都走插入語句。