http://www.cnblogs.com/quanweiru/archive/2012/07/28/2612680.html

一.概述

在EBS系统中,报表是一个非常重要的客户化开发内容,也是系统一种比较常见的数据输出形式。报表的做法有很多种,输出格式也有多种,较为常见的就是“数据文件+报表模板”的形式,例如用脚本程序输出XML数据源,RTF模板控制数据格式的方式。也有直接用PLSQL程序输出HTML格式的报表,数据和格式统一在程序中控制。

Excel是一种非常常用的办公软件,操作比较容易被用户接受,对表格中数据的控制也比较容易,一系列的公式支持也为用户节省了很大的工作量。

Excel标准格式为".xls"或".xlsx"文件格式,也可以另存为XML数据表格的格式(".xml"),这就为程序直接输出XML格式的脚本来生成Excel表格提供了有利条件。经过实际的操作,利用这种方式输出的报表,可以在EBS R12中输出,在Excel中打开,并且可以进行表格相关格式控制。

 

注1:Excel文件 ”.xls” 或”.xlsx” 另存为”.xml”文件可能存在兼容性问题。

注2:Excel文件”.xls” 或”.xlsx”与”.xml”之间的相互转换可能存在一些对象的不相互兼容问题(例如”.xls”或”.xlsx”中的图表)。相关支持内容请查看MSDN

 

 

二. 解决的问题

2.1 解决的问题

以当前项目中的实际为例,客户要求对生产计划和效率产出做一个比较,月初做计划,月底做对比,提交并发程序输出报表。设计文档中报表的样式为Excel表格,其中一些特殊的格式,例如竖排文字,多次汇总,分隔汇总,数据格式控制,公式,字体,表格状的动态增长报表,经过分析,这种形式的报表用Excel的形式输出是非常合适的,相类似HTML在格式控制上没有Excel灵活。

所以此文需要解决的问题是,输出Ecxel脚本报表。Excel的”.xls”或”.xlsx”为二进制文件,直接输出二进制脚本可行性并不大,难度大,所以改用XML脚本的Excel文件来输出。

 

 

 

2.2 准备

相关工具及输入输出

编程:PLSQL Developer X

输出:XML

系统:EBS R12

浏览器:(建议为)IE8及以上版本

其他:Office 20XX

                                                       

注:多次测试发现IE6在打开特定头的XML文件时,常出现卡死状态,不建议使用。

 

三. 一个最简单的XML格式的Excel报表

把”.xls”或”.xlsx”文件以”XML数据表格”的格式另存,用记事本或其他文本编辑器打开,就可以看到此表格的XML脚本。这种形式的Excel文件脚本大体分为4个部分,XML头,Excel样式,Sheet主数据块,XML尾。

 

其中有一些部分的信息是可以没有的,经过简化,将一个最基本的文件结构提取出来,形成如下的例子。

 

3.1 脚本代码

复制代码
复制代码
 1 <?xml version="1.0"?>  
2 <?mso-application progid="Excel.Sheet"?>
3 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
4 xmlns:o="urn:schemas-microsoft-com:office:office"
5 xmlns:x="urn:schemas-microsoft-com:office:excel"
6 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
7 xmlns:html="http://www.w3.org/TR/REC-html40">
8 <Worksheet ss:Name="Demo Sheet">
9 <Table>
10 <Row>
11 <Cell><Data ss:Type="String">Hello!World!</Data></Cell>
12 </Row>
13 <Row>
14 <Cell><Data ss:Type="String">Hello!World!</Data></Cell>
15 </Row>
16 <Row>
17 <Cell><Data ss:Type="String">Hello!World!</Data></Cell>
18 </Row>
19 </Table>
20 </Worksheet>
21 </Workbook>
复制代码
复制代码

 

 

3.2 效果

用Excel打开文件,效果如下:

 

 

 

四. XML格式的Excel文件结构分析

分析文件结构,能够使我们清楚的掌握要用程序输出一个正确的脚本所必须的要素。XML和HTML的一个很大的区别在于,HTML为已定义的标签,每一个标签在浏览器中作用并不会存在很大的冲突,这里的意思是,浏览器在解释HTML脚本的时候,并不会太严格,原因是互联网刚发展的时候网络并不佳,请求丢包很多,所以HTML在丢失少量tag或某些特殊字符时,并不会出现严重的问题。但是XML不一样,XML是结构化自定义的,在确定了namespaces的情况下,必须按照namespace的结构定义来组成XML文件,特别是应用程序特殊处理的XML文件,不能出现细微错误的情况,例如tag有开始没有结束时,在Excel中就无法正确打开这个XML文件。

XML数据表格格式的Excel文件的顶级tag是Workbook,二级Tag主要有:

以下分别说明每一个tag的用途和主要属性。

                                                      

 

4.1 XML头

头信息(必须)

1 <?xml version="1.0"?> 
2
3 <?mso-application progid="Excel.Sheet"?>

 

XML文件头以

                  <?xml version="1.0"?>

开始,表明这是一个XML文件;对于需要用Excel来默认打开的XML文件,需要再加一句:

      <?mso-application progid="Excel.Sheet"?>

表明此XML文件用Excel默认打开。

 

注:后者其实可以不添加,打开时右键用Excel打开也是可行的,但是不推荐。

 

 

4.2 Tag: Namespaces

Namespaces信息(必须,固定内容)

1 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
2 xmlns:o="urn:schemas-microsoft-com:office:office"
3 xmlns:x="urn:schemas-microsoft-com:office:excel"
4 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
5 xmlns:html="http://www.w3.org/TR/REC-html40">

 

这个部分为XML的开始,我们可以看到XML电子表格格式的Excel文件的顶级tag是<workbook> ,放在这个tag里面的属性为Namespace信息,相关控制文件都来自Microsoft.com,说明Excel在打开这种类型的XML文件的时候,是严格按照这几个Namespaces的格式来读取文件的。

这个部分的Namespaces信息为固定的,必须的内容。

 

4.3 Tag: DocumentProperties

DocumentProperties(非必须)

一个例子:

1 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> 
2 <Created>2012-02-13T11:21:51Z</Created>
3 <LastSaved>2012-02-13T14:03:08Z</LastSaved>
4 <Version>14.00</Version>
5 </DocumentProperties>

这个部分主要设置Excel的文档属性内容,可以看到里有创建时间,修改时间和版本信息。

 

注:这个部分的信息非必须的意思是,系统输出XML之后,其实还是”.xml”格式的文件,用Excel打开之后,保存,这些信息会自动添加到脚本中,如果程序要求中不需要这样的信息,可以不添加。

 

4.4 Tag: OfficeDocumentSettings

OfficeDocumentSettings (非必须)

一个例子

1 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> 
2 <AllowPNG/>
3 <RemovePersonalInformation/>
4 </OfficeDocumentSettings>

这个部分设置Offce 文档属性。

 

4.5 Tag: ExcelWorkbook

ExcelWorkbook (非必须)

一个例子:

复制代码
复制代码
1 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> 
2 <WindowHeight>11640</WindowHeight>
3 <WindowWidth>19200</WindowWidth>
4 <WindowTopX>0</WindowTopX>
5 <WindowTopY>90</WindowTopY>
6 <ProtectStructure>False</ProtectStructure>
7 <ProtectWindows>False</ProtectWindows>
8 </ExcelWorkbook>
复制代码
复制代码

这个部分设置工作表的属性。

 

4.6 Tag: Styles

Styles(非必须)

Style是一个可以作很大文章的部分,里面的属性设置包含了Excel里面常见的所有单元格,数据,颜色,方向等等,以ID的方式关联到具体的行,列或者单元格,对工作薄的格式设置支持比较完全。

一个例子:

复制代码
复制代码
 1 <Styles>  
2 <Style ss:ID="m76469184">
3 <Alignment ss:Vertical="Bottom" ss:Rotate="90" ss:Horizontal="Center"/>
4 <Borders>
5 <Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom"/>
6 <Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left"/>
7 <Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right"/>
8 <Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top"/>
9 </Borders>
10 <Font ss:Size="8" ss:FontName="Helv" ss:Bold="1" x:Family="Swiss"/>
11 <Interior ss:Color="#FFFF99" ss:Pattern="Solid"/>
12 <NumberFormat ss:Format="Fixed"/>
13 </Style>
14 </Styles>
复制代码
复制代码

以上这个例子中,<Styles>中可以有若干个<Style>,每一个Style确定一种样式,以ID区分不同的样式(ID为必须,如果ID为”Default”,则该样式为默认样式,只能有一个”Defulat”样式)。可以看到,样式定义中,可以定义对其方式,边框,字体,填充,数据格式等。

常用样式解释如下

<Alignment>

主要属性有:

属性

ss:Vertical

枚举:Bottom/Center/Top

ss:Horizontal

枚举:Left/Center/Right

ss:Rotate

数字,逆时针旋转,例如“90”代表单元格内容逆时针旋转90度

ss:WrapText

数字,“1”代表此单元格为多行显示

 

 

 

 

 

 

< Borders >

Borders里面下一级tag为Border,每一个Border为一个方向的边框格式设置,Border属性主要有:

属性

ss:Position

枚举:Bottom/ Left/ Right/ Top

ss:LineStyle

枚举:Continuous/…

ss:Weight

数字,边框粗细

 

 

 

 

 

<Font>

主要属性有:

属性

ss:FontName

枚举,字体

x:Family

枚举,字体类

ss:Bold

数字,1代表加粗

ss:Italic

数字,1代斜体

ss:Size

数字,字体大小

ss:Underline

枚举:Single/…

ss:Color

RGB或颜色号,文字颜色(不是填充颜色)

 

 

 

 

 

 

 

 

 

<Interior>

主要属性有:

属性

ss:Color

RGB或颜色号,填充颜色(不是字体颜色)

ss:Pattern

枚举:Solid/…,填充类型

 

 

 

 

<NumberFormat>

主要属性为

属性

ss:Format

”0.0”/”0”/”Fixed”/”0%”/…

 

 

 

 

4.7 Tag: Worksheet

Worksheet(必须)

这个部分为工作薄的数据表格部分,格式引用Styles中定义的Style。一个例子:

复制代码
复制代码
 1 <Worksheet ss:Name="Sheet1">  
2 <Table ss:DefaultColumnWidth="54" ss:StyleID="s119" ">
3 <Column ss:StyleID="s119" ss:Width="34.5" ss:AutoFitWidth="0"/>
4 <Column ss:StyleID="s119" ss:Width="34.5" ss:AutoFitWidth="0"/>
5 <Column ss:StyleID="s119" ss:Width="34.5" ss:AutoFitWidth="0"/>
6 <Row ss:StyleID="s28" ss:Height="15" ss:AutoFitHeight="0">
7 <Cell ss:StyleID="s17"><Data ss:Type="String">data here</Data></Cell>
8 <Cell ss:StyleID="s17"><Data ss:Type="String">data here</Data></Cell>
9 <Cell ss:StyleID="s17"><Data ss:Type="String">data here</Data></Cell>
10 </Row>
11 <Row ss:StyleID="s28" ss:Height="15" ss:AutoFitHeight="0">
12 <Cell ss:StyleID="s17" ss:MergeAcross="1" ><Data ss:Type="String">data here</Data></Cell>
13 <Cell ss:StyleID="s17"><Data ss:Type="String">data here</Data></Cell>
14 </Row>
15 </Table>
16 <Worksheet>
复制代码
复制代码

每一个Worksheet代表一个工作薄,即Excel中每一个工作页,”ss:Name” 代表工作表的名字,可以有多个工作表,不可重名。

<Worksheet>二级标签主要是<Table>和<WorksheetOptions>

 

4.7.1 <Table> (必须)

每一页工作表为一个Table,Table标签常用属性有

属性

ss:DefaultColumnWidth

数字,默认列宽

ss:StyleID

引用样式ID

 

 

 

 

 

注:每一处引用Style样式(包括后面的单元格样式),必须在之前的Styles标签中定义过,否则无法正常打开文件。

 

Table的二级标签主要是<Column>和<Row>:

  1. <Column>主要作用是放在< Row >之前,控制每一列的宽度,不作显示数据用;
  2. < Row >是具体行,<Row>标签内是<Cell>,确定行内的每一个单元格。

Table的形式是这样的,先确定列宽度,然后确定行,然后确定单元格,其中值得注意的是列宽只能在<Column>的属性里确定,不能在<Cell>里指定单元格宽度;行高只能在<Row>的属性里确定,不能在<Cell>里指定单元格高度,否则会出现问价无法发开的情况。例如这样设置是正确的:

<Column ss:StyleID="xxC" ss:Width="35" ss:AutoFitWidth="0"/>

<Row ss:StyleID="xxR" ss:Height="15" ss:AutoFitHeight="0">

 

<Column>主要属性有:

属性

ss:Width

数字,宽度

ss:AutoFitWidth

数字,”0”代表非自动宽度

 

 

 

 

 

<Row>主要属性有:

属性

ss:Height

数字,高度

ss:AutoFitHeight

数字,”0”代表非自动高度

 

 

 

 

 

<Cell>内一般放置单元格的内容,用<Data>来包裹内容,<Cell>的主要属性有:

属性

ss:MergeAcross

数字,向右合并单元的数

ss:MergeDown

数字,向下合并单元格数

ss:Index="5"

标记列号,index列号不受合并影响。

 

 

 

 

 

 

注:列号可以不指定,但是不便于代码阅读,例如,在A1这个<Cell>位置做了向下合并3,向右合并2,代码如下来编写:

复制代码
复制代码
1 <Table>  
2 <Row>
3 <Cell ss:Index="1" ss:MergeDown="3" ss:MergeAcross="2"><Cell/
4 <Cell ss:Index="4" ><Cell/>
5 </Row>
6 <Row>
7 <Cell ss:Index="4" ><Cell/>
8 </Row>
9 <Row>
10 <Cell ss:Index="4" ><Cell/>
11 </Row>
12 <Row>
13 <Cell ss:Index="4" ><Cell/>
14 </Row>
15 <Row>
16 <Cell ss:Index="1" ><Cell/>
17 <Cell ss:Index="2" ><Cell/>
18 <Cell ss:Index="3" ><Cell/>
19 <Cell ss:Index="4" ><Cell/>
20 </Row>
21 </Table>
复制代码
复制代码

效果:

 

<Data>的属性主要有:

属性

ss:Type

枚举:”String”/”Number”/…

 

 

 

4.7.2 <WorksheetOptions> (非必须)

此处为一些附加信息,例如

复制代码
复制代码
1 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 
2 <PageSetup>
3 <Print>
4 <Zoom>80</Zoom>
5 <Selected/>
6 <Panes>
7 <ProtectObjects>False</ProtectObjects>
8 <ProtectScenarios>False</ProtectScenarios>
9 </WorksheetOptions>
复制代码
复制代码

 

 

 

五. EBS系统的开发过程

在实际开发的过程中,一般流程是现在Excel中确定报表的模板,格式,然后把”.xls” 或”.xlsx” 另存为”.xml”文件,用文本编辑器编辑一些内容,删除冗余代码,然后再关键逻辑(一般是动态增长的部分)用程序逻辑来控制输出即可。可执行和并发程序的定义和标准一致,并发程序的输出用“HTML”。

 

5.1 PLSQL程序

PLSQL程序输出

1 PROCEDURE output(p_content IN VARCHAR2) IS 
2 BEGIN
3
4 fnd_file.put_line(fnd_file.output, p_content);
5
6 END output;

例如用以上的方法输出,就可以在并发程序的输出中查看,而在程序中,输出的方法,形式,按照具体的需求来就行,和PLSQL输出HTML报表的做法大致相同。

 

 

5.2 可执行及并发程序

可执行定义

定义可执行为PL/SQL程序,关联相应的程序包方法,例如:

 

并发程序定义

定义并发程序关联可执行,设置参数,例如:

 

 

 

5.3 效果

打开方式及效果

并发程序输出用Excel方式打开:

 

浏览器中可选择以下方式:

 

保存或者直接打开,选择“打开”时如下的提示,选择”是”:

 

如果XML脚本正确,Excel就可以打开:

 

如果是之前是的是“保存”,然后手动打开,会有如下提示:

 

点击“是”直接打开。

 

5.4 快速获取XML脚本

逆向工程

对于开发时,没有必要一行一行的写脚本,先用Excel编辑”.xls” 或”.xlsx” 文件,然后另存为”.xml”文件,把一些非必须的脚本代码删除,有关键逻辑的地方特殊处理,就可以在plsql代码中直接输出了。

对于一些特殊的格式,属性的设置,也没有太大的必要去记住,在需要时,”.xls” 或”.xlsx” 文件逆向工程生成XML文件就可以根据ID查出单元格等各种属性的设置。

 

注:用逆向工程生成的代码,在<styles>内重复率很高,代码重用率不高,对于这种代码,可以进行合并,在指定worksheet里各个tag的属性时,根据需要,调整ID,清除不必要的重复。