EBS中 EXCEL 格式报表输出的公用API

http://blog.itpub.net/10359218/viewspace-752601/

最近的项目上写了一个公用的API,很久以前就用EXCEL发布过报表，但从没想过写API来简化。。。

 

 

其实方法很简单，就是自已用EXCEL做个模板，然后另存为XML 电子表格，用TEXT工具打开后，看一下XML 代码就明白了。这个程序无非就是用请求的输出来打印这个XML代码而已.

 

主要功能:

1. 支持数据格式包括字符型，数值型，日期型

2. 支持单元格样式（这个写的比较不灵活，如果有新增样式，需要在sheet_start里增加

3. 支持公式及数组公式

4. 支持自动换行(在样式里)

5. 支持单元格合并(左右合并及上下合并)

6. 支持水平方向定位(INDEX)

 

注意:

在注册请求时，输出方式需定义为XML。请求执行完毕后，点输出，然后选择EXCEL打开即可

 

 

CREATE OR REPLACE PACKAGE BODY cux_common_report_util_pkg IS

 

  --*************************************************************************************

  --*              Copyright (c) XXXXXXXX, 2012

  --*                     All Rights Reserved

  --*************************************************************************************

  --** SYSTEM:                   EBS R12

  --** PROGRAM NAME:             cux_common_report_util_pkg.pls

  --** AUTHOR/EMAIL_ID:          Tony Liu

  --** DATE CREATED:             2013-1-13

  --** LATEST DATE MODIFIED:     2013-1-13

  --**

  --** DESCRIPTION:              用于发布输出格式为EXCEL的报表公用工具包

  --**

  --** INPUTS:

  --**

  --** OUTPUTS:                  N/A

  --**

  --** FUNCTION(S) CALLED:       N/A

  --**

  --** CAUTIONS:

  --**

  --**************************************************************************************

  --** Revision History

  --**   Date                   Revision Number        By         Reason/Defect

  --**-----------------------------------------------------------------------------

  --** 2013-1-13               1.0                     Tony Liu      Initial Version

  --**************************************************************************************

 

 

  --******************************************************************************

  -- Procedure: 输出信息到请求输出(OUTPUT)中

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE line(p_text IN VARCHAR2) IS

  BEGIN

    fnd_file.put_line(fnd_file.output, p_text);

    --dbms_output.put_line(p_text);

  END;

 

 

 

  --******************************************************************************

  -- Procedure: 输出XML文件的头部信息

  -- 版本: 1.0

  -- Author: 刘峰

  -- 说明:  author 传入提交请求的员工全名

  --        creation_date 格式为 to_char(sysdate,'yyyy-mm-ddhh24:mi:ss')

  --        company_name 传入xxxxxxx

  --******************************************************************************

  PROCEDURE sheet_start(p_author        IN VARCHAR2,

                        p_report_name   IN VARCHAR2,

                        p_creation_date IN VARCHAR2 DEFAULT to_char(SYSDATE,

                                                                    'yyyy-mm-dd hh24:mi:ss'),

                        p_company_name  IN VARCHAR2 DEFAULT 'XXXXXX') IS

  

    v_common_info1 VARCHAR2(32767);

    v_common_info2 VARCHAR2(32767);

    v_sheet_name   VARCHAR2(500);

    v_date         VARCHAR2(20);

    v_time         VARCHAR2(20);

    TYPE t_style_tbl IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

    v_style_tbl t_style_tbl;

  

  BEGIN

  

    v_date := substr(p_creation_date, 1, 10);

    v_time := substr(p_creation_date, 12);

  

    --XML 头信息

    v_common_info1 := '

                     

                     

                      xmlns:o="urn:schemas-microsoft-com:office:office"

                       xmlns:x="urn:schemas-microsoft-com:office:excel"

                       xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

                       xmlns:html="http://www.w3.org/TR/REC-html40">

                       

                       ' || p_author ||

                      '

                       ' || p_author ||

                      '

                       ' || v_date || 'T' ||

                      v_time || 'Z

                       ' || p_company_name ||

                      '';

    v_common_info2 := '14.00

                       

                       

                       

                       

                       

                       11250

                       21420

                       120

                       15

                       False

                       False

                       ';

  

    --工作表缺省样式

    v_style_tbl(1) := '

                        //此为模板的默认style

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

    --参数使用的样式

    v_style_tbl(2) := '

                       //此为参数的默认style

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --报表标题的样式(自动换行)

    v_style_tbl(3) := '

                       //此为报表标题的默认style

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --列标题的样式(自动换行)

    v_style_tbl(4) := '

                       //此为报表列标题的默认style

                       

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --普通日期样式

    v_style_tbl(5) := '

                       //此为日期型字段的默认style

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --高亮日期样式

    v_style_tbl(6) := '

                       //此为日期型字段的带背景色的style(黄色加强显示)

                       

                       

                       

                       

                       

                       

                       

                       

                      ';

  

    --普通会计数值样式

    v_style_tbl(7) := '

                       //此为会计用数值型字段的默认style

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --高亮会计数值样式

    v_style_tbl(8) := '

                       //此为会计用数值型字段的带背景色的style(黄色加强显示)

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --常规样式(自动换行)

    v_style_tbl(9) := '

                       //此为普通字段的style

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --常规高亮样式(自动换行)

    v_style_tbl(10) := '

                         //此为普通字段的的带背景色的style(黄色加强显示)

                         

                           

                           

                           

                           

                           

                           

                           

                         ';

    --会计(4位小数)样式

    v_style_tbl(11) := '

                       //此为会计用数值型字段的默认style(保留4位小数)

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

  

    --会计(4位小数)高亮样式

    v_style_tbl(12) := '

                       //此为会计用数值型字段的带背景色的style(保留4位小数,黄色加强显示)

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                     ';

  

  

    --会计样式（两位小数，负数加括号，0显示为'-')

    --样式：999,999,999.00 每三位一个分隔符999,999,999.00，负数用空号包住(123)，如果是零 则是' - '

    v_style_tbl(13) := '                        

                       

                       

                       

                       

                       

                       

                       

                       

                                              

                                               

                        ';

  

    --会计样式（两位小数，负数加括号，0显示为'-', 并用黄色加亮)

    --样式：999,999,999.00 每三位一个分隔符999,999,999.00，负数用空号包住(123)，如果是零 则是' - '

    v_style_tbl(14) := '                        

                       

                       

                       

                       

                       

                       

                       

                       

                       

                                            

                        ';

  

  

    --定义工作表名称

    IF p_report_name IS NULL THEN

      v_sheet_name := '';

    ELSE

      v_sheet_name := '';

    END IF;

  

    --输出XML头

    line(v_common_info1 || v_common_info2);

  

    --输出样式

    FOR i IN 1 .. v_style_tbl.count

    LOOP

      line(v_style_tbl(i));

    END LOOP;

  

    --样式结尾

    line('');

    --工作表名称

    line(v_sheet_name);

  

  

  

  END;

 

  --******************************************************************************

  -- Procedure: 输出XML文件的结尾信息

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE sheet_end IS

  BEGIN

    line('

             

             

             

             

             

             

             

             

              0

              0

             

             

             

             

              3

              4

             

             

              False

              False

             

           

            ');

  END;

 

 

  --******************************************************************************

  -- Procedure: 表格开始

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE table_start IS

  BEGIN

    line('');

  END;

 

 

  --******************************************************************************

  -- Procedure: 表格结束

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE table_end IS

  BEGIN

    line('

');

  END;

 

 

  --******************************************************************************

  -- Procedure: 初始化列宽

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE define_column(p_width IN NUMBER, p_span IN NUMBER DEFAULT NULL) IS

  BEGIN

    IF p_span IS NULL THEN

      line('');

    ELSE

      line('');

    END IF;

  END;

 

 

  --******************************************************************************

  -- Procedure: 输出报表标题

  -- 版本: 1.0

  -- Author: 刘峰

  -- 说明:   merge: 向右合并 值要比将合并的列小1,比如要合并3列，只需传入2

  --        merge_down: 向下合并

  --******************************************************************************

  PROCEDURE rpt_header(p_text       IN VARCHAR2,

                       p_merge      IN NUMBER DEFAULT NULL,

                       p_merge_down IN NUMBER DEFAULT NULL) IS

    v_merge      VARCHAR2(100);

    v_merge_down VARCHAR2(100);

  BEGIN

    IF p_merge IS NOT NULL

       AND p_merge > 0 THEN

      v_merge := 'ss:MergeAcross="' || p_merge || '"';

    END IF;

    IF p_merge_down IS NOT NULL

       AND p_merge_down > 0 THEN

      v_merge_down := 'ss:MergeDown="' || p_merge_down || '"';

    END IF;

  

    line('

               <cell '="" ||="" v_merge="" v_merge_down="" ||<="" div="">

         'ss:StyleID="s1">' || p_text ||

         '

          ');

  

  

  END;

 

 

  --******************************************************************************

  -- Procedure: 输出报表参数

  -- 版本: 1.0

  -- Author: 刘峰

  -- 说明:   merge：向右合并，值要比将合并的列小1,比如要合并3列，只需传入2

  --        merge_down: 向下合并

  --        index: 单元格的位置(水平第N列)

  --******************************************************************************

  PROCEDURE rpt_parameter(p_text       IN VARCHAR2,

                          p_merge      IN NUMBER DEFAULT NULL,

                          p_merge_down IN NUMBER DEFAULT NULL,

                          p_index      IN NUMBER DEFAULT NULL) IS

  

    v_merge      VARCHAR2(100);

    v_merge_down VARCHAR2(100);

    v_index      VARCHAR2(100);

  

  BEGIN

    IF p_merge IS NOT NULL

       AND p_merge > 0 THEN

      v_merge := 'ss:MergeAcross="' || p_merge || '"';

    END IF;

    IF p_merge_down IS NOT NULL

       AND p_merge_down > 0 THEN

      v_merge_down := 'ss:MergeDown="' || p_merge_down || '"';

    END IF;

    IF p_index IS NOT NULL

       AND p_index > 0 THEN

      v_index := 'ss:Index="' || p_index || '"';

    END IF;

  

    line('<cell '="" ||="" v_index="" v_merge="" v_merge_down="" ||<="" div="">

         'ss:StyleID="m1">' || p_text ||

         '');

  

  END;

 

 

  --******************************************************************************

  -- Procedure: 行开始

  -- 版本: 1.0row_start

  -- Author: 刘峰

  -- 说明：Height : 行高度

  --******************************************************************************

  PROCEDURE row_start(p_height IN NUMBER DEFAULT NULL) IS

    v_height VARCHAR2(100);

  

  BEGIN

    IF p_height IS NOT NULL

       AND p_height > 0 THEN

      v_height := 'ss:Height="' || p_height || '"';

    END IF;

    line('');

  END;

 

 

  --******************************************************************************

  -- Procedure: 行结束

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE row_end IS

  BEGIN

    line('');

  END;

 

 

  --******************************************************************************

  -- Procedure: 输出列标题

  -- 版本: 1.0

  -- Author: 刘峰

  -- 说明:  merge的值要比将合并的列小1,比如要合并3列，只需传入2

  --        merge_down: 向下合并

  --        index: 单元格的位置(水平第N列)

  --******************************************************************************

  PROCEDURE rpt_title(p_text       IN VARCHAR2,

                      p_merge      IN NUMBER DEFAULT NULL,

                      p_merge_down IN NUMBER DEFAULT NULL,

                      p_index      IN NUMBER DEFAULT NULL) IS

  

    v_merge      VARCHAR2(100);

    v_merge_down VARCHAR2(100);

    v_index      VARCHAR2(100);

    v_wrap       VARCHAR2(100);

  

  BEGIN

  

    IF p_merge IS NOT NULL

       AND p_merge > 0 THEN

      v_merge := 'ss:MergeAcross="' || p_merge || '"';

    END IF;

    IF p_merge_down IS NOT NULL

       AND p_merge_down > 0 THEN

      v_merge_down := 'ss:MergeDown="' || p_merge_down || '"';

    END IF;

    IF p_index IS NOT NULL

       AND p_index > 0 THEN

      v_index := 'ss:Index="' || p_index || '"';

    END IF;

  

    line('<cell '="" ||="" v_index="" v_merge="" v_merge_down="" ||<="" div="">

         'ss:StyleID="s2">' || p_text ||

         '');

  

  END;

 

 

  --******************************************************************************

  -- Procedure: 输出报表明细

  -- 版本: 1.0

  -- Author: 刘峰

  -- 说明: type 包括 String/Number/DateTime, 注意大小写

  --       formula 必须要以=开头

  --       merge的值要比将合并的列小1,比如要合并3列，只需传入2

  --        merge_down: 向下合并

  --        index: 单元格的位置(水平第N列)

  --        array_range: 数组公式

  --******************************************************************************

  PROCEDURE rpt_detail(p_text        IN VARCHAR2 DEFAULT NULL,

                       p_type        IN VARCHAR2 DEFAULT 'String',

                       p_formula     IN VARCHAR2 DEFAULT NULL,

                       p_style.       IN VARCHAR2 DEFAULT NULL,

                       p_merge       IN NUMBER DEFAULT NULL,

                       p_merge_down  IN NUMBER DEFAULT NULL,

                       p_index       IN NUMBER DEFAULT NULL,

                       p_array_range IN BOOLEAN DEFAULT FALSE) IS

  

    v_style.       VARCHAR2(50);

    v_merge       VARCHAR2(50);

    v_type        VARCHAR2(50);

    v_formula     VARCHAR2(30000);

    v_index       VARCHAR2(100);

    v_merge_down  VARCHAR2(100);

    v_array_range VARCHAR2(100);

  

  BEGIN

  

    IF p_type = 'Number' THEN

      v_type := '';

    ELSIF p_type = 'String' THEN

      v_type := '';

    ELSIF p_type = 'DateTime' THEN

      v_type := '';

    END IF;

  

    IF p_formula IS NOT NULL THEN

      v_formula := 'ss:Formula="' || p_formula || '"';

    END IF;

  

    IF p_style. IS NOT NULL THEN

      v_style.:= 'ss:StyleID="' || p_style. '"';

    ELSE

      v_style.:= 'ss:StyleID="s7"'; --常规格式

    END IF;

    IF p_merge IS NOT NULL

       AND p_merge > 0 THEN

      v_merge := 'ss:MergeAcross="' || p_merge || '"';

    END IF;

  

    IF p_merge_down IS NOT NULL

       AND p_merge_down > 0 THEN

      v_merge_down := 'ss:MergeDown="' || p_merge_down || '"';

    END IF;

  

    IF p_index IS NOT NULL

       AND p_index > 0 THEN

      v_index := 'ss:Index="' || p_index || '"';

    END IF;

  

    IF p_array_range THEN

      v_array_range := 'ss:ArrayRange="RC"';

    END IF;

  

    line('<cell '="" ||="" v_index="" v_merge="" v_merge_down="" ||<="" div="">

         v_style. ' ' || v_array_range || ' ' || v_formula || '>' ||

         v_type || p_text || '');

  END;

 

 

 

  --******************************************************************************

  -- Procedure: 示例代码

  -- 版本: 1.0

  -- Author: 刘峰

  --******************************************************************************

  PROCEDURE report_sample(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS

  

    g_date_style.     VARCHAR2(10) := 's3'; --普通日期型

    g_date_h_style.   VARCHAR2(10) := 's4'; --高亮日期型

    g_number_style.   VARCHAR2(10) := 's5'; --普通会计数字

    g_number_h_style. VARCHAR2(10) := 's6'; --高亮会计数字

  

    g_normal_h_style. VARCHAR2(10) := 's7'; --通用型

    g_normal_style.   VARCHAR2(10) := 's8'; --高亮通用型

  

    g_number4_style.   VARCHAR2(10) := 's9'; --普通会计数字(保留4位小数)

    g_number4_h_style. VARCHAR2(10) := 's10'; --高亮会计数字(保留4位小数)

  

    v_person VARCHAR2(50);

  BEGIN

  

    --获取提交人名称

    BEGIN

      SELECT t.full_name

        INTO v_person

        FROM per_all_people_f t

       WHERE t.person_id =

             (SELECT fu.employee_id

                FROM fnd_user fu

               WHERE fu.user_id = fnd_global.user_id)

         AND nvl(t.effective_end_date, SYSDATE + 1) > SYSDATE;

    EXCEPTION

      WHEN OTHERS THEN

        SELECT fu.user_name

          INTO v_person

          FROM fnd_user fu

         WHERE fu.user_id = fnd_global.user_id;

    END;

  

    --定义XML头信息

    cux_common_report_util_pkg.sheet_start(p_author        => v_person,

                                           p_creation_date => to_char(SYSDATE,

                                                                      'yyyy-mm-dd hh:mi:ss'),

                                           p_company_name  => 'XXXXXX',

                                           p_report_name   => '测试报表');

  

    --表格开始

    cux_common_report_util_pkg.table_start;

  

    --定义列

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

    cux_common_report_util_pkg.define_column(150);

  

    --输出报表标题

    cux_common_report_util_pkg.rpt_header('测试报表', 9);

  

    --输出报表参数

    cux_common_report_util_pkg.row_start;

    cux_common_report_util_pkg.rpt_parameter('参数1: WELCOME', 2);

    cux_common_report_util_pkg.rpt_parameter('参数2: HELLO', 2);

    cux_common_report_util_pkg.row_end;

  

    --输出报表列标题

    cux_common_report_util_pkg.row_start;

  

    cux_common_report_util_pkg.rpt_title('普通字符型');

    cux_common_report_util_pkg.rpt_title('高亮字符型');

    cux_common_report_util_pkg.rpt_title('普通数字型');

    cux_common_report_util_pkg.rpt_title('高亮数字型');

    cux_common_report_util_pkg.rpt_title('会计数字型');

    cux_common_report_util_pkg.rpt_title('高亮会计数字型');

    cux_common_report_util_pkg.rpt_title('普通日期型');

    cux_common_report_util_pkg.rpt_title('高亮日期型');

    cux_common_report_util_pkg.rpt_title('普通公式列');

    cux_common_report_util_pkg.rpt_title('高亮公式列');

  

  

    cux_common_report_util_pkg.row_end;

  

    --输出报表明细

    cux_common_report_util_pkg.row_start;

    --字符类型

    cux_common_report_util_pkg.rpt_detail(p_text  => '王小二',

                                          p_type  => 'String',

                                          p_style. => g_normal_style);

    cux_common_report_util_pkg.rpt_detail(p_text  => '男性',

                                          p_type  => 'String',

                                          p_style. => g_normal_h_style);

    --数值类型

    cux_common_report_util_pkg.rpt_detail(p_text  => '1234.345',

                                          p_type  => 'Number',

                                          p_style. => g_normal_style);

    cux_common_report_util_pkg.rpt_detail(p_text  => '345.456',

                                          p_type  => 'Number',

                                          p_style. => g_normal_h_style);

  

    cux_common_report_util_pkg.rpt_detail(p_text  => '34324.345',

                                          p_type  => 'Number',

                                          p_style. => g_number_style);

    cux_common_report_util_pkg.rpt_detail(p_text  => '34324.345',

                                          p_type  => 'Number',

                                          p_style. => g_number_h_style);

    --日期类型

    cux_common_report_util_pkg.rpt_detail(p_text  => to_char(trunc(SYSDATE),

                                                             'yyyy-mm-dd'),

                                          p_type  => 'DateTime',

                                          p_style. => g_date_style);

    cux_common_report_util_pkg.rpt_detail(p_text  => to_char(trunc(SYSDATE),

                                                             'yyyy-mm-dd'),

                                          p_type  => 'DateTime',

                                          p_style. => g_date_h_style);

  

    --公式类型

    cux_common_report_util_pkg.rpt_detail(p_text    => NULL,

                                          p_formula => '=rc[-6]+rc[-5]',

                                          p_type    => 'Number',

                                          p_style.   => g_number_style);

    cux_common_report_util_pkg.rpt_detail(p_text    => NULL,

                                          p_formula => '=rc[-5]+rc[-4]',

                                          p_type    => 'Number',

                                          p_style.   => g_number_h_style);

  

    cux_common_report_util_pkg.row_end;

  

    --表格结束

    cux_common_report_util_pkg.table_end;

  

    --工作表结束

    cux_common_report_util_pkg.sheet_end;

  END;

 

 

 

END cux_common_report_util_pkg;