文档章节

Excel中用VBA链接Oracle实现位置随意标题

sdvdxl
 sdvdxl
发布于 2016/10/29 17:46
字数 1270
阅读 94
收藏 0

我用的是Oracle 11g + Microsoft Office 2010 旗舰版 

首先要添加引用类库:

  Microsoft ActiveX Data Objects Recordset 6.0(或者2.8)Library

  Microsoft Activex Data Objects 6.1(或者2.8) Library

 

链接Oracle的字符串有三种,而我试验了几次两种,一种是odbc,另一种是ado方式

这两种链接方式如下:

odbc: 

"DSN=orcl;UID=scott;PWD=tiger;DBQ=ORCL;DBA=W;APA=T;EXC=F;

FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;

NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"

ado:

"Provider = OraOLEDB.Oracle;Persist Security Info=true;User ID = scott;Password = whg;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.178.168)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Orcl)))"

其中odbc只能实现链接本机数据库,不能远程,而ado方式可以实现远程连接,只需把host后面的ip改成目标地址即可。

做了一个活动标题的excel vba例子,所谓活动标题,是位置比较随意,可以互换位置,但中间不能有空的单元格,如果表头中有“序列”的话,可以自动编号。下面就贴上代码

VBA代码


  
  1. Option Explicit 
  2. Public Const DATA_START_ROW As Byte = 4 '数据起始位置 
  3. Public fieldsCount As Integer 
  4. Public fieldsZH() As String '中文名称 表头 
  5. Public fieldsEN() As String '英文名称,数据库字段 
  6. Public fieldsType() As String '字段类型 
  7.  
  8. '初始化字段值 
  9. Sub initFields() 
  10.     Dim i As Integer 
  11.     ThisWorkbook.Sheets(1).Activate 
  12.     With Range("A1").CurrentRegion 
  13.         fieldsCount = .Rows.Count 
  14.     End With 
  15.      
  16.     ReDim fieldsZH(fieldsCount - 1) 
  17.     ReDim fieldsEN(fieldsCount - 1) 
  18.     ReDim fieldsType(fieldsCount - 1) 
  19.      
  20.     For i = 0 To fieldsCount - 1 
  21.         fieldsZH(i) = Cells(i + 1, 1) 
  22.         fieldsEN(i) = Cells(i + 1, 2) 
  23.         fieldsType(i) = Cells(i + 1, 3) 
  24.     Next 
  25. End Sub 
  26.  
  27. Option Explicit 
  28.  
  29. '定义链接属性 
  30. Dim conn As ADODB.Connection '################################################## 
  31. Dim rs As ADODB.Recordset '####################################### 
  32. Dim OraID As String 
  33. Dim OraUsr As String 
  34. Dim oraPwd As String 
  35. Dim serIP As String 
  36. Dim sqlStr As String 
  37.      
  38. '初始化链接属性 
  39. Sub InitConnect() 
  40.     On Error GoTo ConnectingError 
  41.     Set conn = New ADODB.Connection 
  42.     Set rs = New ADODB.Recordset 
  43.     OraID = "orcl"       'Oracle数据库的相关配置 
  44.     OraUsr = "scott"      '用户名 
  45.     oraPwd = "tiger"      '登录密码 
  46.     serIP = "127.0.0.1"   '数据库ip地址和数据困服务器名 
  47.     conn.ConnectionString = "Provider = OraOLEDB.Oracle.1;" & _ 
  48.     "Password=" & oraPwd & ";User ID=" & OraUsr & _ 
  49.     ";Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" & _ 
  50.     "(HOST=" & serIP & ")(PORT=1521))" & _ 
  51.     "(CONNECT_DATA=(SERVICE_NAME=" & OraID & ")))" 
  52.     'MsgBox conn.ConnectionString 
  53.     conn.Open 
  54.     rs.ActiveConnection = conn 
  55.     Exit Sub 
  56. ConnectingError: 
  57.     MsgBox "无法连接数据库,请检查数据库服务配置" 
  58.     Exit Sub 
  59. End Sub 
  60.  
  61. '从Excel同步到Oracle 
  62. Sub ExcelToOracle() 
  63.  
  64. End Sub 
  65.  
  66. '关闭连接 
  67. Sub CloseConnect() 
  68.     On Error Resume Next 
  69.     If Not IsEmpty(rs) Then 
  70.         rs.Close 
  71.     End If 
  72.     If Not IsEmpty(conn) Then 
  73.         conn.Close 
  74.     End If 
  75. End Sub 
  76.  
  77. '从Oracle同步到Excel 
  78. Sub OracleToExcel() 
  79.     InitConnect '初始化链接 
  80.     initFields  '初始化字段 
  81.     Dim i As Integer 
  82.     Dim j As Integer 
  83.     Dim k As Integer 
  84.     Dim excelTitleSeq() As Integer '存储表头对应的数据库字段所在位置 
  85.     Dim flag As Boolean '循环跳出标识 
  86.     Dim idSeq As Integer ' 表头中“序列”的下标 
  87.      
  88.     ThisWorkbook.Sheets(2).Activate 
  89.     sqlStr = "select * from empinfo where newdata=1" 
  90.     rs.Open Source:=sqlStr, LockType:=adLockBatchOptimistic 
  91.      
  92.     ReDim excelTitleSeq(rs.Fields.Count - 1) 
  93.     For i = 0 To rs.Fields.Count - 1 
  94.         excelTitleSeq(i) = -1 
  95.     Next 
  96.      
  97.      
  98.      
  99.     '----------------------新算法, 序列位置随意 
  100.     For i = 0 To Cells(DATA_START_ROW - 1, 1).CurrentRegion.Columns.Count - 1 '循环匹配表头 
  101.         If Cells(1, DATA_START_ROW - 1).Value = "序列" Then 
  102.             idSeq = i + 1 
  103.         End If 
  104.         flag = False 
  105.         For j = 0 To fieldsCount - 1 '依次找到对应的数据库字段的下标 
  106.             If Trim(Cells(DATA_START_ROW - 1, i + 1)) = Trim(fieldsZH(j)) Then 
  107.                 For k = 0 To rs.Fields.Count - 1 '从数据库字段中查找这个对应值 
  108.                     If UCase(Trim(fieldsEN(j))) = UCase(Trim(rs.Fields(k).Name)) Then 
  109.                         excelTitleSeq(i) = k 
  110.                         flag = True 
  111.                         Exit For 
  112.                     End If 
  113.                 Next 
  114.             End If 
  115.             If flag Then 
  116.                 Exit For 
  117.             End If 
  118.         Next 
  119.     Next 
  120.                      
  121.                  
  122.      
  123.      
  124.     '给表格赋值 
  125.     i = DATA_START_ROW 
  126.     Do Until rs.EOF 
  127.         For j = 0 To rs.Fields.Count - 1 
  128.             If idSeq <> 0 Then '判断是否有“序列” 
  129.                 Cells(i, idSeq).Value = i - DATA_START_ROW + 1 
  130.             End If 
  131.              
  132.             If excelTitleSeq(j) <> -1 Then 
  133.                 Cells(i, j + 1).Value = rs.Fields(excelTitleSeq(j)).Value 
  134.             End If 
  135.         Next 
  136.         i = i + 1 
  137.         rs.MoveNext 
  138.     Loop 
  139.     CloseConnect 
  140. End Sub 

sql语句

 


  
  1. --人员基本信息表 
  2. create table empinfo(   
  3.    email varchar2(50), --邮箱 
  4.    eno varchar2(12) unique--人员编号 
  5.    ename varchar2(20) not null--人员姓名 
  6.    eid varchar2(20) unique--身份证号码 
  7.    cardno varchar2(6) unique--卡号 
  8.    status varchar2(20), --状态 
  9.    org varchar2(50), --人员组织 
  10.    egroup varchar2(50), --组别 由group改- 
  11.    groupno varchar2(10), --组号 由组别截取第一位 
  12.    formation varchar2(25), --编制 
  13.    sex varchar2(10), --性别 
  14.    birthday varchar2(20), --出生日期 
  15.    address varchar2(100), --家庭住址 
  16.    drivetime varchar2(20), --车程 
  17.    graduate varchar2(50), --毕业院校 
  18.    major varchar2(50), --专业 
  19.    job varchar2(50), --职务 
  20.    elevel varchar2(20), --等级 由level改 
  21.    eresume varchar2(10), --简历 是否有 由resume改 
  22.    erole varchar2(50), --角色 由role改 
  23.    tutor varchar2(20), --导师 
  24.    phone varchar2(20), --电话 
  25.    tel varchar2(20), --座机 
  26.    education varchar2(20), --学历 
  27.    leveltime varchar2(20), --等级时间 
  28.    graduateyear varchar2(10), --毕业年份 
  29.    interntime varchar2(20), --见习时间 
  30.    comtime varchar2(20), --入司时间 
  31.    deptime varchar2(20), --入部门时间 
  32.    depyear varchar2(10), --入部门年度 
  33.    beforeinfo varchar2(500), --入部门前情况 
  34.    leavetime varchar2(20), --离职时间 
  35.    workinfo varchar2(500), --工作经历 
  36.    projectexpr varchar2(500), --卫生政务项目经历 
  37.    tecinfo varchar2(50), --技术认证 
  38.    certificate varchar2(10), --证书 
  39.    marriage varchar2(10), --婚姻 已婚 未婚 离异 
  40.    childyear varchar2(10), --小孩年份 
  41.    im1 varchar2(20), --及时通讯工具1 
  42.    im2 varchar2(20), --及时通讯工具2 
  43.    linkman varchar2(20), --紧急联系人 
  44.    linkmanphone varchar2(20), --紧急联系人电话 
  45.    tecdirection varchar2(50), --推荐技术方向 
  46.    homephone varchar2(20), --家庭电话 
  47.    comments varchar2(500), --备注 
  48.    newdata varchar2(1)  --最新数据标识 
  49. ); 

附件中,只实现了从Oracle导出到excel,另一个按钮功能没有实现

本文出自 “龙之谷” 博客,请务必保留此出处http://sdvdxl.blog.51cto.com/3845763/1184811

本文转载自:http://sdvdxl.blog.51cto.com/3845763/1184811

sdvdxl

sdvdxl

粉丝 1
博文 13
码字总数 3986
作品 0
杭州
程序员
私信 提问
如何利用excel中的数据源制作数据地图

关于这个问题,制作数据地图的方法已不新奇,总体来说有这么几类方案: 一类方案:直接在excel里制作 优势:个人小数据量应用较为方便简单 缺点:需要熟悉VBA,且更强大的功能对VBA水平要求较...

九月你好123
2016/07/13
91
0
推荐一款很好用的oracle,mysql,sqlserver表结构导出到word的软件

平时有时写文档,往往会遇到这样的情况,比如数据库表结构设计好,在数据库中加了 注释后,想导出到WORD,EXCEL文档,今天终于发现有个不错的工具可以实现, 实现其实是利用OFFI...

神勇小白鼠
2011/06/14
3.8K
0
用C#实现在PowerPoint文档中搜索文本

用编程的方式根据对象模型很容易实现在Word、Excel文档中搜索文本,在PowerPoint里面也同样如此,使用对象模型有助于我们了解office的文档结构。 搜索的思路和方法基本是一样的,用PowerPoin...

晨曦之光
2012/03/09
71
0
mysql和oracle的不同

一、日期处理 二、连接字符串在Oracle中用 ,SqlServer中用+,MySQL中用concat('a','b','c') 三、mysql与oracle数据库实现自增列的区别: mysql可以实现自增列,只要在建表时设置auto_incremen......

-wangming-
2016/06/06
85
0
Excel 2013中的应用程序Bing Maps

在Office 2013中添加了插入“Office应用程序”的功能。该功能将在未来成为取代VBA的一种趋势。在2003版本至2013版本中VBE编辑器均为有所变动,也说明这个产品只是在为已有的无数解决方案服务...

技术小牛人
2017/11/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

golang-字符串-地址分析

demo package mainimport "fmt"func main() {str := "map.baidu.com"fmt.Println(&str, str)str = str[0:5]fmt.Println(&str, str)str = "abc"fmt.Println(&s......

李琼涛
46分钟前
4
0
Spring Boot WebFlux 增删改查完整实战 demo

03:WebFlux Web CRUD 实践 前言 上一篇基于功能性端点去创建一个简单服务,实现了 Hello 。这一篇用 Spring Boot WebFlux 的注解控制层技术创建一个 CRUD WebFlux 应用,让开发更方便。这里...

泥瓦匠BYSocket
今天
6
0
从0开始学FreeRTOS-(列表与列表项)-3

FreeRTOS列表&列表项的源码解读 第一次看列表与列表项的时候,感觉很像是链表,虽然我自己的链表也不太会,但是就是感觉很像。 在FreeRTOS中,列表与列表项使用得非常多,是FreeRTOS的一个数...

杰杰1号
今天
4
0
Java反射

Java 反射 反射是框架设计的灵魂(使用的前提条件:必须先得到代表的字节码的 Class,Class 类 用于表示.class 文件(字节码)) 一、反射的概述 定义:JAVA 反射机制是在运行状态中,对于任...

zzz1122334
今天
5
0
聊聊nacos的LocalConfigInfoProcessor

序 本文主要研究一下nacos的LocalConfigInfoProcessor LocalConfigInfoProcessor nacos-1.1.3/client/src/main/java/com/alibaba/nacos/client/config/impl/LocalConfigInfoProcessor.java p......

go4it
昨天
8
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部