Excel与数据库间基于语法规则的数据交换技术

收稿日期:2007-02-18基金项目:国家自然科学基金项目(50474042)Excel 与数据库间基于语法规则的数据交换技术王湘波,王家华(西安石油大学计算机学院,陕西西安710065)摘　要:针对目前Excel 与数据库间的数据交换方法通用性不够的问题,对Excel 表单中各逻辑子表的关联关系,以及Excel 、应用程序、数据库表三者之间的关系进行了分析,提出了基于语法规则的通用数据交换方法。

通过设计一套用于数据交换的语法规则,然后建立解释器解释该规则,便可完成他们间的双向数据沟通。

应用证明,该方法具有很好的通用性。

关键词:电子表格;数据交换;语法规则;数据整合中图分类号:TP311.5 文献标识码:A 文章编号:1000-7180(2008)01-0093-04G ramm ar B ased Data Exchange T echnologyB et w een Excel and DatabaseWAN G Xiang 2bo ,WAN G Jia 2hua(School of Computer Science ,Xi ′an Shiyou University ,Xi ′an 710065,China )Abstract :In this paper ,the relationships between the logic sub 2tables of the Excel sheet ,the relationshi ps among Excel ,application ,and database tables are fully analyzed.In order to solve the problem of inadequacy in general 2utility for data exchange between Excel and database at present ,then a grammar 2based data exchange solution is presented.The data ex 2change between Excel and database can be accom plished.By defining a set of grammar rules used for data exchange ,and designing a interpreter to explain these rules.In practice ,this solution shows good utility of generality.K ey w ords :excel ;data exchange ;grammar ;data integration1　引言Excel 功能强大,在数据运算、数据加工、图表表达、数据分析统计等方面有优势,同时又简单易用。

但与数据库系统相比,Excel 在数据冗余、数据共享、数据检索、数据关系表达、数据独立性、数据完整性、数据安全性、数据恢复、并发控制等方面表现不足。

数据库系统专业性太强,一般工作人员很难直接操作它,因此经常遇到需要在Excel 与后台数据库系统中交换数据的情形。

在前端,工作人员仍使用Excel 方便地制作电子表格;在后台,数据一般都保存在数据库系统中,以方便查询和统计。

将前端的Excel 数据录入到后台的数据库中,一般是通过数据导入程序来完成。

文献[1-5]介绍了用数据导入程序来完成Excel 与数据库间数据交换的一些方法,其共同之处是针对具体问题编写相应的程序来实现,通用性不够。

如何让数据交换方法具有通用性成为亟待解决得问题。

文中通过分析Excel 的特点、与数据库及应用程序的关联关系,提出了一种通用的整合方案,可免于重复编写数据导入程序。

2　基于语法规则的通用整合方案的提出Excel 表单与数据库表有各自的数据格式,它们之间能进行数据交换,前提是它们之间有一定的对应关系,这种对应关系可借助某种描述语言来描述。

Excel 表单的数据操纵可用应用程序来完成,而数据库可执行SQL 语言来操作其关系表。

这两种操作要实现互相沟通,需通过了解二者关系的第三方来进行翻译解释,这就是解释器,它可以按照描述语言描述的关系来转换Excel 与数据库表间的数据。

而描述语言需满足一定的语法规则,才能被解释器所理解。

文中提出的数据交换过程设计如图1所示。

图1　Excel 与数据库的数据交换方案3　语法规则设计为使设计的语法规则具有很好的通用性,需要考虑Excel 数据间的逻辑关系、数据库表之间的关联关系、应用程序的协作等因素对数据交换的影响。

3.1　Excel 表格中逻辑关系的划分一个Excel 文档称为一个Workbook ,一个Workbook 可拥有多个表单(Worksheet )。

一个Worksheet 拥有一个表格(Table )。

一个Table 拥有多行(Row ),一行又拥有多个单元格(Cell )。

Excel 表单也是以表格Table 的形式组织数据,可定义如下:定义1　Excel 表格:Excel 表格定义为:E =(A 0,A 1,A 2,…,A n ),A 0至A n 为属性列,其中A 0为表格元组行号组成的属性列。

Excel 表格内部数据间的逻辑关系较为复杂。

为分析Excel 表格与数据库表之间的关系,需将Excel 表格按数据之间的逻辑关系,进一步划分为更小的单位:逻辑子表。

下面定义两个划分Excel 逻辑子表的操作:定义2　关系的E c i ,n 操作:E ci ,n ={t [A 0]-i +1,t [A c ])|t ∈σi ≤A 0≤n (E )}式中,c 为列上标,i ,n 为行下标。

它表示从关系E 的属性列A 0以及行号属性列中获取行号从i 到n 的元组组成的集合,并且,该集合的行号列转换为从1起点的行号列。

定义3　关系的E c i操作:E c i ={t [A c ]|t ∈σA 0=i (E )}式中,c 为列上标,i 为行下标。

它表示从关系E 的属性列A c 中获取行号为i 的单元构成的集合。

显然该集合内只有一个元素,对应于一个Excel 表格单元。

如表1所示,报告日期对应的数据集为E 21,管理局对应的数据集为E 14,10,OED 剩余对应的数据集为E 34,10。

使用定义2与定义3,可把复杂的Excel 表格分解为最小的逻辑子表。

以这些最小的逻辑子表为基础,通过关系运算,就可组合成更大的逻辑子表或逻辑表。

表1　石油报告(Excel 表格)123451报告日期200123管理局区块油经济可采OED (BBL )剩余采出总数4SHEN G L BIN658100128312845SHEN G L BIN6746504065046SHEN G LBIN64996696678SHEN G L YI9434459535167797629SHEN G L YI942689068910SHEN G LYI9459869411927逻辑子表的连接:设有从Excel 表格中获得的E c 1i ,n ,E c 2j ,m 两个逻辑子表。

则E c 1i ,n ,E c 2j ,m 的属性列都是两列即(A 0,A c 1)、(A 0,A c 2),且第1列都是行号集合,一般而言该行号集合大小相等。

第2列可以相同也可以不同。

现定义一个连接运算,该运算以行号进行等值连接,且结果分别保留E c 1i ,n ,E c 1j ,m 中第2个属性列,而E i 、E j 中第1个属性列在结果中只保留一个。

其数学定义如下:定义4　E c 1i ,n ,E c 2j ,m 的等值连接:E c 1i ,nE c 2j ,m ={t 1[A 0],t 1[A c 1],t 2[A c 2])|}(t 1∈E c 1i ,n )∧(t 2∈E c 2j ,m )∧(t 1[A 0]=t 2[A 0])}若忽略管理局和区块对应的列,则表1到表2的转换过程可用笛卡尔积与等值连接完成,如下:E 12×(E 34,10E 44,10E 54,10)(1)表2　石油报告(数据库表格)REPORT ID REPORT DATE BUREAU IDBLOCK ID OED剩余OED采出OED总数ROW C REATE D BYROW C REATED DATE2001100010011001283128420011000102265040650420011000100596696620011000105544595351677976220011000102168906892001100010089869411927本系统在实现时用MsExcelCell 类来E c i 封装操作;用MsExcelColumn 类来封装E c i ,n 操作。

至此,已完成对Excel 表格的逻辑关系的划分,并能完成这些逻辑关系与数据库表的映射运算。

3.2　数据库表之间的关联影响定义5　关系数据库:一般而言,关系数据库集合定义为D=(T1,T2,…,T n),其中T1,T2,…, T n为关系表。

而关系表的定义为T=(F1,F2,…, F n),其中F1,F2,…,F n称为域或属性列。

在Excel与数据库的数据交换中,不得不考虑数据库表之间的关联关系。

Excel表的数据冗余一般较大,而数据库表将关系分解比较彻底,一般要求达到第三范式(或者更高)的标准。

如表1中的管理局与区块的数据列信息,在对应于表2中以ID的形式出现,而管理局和区块的名称信息则放在其它的数据库表如T1、T2中,这时需利用Excel中提供的名称信息在T1、T2中查找到对应的ID信息,才能向表2中写入这两列信息。

为实现这种关联查找,本系统定义Sql类,来封装这类操作。

3.3　应用程序的影响有时,数据库中的某些字段,如标记各条记录的ID号字段,它并不一定是一个简单的递增数列,其产生规则可能很特别,此时一般由应用程序用算法来产生该字段。

而由于用户有时对ID号并不关心,故该ID号一般不出现在Excel中,而只出现在后台数据库中。

故将数据导入数据库中时,该字段的值不能直接从Excel中获取,而需调用算法程序才能得到。

为使用户在编写这部分程序时,不需要了解整个系统的结构和原理就能顺利地将这部分程序与整个系统连接起来,系统定义一个接口类型JavaClass,用户只需实现该接口,就能将自己的程序数据提交给系统。

3.4　其他影响数据库表记录的信息一般比Excel表格中的信息更详细,比如数据库表中有时记录每一条记录的更新时间,以及更新该记录的操作人员等信息。