你的位置:首页 > 数据库

[数据库]Oracle导出excel


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

使用sqlplus:

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

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

sql脚本,get_tables.sql

--get_tables.sqlselect * 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报错*/ --------- /********************************************** ****************************************************************************** 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_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_convert(p_text);  workbook.sheets(t_sheet).comments(t_ind).author := dbms_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 add1*/ -- procedure add1in out nocopy blob,          p_filename varchar2,          p_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_'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 := '<?';  for s in 1 .. workbook.sheets.count() loop   t_xxx := t_xxx || '<Override PartName="/xl/worksheets/sheet' || s ||        '.';  end loop;  t_xxx := t_xxx || '<Override PartName="/xl/theme/theme1.';  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 ||         '.';   end if;  end loop;  t_xxx := t_xxx || '</Types>';  add1'[Content_Types].', t_xxx);  t_xxx := '<?' || 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>';  add1'docProps/core.', t_xxx);  t_xxx := '<?' || 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>';  add1'docProps/app.', t_xxx);  t_xxx := '<?';  add1'_rels/.rels', t_xxx);  t_xxx := '<?';  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 color: #ff0000;">' || workbook.borders(b).left || '"/>'        end || case         when workbook.borders(b).right is null then         '<right/>'         else         '<right color: #ff0000;">' || workbook.borders(b).right || '"/>'        end || case         when workbook.borders(b).top is null then         '<top/>'         else         '<top color: #ff0000;">' || workbook.borders(b).top || '"/>'        end || case         when workbook.borders(b).bottom is null then         '<bottom/>'         else         '<bottom color: #ff0000;">' || 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" defaultTable defaultPivot/><extLst><ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" ';  add1'xl/styles.', t_xxx);  --<workbookPr date1904="true" defaultThemeVersion="124226"/>  t_xxx := '<?';  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>';  add1'xl/workbook.', t_xxx);  t_xxx := '<?';  add1'xl/theme/theme1.', 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 := '<?' || 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 || '"' ||          ' errorcolor: #ff0000;">' || 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>';   add1'xl/worksheets/sheet' || s || '.', t_xxx);   if workbook.sheets(s)    .hyperlinks.count() > 0 or workbook.sheets(s).comments.count() > 0 then    t_xxx := '<?';    if workbook.sheets(s).comments.count() > 0 then     t_xxx := t_xxx || '<Relationship Id="rId' ||          (workbook.sheets(s).hyperlinks.count() + 2) ||          '" Type="http://schemas.open' || s ||          '.';     t_xxx := t_xxx || '<Relationship Id="rId' ||          (workbook.sheets(s).hyperlinks.count() + 1) ||          '" Type="http://schemas.open' || 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.open' || workbook.sheets(s).hyperlinks(h).url ||          '" TargetMode="External"/>';    end loop;    t_xxx := t_xxx || '</Relationships>';    add1'xl/worksheets/_rels/sheet' || s || '.',        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   := '<?';     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 ' || 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 ' || 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>';    add1'xl/comments' || s || '.', t_xxx);    t_xxx := '<';    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"color: #ff0000;">' ||          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 ><div ></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 || '</';    add1'xl/drawings/vmlDrawing' || s || '.vml', t_xxx);   end if;   --  end loop;  t_xxx := '<?';  for s in 1 .. workbook.sheets.count() loop   t_xxx := t_xxx || '<Relationship Id="rId' || (9 + s) ||        '" Type="http://schemas.open' || s ||        '.';  end loop;  t_xxx := t_xxx || '</Relationships>';  add1'xl/_rels/workbook.', t_xxx);  t_xxx := '<?' ||       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_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>';  add1'xl/sharedStrings.', 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_cursor cdata is   select t2.column_value.getrootelement() colname,       extractvalue(t2.column_value, 'node()') value    from table(table('/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  --   ctx := dbms_-- this is important in order to get all the column headers, even if all data are null  dbms_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且文件最终在服务器端生成。