1.前言
本文讲述的多Sheet页EXCEL报表开发方式和开发HTML,PDF这类报表的方法大致是一致的,唯一不同的是该报表输出是一个XML文件,但是这种XML文件支持EXCEL直接打开。
这种方式有如下两点点非常明显的优点:
(1) 灵活性。
如果客户对报表显示样式要求非常严格的话,那用这种方式就非常方便了,就比如我这次项目的客户是个德国人,对报表的显示样式要求极为苛刻,甚至严格到每个列的颜色,边框线,列宽,字体等。用这种方法就可以以类似CSS开发方式一样来编写EXCEL的显示样式代码,然后到输出数据的时候套用不同的样式即可。
(2) 数据处理非常方便。
当报表对输出的数据要做大量的计算,分类汇总等操作时,如果在编写代码的时候直接进行计算,分类汇总等,一旦数据量大的时候会导致报表运行非常慢,很影响效率。而用这种方法,只需从系统里取出最基本的数据即可,然后写好各种计算和分类汇总等的公式,等报表跑出来后利用EXCEL自带的功能自动计算。这样做一方面可以提高报表的性能,另一方面可以使得整个报表的数据是动态,在报表里修改了基本数据后,那些通过计算和分类汇总等操作得出的数据也会联动改变。
3.EXCEL电子表格
该报表输出文件是一个XML文件,该XML文件和我们常见的文件非常类似,只不过普通XML件是一个无格式的资源文件,而该XML文件是带格式的资源文件。就像我们打开任何一个excel文件,选择另存为“XML电子表格2003(*.xml)”的格式。然后用记事本打开那个xml文件,就可以看到EXCEL表格的庐山真面目。
下面对EXCEL表格的XML格式文件做个简单介绍。
3.1 示例
(1)EXCEL文件以XML格式打开后可以看到其结构图如下:
(2)单个Sheet页的结构图如下:
3.2 语法结构
1.题头
前两行是题头,跟XML Publisher一样,我们可以在第一行的xml标签里增加对编码的控制。
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?>
2. Workbook
Workbook标签包住的就是整个电子表格,有点类似于HTML语言中的html标签。而Worksheet标签包住的就是其中的一个sheet页,有点类似于HTML语言中的body标签,只不过在XML电子表格里可以有多个“body”。
3.”Head”
我们可以看到,从Workbook的开始标签到第一个Worksheet之间还有很多内容,其中包括DocumentProperties、OfficeDocumentSettings、ExcelWorkbook和Styles四个标签。这些内容就相当于HTML语言中的head标签,对整个电子表格进行控制。前三者我们可以不去关注,其中ExcelWorkbook中的ActiveSheet标签决定XML电子表格打开时默认展现第几个sheet页。
4.Styles
“Head”中最重要的是Styles标签。顾名思义,Styles就是电子表格的样式。Style是Styles的子标签,是单个样式。Styles里包含了整张电子表格需要用到的全部样式。
拿下面这个Style来看。
<Style ss:ID="normal"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/> </Style>
XML电子表格标记语言跟HTML语言比较类似。标签的各个属性之间用空格隔开,属性名称跟属性值间用“=”连接,属性值都需要用双引号包住。不同的一点是,每个属性名称的前面都要加上一个“ss:”。
Style标签一定要设置的属性只有一个,就是ID,用来区分不同的Style。Style里面可以有许多的子标签,比如控制单元格边框的Border、控制字体的Font、控制单元格背景的Interior和控制单元格锁定的Protection。
5.Worksheet
Worksheet的Name是sheet页显示的名称,必须有且不可重复,否则excel无法打开。
Worksheet还有一个有用的属性,在上面的例子中没有呈现,是Protected。Protected设置为”1”可以使该sheet页的全部单元格不可编辑。但是XML电子表格不能支持密码保护,也就是说,用户其实可以通过单击Excel“审阅”中的“撤消工作表保护”来使其失效。另外,Style中也有单元格控制,Style的Protection优先级要高于Worksheet的Protected。
6.Table
Table是Worksheet的子标签,是具体显示数据的标签。从结构上看,我猜想一个Worksheet里应该可以有多个Table,但经过多次测试都未能找到在一个Worksheet里放多个Table的方法,暂时认为一个Worksheet最多存放一个Table。
Table有两个属性很重要,是ExpandedColumnCount和ExpandedRowCount。这将决定Excel在打开时按照几行几列的标准去读取文件中的数据,ExpandedColumnCount和ExpandedRowCount可以大于实际有效区域的列数和行数,但是绝不可以小,否则excel无法打开。例如, 一个四行四列成绩单sheet页内共有4*4个有效区域,如果ExpandedColumnCount改成3,excel就无法打开,而ExpandedColumnCount改成5,excel可以正常打开。
7. WorksheetOptions
从Table的结束标签到Worksheet的结束标签之间,还有一个标签:WorksheetOptions。这个标签可以定义该Sheet页的很多属性,比如打开该Sheet页时,鼠标定位在什么位置;以及打印区域设定;还有该Sheet页权限锁定限制也是在此处定义,这里要注意:当Sheet页选择锁定时,用户对该Sheet是不能做任何操作的,包括改变行高列宽,添加行或列,删除行或列等,但是通常用户选择锁定Sheet页时,仅仅希望内容不能被修改,其余的像改变行高列宽,添加行或列,删除行或列等,这些权限是不需要被锁定的,针对这种情况,EXCEL也提供了相应的办法,也是在此处进行设置,就像下面这些常用的权限控制,需要那个就在此处添加该标签即可:
<AllowFormatCells/> :是否允许对单元格的格式做调整 <AllowSizeCols/> :是否允许改变列宽 <AllowSizeRows/> :是否允许改变行高 <AllowInsertCols/> :是否允许插入列 <AllowInsertRows/> :是否允许插入行 <AllowInsertHyperlinks/> :是否允许插入超链接 <AllowDeleteCols/> :是否允许删除列 <AllowDeleteRows/> :是否允许删除行 <AllowSort/> :是否允许排序 <AllowFilter/> :是否允许使用自动筛选 <AllowUsePivotTables/> :是否允许使用数据透视图
注意:此处锁定Sheet页是对整个Sheet页进行锁定了,但是客户要求被锁定Sheet页中部分内容要能够被修改,比如:样例报表第一层中在报表跑出来后手动填写的区域。这种情况应该如何实现呢?其实很简单,只需在一个地方稍作修改即可:
在需要被修改区域的样式 <Style>标签中添加一行 <Protection ss:Protected="0"/>即可,例如:
<Style ss:ID="normal"> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/> <Protection ss:Protected="0"/> </Style>
8.Row&Cell
Row一般设置行的属性,比如行高、是否自适应行高等等, Cell设置该单元格的属性,比如合并单元格、单元格样式、是否用公式等等。
常见的格式如下:
<Row ss:AutoFitHeight="0" ss:Height="30">
<Cell ss:StyleID="(此处为Style的ID)" ss:Formula="(此处为公式)"><Data ss:Type="(此处为数据类型,一般为String或Number)">(此处为显示的数据)</Data></Cell>
</Row>
Style前面已经介绍过了,Cell通过设置StyleID的属性值来确定使用哪种样式。
Data标签来指明在该单元格内的数据时什么类型的,这个很重要,因为只有此处类型为Number的单元格值才能参与后续的计算,否则即使单元格里的值看着是数字,但是其类型如果不是Number的话,是不能和其他的数值一起进行计算的。
看到此处,大家可能注意到,在什么位置设置列的宽度呢?大家可能会想到在<Cell> 标签里加 ss:Width 标签,其实这样是不对,为什么呢?因为EXCEL的整列的宽度是一样宽的,不像在HTML表格里同一列的不同单元格的宽是可以不一样宽的,所以在EXCEL里不能在<Cell>设置单元格的宽度,<Table>标签下,<Row>标签之上单独定义每一列的宽度,如下一个sheet页所示的位置:
<Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5"> <Column ss:AutoFitWidth="0" ss:Width="74.25"/> <Row ss:AutoFitHeight="0" ss:Height="30"> <Cell><Data ss:Type="Number">134</Data></Cell> </Row> </Table> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> …… </WorksheetOptions> </Worksheet>
4.开发步骤
了解了EXCEL表格XML电子表格的语法后,我们就可以像做HTML报表那样,直接在PL/SQL Package中进行开发EXCEL报表了。
4.1 制作Excel模板
我们可以先用Excel做出我们想要的报表的样板,再通过另存为XML文件的形式,获取到大量的现成代码,尤其是报表样式的定义。这样可以给开发大大减少工作量。而且客户提出的任何需求只要他们能在EXCEL中表现出来,咱们就能开发出来。
4.2 编写公共程序
得到Excel模板生成的代码后,我们就可以编写程序来执行报表的输出工作。附件(CUX_EXCEL_REPORT_TEMPLATE.pck)是一个完整的报表模版程序包示例。
以下为公共程序包的主要Procedure。
1.PROCEDURE output_xml_header
此PROCEDURE的作用就是输出XML的header,可以直接从EXCEL另存为得到的XML文件中COPY得到,即从一开始的<?xml version="1.0"?>一直COPY到Styles标签结束。这样报表头部的定义就完成了。需要注意的是,Excel生成的StyleID全都是sXX的格式,可读性差。如有必要,可以在这里将Style的ID命名成自己容易看懂的名称,如gray、green和title等等。
2. PROCEDURE output_xml_ending
此PROCEDURE的作用“</Workbook>”结尾标签。
3.
PROCEDURE OUTPUT_XML_SHEET_HEADER(P_SHEET_TITLE IN VARCHAR2,
P_COL IN NUMBER,
P_ROW IN NUMBER)
此PROCEDURE的作用是定义一个Sheet页,其中p_sheet_title是Sheet页的名称。p_col是该Sheet页有效数据区域的最大列数。p_row是该Sheet页有效数据区域的最大行数。拷贝XML模板代码中任意一个Sheet页的定义代码,并用相应参数变量替换Worksheet的Name属性, Table的ExpandedColumnCount和ExpandedRowCount属性。
4. PROCEDURE output_xml_sheet_ending
此PROCEDURE的作用输出</Table>结尾标签和定义该sheet页的鼠标定位,页面锁定等信息,最后结束该Sheet页。即COPY模版代码中从Table的结束标签到Worksheet的结束标签。
5.
PROCEDURE OUTPUT_XML_ONE_ROW(P_DATA IN G_STRING_ARRAY,
P_TYPE IN G_STRING_ARRAY,
P_STYLE IN G_STRING_ARRAY)
此PROCEDURE的作用是输出Sheet页内的一行数据,即一个Row标签的内容。
其中p_data存放每个单元格的数据,p_type存放每个单元格对应的数据类型,p_style存放每个单元格对应的StyleID。
具体如何输入一行数据,请查看CUX_EXCEL_REPORT_TEMPLATE.pck中该PROCEDURE的定义。
4.3 编写报表的主程序
有了公共程序后,开发EXCLE报表就和开发HTML的报表的逻辑基本一致。只需要先分别将数据、数据类型和样式ID依次分别存入三个数组内,再将三个数组传给公共程序包完成输出即可,详细请查看CUX_EXCEL_REPORT_TEMPLATE.pck模版中的process_request过程。
4.4 定义报表
和其他报表一样:定义可执行、并发程序和参数等。
注意:
1. 并发程序的输出类型选择XML。
4.常见问题
4.1 Excel公式
EXCEL转换成XML后,两个文件中对公式的写法有比较大的不同。
在EXCEL中文件的公式中可以直接用“B4”、“A5”这样的字符串,而在XML文件中则要转换成“R[n]C[m]”这样的形式。
特别要注意的是这里的n和m并不是单元格的行序号和列序号,而是相对于当前单元格位置的行列偏移量。当偏移量是0时,可以直接写“R”或“C”。其中,向右和向下偏移是正数,向左和向上偏移是负数。因此,“RC”就代表当前单元格,“R[1]C”就代表当前单元格下面的那个单元格,“RC[-1]”就代表当前单元格左边的那个单元格,依此类推。
4.2 Excel限制
(1) Sheet页的命名不能重复,也不能太长。否则报表运行时不会报错,但是打开的时候会报错,这个是excel的限制。
(2) Sheet页的个数不能超过5000个,超过的话Excel打不开(这个限制因为数据不够,我没有测试。只是在网上看到有这么一说,所以贴出来大家心里有个数就行。)。
PS:模版代码和文档在附件。