首页 > 专利信息

VB实现EXCEL数据导入SQL SERVER方法解析.PDF

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

数据准备

为了使示例能够正常运行，需要在本机上安装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

抽油机

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

2011009.02

电脑编程技巧与维护

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)”按钮，在代码编辑器中输入以下代码。LED路灯外壳

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

本文发布于:2024-09-21 18:38:31，感谢您对本站的认可！

本文链接：https://www.17tex.com/tex/2/170134.html

上一篇：VB6.0用recordset对数据库进行的更新操作

下一篇：vbmysql_VB连接MYSQL实例

标签：数据数据库应用连接方法唤醒

留言与评论（共有 0 条评论）