基于Oracle数据库的Excel报表系统的开发

1-5基于Oracle数据库的Excel报表系统的开发张玉婷马涛（衡水供电公司，河北衡水，053000）摘要：本文介绍了基于Oracle数据库的Excel报表系统的开发。衡水电网调度自动化系统采用Oracle数据库存储电网运行数据，对数据的访问只能通过编写Oracle脚本实现。为满足企业各部门对数据的需求，方便一般应用人员的使用，在原有应用软件的基础上，利用Excel软件提供的各种控件和VBA开发环境编写报表软件，实现简单界面下方便灵活的数据访问。关键词：Oracle数据库Excel软件VBA数据访问0引言衡水电网调度自动化系统的数据存储采用实时数据库与商用数据库相结合的方式，既满足了系统实时性的要求，又提高了数据存储的安全性和数据管理的方便性。实时数据库采用系统自定义的数据结构，用于存放系统运行参数、数据镜像和计算产生的中间结果。商用库采用Oracle数据库，用于存放电网运行中的各种数据，如电压、电流、有功功率、无功功率等。Oracle数据库作为专业大型数据库具有安全性高、功能丰富的特点，但它要求使用人员具备一定的数据库专业知识，对于调度自动化系统的一般使用人员来讲存在一定困难。同时企业各部门对数据的需求具有相对固定性和周期性。对于这些具有相对规律性的数据访问是否可以采用一种更简单直观的方式实现，从而脱离复杂的Oracle脚本编程。为此，我们提出了“基于Oracle数据库的Excel报表系统的开发”。1数据需求分析随着衡水电网调度自动化水平的提高，遥测数据的准确性也不断提高，大量电网运行数据越来越成为科学调度和辅助决策的依据。而企业各部门对数据的需求主要分为以下几类：数据的月统计值。比如，各市县及变电站最大、最小负荷月报、月累计值；各电压等级电压最大值、最小值及发生时间。数据的年统计值。比如，全区及各县负荷年最大最小值及发生时间；35kV线路电流年最大值及发生时间。某一点某一时刻的数值。比如，各变电站有功功率最大时刻的无功功率。某一点日累计值年报。2Oracle数据库的数据表结构Oracle是关系型数据库管理系统，它功能强大、性能卓越，在当今大型数据库管理系统中占有重要地位。在衡水电网调度自动化系统中，选用了Oracle8.1数据库。Oracle数据库以数据表的方式存储数据，定义了三种类型的表，即数据索引表文件、统计数据表文件和历史数据表文件。数据索引表文件用于索引每个记录点的序列号，表属性包括序列号、站名、站号、点名、点号、点类型。统计数据表文件记录了每个遥测点、计算量点的统计值，表属性包括序列号、日期、最大值、最大值时间、最小值、最小值时间、累计值、峰段最大值等十六个统计属性。历史数据表文件用于存放每天288个点的历史数据，表属性包括序列号、日期和每隔5分钟的数值。Oracle数据库结构如图1所示。3Excel报表系统的开发3.1Excel软件的特点Excel软件是利用计算机进行表格类数据计算、分析的有力工具。电子表格软件诞生后，由于其图1ORACLE数据库结构Fig.1StructureofORACLEDatabasestructureDBDICTIONARYDBHISTORY2005DBHISTORY2006DBSTATISTICS2005DBSTATISTICS2006……2-5使用方便、功能实用，得到了广泛的应用，是目前重要的办公软件之一。Excel电子表格软件主要具有以下特点：（1）灵活的制表功能Excel可迅速、方便地产生出能满足各种特殊格式要求的表格，还提供了许多可自动套用的格式。（2）强大的自动计算功能通过在单元格中定义公式，Excel可自动完成繁琐的计算。而且，当公式中引用的数据发生改动时，计算结果会全部自动刷新。（3）较强的图表图形功能使用者可以将表格中的数据转换为更为直观、生动的彩色图表。此外，还可以插入或绘制图形。（4）丰富的内置函数利用Excel提供的各类函数（财务、金融、统计、数学等），用户可以解决日常业务工作中遇到的大多数问题。（5）多样化的数据分析工具和方便的数据管理工具用户可以使用Excel提供的各种数据分析工具进行数据分析，还可以方便地对表格中的数据做排序、汇总等数据管理工作。（6）完整的程序开发环境与常用控件Excel内部包含了一个叫VisualBasicforApplication（简称VBA）宏语言的编辑工具，使构造Excel下的宏驱动应用程序十分简单方便。创建宏程序可以完成一组动作，可以开发出Excel下的应用程序。它还提供了可自行定义控制参数的许多常用控件，使得界面美观，操作方便。（7）充分的数据共享能力例如，Excel可以和数据库之间交换数据，可以在网络上和他人共享工作簿，甚至可以把数据发送到互联网或者从互联网上引入外部数据。由于Excel软件具有上述特点，我们将Oracle数据库的数据查询结果以Excel表格的形式提供给数据使用者，使之可以方便地对数据作进一步的统计分析。目标明确后，我们决定充分利用Excel提供的各种控件和VBA开发环境进行报表软件的开发。使用ODBC接口将Excel与Oracle数据库相连接，通过简单的按钮点击实现批量数据的查询。同时设置相应的用户权限，禁止通过Excel报表软件直接修改数据库，保证数据存储的安全性。3.2软件设计3.2.1在进行软件详细设计之前，我们需明确几个问题：（1）系统具有灵活选择ODBC数据源的功能。电网调度自动化系统采用双机互为热备用的方式，电网运行数据同时记录在两台数据服务器中。正常情况下一台数据服务器作为主服务器响应数据请求，当主数据服务器出现故障后，另一台数据服务器可升为主数据服务器。因此Excel报表系统应具有灵活选择数据源的功能。（2）系统可以灵活设置查询时间。企业各部门的数据需求具有相对周期性，如月报、年报等，但同时也有一些非周期性的数据访问。要求系统能够灵活设置查询时间，更好地满足各种数据需求。（3）系统的一些指标。最大厂站数64有效历史数据日期范围1999/1/1–2010/12/31一天最大历史采样数据个数288实时数据有效延迟时间（可设置）实时数据有效延迟时间（最大）300秒实时数据有效延迟时间（最小）5秒实时数据有效延迟时间（默认）30秒每一厂站某类点的个数最大2048厂站索引表名dbDictionary历史数据表命名规则为dbHistory{4位的年份}3-5统计数据表命名规则为dbStatistics{4位的年份}数据库中无效数据处理为-9.9999E-20数据库中无效数据在Excel报表中处理为0.03.2.2Excel报表系统的详细设计经过上述分析之后我们进入系统的详细设计阶段。3.2.2.1报表函数的设计函数是报表系统的基本模块，每个函数实现一个相对独立的功能。比如，yhStat函数，用于取当前设定历史日期（年）的给定年统计项目值。使用VBA开发环境自定义的函数与Excel软件自带函数等同使用，通过在Excel单元格中输入相应的函数及其相关参数完成数据的查询。函数命名规则定义：(1)函数命名采用前缀+主体+后缀的方法。(2)对于单个历史数据函数，都带有”h”前缀(history)。(3)对于月有关的统计函数，带有前缀”m”(month)。(4)对于年有关的统计函数，带有前缀”y”(year)。(5)对于非系统当前设定日期时间，而由函数带有日期时间参数的函数，带有前缀”x”。(6)以r（Relative）开头的函数，起日期时间采用SCADA日期时间格式，相对于系统当前设定的日期和当前时间进行转换。(7)对于历史数据函数主体采用Val(Value)。(8)对于统计函数主体采用Stat(Statistics)。(9)对于在单个时间点上的统计平均函数主体采用Avg(Average)。(10)对于站点信息参数使用字符串的函数，后缀带有S(String)。(11)同类型函数，由于参数的类型不同，其前缀和主体都相同，后缀用阿拉伯数字加以区分。例如：mhStat函数功能：取当前设定历史日期（月）的给定月统计项目值语法：FunctionmhStat(StnNoAsLong,PntNoAsLong,PntTypeAsString,attr_noAsLong)AsVariant参数：StnNo整数，厂站号，取值1～64PntNo整数，点号，从1开始PntType字符串，点类型，取值”A”,”D”,”N”,”Y”,”X”attr_no整数，月统计项目属性序号，取值1～18返回值:根据attr_no所指示的统计属性，返回相应的统计值。值可以是单精度浮点数，也可能是日期时间类型。如果失败，返回空值(null)。示例：mhStat(1,32,“a”,1)=》在当前系统设定的历史日期（月），取1号站32号遥测点第1号统计项目值（即：当月最大值）。3.2.2.2几个过程的设计(1)SetHistoryDate()设置历史数据日期，在设置完历史数据日期后可以随即刷新当前活动工作表(2)Refresh()刷新当前工作表(3)SetRtdbEffectiveTime()设置实时数据有效持续时间(4)SelectODBCConnect()选择ODBC数据库连接(5)ViewInformation()4-5查看当前报表设置(6)RefreshDataBuffers()主动刷新系统的数据缓冲区，使刷新后的函数计算不使用缓冲区中缓冲的数据，而是到数据库中查询。3.2.3经过上述工作，一个简单实用的基于Oracle数据库的Excel报表系统就开发完成了。系统的界面如图2、3、4所示。图2Excel报表系统界面Fig.2TheInterfaceofExcelReportFormSystem4结语基于Oracle数据库的Excel报表系统将Oracle数据库与Excel电子表格软件相结合，在不修改原有应用软件的前提下，通过方便的查询接口满足了企业各部门绝大部分的数据需求。系统的开发不仅简轻了系统维护人员的工作量，而且提高了系统使用人员的工作效率。TheDevelopmentofExcelReportFormSystemBasedonOracleDatabaseZHANGYu-ting,MATao(HengshuiPowerSuppliedCompany,Hengshui,053000,China)Abstract:ThisarticleintroducedthedevelopmentofExcelreportformsystembasedonOracledatabase.IntheSCADAsystemofHengshuiPowernetworkstheoperationaldataisstoredintheOracledatabase,sodataisachievedonlythroughthepreparationofOraclescript.Tomeettheneedsofenterprisesectorsto图3设置ODBC数据源对话框Fig.3TheDialogBoxofChoosingODBCDataSource图4设置查询时间对话框Fig.4TheDialogBoxofSettingDate5-5dataandtofacilitatetheuseofgeneralapplication,wedeveloptheExcelreportformsystemusingtheactivexprovidedbyExcelandVBAsoftwaredevelopmentenvironmentbasedontheoriginalapplicationsoftwareinordretoachieveflexibledataeasilythroughasimpleinterface.Keywords:OracleDatabase;MicrosoftExcel;VBA;Dataaccess---------------------------------------------------------------------