另一篇值得参考的文章:https://www.cnblogs.com/ericli/p/6806080.html
其他文件资料:
文章来源:https://blog.csdn.net/iamlaosong/article/details/12617757
网络应用程序一般有两种结构形式,就是所谓的C/S结构和B/S结构,通过Excel和数据库的配合,可以实现简单C/S结构的应用程序,客户端只要安装数据库客户端和Excel应用即可。下面介绍VBA实现Excel和Oracle数据库交换技术的关键点。
1、数据库连接
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=msdaora;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"
这儿的DL580是oracle客户端配置的网络服务名称,配置放在tnsnames.ora文件中,如:
DL580 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 130.81.100.136)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
2、将Excel数据更新或插入到数据库表中
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
sqls = "connect database"
cnn.Open "Provider=msdaora;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"
OraOpen = True '成功执行后,数据库即被打开
'If OraOpen Then lineno = [D65536].End(xlUp).Row Else lineno = 0 '行数
For i = Minfield To Maxfield
myrecord(i) = Worksheets("客户录入").Cells(i, 2)
Next i
myrecord(i) = Now()
sqls = "select count(*) from EMSAPP_MAP_TABLE where CLIENT_ID ='" & myrecord(Minfield) & "'"
Set rst = cnn.Execute(sqls)
If rst(0) > 0 Then
'ID已经存在,更新客户资料
i = Minfield + 1
sqls = "update EMSAPP_MAP_TABLE set CLIENT_NAME='" & myrecord(i) & "',"
sqls = sqls & "CLIENT_ADDR='" & myrecord(i + 1) & "',"
sqls = sqls & "PROD_FLOW = '" & myrecord(i + 2) & "',"
sqls = sqls & "UNIT_SCALE ='" & myrecord(i + 3) & "',"
sqls = sqls & "DEMAND_EXPRESS='" & myrecord(i + 4) & "',"
sqls = sqls & "MAIL_SITUATION='" & myrecord(i + 5) & "',"
sqls = sqls & "UNIT_PROP='" & myrecord(i + 6) & "',"
sqls = sqls & "INDUSTRY_CAT='" & myrecord(i + 7) & "',"
sqls = sqls & "DELIVERY_MODE='" & myrecord(i + 8) & "',"
sqls = sqls & "COLLECT_MODE='" & myrecord(i + 9) & "',"
sqls = sqls & "RESPONSE_MODE='" & myrecord(i + 10) & "',"
sqls = sqls & "SERVICE_MODE='" & myrecord(i + 11) & "',"
sqls = sqls & "MAIL_COMPETOR='" & myrecord(i + 12) & "',"
sqls = sqls & "CONTACT_NAME='" & myrecord(i + 13) & "',"
sqls = sqls & "CONTACT_PHONE='" & myrecord(i + 14) & "',"
sqls = sqls & "REMARK='" & myrecord(i + 15) & "' where CLIENT_ID = '" & myrecord(Minfield) & "'"
Else
sqls = "insert into EMSAPP_MAP_TABLE (CLIENT_ID,CLIENT_NAME,CLIENT_ADDR,PROD_FLOW,UNIT_SCALE,DEMAND_EXPRESS,"
sqls = sqls & "MAIL_SITUATION,UNIT_PROP,INDUSTRY_CAT,DELIVERY_MODE,COLLECT_MODE,RESPONSE_MODE,SERVICE_MODE,"
sqls = sqls & "MAIL_COMPETOR,CONTACT_NAME,CONTACT_PHONE,REMARK,CREATE_DATE) values ('"
For i = Minfield To Maxfield
sqls = sqls & myrecord(i) & "','"
Next i
sqls = sqls & myrecord(i) & "')"
End If
Set rst = cnn.Execute(sqls)
3、将表中的数据读入Excel工作表中
(1) 直接引用记录集
记录集中字段的引用可以通过字段名,如rst(“CLIENT_ID”),也可以通过序号如rst(2),注意,序号从0开始,rst(2)表示第3个字段。多条记录时通过rst.movenext进行移动,在默认情况下,当打开记录集,为向前指针,只能用MoveNext方法向前单向移动指针,其他操作不受支持。
Set cnn = CreateObject(“ADODB.Connection”)
Set rst = CreateObject(“ADODB.Recordset”)
sqls = “connect database”
cnn.Open "Provider=msdaora;Data Source=dl580;User Id=emssxjk;Password=emssxjk;"
OraOpen = True '成功执行后,数据库即被打开
'If OraOpen Then lineno = [D65536].End(xlUp).Row Else lineno = 0 '行数
sqls = "select CLIENT_ID,CLIENT_NAME,CLIENT_ADDR,PROD_FLOW,UNIT_SCALE,DEMAND_EXPRESS,MAIL_SITUATION,"
sqls = sqls & "UNIT_PROP,INDUSTRY_CAT,DELIVERY_MODE,COLLECT_MODE,RESPONSE_MODE,SERVICE_MODE,MAIL_COMPETOR,"
sqls = sqls & "CONTACT_NAME,CONTACT_PHONE,REMARK,CREATE_DATE from EMSAPP_MAP_TABLE where CLIENT_ID ='" & id & "'"
Set rst = cnn.Execute(sqls)
'MsgBox sqls
If Not (rst.EOF) Then
For i = Minfield To Maxfield
Worksheets("客户录入").Cells(i, 2) = rst(i - Minfield) '记录集rst()下标从0开始
Next i
Worksheets("客户录入").Cells(Maxfield, 3) = "老客户,创建时间:" & rst(i - Minfield)
msg = MsgBox("成功读取老客户资料!", vbOKOnly, "iamlaosong")
Else
msg = MsgBox("老客户资料不存在!", vbOKOnly, "iamlaosong")
End If
(2) 将记录集保存到表中
‘ 数据读入到名字由name变量指定的工作表中
Set rst = cnn.Execute(sqls)
Maxrow = Sheets(name).[A65536].End(xlUp).Row + 1
Sheets(name).Range(“a3:” & field & Maxrow).ClearContents
Sheets(name).Range(“a3”).CopyFromRecordset rst
4、复杂功能用存储过程实现,结果保存到表中以便读取。
存储过程的调用参考:http://blog.csdn.net/iamlaosong/article/details/9306977
附:Recordset记录集对象的属性
Source 指示记录集对象中数据的来源(命令对象名或SQL语句或表名)
ActiveConnection 连接对象名或包含数据库的连接信息的字符串
CursorType 记录集中的指针类型,可选,见表6-16
LockType 锁定类型,可选,见表6-17
MaxRecors 控制从服务器获取的记录集的最大记录数
CursorLocation 控制数据处理是在客户端还是在服务器端
Filter 控制要显示的内容
Bof 记录集的开头
Eof 记录集的结尾
RecordCount 记录集总数
PageSize 分页显示时每一页的记录数
PageCount 分页显示时数据页的总页数
AbsolutePage 当前指针所在的数据页
AbsolutePosition 当前指针所在的记录行
Recordset记录集对象常用属性具体说明如下。
1.Source
用于设置数据库的查询信息,查询信息可以是命令对象名或SQL语句或表名,语法如下:
rs.Source=查询信息
2.ActiveConnection
用于设置数据库的连接信息,连接信息可以是连接对象名或包含数据库的连接信息的字符串,语法如下:
rs.ActiveConnection=连接信息
3.CursorType
用于设置记录集指针类型,取值参见表6-17,语法如下:
rs.CursorType=值
默认为0,即指针只能前移。如果要让指针自由移动,一般设为键盘指针1。
4. LockType
用于设置记录集的锁定类型。取值参见表6-18,语法如下:
rs.LockType=值
默认为1,只读属性。如果要利用记录集对象进行添加、删除、更新等操作,只能由一个用户修改,一般需要设置其属性为2。
5.MaxRecors
用于设定从服务器上得到的最大记录数,语法如下:
rs.MaxRecors=最大记录数
通常这种方法不常用,而是使用SQL语句来指定所得到的最大记录数,”Select Top 整数…”。
6.CursorLocation
用于设置数据处理是在客户端还是在服务器端。取值参见表6-21,语法如下:
rs.CursorLocation =值
表6-21 CursorLocation参数值
参数
值
说明
AdUseClient 1 客户端处理
AdUseServer 2 服务器端处理
AdUseClientBatch 3 动态处理,在客户端处理,处理时连接切断,处理完毕重新连接
有的时候,为了减轻服务器的工作负担,可以根据情况的需要,设置在客户端处理。
7.Filter
用于设置要显示的内容。取值参见表6-22,语法如下:
rs.Filter =值
表6-22 Filter参数值
参数
值
说明
AdFilterNone 0 显示所有数据
AdFilterpendRecords 1 只显示没有修改过的数据
AdFilterAffectedRecords 2 只显示最近修改过的数据
AdFilterFetchedRecords 3 只显示暂存于客户端缓存中的数据
8.Bof
用于判断当前记录指针是否在记录集的开头,如在开头,返回True,否则返回Falsh。如果记录集为空,也返回True。
9.EOF
用于判断当前记录指针是否在记录集的结尾,如在结尾,返回True,否则返回Flash。如果记录集为空,也返回True。
记录集有两个特殊位置:Bof和 EOF。Bof表示记录集的开头,位于第一条记录之前;EOF表示记录集结尾,位于最后一条记录之后。Bof 为 True,当前指针指到 RecordSet 的第一条记录; Eof 为 True,当前指标指到 RecordSet 的最后一条记录。如果记录集不为空,指针可以在Bof、所有记录和Eof移动。如果记录集为空,此时指针同时指向Bof和 Eof,它们的值均为True。
具体判断如下:若当前记录的位置是在一个 Recordset 对象第一行记录之前时, Bof 属性返回 True,反之则返回 False。;若当前记录的位置是在一个 Recordset 对象最后一行记录之后时,Eof 属性返回 True,反之则返回 False;Bof 与 Eof 都为 False,表示指标位于 RecordSet 的当中;Bof 与 Eof 都为 True,在 RecordSet 里没有任何记录。
从以上可知,通过检验 Bof 与 Eof 属性,可以得知当前指针所指向的 RecordSet 的位置,使用 Bof 与 Eof 属性,可以得知一个 Recordset 对象是否包含有记录或者得知移动记录行是否已经超出该 Recordset 对象的范围。
判断记集录是否为空的代码如下:
< % if not rs.bof and rs.eof then ‘ 如果不是开头,也不是结尾,则执行 … End if %>
循环输出记录集记录的代码如下:
< % Do while not rs.eof ‘ 如果没有到达记录集未尾,则循环输出下面的记录 … Rs.MoveNext Loop %>
10.RecordCount
用于返回记录集中的记录总数。我们常用 RecordCount 属性来找出一个 Recordset 对象包括多少条记录。例如:
< % Rsponse.Write rs.RecordCount %>
要注意的是,使用RecordCount必须设置指针类型为键盘指针1或是静态指针3。
11.PageCount
用于设置分页显示时数据页的总数。使用 PageCount 属性,决定 Recordset 对象包括多少“页”的数据。这里的“页”是指数据记录的集合,大小等于 PageSize 属性的设定,即使最后一页的记录数比 PageSize 的值少,最后一页也算是 PageCount 的一页。
12. PageSize 属性
用于设置分页时每一页所显示的记录数。PageSize 属性是决定 ADO 存取数据库时如何分页显示的关键,使用它就可以决定多少记录组成一个逻辑上的“1页”。
第10、11、12条属性通常是用于分页显示,具体运用见下面的范例。
13.AbsolutePage
用于设置当前指针位于哪一页。语法如下:
rs.AbsolutePage =整数值
14.AbsolutePosition
用于设置当前指针所在的记录行。语法如下:
rs.AbsolutePosition =整数值
后面的这些属性使用时,一般需要设置指针类型为键盘指针1。
文章来源:https://blog.csdn.net/iamlaosong/article/details/18043433
▲连接Oracle数据库
Set cnn = CreateObject(“ADODB.Connection”)
cnn.Open “Provider=msdaora;Data Source=dl580;User Id=emssxjk;Password=emssxjk;”
dl580是tnsnames.ora中配置的服务名。
▲创建记录集recordset
Set rst = CreateObject(“ADODB.Recordset”)
sqls = “select * from tb_city”
Set rst = cnn.Execute(sqls)
注意:Set rst = cnn.Execute(sqls)如果用rst.Open sqls, cnn, adOpenKeyset, adLockOptimistic代替,效果更好。
这儿的SQL语句除了查询语句select以外,也可以用insert、update、delete等命令,也可以用truncate table这样的命令。这些SQL语句也可以用command对象完成。command对象还可以进行存储过程的调用并传递参数。
▲记录集结果的引用
Excel VBA 通过ADO取到数据后,有如下引用方式(假定记录集名字为rst):
1、字段数量:rst.fields.count;
2、字段名称:rst.fields(0).name,0表示第一个字段,其它字段分别是1,2,…rst.fields.count-1;
3、字段值:rst(0)或者rst(0).Value或者rst.fields(0).value,0表示第一个字段,也可以用字段名代替,如:rst(“city”);
4、整个记录集: Range(“a2”).CopyFromRecordset rst命令将记录集保存到A2单元格开始的工作表中;
5、记录移动:取数后记录定位在第一条,rst.movenext可以移动到下一条,结尾标志:rst.eof = true,Excel VBA只支持向前移动(rst.movenext);例如:
Do While Not rst.EOF()
k = k + 1
sku(k, 1) = rst(0)
sku(k, 2) = rst(1)
rst.MoveNext
Loop
6、记录数:rst.RecordCount是反馈记录数的,是否有值要看SQL语句的执行方式。
执行SQL语句有三种方式,一种是cnn. Execute,这种方式比较适合无返回记录的语句,即DML语句。如果执行有返回记录的SQL语句,也可以取到记录,只是RecordCount总是反馈-1。这种情况下可以根据rst.eof判断有无查询结果,如果rst.eof= true就表示查询结果为空。另一种方式是rst.Open,这个适合有返回记录的语句,即select语句,因为这种方式能够返回记录数RecordCount。当然还有第三种方式,就是用command,这个比较适合执行存储过程,因为这种方式可以传递参数。三种方式command方式功能最强,用起来也最麻烦,connection最弱,用起来也最简单。
▲调用存储过程
1、连接数据库,如前;
2、定义存储过程
Set cmd = CreateObject(“ADODB.Command”)
Set cmd.ActiveConnection = cnn
cmd.CommandText = “zfqf_bag2mail” ‘存储过程名称,有两个参数
cmd.CommandType = adCmdStoredProc
注意:CommandType的值决定了CommandText内容的类型,其取值定义如下:
‘—- CommandTypeEnum Values —-
‘Const adCmdUnknown = &H8
‘Const adCmdText = &H1
‘Const adCmdTable = &H2
Const adCmdStoredProc = &H4
‘Const adCmdFile = &H100
‘Const adCmdTableDirect = &H200
3、参数赋值
cmd.Parameters(0).Value = Cells(row1, pos_acc)
cmd.Parameters(1).Value = Cells(row1, pos_lab)
4、执行存储过程
cmd.Execute
这个存储过程的结果保存在表中,通过查询语句得到结果,没有通过变量返回。
===================================
附:ADODB.RecordSet常用方法
rs = Server.CreateObject(“ADODB.RecordSet”)
rs.Open(sqlStr,conn,1,A)
注:A=1表示读取数据;A=3表示新增、修改或删除数据。
在RecordSet组件中,常用的属性和方法有:
rs.Fields.Count:RecordSet对象字段数。
rs(i).Name:第i个字段的名称,i为0至rs.Fields.Count-1
rs(i):第i个字段的数据,i为0至rs.Fields.Count-1
rs(“字段名”):指定字段的数据。
rs.Record.Count:数据记录总数。
rs.EOF:是否最后一条记录。
rs.MoveFirst:指向第一条记录。
rs.MoveLast:指向最后一条记录。
rs.MovePrev:指向上一条记录。
rs.MoveNext:指向下一条记录。
rs.GetRows:将数据放入数组中。
rs.Properties.Count:ADO的ResultSet或Connection的属性个数。
rs.Properties(item).Name:ADO的ResultSet或Connection的名称。
rs.Properties:ADO的ResultSet或Connection的值。
rs.close():关闭连接。