EBS 多sheet页Excel动态报表开发过程

fdhfgh

　　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标签里增加对编码的控制。
　　
　　
　　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来看。
　　
　　
　　
　　
　　
　　
　　
　　
　　
　　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也提供了相应的办法，也是在此处进行设置，就像下面这些常用的权限控制，需要那个就在此处添加该标签即可：
　　         ：是否允许对单元格的格式做调整
　　            ：是否允许改变列宽
　　           ：是否允许改变行高
　　           ：是否允许插入列
　　          ：是否允许插入行
　　     ：是否允许插入超链接
　　           ：是否允许删除列
　　       ：是否允许删除行
　　                 ：是否允许排序
　　                ：是否允许使用自动筛选
　　       ：是否允许使用数据透视图
　　注意：此处锁定Sheet页是对整个Sheet页进行锁定了，但是客户要求被锁定Sheet页中部分内容要能够被修改，比如：样例报表第一层中在报表跑出来后手动填写的区域。这种情况应该如何实现呢？其实很简单，只需在一个地方稍作修改即可：
　　在需要被修改区域的样式 标签中添加一行 即可,例如：
　　
　　
　　
　　
　　
　　
　　
　　
　　
　　
　　8.Row&Cell
　　Row一般设置行的属性，比如行高、是否自适应行高等等， Cell设置该单元格的属性，比如合并单元格、单元格样式、是否用公式等等。
　　常见的格式如下：
　　
　　(此处为显示的数据)
　　
　　Style前面已经介绍过了，Cell通过设置StyleID的属性值来确定使用哪种样式。
　　Data标签来指明在该单元格内的数据时什么类型的，这个很重要，因为只有此处类型为Number的单元格值才能参与后续的计算，否则即使单元格里的值看着是数字，但是其类型如果不是Number的话，是不能和其他的数值一起进行计算的。
　　看到此处，大家可能注意到，在什么位置设置列的宽度呢？大家可能会想到在 标签里加 ss:Width  标签，其实这样是不对，为什么呢？因为EXCEL的整列的宽度是一样宽的，不像在HTML表格里同一列的不同单元格的宽是可以不一样宽的，所以在EXCEL里不能在设置单元格的宽度，标签下，标签之上单独定义每一列的宽度，如下一个sheet页所示的位置：
　　
　　
　　
　　
　　134
　　
　　
　　
　　……
　　
　　
　　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得到，即从一开始的一直COPY到Styles标签结束。这样报表头部的定义就完成了。需要注意的是，Excel生成的StyleID全都是sXX的格式，可读性差。如有必要，可以在这里将Style的ID命名成自己容易看懂的名称，如gray、green和title等等。
　　2. PROCEDURE output_xml_ending
　　此PROCEDURE的作用“”结尾标签。
　　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的作用输出结尾标签和定义该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打不开（这个限制因为数据不够，我没有测试。只是在网上看到有这么一说，所以贴出来大家心里有个数就行。）。