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

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:模版代码和文档在附件。

 

评论

7 楼 18335864773 2017-12-06  

用pageoffice 吧.直接在线生成excel.并且兼容所有的office版本

6 楼 mthhk 2015-05-20  

请问楼主如何解决将填充数据以后的xml转换为.xls(x)。
如果只是简单地将文件重命名为.xls(x)，excel软件会出现提示框。

5 楼 yangjiefu0909 2014-08-01  

好东西看到了就必须顶

4 楼 lan771 2014-05-05  

强烈推荐 好文章 谢谢技术共享

3 楼 staler 2013-09-26  

2 楼 julycool 2013-01-31  

类似的方法我用过，很好用，当时解决了海量数据灌报表的问题

1 楼 boygirl 2013-01-30  

难啊