导入excel数据到DB

'导入数据方法
    Private Function ImportData() As Boolean
        Try
            Dim dsImport As New DataSet
            'EXCEL模板格式校验
            If Not ImportByExcel(dsImport) Then
                Return False
            End If
            Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
            '导入前检查
            If Not ImportCheck(dsImport) Then
                Return False
            End If
            '是否导入过
            If Not ImportedCheck(dsImport) Then
                Return False
            End If
            '开始事务
            OpenTransaction()
            '导入数据
            mlogic.InsertSearchData(dsImport)
            '提交事务
            CommitTransaction()
            'EXCEL导入成功！
            MsgBox("导入成功！", MsgBoxStyle.Information, Me.Text)
            Return True
        Catch ex As Exception
            RollbackTransaction()
            MsgBox(ex.Message, MsgBoxStyle.Critical, Me.Text)
        Finally
            CloseConnection()
            '光标变为标准形状
            Me.Cursor = System.Windows.Forms.Cursors.Default
        End Try
    End Function
    
'EXCEL模板格式校验
    Private Function ImportByExcel(ByRef dsImport As DataSet) As Boolean
        Dim openFileDialog As New System.Windows.Forms.OpenFileDialog
        '设置所取文件的类型
        openFileDialog.Filter = "Excel 文件(*.xlsx;*.xls;*.xlsm)|*.xlsx;*.xls;*.xlsm"
        openFileDialog.FilterIndex = 1
        openFileDialog.RestoreDirectory = True
        If (openFileDialog.ShowDialog() = Windows.Forms.DialogResult.OK) Then
            '查看导入的EXCEL是否已经打开了
            If CheckExcelIsOpen(openFileDialog.FileName) Then
                Return False
            End If
            dsImport = GetDataFromExcel(openFileDialog.FileName)
            '判断导入的模板是否正确
            If Not checkDataColumnsCount(dsImport) Then
                Return False
            End If
        Else
            Return False
        End If
        Return True
    End Function
'判断EXCEL是否已经被打开
    Public Function CheckExcelIsOpen(ByVal sfilename As String) As Boolean
        Dim fs As System.IO.FileStream = Nothing
        Try
            fs = New System.IO.FileStream(sfilename, IO.FileMode.Open, IO.FileAccess.ReadWrite, IO.FileShare.Write)
            If Not fs.CanWrite Then
                MsgBox("请先关闭导入的EXCEL文件！", MsgBoxStyle.Information, Me.Text)
                Return True
            End If
            Return False
        Catch ex As Exception
            MsgBox(showMessage("请先关闭导入的EXCEL文件！"), MsgBoxStyle.Information, Me.Text)
            Return True
        Finally
            If fs IsNot Nothing Then
                fs.Close()
                fs.Dispose()
            End If
        End Try
    End Function

 Private Function GetDataFromExcel(ByVal sfilename As String) As DataSet
        '返回用DataSet
        Dim dsReturn As New DataSet
        Dim ds As New DataSet
        'EXCEL对象
        Dim xlApp As Excel.Application = Nothing
        '打开指定的Excel文件
        Dim xlBook As Excel.Workbook = Nothing
        '取得数据库连接
        Dim oDbConn As System.Data.OleDb.OleDbConnection = Nothing
        Dim sConn As String
        Try
            Me.Cursor = System.Windows.Forms.Cursors.WaitCursor
            '打开数据与EXCEL连接
            Dim fileNameList As String() = sfilename.Split(".")
            If fileNameList(fileNameList.Length - 1).Equals("xlsx") OrElse fileNameList(fileNameList.Length - 1).Equals("xlsm") Then
                sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & sfilename & ";" & "Extended Properties='Excel 12.0;HDR=NO;IMEX=1'"
            Else
                sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & sfilename & ";" & "Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"
            End If
            '取得数据库连接
            oDbConn = New System.Data.OleDb.OleDbConnection(sConn)
            '打开数据库连接
            oDbConn.Open()
            '数据检索用
            Dim oDataAdapter As System.Data.OleDb.OleDbDataAdapter
            'EXCEL对象
            xlApp = New Excel.Application
            'EXCEL文件不可见
            xlApp.Visible = False
            '打开指定的Excel文件
            xlBook = xlApp.Workbooks.Open(sfilename)
            '取得文件的第一个Sheets名
            Dim sSheetName As String = ""
            '检索用SQL文
            Dim sSqlSelect As String = ""
            Dim count As Integer = 0
            For i As Integer = 0 To xlBook.Sheets.Count - 1
                sSheetName = xlBook.Sheets(count + i + 1).Name
                sSqlSelect = "SELECT * FROM [" & sSheetName & "$]"
                'Excel文件转换到DataSet
                oDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sSqlSelect, oDbConn)
                oDataAdapter.Fill(ds)
                ds.Tables(i).TableName = sSheetName
                dsReturn.Tables.Add(ds.Tables(i).Copy())
            Next
            '设定返回值
            GetDataFromExcel = dsReturn
        Catch ex As Exception
            '发生异常
            Throw New Exception(ex.Message)
        Finally
            If oDbConn.State <> ConnectionState.Broken And _
                oDbConn.State <> ConnectionState.Closed Then
                '关闭数据库连接
                oDbConn.Close()
            End If
            If xlBook IsNot Nothing Then
                xlBook.Saved = True
                '关闭Excel文件
                xlBook.Close()
                '释放COM对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
            End If
            xlBook = Nothing
            If xlApp IsNot Nothing Then
                '退出Excel对象
                xlApp.Quit()
                '释放COM对象
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
            End If
            xlApp = Nothing
            '垃圾回收
            GC.Collect()
            Me.Cursor = System.Windows.Forms.Cursors.Default
        End Try
    End Function
    ''判断模板数据列数是否正确
Private Function checkDataColumnsCount(ByVal ds As DataSet) As Boolean
        'sheet名
        Dim strTableName As String = ""
        If ds.Tables.Count < 1 Then
            '导入文件格式不正确！
            MsgBox(showMessage("PLP2010_006"), MsgBoxStyle.Information, Me.Text)
            Return False
        End If
        If ds.Tables(0).Columns.Count < 31 Then
            '导入文件格式不正确！
            MsgBox(showMessage("PLP2010_006"), MsgBoxStyle.Information, Me.Text)
            Return False
        End If
        Return True
    End Function
  
  Private Function ImportCheck(ByRef dsImport As DataSet) As Boolean
        ImportCheck = False
        Dim strMsg As String = ""
        'Dim dr As DataRow()
        Dim intRowsCount As Integer = dsImport.Tables(0).Rows.Count - 1
        Dim cmdParms(intRowsCount) As DictionaryEntry
        'Dim aryInvoiceCD As New ArrayList
        Dim strPO As String = ""
        Dim strF_Code As String = ""
        Dim strPO_Count As String = ""
        dsImport.Tables(0).Columns(1).ColumnName = "PO"
        dsImport.Tables(0).Columns(20).ColumnName = "F_CODE"
        dsImport.Tables(0).Columns(28).ColumnName = "PO_COUNT"
        '第二行开始开始循环
        For inti As Integer = 1 To dsImport.Tables(0).Rows.Count - 1
            strPO = dsImport.Tables(0).Rows(inti)("PO").ToString().Trim
            strF_Code = dsImport.Tables(0).Rows(inti)("F_CODE").ToString().Trim
            strPO_Count = dsImport.Tables(0).Rows(inti)("PO_COUNT").ToString().Trim
            'PO号 机种 非空校验
            If strPO.Equals("") Or strF_Code.Equals("") Then
                Continue For
            End If
            '判断重复存在
            Dim keyPO As String
            Dim valFcode As String
            For index As Integer = 0 To cmdParms.Length - 1
                keyPO = Convert.ToString(cmdParms(index).Key)
                valFcode = Convert.ToString(cmdParms(index).Value)
                If strPO.Equals(keyPO) AndAlso strF_Code.Equals(valFcode) Then
                    strMsg &= String.Format("第{0}行：PO号+机种【{1}+{2}】重复存在！", inti + 1, strPO, strF_Code) & Environment.NewLine
                    Continue For
                End If
            Next
            '保存记录的主键
            cmdParms(inti - 1) = New DictionaryEntry(strPO, strF_Code)
        Next
        If strMsg.Length > 0 Then
            Using frm As New PLP010(strMsg)
                frm.ShowDialog()
            End Using
            Exit Function
        End If
        ImportCheck = True
    End Function  

Private Function ImportedCheck(ByRef dsImport As DataSet) As Boolean
        ImportedCheck = False
        Dim strMsg As String = ""
        'Dim dr As DataRow()
        Dim dt As DataTable 
        '表中数据
        dt = mlogic.SearchAll_Logic().Tables(0)
        Dim cmdParms(dt.Rows.Count - 1) As DictionaryEntry
        '添加数据到字典中
        For index As Integer = 0 To dt.Rows.Count - 1
            cmdParms(index) = New DictionaryEntry(dt.Rows(index).Item(0).ToString(), dt.Rows(index).Item(1).ToString())
        Next
        Dim strPO As String = ""
        Dim strF_Code As String = ""
        Dim strPO_Count As String = ""
        dsImport.Tables(0).Columns(1).ColumnName = "PO"
        dsImport.Tables(0).Columns(20).ColumnName = "F_CODE"
        dsImport.Tables(0).Columns(28).ColumnName = "PO_COUNT"
        '第二行开始开始循环
        For inti As Integer = 1 To dsImport.Tables(0).Rows.Count - 1
            strPO = dsImport.Tables(0).Rows(inti)(0).ToString()
            strF_Code = dsImport.Tables(0).Rows(inti)(21).ToString()
            strPO_Count = dsImport.Tables(0).Rows(inti)(29).ToString()
            '判断重复存在
            Dim keyPO As String
            Dim valFcode As String
            For index As Integer = 0 To cmdParms.Length - 1
                keyPO = cmdParms(index).Key.ToString()
                valFcode = cmdParms(index).Value.ToString()
                If strPO.Equals(keyPO) AndAlso strF_Code.Equals(valFcode) Then
                    strMsg &= String.Format("第{0}行：PO号+机种【{1}+{2}】已导入！", inti + 1, strPO, strF_Code) & Environment.NewLine
                    Continue For
                End If
            Next
        Next
        If strMsg.Length > 0 Then
            Using frm As New PLP010(strMsg)
                frm.ShowDialog()
            End Using
            Exit Function
        End If
        ImportedCheck = True
    End Function