VB实现Excel数据导入SQL+Server方法解析.pdf.doc

DATABASE AND INFORMATION MANAGEMENT数据库与信息管理VB 实现 Excel 数据导入 SQL Server 方法解析李爱华（中国石油长庆油田分公司超低渗透油藏研究中心油藏地质室，西安 710021）摘要： 基于 Visual Basic 编程语言，举例说明了 Excel 数据导入 Microsoft SQL Server 数据库常用的 3 种方法。关键词： VB； Excel； 数据导入； SQL ServerThe Resolution of Excel Data Import SQL Servers Using VBLI Aihua（Petrochina Ehangqing Oilfield Company Ultra-low Permeability Reservoir Research centre Reservoir Geology Office , Xian 710021）Abstract：This article is based on VB programming lang uage, which illustrates three ways of that Excel data import Mi crosoft SQL Server database.Key words：VB ; Excel ; Data import ; SQL Server随着计算机及互联网技术的普及，数据网络存取、分散应用已成为目前国内各大中型企业数据库应用的主要途径，数据库系统在办公信息化的应用也越来越广泛，如何将已有的 Excel 数据自动转入数据库系统已经成为数据转换及应用的焦点，下面就介绍几种使用 VB 编写数据库程序的方法，以实现本地 Excel 数据快速批量导入 Microsoft SQL Server 网络数据库。1 前期准备1.1数据准备为了使示例能够正常运行，需要在本机上安装 MicrosoftSQL Server2000 数据库、Visual Basic6.0 及 Microsoft Excel （版本在 2003 以上），并准备一个 Excel 数据文件 （必须是数据格式，这里以油田某日的生产数据个别字段为例），格式如表 1所示，数据记录数 (行) 和字段数（列） 可根据需要增加。表 1数据表井号日期投产日期生产时间采油方式日产液日产油日产水桐 24- 212010- 10- 252008- 8- 1524抽油机4.690.294.4桐 24- 222010- 10- 252008- 8- 1524抽油机2.670.472.2桐 24- 232010- 10- 252008- 8- 1524抽油机2.280.042.241.2 界面设计打开 Visual Basic6.0 应用程序，新建一个工程，在 form1中添加 3 个按钮，将其 caption 属性分别设为“Excel 数据导入(OpenRowSet)”、“Excel 数据导入 (OpenDataSource)”和“Excel 数据导入 (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-172011009. 0241电脑编程技巧与维护Private Sub command1_Click ()Dim strconn As String 定义连接字符串 初始化 commandialog1 的属性 CommonDialog1.Filter = 电子表格文件 (.xls) |*.xls CommonDialog1.DialogTitle = 请选择要导入的文件 初始化记录集及连接Set rstemp = CreateObject ( ADODB.Recordset) Set conn = CreateObject ( adodb.connection) 打开连接，并加参数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,Excel 8.0;HDR =Yes;database = &CommonDialog1.FileName & ;,select * fromsheet1$)where 井号 合计 rstemp.Open strsql, conn, 1, 1 提示MsgBox 数 据 导 入 成 功 ！, vbExclamation +vbOKOnly 关闭数据库连接及记录集，释放资源 conn.CloseSet conn = Nothing rstemp.CloseSet rstemp = Nothing End Sub2.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;DataSource= (local) 为需要修改部分，分别为 SQL Server 数据库的用户名、密码、数据库名称、数据库所在机器的机器名或 IP 地址。从 Excel 文件中取得记录集的语句是 OpenRowSet ( microsoft.jet.oledb.4.0,Excel 8.0;HDR =Yes;database = & CommonDialog1.FileName & ;,select * fromsheet1$)，其中database=数据的路径和文件， sheet1$ 为工作表的名称，读者可根据实际情况进行修改，或者设置变量来存取这些内容，使程序运行不受数据文件自身因素的影响。2.1.2 写入数据库 SQL 语句strsql = select * into tablename from OpenRowSet (mi crosoft.jet.oledb.4.0,Excel 8.0;HDR =Yes;database = & Com monDialog1.FileName & ;,select * from sheet1 $ ) where 井号 合计这里用到了最简单的从一个表创建另一个表的方式，即“select * into 新表名 from 记录集 where 条件”；如果数据库有相同结构的表，可向已有表中追加数据，使用“insert into 已有的表名 select * from 记录集 where 条件”，另外也可以根据 需要写出更加复杂的 sql 语句，这里不再详述。2.2OpenDataSource 方法2011. 0242双击“Excel 数据导入 (OpenDataSource)”按钮，打开代码编辑器， 将 command1_click 中的代码拷贝到 Private Subcommand2_Click () 事件代码中，并将 strsql=语句修改为以下代码，strsql = select * from OpenDataSource ( Microsoft.Jet. OLEDB.4.0, Data Source = & CommonDialog1.FileName & ;Extended properties=Excel 8.0) . Sheet1$ where 井号 合计 此种方法与上一种的不同之处在于这里使用了 OpenDataSource 的方法来取得记录集，该方法的语法详细说明如下：OPENDATASOURCE( provider_name, init_string )provider_name：注册为用于访问数据源的 OLE DB 提供程序的 PROGID 的名称。provider_name 的数据类型为字符型，没有默认值。init_string：连接字符串，这些字符串将要传递给目标提 供程序的 IDataInitialize 接口。提供程序字符串语法是以关键 字值对为基础的，这些关键字值对由分号隔开。在本例中 init_string 是 Data Source 和 Extended properties 的组合，设置了数据源文件的路径和提供程序特定的连接字符串。2.3 OpenSchema 方法上述两种方法一般用于 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=Excel 8.0cn.Open strconnSet rstschema = cn.OpenSchema (adSchemaTables) 设置 Excel 记录集 循环将 Excel 数据文件中的各个数据表写入数据库Do Until rstschema.EOFstrsql = insert into & strtemp & .rukc select * from & rstschema! TABLE_NAME & cn.Execute strsql, lngRecsAff, adExecuteNoRecords MsgBox 成功导入- & CommonDialog1.FileName & - 文件中 - & rstschema! TABLE_NAME & -的数据到 SQL 数据库中！ , vbExclamation + vbOKOnly rstschema.MoveNextDATABASE AND INFORMATION MANAGEMENT数据库与信息管理Loop 释放资源 rstschema.Close cn.CloseSet cn = NothingSet rstschema = Nothing End Sub这种方法的优势就在于它可以自动读取 Excel 工作簿中各工作表名称，循环将本地 Excel 数据批量导入到远程的 SQLServer 数据库。2.3.1 利用 ODBC 连接远程数据库strtemp = odbc;Driver = SQL Server ;Server =10.78. 173.77;Database=scsjk;UID=test;PWD=test2.3.2 多工作表数据循环入库Set rstschema = cn.OpenSchema (adSchemaTables) 设置 excel 记录集 循环将 Excel 数据文件中的各个数据表写入数据库Do Until rstschema.EOFstrsql = insert into & strtemp & .rukc select * from & rstschema! TABLE_NAME & cn.Execute strsql, lngRecsAff, adExecuteNoRecords MsgBox 成功导入- & CommonDialog1.FileName& -文件中- & rstschema! TABLE_NAME & -的 数据到 SQL 数据库中！ , vbExclamation + vbOKOnlyrstschema.MoveNext Loop其中 rukc 为数据库中事先建的同结构的数据表名称，rstschema! TABLE_NAME 为调用的当前工作表的名称。3 结语详述了在 VB6 环境下，用代码实现常用办公数据文件 Excel 批量导入 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 = 55 广播方式发送.Protocol = sckUDPProtocol 使用 UDP 协议End With实现“远程唤醒”功能的主要代码如下：Dim macaddr,i,j,kDim mac (101)As Bytemac 为字节数组，用来构造远程唤醒数据包Open Configlist For Input As #1 打开 list 文件 Do While Not EOF (1)Line Input #1, macaddr For i = 0 To 5 构造唤醒包的起始部分 FF-FF-FF-FF-FF-FFmac (i) = 255Next imac ( 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 (