Ｖｉｓｕａｌ Ｂａｓｉｃ實現Ｅｘｃｅｌ和ＳＱＬ ｓｅｖｅｒ之間的數據傳輸

摘要：研究了Excel和SQL sever之間的連接和數據的傳輸。Excel軟件對數據的修改和編輯具有很大的優勢，而SQL Sever在數據的查詢、存儲以及安全方面是Excel無法相比的。因此，通過Visual Basic 實現了Excel和SQL sever之間的數據傳輸可以很好地結合兩者的優點，更好更快地編輯、修改和存儲數據。

關鍵詞：Visual Basic；Excel；SQL sever；數據；傳輸

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2008)30-0613-02

The Realization of Data Transmission between Excel and SQL Sever by Visual Basic

LI Xiao-bo， ZHOU Ting-mei

(Wuhan University of Technology， Wuhan 430070， China)

Abstract: The connection and the data transmission between Excel and SQL sever has been studied in this arcticle. The Excel software has great superiority in the amending and the edition of the datum， but in the inquiring， the storaging and the security aspects of the datum， SQL sever can execute better than Excel. Therefore， data transmission between Excel and the SQL sever that has been realized by Visual Basic can unify both's merit well， which enable the datum editing， datum amending and datum storing better and more quickly.

Key words: visual basic; Excel; SQL sever; data; transmission

1 引言

在現代互聯網信息爆炸的時代，信息不論在數量和內容上都會發生很快的更新，因此及時實現對信息的管理和更新非常重要。SQL sever數據庫在數據的存儲、管理和查詢方面非常的便捷，尤其提供了很大程度的安全性，然而SQL sever數據庫在跨系統的數據交換仍存在一定的不便。在數據的編輯方面也不能和Excel相比。Excel在文件處理、保存、傳送、交換數據方面非常便利，卻在數據的集中管理和查詢方面不方便。本文主要研究了使用Visual Basic實現Excel和SQL sever數據庫之間的數據交換在信息管理系統的應用，包括將SQL sever數據導入到Excel中、將Excel的數據導入到SQL sever中，同時可以直接在Visual Basic的可視化界面上對數據進行修改、刪除和添加等編輯動作。

2 SQL sever數據導入到Excel的實現

數據的導出主要是將SQL sever數據庫中的數據導出到Excel中，這樣可以方便數據庫的使用者下載自己需要的信息。主要的思路是將數據存入到一個新建的Excel的文件中，然后存儲起來以便使用。

2.1 使用的Visual Basic 控件

Adodc 數據庫控件是實現Visual Basic 和SQL sever數據庫之間橋梁，通過它可以將數據庫中的數據讀取出來，然后在DataGrid 數據庫控件中顯示出來。DataGrid控件就是以Adodc控件連接的數據庫為數據源，在界面上可以對應的顯示出來。

2.2 導入Excel文件

通過Adodc建立連接，采用Excel VBA的相關技術實現導出。代碼如下：

‘下面的代碼實現DataGrid中的數據寫入Excel中

Dim xlApp As New Excel.Application，xlBook As Excel.Workbook

Dim xlQuery As Excel.QueryTable，xlSheet As Excel.Worksheet

Dim i As Integer

Set xlApp = CreateObject(\"Excel.Application\")

Set xlBook = Nothing

Set xlBook = xlApp.Workbooks().Add

Set xlSheet = xlBook.Worksheets(\"sheet1\")

xlApp.Visible = True

With xlSheet

.Range(\"a1\").CurrentRegion.Clear

For i = 1 To DataGrid1.Columns.Count

.Cells(1， i) = DataGrid1.Columns(i - 1).Caption

For j = 0 To Adodc1.Recordset.RecordCount - 1

.Cells(j + 2， i) = DataGrid1.Columns(i- 1)

.CellText(DataGrid1.RowBookmark(j))

Next j

Next i

End With

2.3數據導入Excel的操作界面

SQL sever的數據首先通過Adodc顯示到DataGrid中，然后單擊寫入Excel按鈕就可以實現寫入到Excel的文件中，操作界面如圖1所示。

3 Excel的數據導入到SQL sever數據庫的實現

要將數據導入到SQL sever中，首先需要采用Excel建立連接，將Excel中的信息讀取存入到DataGrid數據庫控件中，然后連接到SQL sever數據庫，執行SQL語句將數據寫入到數據的相應的表文件中。

3.1 將Excel中的數據讀入DataGrid中

要將數據讀入DataGrid中，需要采用Excel和Visual Basic之間的數據交換連接驅動Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls)，以下代碼是將數據讀取到DataGrid中的：

Dim adoConnection As New ADODB.Connection

Dim adoRecordset As New ADODB.Recordset

Dim i As Integer， cmd As String

Dim connStr， AccessLocation As String

adoConnection.Open\"Data Provider= MSDASQL.1;

driver=MicrosoftExcelDriver (*.xls);DBQ=（Excel文件的存放地址） \"adoRecordset.Open\"select*from[sheet1$]\"，adoConnection，

adOpenKeyset， dLockOptimistic

connStr = \"Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=（Excel文件的存放地址）\"

Adodc1.ConnectionString = connStr

Adodc1.CommandType = adCmdText

cmd = \"Select * from [Sheet1$]\"

Adodc1.RecordSource = cmd

Adodc1.Refresh

SetDataGrid1.DataSource = Adodc1

3.2 數據庫連接

要寫數據到數據庫中，首先要建立數據庫的連接，然后執行相應的SQL語句將數據寫入到表文件中，然后關閉數據庫。以下的代碼就是實現數據庫的連接，寫入數據，關閉數據庫的。代碼如下：

Private cnn As ADODB.Connection

Private re As ADODB.Recordset

Dim cmd As New ADODB.Command

Set cnn = New ADODB.Connection

‘這段代碼實現數據庫的連接

cnn.ConnectionString=\"driver={SQLServer};server=（數據庫服務器的名稱）;uid=sa;pwd=（密碼）;database=（所要連接數據庫的名稱）\"

cnn.Open

‘這段代碼實現數據寫入到SQL sever的表文件中

Set cmd.ActiveConnection = cnn

cmd.CommandText = \"insertin（表的名稱） (fieldname1，fieldname2，……) values(value1，value2，……)\"

‘其中value的值就是對應的DataGrid中的數據

cmd.Execute

Set cmd = Nothing

‘后面實現數據庫的關閉

cnn.Close ：Set cnn = Nothing

3.3 數據存入SQL sever數據庫

點擊了圖1中的讀取Excel的按鈕，程序就會將數據顯示到另一個操作界面的DataGrid控件中，然后點擊寫入數據庫這個按鈕就可以將數據寫入數據庫的相應的表文件中。其運行結果如圖2所示。

4 結論

本文研究的是SQL sever 和Excel之間通過Visual Basic 來實現數據的交換，通過這個數據的導出和導入交互，可以采用Excel實現數據的快速編輯和跨數據庫的傳遞，采用SQLsever 實現數據的查詢，并且可以保證數據的安全性。為信息系統的管理和編輯提供了更高更快的途徑。

參考文獻：

[1] 趙喜來，崔程.SQL sever 2005從入門到精通[M].北京:電子工業出版社，2007.

[2] 高春艷，谷偉東.Visual Basic 數據庫開發關鍵技術與實例應用[M].北京:人民郵電出版社，2004.

[3] 龔沛真.VB程序設計教程[M].北京:高等教育出版社，2000.

[4] 王平勤.asp.net/c#實現excel與SQL Server數據遷移技術研究[J].福建電腦，2008(3):184-184，212.

[5] Visual Basic 數據庫開發[M].北京:清華大學出版社，2002.

[6] 馬維峰.Excel VBA應用開發從基礎到實踐[M].北京:電子工業出版社，2006.

[7] Lomax P. VB與VBA技術手冊[M].北京:中國電力出版社，2002.

[8] 魏文勝.Excel與SQL Server數據的相互轉換之方法[J].電腦知識與技術，2007，2(10):892.

[9] 曾金發.C#2.0實現Excel與SQL SERVER 2005數據交互[J].科技創新導報，2007(31):11.

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文