VB.NET将EXCEL中的数据导入到SQL SERVER

        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/book1.xls; Extended Properties=Excel 8.0;"

        Try

            Dim oleDbConnection As OleDbConnection = New OleDbConnection(sConnectionString)

            oleDbConnection.Open()

 

            '获取excel

            Dim dataTable As DataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

 

            '获取sheet名,其中(0)(1)...(N): 按名称排列的表单元素

            Dim tableName As String = dataTable.Rows(0)(2).ToString().Trim()

            tableName = "[" & tableName.Replace(" ' ", " ") & "]"

 

            '利用SQL语句从Excel文件里获取数据

            Dim query As String = "SELECT c1,c2,c3 FROM " & tableName

            Dim dataset As DataSet = New DataSet()

            Dim oleAdapter As OleDbDataAdapter = New OleDbDataAdapter(query, sConnectionString)

 

            oleAdapter.Fill(dataset, "Rwb")

 

            '可读取EXCEL中的记录数

            MessageBox.Show(dataset.Tables(0).Rows.Count)

 

            'SQL数据库连接

            Dim sqlcon As SqlClient.SqlConnection = New SqlClient.SqlConnection("Data Source=./sqlexpress;Initial Catalog=test;User ID=sa;Password=123")

            sqlcon.Open()

 

            'excel文件获得数据后,插入记录到SQL Server的数据表

            Dim dataTable1 As DataTable = New DataTable()

            Dim sqlDA1 As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter("SELECT c1,c2,c3 from BOOK1 ", sqlcon)

            Dim sqlCB1 As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(sqlDA1)

 

            sqlDA1.Fill(dataTable1)

 

            Dim dataRow11 As DataRow

            For Each dataRow11 In dataset.Tables("Rwb").Rows

                'sql里数据dataRow1

                Dim dataRow1 As DataRow = dataTable1.NewRow()

                dataRow1("C1") = dataRow11("C1")

                dataRow1("C2") = dataRow11("C2")

                dataRow1("C3") = dataRow11("C3")

                dataTable1.Rows.Add(dataRow1)

            Next

            MessageBox.Show("新插入 " & dataTable1.Rows.Count.ToString() & " 条记录 ")

            sqlDA1.Update(dataTable1)

 

            oleDbConnection.Close()

        Catch ex As Exception

            Console.WriteLine(ex.ToString())

        End Try

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值