(本文写于2008年,原本想通过期刊发表,但当时感觉刊登费用过高而放弃发表。)
一 引言
在基于Web应用的企业信息管理系统中,经常需要将相关数据以Excel文档的方式输出,或打印,或经过二次加工后被其它文档所引用。有两种实现方法:一是在服务器端处理Excel文档,完毕后发送到客户端,在客户端的Excel中打开,另一种方法是在服务器端将数据处理好后发送到客户端,在客户端用脚本打开Excel并装入该数据。后一种方法由于涉及在浏览器中创建一外部对象,要求浏览器设置为较低的安全级别并且需要较复杂的脚本逻辑,因此在多数情况下,第一种方法是首选方法。
在服务器端处理Excel文档,通常的做法是采用Excel文档对象模型直接操作文档,这种方法要求服务器端安装Excel,操作完成后,由于Excel对象只提供了将结果保存在磁盘上的功能,后续程序必须再从磁盘上读取该文档,再发送到客户端,可以看出,这种方法从安全性(服务器端安装Office软件)和效率上都值得商榷。本文提出采用XML形式而非xls形式Excel文档的方法,支持XML格式是自Excel XP版提供的功能,由于.Net Framework提供了丰富的方法操作XML文档,处理完毕后,可直接发送到客户端,无需安装任何额外的软件,因此这是一种简单易行的报表生产方法。
多数情况下,用户要求输出的报表格式都已预先确定,可以先制作好这些模板,以XML形式保存在服务器端,在输出时,只需填充数据到对应的单元格,这样,虽然XML文档比较复杂,但制作报表时,只关心如何填充数据即可。
如果不涉及复杂的业务逻辑,计算数据尽量在Excel中通过设置的公式来完成,某些中间结果也可以保存在隐藏列中,以后当报表内容发生小变化时,信息系统管理人员就可以通过修改Excel模板来完成,而不必要求开发人员到场,从而减少了系统维护量。
由于Excel 2003版全面支持XML形式的载入和输出,是目前最常用的Excel版本,所以本文的分析设计都基于该版本。
二 XML 形式的Excel 文档分析
XML 形式的 Excel 文档的结构大致如图 1 所示,其中 (1..n) 表示该元素可以重复 n 次。图中 <DocumentProperties> 、 <ExcelWorkbook> 和 <Style> 分别用来设置 Excel 的文档属性、窗口属性以及文档中所用到的格式信息,本文不必考虑这些元素。 <Worksheet> 是文档内容的主体,代表一个 Excel 页,其中 <Table> 又是 <Worksheet> 的主体,其属性 ExpandedColumnCount 和 ExpandedRowCount 分别用来设置非空单元格的最大行、列数。元素 <Column> 设置列的显示属性,本文不必考虑,元素 <Row> 代表一行,它的子元素 <Cell> 代表单元格,单元格的内容在 <Data> 中, <Data> 的属性 Type 表示单元格内容的数据类型,最常用的类型是 String( 字符串 ) 和 Number( 数字 ) 。如果单元格是一个公式,以 <Cell> 的属性 Formula 表示。
图1 XML 形式的Excel 文档的大致结构
为压缩 XML 文档的大小,出现空行或空单元格时,采用特别的处理方式。
- 连续空行大于 2 时,不存储后续的空行信息,第一个空行用属性 Span=“n” 表示后续还有 n 个连续空行,紧跟的第一个非空行用属性 Index=“m” 表示再次的行起始于第 m 行。
- 一行内不保存完全为空的单元格。在下一个非空单元格出现时,用属性 Index=“n” 表示数据再次起始于第 n 列。
三 操作Excel 文档
了解XML 形式的Excel 文档格式,就可以构造出操作此文档的类。作者构造出的操作类如图2 所示。
图2 Excel 文档操作类
ExcelCell 类与文档中 <Cell> 元素对应,包含一个 XmlNode 类和 Index 属性,代表某行的非空节点, ExcelRow 类与 <Row> 元素对应,包含一个 ExcelCell 链表、 XmlNode 类和 Index 属性。 ExcelTable 类与 <Worksheet> 元素对应,维护了一个 ExcelRow 链表。 ExcelCell 和 ExcelRow 只起到存储数据的作用,实际的操作都在 ExcelTable 中完成。 ExcelXMLHelper 类维护了一个 ExcelTable 链表,提供了操作 XML 文档的方法, ExcelCell 、 ExcelRow 和 ExcelTable 都是供 ExcelXMLHelper 内部使用的类。
操作 XML 文档时, ExcelXMLHelper 先读入 XML 模板,解析其结构,生成 ExcelTable 链表,每个 ExcelTable 节点生成 ExcelRow 链表,每个 ExcelRow 节点生成 ExcelCell 链表,每个 ExcelCell 节点填充 <Cell> 内容和 Index 属性, ExcelRow 也有 Index 属性,表明该节点所处位置。
由于文档采用“压缩”结构,填写或修改 Excel 单元格(已知行、列的 Index )内容分 3 种情况:
- 根据 Index 能够查找出 ExcelCell 对象,表明该单元格已存在,则直接修改其内容;
- 根据 Index 只能查找出 ExcelRow 对象,表明该行存在而单元格不存在,需要在该 ExcelRow 对象的 ExcelCell 链表的合适位置创建 ExcelCell ,视不同情况,设置、修改或删除当前和后面 ExcelCell 对象中 XmlNode 的 Index 属性
- 根据 Index 不能查找出 ExcelRow ,表明该行和单元格都不存在,需在 ExcelTable 的 ExcelRow 链表的合适位置创建 ExcelRow ,视不同情况,设置、修改或删除前面、当前和后面 ExcelRow 对象中 XmlNode 的 Index 属性,再按第二种情况处理。
对于后两种操作,如果添加的行或列序数大于属性 ExpandedColumnCount 、 ExpandedRowCount 的值,则应修改对应的属性。
为使单元格的赋值使用简洁并符合 Excel 习惯, XMLExcelHelper 类重载了 [] 操作符,可以直接使用诸如 excel[3,“AB”]=1 或 excel[5,6]=“A” 等样式的赋值语句,使使用十分整齐。
四 实际应用举例
在作者开发的炼铁生产管理系统中,所有需要打印或上报的报表都采用XML 形式的Excel 文档输出,输出的原始报表模板保存在特定的目录下,当用户需要输出某个报表时,服务器端程序从数据层获得数据,采用上面的操作类将其填入到对应单元格,以XML 格式的字符串输出到客户端,同时将Content-type 设置为“application/excel ”,则客户端自动打开Excel 装载接收到的报表。
图3 是系统中较复杂的一个报表-炼铁日报,包含了当日几乎所有的生产参数,其中约四分之一是计算数据,通过公式设置产生,有十余个辅助计算的数据保存在“页2 ”中,一般情况下,用户不关心“页2 ”的内容。该日报要求每日打印一份,打印前,管理人员偶而会调整有关数据或强调某些指标数据,用Excel 处理这种要求是非常方便的。
图3 炼铁日报的Excel 报表输出
五 结论
Excel 提供了载入和输出 XML 格式文档的功能,也就提供了一种不依赖于 Excel 对象模型而操作 Excel 文档的方法。通过分析 XML 的格式,编写了操作 Excel 文档的辅助类,将这些类用于企业信息系统中的报表输出,既符合用户的使用习惯,又简化了报表制作,同时提高了服务器的安全性。