你的位置:首页 > 数据库

[数据库]Visual Basic 2012 借助DataGridView控件将Excel 2010数据导入到SQL server 2012


  :DataGridView控件作为数据传输的中介,只需简短的几行代码,可以实现数据元素的无缝传递。在这篇简短的案例中,我所做的只是数据传输的最简短代码,在界面的视觉感上,几乎没有任何吸引人的地方。不过,我要追求的可不是什么高级代码片段,而是活代码——在完全可工作的程序环境下,就可以迅速实现一个完整的程序。

关键字:DataGridView控件;Excel 2010;数据;SQL server 2012;

座右铭:一招鲜,吃遍天。——爱默生(美国作家)

具体的操作步骤:

第一步:创建一个“Excel 2010数据导入SQL Server 2012”(“开始”——>“所有程序”——>“Microsoft Visual Studio 2012”——>“Visual Studio 2012”——>“开始”——>“新建”——>“项目”选中Visual Basic、选中Window应用程序——>名称中输入Excel数据导入SQL Server——“确定”)。

第二步:创建程序界面。(在工具箱中添加一个DataGridView控件,两个Button按钮。

第三步:设置窗体和控件属性。

窗体或控件

属性名称

属性值

窗体form1

Text

Excel 2010数据导入SQL Server 2012

Button1

Text

Excel数据导入DataGridView

(Name)

excelToDataGridView_Button

Button2

Text

DataGridView导入到SQL server

(Name)

dataGridViewtoSQLserver_Button

DataGridView1

默认

默认

第四步:编写代码:

   a:导入三个名称空间绿色字是注释,不会被编译

        Imports System.Data            '本例中由于使用DataSet类,又不想输入全名,就必须导入System.Data名称空间

Imports System.Data.SqlClient  '本例中由于使用SQLConnection、SqlDataAdapter、SqlCommand、SqlParameter类,

‘又不想输入全名,就必须导入System.Data.SqlClient名称空间

Imports System.Data.OleDb      '本例中由于使用OleDb Connection、OleDb DataAdapter、OleDb Commandr类,

‘又不想输入全名,就必须导入System.Data.SqlClient名称空间

     b:添加excelToDataGridView_Button按钮的单击事件(绿色字是注释,不会被编译

           Private Sub excelToDataGridView_Button_Click(sender As Object, e As EventArgs) Handles excelToDataGridView_Button.Click

           '定义连接Excel的字符串

           Dim strConn As String

           ' IMEX=1 可把混合型作为文本型读取,避免null值, Data Source=Test.xlsx表示项目Debug文件中的文件Test.xlsx

           'Test.xlsx文件中的sheet1表中的第一行将会是导入DataGridView1中的标题

           strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Test.xlsx;Extended Properties='Excel 12.0;HDR=False;IMEX=1'"

           '定义OleDbConnection对象

           Dim oleConn As OleDbConnection

           '将连接字符串strConn赋值给对象oleConn

           oleConn = New OleDbConnection(strConn)

           '打开数据库

           oleConn.Open()

           '定义查询字符串

           Dim sql As String

           '给字符串赋值

           sql = "SELECT * FROM  [Sheet1$]" '可更改Sheet名称,根据实际情况而定,默认名是sheet1,sheet2……  

           '定义OleDbDataAdapter对象

           Dim oleDaExcel As OleDbDataAdapter

           '给对象oleDaExcel赋值,OleDbDataAdapter(String, OleDbConnection),第一个参数为连接字符串,第二个参数是连接对象

           oleDaExcel = New OleDbDataAdapter(sql, oleConn)

           Dim ds As DataSet '定义DataSet对象

           ds = New DataSet() '给对象ds赋值

           oleDaExcel.Fill(ds, "MyTable")  '此句OledbDataAdaper类的Fill方法作用:将DataSet对象中的数据填充到MyTable(随便设的)中

           DataGridView1.DataSource = ds '将控件DataGridView1的数据源设置为DataSet对象

           DataGridView1.DataMember = "MyTable" '将控件DataGridView1的数据元素设置为MyTable

           oleConn.Close() '关闭数据库

    End Sub

      C:添加dataGridViewtoSQLserver_Button按钮的单击事件(绿色字是注释,不会被编译

       Private Sub dataGridViewtoSQLserver_Button_Click(sender As Object, e As EventArgs) Handles dataGridViewtoSQLserver_Button.Click

          'My.Application.Info.DirectoryPath指的是项目的Debug文件,我的电脑默认为:C:\Users\Administrator\Documents\Visual Studio 2012\Projects\Excel数据导入SQL Server\Excel数据导入SQL Server\bin\Debug

          Dim objConnection As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=" & My.Application.Info.DirectoryPath & "\books.mdf;Persist Security Info = False")

        '定义SqlCommand对象,实现对SQL server数据库文件的操作

          Dim objCommand As SqlCommand = New SqlCommand()

          Dim i, j As Integer 'i为行,j为总行数

          j = DataGridView1.Rows.Count '获取总行数DataGridView1.Rows.Count;获取总列数DataGridView1.Columns.Count。

          For i = 0 To j - 1  'j 为行  DataGridView1的数据坐标以(0,0)开始

              objConnection.Open() '打开数据库

              objCommand.Connection = objConnection '将sqlCommand对象连接打开的数据

              'sqlCommand对象执行数据查询,@FirstName和@LastName中的FirstName与LastName是可以任意设置的,比如:@FName, @LName。

              objCommand.CommandText = "INSERT INTO Authors ( FirstName, LastName ) VALUES ( @FirstName, @LastName )"

              objCommand.Parameters.AddWithValue("@FirstName", DataGridView1.Item(1, i).Value)

              objCommand.Parameters.AddWithValue("@LastName", DataGridView1.Item(2, i).Value)

              '此时若用objCommand.ExecuteNonQuery(),就只执行第一条记录,并提示错误,所以必须用objCommand.ExecuteNonQueryAsync()

              '若只添加一条完整的记录,比如添加几个TextBox.text值,强烈建议用objCommand.ExecuteNonQuery(),速度快

              objCommand.ExecuteNonQueryAsync() '这种异步执行操作实质操作的是数据库副本,所以可以避免@FirstName的唯一性。

              objConnection.Close()

              objCommand.Parameters.Clear() '这是非常关键的一个语句,决定着是否能够顺利循环执行对象objCommand的所有语句,这个语句我琢磨了一天

          Next i

          MessageBox.Show("数据导入" & j - 1 & "条数据", "导入提示") '因为要除去标题,所以必须在所有行的基础上减1,即j-1才表示实际导入的行数

      End Sub

       d:向本项目的Debug文件中添加一个test.xlsxBooks.mdf数据库:

我的默认路径为C:\Users\Administrator\Documents\Visual Studio 2012\Projects\Excel数据

导入SQL Server\Excel数据导入SQL Server\bin\Debug中。

test.xlsx文件的内容:                      

  

 

Books.mdf数据库Authors表的内容

 

        我的Debug文件夹中的文件如下图:

       

 

第五步:运行程序。

              A:未运行图

              

             B:单击“Excel数据导入DataGridView”按钮,运行结果。

              

             C:单击“DataGridView导入到SQL server”按钮,运行结果。

                

             D:用SQL server 2012 打开Debug文件夹中的Books.mdf文件。

               (由于Books.mdf数据库Authors表的AuthorID列是自动增长的,并且我做过几次测试,所以

在第5行之后出现了41行、42行、43行、44行、45行的连续行,即是导入的5条数据。)

             

附:完整代码:

Imports System.Data            '本例中由于使用DataSet类,又不想输入全名,就必须导入System.Data名称空间

Imports System.Data.SqlClient  '本例中由于使用SQLConnection、SqlDataAdapter、SqlCommand、SqlParameter类,又不想输入全名,就必须导入System.Data.SqlClient名称空间

Imports System.Data.OleDb      '本例中由于使用OleDb Connection、OleDb DataAdapter、OleDb Commandr类,又不想输入全名,就必须导入System.Data.SqlClient名称空间

 

 

Public Class Form1

 

    Private Sub excelToDataGridView_Button_Click(sender As Object, e As EventArgs) Handles excelToDataGridView_Button.Click

        '定义连接Excel的字符串

        Dim strConn As String

        ' IMEX=1 可把混合型作为文本型读取,避免null值, Data Source=Test.xlsx表示项目Debug文件中的文件Test.xlsx

        'Test.xlsx文件中的sheet1表中的第一行将会是导入DataGridView1中的标题

        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Test.xlsx;Extended Properties='Excel 12.0;HDR=False;IMEX=1'"

        '定义OleDbConnection对象

        Dim oleConn As OleDbConnection

        '将连接字符串strConn赋值给对象oleConn

        oleConn = New OleDbConnection(strConn)

        '打开数据库

        oleConn.Open()

        '定义查询字符串

        Dim sql As String

        '给字符串赋值

        sql = "SELECT * FROM  [Sheet1$]" '可更改Sheet名称,根据实际情况而定,默认名是sheet1,sheet2……  

        '定义OleDbDataAdapter对象

        Dim oleDaExcel As OleDbDataAdapter

        '给对象oleDaExcel赋值,OleDbDataAdapter(String, OleDbConnection),第一个参数为连接字符串,第二个参数是连接对象

        oleDaExcel = New OleDbDataAdapter(sql, oleConn)

        Dim ds As DataSet '定义DataSet对象

        ds = New DataSet() '给对象ds赋值

        oleDaExcel.Fill(ds, "MyTable")  '此句OledbDataAdaper类的Fill方法作用:将DataSet对象中的数据填充到MyTable(随便设的)中

        DataGridView1.DataSource = ds '将控件DataGridView1的数据源设置为DataSet对象

        DataGridView1.DataMember = "MyTable" '将控件DataGridView1的数据元素设置为MyTable

        oleConn.Close() '关闭数据库

    End Sub

 

    Private Sub dataGridViewtoSQLserver_Button_Click(sender As Object, e As EventArgs) Handles dataGridViewtoSQLserver_Button.Click

        'My.Application.Info.DirectoryPath指的是项目的Debug文件,我的电脑默认为:C:\Users\Administrator\Documents\Visual Studio 2012\Projects\Excel数据导入SQL Server\Excel数据导入SQL Server\bin\Debug

        Dim objConnection As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=" & My.Application.Info.DirectoryPath & "\books.mdf;Persist Security Info = False")

        '定义SqlCommand对象,实现对SQL server数据库文件的操作

        Dim objCommand As SqlCommand = New SqlCommand()

 

        Dim i, j As Integer 'i为行,j为总行数

        j = DataGridView1.Rows.Count '获取总行数DataGridView1.Rows.Count;获取总列数DataGridView1.Columns.Count。

        For i = 0 To j - 1  'j 为行  DataGridView1的数据坐标以(0,0)开始

            objConnection.Open() '打开数据库

            objCommand.Connection = objConnection '将sqlCommand对象连接打开的数据

            'sqlCommand对象执行数据查询,@FirstName和@LastName中的FirstName与LastName是可以任意设置的,比如:@FName, @LName。

            objCommand.CommandText = "INSERT INTO Authors ( FirstName, LastName ) VALUES ( @FirstName, @LastName )"

            objCommand.Parameters.AddWithValue("@FirstName", DataGridView1.Item(1, i).Value)

            objCommand.Parameters.AddWithValue("@LastName", DataGridView1.Item(2, i).Value)

            '此时若用objCommand.ExecuteNonQuery(),就只执行第一条记录,并提示错误,所以必须用objCommand.ExecuteNonQueryAsync()

            '若只添加一条完整的记录,比如添加几个TextBox.text值,强烈建议用objCommand.ExecuteNonQuery(),速度快

            objCommand.ExecuteNonQueryAsync() '这种异步执行操作实质操作的是数据库副本,所以可以避免@FirstName的唯一性。

            objConnection.Close()

            objCommand.Parameters.Clear() '这是非常关键的一个语句,决定着是否能够顺利循环执行对象objCommand的所有语句,这个语句我琢磨了一天

        Next i

        MessageBox.Show("数据导入" & j - 1 & "条数据", "导入提示") '因为要除去标题,所以必须在所有行的基础上减1,即j-1才表示实际导入的行数

    End Sub

 

End Class