1. 引言
Visual Basic for Applications (简称VBA)是新一代标准宏语言[1]，基于Visual Basic for Windows发展而来的。VBA与传统的宏语言不同，传统的宏语言不具有高级语言的特征，没有面向对象的程序设计概念和方法[2]。而VBA提供了面向对象的程序设计方法，提供了相当完整的程序设计语言[3]。
在具有一定规模的测绘和地理信息工程项目实施过程中，技术人员日常处理数据基本依赖于Excel [4]工作簿及其工作表作为生产的数据入口，而专业管理软件对数据库的要求则更趋于诸如Oracle关系数据库[5]以满足生产成果作为管理要素的出口，以便政府部门、专业机构在日常管理、生产经营中使用。诸多测绘专业部门如今还停留在劳动密集型的操作模式，还未能在实际工程实施过程中实现自动化操作。本文针对上述问题，在实际项目实施过程中，就如何在Excel与Oracle之间建立起数据批量导入与导出的桥梁等关键方法做了有益的探索。
利用VBA开发工具编写宏[6]，建立Oracle数据库表结构、从服务器上下载Oracle已建立的表、将PC机上的Excel表批量导入Oracle数据库等执行模块[7]，是一种经济高效的技术方法。
本文结合实景三维资产管理平台开发过程中，利用Excel表对服务器Oracle数据库批量建立表结构、批量下载数据表、批量更新Oracle数据库等，提供了具体技术方法和代码。
2. 环境配置
2.1. 系统环境
2.1.1. 服务器端软件
服务器操作系统Windows Server 2012 R2，数据库Oracle Database 11g发行版2。Oracle数据库端口号设置为1521，数据库名称为ORCL。
2.1.2. PC端软件
Windows 10 64bit操作系统，Oracle_win64_11gR2_client完整客户端64bit，Excel 2019 64bit。
2.2. Oracle客户端安装
Figure 1. Oracle client installation parameter selection
图1. Oracle客户端安装参数选择
Oracle客户端安装必须对应自己操作系统版本的完整版客户端软件。在步骤1/6中，选择“管理员”，如图1。
数据库配置好之后，使用PLSQL Developer或者Navicat等连接工具，测试数据库是否正常登录。可以用sqlplus password/username @ serverName: 1521/ORCL CMD命令检查，确定正常登录后开展后续工作。
2.3. 配置ODBC驱动
打开Windows“控制面板”里的“管理工具”“ODBC数据源(64位)”，在“驱动程序”栏里可以看到对应版本的Oracle ODBC驱动，Oracle in OraClient11g_home1是本文的驱动版本号为11.02.00.01。
选择“用户DSN”，点击“添加”，在“创建新数据源”下拉框里找到Oracle in OraClient11g_home1，双击Oracle in OraClient11g_home1弹出“Oracle ODBC Driver Configuration”对话框，“Data Source Name”和“Description”是对数据源命名和描述；“TNS Server Name”是数据库连接名称，设置时，服务IP后用冒号连接端口号，用正斜杠连接“ORCL”；“User ID”是数据库的用户名。
ODBC驱动配置完成后，点击“Test Connection”，配置正确会给出“Connection Successful”连接成功，如图2。
Figure 2. Oracle ODBC driver parameter configuration
图2. Oracle ODBC驱动参数配置
2.4. VBE配置
在Excel里需要先行对VBE中启动数据库连接支持。选择“开发工具”–“Visual Basic”–“工具”–“引用”，在弹出的引用窗口中，找到“Microsoft ActiveX Data Objects 6.1 Library”和“Microsoft ActiveX Data Objects Recordset 2.8 Library”二个实体库，勾选确定，如果没有上述二两个版本，选择最高的版本号。
2.5. 处理汉字不显示
VBA从服务器端下载Oracle数据至Excel表后，由于字库等原因，所有的汉字都是问号。
用“控制面板”–“系统和安全”–“编辑系统环境变量”–“系统和安全”–“系统属性”，选择“高级”–“环境变量”–“系统变量”，对变量“Path”新建系统变量，“变量名”为NLS_LANG，“变量值”为AMERICAN_AMERICA.ZHS16GBK。
3. VBA建立Oracle数据库表结构
3.1. Excel表编制Oracle表结构
根据Oracle表结构建立规范，建立Oracle数据库表结构的要素有：名称，是否虚拟，类型，是否可为空，默认/表达式，存储，注释，主键，如表1。
利用Excel表，对管理对象以上述8个要素建立表单，Oracle数据类型有：数值类型、字符类型、日期类型、clob和blob类型、rowid类型。其中，varchar2为变长字符串。clob为变长的字符串大对象，clob为变长的二进制大对象，最长可达4 GB。
Table 1. Schematic table of Oracle database structure
表1. Oracle数据库结构示意表
名称 |
虚拟 |
类型 |
空值 |
默认/表达式 |
存储 |
注释 |
主键 |
USERNAME |
N |
VARCHAR2 (500) |
Y |
|
|
使用人 |
|
CLASS_1 |
N |
VARCHAR2 (500) |
Y |
|
|
一类名称 |
|
CLASS_2 |
N |
VARCHAR2 (500) |
Y |
|
|
二类名称 |
|
CLASS_3 |
N |
VARCHAR2 (500) |
Y |
|
|
三类名称 |
|
GNQMJ |
N |
NUMBER |
Y |
|
|
分户建筑面积 |
|
SFCZ |
N |
VARCHAR2 (500) |
Y |
|
|
是否出租 |
|
SFDY |
N |
VARCHAR2 (500) |
Y |
|
|
是否抵押 |
|
PARCELID |
N |
VARCHAR2 (500) |
Y |
|
|
宗地唯一值 |
|
HOUSEID |
N |
VARCHAR2 (500) |
N |
|
|
功能区唯一值 |
BKEY |
3.2. 链接服务器Oracle数据库
定义数据库连接对象(ADODB. Connection)变量conn，该对象用于与ODBC数据库建立连接，所有对数据库的操作均通过该连接进行。
定义ActiveX Data Objects (ADO)库Recordset对象模型变量rs，该变量在应用程序中执行数据库查询、插入、更新和删除操作
定义ADO Command对象变量cmd，Command对象用于执行面向数据库的一次诸如创建、添加、取回、删除或更新记录等动作。
定义FileDialog对象fd，FileDialog对象允许用户通过对话框选择文件、文件夹、打开文件、保存文件等功能。
定义Variant数据类型变量selectedFile，Variant是所有没被显式Dim、Private、Public或Static等声明的其他类型变量的数据类型。
定义Drv、serverName、userName、password等变量为字符型。
对象赋值：驱动版本号Drv = "Driver = {Oracle in OraClient11g_home1};"；
Oracle服务器地址serverName = "Dbq = 218.93.44.44:1521/ORCL;"；
用户名userName = "Uid = cztn;"；
密码password = "Pwd=cbbbztn;"；
链接超时处理timeout = "QTO = F;"；
conn = New ADODB. Connection；rs = New ADODB. Recordset；cmd = New ADODB. Command。
创建ADO连接对象strConn = Drv + serverName + userName + password + timeout。
打开数据库conn. Open strConn，如果数据库连接成功conn. State = adStateOpen，弹窗提示MsgBox ("数据库连接成功！" & vbCrLf &"数据库状态："& conn. State & vbCrLf &"数据库版本："& conn. Version)
3.3. VBA建立Oracle数据库表结构
选择EXCEL工作簿，该表为ORACLE库表结构，根据该表建立ORACLE表，表名为该工作簿sheet1的表名。
判断Oracle数据库里是否存在要建立的表结构，如果该表存在就删除该表。要删除表先对其进行查询，SQL查询语句为sql = "SELECT table name FROM user tables WHERE table name = '" & Mytablename & "'", rs. Open sql, conn执行查询。
删除表结构的SQL查询为sql = "DROP TABLE " & Mytablename，conn. Execute sql执行删除。
利用循环语句建立Oracle数据库结构的SQL语句，表3-1建立的表结构SQL语句为：CREATE TABLE MyOraclename (USERNAME VARCHAR2 (500), CLASS_1 VARCHAR2 (500), CLASS_2 VARCHAR2 (500), CLASS_3 VARCHAR2 (500), GNQMJ NUMBER, SFCZ VARCHAR2 (500), SFDY VARCHAR2 (500), PARCELID VARCHAR2 (500), BUILDINGID VARCHAR2 (500), FLOORID VARCHAR2 (500), HOUSEID VARCHAR2 (500) NOT NULL CONSTRAINT BHOUSEKEY PRIMARY KEY)
4. VBA批量更新Oracle数据库
本文将资产管理相关要素的6张表存放在一个工作簿中，工作表名称为：“TNCFCZJBXX”、“TNCFCZMXB”、“TNCFDZJBXX”、“TNCFHOSUSE”、“TNCFXSJBXX”和“TNCFPARCEL”，赋值给tablename (i)。
在批量更新Oracle数据库表前，对被更新的数据表进行备份后，删除对应表里的所有记录，对要求更新部分数据表进行对应数据更新。本文对“TNCFPARCEL”表里的宗地面积进行更新。
4.1. 对Oracle数据库备份
利用日期与原表名组合进行同名表备份，Dda = Format (DateValue (Now), "yyyy-mm-dd"，表名为tablenamedate (i)，其值为tablename (i) & Dda。对备份表进行同名检测，如果Excel里已有tablenamedate (i)则将其删除。
For Each ws In wb.Sheets
If ws.Name = tablenamedate(i) Then
ws.Delete
End If
Next ws
打开Oracle数据库相对应的表，对表里全部记录进行选取。
Ename = tablename(i)
rs.Open "select * from " & Ename, conn
wb.Sheets(tablenamedate(i)).Select
将选取的记录复制到对应的Excel里相应的位置。
Range("A2").CopyFromRecordset rs。
4.2. 整体更新Oracle数据库
4.2.1. 删除Oracle数据库对应表记录
批量更新Oracle数据库前，删除对应表里的数据记录，建立SQL语句时用TRUNCATE TABLE来解决。queryString = "TRUNCATE TABLE " & Ename，配置命令对象：
With cmd
.ActiveConnection = conn
.CommandText = queryString
.CommandType = adCmdText
End With
执行cmd.Execute，完成删除。
4.2.2. 批量更新Oracle数据库
对Excle记录内容遍历，按记录构建SQL语句时用INSERT INTO来解决。
For i = 2 To sheet.Cells(sheet.Rows.Count, 1).End(xlUp).Row
strSQL = "INSERT INTO " & Ename & " VALUES("
For j = 1 To sheet.Cells(1, sheet.Columns.Count).End(xlToLeft).Column
fieldvalue = sheet.Cells(i, j).Value
strSQL = strSQL & "'" & fieldvalue & "'" & ","
Next j
strSQL = Left(strSQL, Len(strSQL) - 1)
strSQL = strSQL & ")"
With cmd
.ActiveConnection = conn
.CommandText = strSQL
.CommandType = adCmdText
End With
cmd.Execute
next i
4.2.3. 更新Oracle数据库特殊字段
对数据库里单独更新特殊字段的值，在建立SQL语句时用update来解决。
遍历每一行，构建SQL语句：
For i = 2 To sheet.Cells(sheet.Rows.Count, 1).End(xlUp).Row
prcnu(i) = Trim(Cells(i, 2))
area(i) = Cells(i, 39).Value
Next i
surecord = sheet.Cells(sheet.Rows.Count, 1).End(xlUp).Row
For i = 2 To surecord
strSQL = "update " & Ename & " set KZMJ = " & area(i) & " where ZCMC=" & "'" & prcnu(i) & "'"
Next i
5. 结语
在诸多测绘地理信息工程里，对数据的处理基本依附于Excel表，工程最终必须有一定方式系统地管理数据。利用VBA开发技术，建立起数据处理成果与数据库批量更新，在实践中有重要的现实意义和经济价值。