EXCEL文件导入远程SQL SERVER数据库

一、ASP.NET

1、将文件上传到服务器上

2、执行存贮过程EC_ExcelToSQL，注意文件路径为服务器上EXCEL所在的路径

二、winform下数据库在本机

1、获取EXCEL表名

 Try
            temp = GetExcelSheetNames(txtPath.Text)
            cboSheet.Items.Clear()
            For i = 0 To UBound(temp)
                cboSheet.Items.Add(temp(i))
            Next
            cboSheet.SelectedIndex = 0
        Catch ex As Exception


        End Try

  '获取EXCEL文件工作表名
    Public Function GetExcelSheetNames(ByVal excelFile As String) As String()
        Dim objConn As OleDbConnection = Nothing
        Dim dt As System.Data.DataTable = Nothing


        Try
            Dim connString As String


            If excelFile.EndsWith("xls") Or excelFile.EndsWith("XLS") Then
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excelFile & "; Extended Properties='Excel 8.0;IMEX=1'"
            ElseIf excelFile.EndsWith("xlsx") Or excelFile.EndsWith("XLSX") Then
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelFile & ";Extended Properties=""Excel 12.0;HDR=YES"""
            Else
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excelFile & "; Extended Properties='Excel 8.0;IMEX=1'"
            End If


            objConn = New OleDbConnection(connString)
            objConn.Open()
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)


            If dt Is Nothing Then
                Return Nothing
            End If


            Dim excelSheets As String() = New String(dt.Rows.Count - 1) {}
            Dim i As Integer = 0


            For Each row As DataRow In dt.Rows
                excelSheets(i) = row("TABLE_NAME").ToString()
                i += 1
            Next
            For j As Integer = 0 To excelSheets.Length - 1




            Next


            Return excelSheets
        Catch ex As Exception
            Return Nothing
        Finally
            ' Clean up. 
            If Not objConn Is Nothing Then
                objConn.Close()
                objConn.Dispose()
            End If
            If Not dt Is Nothing Then
                dt.Dispose()
            End If
        End Try
    End Function

2、如果SQL服务器在本机，则利用存贮过程可完成数据的导入，其中第一个参数为EXCEL表名，第2个参数为文件名。导入到TEMPDB下的MYTEST下

      strSql = "exec EC_ExcelToSQL '" & cboSheet.Text & "','" & txtPath.Text.Trim & "','" & cUserID & "'"
            if Not o.runSql(strSql) Then
                MsgBox("数据导入失败")
                Exit Sub
            End If

3、存贮过程EC_ExcelToSQL

create       PROCEDURE [EC_ExcelToSQL] 
@SheetName as nvarchar(50),
@ExcelPath as nvarchar(200),
@User as nvarchar(50)
AS 
BEGIN
   Declare @sql as nvarchar(800)
   Declare @str as nvarchar(80)
   if exists (select * from tempdb..sysobjects where name =N'mytest'+ @User and type='U')
    begin   
     set @sql=''
     set @sql='drop table tempdb..mytest' + @User
     exec(@sql)
    end
   set @str=LOWER(substring(@ExcelPath,charindex('.',@ExcelPath)+1,5))
   Set @sql=''
   if @str='xls'
   Set @sql='select * into tempdb..mytest' + @user + ' from OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE=' + @ExcelPath + ''',''select * from ['+ @SheetName + ']'')'
   else
   Set @sql='select * into tempdb..mytest' + @user + ' from OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;HDR=YES;DATABASE=' + @ExcelPath + ''',''select * from ['+ @SheetName + ']'')'


   exec(@sql)


END

三、WINFORM下数据库服务器在远程

1、解决思路

使用DataSet对象完成数据插入，datatable 的表结构必须与数据库中你要更新的表的结构一致。

(1) 创建一个Connection对象。

(2)创建一个DataAdapter对象。

(3) 初始化适配器。

(4)使用数据适配器的Fill方法执行SELECT命令，并填充DataSet。

(5) 使用DataTable对象提供的NewRow方法创建新行。

(6)将数据行的字段设置为插入的值。

(7) 使用DataRowAdd类的Add方法将数据行添加到数据表中。

(8)把DataAdapter类的InsertCommand属性设置成需要插入记录的INSERT语句。

(9) 使用数据适配器提供的Update方法将新记录插入数据库。

2、操作步骤，先导入临时表mytest001中其中001为用户ID，再根据需要对数据进行有效性校验，再进入正式的系统应用

   '删除临时数据mytest 该数据为原始的数据，EXCEL导入进来就不动了
            strSql = " if exists (select * from sysobjects where name =N'mytest" & cUserID & "' and type='U')  drop table mytest" & cUserID
            Try
                o.runSql(strSql)
            Catch ex As Exception

            End Try

            '定义EXCEK连接字符串 
            Dim excelsConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtPath.Text.Trim & "; Extended Properties=Excel 8.0;"
            Dim exceloleDbConnection As OleDbConnection = New OleDbConnection(excelsConnectionString)
            'SQL数据库连接 
            Dim sqlcon As SqlClient.SqlConnection = New SqlClient.SqlConnection("server=(local);database=test;user id=sa;password=123")


            Try
                exceloleDbConnection.Open()


                '获取excel表 
                Dim dataTable As DataTable = exceloleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)




                '利用SQL语句从Excel文件里获取数据 
                Dim exceltable As String = "SELECT * FROM [" & cboSheet.Text & "]"
                Dim exceldataset As DataSet = New DataSet
                Dim exceloleAdapter As OleDbDataAdapter = New OleDbDataAdapter(exceltable, excelsConnectionString)
                exceloleAdapter.Fill(exceldataset, "mytest" & cUserID)
                Dim exceldataRow As DataRow


                Try
                    '生成数据库
                    Dim i As Integer
                    Dim tempziduan As String  '字段名称
                    tempziduan = ""


                    exceldataRow = exceldataset.Tables("mytest" & cUserID).Rows(0)
                    For i = 0 To exceldataRow.Table.Columns.Count - 1


                        If tempziduan = "" Then
                            tempziduan = "[" & exceldataRow.Table.Columns(i).ToString() & "] [varchar](200)"
                        Else
                            tempziduan += ",[" & exceldataRow.Table.Columns(i).ToString() & "] [varchar](200)"
                        End If
                    Next
                    '根据EXCEL各字段名称在SQL中生成mytest
                    strSql = "CREATE TABLE mytest" & cUserID & " (" & tempziduan & ")"
                    o.runSql(strSql)


                Catch ex As Exception
                    MsgBox("生成数据库时发生错误", MsgBoxStyle.Information, "错误")
                    Exit Sub
                End Try


                sqlcon.Open()
                '从excel文件获得数据后，插入记录到SQL Server的数据表 
                Dim sqldatatable As DataTable = New DataTable
                Dim sqlDA As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT * FROM mytest" & cUserID, sqlcon)




                Dim sqlCB As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA)
                sqlDA.Fill(sqldatatable)


                For Each exceldataRow In exceldataset.Tables("mytest" & cUserID).Rows
              
                    Dim sqldatarow As DataRow = sqldatatable.NewRow()
                    Dim inumziduan As Integer
                    For inumziduan = 0 To exceldataRow.Table.Columns.Count - 1
                        sqldatarow(inumziduan) = exceldataRow(inumziduan)
                    Next


                    '''sqldatarow("考试分数") = exceldataRow("考试分数")
                 
                    sqldatatable.Rows.Add(sqldatarow)
                Next
                MsgBox("共检测到临时数据 " & sqldatatable.Rows.Count.ToString() & " 条记录 ")
                sqlDA.Update(sqldatatable)
            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                exceloleDbConnection.Close()
                sqlcon.Close()
            End Try