DATABASE AND INFORMATION MANAGEMENT
数据库与信息管理
VB 实现Excel 数据导入SQL Server 方法解析
李爱华
（中国石油长庆油田分公司超低渗透油藏研究中心油藏地质室，西安710021）
摘要：基于Visual Basic 编程语言，举例说明了Excel 数据导入Microsoft SQL Server 数据库常用的3种方法。关键词：VB ；Excel ；数据导入；SQL Server
The Resolution of Excel Data Import SQL Server’s Using VB
LI Aihua
（Petrochina Ehangqing Oilfield Company Ultra-low Permeability Reservoir Research centre Reservoir Geology Office ,Xi'an 710021）
Abstract ：This article is based on VB programming lang uage,which illustrates three ways of that Exc
el data import Mi －crosoft SQL Server database.
Key words ：VB ;Excel ;Data import ;SQL Server
随着计算机及互联网技术的普及，数据网络存取、分散应用已成为目前国内各大中型企业数据库应用的主要途径，数据库系统在办公信息化的应用也越来越广泛，如何将已有
的Excel 数据自动转入数据库系统已经成为数据转换及应用的焦点，下面就介绍几种使用VB 编写数据库程序的方法，以实现本地Excel 数据快速批量导入Microsoft SQL Server 网络数据
库。
1
前期准备
1.1
数据准备
为了使示例能够正常运行，需要在本机上安装Microsoft
SQL Server2000数据库、Visual Basic6.0及Microsoft Excel （版本在2003以上），并准备一个Excel 数据文件（必须是数据格
式，这里以油田某日的生产数据个别字段为例），格式如表1所示，数据记录数(行)和字段数（列）可根据需要增加。
表1数据表
井号
日期
投产日期生产时间采油方式日产液日产油日产水
桐24-212010-10-252008-8-1524抽油机  4.690.29  4.4桐24-222010-10-252008-8-1524抽油机  2.670.47  2.2桐24-232010-10-
252008-8-15
24
抽油机
2.28
0.04
2.24
1.2界面设计
打开Visual Basic6.0应用程序，新建一个工程，在form1中添加3个按钮，将其caption 属性分别设为“Excel 数据导入
(OpenRowSet)”、“Excel 数据导入(OpenDataSource)”和“Ex －
cel 数据导入(OpenSchema)”，以实现通过按按钮来运行VB 代
码。
为了使示例能够自动选取存在本机上的Excel 文件，本例应用了commandialog 控件，因此这里需要首先添加相应的部件(详情如图1)，部件添加后，在工具栏里就会出现一个
工具，将它添加到form1窗口的任意位置即可，做好的界面如
图2所示。双击“Excel 数据导入(OpenRowSet)”按钮，在代码编辑器中输入以下代码：
图1添加控件
Excel 数据导入SQL 方法
Excel 数据导入（OpenRowSet ）
Excel 数据导入（OpenDataSource ）
Excel 数据导入（OpenSchema ）
图2示例界面
作者简介：李爱华（1975-），女，工程师，研究方向：数据
2代码编写
库建设及软件开发应用。2.1OpenRowSet 方法
收稿日期：2010-11-17
visual basic pdf2011009.02
41
电脑编程技巧与维护
Private Sub command1_Click()
Dim strconn As String'定义连接字符串
'初始化commandialog1的属性
CommonDialog1.Filter="电子表格文件(.xls)|*.xls"
CommonDialog1.DialogTitle="请选择要导入的文件"
'初始化记录集及连接
Set rstemp=CreateObject("ADODB.Recordset")Set conn=
CreateObject("tion")
'打开连接，并加参数
Strconn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=qaz;Initial Catalog =scsjk;Data Source=(local)"
conn.Open strconn
'选取Excel数据文件，文件路径及名称记录在Com'monDialog1.FileName中
CommonDialog1.ShowOpen
'写SQL语句，数据写入SQL Server中
strsql="select*into tablename from OpenRowSet ('microsoft.jet.oledb.4.0','Excel8.0;HDR=Yes;database="
& CommonDialog1.FileName&";','select* from[sheet1$]')
where井号<>'合计'"
rstemp.Open strsql,conn,1,1
'提示
MsgBox"数据导入成功！",vbExclamation+ vbOKOnly
'关闭数据库连接及记录集，释放资
源conn.Close
Set conn=Nothing
rstemp.Close
Set rstemp=Nothing End
Sub
2.1.1数据库连接及提取记录集字符串
Strconn="Provider=SQLOLEDB.1;Persist Security Info=False;
User ID=sa;password=qaz;Initial Catalog=scsjk;Data Source=(lo－cal)"，其中User=sa;password=qaz,initial Catalog=scsjk;Data Source=(local)为需要修改部分，分别为SQL Server数据库的用
户名、密码、数据库名称、数据库所在机器的机器名或IP地址。
从Excel文件中取得记录集的语句是OpenRowSet('mi－
crosoft.jet.oledb.4.0','Excel8.0;HDR=Yes;database="
&Com－monDialog1.FileName&";','select*from[sheet1$]')，其中database=数据的路径和文件，[sheet1$]为工作表的名称，读
者可根据实际情况进行修改，或者设置变量来存取这些内容，
使程序运行不受数据文件自身因素的影响。
2.1.2写入数据库SQL语句
strsql="select*into tablename from OpenRowSet('mi －crosoft.jet.oledb.4.0','Excel8.0;HDR=Yes;database="& Com－monDialog1.FileName&";','select*from[sheet1$] ')where井号<>'合计'"
这里用到了最简单的从一个表创建另一个表的方式，即
“select*into新表名from记录集where条件”；如果数据库有相同结构的表，可向已有表中追加数据，使用“insert into已有的表名select*from记录集where条件”，另外也可以根据需要写出更加复杂的sql语句，这里不再详述。2.2OpenDataSource方法
2011.02
42
双击“Excel数据导入(OpenDataSource)”按钮，打开代码编辑器，将command1_click中的代码拷贝到Private Sub command2_Click()事件代码中，并将strsql=……语句修改为以下代码，
strsql="select*from OpenDataSource('Microsoft.Jet. OLEDB.4.0','Data Source="&CommonDialog1.FileName &";Extended properties=Excel8.0')...[Sheet1$]where井号<>'合计'"
此种方法与上一种的不同之处在于这里使用了OpenData－Source的方法来取得记录集，该方法的语法详细说明如下：OPENDATASOURCE(provider_name,init_string)
provider_name：注册为用于访问数据源的OLE DB提供程序的PROGID的名称。provider_name的数据类型为字符型，没有默认值。
init_string：连接字符串，这些字符串将要传递给目标提供程序的IDataInitialize接口。提供程序字符串语法是以关键字值对为基础的，这些关键字值对由分号隔开。在本例中init_string是Data Source和Extended properties的组合，设置
了数据源文件的路径和提供程序特定的连接字符串。
2.3OpenSchema方法
上述两种方法一般用于Excel文件与SQL Server安装在同一台计算机上的情况，下面介绍一种本地Excel导入异地SQL 的方法。
双击“Excel数据导入(OpenSchema)”按钮，在代码编辑器中输入以下代码。
Private Sub Command3_Click()
Dim strconn As String'定义Excel连接字符串
Dim cn As ADODB.Connection'定义Excel连
接Set cn=New ADODB.Connection
'初始化commandialog1的属性，选取Excel文件，文'件名保存在CommanDialog1.filename中备用
CommonDialog1.Filter="电子表格文件(.xls)|*.xls"
CommonDialog1.DialogTitle="请选择要导入的文件"
CommonDialog1.ShowOpen
'设置连接SQL数据库的连接字符串
strtemp="[odbc;Driver={SQL Server};Server=10.
78.173.77;Database=scsjk;UID=test;PWD=test]"
'设置Excel数据连接
strconn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&CommonDialog1.FileName&";Extended Prop－erties=Excel8.0"
cn.Open strconn
Set rstschema=cn.OpenSchema(adSchemaTables)
'设置Excel记录集
'循环将Excel数据文件中的各个数据表写入数据库
Do Until rstschema.EOF
strsql="insert into"&strtemp&".rukc select*from["&rstschema! TABLE_NAME&"]"
cn.Execute strsql,lngRecsAff,adExecuteNoRecords MsgBox"成功导入--"
&CommonDialog1.File－
Name&"--文件中--"&rstschema!TABLE_NAME&"
---的数据到SQL数据库中！",vbExclamation+
vbOKOnly rstschema.MoveNext
DATABASE AND INFORMATION MANAGEMENT数据库与信息管理
Loop
'释放资源
rstschema.Close
cn.Close
Set cn=Nothing
Set rstschema=Nothing End
Sub
这种方法的优势就在于它可以自动读取Excel工作簿中各工作表名称，循环将本地Excel数据批量导入到远程的SQL Server数据库。
2.3.1利用ODBC连接远程数据库
strtemp="[odbc;Driver={SQL Server};Server=10.78.
173.77;Database=scsjk;UID=test;PWD=test]"
2.3.2多工作表数据循环入库
Set rstschema=cn.OpenSchema(adSchemaTables)'设置excel记录集
'循环将Excel数据文件中的各个数据表写入数据库
Do Until rstschema.EOF
strsql="insert into"&strtemp&".rukc select*from["&rstschema! TABLE_NAME&"]"
cn.Execute strsql,lngRecsAff,adExecuteNoRecords MsgBox"成功导
入--"&CommonDialog1.FileName &"--文件中--"&rstschema!TABLE_NAME&"---的数据到SQL数据库中！",vbExclamation+vbOKOnly
rstschema.MoveNext
Loop
其中rukc为数据库中事先建的同结构的数据表名称，rstschema!TABLE_NAME为调用的当前工作表的名称。
3结语
详述了在VB6环境下，用代码实现常用办公数据文件Ex－cel批量导入SQL Server数据库系统的3种方法。特别是第三种方法，实现了本地Excel数据导入异地SQL Server数据库，可以这说是类似应用的一个难点，希望能给广大编程爱好者以启迪。
参考文献
[1]孔志勇，王毅，房大伟.SQL应用范例宝典.北京：人民
邮电出版社，2008.
[2]钟军，等.Visual Basic数据库高级实例导航.北京：科
学出版社，2004.
[3]【美】Microsoft公司.Microsoft Visual Basic6.0控件参考手册.
北京：北京希望电子出版社，1999.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
（上接第15页）
计算机的网卡MAC地址，利用存储于list文件中的MAC地址
构造远程唤醒数据包，再利用UDP协议进行广播发送，便可达到远程启动计算机的目的。这里要使用Winsock控件，相关代码如下：
With Winsock1
.RemoteHost="255.255.255.255"'广播方式发送
.Protocol=sckUDPProtocol'使用UDP协议
End With
实现“远程唤醒”功能的主要代码如下：
Dim macaddr,i,j,k
Dim mac(101)As Byte
'mac为字节数组，用来构造远程唤醒数据包
Open"Config\list"For Input As#1'打开list文件
Do While Not EOF(1)
Line Input#1,macaddr For i
=0To5
'构造唤醒包的起始部分FF-FF-FF-FF-FF-FF
mac(i)=255
Next i
mac(6)=Val("&H"&Left(macaddr,2))'构造唤醒包中第1遍
MAC地址
mac(7)=Val("&H"&Mid(macaddr,3,2))mac
(8)=Val("&H"&Mid(macaddr,5,2))mac(9)=
Val("&H"&Mid(macaddr,7,2))mac(10)=Val
("&H"&Mid(macaddr,9,2))mac(11)=Val("
&H"&Mid(macaddr,11,2))j=12
Do While j<=101
'构造唤醒包中第2遍至第16遍MAC地址
For k=j To j+5
mac(k)=mac(k-6)Next k
j=j+6
Loop
Winsock1.SendData mac
'使用SendData方法发送远程唤醒数据包Loop
Close#1'关闭list文件
4结语
基于VB的远程唤醒软件设计实现方案展现了该软件的技术要点和实现细节。该软件具有使用简捷、绿免安装等特点，在实际应用中运行稳定，能迅速批量唤醒局域网中的计算机，可有效提高计算机房的管理效率和管理水平。
参考文献
[1]AMD.Magic Packet Technology white paper[DB/OL].
www.amd/products/npd/overview/20212.html.1998.
[2]Timothy Parker.自学TCP/IP十四日通[M].北京：学苑
出版社，1994.
[3]梁丽平，黄平，黄振中.Visual Basic程序设计[M].第2
版.北京：北京理工大学出版社，2007.
[4]在VB中使用Iphlpapi.dll获取网络信息[DB/OL].
wenku.baidu/view/17ffec1755270722192ef7fe.html.
2010.
[5]孙秀梅，安剑，刘彬彬.Visual Basic开发技术大全[M].
第2版.北京：人民邮电出版社，2009.
2011009.02
43

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。