精华内容
下载资源
问答
  • Oracle 导出导入数据库

    千次阅读 2018-10-24 16:02:06
    好久在windows环境下没进行过导入导出数据库操作,最近操作了一下,出现了一些小问题,重新尝试了下各种导出,做过记录。 环境准备 window环境下执行数据库导入导出,其先决条件有两个: 1,安装了oracle的客户端...

    好久在windows环境下没进行过导入导出数据库操作,最近操作了一下,出现了一些小问题,重新尝试了下各种导出,做过记录。

    环境准备

    window环境下执行数据库导入导出,其先决条件有两个:

    1,安装了oracle的客户端或服务器端;

    2,在windows路径设置了相应的oracle路径。

    在以上情况下,在dos环境中可以通过输入 exp 导出数据库数据,imp 导入数据库数据。

    参数说明

    可通过输入命令 exp help=y 查询查看具体exp的各项参数情况如下

    C:\Users\Administrator>exp help=y
    
    Export: Release 11.2.0.1.0 - Production on 星期三 10月 24 11:50:09 2018
    
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    
    通过输入 EXP 命令和您的用户名/口令, 导出
    操作将提示您输入参数:
    
         例如: EXP SCOTT/TIGER
    
    或者, 您也可以通过输入跟有各种参数的 EXP 命令来控制导出
    的运行方式。要指定参数, 您可以使用关键字:
    
         格式:  EXP KEYWORD=value 或 KEYWORD=(value1,value2,..
    展开全文
  • 1.本地数据库导入导出1.导出(运行---cmd中操作)exp 用户名/密码@数据库实例名file=本地存放路径eg: exp jnjp/jnjp@ORCL file=C:/jnjp.dmp2.导入imp 用户名/密码@数据库实例名file=本地数据库文件存放路径full=y ...

    1.本地数据库导入导出

    1.导出

    (运行---cmd中操作)

    exp 用户名/密码@数据库实例名file=本地存放路径

    eg: exp jnjp/jnjp@ORCL file=C:/jnjp.dmp

    2.导入

    imp 用户名/密码@数据库实例名file=本地数据库文件存放路径full=y ignore=y

    eg: imp jnjp/jnjp@orcl file=E:\jnjp.dmp full=y ignore=y

    2.远程数据库导入导出

    1.导出

    exp 用户名/密码@ip地址/数据库实例名file=本地存放路径

    eg: exp jnjp/jnjp@10.15.135.18/orcl file=C:/jnjp.dmp

    2.导入

    imp 用户名/密码@ip地址/数据库实例名file=本地数据库文件存放路径full=y

    3.EXP的所有参数(括号中为参数的默认值)

    USERID 用户名/口令 如:USERID=duanl/duanl

    FULL 导出整个数据库 (N)

    BUFFER 数据缓冲区的大小

    OWNER 所有者用户名列表,你希望导出哪个用户的对象,就用owner=username

    FILE 输出文件 (EXPDAT.DMP)

    TABLES 表名列表 ,指定导出的table名称,如:TABLES=table1,table2

    COMPRESS 导入一个extent (Y)

    RECORDLENGTH IO 记录的长度

    GRANTS 导出权限 (Y)

    INCTYPE 增量导出类型

    INDEXES 导出索引 (Y)

    RECORD 跟踪增量导出 (Y)

    ROWS 导出数据行 (Y)

    PARFILE 参数文件名,如果你exp的参数很多,可以存成参数文件.

    CONSTRAINTS 导出约束 (Y)

    CONSISTENT 交叉表一致性

    LOG 屏幕输出的日志文件

    STATISTICS 分析对象 (ESTIMATE)

    DIRECT 直接路径 (N)

    TRIGGERS 导出触发器 (Y)

    FEEDBACK 显示每 x行(0)的进度

    FILESIZE 各转储文件的最大尺寸

    QUERY 选定导出表子集的子句

    下列关键字仅用于可传输的表空间

    TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)

    TABLESPACES 将传输的表空间列表

    4.IMP的所有参数(括号中为参数的默认值)

    USERID 用户名/口令

    FULL 导入整个文件 (N)

    BUFFER 数据缓冲区大小

    FROMUSER 所有人用户名列表

    FILE 输入文件 (EXPDAT.DMP)

    TOUSER 用户名列表

    SHOW 只列出文件内容 (N)

    TABLES 表名列表

    IGNORE 忽略创建错误 (N)

    RECORDLENGTH IO 记录的长度

    GRANTS 导入权限 (Y)

    INCTYPE 增量导入类型

    INDEXES 导入索引 (Y)

    COMMIT 提交数组插入 (N)

    ROWS 导入数据行 (Y)

    PARFILE 参数文件名

    LOG 屏幕输出的日志文件

    CONSTRAINTS 导入限制 (Y)

    DESTROY 覆盖表空间数据文件 (N)

    INDEXFILE 将表/索引信息写入指定的文件

    SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)

    ANALYZE 执行转储文件中的 ANALYZE语句(Y)

    FEEDBACK 显示每 x行(0)的进度

    TOID_NOVALIDATE 跳过指定类型 id的校验

    FILESIZE 各转储文件的最大尺寸

    RECALCULATE_STATISTICS 重新计算统计值 (N)

    下列关键字仅用于可传输的表空间

    TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)

    TABLESPACES 将要传输到数据库的表空间

    DATAFILES 将要传输到数据库的数据文件

    TTS_OWNERS 拥有可传输表空间集中数据的用户

    5.EXP常用选项

    1.FULL

    这个用于导出整个数据库,在ROWS=N一起使用时,可以导出整个数据库的结构。例如:

    exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y

    注:在oracle10g中,userid这个是可以不用写的直接写 用户名/密码 就可以了像这样:

    exp test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y

    2.OWNER和TABLE

    这两个选项用于定义EXP的对象。OWNER定义导出指定用户的对象;TABLE指定EXP的table名称,例如:

    exp userid=test/test file=./db_str.dmp log=./db_str.log owner=duanl

    注:有的时候我们可能会遇到只导出单表的情况,下面这种方法可以帮你解决问题

    exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap

    3.FILE和LOG

    这两个参数分别指定备份的DMP名称和LOG名称,包括文件名和目录,例子见上面。

    4.COMPRESS

    COMPRESS 参数不压缩导出数据的内容。用来控制导出对象的storage语句如何产生。默认值为Y,使用默认值,对象的存储语句的init extent等于当前导出对象的extent的总和。推荐使用COMPRESS=N。

    6.IMP常用选项

    1、FROMUSER和TOUSER

    使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。例如:假设我们做exp时导出的为test的对象,现在我们想把对象导入用户:

    imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1

    2、IGNORE、GRANTS和INDEXES

    其中IGNORE参数将忽略表的存在,继续导入,这个对于需要调整表的存储参数时很有用,我们可以先根据实际情况用合理的存储参数建好表,然后直接导入数据。而GRANTS和INDEXES则表示是否导入授权和索引,如果想使用新的存储参数重建索引,或者为了加快到入速度,我们可以考虑将INDEXES设为N,而GRANTS一般都是Y。例如:imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N

    7.导入导出实例

    下面介绍的是导入导出的实例,向导入导出看实例基本上就可以完成,因为导入导出很简单。

    1.数据导出:

    1.1将数据库TEST完全导出,用户名system密码manager导出到D:\daochu.dmp中

    exp system/manager@TEST file=d:\daochu.dmp full=y

    1.2 将数据库中system用户与sys用户的表导出

    exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)

    1.3 将数据库中的表table1、table2导出

    exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)

    1. 4 将数据库中的表table1中的字段filed1以"00"打头的数据导出

    exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

    2.数据的导入:

    2.1 将D:\daochu.dmp中的数据导入TEST数据库中。

    imp system/manager@TEST file=d:\daochu.dmp

    上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。

    在后面加上 ignore=y 就可以了。

    2. 2 将d:\daochu.dmp中的表table1导入

    imp system/manager@TEST file=d:\daochu.dmp tables=(table1)

    基本上上面的导入导出够用了。不少情况我是将表彻底删除,然后导入。

    3.导出、导入通配符的使用:

    想导出格式为TABLE_1,TABLE_2,TABLE_3这样的表,这样的表在库中共有300张,难道要一张张的写语句?太费神了吧,不用担心,oracle提供通配符可以使用来满足这样的需求:

    exp system/test@192.168.1.198/orcl file=d:\exp_test.dmptables=TABLE_% log=exp_test.log

    展开全文
  • Oracle导出excel

    2016-03-15 01:16:00
    oracle导出excel(非csv)的方法有两种,1、使用sqlplus spool,2、使用包体 现将网上相关代码整理后贴出以备不时之需: 使用sqlplus: 使用sqlplus需要两个文件:sql脚本文件和格式设置文件。 去除冗余信息,...

    oracle导出excel(非csv)的方法有两种,1、使用sqlplus  spool,2、使用包体 现将网上相关代码整理后贴出以备不时之需:

    使用sqlplus:

    使用sqlplus需要两个文件:sql脚本文件和格式设置文件。

    去除冗余信息,main.sql
    --main.sql   注意,需要在sqlplus下运行  非plsql命令行下
    set linesize 200
    set term off verify off feedback off pagesize 999 
    set markup html on entmap ON spool on preformat off
    spool test_tables.xls
    @get_tables.sql
    spool off
    exit

    sql脚本,get_tables.sql

    --get_tables.sql
    select * from all_objects where rownum<=1000;

    然后在sqlplus下运行main.sql

    使用包体编程:

     

    create or replace package smt_xlsx_maker_pkg is
    
      /******************************************************************************
         NAME:       SMT_XLSX_MAKER_PKG
         PURPOSE:    XLSX 生成 Pkg,主要是从Oracle数据库端生成Xlsx二进制的文件。
      
         REVISIONS:
         Ver        Date        Author           Description
         ---------  ----------  ---------------  ------------------------------------
         1.0        2011/2/19   Anton Scheffer   1,New Create the pkg
         1.1        2015/6/10     Sam.T          1.优化核心处理生成xlsx的代码,使得生成文档的执行效率大大提高!
         1.1        2015/6/10     Sam.T          1.query2sheet增加绑定变量的可选输入参数。
         1.2        2015/6/15     Sam.T          1.代码增加调试模式。直接设置G_DEBUG_MODE变量即可。
         1.2        2015/6/20     Sam.T          1.为方便使用,再次封装一些简单易用的过程,生成xlsx文档
         1.2        2015/7/5     Sam.T          1.单条SQL生成xlsx文档的内容,增加是否显示foot,以及显示的行数。
                                 jinzhao         解决数据量较大情况下导出excel报错xml的问题。将dbms_lob.writeappend修改为dbms_lob.append
      ******************************************************************************/
      ---------
      /**********************************************
      ******************************************************************************
      Copyright (C) 2011, 2012 by Anton Scheffer
      
      Permission is hereby granted, free of charge, to any person obtaining a copy
      of this software and associated documentation files (the "Software"), to deal
      in the Software without restriction, including without limitation the rights
      to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
      copies of the Software, and to permit persons to whom the Software is
      furnished to do so, subject to the following conditions:
      
      The above copyright notice and this permission notice shall be included in
      all copies or substantial portions of the Software.
      
      THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
      IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
      FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
      AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
      LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
      OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
      THE SOFTWARE.
      
      ******************************************************************************
      ******************************************** */
      /*
      使用方法:
      declare
        l_sql varchar2(30000);
      begin
        l_sql := 'select * from all_objects';
        smt_xlsx_maker_pkg.query2sheet(l_sql,
                                       true,
                                       'XLS_DIR',
                                       'Export2.xlsx');
      end;
      除此之外,基本上Excel 中有的效果它都可以生成。
      以下一个例子,包括居中,合并单元格,底色,新增工作表等:
      begin
        smt_xlsx_maker_pkg.clear_workbook;
        smt_xlsx_maker_pkg.new_sheet;
        smt_xlsx_maker_pkg.cell(5, 1, 5);
        smt_xlsx_maker_pkg.cell(3, 1, 3);
        smt_xlsx_maker_pkg.cell(2, 2, 45);
        smt_xlsx_maker_pkg.cell(3,
                                2,
                                'Anton Scheffer',
                                p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true));
        smt_xlsx_maker_pkg.cell(1,
                                4,
                                sysdate,
                                p_fontid => smt_xlsx_maker_pkg.get_font('Calibri',
                                                                        p_rgb => 'FFFF0000'));
        smt_xlsx_maker_pkg.cell(2,
                                4,
                                sysdate,
                                p_numfmtid => smt_xlsx_maker_pkg.get_numfmt('dd/mm/yyyy h:mm'));
        smt_xlsx_maker_pkg.cell(3,
                                4,
                                sysdate,
                                p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel('dd/mon/yyyy')));
        smt_xlsx_maker_pkg.cell(5,
                                5,
                                75,
                                p_borderid => smt_xlsx_maker_pkg.get_border('double',
                                                                            'double',
                                                                            'double',
                                                                            'double'));
        smt_xlsx_maker_pkg.cell(2, 3, 33);
        smt_xlsx_maker_pkg.hyperlink(1,
                                     6,
                                     'http://www.cnblogs.com/mellowsmile',
                                     'jinzhao site');
        smt_xlsx_maker_pkg.cell(1,
                                7,
                                'Some merged cells',
                                p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => 'center'));
        smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7);
        for i in 1 .. 5 loop
          smt_xlsx_maker_pkg.comment(3, i + 3, 'Row ' || (i + 3), 'Anton');
        end loop;
        smt_xlsx_maker_pkg.new_sheet;
        smt_xlsx_maker_pkg.set_row(1,
                                   p_fillid => smt_xlsx_maker_pkg.get_fill('solid',
                                                                           'FFFF0000'));
        for i in 1 .. 5 loop
          smt_xlsx_maker_pkg.cell(1, i, i);
          smt_xlsx_maker_pkg.cell(2, i, i * 3);
          smt_xlsx_maker_pkg.cell(3, i, 'x ' || i * 3);
        end loop;
        smt_xlsx_maker_pkg.query2sheet('select rownum, x.*
      , case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
      , case when mod( rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect by rownum <= 5');
        smt_xlsx_maker_pkg.save('XLS_DIR', 'Export3.xlsx');
      end;
      
      */
      g_debug_mode boolean := false;
    
      ---DBMS_OUTPUT直接输出/FILE_OUTPUT文档输出/REQUEST_OUTPUT请求日志输出/CONTEXT_OUTPUT 将日志改为上下文输出
      g_debug_type varchar2(240) := 'DBMS_OUTPUT';
    
      ---绑定变量用的临时表变量
      type rec_col_value is record(
        col_id    number(3),
        col_value varchar2(2400));
    
      type tab_col_value is table of rec_col_value index by binary_integer;
    
      --
      type tp_alignment is record(
        vertical   varchar2(11),
        horizontal varchar2(16),
        wraptext   boolean);
    
      --
      procedure clear_workbook;
    
      --
      procedure new_sheet(p_sheetname varchar2 := null);
    
      --
      function orafmt2excel(p_format varchar2 := null) return varchar2;
    
      --
      function get_numfmt(p_format varchar2 := null) return pls_integer;
    
      --
      function get_font(p_name      varchar2,
                        p_family    pls_integer := 2,
                        p_fontsize  number := 11,
                        p_theme     pls_integer := 1,
                        p_underline boolean := false,
                        p_italic    boolean := false,
                        p_bold      boolean := false,
                        p_rgb       varchar2 := null -- this is a hex ALPHA Red Green Blue value
                        ) return pls_integer;
    
      --
      function get_fill(p_patterntype varchar2,
                        p_fgrgb       varchar2 := null -- this is a hex ALPHA Red Green Blue value
                        ) return pls_integer;
    
      --
      function get_border(p_top    varchar2 := 'thin',
                          p_bottom varchar2 := 'thin',
                          p_left   varchar2 := 'thin',
                          p_right  varchar2 := 'thin')
      /*
        none
        thin
        medium
        dashed
        dotted
        thick
        double
        hair
        mediumDashed
        dashDot
        mediumDashDot
        dashDotDot
        mediumDashDotDot
        slantDashDot
        */
       return pls_integer;
    
      --
      function get_alignment(p_vertical   varchar2 := null,
                             p_horizontal varchar2 := null,
                             p_wraptext   boolean := null)
      /* horizontal
        center
        centerContinuous
        distributed
        fill
        general
        justify
        left
        right
        */
        /* vertical
        bottom
        center
        distributed
        justify
        top
        */
       return tp_alignment;
    
      --
      procedure cell(p_col       pls_integer,
                     p_row       pls_integer,
                     p_value     number,
                     p_numfmtid  pls_integer := null,
                     p_fontid    pls_integer := null,
                     p_fillid    pls_integer := null,
                     p_borderid  pls_integer := null,
                     p_alignment tp_alignment := null,
                     p_sheet     pls_integer := null);
    
      --
      procedure cell(p_col       pls_integer,
                     p_row       pls_integer,
                     p_value     varchar2,
                     p_numfmtid  pls_integer := null,
                     p_fontid    pls_integer := null,
                     p_fillid    pls_integer := null,
                     p_borderid  pls_integer := null,
                     p_alignment tp_alignment := null,
                     p_sheet     pls_integer := null);
    
      --
      procedure cell(p_col       pls_integer,
                     p_row       pls_integer,
                     p_value     date,
                     p_numfmtid  pls_integer := null,
                     p_fontid    pls_integer := null,
                     p_fillid    pls_integer := null,
                     p_borderid  pls_integer := null,
                     p_alignment tp_alignment := null,
                     p_sheet     pls_integer := null);
    
      --
      procedure hyperlink(p_col   pls_integer,
                          p_row   pls_integer,
                          p_url   varchar2,
                          p_value varchar2 := null,
                          p_sheet pls_integer := null);
    
      --
      procedure comment(p_col    pls_integer,
                        p_row    pls_integer,
                        p_text   varchar2,
                        p_author varchar2 := null,
                        p_width  pls_integer := 150 -- pixels
                       ,
                        p_height pls_integer := 100 -- pixels
                       ,
                        p_sheet  pls_integer := null);
    
      --
      procedure mergecells(p_tl_col pls_integer -- top left
                          ,
                           p_tl_row pls_integer,
                           p_br_col pls_integer -- bottom right
                          ,
                           p_br_row pls_integer,
                           p_sheet  pls_integer := null);
    
      --
      procedure list_validation(p_sqref_col   pls_integer,
                                p_sqref_row   pls_integer,
                                p_tl_col      pls_integer -- top left
                               ,
                                p_tl_row      pls_integer,
                                p_br_col      pls_integer -- bottom right
                               ,
                                p_br_row      pls_integer,
                                p_style       varchar2 := 'stop' -- stop, warning, information
                               ,
                                p_title       varchar2 := null,
                                p_prompt      varchar := null,
                                p_show_error  boolean := false,
                                p_error_title varchar2 := null,
                                p_error_txt   varchar2 := null,
                                p_sheet       pls_integer := null);
    
      --
      procedure list_validation(p_sqref_col    pls_integer,
                                p_sqref_row    pls_integer,
                                p_defined_name varchar2,
                                p_style        varchar2 := 'stop' -- stop, warning, information
                               ,
                                p_title        varchar2 := null,
                                p_prompt       varchar := null,
                                p_show_error   boolean := false,
                                p_error_title  varchar2 := null,
                                p_error_txt    varchar2 := null,
                                p_sheet        pls_integer := null);
    
      --
      procedure defined_name(p_tl_col     pls_integer -- top left
                            ,
                             p_tl_row     pls_integer,
                             p_br_col     pls_integer -- bottom right
                            ,
                             p_br_row     pls_integer,
                             p_name       varchar2,
                             p_sheet      pls_integer := null,
                             p_localsheet pls_integer := null);
    
      --
      procedure set_column_width(p_col   pls_integer,
                                 p_width number,
                                 p_sheet pls_integer := null);
    
      --
      procedure set_column(p_col       pls_integer,
                           p_numfmtid  pls_integer := null,
                           p_fontid    pls_integer := null,
                           p_fillid    pls_integer := null,
                           p_borderid  pls_integer := null,
                           p_alignment tp_alignment := null,
                           p_sheet     pls_integer := null);
    
      --
      procedure set_row(p_row       pls_integer,
                        p_numfmtid  pls_integer := null,
                        p_fontid    pls_integer := null,
                        p_fillid    pls_integer := null,
                        p_borderid  pls_integer := null,
                        p_alignment tp_alignment := null,
                        p_sheet     pls_integer := null);
    
      --
      procedure freeze_rows(p_nr_rows pls_integer := 1,
                            p_sheet   pls_integer := null);
    
      --
      procedure freeze_cols(p_nr_cols pls_integer := 1,
                            p_sheet   pls_integer := null);
    
      --
      procedure freeze_pane(p_col   pls_integer,
                            p_row   pls_integer,
                            p_sheet pls_integer := null);
    
      --
      procedure set_autofilter(p_column_start pls_integer := null,
                               p_column_end   pls_integer := null,
                               p_row_start    pls_integer := null,
                               p_row_end      pls_integer := null,
                               p_sheet        pls_integer := null);
    
      --
      function finish return blob;
    
      --
      procedure save(p_directory varchar2, p_filename varchar2);
    
      --
      ---当p_footer设定参数为真,这个g_query2sheet_footer有值,则会显示这个值的内容。没有则默认是Generated xxxx
      ---有一点说明的是,&ROWS 字段会被自动替换为结果返回的行数。
      g_query2sheet_footer varchar2(1000);
    
      g_query2sheet_rows   number; --结果返回的是多少行记录
      --这个是这个程序的"完全体"
      procedure query2sheet(p_sql            varchar2,
                            p_col_value_tab  smt_xlsx_maker_pkg.tab_col_value ---运行的动态SQL的绑定变量
                           ,
                            p_column_headers boolean := true,
                            p_directory      varchar2 := null,
                            p_filename       varchar2 := null,
                            p_sheet          pls_integer := null,
                            p_footer         boolean := true,
                            x_retcode        out number ---0:成功  非0:失败( 或者:0:成功  1:警告   2:错误  ----注意:确定警告的时候要做什么动作)
                           ,
                            x_errbuf         out varchar2 ---具体的错误信息
                            );
    
      ---默认用上绑定变量的逻辑!
      procedure query2sheet(p_sql            varchar2,
                            p_column_headers boolean := true,
                            p_directory      varchar2 := null,
                            p_filename       varchar2 := null,
                            p_sheet          pls_integer := null,
                            p_footer         boolean := true,
                            x_retcode        out number ---0:成功  非0:失败( 或者:0:成功  1:警告   2:错误  ----注意:确定警告的时候要做什么动作)
                           ,
                            x_errbuf         out varchar2 ---具体的错误信息
                            );
    
      ---最简单的使用版本
      procedure query2sheet(p_sql            varchar2,
                            p_column_headers boolean := true,
                            p_directory      varchar2 := null,
                            p_filename       varchar2 := null,
                            p_sheet          pls_integer := null,
                            p_footer         boolean := true);
    
      ---参考老外的,另外封装的一个好用的过程!游标直接输出excel。未测试过~
      procedure cursor2sheet(p_sql            in sys_refcursor,
                             p_column_headers boolean := true,
                             p_directory      varchar2 := null,
                             p_filename       varchar2 := null,
                             p_sheet          pls_integer := null,
                             p_footer         boolean := true);
    
      --
    end;
    smt_xlsx_maker_pkg 包头部分

     

    create or replace package body smt_xlsx_maker_pkg is
    
      --
      c_local_file_header        constant raw(4) := hextoraw('504B0304'); -- Local file header signature
      c_end_of_central_directory constant raw(4) := hextoraw('504B0506'); -- End of central directory signature
      --
      type tp_xf_fmt is record(
        numfmtid  pls_integer,
        fontid    pls_integer,
        fillid    pls_integer,
        borderid  pls_integer,
        alignment tp_alignment);
    
      type tp_col_fmts is table of tp_xf_fmt index by pls_integer;
    
      type tp_row_fmts is table of tp_xf_fmt index by pls_integer;
    
      type tp_widths is table of number index by pls_integer;
    
      type tp_cell is record(
        value number,
        style varchar2(50));
    
      type tp_cells is table of tp_cell index by pls_integer;
    
      type tp_rows is table of tp_cells index by pls_integer;
    
      type tp_autofilter is record(
        column_start pls_integer,
        column_end   pls_integer,
        row_start    pls_integer,
        row_end      pls_integer);
    
      type tp_autofilters is table of tp_autofilter index by pls_integer;
    
      type tp_hyperlink is record(
        cell varchar2(10),
        url  varchar2(1000));
    
      type tp_hyperlinks is table of tp_hyperlink index by pls_integer;
    
      subtype tp_author is varchar2(32767 char);
    
      type tp_authors is table of pls_integer index by tp_author;
    
      authors tp_authors;
    
      type tp_comment is record(
        text   varchar2(32767 char),
        author tp_author,
        row    pls_integer,
        column pls_integer,
        width  pls_integer,
        height pls_integer);
    
      type tp_comments is table of tp_comment index by pls_integer;
    
      type tp_mergecells is table of varchar2(21) index by pls_integer;
    
      type tp_validation is record(
        type             varchar2(10),
        errorstyle       varchar2(32),
        showinputmessage boolean,
        prompt           varchar2(32767 char),
        title            varchar2(32767 char),
        error_title      varchar2(32767 char),
        error_txt        varchar2(32767 char),
        showerrormessage boolean,
        formula1         varchar2(32767 char),
        formula2         varchar2(32767 char),
        allowblank       boolean,
        sqref            varchar2(32767 char));
    
      type tp_validations is table of tp_validation index by pls_integer;
    
      type tp_sheet is record(
        rows        tp_rows,
        widths      tp_widths,
        name        varchar2(100),
        freeze_rows pls_integer,
        freeze_cols pls_integer,
        autofilters tp_autofilters,
        hyperlinks  tp_hyperlinks,
        col_fmts    tp_col_fmts,
        row_fmts    tp_row_fmts,
        comments    tp_comments,
        mergecells  tp_mergecells,
        validations tp_validations);
    
      type tp_sheets is table of tp_sheet index by pls_integer;
    
      type tp_numfmt is record(
        numfmtid   pls_integer,
        formatcode varchar2(100));
    
      type tp_numfmts is table of tp_numfmt index by pls_integer;
    
      type tp_fill is record(
        patterntype varchar2(30),
        fgrgb       varchar2(8));
    
      type tp_fills is table of tp_fill index by pls_integer;
    
      type tp_cellxfs is table of tp_xf_fmt index by pls_integer;
    
      type tp_font is record(
        name      varchar2(100),
        family    pls_integer,
        fontsize  number,
        theme     pls_integer,
        rgb       varchar2(8),
        underline boolean,
        italic    boolean,
        bold      boolean);
    
      type tp_fonts is table of tp_font index by pls_integer;
    
      type tp_border is record(
        top    varchar2(17),
        bottom varchar2(17),
        left   varchar2(17),
        right  varchar2(17));
    
      type tp_borders is table of tp_border index by pls_integer;
    
      type tp_numfmtindexes is table of pls_integer index by pls_integer;
    
      type tp_strings is table of pls_integer index by varchar2(32767 char);
    
      type tp_str_ind is table of varchar2(32767 char) index by pls_integer;
    
      type tp_defined_name is record(
        name  varchar2(32767 char),
        ref   varchar2(32767 char),
        sheet pls_integer);
    
      type tp_defined_names is table of tp_defined_name index by pls_integer;
    
      type tp_book is record(
        sheets        tp_sheets,
        strings       tp_strings,
        str_ind       tp_str_ind,
        str_cnt       pls_integer := 0,
        fonts         tp_fonts,
        fills         tp_fills,
        borders       tp_borders,
        numfmts       tp_numfmts,
        cellxfs       tp_cellxfs,
        numfmtindexes tp_numfmtindexes,
        defined_names tp_defined_names);
    
      workbook tp_book;
    
      lc_rows  tp_rows; --new 2015.5.27
      --
      procedure debuglog(p_msg in varchar2) is
      begin
        if g_debug_type = 'DBMS_OUTPUT' then
          dbms_output.put_line(p_msg);
        elsif g_debug_type = 'FILE_OUTPUT' then
          --XYG_FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_MSG);
          null;
        elsif g_debug_type = 'REQUEST_OUTPUT' then
          --LOG(P_MSG);
          --FND_FILE.PUT_LINE (FND_FILE.LOG, P_MSG);
          null;
        end if;
      end debuglog;
    
      procedure blob2file(p_blob      blob,
                          p_directory varchar2 := 'MY_DIR',
                          p_filename  varchar2 := 'my.xlsx') is
        t_fh  utl_file.file_type;
        t_len pls_integer := 32767;
      begin
        t_fh := utl_file.fopen(p_directory, p_filename, 'wb');
        for i in 0 .. trunc((dbms_lob.getlength(p_blob) - 1) / t_len) loop
          utl_file.put_raw(t_fh, dbms_lob.substr(p_blob, t_len, i * t_len + 1));
        end loop;
        utl_file.fclose(t_fh);
      end;
    
      --
      function raw2num(p_raw raw, p_len integer, p_pos integer) return number is
      begin
        return utl_raw.cast_to_binary_integer(utl_raw.substr(p_raw,
                                                             p_pos,
                                                             p_len),
                                              utl_raw.little_endian);
      end;
    
      --
      function little_endian(p_big number, p_bytes pls_integer := 4) return raw is
      begin
        return utl_raw.substr(utl_raw.cast_from_binary_integer(p_big,
                                                               utl_raw.little_endian),
                              1,
                              p_bytes);
      end;
    
      --
      function blob2num(p_blob blob, p_len integer, p_pos integer) return number is
      begin
        return utl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob,
                                                              p_len,
                                                              p_pos),
                                              utl_raw.little_endian);
      end;
    
      --
      procedure add1file(p_zipped_blob in out blob,
                         p_name        varchar2,
                         p_content     blob) is
        t_now        date;
        t_blob       blob;
        t_len        integer;
        t_clen       integer;
        t_crc32      raw(4) := hextoraw('00000000');
        t_compressed boolean := false;
        t_name       raw(32767);
      begin
        t_now := sysdate;
        t_len := nvl(dbms_lob.getlength(p_content), 0);
        if t_len > 0 then
          t_blob       := utl_compress.lz_compress(p_content);
          t_clen       := dbms_lob.getlength(t_blob) - 18;
          t_compressed := t_clen < t_len;
          t_crc32      := dbms_lob.substr(t_blob, 4, t_clen + 11);
        end if;
        if not t_compressed then
          t_clen := t_len;
          t_blob := p_content;
        end if;
        if p_zipped_blob is null then
          dbms_lob.createtemporary(p_zipped_blob, true);
        end if;
        t_name := utl_i18n.string_to_raw(p_name, 'AL32UTF8');
        dbms_lob.append(p_zipped_blob,
                        utl_raw.concat(c_local_file_header -- Local file header signature
                                      ,
                                       hextoraw('1400') -- version 2.0
                                      ,
                                       case when
                                       t_name =
                                       utl_i18n.string_to_raw(p_name, 'US8PC437') then
                                       hextoraw('0000') -- no General purpose bits
                                       else hextoraw('0008') -- set Language encoding flag (EFS)
                                       end,
                                       case when t_compressed then
                                       hextoraw('0800') -- deflate
                                       else hextoraw('0000') -- stored
                                       end,
                                       little_endian(to_number(to_char(t_now,
                                                                       'ss')) / 2 +
                                                     to_number(to_char(t_now,
                                                                       'mi')) * 32 +
                                                     to_number(to_char(t_now,
                                                                       'hh24')) * 2048,
                                                     2) -- File last modification time
                                      ,
                                       little_endian(to_number(to_char(t_now,
                                                                       'dd')) +
                                                     to_number(to_char(t_now,
                                                                       'mm')) * 32 +
                                                     (to_number(to_char(t_now,
                                                                        'yyyy')) - 1980) * 512,
                                                     2) -- File last modification date
                                      ,
                                       t_crc32 -- CRC-32
                                      ,
                                       little_endian(t_clen) -- compressed size
                                      ,
                                       little_endian(t_len) -- uncompressed size
                                      ,
                                       little_endian(utl_raw.length(t_name), 2) -- File name length
                                      ,
                                       hextoraw('0000') -- Extra field length
                                      ,
                                       t_name -- File name
                                       ));
        if t_compressed then
          dbms_lob.copy(p_zipped_blob,
                        t_blob,
                        t_clen,
                        dbms_lob.getlength(p_zipped_blob) + 1,
                        11); -- compressed content
        elsif t_clen > 0 then
          dbms_lob.copy(p_zipped_blob,
                        t_blob,
                        t_clen,
                        dbms_lob.getlength(p_zipped_blob) + 1,
                        1); --  content
        end if;
        if dbms_lob.istemporary(t_blob) = 1 then
          dbms_lob.freetemporary(t_blob);
        end if;
      end;
    
      --
      procedure finish_zip(p_zipped_blob in out blob) is
        t_cnt             pls_integer := 0;
        t_offs            integer;
        t_offs_dir_header integer;
        t_offs_end_header integer;
        t_comment         raw(32767) := utl_raw.cast_to_raw('Implementation by Anton Scheffer');
      begin
        t_offs_dir_header := dbms_lob.getlength(p_zipped_blob);
        t_offs            := 1;
        while dbms_lob.substr(p_zipped_blob,
                              utl_raw.length(c_local_file_header),
                              t_offs) = c_local_file_header loop
          t_cnt := t_cnt + 1;
          dbms_lob.append(p_zipped_blob,
                          utl_raw.concat(hextoraw('504B0102') -- Central directory file header signature
                                        ,
                                         hextoraw('1400') -- version 2.0
                                        ,
                                         dbms_lob.substr(p_zipped_blob,
                                                         26,
                                                         t_offs + 4),
                                         hextoraw('0000') -- File comment length
                                        ,
                                         hextoraw('0000') -- Disk number where file starts
                                        ,
                                         hextoraw('0000') -- Internal file attributes =>
                                         --     0000 binary file
                                         --     0100 (ascii)text file
                                        ,
                                         case when dbms_lob.substr(p_zipped_blob,
                                                         1,
                                                         t_offs + 30 +
                                                         blob2num(p_zipped_blob,
                                                                  2,
                                                                  t_offs + 26) - 1) in
                                         (hextoraw('2F') -- /
                                        ,
                                          hextoraw('5C') -- \
                                          ) then hextoraw('10000000') -- a directory/folder
                                         else hextoraw('2000B681') -- a file
                                         end -- External file attributes
                                        ,
                                         little_endian(t_offs - 1) -- Relative offset of local file header
                                        ,
                                         dbms_lob.substr(p_zipped_blob,
                                                         blob2num(p_zipped_blob,
                                                                  2,
                                                                  t_offs + 26),
                                                         t_offs + 30) -- File name
                                         ));
          t_offs := t_offs + 30 + blob2num(p_zipped_blob, 4, t_offs + 18) -- compressed size
                    + blob2num(p_zipped_blob, 2, t_offs + 26) -- File name length
                    + blob2num(p_zipped_blob, 2, t_offs + 28); -- Extra field length
        end loop;
        t_offs_end_header := dbms_lob.getlength(p_zipped_blob);
        dbms_lob.append(p_zipped_blob,
                        utl_raw.concat(c_end_of_central_directory -- End of central directory signature
                                      ,
                                       hextoraw('0000') -- Number of this disk
                                      ,
                                       hextoraw('0000') -- Disk where central directory starts
                                      ,
                                       little_endian(t_cnt, 2) -- Number of central directory records on this disk
                                      ,
                                       little_endian(t_cnt, 2) -- Total number of central directory records
                                      ,
                                       little_endian(t_offs_end_header -
                                                     t_offs_dir_header) -- Size of central directory
                                      ,
                                       little_endian(t_offs_dir_header) -- Offset of start of central directory, relative to start of archive
                                      ,
                                       little_endian(nvl(utl_raw.length(t_comment),
                                                         0),
                                                     2) -- ZIP file comment length
                                      ,
                                       t_comment));
      end;
    
      --
      function alfan_col(p_col pls_integer) return varchar2 is
      begin
        return case when p_col > 702 then chr(64 + trunc((p_col - 27) / 676)) || chr(65 +
                                                                                     mod(trunc((p_col - 1) / 26) - 1,
                                                                                         26)) || chr(65 +
                                                                                                     mod(p_col - 1,
                                                                                                         26)) when p_col > 26 then chr(64 +
                                                                                                                                       trunc((p_col - 1) / 26)) || chr(65 +
                                                                                                                                                                       mod(p_col - 1,
                                                                                                                                                                           26)) else chr(64 +
                                                                                                                                                                                         p_col) end;
      end;
    
      --
      function col_alfan(p_col varchar2) return pls_integer is
      begin
        return ascii(substr(p_col, -1)) - 64 + nvl((ascii(substr(p_col, -2, 1)) - 64) * 26,
                                                   0) + nvl((ascii(substr(p_col,
                                                                          -3,
                                                                          1)) - 64) * 676,
                                                            0);
      end;
    
      --
      procedure clear_workbook is
        --t_row_ind pls_integer;
        t_clear_rows tp_rows;
      begin
        for s in 1 .. workbook.sheets.count() loop
          /*
          t_row_ind := workbook.sheets( s ).rows.first();
          while t_row_ind is not null
          loop
            workbook.sheets( s ).rows( t_row_ind ).delete();
            t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
          end loop;
          */
          workbook.sheets(s).rows := t_clear_rows;
          --lc_rows := t_clear_rows;
          /*
          lc_rows := workbook.sheets( s ).rows;
          t_row_ind := lc_rows.first();
          loop
            exit when t_row_ind is null;
            workbook.sheets( s ).rows( t_row_ind ).delete();
            lc_rows( t_row_ind ).delete();
            t_row_ind := lc_rows.next( t_row_ind );
          end loop;*/
          --lc_rows.delete();
          workbook.sheets(s).rows.delete();
          workbook.sheets(s).widths.delete();
          workbook.sheets(s).autofilters.delete();
          workbook.sheets(s).hyperlinks.delete();
          workbook.sheets(s).col_fmts.delete();
          workbook.sheets(s).row_fmts.delete();
          workbook.sheets(s).comments.delete();
          workbook.sheets(s).mergecells.delete();
          workbook.sheets(s).validations.delete();
        end loop;
        workbook.strings.delete();
        workbook.str_ind.delete();
        workbook.fonts.delete();
        workbook.fills.delete();
        workbook.borders.delete();
        workbook.numfmts.delete();
        workbook.cellxfs.delete();
        workbook.defined_names.delete();
        workbook             := null;
        g_query2sheet_footer := null;
        g_query2sheet_rows   := null;
      end;
    
      --
      procedure new_sheet(p_sheetname varchar2 := null) is
        t_nr  pls_integer := workbook.sheets.count() + 1;
        t_ind pls_integer;
      begin
        workbook.sheets(t_nr).name := nvl(dbms_xmlgen.convert(translate(p_sheetname,
                                                                        'a/\[]*:?',
                                                                        'a')),
                                          'Sheet' || t_nr);
        if workbook.strings.count() = 0 then
          workbook.str_cnt := 0;
        end if;
        if workbook.fonts.count() = 0 then
          t_ind := get_font('Calibri');
        end if;
        if workbook.fills.count() = 0 then
          t_ind := get_fill('none');
          t_ind := get_fill('gray125');
        end if;
        if workbook.borders.count() = 0 then
          t_ind := get_border('', '', '', '');
        end if;
      end;
    
      --
      procedure set_col_width(p_sheet  pls_integer,
                              p_col    pls_integer,
                              p_format varchar2) is
        t_width  number;
        t_nr_chr pls_integer;
      begin
        if p_format is null then
          return;
        end if;
        if instr(p_format, ';') > 0 then
          t_nr_chr := length(translate(substr(p_format,
                                              1,
                                              instr(p_format, ';') - 1),
                                       'a\"',
                                       'a'));
        else
          t_nr_chr := length(translate(p_format, 'a\"', 'a'));
        end if;
        t_width := trunc((t_nr_chr * 7 + 5) / 7 * 256) / 256; -- assume default 11 point Calibri
        if workbook.sheets(p_sheet).widths.exists(p_col) then
          workbook.sheets(p_sheet).widths(p_col) := greatest(workbook.sheets(p_sheet)
                                                             .widths(p_col),
                                                             t_width);
        else
          workbook.sheets(p_sheet).widths(p_col) := greatest(t_width, 8.43);
        end if;
      end;
    
      --
      function orafmt2excel(p_format varchar2 := null) return varchar2 is
        t_format varchar2(1000) := substr(p_format, 1, 1000);
      begin
        t_format := replace(replace(t_format, 'hh24', 'hh'), 'hh12', 'hh');
        t_format := replace(t_format, 'mi', 'mm');
        t_format := replace(replace(replace(t_format, 'AM', '~~'), 'PM', '~~'),
                            '~~',
                            'AM/PM');
        t_format := replace(replace(replace(t_format, 'am', '~~'), 'pm', '~~'),
                            '~~',
                            'AM/PM');
        t_format := replace(replace(t_format, 'day', 'DAY'), 'DAY', 'dddd');
        t_format := replace(replace(t_format, 'dy', 'DY'), 'DAY', 'ddd');
        t_format := replace(replace(t_format, 'RR', 'RR'), 'RR', 'YY');
        t_format := replace(replace(t_format, 'month', 'MONTH'),
                            'MONTH',
                            'mmmm');
        t_format := replace(replace(t_format, 'mon', 'MON'), 'MON', 'mmm');
        return t_format;
      end;
    
      --
      function get_numfmt(p_format varchar2 := null) return pls_integer is
        t_cnt      pls_integer;
        t_numfmtid pls_integer;
      begin
        if p_format is null then
          return 0;
        end if;
        t_cnt := workbook.numfmts.count();
        for i in 1 .. t_cnt loop
          if workbook.numfmts(i).formatcode = p_format then
            t_numfmtid := workbook.numfmts(i).numfmtid;
            exit;
          end if;
        end loop;
        if t_numfmtid is null then
          t_numfmtid := case
                          when t_cnt = 0 then
                           164
                          else
                           workbook.numfmts(t_cnt).numfmtid + 1
                        end;
          t_cnt := t_cnt + 1;
          workbook.numfmts(t_cnt).numfmtid := t_numfmtid;
          workbook.numfmts(t_cnt).formatcode := p_format;
          workbook.numfmtindexes(t_numfmtid) := t_cnt;
        end if;
        return t_numfmtid;
      end;
    
      --
      function get_font(p_name      varchar2,
                        p_family    pls_integer := 2,
                        p_fontsize  number := 11,
                        p_theme     pls_integer := 1,
                        p_underline boolean := false,
                        p_italic    boolean := false,
                        p_bold      boolean := false,
                        p_rgb       varchar2 := null -- this is a hex ALPHA Red Green Blue value
                        ) return pls_integer is
        t_ind pls_integer;
      begin
        if workbook.fonts.count() > 0 then
          for f in 0 .. workbook.fonts.count() - 1 loop
            if (workbook.fonts(f)
               .name = p_name and workbook.fonts(f).family = p_family and workbook.fonts(f)
               .fontsize = p_fontsize and workbook.fonts(f).theme = p_theme and workbook.fonts(f)
               .underline = p_underline and workbook.fonts(f).italic = p_italic and workbook.fonts(f)
               .bold = p_bold and
                (workbook.fonts(f)
                .rgb = p_rgb or (workbook.fonts(f).rgb is null and p_rgb is null))) then
              return f;
            end if;
          end loop;
        end if;
        t_ind := workbook.fonts.count();
        workbook.fonts(t_ind).name := p_name;
        workbook.fonts(t_ind).family := p_family;
        workbook.fonts(t_ind).fontsize := p_fontsize;
        workbook.fonts(t_ind).theme := p_theme;
        workbook.fonts(t_ind).underline := p_underline;
        workbook.fonts(t_ind).italic := p_italic;
        workbook.fonts(t_ind).bold := p_bold;
        workbook.fonts(t_ind).rgb := p_rgb;
        return t_ind;
      end;
    
      --
      function get_fill(p_patterntype varchar2, p_fgrgb varchar2 := null)
        return pls_integer is
        t_ind pls_integer;
      begin
        if workbook.fills.count() > 0 then
          for f in 0 .. workbook.fills.count() - 1 loop
            if (workbook.fills(f)
               .patterntype = p_patterntype and
                nvl(workbook.fills(f).fgrgb, 'x') = nvl(upper(p_fgrgb), 'x')) then
              return f;
            end if;
          end loop;
        end if;
        t_ind := workbook.fills.count();
        workbook.fills(t_ind).patterntype := p_patterntype;
        workbook.fills(t_ind).fgrgb := upper(p_fgrgb);
        return t_ind;
      end;
    
      --
      function get_border(p_top    varchar2 := 'thin',
                          p_bottom varchar2 := 'thin',
                          p_left   varchar2 := 'thin',
                          p_right  varchar2 := 'thin') return pls_integer is
        t_ind pls_integer;
      begin
        if workbook.borders.count() > 0 then
          for b in 0 .. workbook.borders.count() - 1 loop
            if (nvl(workbook.borders(b).top, 'x') = nvl(p_top, 'x') and
               nvl(workbook.borders(b).bottom, 'x') = nvl(p_bottom, 'x') and
               nvl(workbook.borders(b).left, 'x') = nvl(p_left, 'x') and
               nvl(workbook.borders(b).right, 'x') = nvl(p_right, 'x')) then
              return b;
            end if;
          end loop;
        end if;
        t_ind := workbook.borders.count();
        workbook.borders(t_ind).top := p_top;
        workbook.borders(t_ind).bottom := p_bottom;
        workbook.borders(t_ind).left := p_left;
        workbook.borders(t_ind).right := p_right;
        return t_ind;
      end;
    
      --
      function get_alignment(p_vertical   varchar2 := null,
                             p_horizontal varchar2 := null,
                             p_wraptext   boolean := null) return tp_alignment is
        t_rv tp_alignment;
      begin
        t_rv.vertical   := p_vertical;
        t_rv.horizontal := p_horizontal;
        t_rv.wraptext   := p_wraptext;
        return t_rv;
      end;
    
      --
      function get_xfid(p_sheet     pls_integer,
                        p_col       pls_integer,
                        p_row       pls_integer,
                        p_numfmtid  pls_integer := null,
                        p_fontid    pls_integer := null,
                        p_fillid    pls_integer := null,
                        p_borderid  pls_integer := null,
                        p_alignment tp_alignment := null) return varchar2 is
        t_cnt    pls_integer;
        t_xfid   pls_integer;
        t_xf     tp_xf_fmt;
        t_col_xf tp_xf_fmt;
        t_row_xf tp_xf_fmt;
      begin
        if workbook.sheets(p_sheet).col_fmts.exists(p_col) then
          t_col_xf := workbook.sheets(p_sheet).col_fmts(p_col);
        end if;
        if workbook.sheets(p_sheet).row_fmts.exists(p_row) then
          t_row_xf := workbook.sheets(p_sheet).row_fmts(p_row);
        end if;
        t_xf.numfmtid  := coalesce(p_numfmtid,
                                   t_col_xf.numfmtid,
                                   t_row_xf.numfmtid,
                                   0);
        t_xf.fontid    := coalesce(p_fontid,
                                   t_col_xf.fontid,
                                   t_row_xf.fontid,
                                   0);
        t_xf.fillid    := coalesce(p_fillid,
                                   t_col_xf.fillid,
                                   t_row_xf.fillid,
                                   0);
        t_xf.borderid  := coalesce(p_borderid,
                                   t_col_xf.borderid,
                                   t_row_xf.borderid,
                                   0);
        t_xf.alignment := coalesce(p_alignment,
                                   t_col_xf.alignment,
                                   t_row_xf.alignment);
        if (t_xf.numfmtid + t_xf.fontid + t_xf.fillid + t_xf.borderid = 0 and
           t_xf.alignment.vertical is null and
           t_xf.alignment.horizontal is null and
           not nvl(t_xf.alignment.wraptext, false)) then
          return '';
        end if;
        if t_xf.numfmtid > 0 then
          set_col_width(p_sheet,
                        p_col,
                        workbook.numfmts(workbook.numfmtindexes(t_xf.numfmtid))
                        .formatcode);
        end if;
        t_cnt := workbook.cellxfs.count();
        for i in 1 .. t_cnt loop
          if (workbook.cellxfs(i)
             .numfmtid = t_xf.numfmtid and workbook.cellxfs(i)
             .fontid = t_xf.fontid and workbook.cellxfs(i).fillid = t_xf.fillid and workbook.cellxfs(i)
             .borderid = t_xf.borderid and
              nvl(workbook.cellxfs(i).alignment.vertical, 'x') =
              nvl(t_xf.alignment.vertical, 'x') and
              nvl(workbook.cellxfs(i).alignment.horizontal, 'x') =
              nvl(t_xf.alignment.horizontal, 'x') and
              nvl(workbook.cellxfs(i).alignment.wraptext, false) =
              nvl(t_xf.alignment.wraptext, false)) then
            t_xfid := i;
            exit;
          end if;
        end loop;
        if t_xfid is null then
          t_cnt := t_cnt + 1;
          t_xfid := t_cnt;
          workbook.cellxfs(t_cnt) := t_xf;
        end if;
        return 's="' || t_xfid || '"';
      end;
    
      --
      procedure cell(p_col       pls_integer,
                     p_row       pls_integer,
                     p_value     number,
                     p_numfmtid  pls_integer := null,
                     p_fontid    pls_integer := null,
                     p_fillid    pls_integer := null,
                     p_borderid  pls_integer := null,
                     p_alignment tp_alignment := null,
                     p_sheet     pls_integer := null) is
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).rows(p_row)(p_col).value := p_value;
        workbook.sheets(t_sheet).rows(p_row)(p_col).style := null;
        workbook.sheets(t_sheet).rows(p_row)(p_col).style := get_xfid(t_sheet,
                                                                      p_col,
                                                                      p_row,
                                                                      p_numfmtid,
                                                                      p_fontid,
                                                                      p_fillid,
                                                                      p_borderid,
                                                                      p_alignment);
      end;
    
      --
      function add_string(p_string varchar2) return pls_integer is
        t_cnt pls_integer;
      begin
        if workbook.strings.exists(p_string) then
          t_cnt := workbook.strings(p_string);
        else
          t_cnt := workbook.strings.count();
          workbook.str_ind(t_cnt) := p_string;
          workbook.strings(nvl(p_string, '')) := t_cnt;
        end if;
        workbook.str_cnt := workbook.str_cnt + 1;
        return t_cnt;
      end;
    
      --
      procedure cell(p_col       pls_integer,
                     p_row       pls_integer,
                     p_value     varchar2,
                     p_numfmtid  pls_integer := null,
                     p_fontid    pls_integer := null,
                     p_fillid    pls_integer := null,
                     p_borderid  pls_integer := null,
                     p_alignment tp_alignment := null,
                     p_sheet     pls_integer := null) is
        t_sheet     pls_integer := nvl(p_sheet, workbook.sheets.count());
        t_alignment tp_alignment := p_alignment;
      begin
        workbook.sheets(t_sheet).rows(p_row)(p_col).value := add_string(p_value);
        if t_alignment.wraptext is null and instr(p_value, chr(13)) > 0 then
          t_alignment.wraptext := true;
        end if;
        workbook.sheets(t_sheet).rows(p_row)(p_col).style := 't="s" ' ||
                                                             get_xfid(t_sheet,
                                                                      p_col,
                                                                      p_row,
                                                                      p_numfmtid,
                                                                      p_fontid,
                                                                      p_fillid,
                                                                      p_borderid,
                                                                      t_alignment);
      end;
    
      --
      procedure cell(p_col       pls_integer,
                     p_row       pls_integer,
                     p_value     date,
                     p_numfmtid  pls_integer := null,
                     p_fontid    pls_integer := null,
                     p_fillid    pls_integer := null,
                     p_borderid  pls_integer := null,
                     p_alignment tp_alignment := null,
                     p_sheet     pls_integer := null) is
        t_numfmtid pls_integer := p_numfmtid;
        t_sheet    pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        --这里必须要改为1900系统!否则不同的Excel之间的复制和黏贴会出问题!相差4年。
        --因为Windows的Excel默认是1900系统。
        --workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value - to_date('01-01-1904','DD-MM-YYYY');
        workbook.sheets(t_sheet).rows(p_row)(p_col).value := p_value -
                                                             to_date('01-03-1900',
                                                                     'DD-MM-YYYY') + 61;
        if t_numfmtid is null and
           not (workbook.sheets(t_sheet).col_fmts.exists(p_col) and workbook.sheets(t_sheet).col_fmts(p_col)
            .numfmtid is not null) and
           not (workbook.sheets(t_sheet).row_fmts.exists(p_row) and workbook.sheets(t_sheet).row_fmts(p_row)
            .numfmtid is not null) then
          t_numfmtid := get_numfmt('yyyy-mm-dd'); --dd/mm/yyyy 2015.7.1修改
        end if;
        workbook.sheets(t_sheet).rows(p_row)(p_col).style := get_xfid(t_sheet,
                                                                      p_col,
                                                                      p_row,
                                                                      t_numfmtid,
                                                                      p_fontid,
                                                                      p_fillid,
                                                                      p_borderid,
                                                                      p_alignment);
      end;
    
      --
      procedure hyperlink(p_col   pls_integer,
                          p_row   pls_integer,
                          p_url   varchar2,
                          p_value varchar2 := null,
                          p_sheet pls_integer := null) is
        t_ind   pls_integer;
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).rows(p_row)(p_col).value := add_string(nvl(p_value,
                                                                            p_url));
        workbook.sheets(t_sheet).rows(p_row)(p_col).style := 't="s" ' ||
                                                             get_xfid(t_sheet,
                                                                      p_col,
                                                                      p_row,
                                                                      '',
                                                                      get_font('Calibri',
                                                                               p_theme     => 10,
                                                                               p_underline => true));
        t_ind := workbook.sheets(t_sheet).hyperlinks.count() + 1;
        workbook.sheets(t_sheet).hyperlinks(t_ind).cell := alfan_col(p_col) ||
                                                           p_row;
        workbook.sheets(t_sheet).hyperlinks(t_ind).url := p_url;
      end;
    
      --
      procedure comment(p_col    pls_integer,
                        p_row    pls_integer,
                        p_text   varchar2,
                        p_author varchar2 := null,
                        p_width  pls_integer := 150,
                        p_height pls_integer := 100,
                        p_sheet  pls_integer := null) is
        t_ind   pls_integer;
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        t_ind := workbook.sheets(t_sheet).comments.count() + 1;
        workbook.sheets(t_sheet).comments(t_ind).row := p_row;
        workbook.sheets(t_sheet).comments(t_ind).column := p_col;
        workbook.sheets(t_sheet).comments(t_ind).text := dbms_xmlgen.convert(p_text);
        workbook.sheets(t_sheet).comments(t_ind).author := dbms_xmlgen.convert(p_author);
        workbook.sheets(t_sheet).comments(t_ind).width := p_width;
        workbook.sheets(t_sheet).comments(t_ind).height := p_height;
      end;
    
      --
      procedure mergecells(p_tl_col pls_integer -- top left
                          ,
                           p_tl_row pls_integer,
                           p_br_col pls_integer -- bottom right
                          ,
                           p_br_row pls_integer,
                           p_sheet  pls_integer := null) is
        t_ind   pls_integer;
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        t_ind := workbook.sheets(t_sheet).mergecells.count() + 1;
        workbook.sheets(t_sheet).mergecells(t_ind) := alfan_col(p_tl_col) ||
                                                      p_tl_row || ':' ||
                                                      alfan_col(p_br_col) ||
                                                      p_br_row;
      end;
    
      --
      procedure add_validation(p_type        varchar2,
                               p_sqref       varchar2,
                               p_style       varchar2 := 'stop' -- stop, warning, information
                              ,
                               p_formula1    varchar2 := null,
                               p_formula2    varchar2 := null,
                               p_title       varchar2 := null,
                               p_prompt      varchar := null,
                               p_show_error  boolean := false,
                               p_error_title varchar2 := null,
                               p_error_txt   varchar2 := null,
                               p_sheet       pls_integer := null) is
        t_ind   pls_integer;
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        t_ind := workbook.sheets(t_sheet).validations.count() + 1;
        workbook.sheets(t_sheet).validations(t_ind).type := p_type;
        workbook.sheets(t_sheet).validations(t_ind).errorstyle := p_style;
        workbook.sheets(t_sheet).validations(t_ind).sqref := p_sqref;
        workbook.sheets(t_sheet).validations(t_ind).formula1 := p_formula1;
        workbook.sheets(t_sheet).validations(t_ind).error_title := p_error_title;
        workbook.sheets(t_sheet).validations(t_ind).error_txt := p_error_txt;
        workbook.sheets(t_sheet).validations(t_ind).title := p_title;
        workbook.sheets(t_sheet).validations(t_ind).prompt := p_prompt;
        workbook.sheets(t_sheet).validations(t_ind).showerrormessage := p_show_error;
      end;
    
      --
      procedure list_validation(p_sqref_col   pls_integer,
                                p_sqref_row   pls_integer,
                                p_tl_col      pls_integer -- top left
                               ,
                                p_tl_row      pls_integer,
                                p_br_col      pls_integer -- bottom right
                               ,
                                p_br_row      pls_integer,
                                p_style       varchar2 := 'stop' -- stop, warning, information
                               ,
                                p_title       varchar2 := null,
                                p_prompt      varchar := null,
                                p_show_error  boolean := false,
                                p_error_title varchar2 := null,
                                p_error_txt   varchar2 := null,
                                p_sheet       pls_integer := null) is
      begin
        add_validation('list',
                       alfan_col(p_sqref_col) || p_sqref_row,
                       p_style => lower(p_style),
                       p_formula1 => '$' || alfan_col(p_tl_col) || '$' ||
                                     p_tl_row || ':$' || alfan_col(p_br_col) || '$' ||
                                     p_br_row,
                       p_title => p_title,
                       p_prompt => p_prompt,
                       p_show_error => p_show_error,
                       p_error_title => p_error_title,
                       p_error_txt => p_error_txt,
                       p_sheet => p_sheet);
      end;
    
      --
      procedure list_validation(p_sqref_col    pls_integer,
                                p_sqref_row    pls_integer,
                                p_defined_name varchar2,
                                p_style        varchar2 := 'stop' -- stop, warning, information
                               ,
                                p_title        varchar2 := null,
                                p_prompt       varchar := null,
                                p_show_error   boolean := false,
                                p_error_title  varchar2 := null,
                                p_error_txt    varchar2 := null,
                                p_sheet        pls_integer := null) is
      begin
        add_validation('list',
                       alfan_col(p_sqref_col) || p_sqref_row,
                       p_style => lower(p_style),
                       p_formula1 => p_defined_name,
                       p_title => p_title,
                       p_prompt => p_prompt,
                       p_show_error => p_show_error,
                       p_error_title => p_error_title,
                       p_error_txt => p_error_txt,
                       p_sheet => p_sheet);
      end;
    
      --
      procedure defined_name(p_tl_col     pls_integer -- top left
                            ,
                             p_tl_row     pls_integer,
                             p_br_col     pls_integer -- bottom right
                            ,
                             p_br_row     pls_integer,
                             p_name       varchar2,
                             p_sheet      pls_integer := null,
                             p_localsheet pls_integer := null) is
        t_ind   pls_integer;
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        t_ind := workbook.defined_names.count() + 1;
        workbook.defined_names(t_ind).name := p_name;
        workbook.defined_names(t_ind).ref := 'Sheet' || t_sheet || '!$' ||
                                             alfan_col(p_tl_col) || '$' ||
                                             p_tl_row || ':$' ||
                                             alfan_col(p_br_col) || '$' ||
                                             p_br_row;
        workbook.defined_names(t_ind).sheet := p_localsheet;
      end;
    
      --
      procedure set_column_width(p_col   pls_integer,
                                 p_width number,
                                 p_sheet pls_integer := null) is
      begin
        workbook.sheets(nvl(p_sheet, workbook.sheets.count())).widths(p_col) := p_width;
      end;
    
      --
      procedure set_column(p_col       pls_integer,
                           p_numfmtid  pls_integer := null,
                           p_fontid    pls_integer := null,
                           p_fillid    pls_integer := null,
                           p_borderid  pls_integer := null,
                           p_alignment tp_alignment := null,
                           p_sheet     pls_integer := null) is
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).col_fmts(p_col).numfmtid := p_numfmtid;
        workbook.sheets(t_sheet).col_fmts(p_col).fontid := p_fontid;
        workbook.sheets(t_sheet).col_fmts(p_col).fillid := p_fillid;
        workbook.sheets(t_sheet).col_fmts(p_col).borderid := p_borderid;
        workbook.sheets(t_sheet).col_fmts(p_col).alignment := p_alignment;
      end;
    
      --
      procedure set_row(p_row       pls_integer,
                        p_numfmtid  pls_integer := null,
                        p_fontid    pls_integer := null,
                        p_fillid    pls_integer := null,
                        p_borderid  pls_integer := null,
                        p_alignment tp_alignment := null,
                        p_sheet     pls_integer := null) is
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).row_fmts(p_row).numfmtid := p_numfmtid;
        workbook.sheets(t_sheet).row_fmts(p_row).fontid := p_fontid;
        workbook.sheets(t_sheet).row_fmts(p_row).fillid := p_fillid;
        workbook.sheets(t_sheet).row_fmts(p_row).borderid := p_borderid;
        workbook.sheets(t_sheet).row_fmts(p_row).alignment := p_alignment;
      end;
    
      --
      procedure freeze_rows(p_nr_rows pls_integer := 1,
                            p_sheet   pls_integer := null) is
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).freeze_cols := null;
        workbook.sheets(t_sheet).freeze_rows := p_nr_rows;
      end;
    
      --
      procedure freeze_cols(p_nr_cols pls_integer := 1,
                            p_sheet   pls_integer := null) is
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).freeze_rows := null;
        workbook.sheets(t_sheet).freeze_cols := p_nr_cols;
      end;
    
      --
      procedure freeze_pane(p_col   pls_integer,
                            p_row   pls_integer,
                            p_sheet pls_integer := null) is
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        workbook.sheets(t_sheet).freeze_rows := p_row;
        workbook.sheets(t_sheet).freeze_cols := p_col;
      end;
    
      --
      procedure set_autofilter(p_column_start pls_integer := null,
                               p_column_end   pls_integer := null,
                               p_row_start    pls_integer := null,
                               p_row_end      pls_integer := null,
                               p_sheet        pls_integer := null) is
        t_ind   pls_integer;
        t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
      begin
        t_ind := 1;
        workbook.sheets(t_sheet).autofilters(t_ind).column_start := p_column_start;
        workbook.sheets(t_sheet).autofilters(t_ind).column_end := p_column_end;
        workbook.sheets(t_sheet).autofilters(t_ind).row_start := p_row_start;
        workbook.sheets(t_sheet).autofilters(t_ind).row_end := p_row_end;
        defined_name(p_column_start,
                     p_row_start,
                     p_column_end,
                     p_row_end,
                     '_xlnm._FilterDatabase',
                     t_sheet,
                     t_sheet - 1);
      end;
    
      --
      /*
        procedure add1xml
          ( p_excel in out nocopy blob
          , p_filename varchar2
          , p_xml clob
          )
        is
          t_tmp blob;
          c_step constant number := 24396;
        begin
          dbms_lob.createtemporary( t_tmp, true );
          for i in 0 .. trunc( length( p_xml ) / c_step )
          loop
            dbms_lob.append( t_tmp, utl_i18n.string_to_raw( substr( p_xml, i * c_step + 1, c_step ), 'AL32UTF8' ) );
          end loop;
          add1file( p_excel, p_filename, t_tmp );
          dbms_lob.freetemporary( t_tmp );
        end;
      */
      --
      procedure add1xml(p_excel    in out nocopy blob,
                        p_filename varchar2,
                        p_xml      clob) is
        t_tmp        blob;
        dest_offset  integer := 1;
        src_offset   integer := 1;
        lang_context integer;
        warning      integer;
      begin
        lang_context := dbms_lob.default_lang_ctx;
        dbms_lob.createtemporary(t_tmp, true);
        dbms_lob.converttoblob(t_tmp,
                               p_xml,
                               dbms_lob.lobmaxsize,
                               dest_offset,
                               src_offset,
                               nls_charset_id('AL32UTF8'),
                               lang_context,
                               warning);
        add1file(p_excel, p_filename, t_tmp);
        dbms_lob.freetemporary(t_tmp);
      end;
    
      --
      function finish return blob is
        t_excel blob;
        t_xxx   clob;
        t_tmp   varchar2(32767 char);
        --t_tmp clob;
        t_str     varchar2(32767 char);
        t_c       number;
        t_h       number;
        t_w       number;
        t_cw      number;
        t_cell    varchar2(1000 char);
        t_row_ind pls_integer;
        t_col_min pls_integer;
        t_col_max pls_integer;
        t_col_ind pls_integer;
        t_len     pls_integer;
        ts        timestamp := systimestamp;
        ---
        l_debug_time date;
      begin
        if g_debug_mode then
          debuglog('finish(+)');
          l_debug_time := sysdate;
        end if;
        dbms_lob.createtemporary(t_excel, true);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
    <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
    <Default Extension="xml" ContentType="application/xml"/>
    <Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>
    <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
        for s in 1 .. workbook.sheets.count() loop
          t_xxx := t_xxx || '
    <Override PartName="/xl/worksheets/sheet' || s ||
                   '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
        end loop;
        t_xxx := t_xxx || '
    <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
    <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
    <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
    <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
    <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
        for s in 1 .. workbook.sheets.count() loop
          if workbook.sheets(s).comments.count() > 0 then
            t_xxx := t_xxx || '
    <Override PartName="/xl/comments' || s ||
                     '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"/>';
          end if;
        end loop;
        t_xxx := t_xxx || '
    </Types>';
        add1xml(t_excel, '[Content_Types].xml', t_xxx);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <dc:creator>' || sys_context('userenv', 'os_user') ||
                 '</dc:creator>
    <cp:lastModifiedBy>' || sys_context('userenv', 'os_user') ||
                 '</cp:lastModifiedBy>
    <dcterms:created xsi:type="dcterms:W3CDTF">' ||
                 to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') ||
                 '</dcterms:created>
    <dcterms:modified xsi:type="dcterms:W3CDTF">' ||
                 to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') ||
                 '</dcterms:modified>
    </cp:coreProperties>';
        add1xml(t_excel, 'docProps/core.xml', t_xxx);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
    <Application>Microsoft Excel</Application>
    <DocSecurity>0</DocSecurity>
    <ScaleCrop>false</ScaleCrop>
    <HeadingPairs>
    <vt:vector size="2" baseType="variant">
    <vt:variant>
    <vt:lpstr>Worksheets</vt:lpstr>
    </vt:variant>
    <vt:variant>
    <vt:i4>' || workbook.sheets.count() || '</vt:i4>
    </vt:variant>
    </vt:vector>
    </HeadingPairs>
    <TitlesOfParts>
    <vt:vector size="' || workbook.sheets.count() ||
                 '" baseType="lpstr">';
        for s in 1 .. workbook.sheets.count() loop
          t_xxx := t_xxx || '
    <vt:lpstr>' || workbook.sheets(s).name || '</vt:lpstr>';
        end loop;
        t_xxx := t_xxx || '</vt:vector>
    </TitlesOfParts>
    <LinksUpToDate>false</LinksUpToDate>
    <SharedDoc>false</SharedDoc>
    <HyperlinksChanged>false</HyperlinksChanged>
    <AppVersion>14.0300</AppVersion>
    </Properties>';
        add1xml(t_excel, 'docProps/app.xml', t_xxx);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
    </Relationships>';
        add1xml(t_excel, '_rels/.rels', t_xxx);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">';
        if workbook.numfmts.count() > 0 then
          t_xxx := t_xxx || '<numFmts count="' || workbook.numfmts.count() || '">';
          for n in 1 .. workbook.numfmts.count() loop
            t_xxx := t_xxx || '<numFmt numFmtId="' || workbook.numfmts(n)
                    .numfmtid || '" formatCode="' || workbook.numfmts(n)
                    .formatcode || '"/>';
          end loop;
          t_xxx := t_xxx || '</numFmts>';
        end if;
        t_xxx := t_xxx || '<fonts count="' || workbook.fonts.count() ||
                 '" x14ac:knownFonts="1">';
        for f in 0 .. workbook.fonts.count() - 1 loop
          t_xxx := t_xxx || '<font>' || case
                     when workbook.fonts(f).bold then
                      '<b/>'
                   end || case
                     when workbook.fonts(f).italic then
                      '<i/>'
                   end || case
                     when workbook.fonts(f).underline then
                      '<u/>'
                   end || '<sz val="' ||
                   to_char(workbook.fonts(f).fontsize,
                           'TM9',
                           'NLS_NUMERIC_CHARACTERS=.,') || '"/>
    <color ' || case
                     when workbook.fonts(f).rgb is not null then
                      'rgb="' || workbook.fonts(f).rgb
                     else
                      'theme="' || workbook.fonts(f).theme
                   end || '"/>
    <name val="' || workbook.fonts(f).name || '"/>
    <family val="' || workbook.fonts(f).family || '"/>
    <scheme val="none"/>
    </font>';
        end loop;
        t_xxx := t_xxx || '</fonts>
    <fills count="' || workbook.fills.count() || '">';
        for f in 0 .. workbook.fills.count() - 1 loop
          t_xxx := t_xxx || '<fill><patternFill patternType="' || workbook.fills(f)
                  .patterntype || '">' || case
                     when workbook.fills(f).fgrgb is not null then
                      '<fgColor rgb="' || workbook.fills(f).fgrgb || '"/>'
                   end || '</patternFill></fill>';
        end loop;
        t_xxx := t_xxx || '</fills>
    <borders count="' || workbook.borders.count() || '">';
        for b in 0 .. workbook.borders.count() - 1 loop
          t_xxx := t_xxx || '<border>' || case
                     when workbook.borders(b).left is null then
                      '<left/>'
                     else
                      '<left style="' || workbook.borders(b).left || '"/>'
                   end || case
                     when workbook.borders(b).right is null then
                      '<right/>'
                     else
                      '<right style="' || workbook.borders(b).right || '"/>'
                   end || case
                     when workbook.borders(b).top is null then
                      '<top/>'
                     else
                      '<top style="' || workbook.borders(b).top || '"/>'
                   end || case
                     when workbook.borders(b).bottom is null then
                      '<bottom/>'
                     else
                      '<bottom style="' || workbook.borders(b).bottom || '"/>'
                   end || '</border>';
        end loop;
        t_xxx := t_xxx || '</borders>
    <cellStyleXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
    </cellStyleXfs>
    <cellXfs count="' || (workbook.cellxfs.count() + 1) || '">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>';
        for x in 1 .. workbook.cellxfs.count() loop
          t_xxx := t_xxx || '<xf numFmtId="' || workbook.cellxfs(x).numfmtid ||
                   '" fontId="' || workbook.cellxfs(x).fontid || '" fillId="' || workbook.cellxfs(x)
                  .fillid || '" borderId="' || workbook.cellxfs(x).borderid || '">';
          if (workbook.cellxfs(x).alignment.horizontal is not null or workbook.cellxfs(x)
             .alignment.vertical is not null or workbook.cellxfs(x)
             .alignment.wraptext) then
            t_xxx := t_xxx || '<alignment' || case
                       when workbook.cellxfs(x).alignment.horizontal is not null then
                        ' horizontal="' || workbook.cellxfs(x).alignment.horizontal || '"'
                     end || case
                       when workbook.cellxfs(x).alignment.vertical is not null then
                        ' vertical="' || workbook.cellxfs(x).alignment.vertical || '"'
                     end || case
                       when workbook.cellxfs(x).alignment.wraptext then
                        ' wrapText="true"'
                     end || '/>';
          end if;
          t_xxx := t_xxx || '</xf>';
        end loop;
        t_xxx := t_xxx || '</cellXfs>
    <cellStyles count="1">
    <cellStyle name="Normal" xfId="0" builtinId="0"/>
    </cellStyles>
    <dxfs count="0"/>
    <tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16"/>
    <extLst>
    <ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
    <x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>
    </ext>
    </extLst>
    </styleSheet>';
        add1xml(t_excel, 'xl/styles.xml', t_xxx);
        --<workbookPr date1904="true" defaultThemeVersion="124226"/>
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9302"/>
    <workbookPr date1904="false" defaultThemeVersion="124226"/>
    <bookViews>
    <workbookView xWindow="120" yWindow="45" windowWidth="19155" windowHeight="4935"/>
    </bookViews>
    <sheets>';
        for s in 1 .. workbook.sheets.count() loop
          t_xxx := t_xxx || '
    <sheet name="' || workbook.sheets(s).name || '" sheetId="' || s ||
                   '" r:id="rId' || (9 + s) || '"/>';
        end loop;
        t_xxx := t_xxx || '</sheets>';
        if workbook.defined_names.count() > 0 then
          t_xxx := t_xxx || '<definedNames>';
          for s in 1 .. workbook.defined_names.count() loop
            t_xxx := t_xxx || '
    <definedName name="' || workbook.defined_names(s).name || '"' || case
                       when workbook.defined_names(s).sheet is not null then
                        ' localSheetId="' || to_char(workbook.defined_names(s).sheet) || '"'
                     end || '>' || workbook.defined_names(s).ref ||
                     '</definedName>';
          end loop;
          t_xxx := t_xxx || '</definedNames>';
        end if;
        t_xxx := t_xxx || '<calcPr calcId="144525"/></workbook>';
        add1xml(t_excel, 'xl/workbook.xml', t_xxx);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme">
    <a:themeElements>
    <a:clrScheme name="Office">
    <a:dk1>
    <a:sysClr val="windowText" lastClr="000000"/>
    </a:dk1>
    <a:lt1>
    <a:sysClr val="window" lastClr="FFFFFF"/>
    </a:lt1>
    <a:dk2>
    <a:srgbClr val="1F497D"/>
    </a:dk2>
    <a:lt2>
    <a:srgbClr val="EEECE1"/>
    </a:lt2>
    <a:accent1>
    <a:srgbClr val="4F81BD"/>
    </a:accent1>
    <a:accent2>
    <a:srgbClr val="C0504D"/>
    </a:accent2>
    <a:accent3>
    <a:srgbClr val="9BBB59"/>
    </a:accent3>
    <a:accent4>
    <a:srgbClr val="8064A2"/>
    </a:accent4>
    <a:accent5>
    <a:srgbClr val="4BACC6"/>
    </a:accent5>
    <a:accent6>
    <a:srgbClr val="F79646"/>
    </a:accent6>
    <a:hlink>
    <a:srgbClr val="0000FF"/>
    </a:hlink>
    <a:folHlink>
    <a:srgbClr val="800080"/>
    </a:folHlink>
    </a:clrScheme>
    <a:fontScheme name="Office">
    <a:majorFont>
    <a:latin typeface="Cambria"/>
    <a:ea typeface=""/>
    <a:cs typeface=""/>
    <a:font script="Jpan" typeface="MS P????"/>
    <a:font script="Hang" typeface="?? ??"/>
    <a:font script="Hans" typeface="??"/>
    <a:font script="Hant" typeface="????"/>
    <a:font script="Arab" typeface="Times New Roman"/>
    <a:font script="Hebr" typeface="Times New Roman"/>
    <a:font script="Thai" typeface="Tahoma"/>
    <a:font script="Ethi" typeface="Nyala"/>
    <a:font script="Beng" typeface="Vrinda"/>
    <a:font script="Gujr" typeface="Shruti"/>
    <a:font script="Khmr" typeface="MoolBoran"/>
    <a:font script="Knda" typeface="Tunga"/>
    <a:font script="Guru" typeface="Raavi"/>
    <a:font script="Cans" typeface="Euphemia"/>
    <a:font script="Cher" typeface="Plantagenet Cherokee"/>
    <a:font script="Yiii" typeface="Microsoft Yi Baiti"/>
    <a:font script="Tibt" typeface="Microsoft Himalaya"/>
    <a:font script="Thaa" typeface="MV Boli"/>
    <a:font script="Deva" typeface="Mangal"/>
    <a:font script="Telu" typeface="Gautami"/>
    <a:font script="Taml" typeface="Latha"/>
    <a:font script="Syrc" typeface="Estrangelo Edessa"/>
    <a:font script="Orya" typeface="Kalinga"/>
    <a:font script="Mlym" typeface="Kartika"/>
    <a:font script="Laoo" typeface="DokChampa"/>
    <a:font script="Sinh" typeface="Iskoola Pota"/>
    <a:font script="Mong" typeface="Mongolian Baiti"/>
    <a:font script="Viet" typeface="Times New Roman"/>
    <a:font script="Uigh" typeface="Microsoft Uighur"/>
    <a:font script="Geor" typeface="Sylfaen"/>
    </a:majorFont>
    <a:minorFont>
    <a:latin typeface="Calibri"/>
    <a:ea typeface=""/>
    <a:cs typeface=""/>
    <a:font script="Jpan" typeface="MS P????"/>
    <a:font script="Hang" typeface="?? ??"/>
    <a:font script="Hans" typeface="??"/>
    <a:font script="Hant" typeface="????"/>
    <a:font script="Arab" typeface="Arial"/>
    <a:font script="Hebr" typeface="Arial"/>
    <a:font script="Thai" typeface="Tahoma"/>
    <a:font script="Ethi" typeface="Nyala"/>
    <a:font script="Beng" typeface="Vrinda"/>
    <a:font script="Gujr" typeface="Shruti"/>
    <a:font script="Khmr" typeface="DaunPenh"/>
    <a:font script="Knda" typeface="Tunga"/>
    <a:font script="Guru" typeface="Raavi"/>
    <a:font script="Cans" typeface="Euphemia"/>
    <a:font script="Cher" typeface="Plantagenet Cherokee"/>
    <a:font script="Yiii" typeface="Microsoft Yi Baiti"/>
    <a:font script="Tibt" typeface="Microsoft Himalaya"/>
    <a:font script="Thaa" typeface="MV Boli"/>
    <a:font script="Deva" typeface="Mangal"/>
    <a:font script="Telu" typeface="Gautami"/>
    <a:font script="Taml" typeface="Latha"/>
    <a:font script="Syrc" typeface="Estrangelo Edessa"/>
    <a:font script="Orya" typeface="Kalinga"/>
    <a:font script="Mlym" typeface="Kartika"/>
    <a:font script="Laoo" typeface="DokChampa"/>
    <a:font script="Sinh" typeface="Iskoola Pota"/>
    <a:font script="Mong" typeface="Mongolian Baiti"/>
    <a:font script="Viet" typeface="Arial"/>
    <a:font script="Uigh" typeface="Microsoft Uighur"/>
    <a:font script="Geor" typeface="Sylfaen"/>
    </a:minorFont>
    </a:fontScheme>
    <a:fmtScheme name="Office">
    <a:fillStyleLst>
    <a:solidFill>
    <a:schemeClr val="phClr"/>
    </a:solidFill>
    <a:gradFill rotWithShape="1">
    <a:gsLst>
    <a:gs pos="0">
    <a:schemeClr val="phClr">
    <a:tint val="50000"/>
    <a:satMod val="300000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="35000">
    <a:schemeClr val="phClr">
    <a:tint val="37000"/>
    <a:satMod val="300000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="100000">
    <a:schemeClr val="phClr">
    <a:tint val="15000"/>
    <a:satMod val="350000"/>
    </a:schemeClr>
    </a:gs>
    </a:gsLst>
    <a:lin ang="16200000" scaled="1"/>
    </a:gradFill>
    <a:gradFill rotWithShape="1">
    <a:gsLst>
    <a:gs pos="0">
    <a:schemeClr val="phClr">
    <a:shade val="51000"/>
    <a:satMod val="130000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="80000">
    <a:schemeClr val="phClr">
    <a:shade val="93000"/>
    <a:satMod val="130000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="100000">
    <a:schemeClr val="phClr">
    <a:shade val="94000"/>
    <a:satMod val="135000"/>
    </a:schemeClr>
    </a:gs>
    </a:gsLst>
    <a:lin ang="16200000" scaled="0"/>
    </a:gradFill>
    </a:fillStyleLst>
    <a:lnStyleLst>
    <a:ln w="9525" cap="flat" cmpd="sng" algn="ctr">
    <a:solidFill>
    <a:schemeClr val="phClr">
    <a:shade val="95000"/>
    <a:satMod val="105000"/>
    </a:schemeClr>
    </a:solidFill>
    <a:prstDash val="solid"/>
    </a:ln>
    <a:ln w="25400" cap="flat" cmpd="sng" algn="ctr">
    <a:solidFill>
    <a:schemeClr val="phClr"/>
    </a:solidFill>
    <a:prstDash val="solid"/>
    </a:ln>
    <a:ln w="38100" cap="flat" cmpd="sng" algn="ctr">
    <a:solidFill>
    <a:schemeClr val="phClr"/>
    </a:solidFill>
    <a:prstDash val="solid"/>
    </a:ln>
    </a:lnStyleLst>
    <a:effectStyleLst>
    <a:effectStyle>
    <a:effectLst>
    <a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0">
    <a:srgbClr val="000000">
    <a:alpha val="38000"/>
    </a:srgbClr>
    </a:outerShdw>
    </a:effectLst>
    </a:effectStyle>
    <a:effectStyle>
    <a:effectLst>
    <a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
    <a:srgbClr val="000000">
    <a:alpha val="35000"/>
    </a:srgbClr>
    </a:outerShdw>
    </a:effectLst>
    </a:effectStyle>
    <a:effectStyle>
    <a:effectLst>
    <a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
    <a:srgbClr val="000000">
    <a:alpha val="35000"/>
    </a:srgbClr>
    </a:outerShdw>
    </a:effectLst>
    <a:scene3d>
    <a:camera prst="orthographicFront">
    <a:rot lat="0" lon="0" rev="0"/>
    </a:camera>
    <a:lightRig rig="threePt" dir="t">
    <a:rot lat="0" lon="0" rev="1200000"/>
    </a:lightRig>
    </a:scene3d>
    <a:sp3d>
    <a:bevelT w="63500" h="25400"/>
    </a:sp3d>
    </a:effectStyle>
    </a:effectStyleLst>
    <a:bgFillStyleLst>
    <a:solidFill>
    <a:schemeClr val="phClr"/>
    </a:solidFill>
    <a:gradFill rotWithShape="1">
    <a:gsLst>
    <a:gs pos="0">
    <a:schemeClr val="phClr">
    <a:tint val="40000"/>
    <a:satMod val="350000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="40000">
    <a:schemeClr val="phClr">
    <a:tint val="45000"/>
    <a:shade val="99000"/>
    <a:satMod val="350000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="100000">
    <a:schemeClr val="phClr">
    <a:shade val="20000"/>
    <a:satMod val="255000"/>
    </a:schemeClr>
    </a:gs>
    </a:gsLst>
    <a:path path="circle">
    <a:fillToRect l="50000" t="-80000" r="50000" b="180000"/>
    </a:path>
    </a:gradFill>
    <a:gradFill rotWithShape="1">
    <a:gsLst>
    <a:gs pos="0">
    <a:schemeClr val="phClr">
    <a:tint val="80000"/>
    <a:satMod val="300000"/>
    </a:schemeClr>
    </a:gs>
    <a:gs pos="100000">
    <a:schemeClr val="phClr">
    <a:shade val="30000"/>
    <a:satMod val="200000"/>
    </a:schemeClr>
    </a:gs>
    </a:gsLst>
    <a:path path="circle">
    <a:fillToRect l="50000" t="50000" r="50000" b="50000"/>
    </a:path>
    </a:gradFill>
    </a:bgFillStyleLst>
    </a:fmtScheme>
    </a:themeElements>
    <a:objectDefaults/>
    <a:extraClrSchemeLst/>
    </a:theme>';
        add1xml(t_excel, 'xl/theme/theme1.xml', t_xxx);
        if g_debug_mode then
          debuglog('Stp1 run time(sec):' ||
                   round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
          l_debug_time := sysdate;
        end if;
        for s in 1 .. workbook.sheets.count() loop
          t_col_min := 16384;
          t_col_max := 1;
          ---CHANGE 2015.5.27 BY SAM.T
          /*
          t_row_ind := workbook.sheets( s ).rows.first();
          while t_row_ind is not null
          loop
            t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
            t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
            t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
          end loop;
          */
          lc_rows   := workbook.sheets(s).rows;
          t_row_ind := lc_rows.first();
          loop
            exit when t_row_ind is null;
            t_col_min := least(t_col_min, lc_rows(t_row_ind).first());
            t_col_max := greatest(t_col_max, lc_rows(t_row_ind).last());
            t_row_ind := lc_rows.next(t_row_ind);
          end loop;
          ---
          t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="' || alfan_col(t_col_min) || workbook.sheets(s)
                  .rows.first() || ':' || alfan_col(t_col_max) || workbook.sheets(s)
                  .rows.last() || '"/>
    <sheetViews>
    <sheetView' || case
                     when s = 1 then
                      ' tabSelected="1"'
                   end || ' workbookViewId="0">';
          if workbook.sheets(s)
           .freeze_rows > 0 and workbook.sheets(s).freeze_cols > 0 then
            t_xxx := t_xxx || ('<pane xSplit="' || workbook.sheets(s)
                     .freeze_cols || '" ' || 'ySplit="' || workbook.sheets(s)
                     .freeze_rows || '" ' || 'topLeftCell="' ||
                     alfan_col(workbook.sheets(s).freeze_cols + 1) ||
                     (workbook.sheets(s).freeze_rows + 1) || '" ' ||
                     'activePane="bottomLeft" state="frozen"/>');
          else
            if workbook.sheets(s).freeze_rows > 0 then
              t_xxx := t_xxx || '<pane ySplit="' || workbook.sheets(s)
                      .freeze_rows || '" topLeftCell="A' ||
                       (workbook.sheets(s).freeze_rows + 1) ||
                       '" activePane="bottomLeft" state="frozen"/>';
            end if;
            if workbook.sheets(s).freeze_cols > 0 then
              t_xxx := t_xxx || '<pane xSplit="' || workbook.sheets(s)
                      .freeze_cols || '" topLeftCell="' ||
                       alfan_col(workbook.sheets(s).freeze_cols + 1) ||
                       '1" activePane="bottomLeft" state="frozen"/>';
            end if;
          end if;
          t_xxx := t_xxx ||
                   '</sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>';
          if workbook.sheets(s).widths.count() > 0 then
            t_xxx     := t_xxx || '<cols>';
            t_col_ind := workbook.sheets(s).widths.first();
            while t_col_ind is not null loop
              t_xxx     := t_xxx || '<col min="' || t_col_ind || '" max="' ||
                           t_col_ind || '" width="' ||
                           to_char(workbook.sheets(s).widths(t_col_ind),
                                   'TM9',
                                   'NLS_NUMERIC_CHARACTERS=.,') ||
                           '" customWidth="1"/>';
              t_col_ind := workbook.sheets(s).widths.next(t_col_ind);
            end loop;
            t_xxx := t_xxx || '</cols>';
          end if;
          t_xxx := t_xxx || '<sheetData>';
          ---CHANGE 2015.5.27 BY SAM.T
          /*
          t_row_ind := workbook.sheets( s ).rows.first();
          t_tmp := null;
          while t_row_ind is not null
          loop
            t_tmp :=  t_tmp || '<row r="' || t_row_ind || '" spans="' || t_col_min || ':' || t_col_max || '">';
            t_len := length( t_tmp );
            t_col_ind := workbook.sheets( s ).rows( t_row_ind ).first();
            while t_col_ind is not null
            loop
              t_cell := '<c r="' || alfan_col( t_col_ind ) || t_row_ind || '"'
                     || ' ' || workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).style
                     || '><v>'
                     || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' )
                     || '</v></c>';
              if t_len > 32000
              then
                dbms_lob.writeappend( t_xxx, t_len, t_tmp );
                t_tmp := null;
                t_len := 0;
              end if;
              t_tmp :=  t_tmp || t_cell;
              t_len := t_len + length( t_cell );
              t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
            end loop;
            t_tmp :=  t_tmp || '</row>';
            t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
          end loop;
          */
          t_row_ind := lc_rows.first();
          t_tmp     := null;
          while t_row_ind is not null loop
            t_tmp     := t_tmp || '<row r="' || t_row_ind || '" spans="' ||
                         t_col_min || ':' || t_col_max || '">';
            t_len     := length(t_tmp);
            t_col_ind := lc_rows(t_row_ind).first();
            while t_col_ind is not null loop
              t_cell := '<c r="' || alfan_col(t_col_ind) || t_row_ind || '"' || ' ' || workbook.sheets(s)
                       .rows(t_row_ind)(t_col_ind)
                       .style || '><v>' ||
                        to_char(lc_rows(t_row_ind)(t_col_ind).value,
                                'TM9',
                                'NLS_NUMERIC_CHARACTERS=.,') || q'[</v></c>]';
              if t_len > 32000 then
                --modified by jinzhao writeappend->append 
                --dbms_lob.writeappend( t_xxx, t_len, t_tmp );
                dbms_lob.append(t_xxx, t_tmp);
                t_tmp := null;
                t_len := 0;
              end if;
              t_tmp     := t_tmp || t_cell;
              t_len     := t_len + length(t_cell);
              t_col_ind := lc_rows(t_row_ind).next(t_col_ind);
            end loop;
            t_tmp     := t_tmp || '</row>';
            t_row_ind := lc_rows.next(t_row_ind);
          end loop;
          ------------
          t_tmp := t_tmp || '</sheetData>';
          t_len := length(t_tmp);
          --modified by jinzhao writeappend->append 
          --dbms_lob.writeappend( t_xxx, t_len, t_tmp );
          dbms_lob.append(t_xxx, t_tmp);
          for a in 1 .. workbook.sheets(s).autofilters.count() loop
            t_xxx := t_xxx || '<autoFilter ref="' ||
                     alfan_col(nvl(workbook.sheets(s).autofilters(a)
                                   .column_start,
                                   t_col_min)) ||
                     nvl(workbook.sheets(s).autofilters(a).row_start,
                         workbook.sheets(s).rows.first()) || ':' ||
                     alfan_col(coalesce(workbook.sheets(s).autofilters(a)
                                        .column_end,
                                        workbook.sheets(s).autofilters(a)
                                        .column_start,
                                        t_col_max)) ||
                     nvl(workbook.sheets(s).autofilters(a).row_end,
                         workbook.sheets(s).rows.last()) || '"/>';
          end loop;
          if workbook.sheets(s).mergecells.count() > 0 then
            t_xxx := t_xxx || '<mergeCells count="' ||
                     to_char(workbook.sheets(s).mergecells.count()) || '">';
            for m in 1 .. workbook.sheets(s).mergecells.count() loop
              t_xxx := t_xxx || '<mergeCell ref="' || workbook.sheets(s)
                      .mergecells(m) || '"/>';
            end loop;
            t_xxx := t_xxx || '</mergeCells>';
          end if;
          --
          if workbook.sheets(s).validations.count() > 0 then
            t_xxx := t_xxx || '<dataValidations count="' ||
                     to_char(workbook.sheets(s).validations.count()) || '">';
            for m in 1 .. workbook.sheets(s).validations.count() loop
              t_xxx := t_xxx || '<dataValidation' || ' type="' || workbook.sheets(s).validations(m).type || '"' ||
                       ' errorStyle="' || workbook.sheets(s).validations(m)
                      .errorstyle || '"' || ' allowBlank="' || case
                         when nvl(workbook.sheets(s).validations(m).allowblank, true) then
                          '1'
                         else
                          '0'
                       end || '"' || ' sqref="' || workbook.sheets(s).validations(m)
                      .sqref || '"';
              if workbook.sheets(s).validations(m).prompt is not null then
                t_xxx := t_xxx || ' showInputMessage="1" prompt="' || workbook.sheets(s).validations(m)
                        .prompt || '"';
                if workbook.sheets(s).validations(m).title is not null then
                  t_xxx := t_xxx || ' promptTitle="' || workbook.sheets(s).validations(m)
                          .title || '"';
                end if;
              end if;
              if workbook.sheets(s).validations(m).showerrormessage then
                t_xxx := t_xxx || ' showErrorMessage="1"';
                if workbook.sheets(s).validations(m).error_title is not null then
                  t_xxx := t_xxx || ' errorTitle="' || workbook.sheets(s).validations(m)
                          .error_title || '"';
                end if;
                if workbook.sheets(s).validations(m).error_txt is not null then
                  t_xxx := t_xxx || ' error="' || workbook.sheets(s).validations(m)
                          .error_txt || '"';
                end if;
              end if;
              t_xxx := t_xxx || '>';
              if workbook.sheets(s).validations(m).formula1 is not null then
                t_xxx := t_xxx || '<formula1>' || workbook.sheets(s).validations(m)
                        .formula1 || '</formula1>';
              end if;
              if workbook.sheets(s).validations(m).formula2 is not null then
                t_xxx := t_xxx || '<formula2>' || workbook.sheets(s).validations(m)
                        .formula2 || '</formula2>';
              end if;
              t_xxx := t_xxx || '</dataValidation>';
            end loop;
            t_xxx := t_xxx || '</dataValidations>';
          end if;
          --
          if workbook.sheets(s).hyperlinks.count() > 0 then
            t_xxx := t_xxx || '<hyperlinks>';
            for h in 1 .. workbook.sheets(s).hyperlinks.count() loop
              t_xxx := t_xxx || '<hyperlink ref="' || workbook.sheets(s).hyperlinks(h).cell ||
                       '" r:id="rId' || h || '"/>';
            end loop;
            t_xxx := t_xxx || '</hyperlinks>';
          end if;
          t_xxx := t_xxx ||
                   '<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>';
          if workbook.sheets(s).comments.count() > 0 then
            t_xxx := t_xxx || '<legacyDrawing r:id="rId' ||
                     (workbook.sheets(s).hyperlinks.count() + 1) || '"/>';
          end if;
          --
          t_xxx := t_xxx || '</worksheet>';
          add1xml(t_excel, 'xl/worksheets/sheet' || s || '.xml', t_xxx);
          if workbook.sheets(s)
           .hyperlinks.count() > 0 or workbook.sheets(s).comments.count() > 0 then
            t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
            if workbook.sheets(s).comments.count() > 0 then
              t_xxx := t_xxx || '<Relationship Id="rId' ||
                       (workbook.sheets(s).hyperlinks.count() + 2) ||
                       '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments" Target="../comments' || s ||
                       '.xml"/>';
              t_xxx := t_xxx || '<Relationship Id="rId' ||
                       (workbook.sheets(s).hyperlinks.count() + 1) ||
                       '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing' || s ||
                       '.vml"/>';
            end if;
            for h in 1 .. workbook.sheets(s).hyperlinks.count() loop
              t_xxx := t_xxx || '<Relationship Id="rId' || h ||
                       '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="' || workbook.sheets(s).hyperlinks(h).url ||
                       '" TargetMode="External"/>';
            end loop;
            t_xxx := t_xxx || '</Relationships>';
            add1xml(t_excel,
                    'xl/worksheets/_rels/sheet' || s || '.xml.rels',
                    t_xxx);
          end if;
          --
          if workbook.sheets(s).comments.count() > 0 then
            declare
              cnt        pls_integer;
              author_ind tp_author;
              --          t_col_ind := workbook.sheets( s ).widths.next( t_col_ind );
            begin
              authors.delete();
              for c in 1 .. workbook.sheets(s).comments.count() loop
                authors(workbook.sheets(s).comments(c).author) := 0;
              end loop;
              t_xxx      := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <authors>';
              cnt        := 0;
              author_ind := authors.first();
              while author_ind is not null or
                    authors.next(author_ind) is not null loop
                authors(author_ind) := cnt;
                t_xxx := t_xxx || '<author>' || author_ind || '</author>';
                cnt := cnt + 1;
                author_ind := authors.next(author_ind);
              end loop;
            end;
            t_xxx := t_xxx || '</authors><commentList>';
            for c in 1 .. workbook.sheets(s).comments.count() loop
              t_xxx := t_xxx || '<comment ref="' ||
                       alfan_col(workbook.sheets(s).comments(c).column) ||
                       to_char(workbook.sheets(s).comments(c)
                               .row || '" authorId="' ||
                                authors(workbook.sheets(s).comments(c).author)) || '">
    <text>';
              if workbook.sheets(s).comments(c).author is not null then
                t_xxx := t_xxx ||
                         '<r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">' || workbook.sheets(s).comments(c)
                        .author || ':</t></r>';
              end if;
              t_xxx := t_xxx ||
                       '<r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">' || case
                         when workbook.sheets(s).comments(c).author is not null then
                          '
    '
                       end || workbook.sheets(s).comments(c).text ||
                       '</t></r></text></comment>';
            end loop;
            t_xxx := t_xxx || '</commentList></comments>';
            add1xml(t_excel, 'xl/comments' || s || '.xml', t_xxx);
            t_xxx := '<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
    <o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="2"/></o:shapelayout>
    <v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe"><v:stroke joinstyle="miter"/><v:path gradientshapeok="t" o:connecttype="rect"/></v:shapetype>';
            for c in 1 .. workbook.sheets(s).comments.count() loop
              t_xxx := t_xxx || '<v:shape id="_x0000_s' || to_char(c) ||
                       '" type="#_x0000_t202"
    style="position:absolute;margin-left:35.25pt;margin-top:3pt;z-index:' ||
                       to_char(c) ||
                       ';visibility:hidden;" fillcolor="#ffffe1" o:insetmode="auto">
    <v:fill color2="#ffffe1"/><v:shadow on="t" color="black" obscured="t"/><v:path o:connecttype="none"/>
    <v:textbox style="mso-direction-alt:auto"><div style="text-align:left"></div></v:textbox>
    <x:ClientData ObjectType="Note"><x:MoveWithCells/><x:SizeWithCells/>';
              t_w   := workbook.sheets(s).comments(c).width;
              t_c   := 1;
              loop
                if workbook.sheets(s)
                 .widths.exists(workbook.sheets(s).comments(c).column + t_c) then
                  t_cw := 256 * workbook.sheets(s)
                         .widths(workbook.sheets(s).comments(c).column + t_c);
                  t_cw := trunc((t_cw + 18) / 256 * 7); -- assume default 11 point Calibri
                else
                  t_cw := 64;
                end if;
                exit when t_w < t_cw;
                t_c := t_c + 1;
                t_w := t_w - t_cw;
              end loop;
              t_h   := workbook.sheets(s).comments(c).height;
              t_xxx := t_xxx || to_char('<x:Anchor>' || workbook.sheets(s).comments(c)
                                        .column || ',15,' || workbook.sheets(s).comments(c).row ||
                                        ',30,' || (workbook.sheets(s).comments(c)
                                        .column + t_c - 1) || ',' || round(t_w) || ',' ||
                                        (workbook.sheets(s).comments(c)
                                        .row + 1 + trunc(t_h / 20)) || ',' ||
                                        mod(t_h, 20) || '</x:Anchor>');
              t_xxx := t_xxx ||
                       to_char('<x:AutoFill>False</x:AutoFill><x:Row>' ||
                               (workbook.sheets(s).comments(c).row - 1) ||
                               '</x:Row><x:Column>' ||
                               (workbook.sheets(s).comments(c).column - 1) ||
                               '</x:Column></x:ClientData></v:shape>');
            end loop;
            t_xxx := t_xxx || '</xml>';
            add1xml(t_excel, 'xl/drawings/vmlDrawing' || s || '.vml', t_xxx);
          end if;
          --
        end loop;
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>';
        for s in 1 .. workbook.sheets.count() loop
          t_xxx := t_xxx || '
    <Relationship Id="rId' || (9 + s) ||
                   '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' || s ||
                   '.xml"/>';
        end loop;
        t_xxx := t_xxx || '</Relationships>';
        add1xml(t_excel, 'xl/_rels/workbook.xml.rels', t_xxx);
        t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' ||
                 workbook.str_cnt || '" uniqueCount="' ||
                 workbook.strings.count() || '">';
        t_tmp := null;
        for i in 0 .. workbook.str_ind.count() - 1 loop
          t_str := '<si><t>' ||
                   dbms_xmlgen.convert(substr(workbook.str_ind(i), 1, 32000)) ||
                   '</t></si>';
          if lengthb(t_tmp) + lengthb(t_str) > 32000 then
            t_xxx := t_xxx || t_tmp;
            t_tmp := null;
          end if;
          t_tmp := t_tmp || t_str;
        end loop;
        t_xxx := t_xxx || t_tmp || '</sst>';
        add1xml(t_excel, 'xl/sharedStrings.xml', t_xxx);
        if g_debug_mode then
          debuglog('Stp2 run time(sec):' ||
                   round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
          l_debug_time := sysdate;
        end if;
        finish_zip(t_excel);
        if g_debug_mode then
          debuglog('Stp3 run time(sec):' ||
                   round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
          l_debug_time := sysdate;
        end if;
        clear_workbook;
        if g_debug_mode then
          debuglog('Stp4 run time(sec):' ||
                   round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
          l_debug_time := sysdate;
          debuglog('finish(-)');
        end if;
        return t_excel;
      end;
    
      --
      procedure save(p_directory varchar2, p_filename varchar2) is
      begin
        blob2file(finish, p_directory, p_filename);
      end;
    
      --
      procedure query2sheet(p_sql            varchar2,
                            p_col_value_tab  smt_xlsx_maker_pkg.tab_col_value ---运行的动态SQL的绑定变量
                           ,
                            p_column_headers boolean := true,
                            p_directory      varchar2 := null,
                            p_filename       varchar2 := null,
                            p_sheet          pls_integer := null,
                            p_footer         boolean := true,
                            x_retcode        out number ---0:成功  非0:失败( 或者:0:成功  1:警告   2:错误  ----注意:确定警告的时候要做什么动作)
                           ,
                            x_errbuf         out varchar2 ---具体的错误信息
                            ) is
        l_process_phase number; --标识程序的进度
        t_sheet         pls_integer;
        t_c             integer;
        t_col_cnt       integer;
        t_desc_tab      dbms_sql.desc_tab2;
        d_tab           dbms_sql.date_table;
        n_tab           dbms_sql.number_table;
        v_tab           dbms_sql.varchar2_table;
        t_bulk_size     pls_integer := 200;
        t_r             integer;
        t_cur_row       pls_integer;
        ---
        l_debug_time date;
      begin
        x_retcode       := 0;
        x_errbuf        := null;
        l_process_phase := 0;
        if g_debug_mode then
          debuglog('query2sheet(+)');
          l_debug_time := sysdate;
        end if;
        if p_sql is null then
          x_errbuf  := '调用query2sheet的时候,必要的参数不存在,请检查参数!';
          x_retcode := 2;
          return;
        end if;
        if p_sheet is null then
          new_sheet;
        end if;
        l_process_phase := 1;
        t_c             := dbms_sql.open_cursor;
        dbms_sql.parse(t_c, p_sql, dbms_sql.native);
        l_process_phase := 2;
        if p_col_value_tab.count > 0 then
          for i in 1 .. p_col_value_tab.last loop
            if p_col_value_tab.exists(i) then
              dbms_sql.bind_variable(t_c,
                                     ':' || i,
                                     p_col_value_tab(i).col_value);
              if g_debug_mode then
                debuglog('绑定变量--' || i || ':' || p_col_value_tab(i).col_value);
              end if;
            end if;
          end loop;
        end if;
        l_process_phase := 3;
        dbms_sql.describe_columns2(t_c, t_col_cnt, t_desc_tab);
        for c in 1 .. t_col_cnt loop
          if p_column_headers then
            cell(c,
                 1,
                 t_desc_tab(c).col_name,
                 p_fontid   => get_font('Calibri', p_bold => true),
                 p_sheet    => t_sheet,
                 p_fillid   => get_fill('solid', 'cccccc'));
          end if;
          --      dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
          case
            when t_desc_tab(c).col_type in (2, 100, 101) then
              dbms_sql.define_array(t_c, c, n_tab, t_bulk_size, 1);
            when t_desc_tab(c).col_type in (12, 178, 179, 180, 181, 231) then
              dbms_sql.define_array(t_c, c, d_tab, t_bulk_size, 1);
            when t_desc_tab(c).col_type in (1, 8, 9, 96, 112) then
              dbms_sql.define_array(t_c, c, v_tab, t_bulk_size, 1);
            else
              null;
          end case;
        end loop;
        l_process_phase := 4;
        --
        t_cur_row := case
                       when p_column_headers then
                        2
                       else
                        1
                     end;
        t_sheet   := nvl(p_sheet, workbook.sheets.count());
        l_process_phase := 5;
        --
        t_r := dbms_sql.execute(t_c);
        loop
          t_r := dbms_sql.fetch_rows(t_c);
          if t_r > 0 then
            for c in 1 .. t_col_cnt loop
              case
                when t_desc_tab(c).col_type in (2, 100, 101) then
                  dbms_sql.column_value(t_c, c, n_tab);
                  for i in 0 .. t_r - 1 loop
                    if n_tab(i + n_tab.first()) is not null then
                      cell(c,
                           t_cur_row + i,
                           n_tab(i + n_tab.first()),
                           p_sheet => t_sheet);
                    end if;
                  end loop;
                  n_tab.delete;
                when t_desc_tab(c).col_type in (12, 178, 179, 180, 181, 231) then
                  dbms_sql.column_value(t_c, c, d_tab);
                  for i in 0 .. t_r - 1 loop
                    if d_tab(i + d_tab.first()) is not null then
                      cell(c,
                           t_cur_row + i,
                           d_tab(i + d_tab.first()),
                           p_sheet => t_sheet);
                    end if;
                  end loop;
                  d_tab.delete;
                when t_desc_tab(c).col_type in (1, 8, 9, 96, 112) then
                  dbms_sql.column_value(t_c, c, v_tab);
                  for i in 0 .. t_r - 1 loop
                    if v_tab(i + v_tab.first()) is not null then
                      cell(c,
                           t_cur_row + i,
                           v_tab(i + v_tab.first()),
                           p_sheet => t_sheet);
                    end if;
                  end loop;
                  v_tab.delete;
                else
                  null;
              end case;
            end loop;
          end if;
          if t_r != t_bulk_size then
            t_cur_row := t_cur_row + t_r;
          end if;
          exit when t_r != t_bulk_size;
          t_cur_row := t_cur_row + t_r;
        end loop;
        g_query2sheet_rows := t_cur_row - case
                                when p_column_headers then
                                 2
                                else
                                 1
                              end;
        l_process_phase := 6;
        dbms_sql.close_cursor(t_c);
        if g_debug_mode then
          debuglog('Stp1 run time(sec):' ||
                   round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
          l_debug_time := sysdate;
        end if;
        l_process_phase := 7;
        if p_footer then
          -- set footer
          if g_query2sheet_footer is null then
            cell(1,
                 t_cur_row + 2,
                 'Generated ' || to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') ||
                 ' by ' || user || ', rows:' || g_query2sheet_rows,
                 p_sheet => t_sheet);
          else
            cell(1,
                 t_cur_row + 2,
                 replace(g_query2sheet_footer, '&ROWS', g_query2sheet_rows),
                 p_sheet => t_sheet);
          end if;
        end if;
        l_process_phase := 8;
        if (p_directory is not null and p_filename is not null) then
          save(p_directory, p_filename);
        end if;
        if g_debug_mode then
          debuglog('Stp2 run time(sec):' ||
                   round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
          l_debug_time := sysdate;
          debuglog('query2sheet(-)');
        end if;
        l_process_phase := 99;
      exception
        when others then
          if dbms_sql.is_open(t_c) then
            dbms_sql.close_cursor(t_c);
          end if;
          clear_workbook;
          x_retcode := 2;
          x_errbuf  := 'p_sql:' || substr(p_sql, 1, 20) ||
                       ' 做自动输出XLSX文件的时候有异常错误!' || chr(10) || '错误信息:' ||
                       to_char(sqlcode) || '-' || sqlerrm || chr(10) || '程序进度:' ||
                       l_process_phase;
      end;
    
      procedure query2sheet(p_sql            varchar2,
                            p_column_headers boolean := true,
                            p_directory      varchar2 := null,
                            p_filename       varchar2 := null,
                            p_sheet          pls_integer := null,
                            p_footer         boolean := true,
                            x_retcode        out number ---0:成功  非0:失败( 或者:0:成功  1:警告   2:错误  ----注意:确定警告的时候要做什么动作)
                           ,
                            x_errbuf         out varchar2 ---具体的错误信息
                            ) is
        l_sql_statement    varchar2(32767);
        l_sql_statement_bv varchar2(32767);
        l_col_value_tab    smt_xlsx_maker_pkg.tab_col_value;
      begin
        if g_debug_mode then
          debuglog('query2sheetII(+)');
        end if;
        ---SQL自动转换,用上传说中的绑定变量!
        l_sql_statement := p_sql;
        ----
        l_sql_statement_bv := p_sql;
        /*--这个要额外的开发,这里先备注掉。
        XYG_FND_COMMON_PKG.SQL_BIND_VARIABLE_CHANGE (
            L_SQL_STATEMENT--P_SQL_STATEMENT        IN VARCHAR2 ---要改变的SQL语句
           ,L_SQL_STATEMENT_BV--X_SQL_STATEMENT_BV     OUT VARCHAR2--变化之后的SQL语句
           ,L_COL_VALUE_TAB--X_COL_VALUE_TAB        OUT T_COL_VALUE ---绑定参数的输出
           ,x_retcode--x_retcode              OUT NUMBER  ---0:成功  非0:失败( 或者:0:成功  1:警告   2:错误  ----注意:确定警告的时候要做什么动作)
           ,x_errbuf--x_errbuf               OUT VARCHAR2  ---具体的错误信息
         );
         */
        x_retcode := 0;
        if x_retcode <> 0 then
          x_errbuf := '产生绑定变量的动态SQL有异常错误!错误信息:' || x_errbuf;
          if g_debug_mode then
            debuglog('x_errbuf:' || x_errbuf);
          end if;
          return;
        else
          if g_debug_mode then
            if l_col_value_tab.count > 0 then
              debuglog('LAST绑定变量:' || l_col_value_tab.last);
              for i in 1 .. l_col_value_tab.last loop
                if l_col_value_tab.exists(i) then
                  debuglog('绑定变量:' || i || '--' || l_col_value_tab(i)
                           .col_value);
                end if;
              end loop;
            end if;
            debuglog('L_SQL_STATEMENT_BV:' || chr(10) || l_sql_statement_bv);
          end if;
        end if;
        query2sheet(l_sql_statement_bv,
                    l_col_value_tab ---运行的动态SQL的绑定变量
                   ,
                    p_column_headers,
                    p_directory,
                    p_filename,
                    p_sheet,
                    p_footer,
                    x_retcode,
                    x_errbuf);
        if g_debug_mode then
          debuglog('query2sheetII(-)');
        end if;
      end;
    
      procedure query2sheet(p_sql            varchar2,
                            p_column_headers boolean := true,
                            p_directory      varchar2 := null,
                            p_filename       varchar2 := null,
                            p_sheet          pls_integer := null,
                            p_footer         boolean := true) is
        l_retcode number;
        l_errbuf  varchar2(4000);
      begin
        query2sheet(p_sql,
                    p_column_headers,
                    p_directory,
                    p_filename,
                    p_sheet,
                    p_footer,
                    l_retcode,
                    l_errbuf);
        if l_retcode <> 0 then
          raise_application_error(-20008, l_errbuf, true);
        end if;
      end;
    
      procedure cursor2sheet(p_sql            in sys_refcursor,
                             p_column_headers boolean := true,
                             p_directory      varchar2 := null,
                             p_filename       varchar2 := null,
                             p_sheet          pls_integer := null,
                             p_footer         boolean := true) is
        ctx    dbms_xmlgen.ctxhandle;
        tmpxml xmltype;
        cursor cdata is
          select t2.column_value.getrootelement() colname,
                 extractvalue(t2.column_value, 'node()') value
            from table(xmlsequence(tmpxml)) t,
                 table(xmlsequence(extract(t.column_value, '/ROWSET/ROW/node()'))) t2
           order by rownum;
        tscolheaders sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
        tsvalues     sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
        t_sheet      pls_integer := 1;
        t_cur_row    pls_integer := 1;
        colid        pls_integer := 1;
        ncolnumber   pls_integer;
        n            pls_integer;
        atmpval      sys.anydata;
        nnumval      number;
        ttsval       timestamp;
        ddateval     date;
        svarcharval  varchar2(4000);
        bgottype     boolean;
        etypeconvert    exception;
        etypedateformat exception;
        etypenonnumeric exception;
        etypenotdefined exception;
        pragma exception_init(etypeconvert, -6502);
        pragma exception_init(etypedateformat, -1830);
        pragma exception_init(etypenonnumeric, -1858);
      begin
        -- XML Creation from the sys_refcursor
        ctx := dbms_xmlgen.newcontext(p_sql);
        -- this is important in order to get all the column headers, even if all data are null
        dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.empty_tag);
        dbms_xmlgen.getxmltype(ctx, tmpxml);
        if p_sheet is null then
          new_sheet;
        end if;
        -- Load Columns and Values into Arrays
        open cdata;
        fetch cdata bulk collect
          into tscolheaders, tsvalues;
        close cdata;
        -- get distinct headers
        tscolheaders := set(tscolheaders);
        -- get number of headers (of columns)
        ncolnumber := tscolheaders.count;
        -- Create column headers if wanted
        if p_column_headers then
          -- set headers into sheet
          for i in tscolheaders.first .. tscolheaders.last loop
            cell(i,
                 t_cur_row,
                 tscolheaders(i),
                 p_fontid => get_font('Calibri', p_bold => true),
                 p_sheet => t_sheet);
          end loop;
          t_cur_row := 2;
        end if;
        t_sheet := nvl(p_sheet, workbook.sheets.count());
        -- fill cells
        for i in tsvalues.first .. tsvalues.last loop
          -- check if we must reset col to 1 and go to next line
          if i > ncolnumber and mod(i, ncolnumber) = 1 then
            -- reset colId to 1 and go to next line
            colid     := 1;
            t_cur_row := t_cur_row + 1;
          end if;
          -- find the good type and insert into Cell
          -- initialize “checker”
          bgottype := false;
          -- Number ?
          if not bgottype then
            begin
              atmpval  := sys.anydata.convertnumber(tsvalues(i));
              bgottype := true;
              n        := atmpval.getnumber(nnumval);
              -- load data into cell
              cell(colid, t_cur_row, nnumval, p_sheet => t_sheet);
              -- if conversion fails
            exception
              when etypeconvert or etypedateformat or etypenonnumeric then
                bgottype := false;
            end;
          end if;
          -- TimeStamp ?
          if not bgottype then
            begin
              atmpval  := sys.anydata.converttimestamp(tsvalues(i));
              bgottype := true;
              n        := atmpval.gettimestamp(ttsval);
              -- load data into cell
              cell(colid, t_cur_row, to_date(ttsval), p_sheet => t_sheet);
              -- if conversion fails
            exception
              when etypeconvert or etypedateformat or etypenonnumeric then
                bgottype := false;
            end;
          end if;
          -- Date ?
          if not bgottype then
            begin
              atmpval  := sys.anydata.convertdate(tsvalues(i));
              bgottype := true;
              n        := atmpval.getdate(ddateval);
              -- load data into cell
              cell(colid, t_cur_row, ddateval, p_sheet => t_sheet);
              -- if conversion fails
            exception
              when etypeconvert or etypedateformat or etypenonnumeric then
                bgottype := false;
            end;
          end if;
          -- Varchar2 ?
          if not bgottype then
            begin
              atmpval  := sys.anydata.convertvarchar2(tsvalues(i));
              bgottype := true;
              n        := atmpval.getvarchar2(svarcharval);
              -- load data into cell
              cell(colid, t_cur_row, svarcharval, p_sheet => t_sheet);
              -- if conversion fails
            exception
              when etypeconvert or etypedateformat or etypenonnumeric then
                bgottype := false;
            end;
          end if;
          -- unsupported type
          if not bgottype then
            raise etypenotdefined;
          end if;
          -- go to next col
          colid := colid + 1;
        end loop;
        if p_footer then
          -- set footer
          cell(1,
               t_cur_row + 2,
               'Generated ' || sysdate || ' by ' || user,
               p_sheet => t_sheet);
        end if;
        if (p_directory is not null and p_filename is not null) then
          save(p_directory, p_filename);
        end if;
      exception
        when etypenotdefined then
          raise_application_error(-20999,
                                  'one data has an unsupported type',
                                  false);
          raise;
        when others then
          raise_application_error(-20999, 'Export to XLSX failed', true);
      end;
    
    end;
    smt_xlsx_maker_pkg 包体部分

     

    使用方法:

    使用方法:
      declare
        l_sql varchar2(30000);
      begin
        l_sql := 'select * from all_objects';
        smt_xlsx_maker_pkg.query2sheet(l_sql,
                                       true,
                                       'XLS_DIR',
                                       'Export2.xlsx');
      end;
      除此之外,基本上Excel 中有的效果它都可以生成。
      以下一个例子,包括居中,合并单元格,底色,新增工作表等:
      begin
        smt_xlsx_maker_pkg.clear_workbook;
        smt_xlsx_maker_pkg.new_sheet;
        smt_xlsx_maker_pkg.cell(5, 1, 5);
        smt_xlsx_maker_pkg.cell(3, 1, 3);
        smt_xlsx_maker_pkg.cell(2, 2, 45);
        smt_xlsx_maker_pkg.cell(3,
                                2,
                                'Anton Scheffer',
                                p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true));
        smt_xlsx_maker_pkg.cell(1,
                                4,
                                sysdate,
                                p_fontid => smt_xlsx_maker_pkg.get_font('Calibri',
                                                                        p_rgb => 'FFFF0000'));
        smt_xlsx_maker_pkg.cell(2,
                                4,
                                sysdate,
                                p_numfmtid => smt_xlsx_maker_pkg.get_numfmt('dd/mm/yyyy h:mm'));
        smt_xlsx_maker_pkg.cell(3,
                                4,
                                sysdate,
                                p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel('dd/mon/yyyy')));
        smt_xlsx_maker_pkg.cell(5,
                                5,
                                75,
                                p_borderid => smt_xlsx_maker_pkg.get_border('double',
                                                                            'double',
                                                                            'double',
                                                                            'double'));
        smt_xlsx_maker_pkg.cell(2, 3, 33);
        smt_xlsx_maker_pkg.hyperlink(1,
                                     6,
                                     'http://www.cnblogs.com/mellowsmile',
                                     'jinzhao site');
        smt_xlsx_maker_pkg.cell(1,
                                7,
                                'Some merged cells',
                                p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => 'center'));
        smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7);
        for i in 1 .. 5 loop
          smt_xlsx_maker_pkg.comment(3, i + 3, 'Row ' || (i + 3), 'Anton');
        end loop;
        smt_xlsx_maker_pkg.new_sheet;
        smt_xlsx_maker_pkg.set_row(1,
                                   p_fillid => smt_xlsx_maker_pkg.get_fill('solid',
                                                                           'FFFF0000'));
        for i in 1 .. 5 loop
          smt_xlsx_maker_pkg.cell(1, i, i);
          smt_xlsx_maker_pkg.cell(2, i, i * 3);
          smt_xlsx_maker_pkg.cell(3, i, 'x ' || i * 3);
        end loop;
        smt_xlsx_maker_pkg.query2sheet('select rownum, x.*
      , case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
      , case when mod( rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect by rownum <= 5');
        smt_xlsx_maker_pkg.save('XLS_DIR', 'Export3.xlsx');
      end;

    对比两种导出excel的方法,第一种方法实际上导出的是html文件格式,且注意set pagesize 最大为50000,超过50000行数据后会自动分页重新打印出一行标题,不需要创建oracle directory可以导出excel到客户机但是单元格合并、批注等不可以个性化设置,第二种可设置单元格样式但是需要创建directory且文件最终在服务器端生成。

    转载于:https://www.cnblogs.com/mellowsmile/p/5277880.html

    展开全文
  • oracle 导出关键字说明

    2017-09-12 15:33:00
    关键字 说明(默认) ----------------------------------------...Full 导出整个文件(N) Buffer 数据缓冲区的大小 Owner 所有者用户名列表 File 输出文件(ExpDat.dmp) Tables 表名列表 Compress 导入一个范...

     

    关键字       说明(默认)

    -----------------------------------------

    UserId    用户名/口令

    Full      导出整个文件(N)

    Buffer  数据缓冲区的大小

    Owner 所有者用户名列表

    File    输出文件(ExpDat.dmp)

    Tables    表名列表

    Compress  导入一个范围(Y),实现压缩,相当于用winzip把dmp文件很好的压缩。

    Recordlength IO   记录的长度

    Grants   导出权限(Y)

    Inctype   增量导出类型

    Indexs   导出索引(Y)

    Record 跟踪增量导出(Y)

    Rows    导出数据行(Y)

    Parfile   参数文件名

    Constraints  导出约束(Y)

    Consistent  交叉表一致性

    Log  屏幕输出的 日志文件

    Statistics  分析对象(Estimate)

    Direct  直接路径(N)

    Triggers  导出触发器(Y)

    FeedBack  显示每x行(0) 的进度

    FileSize   各转出文件的最大尺寸

    Query  选定导出表子集的子句

     

    下列关键字仅用于可传输的表空

    Transport_Tablespace    导出可传输的表空间元数据(N)

    Tablespace      将传输的表空间列表

    转载于:https://www.cnblogs.com/tianciliangen/p/7510412.html

    展开全文
  • oracle课程进度

    2011-12-21 18:51:49
    oracle学习课程进度表详细介绍Oracle11g安装、升级 管理模式对象 Oracle性能调优 Oracle11g数据库备份与恢复 RAC概述 Oracle11g数据库概述及新特性 Oracle 11g Enterprise Manager简介 SGA和PGA调优 使用RMAN工具 ...
  • Oracle数据泵,impdp,expdp,查看导出导入进度
  • Oracle导出导入dmp文件(exp.imp命令行)

    千次阅读 2019-10-28 17:26:28
    使用Oracle命令行导出导入dmp文件, 从而在两个数据库之间快速转移数据, 也可以用来作为数据库的备份, 将来可以快速恢复数据。 命令:导出exp、导入imp 步骤: 使用Oracle的exp命令将指定数据库导出为dmp文件; ...
  • oracle导出及导入dmp

    2018-07-11 11:32:00
    ATTACH该选项用于在客户会话与已存在导出作用之间建立关联.语法如下ATTACH=[schema_name.]job_nameSchema_name用于指定方案名,job_name用于指定导出作业名.注意,如果使用ATTACH选项,在命令行除了连接字符串和ATTACH...
  • 导出数据 exp zl_gj/zlkj@gqxt grants=y tables=(zl_gj.ckgj,zl_gj.gjlx,zl_gj.rkgj) file=c:\gj.dmp log=c:\gj.log exp 导出命令; 可以通过输入 IMP 命令和您的用户名/口令后接用户名/口令的命令: 例程: IMP ...
  • oracle 导入导出

    2011-11-03 15:30:04
    Oracle数据导入导出imp/exp 功能:Oracle数据导入导出imp/exp就相当与oracle数据还原与备份。 大多情况都可以用Oracle数据导入导出完成数据的备份和还原(不会造成数据的丢失)。 Oracle有个好处,虽然你的...
  • oracle导入导出

    2018-01-19 18:46:16
    如果爱一个人,那就爱整个的他,实事求是地照他本来的面目去爱他,而不是脱离实际希望他这样那样的。-------------托尔斯泰 ...Oracle导出导入,即oracle exp/imp和expdp/impdp工具,是数据库备份恢复的一个手段。用
  • E:\oracle\ora90\BIN&gt;exp userid=roeee file=c:\roeee.dmp Export: Release 9.0.1.1.1 - Production on 星期三 12月 6 15:41:52 2006 (c) Copyright 2001 Oracle Corporation. All rights reserved. 口令:...
  • Oracle导出方法(emp)

    千次阅读 2009-03-12 09:36:00
    exp scott/tiger buffer=32768 file=d:/scott.dmp tables=(emp,dept,bonus) log=d:/czrk.logE:/>exp help=y通过输入 EXP 命令和用户名/口令,您可以在用户 / 口令之后的命令:实例: ...EXP 命令来控制“导出”的运行方式
  • 这段时间,一直在忙于数据库的备份工作。以前一直不理解Java所谓的分布式部署,以为属于一种数据库与程序分离,或者程序与资源的...在理解什么叫分布式之前,还是写一下关于Oracle备份和导入的一些脚本吧。导出:#ex
  • E:\oracle\ora90\BIN&gt;exp userid=roeee file=c:\roeee.dmp Export: Release 9.0.1.1.1 - Production on 星期三 12月 6 15:41:52 2006 (c) Copyright 2001 Oracle Corporation. All rights reserved. 口令: ...
  • 1.oracle导出表结构到sql文件  需要登陆oralce用户: sqlplus 用户名/密码@实例名 SQL> set serveroutput on SQL> set long 999999 SQL> set feedback off SQL> spool temp.sql SQL> SELECT DBMS_...
  • Oracle导入导出工具

    千次阅读 2017-02-23 19:39:31
    Oracle导入导出工具 EXP和IMP介绍: 1、EXP:数据导出工具  有三种主要的方式(完全、用户、表)  1、完全:  将整个Oracle数据库中的所有对象、用户等导出。  2、用户模式:  将指定用户的所有对象和...
  • 昨天和同事聊起ORACLE的数据备份,imp/exp命令没有提供进度信息,只显示当前处理的对象名(表、视图等),似乎有点儿缺憾。于是想到几年前自己写的一个备份恢复小程序,提供的备份进度显示功能,它的实现思路一直让我...
  • oracle数据导出导入

    2015-12-08 12:57:03
    oracel数据导出导入 一、导出模式(三种模式)及命令格式 1、 全库模式 exp 用户名/密码@网络服务名 full=y file=路径\文件名.dmp log=路径\文件名.log 2、 用户模式(一般情况下采用此模式) exp 用户名/密码@...
  • 1.查看索引创建进度 /* Formatted on 2019/8/20 下午 05:08:25 (QP5 v5.163.1008.3004) */ SELECT SID, DECODE (totalwork, 0, 0, ROUND (100 * sofar / totalwork, 2)) "Percent", MESSAGE "M...
  • oracle文件导入导出

    2020-04-22 11:50:44
    oracle文件导出命令 命令行运行: exp 用户名/密码@服务名 file=路径/导出文件名.dmp tables=表名 若导出需添加条件过滤,增加query限制 exp 用户名/密码@服务名 file=路径/导出文件名.dmp tables=表名 query=\" ...
  • Oracle导入导出详解

    2020-10-07 22:19:06
    导入/导出ORACLE幸存的最古老的两个命令行工具,其实我从来不认为Exp/Imp是一种好的备份方式,正确的说法是Exp/Imp只能是一个好的转储工具,特别是在小型数据库的转储,表空间的迁移,表的抽取,检测逻辑和物理...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,855
精华内容 1,542
关键字:

oracle导出进度