VB实现EXCEL数据导入SQL SERVER方法解析.PDF.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 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数据准备 为了使示例能够正常运行 需要在本机上安装 Microsoft SQL Server2000 数据库 Visual Basic6 0 及 Microsoft Excel 版 本在 2003 以上 并准备一个 Excel 数据文件 必须是数据格 式 这里以油田某日的生产数据个别字段为例 格式如表 1 所示 数据记录数 行 和字段数 列 可根据需要增加 表 1数据表 井号日期投产日期 生产时间采油方式日产液日产油日产水 桐24 21 2010 10 252008 8 1524抽油机4 690 294 4 桐24 22 2010 10 252008 8 1524抽油机2 670 472 2 桐24 23 2010 10 252008 8 1524抽油机2 280 042 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 1 OpenRowSet 方法 收稿日期 2010 11 17 2011009 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 adodb connection 打开连接 并加参数 Strconn Provider SQLOLEDB 1 Persist Security Info False UserID sa password qaz InitialCatalog 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 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 Excel 8 0 HDR Yes database select from sheet1 其中 database 数据的路径和文件 sheet1 为工作表的名称 读 者可根据实际情况进行修改 或者设置变量来存取这些内容 使程序运行不受数据文件自身因素的影响 2 1 2 写入数据库 SQL 语句 strsql select into tablename from OpenRowSet mi crosoft jet oledb 4 0 Excel 8 0 HDR Yes database 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 Extended properties Excel 8 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 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 SQLServer Server 10 78 173 77 Database scsjk UID test PWD test 设置 Excel 数据连接 strconn Provider Microsoft Jet OLEDB 4 0 Data Source Extended Prop erties Excel 8 0 cn Open strconn Set rstschema cn OpenSchema adSchemaTables 设置 Excel 记录集 循环将 Excel 数据文件中的各个数据表写入数据库 Do Until rstschema EOF strsql insert into 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 0 To 5 构造唤醒包的起始部分 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 V