Oracle导出excel数据的方法
来源:爱站网时间:2021-01-07编辑:网友分享
导出excel数据在oracle中是经常需要使用的功能,那么你知道Oracle怎么导出excel数据吗?以下就让爱站小编为大家介绍Oracle导出excel数据的方法。
导出excel数据在oracle中是经常需要使用的功能,那么你知道Oracle怎么导出excel数据吗?以下就让爱站小编为大家介绍Oracle导出excel数据的方法。
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
然后在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 0 then t_blob := utl_compress.lz_compress(p_content); t_clen := dbms_lob.getlength(t_blob) - 18; t_compressed := 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 := ''; add1xml(t_excel, '[Content_Types].xml', t_xxx); t_xxx := ' '; for s in 1 .. workbook.sheets.count() loop t_xxx := t_xxx || ' '; end loop; t_xxx := t_xxx || ' '; for s in 1 .. workbook.sheets.count() loop if workbook.sheets(s).comments.count() > 0 then t_xxx := t_xxx || ' '; end if; end loop; t_xxx := t_xxx || ' '; add1xml(t_excel, 'docProps/core.xml', t_xxx); t_xxx := ' ' || sys_context('userenv', 'os_user') || ' ' || sys_context('userenv', 'os_user') || ' ' || to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') || ' ' || to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') || ' '; add1xml(t_excel, 'docProps/app.xml', t_xxx); t_xxx := ' Microsoft Excel 0 false Worksheets ' || workbook.sheets.count() || ' '; for s in 1 .. workbook.sheets.count() loop t_xxx := t_xxx || ' ' || workbook.sheets(s).name || ' '; end loop; t_xxx := t_xxx || 'false false false 14.0300 '; add1xml(t_excel, '_rels/.rels', t_xxx); t_xxx := ' '; if workbook.numfmts.count() > 0 then t_xxx := t_xxx || ' '; add1xml(t_excel, 'xl/styles.xml', t_xxx); --'; for n in 1 .. workbook.numfmts.count() loop t_xxx := t_xxx || ' '; end if; t_xxx := t_xxx || ''; end loop; t_xxx := t_xxx || ' '; for f in 0 .. workbook.fonts.count() - 1 loop t_xxx := t_xxx || '' || case when workbook.fonts(f).bold then '' end || case when workbook.fonts(f).italic then '' end || case when workbook.fonts(f).underline then '' end || ' '; end loop; t_xxx := t_xxx || ' '; for f in 0 .. workbook.fills.count() - 1 loop t_xxx := t_xxx || ' '; end loop; t_xxx := t_xxx || ' ' || case when workbook.fills(f).fgrgb is not null then ' ' end || ' '; for b in 0 .. workbook.borders.count() - 1 loop t_xxx := t_xxx || ' ' || case when workbook.borders(b).left is null then ' '; end loop; t_xxx := t_xxx || '' else ' ' end || case when workbook.borders(b).right is null then ' ' else ' ' end || case when workbook.borders(b).top is null then ' ' else ' ' end || case when workbook.borders(b).bottom is null then ' ' else ' ' end || ' '; for x in 1 .. workbook.cellxfs.count() loop t_xxx := t_xxx || ' '; 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 || ' '; end loop; t_xxx := t_xxx || ''; end if; t_xxx := t_xxx || ' t_xxx := ' '; add1xml(t_excel, 'xl/workbook.xml', t_xxx); t_xxx := ' '; for s in 1 .. workbook.sheets.count() loop t_xxx := t_xxx || ' '; if workbook.defined_names.count() > 0 then t_xxx := t_xxx || ''; end loop; t_xxx := t_xxx || ' '; for s in 1 .. workbook.defined_names.count() loop t_xxx := t_xxx || ' '; end if; t_xxx := t_xxx || '' || workbook.defined_names(s).ref || ' '; end loop; t_xxx := t_xxx || ''; 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 := ' '; 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 := ' '; if workbook.sheets(s) .freeze_rows > 0 and workbook.sheets(s).freeze_cols > 0 then t_xxx := t_xxx || (' '); else if workbook.sheets(s).freeze_rows > 0 then t_xxx := t_xxx || ' '; end if; if workbook.sheets(s).freeze_cols > 0 then t_xxx := t_xxx || ' '; end if; end if; t_xxx := t_xxx || ' '; if workbook.sheets(s).widths.count() > 0 then t_xxx := t_xxx || ' '; t_col_ind := workbook.sheets(s).widths.first(); while t_col_ind is not null loop t_xxx := t_xxx || ' '; end if; t_xxx := t_xxx || ''; t_col_ind := workbook.sheets(s).widths.next(t_col_ind); end loop; t_xxx := t_xxx || ' '; ---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 || ' '; 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 || ''; 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 := '
'; 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 || ''; 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 || ' ' || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' ) || ' '; t_len := length(t_tmp); t_col_ind := lc_rows(t_row_ind).first(); while t_col_ind is not null loop t_cell := '
'; t_row_ind := lc_rows.next(t_row_ind); end loop; ------------ t_tmp := t_tmp || ']'; 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 || ' ' || to_char(lc_rows(t_row_ind)(t_col_ind).value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,') || q'[ '; end loop; if workbook.sheets(s).mergecells.count() > 0 then t_xxx := t_xxx || ' '; for m in 1 .. workbook.sheets(s).mergecells.count() loop t_xxx := t_xxx || ' '; end if; -- if workbook.sheets(s).validations.count() > 0 then t_xxx := t_xxx || ''; end loop; t_xxx := t_xxx || ' '; for m in 1 .. workbook.sheets(s).validations.count() loop t_xxx := t_xxx || ' '; end if; -- if workbook.sheets(s).hyperlinks.count() > 0 then t_xxx := t_xxx || ''; if workbook.sheets(s).validations(m).formula1 is not null then t_xxx := t_xxx || ' '; end loop; t_xxx := t_xxx || '' || workbook.sheets(s).validations(m) .formula1 || ' '; end if; if workbook.sheets(s).validations(m).formula2 is not null then t_xxx := t_xxx || '' || workbook.sheets(s).validations(m) .formula2 || ' '; end if; t_xxx := t_xxx || ''; for h in 1 .. workbook.sheets(s).hyperlinks.count() loop t_xxx := t_xxx || ' '; end if; t_xxx := t_xxx || ''; end loop; t_xxx := t_xxx || ' '; if workbook.sheets(s).comments.count() > 0 then t_xxx := t_xxx || ' '; end if; -- t_xxx := t_xxx || ' '; if workbook.sheets(s).comments.count() > 0 then t_xxx := t_xxx || ' '; 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 := ''; t_xxx := t_xxx || ' '; end if; for h in 1 .. workbook.sheets(s).hyperlinks.count() loop t_xxx := t_xxx || ' '; end loop; t_xxx := t_xxx || ' '; add1xml(t_excel, 'xl/comments' || s || '.xml', t_xxx); 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_ind || ' '; cnt := cnt + 1; author_ind := authors.next(author_ind); end loop; end; t_xxx := t_xxx || ''; for c in 1 .. workbook.sheets(s).comments.count() loop t_xxx := t_xxx || ' '; end loop; t_xxx := t_xxx || ' '; if workbook.sheets(s).comments(c).author is not null then t_xxx := t_xxx || ' '; end if; t_xxx := t_xxx || ' ' || workbook.sheets(s).comments(c) .author || ': ' || case when workbook.sheets(s).comments(c).author is not null then ' ' end || workbook.sheets(s).comments(c).text || ' '; add1xml(t_excel, 'xl/drawings/vmlDrawing' || s || '.vml', t_xxx); end if; -- end loop; t_xxx := ' '; for c in 1 .. workbook.sheets(s).comments.count() loop t_xxx := t_xxx || ' '); end loop; t_xxx := t_xxx || ' '; 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 ' || 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) || ''); t_xxx := t_xxx || to_char(' False ' || (workbook.sheets(s).comments(c).row - 1) || '
' || (workbook.sheets(s).comments(c).column - 1) || ' '; add1xml(t_excel, 'xl/_rels/workbook.xml.rels', t_xxx); t_xxx := ' '; for s in 1 .. workbook.sheets.count() loop t_xxx := t_xxx || ' '; end loop; t_xxx := t_xxx || ' '; t_tmp := null; for i in 0 .. workbook.str_ind.count() - 1 loop t_str := ' '; 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;'; 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 || ' ' || dbms_xmlgen.convert(substr(workbook.str_ind(i), 1, 32000)) || '
使用方法:
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
对比两种导出excel的方法,第一种方法实际上导出的是html文件格式,且注意set pagesize 最大为50000,超过50000行数据后会自动分页重新打印出一行标题,不需要创建oracle directory可以导出excel到客户机但是单元格合并、批注等不可以个性化设置,第二种可设置单元格样式但是需要创建directory且文件最终在服务器端生成。
以上所述是小编给大家介绍Oracle导出excel数据的方法,希望自己能在不断的学习中,不断的总结,使自己的学习能力和学习效率能有一定的提高.
下一篇:如何使用Oracle触发器