Oracle導出excel，oracleexcel
oracle導出excel（非csv）的方法有兩種，1、使用sqlplus spool，2、使用包體 現將網上相關代碼整理後貼出以備不時之需：
使用sqlplus：
使用sqlplus需要兩個文件：sql腳本文件和格式設置文件。
去除冗余信息，main.sql
--main.sql 注意，需要在sqlplus下運行 非plsql命令行下
set linesize 200
set term off verify off feedback off pagesize 999
set markup html on entmap ON spool on preformat off
spool test_tables.xls
@get_tables.sql
spool off
exit
sql腳本，get_tables.sql
--get_tables.sql
select * from all_objects where rownum<=1000;
然後在sqlplus下運行main.sql
使用包體編程：
![]()
create or replace package smt_xlsx_maker_pkg is
/******************************************************************************
NAME: SMT_XLSX_MAKER_PKG
PURPOSE: XLSX 生成 Pkg，主要是從Oracle數據庫端生成Xlsx二進制的文件。
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2011/2/19 Anton Scheffer 1,New Create the pkg
1.1 2015/6/10 Sam.T 1.優化核心處理生成xlsx的代碼，使得生成文檔的執行效率大大提高！
1.1 2015/6/10 Sam.T 1.query2sheet增加綁定變量的可選輸入參數。
1.2 2015/6/15 Sam.T 1.代碼增加調試模式。直接設置G_DEBUG_MODE變量即可。
1.2 2015/6/20 Sam.T 1.為方便使用，再次封裝一些簡單易用的過程，生成xlsx文檔
1.2 2015/7/5 Sam.T 1.單條SQL生成xlsx文檔的內容，增加是否顯示foot，以及顯示的行數。
jinzhao 解決數據量較大情況下導出excel報錯xml的問題。將dbms_lob.writeappend修改為dbms_lob.append
******************************************************************************/
---------
/**********************************************
******************************************************************************
Copyright (C) 2011, 2012 by Anton Scheffer
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
******************************************************************************
******************************************** */
/*
使用方法：
declare
l_sql varchar2(30000);
begin
l_sql := 'select * from all_objects';
smt_xlsx_maker_pkg.query2sheet(l_sql,
true,
'XLS_DIR',
'Export2.xlsx');
end;
除此之外，基本上Excel 中有的效果它都可以生成。
以下一個例子，包括居中，合並單元格，底色，新增工作表等：
begin
smt_xlsx_maker_pkg.clear_workbook;
smt_xlsx_maker_pkg.new_sheet;
smt_xlsx_maker_pkg.cell(5, 1, 5);
smt_xlsx_maker_pkg.cell(3, 1, 3);
smt_xlsx_maker_pkg.cell(2, 2, 45);
smt_xlsx_maker_pkg.cell(3,
2,
'Anton Scheffer',
p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true));
smt_xlsx_maker_pkg.cell(1,
4,
sysdate,
p_fontid => smt_xlsx_maker_pkg.get_font('Calibri',
p_rgb => 'FFFF0000'));
smt_xlsx_maker_pkg.cell(2,
4,
sysdate,
p_numfmtid => smt_xlsx_maker_pkg.get_numfmt('dd/mm/yyyy h:mm'));
smt_xlsx_maker_pkg.cell(3,
4,
sysdate,
p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel('dd/mon/yyyy')));
smt_xlsx_maker_pkg.cell(5,
5,
75,
p_borderid => smt_xlsx_maker_pkg.get_border('double',
'double',
'double',
'double'));
smt_xlsx_maker_pkg.cell(2, 3, 33);
smt_xlsx_maker_pkg.hyperlink(1,
6,
'http://www.cnblogs.com/mellowsmile',
'jinzhao site');
smt_xlsx_maker_pkg.cell(1,
7,
'Some merged cells',
p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => 'center'));
smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7);
for i in 1 .. 5 loop
smt_xlsx_maker_pkg.comment(3, i + 3, 'Row ' || (i + 3), 'Anton');
end loop;
smt_xlsx_maker_pkg.new_sheet;
smt_xlsx_maker_pkg.set_row(1,
p_fillid => smt_xlsx_maker_pkg.get_fill('solid',
'FFFF0000'));
for i in 1 .. 5 loop
smt_xlsx_maker_pkg.cell(1, i, i);
smt_xlsx_maker_pkg.cell(2, i, i * 3);
smt_xlsx_maker_pkg.cell(3, i, 'x ' || i * 3);
end loop;
smt_xlsx_maker_pkg.query2sheet('select rownum, x.*
, case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
, case when mod( rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect by rownum <= 5');
smt_xlsx_maker_pkg.save('XLS_DIR', 'Export3.xlsx');
end;
*/
g_debug_mode boolean := false;
---DBMS_OUTPUT直接輸出/FILE_OUTPUT文檔輸出/REQUEST_OUTPUT請求日志輸出/CONTEXT_OUTPUT 將日志改為上下文輸出
g_debug_type varchar2(240) := 'DBMS_OUTPUT';
---綁定變量用的臨時表變量
type rec_col_value is record(
col_id number(3),
col_value varchar2(2400));
type tab_col_value is table of rec_col_value index by binary_integer;
--
type tp_alignment is record(
vertical varchar2(11),
horizontal varchar2(16),
wraptext boolean);
--
procedure clear_workbook;
--
procedure new_sheet(p_sheetname varchar2 := null);
--
function orafmt2excel(p_format varchar2 := null) return varchar2;
--
function get_numfmt(p_format varchar2 := null) return pls_integer;
--
function get_font(p_name varchar2,
p_family pls_integer := 2,
p_fontsize number := 11,
p_theme pls_integer := 1,
p_underline boolean := false,
p_italic boolean := false,
p_bold boolean := false,
p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
) return pls_integer;
--
function get_fill(p_patterntype varchar2,
p_fgrgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
) return pls_integer;
--
function get_border(p_top varchar2 := 'thin',
p_bottom varchar2 := 'thin',
p_left varchar2 := 'thin',
p_right varchar2 := 'thin')
/*
none
thin
medium
dashed
dotted
thick
double
hair
mediumDashed
dashDot
mediumDashDot
dashDotDot
mediumDashDotDot
slantDashDot
*/
return pls_integer;
--
function get_alignment(p_vertical varchar2 := null,
p_horizontal varchar2 := null,
p_wraptext boolean := null)
/* horizontal
center
centerContinuous
distributed
fill
general
justify
left
right
*/
/* vertical
bottom
center
distributed
justify
top
*/
return tp_alignment;
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value number,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null);
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value varchar2,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null);
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value date,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null);
--
procedure hyperlink(p_col pls_integer,
p_row pls_integer,
p_url varchar2,
p_value varchar2 := null,
p_sheet pls_integer := null);
--
procedure comment(p_col pls_integer,
p_row pls_integer,
p_text varchar2,
p_author varchar2 := null,
p_width pls_integer := 150 -- pixels
,
p_height pls_integer := 100 -- pixels
,
p_sheet pls_integer := null);
--
procedure mergecells(p_tl_col pls_integer -- top left
,
p_tl_row pls_integer,
p_br_col pls_integer -- bottom right
,
p_br_row pls_integer,
p_sheet pls_integer := null);
--
procedure list_validation(p_sqref_col pls_integer,
p_sqref_row pls_integer,
p_tl_col pls_integer -- top left
,
p_tl_row pls_integer,
p_br_col pls_integer -- bottom right
,
p_br_row pls_integer,
p_style varchar2 := 'stop' -- stop, warning, information
,
p_title varchar2 := null,
p_prompt varchar := null,
p_show_error boolean := false,
p_error_title varchar2 := null,
p_error_txt varchar2 := null,
p_sheet pls_integer := null);
--
procedure list_validation(p_sqref_col pls_integer,
p_sqref_row pls_integer,
p_defined_name varchar2,
p_style varchar2 := 'stop' -- stop, warning, information
,
p_title varchar2 := null,
p_prompt varchar := null,
p_show_error boolean := false,
p_error_title varchar2 := null,
p_error_txt varchar2 := null,
p_sheet pls_integer := null);
--
procedure defined_name(p_tl_col pls_integer -- top left
,
p_tl_row pls_integer,
p_br_col pls_integer -- bottom right
,
p_br_row pls_integer,
p_name varchar2,
p_sheet pls_integer := null,
p_localsheet pls_integer := null);
--
procedure set_column_width(p_col pls_integer,
p_width number,
p_sheet pls_integer := null);
--
procedure set_column(p_col pls_integer,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null);
--
procedure set_row(p_row pls_integer,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null);
--
procedure freeze_rows(p_nr_rows pls_integer := 1,
p_sheet pls_integer := null);
--
procedure freeze_cols(p_nr_cols pls_integer := 1,
p_sheet pls_integer := null);
--
procedure freeze_pane(p_col pls_integer,
p_row pls_integer,
p_sheet pls_integer := null);
--
procedure set_autofilter(p_column_start pls_integer := null,
p_column_end pls_integer := null,
p_row_start pls_integer := null,
p_row_end pls_integer := null,
p_sheet pls_integer := null);
--
function finish return blob;
--
procedure save(p_directory varchar2, p_filename varchar2);
--
---當p_footer設定參數為真，這個g_query2sheet_footer有值，則會顯示這個值的內容。沒有則默認是Generated xxxx
---有一點說明的是，&ROWS 字段會被自動替換為結果返回的行數。
g_query2sheet_footer varchar2(1000);
g_query2sheet_rows number; --結果返回的是多少行記錄
--這個是這個程序的"完全體"
procedure query2sheet(p_sql varchar2,
p_col_value_tab smt_xlsx_maker_pkg.tab_col_value ---運行的動態SQL的綁定變量
,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true,
x_retcode out number ---0:成功 非0:失敗( 或者：0:成功 1:警告 2:錯誤 ----注意：確定警告的時候要做什麼動作)
,
x_errbuf out varchar2 ---具體的錯誤信息
);
---默認用上綁定變量的邏輯！
procedure query2sheet(p_sql varchar2,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true,
x_retcode out number ---0:成功 非0:失敗( 或者：0:成功 1:警告 2:錯誤 ----注意：確定警告的時候要做什麼動作)
,
x_errbuf out varchar2 ---具體的錯誤信息
);
---最簡單的使用版本
procedure query2sheet(p_sql varchar2,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true);
---參考老外的，另外封裝的一個好用的過程！游標直接輸出excel。未測試過~
procedure cursor2sheet(p_sql in sys_refcursor,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true);
--
end;
smt_xlsx_maker_pkg 包頭部分
![]()
create or replace package body smt_xlsx_maker_pkg is
--
c_local_file_header constant raw(4) := hextoraw('504B0304'); -- Local file header signature
c_end_of_central_directory constant raw(4) := hextoraw('504B0506'); -- End of central directory signature
--
type tp_xf_fmt is record(
numfmtid pls_integer,
fontid pls_integer,
fillid pls_integer,
borderid pls_integer,
alignment tp_alignment);
type tp_col_fmts is table of tp_xf_fmt index by pls_integer;
type tp_row_fmts is table of tp_xf_fmt index by pls_integer;
type tp_widths is table of number index by pls_integer;
type tp_cell is record(
value number,
style varchar2(50));
type tp_cells is table of tp_cell index by pls_integer;
type tp_rows is table of tp_cells index by pls_integer;
type tp_autofilter is record(
column_start pls_integer,
column_end pls_integer,
row_start pls_integer,
row_end pls_integer);
type tp_autofilters is table of tp_autofilter index by pls_integer;
type tp_hyperlink is record(
cell varchar2(10),
url varchar2(1000));
type tp_hyperlinks is table of tp_hyperlink index by pls_integer;
subtype tp_author is varchar2(32767 char);
type tp_authors is table of pls_integer index by tp_author;
authors tp_authors;
type tp_comment is record(
text varchar2(32767 char),
author tp_author,
row pls_integer,
column pls_integer,
width pls_integer,
height pls_integer);
type tp_comments is table of tp_comment index by pls_integer;
type tp_mergecells is table of varchar2(21) index by pls_integer;
type tp_validation is record(
type varchar2(10),
errorstyle varchar2(32),
showinputmessage boolean,
prompt varchar2(32767 char),
title varchar2(32767 char),
error_title varchar2(32767 char),
error_txt varchar2(32767 char),
showerrormessage boolean,
formula1 varchar2(32767 char),
formula2 varchar2(32767 char),
allowblank boolean,
sqref varchar2(32767 char));
type tp_validations is table of tp_validation index by pls_integer;
type tp_sheet is record(
rows tp_rows,
widths tp_widths,
name varchar2(100),
freeze_rows pls_integer,
freeze_cols pls_integer,
autofilters tp_autofilters,
hyperlinks tp_hyperlinks,
col_fmts tp_col_fmts,
row_fmts tp_row_fmts,
comments tp_comments,
mergecells tp_mergecells,
validations tp_validations);
type tp_sheets is table of tp_sheet index by pls_integer;
type tp_numfmt is record(
numfmtid pls_integer,
formatcode varchar2(100));
type tp_numfmts is table of tp_numfmt index by pls_integer;
type tp_fill is record(
patterntype varchar2(30),
fgrgb varchar2(8));
type tp_fills is table of tp_fill index by pls_integer;
type tp_cellxfs is table of tp_xf_fmt index by pls_integer;
type tp_font is record(
name varchar2(100),
family pls_integer,
fontsize number,
theme pls_integer,
rgb varchar2(8),
underline boolean,
italic boolean,
bold boolean);
type tp_fonts is table of tp_font index by pls_integer;
type tp_border is record(
top varchar2(17),
bottom varchar2(17),
left varchar2(17),
right varchar2(17));
type tp_borders is table of tp_border index by pls_integer;
type tp_numfmtindexes is table of pls_integer index by pls_integer;
type tp_strings is table of pls_integer index by varchar2(32767 char);
type tp_str_ind is table of varchar2(32767 char) index by pls_integer;
type tp_defined_name is record(
name varchar2(32767 char),
ref varchar2(32767 char),
sheet pls_integer);
type tp_defined_names is table of tp_defined_name index by pls_integer;
type tp_book is record(
sheets tp_sheets,
strings tp_strings,
str_ind tp_str_ind,
str_cnt pls_integer := 0,
fonts tp_fonts,
fills tp_fills,
borders tp_borders,
numfmts tp_numfmts,
cellxfs tp_cellxfs,
numfmtindexes tp_numfmtindexes,
defined_names tp_defined_names);
workbook tp_book;
lc_rows tp_rows; --new 2015.5.27
--
procedure debuglog(p_msg in varchar2) is
begin
if g_debug_type = 'DBMS_OUTPUT' then
dbms_output.put_line(p_msg);
elsif g_debug_type = 'FILE_OUTPUT' then
--XYG_FND_FILE.PUT_LINE(FND_FILE.OUTPUT, P_MSG);
null;
elsif g_debug_type = 'REQUEST_OUTPUT' then
--LOG(P_MSG);
--FND_FILE.PUT_LINE (FND_FILE.LOG, P_MSG);
null;
end if;
end debuglog;
procedure blob2file(p_blob blob,
p_directory varchar2 := 'MY_DIR',
p_filename varchar2 := 'my.xlsx') is
t_fh utl_file.file_type;
t_len pls_integer := 32767;
begin
t_fh := utl_file.fopen(p_directory, p_filename, 'wb');
for i in 0 .. trunc((dbms_lob.getlength(p_blob) - 1) / t_len) loop
utl_file.put_raw(t_fh, dbms_lob.substr(p_blob, t_len, i * t_len + 1));
end loop;
utl_file.fclose(t_fh);
end;
--
function raw2num(p_raw raw, p_len integer, p_pos integer) return number is
begin
return utl_raw.cast_to_binary_integer(utl_raw.substr(p_raw,
p_pos,
p_len),
utl_raw.little_endian);
end;
--
function little_endian(p_big number, p_bytes pls_integer := 4) return raw is
begin
return utl_raw.substr(utl_raw.cast_from_binary_integer(p_big,
utl_raw.little_endian),
1,
p_bytes);
end;
--
function blob2num(p_blob blob, p_len integer, p_pos integer) return number is
begin
return utl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob,
p_len,
p_pos),
utl_raw.little_endian);
end;
--
procedure add1file(p_zipped_blob in out blob,
p_name varchar2,
p_content blob) is
t_now date;
t_blob blob;
t_len integer;
t_clen integer;
t_crc32 raw(4) := hextoraw('00000000');
t_compressed boolean := false;
t_name raw(32767);
begin
t_now := sysdate;
t_len := nvl(dbms_lob.getlength(p_content), 0);
if t_len > 0 then
t_blob := utl_compress.lz_compress(p_content);
t_clen := dbms_lob.getlength(t_blob) - 18;
t_compressed := t_clen < t_len;
t_crc32 := dbms_lob.substr(t_blob, 4, t_clen + 11);
end if;
if not t_compressed then
t_clen := t_len;
t_blob := p_content;
end if;
if p_zipped_blob is null then
dbms_lob.createtemporary(p_zipped_blob, true);
end if;
t_name := utl_i18n.string_to_raw(p_name, 'AL32UTF8');
dbms_lob.append(p_zipped_blob,
utl_raw.concat(c_local_file_header -- Local file header signature
,
hextoraw('1400') -- version 2.0
,
case when
t_name =
utl_i18n.string_to_raw(p_name, 'US8PC437') then
hextoraw('0000') -- no General purpose bits
else hextoraw('0008') -- set Language encoding flag (EFS)
end,
case when t_compressed then
hextoraw('0800') -- deflate
else hextoraw('0000') -- stored
end,
little_endian(to_number(to_char(t_now,
'ss')) / 2 +
to_number(to_char(t_now,
'mi')) * 32 +
to_number(to_char(t_now,
'hh24')) * 2048,
2) -- File last modification time
,
little_endian(to_number(to_char(t_now,
'dd')) +
to_number(to_char(t_now,
'mm')) * 32 +
(to_number(to_char(t_now,
'yyyy')) - 1980) * 512,
2) -- File last modification date
,
t_crc32 -- CRC-32
,
little_endian(t_clen) -- compressed size
,
little_endian(t_len) -- uncompressed size
,
little_endian(utl_raw.length(t_name), 2) -- File name length
,
hextoraw('0000') -- Extra field length
,
t_name -- File name
));
if t_compressed then
dbms_lob.copy(p_zipped_blob,
t_blob,
t_clen,
dbms_lob.getlength(p_zipped_blob) + 1,
11); -- compressed content
elsif t_clen > 0 then
dbms_lob.copy(p_zipped_blob,
t_blob,
t_clen,
dbms_lob.getlength(p_zipped_blob) + 1,
1); -- content
end if;
if dbms_lob.istemporary(t_blob) = 1 then
dbms_lob.freetemporary(t_blob);
end if;
end;
--
procedure finish_zip(p_zipped_blob in out blob) is
t_cnt pls_integer := 0;
t_offs integer;
t_offs_dir_header integer;
t_offs_end_header integer;
t_comment raw(32767) := utl_raw.cast_to_raw('Implementation by Anton Scheffer');
begin
t_offs_dir_header := dbms_lob.getlength(p_zipped_blob);
t_offs := 1;
while dbms_lob.substr(p_zipped_blob,
utl_raw.length(c_local_file_header),
t_offs) = c_local_file_header loop
t_cnt := t_cnt + 1;
dbms_lob.append(p_zipped_blob,
utl_raw.concat(hextoraw('504B0102') -- Central directory file header signature
,
hextoraw('1400') -- version 2.0
,
dbms_lob.substr(p_zipped_blob,
26,
t_offs + 4),
hextoraw('0000') -- File comment length
,
hextoraw('0000') -- Disk number where file starts
,
hextoraw('0000') -- Internal file attributes =>
-- 0000 binary file
-- 0100 (ascii)text file
,
case when dbms_lob.substr(p_zipped_blob,
1,
t_offs + 30 +
blob2num(p_zipped_blob,
2,
t_offs + 26) - 1) in
(hextoraw('2F') -- /
,
hextoraw('5C') -- \
) then hextoraw('10000000') -- a directory/folder
else hextoraw('2000B681') -- a file
end -- External file attributes
,
little_endian(t_offs - 1) -- Relative offset of local file header
,
dbms_lob.substr(p_zipped_blob,
blob2num(p_zipped_blob,
2,
t_offs + 26),
t_offs + 30) -- File name
));
t_offs := t_offs + 30 + blob2num(p_zipped_blob, 4, t_offs + 18) -- compressed size
+ blob2num(p_zipped_blob, 2, t_offs + 26) -- File name length
+ blob2num(p_zipped_blob, 2, t_offs + 28); -- Extra field length
end loop;
t_offs_end_header := dbms_lob.getlength(p_zipped_blob);
dbms_lob.append(p_zipped_blob,
utl_raw.concat(c_end_of_central_directory -- End of central directory signature
,
hextoraw('0000') -- Number of this disk
,
hextoraw('0000') -- Disk where central directory starts
,
little_endian(t_cnt, 2) -- Number of central directory records on this disk
,
little_endian(t_cnt, 2) -- Total number of central directory records
,
little_endian(t_offs_end_header -
t_offs_dir_header) -- Size of central directory
,
little_endian(t_offs_dir_header) -- Offset of start of central directory, relative to start of archive
,
little_endian(nvl(utl_raw.length(t_comment),
0),
2) -- ZIP file comment length
,
t_comment));
end;
--
function alfan_col(p_col pls_integer) return varchar2 is
begin
return case when p_col > 702 then chr(64 + trunc((p_col - 27) / 676)) || chr(65 +
mod(trunc((p_col - 1) / 26) - 1,
26)) || chr(65 +
mod(p_col - 1,
26)) when p_col > 26 then chr(64 +
trunc((p_col - 1) / 26)) || chr(65 +
mod(p_col - 1,
26)) else chr(64 +
p_col) end;
end;
--
function col_alfan(p_col varchar2) return pls_integer is
begin
return ascii(substr(p_col, -1)) - 64 + nvl((ascii(substr(p_col, -2, 1)) - 64) * 26,
0) + nvl((ascii(substr(p_col,
-3,
1)) - 64) * 676,
0);
end;
--
procedure clear_workbook is
--t_row_ind pls_integer;
t_clear_rows tp_rows;
begin
for s in 1 .. workbook.sheets.count() loop
/*
t_row_ind := workbook.sheets( s ).rows.first();
while t_row_ind is not null
loop
workbook.sheets( s ).rows( t_row_ind ).delete();
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
*/
workbook.sheets(s).rows := t_clear_rows;
--lc_rows := t_clear_rows;
/*
lc_rows := workbook.sheets( s ).rows;
t_row_ind := lc_rows.first();
loop
exit when t_row_ind is null;
workbook.sheets( s ).rows( t_row_ind ).delete();
lc_rows( t_row_ind ).delete();
t_row_ind := lc_rows.next( t_row_ind );
end loop;*/
--lc_rows.delete();
workbook.sheets(s).rows.delete();
workbook.sheets(s).widths.delete();
workbook.sheets(s).autofilters.delete();
workbook.sheets(s).hyperlinks.delete();
workbook.sheets(s).col_fmts.delete();
workbook.sheets(s).row_fmts.delete();
workbook.sheets(s).comments.delete();
workbook.sheets(s).mergecells.delete();
workbook.sheets(s).validations.delete();
end loop;
workbook.strings.delete();
workbook.str_ind.delete();
workbook.fonts.delete();
workbook.fills.delete();
workbook.borders.delete();
workbook.numfmts.delete();
workbook.cellxfs.delete();
workbook.defined_names.delete();
workbook := null;
g_query2sheet_footer := null;
g_query2sheet_rows := null;
end;
--
procedure new_sheet(p_sheetname varchar2 := null) is
t_nr pls_integer := workbook.sheets.count() + 1;
t_ind pls_integer;
begin
workbook.sheets(t_nr).name := nvl(dbms_xmlgen.convert(translate(p_sheetname,
'a/\[]*:?',
'a')),
'Sheet' || t_nr);
if workbook.strings.count() = 0 then
workbook.str_cnt := 0;
end if;
if workbook.fonts.count() = 0 then
t_ind := get_font('Calibri');
end if;
if workbook.fills.count() = 0 then
t_ind := get_fill('none');
t_ind := get_fill('gray125');
end if;
if workbook.borders.count() = 0 then
t_ind := get_border('', '', '', '');
end if;
end;
--
procedure set_col_width(p_sheet pls_integer,
p_col pls_integer,
p_format varchar2) is
t_width number;
t_nr_chr pls_integer;
begin
if p_format is null then
return;
end if;
if instr(p_format, ';') > 0 then
t_nr_chr := length(translate(substr(p_format,
1,
instr(p_format, ';') - 1),
'a\"',
'a'));
else
t_nr_chr := length(translate(p_format, 'a\"', 'a'));
end if;
t_width := trunc((t_nr_chr * 7 + 5) / 7 * 256) / 256; -- assume default 11 point Calibri
if workbook.sheets(p_sheet).widths.exists(p_col) then
workbook.sheets(p_sheet).widths(p_col) := greatest(workbook.sheets(p_sheet)
.widths(p_col),
t_width);
else
workbook.sheets(p_sheet).widths(p_col) := greatest(t_width, 8.43);
end if;
end;
--
function orafmt2excel(p_format varchar2 := null) return varchar2 is
t_format varchar2(1000) := substr(p_format, 1, 1000);
begin
t_format := replace(replace(t_format, 'hh24', 'hh'), 'hh12', 'hh');
t_format := replace(t_format, 'mi', 'mm');
t_format := replace(replace(replace(t_format, 'AM', '~~'), 'PM', '~~'),
'~~',
'AM/PM');
t_format := replace(replace(replace(t_format, 'am', '~~'), 'pm', '~~'),
'~~',
'AM/PM');
t_format := replace(replace(t_format, 'day', 'DAY'), 'DAY', 'dddd');
t_format := replace(replace(t_format, 'dy', 'DY'), 'DAY', 'ddd');
t_format := replace(replace(t_format, 'RR', 'RR'), 'RR', 'YY');
t_format := replace(replace(t_format, 'month', 'MONTH'),
'MONTH',
'mmmm');
t_format := replace(replace(t_format, 'mon', 'MON'), 'MON', 'mmm');
return t_format;
end;
--
function get_numfmt(p_format varchar2 := null) return pls_integer is
t_cnt pls_integer;
t_numfmtid pls_integer;
begin
if p_format is null then
return 0;
end if;
t_cnt := workbook.numfmts.count();
for i in 1 .. t_cnt loop
if workbook.numfmts(i).formatcode = p_format then
t_numfmtid := workbook.numfmts(i).numfmtid;
exit;
end if;
end loop;
if t_numfmtid is null then
t_numfmtid := case
when t_cnt = 0 then
164
else
workbook.numfmts(t_cnt).numfmtid + 1
end;
t_cnt := t_cnt + 1;
workbook.numfmts(t_cnt).numfmtid := t_numfmtid;
workbook.numfmts(t_cnt).formatcode := p_format;
workbook.numfmtindexes(t_numfmtid) := t_cnt;
end if;
return t_numfmtid;
end;
--
function get_font(p_name varchar2,
p_family pls_integer := 2,
p_fontsize number := 11,
p_theme pls_integer := 1,
p_underline boolean := false,
p_italic boolean := false,
p_bold boolean := false,
p_rgb varchar2 := null -- this is a hex ALPHA Red Green Blue value
) return pls_integer is
t_ind pls_integer;
begin
if workbook.fonts.count() > 0 then
for f in 0 .. workbook.fonts.count() - 1 loop
if (workbook.fonts(f)
.name = p_name and workbook.fonts(f).family = p_family and workbook.fonts(f)
.fontsize = p_fontsize and workbook.fonts(f).theme = p_theme and workbook.fonts(f)
.underline = p_underline and workbook.fonts(f).italic = p_italic and workbook.fonts(f)
.bold = p_bold and
(workbook.fonts(f)
.rgb = p_rgb or (workbook.fonts(f).rgb is null and p_rgb is null))) then
return f;
end if;
end loop;
end if;
t_ind := workbook.fonts.count();
workbook.fonts(t_ind).name := p_name;
workbook.fonts(t_ind).family := p_family;
workbook.fonts(t_ind).fontsize := p_fontsize;
workbook.fonts(t_ind).theme := p_theme;
workbook.fonts(t_ind).underline := p_underline;
workbook.fonts(t_ind).italic := p_italic;
workbook.fonts(t_ind).bold := p_bold;
workbook.fonts(t_ind).rgb := p_rgb;
return t_ind;
end;
--
function get_fill(p_patterntype varchar2, p_fgrgb varchar2 := null)
return pls_integer is
t_ind pls_integer;
begin
if workbook.fills.count() > 0 then
for f in 0 .. workbook.fills.count() - 1 loop
if (workbook.fills(f)
.patterntype = p_patterntype and
nvl(workbook.fills(f).fgrgb, 'x') = nvl(upper(p_fgrgb), 'x')) then
return f;
end if;
end loop;
end if;
t_ind := workbook.fills.count();
workbook.fills(t_ind).patterntype := p_patterntype;
workbook.fills(t_ind).fgrgb := upper(p_fgrgb);
return t_ind;
end;
--
function get_border(p_top varchar2 := 'thin',
p_bottom varchar2 := 'thin',
p_left varchar2 := 'thin',
p_right varchar2 := 'thin') return pls_integer is
t_ind pls_integer;
begin
if workbook.borders.count() > 0 then
for b in 0 .. workbook.borders.count() - 1 loop
if (nvl(workbook.borders(b).top, 'x') = nvl(p_top, 'x') and
nvl(workbook.borders(b).bottom, 'x') = nvl(p_bottom, 'x') and
nvl(workbook.borders(b).left, 'x') = nvl(p_left, 'x') and
nvl(workbook.borders(b).right, 'x') = nvl(p_right, 'x')) then
return b;
end if;
end loop;
end if;
t_ind := workbook.borders.count();
workbook.borders(t_ind).top := p_top;
workbook.borders(t_ind).bottom := p_bottom;
workbook.borders(t_ind).left := p_left;
workbook.borders(t_ind).right := p_right;
return t_ind;
end;
--
function get_alignment(p_vertical varchar2 := null,
p_horizontal varchar2 := null,
p_wraptext boolean := null) return tp_alignment is
t_rv tp_alignment;
begin
t_rv.vertical := p_vertical;
t_rv.horizontal := p_horizontal;
t_rv.wraptext := p_wraptext;
return t_rv;
end;
--
function get_xfid(p_sheet pls_integer,
p_col pls_integer,
p_row pls_integer,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null) return varchar2 is
t_cnt pls_integer;
t_xfid pls_integer;
t_xf tp_xf_fmt;
t_col_xf tp_xf_fmt;
t_row_xf tp_xf_fmt;
begin
if workbook.sheets(p_sheet).col_fmts.exists(p_col) then
t_col_xf := workbook.sheets(p_sheet).col_fmts(p_col);
end if;
if workbook.sheets(p_sheet).row_fmts.exists(p_row) then
t_row_xf := workbook.sheets(p_sheet).row_fmts(p_row);
end if;
t_xf.numfmtid := coalesce(p_numfmtid,
t_col_xf.numfmtid,
t_row_xf.numfmtid,
0);
t_xf.fontid := coalesce(p_fontid,
t_col_xf.fontid,
t_row_xf.fontid,
0);
t_xf.fillid := coalesce(p_fillid,
t_col_xf.fillid,
t_row_xf.fillid,
0);
t_xf.borderid := coalesce(p_borderid,
t_col_xf.borderid,
t_row_xf.borderid,
0);
t_xf.alignment := coalesce(p_alignment,
t_col_xf.alignment,
t_row_xf.alignment);
if (t_xf.numfmtid + t_xf.fontid + t_xf.fillid + t_xf.borderid = 0 and
t_xf.alignment.vertical is null and
t_xf.alignment.horizontal is null and
not nvl(t_xf.alignment.wraptext, false)) then
return '';
end if;
if t_xf.numfmtid > 0 then
set_col_width(p_sheet,
p_col,
workbook.numfmts(workbook.numfmtindexes(t_xf.numfmtid))
.formatcode);
end if;
t_cnt := workbook.cellxfs.count();
for i in 1 .. t_cnt loop
if (workbook.cellxfs(i)
.numfmtid = t_xf.numfmtid and workbook.cellxfs(i)
.fontid = t_xf.fontid and workbook.cellxfs(i).fillid = t_xf.fillid and workbook.cellxfs(i)
.borderid = t_xf.borderid and
nvl(workbook.cellxfs(i).alignment.vertical, 'x') =
nvl(t_xf.alignment.vertical, 'x') and
nvl(workbook.cellxfs(i).alignment.horizontal, 'x') =
nvl(t_xf.alignment.horizontal, 'x') and
nvl(workbook.cellxfs(i).alignment.wraptext, false) =
nvl(t_xf.alignment.wraptext, false)) then
t_xfid := i;
exit;
end if;
end loop;
if t_xfid is null then
t_cnt := t_cnt + 1;
t_xfid := t_cnt;
workbook.cellxfs(t_cnt) := t_xf;
end if;
return 's="' || t_xfid || '"';
end;
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value number,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).rows(p_row)(p_col).value := p_value;
workbook.sheets(t_sheet).rows(p_row)(p_col).style := null;
workbook.sheets(t_sheet).rows(p_row)(p_col).style := get_xfid(t_sheet,
p_col,
p_row,
p_numfmtid,
p_fontid,
p_fillid,
p_borderid,
p_alignment);
end;
--
function add_string(p_string varchar2) return pls_integer is
t_cnt pls_integer;
begin
if workbook.strings.exists(p_string) then
t_cnt := workbook.strings(p_string);
else
t_cnt := workbook.strings.count();
workbook.str_ind(t_cnt) := p_string;
workbook.strings(nvl(p_string, '')) := t_cnt;
end if;
workbook.str_cnt := workbook.str_cnt + 1;
return t_cnt;
end;
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value varchar2,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
t_alignment tp_alignment := p_alignment;
begin
workbook.sheets(t_sheet).rows(p_row)(p_col).value := add_string(p_value);
if t_alignment.wraptext is null and instr(p_value, chr(13)) > 0 then
t_alignment.wraptext := true;
end if;
workbook.sheets(t_sheet).rows(p_row)(p_col).style := 't="s" ' ||
get_xfid(t_sheet,
p_col,
p_row,
p_numfmtid,
p_fontid,
p_fillid,
p_borderid,
t_alignment);
end;
--
procedure cell(p_col pls_integer,
p_row pls_integer,
p_value date,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null) is
t_numfmtid pls_integer := p_numfmtid;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
--這裡必須要改為1900系統！否則不同的Excel之間的復制和黏貼會出問題！相差4年。
--因為Windows的Excel默認是1900系統。
--workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value - to_date('01-01-1904','DD-MM-YYYY');
workbook.sheets(t_sheet).rows(p_row)(p_col).value := p_value -
to_date('01-03-1900',
'DD-MM-YYYY') + 61;
if t_numfmtid is null and
not (workbook.sheets(t_sheet).col_fmts.exists(p_col) and workbook.sheets(t_sheet).col_fmts(p_col)
.numfmtid is not null) and
not (workbook.sheets(t_sheet).row_fmts.exists(p_row) and workbook.sheets(t_sheet).row_fmts(p_row)
.numfmtid is not null) then
t_numfmtid := get_numfmt('yyyy-mm-dd'); --dd/mm/yyyy 2015.7.1修改
end if;
workbook.sheets(t_sheet).rows(p_row)(p_col).style := get_xfid(t_sheet,
p_col,
p_row,
t_numfmtid,
p_fontid,
p_fillid,
p_borderid,
p_alignment);
end;
--
procedure hyperlink(p_col pls_integer,
p_row pls_integer,
p_url varchar2,
p_value varchar2 := null,
p_sheet pls_integer := null) is
t_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).rows(p_row)(p_col).value := add_string(nvl(p_value,
p_url));
workbook.sheets(t_sheet).rows(p_row)(p_col).style := 't="s" ' ||
get_xfid(t_sheet,
p_col,
p_row,
'',
get_font('Calibri',
p_theme => 10,
p_underline => true));
t_ind := workbook.sheets(t_sheet).hyperlinks.count() + 1;
workbook.sheets(t_sheet).hyperlinks(t_ind).cell := alfan_col(p_col) ||
p_row;
workbook.sheets(t_sheet).hyperlinks(t_ind).url := p_url;
end;
--
procedure comment(p_col pls_integer,
p_row pls_integer,
p_text varchar2,
p_author varchar2 := null,
p_width pls_integer := 150,
p_height pls_integer := 100,
p_sheet pls_integer := null) is
t_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
t_ind := workbook.sheets(t_sheet).comments.count() + 1;
workbook.sheets(t_sheet).comments(t_ind).row := p_row;
workbook.sheets(t_sheet).comments(t_ind).column := p_col;
workbook.sheets(t_sheet).comments(t_ind).text := dbms_xmlgen.convert(p_text);
workbook.sheets(t_sheet).comments(t_ind).author := dbms_xmlgen.convert(p_author);
workbook.sheets(t_sheet).comments(t_ind).width := p_width;
workbook.sheets(t_sheet).comments(t_ind).height := p_height;
end;
--
procedure mergecells(p_tl_col pls_integer -- top left
,
p_tl_row pls_integer,
p_br_col pls_integer -- bottom right
,
p_br_row pls_integer,
p_sheet pls_integer := null) is
t_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
t_ind := workbook.sheets(t_sheet).mergecells.count() + 1;
workbook.sheets(t_sheet).mergecells(t_ind) := alfan_col(p_tl_col) ||
p_tl_row || ':' ||
alfan_col(p_br_col) ||
p_br_row;
end;
--
procedure add_validation(p_type varchar2,
p_sqref varchar2,
p_style varchar2 := 'stop' -- stop, warning, information
,
p_formula1 varchar2 := null,
p_formula2 varchar2 := null,
p_title varchar2 := null,
p_prompt varchar := null,
p_show_error boolean := false,
p_error_title varchar2 := null,
p_error_txt varchar2 := null,
p_sheet pls_integer := null) is
t_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
t_ind := workbook.sheets(t_sheet).validations.count() + 1;
workbook.sheets(t_sheet).validations(t_ind).type := p_type;
workbook.sheets(t_sheet).validations(t_ind).errorstyle := p_style;
workbook.sheets(t_sheet).validations(t_ind).sqref := p_sqref;
workbook.sheets(t_sheet).validations(t_ind).formula1 := p_formula1;
workbook.sheets(t_sheet).validations(t_ind).error_title := p_error_title;
workbook.sheets(t_sheet).validations(t_ind).error_txt := p_error_txt;
workbook.sheets(t_sheet).validations(t_ind).title := p_title;
workbook.sheets(t_sheet).validations(t_ind).prompt := p_prompt;
workbook.sheets(t_sheet).validations(t_ind).showerrormessage := p_show_error;
end;
--
procedure list_validation(p_sqref_col pls_integer,
p_sqref_row pls_integer,
p_tl_col pls_integer -- top left
,
p_tl_row pls_integer,
p_br_col pls_integer -- bottom right
,
p_br_row pls_integer,
p_style varchar2 := 'stop' -- stop, warning, information
,
p_title varchar2 := null,
p_prompt varchar := null,
p_show_error boolean := false,
p_error_title varchar2 := null,
p_error_txt varchar2 := null,
p_sheet pls_integer := null) is
begin
add_validation('list',
alfan_col(p_sqref_col) || p_sqref_row,
p_style => lower(p_style),
p_formula1 => '$' || alfan_col(p_tl_col) || '$' ||
p_tl_row || ':$' || alfan_col(p_br_col) || '$' ||
p_br_row,
p_title => p_title,
p_prompt => p_prompt,
p_show_error => p_show_error,
p_error_title => p_error_title,
p_error_txt => p_error_txt,
p_sheet => p_sheet);
end;
--
procedure list_validation(p_sqref_col pls_integer,
p_sqref_row pls_integer,
p_defined_name varchar2,
p_style varchar2 := 'stop' -- stop, warning, information
,
p_title varchar2 := null,
p_prompt varchar := null,
p_show_error boolean := false,
p_error_title varchar2 := null,
p_error_txt varchar2 := null,
p_sheet pls_integer := null) is
begin
add_validation('list',
alfan_col(p_sqref_col) || p_sqref_row,
p_style => lower(p_style),
p_formula1 => p_defined_name,
p_title => p_title,
p_prompt => p_prompt,
p_show_error => p_show_error,
p_error_title => p_error_title,
p_error_txt => p_error_txt,
p_sheet => p_sheet);
end;
--
procedure defined_name(p_tl_col pls_integer -- top left
,
p_tl_row pls_integer,
p_br_col pls_integer -- bottom right
,
p_br_row pls_integer,
p_name varchar2,
p_sheet pls_integer := null,
p_localsheet pls_integer := null) is
t_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
t_ind := workbook.defined_names.count() + 1;
workbook.defined_names(t_ind).name := p_name;
workbook.defined_names(t_ind).ref := 'Sheet' || t_sheet || '!$' ||
alfan_col(p_tl_col) || '$' ||
p_tl_row || ':$' ||
alfan_col(p_br_col) || '$' ||
p_br_row;
workbook.defined_names(t_ind).sheet := p_localsheet;
end;
--
procedure set_column_width(p_col pls_integer,
p_width number,
p_sheet pls_integer := null) is
begin
workbook.sheets(nvl(p_sheet, workbook.sheets.count())).widths(p_col) := p_width;
end;
--
procedure set_column(p_col pls_integer,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).col_fmts(p_col).numfmtid := p_numfmtid;
workbook.sheets(t_sheet).col_fmts(p_col).fontid := p_fontid;
workbook.sheets(t_sheet).col_fmts(p_col).fillid := p_fillid;
workbook.sheets(t_sheet).col_fmts(p_col).borderid := p_borderid;
workbook.sheets(t_sheet).col_fmts(p_col).alignment := p_alignment;
end;
--
procedure set_row(p_row pls_integer,
p_numfmtid pls_integer := null,
p_fontid pls_integer := null,
p_fillid pls_integer := null,
p_borderid pls_integer := null,
p_alignment tp_alignment := null,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).row_fmts(p_row).numfmtid := p_numfmtid;
workbook.sheets(t_sheet).row_fmts(p_row).fontid := p_fontid;
workbook.sheets(t_sheet).row_fmts(p_row).fillid := p_fillid;
workbook.sheets(t_sheet).row_fmts(p_row).borderid := p_borderid;
workbook.sheets(t_sheet).row_fmts(p_row).alignment := p_alignment;
end;
--
procedure freeze_rows(p_nr_rows pls_integer := 1,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).freeze_cols := null;
workbook.sheets(t_sheet).freeze_rows := p_nr_rows;
end;
--
procedure freeze_cols(p_nr_cols pls_integer := 1,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).freeze_rows := null;
workbook.sheets(t_sheet).freeze_cols := p_nr_cols;
end;
--
procedure freeze_pane(p_col pls_integer,
p_row pls_integer,
p_sheet pls_integer := null) is
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
workbook.sheets(t_sheet).freeze_rows := p_row;
workbook.sheets(t_sheet).freeze_cols := p_col;
end;
--
procedure set_autofilter(p_column_start pls_integer := null,
p_column_end pls_integer := null,
p_row_start pls_integer := null,
p_row_end pls_integer := null,
p_sheet pls_integer := null) is
t_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
begin
t_ind := 1;
workbook.sheets(t_sheet).autofilters(t_ind).column_start := p_column_start;
workbook.sheets(t_sheet).autofilters(t_ind).column_end := p_column_end;
workbook.sheets(t_sheet).autofilters(t_ind).row_start := p_row_start;
workbook.sheets(t_sheet).autofilters(t_ind).row_end := p_row_end;
defined_name(p_column_start,
p_row_start,
p_column_end,
p_row_end,
'_xlnm._FilterDatabase',
t_sheet,
t_sheet - 1);
end;
--
/*
procedure add1xml
( p_excel in out nocopy blob
, p_filename varchar2
, p_xml clob
)
is
t_tmp blob;
c_step constant number := 24396;
begin
dbms_lob.createtemporary( t_tmp, true );
for i in 0 .. trunc( length( p_xml ) / c_step )
loop
dbms_lob.append( t_tmp, utl_i18n.string_to_raw( substr( p_xml, i * c_step + 1, c_step ), 'AL32UTF8' ) );
end loop;
add1file( p_excel, p_filename, t_tmp );
dbms_lob.freetemporary( t_tmp );
end;
*/
--
procedure add1xml(p_excel in out nocopy blob,
p_filename varchar2,
p_xml clob) is
t_tmp blob;
dest_offset integer := 1;
src_offset integer := 1;
lang_context integer;
warning integer;
begin
lang_context := dbms_lob.default_lang_ctx;
dbms_lob.createtemporary(t_tmp, true);
dbms_lob.converttoblob(t_tmp,
p_xml,
dbms_lob.lobmaxsize,
dest_offset,
src_offset,
nls_charset_id('AL32UTF8'),
lang_context,
warning);
add1file(p_excel, p_filename, t_tmp);
dbms_lob.freetemporary(t_tmp);
end;
--
function finish return blob is
t_excel blob;
t_xxx clob;
t_tmp varchar2(32767 char);
--t_tmp clob;
t_str varchar2(32767 char);
t_c number;
t_h number;
t_w number;
t_cw number;
t_cell varchar2(1000 char);
t_row_ind pls_integer;
t_col_min pls_integer;
t_col_max pls_integer;
t_col_ind pls_integer;
t_len pls_integer;
ts timestamp := systimestamp;
---
l_debug_time date;
begin
if g_debug_mode then
debuglog('finish(+)');
l_debug_time := sysdate;
end if;
dbms_lob.createtemporary(t_excel, true);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '
<Override PartName="/xl/worksheets/sheet' || s ||
'.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
end loop;
t_xxx := t_xxx || '
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
for s in 1 .. workbook.sheets.count() loop
if workbook.sheets(s).comments.count() > 0 then
t_xxx := t_xxx || '
<Override PartName="/xl/comments' || s ||
'.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"/>';
end if;
end loop;
t_xxx := t_xxx || '
</Types>';
add1xml(t_excel, '[Content_Types].xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:creator>' || sys_context('userenv', 'os_user') ||
'</dc:creator>
<cp:lastModifiedBy>' || sys_context('userenv', 'os_user') ||
'</cp:lastModifiedBy>
<dcterms:created xsi:type="dcterms:W3CDTF">' ||
to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') ||
'</dcterms:created>
<dcterms:modified xsi:type="dcterms:W3CDTF">' ||
to_char(current_timestamp, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') ||
'</dcterms:modified>
</cp:coreProperties>';
add1xml(t_excel, 'docProps/core.xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>Worksheets</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>' || workbook.sheets.count() || '</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="' || workbook.sheets.count() ||
'" baseType="lpstr">';
for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '
<vt:lpstr>' || workbook.sheets(s).name || '</vt:lpstr>';
end loop;
t_xxx := t_xxx || '</vt:vector>
</TitlesOfParts>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>14.0300</AppVersion>
</Properties>';
add1xml(t_excel, 'docProps/app.xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>';
add1xml(t_excel, '_rels/.rels', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">';
if workbook.numfmts.count() > 0 then
t_xxx := t_xxx || '<numFmts count="' || workbook.numfmts.count() || '">';
for n in 1 .. workbook.numfmts.count() loop
t_xxx := t_xxx || '<numFmt numFmtId="' || workbook.numfmts(n)
.numfmtid || '" formatCode="' || workbook.numfmts(n)
.formatcode || '"/>';
end loop;
t_xxx := t_xxx || '</numFmts>';
end if;
t_xxx := t_xxx || '<fonts count="' || workbook.fonts.count() ||
'" x14ac:knownFonts="1">';
for f in 0 .. workbook.fonts.count() - 1 loop
t_xxx := t_xxx || '<font>' || case
when workbook.fonts(f).bold then
'<b/>'
end || case
when workbook.fonts(f).italic then
'<i/>'
end || case
when workbook.fonts(f).underline then
'<u/>'
end || '<sz val="' ||
to_char(workbook.fonts(f).fontsize,
'TM9',
'NLS_NUMERIC_CHARACTERS=.,') || '"/>
<color ' || case
when workbook.fonts(f).rgb is not null then
'rgb="' || workbook.fonts(f).rgb
else
'theme="' || workbook.fonts(f).theme
end || '"/>
<name val="' || workbook.fonts(f).name || '"/>
<family val="' || workbook.fonts(f).family || '"/>
<scheme val="none"/>
</font>';
end loop;
t_xxx := t_xxx || '</fonts>
<fills count="' || workbook.fills.count() || '">';
for f in 0 .. workbook.fills.count() - 1 loop
t_xxx := t_xxx || '<fill><patternFill patternType="' || workbook.fills(f)
.patterntype || '">' || case
when workbook.fills(f).fgrgb is not null then
'<fgColor rgb="' || workbook.fills(f).fgrgb || '"/>'
end || '</patternFill></fill>';
end loop;
t_xxx := t_xxx || '</fills>
<borders count="' || workbook.borders.count() || '">';
for b in 0 .. workbook.borders.count() - 1 loop
t_xxx := t_xxx || '<border>' || case
when workbook.borders(b).left is null then
'<left/>'
else
'<left>' || workbook.borders(b).left || '"/>'
end || case
when workbook.borders(b).right is null then
'<right/>'
else
'<right>' || workbook.borders(b).right || '"/>'
end || case
when workbook.borders(b).top is null then
'<top/>'
else
'<top>' || workbook.borders(b).top || '"/>'
end || case
when workbook.borders(b).bottom is null then
'<bottom/>'
else
'<bottom>' || 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}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
<x14:slicerStyles defaultSlicer/>
</ext>
</extLst>
</styleSheet>';
add1xml(t_excel, 'xl/styles.xml', t_xxx);
--<workbookPr date1904="true" defaultThemeVersion="124226"/>
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9302"/>
<workbookPr date1904="false" defaultThemeVersion="124226"/>
<bookViews>
<workbookView xWindow="120" yWindow="45" windowWidth="19155" windowHeight="4935"/>
</bookViews>
<sheets>';
for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '
<sheet name="' || workbook.sheets(s).name || '" sheetId="' || s ||
'" r:id="rId' || (9 + s) || '"/>';
end loop;
t_xxx := t_xxx || '</sheets>';
if workbook.defined_names.count() > 0 then
t_xxx := t_xxx || '<definedNames>';
for s in 1 .. workbook.defined_names.count() loop
t_xxx := t_xxx || '
<definedName name="' || workbook.defined_names(s).name || '"' || case
when workbook.defined_names(s).sheet is not null then
' localSheetId="' || to_char(workbook.defined_names(s).sheet) || '"'
end || '>' || workbook.defined_names(s).ref ||
'</definedName>';
end loop;
t_xxx := t_xxx || '</definedNames>';
end if;
t_xxx := t_xxx || '<calcPr calcId="144525"/></workbook>';
add1xml(t_excel, 'xl/workbook.xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme">
<a:themeElements>
<a:clrScheme name="Office">
<a:dk1>
<a:sysClr val="windowText" lastClr="000000"/>
</a:dk1>
<a:lt1>
<a:sysClr val="window" lastClr="FFFFFF"/>
</a:lt1>
<a:dk2>
<a:srgbClr val="1F497D"/>
</a:dk2>
<a:lt2>
<a:srgbClr val="EEECE1"/>
</a:lt2>
<a:accent1>
<a:srgbClr val="4F81BD"/>
</a:accent1>
<a:accent2>
<a:srgbClr val="C0504D"/>
</a:accent2>
<a:accent3>
<a:srgbClr val="9BBB59"/>
</a:accent3>
<a:accent4>
<a:srgbClr val="8064A2"/>
</a:accent4>
<a:accent5>
<a:srgbClr val="4BACC6"/>
</a:accent5>
<a:accent6>
<a:srgbClr val="F79646"/>
</a:accent6>
<a:hlink>
<a:srgbClr val="0000FF"/>
</a:hlink>
<a:folHlink>
<a:srgbClr val="800080"/>
</a:folHlink>
</a:clrScheme>
<a:fontScheme name="Office">
<a:majorFont>
<a:latin typeface="Cambria"/>
<a:ea typeface=""/>
<a:cs typeface=""/>
<a:font script="Jpan" typeface="MS P????"/>
<a:font script="Hang" typeface="?? ??"/>
<a:font script="Hans" typeface="??"/>
<a:font script="Hant" typeface="????"/>
<a:font script="Arab" typeface="Times New Roman"/>
<a:font script="Hebr" typeface="Times New Roman"/>
<a:font script="Thai" typeface="Tahoma"/>
<a:font script="Ethi" typeface="Nyala"/>
<a:font script="Beng" typeface="Vrinda"/>
<a:font script="Gujr" typeface="Shruti"/>
<a:font script="Khmr" typeface="MoolBoran"/>
<a:font script="Knda" typeface="Tunga"/>
<a:font script="Guru" typeface="Raavi"/>
<a:font script="Cans" typeface="Euphemia"/>
<a:font script="Cher" typeface="Plantagenet Cherokee"/>
<a:font script="Yiii" typeface="Microsoft Yi Baiti"/>
<a:font script="Tibt" typeface="Microsoft Himalaya"/>
<a:font script="Thaa" typeface="MV Boli"/>
<a:font script="Deva" typeface="Mangal"/>
<a:font script="Telu" typeface="Gautami"/>
<a:font script="Taml" typeface="Latha"/>
<a:font script="Syrc" typeface="Estrangelo Edessa"/>
<a:font script="Orya" typeface="Kalinga"/>
<a:font script="Mlym" typeface="Kartika"/>
<a:font script="Laoo" typeface="DokChampa"/>
<a:font script="Sinh" typeface="Iskoola Pota"/>
<a:font script="Mong" typeface="Mongolian Baiti"/>
<a:font script="Viet" typeface="Times New Roman"/>
<a:font script="Uigh" typeface="Microsoft Uighur"/>
<a:font script="Geor" typeface="Sylfaen"/>
</a:majorFont>
<a:minorFont>
<a:latin typeface="Calibri"/>
<a:ea typeface=""/>
<a:cs typeface=""/>
<a:font script="Jpan" typeface="MS P????"/>
<a:font script="Hang" typeface="?? ??"/>
<a:font script="Hans" typeface="??"/>
<a:font script="Hant" typeface="????"/>
<a:font script="Arab" typeface="Arial"/>
<a:font script="Hebr" typeface="Arial"/>
<a:font script="Thai" typeface="Tahoma"/>
<a:font script="Ethi" typeface="Nyala"/>
<a:font script="Beng" typeface="Vrinda"/>
<a:font script="Gujr" typeface="Shruti"/>
<a:font script="Khmr" typeface="DaunPenh"/>
<a:font script="Knda" typeface="Tunga"/>
<a:font script="Guru" typeface="Raavi"/>
<a:font script="Cans" typeface="Euphemia"/>
<a:font script="Cher" typeface="Plantagenet Cherokee"/>
<a:font script="Yiii" typeface="Microsoft Yi Baiti"/>
<a:font script="Tibt" typeface="Microsoft Himalaya"/>
<a:font script="Thaa" typeface="MV Boli"/>
<a:font script="Deva" typeface="Mangal"/>
<a:font script="Telu" typeface="Gautami"/>
<a:font script="Taml" typeface="Latha"/>
<a:font script="Syrc" typeface="Estrangelo Edessa"/>
<a:font script="Orya" typeface="Kalinga"/>
<a:font script="Mlym" typeface="Kartika"/>
<a:font script="Laoo" typeface="DokChampa"/>
<a:font script="Sinh" typeface="Iskoola Pota"/>
<a:font script="Mong" typeface="Mongolian Baiti"/>
<a:font script="Viet" typeface="Arial"/>
<a:font script="Uigh" typeface="Microsoft Uighur"/>
<a:font script="Geor" typeface="Sylfaen"/>
</a:minorFont>
</a:fontScheme>
<a:fmtScheme name="Office">
<a:fillStyleLst>
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="50000"/>
<a:satMod val="300000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="35000">
<a:schemeClr val="phClr">
<a:tint val="37000"/>
<a:satMod val="300000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:tint val="15000"/>
<a:satMod val="350000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="16200000" scaled="1"/>
</a:gradFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:shade val="51000"/>
<a:satMod val="130000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="80000">
<a:schemeClr val="phClr">
<a:shade val="93000"/>
<a:satMod val="130000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="94000"/>
<a:satMod val="135000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="16200000" scaled="0"/>
</a:gradFill>
</a:fillStyleLst>
<a:lnStyleLst>
<a:ln w="9525" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr">
<a:shade val="95000"/>
<a:satMod val="105000"/>
</a:schemeClr>
</a:solidFill>
<a:prstDash val="solid"/>
</a:ln>
<a:ln w="25400" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:prstDash val="solid"/>
</a:ln>
<a:ln w="38100" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:prstDash val="solid"/>
</a:ln>
</a:lnStyleLst>
<a:effectStyleLst>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="38000"/>
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
</a:effectStyle>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="35000"/>
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
</a:effectStyle>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="35000"/>
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
<a:scene3d>
<a:camera prst="orthographicFront">
<a:rot lat="0" lon="0" rev="0"/>
</a:camera>
<a:lightRig rig="threePt" dir="t">
<a:rot lat="0" lon="0" rev="1200000"/>
</a:lightRig>
</a:scene3d>
<a:sp3d>
<a:bevelT w="63500" h="25400"/>
</a:sp3d>
</a:effectStyle>
</a:effectStyleLst>
<a:bgFillStyleLst>
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="40000"/>
<a:satMod val="350000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="40000">
<a:schemeClr val="phClr">
<a:tint val="45000"/>
<a:shade val="99000"/>
<a:satMod val="350000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="20000"/>
<a:satMod val="255000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:path path="circle">
<a:fillToRect l="50000" t="-80000" r="50000" b="180000"/>
</a:path>
</a:gradFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="80000"/>
<a:satMod val="300000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="30000"/>
<a:satMod val="200000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:path path="circle">
<a:fillToRect l="50000" t="50000" r="50000" b="50000"/>
</a:path>
</a:gradFill>
</a:bgFillStyleLst>
</a:fmtScheme>
</a:themeElements>
<a:objectDefaults/>
<a:extraClrSchemeLst/>
</a:theme>';
add1xml(t_excel, 'xl/theme/theme1.xml', t_xxx);
if g_debug_mode then
debuglog('Stp1 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time := sysdate;
end if;
for s in 1 .. workbook.sheets.count() loop
t_col_min := 16384;
t_col_max := 1;
---CHANGE 2015.5.27 BY SAM.T
/*
t_row_ind := workbook.sheets( s ).rows.first();
while t_row_ind is not null
loop
t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
*/
lc_rows := workbook.sheets(s).rows;
t_row_ind := lc_rows.first();
loop
exit when t_row_ind is null;
t_col_min := least(t_col_min, lc_rows(t_row_ind).first());
t_col_max := greatest(t_col_max, lc_rows(t_row_ind).last());
t_row_ind := lc_rows.next(t_row_ind);
end loop;
---
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<dimension ref="' || alfan_col(t_col_min) || workbook.sheets(s)
.rows.first() || ':' || alfan_col(t_col_max) || workbook.sheets(s)
.rows.last() || '"/>
<sheetViews>
<sheetView' || case
when s = 1 then
' tabSelected="1"'
end || ' workbookViewId="0">';
if workbook.sheets(s)
.freeze_rows > 0 and workbook.sheets(s).freeze_cols > 0 then
t_xxx := t_xxx || ('<pane xSplit="' || workbook.sheets(s)
.freeze_cols || '" ' || 'ySplit="' || workbook.sheets(s)
.freeze_rows || '" ' || 'topLeftCell="' ||
alfan_col(workbook.sheets(s).freeze_cols + 1) ||
(workbook.sheets(s).freeze_rows + 1) || '" ' ||
'activePane="bottomLeft" state="frozen"/>');
else
if workbook.sheets(s).freeze_rows > 0 then
t_xxx := t_xxx || '<pane ySplit="' || workbook.sheets(s)
.freeze_rows || '" topLeftCell="A' ||
(workbook.sheets(s).freeze_rows + 1) ||
'" activePane="bottomLeft" state="frozen"/>';
end if;
if workbook.sheets(s).freeze_cols > 0 then
t_xxx := t_xxx || '<pane xSplit="' || workbook.sheets(s)
.freeze_cols || '" topLeftCell="' ||
alfan_col(workbook.sheets(s).freeze_cols + 1) ||
'1" activePane="bottomLeft" state="frozen"/>';
end if;
end if;
t_xxx := t_xxx ||
'</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>';
if workbook.sheets(s).widths.count() > 0 then
t_xxx := t_xxx || '<cols>';
t_col_ind := workbook.sheets(s).widths.first();
while t_col_ind is not null loop
t_xxx := t_xxx || '<col min="' || t_col_ind || '" max="' ||
t_col_ind || '" width="' ||
to_char(workbook.sheets(s).widths(t_col_ind),
'TM9',
'NLS_NUMERIC_CHARACTERS=.,') ||
'" customWidth="1"/>';
t_col_ind := workbook.sheets(s).widths.next(t_col_ind);
end loop;
t_xxx := t_xxx || '</cols>';
end if;
t_xxx := t_xxx || '<sheetData>';
---CHANGE 2015.5.27 BY SAM.T
/*
t_row_ind := workbook.sheets( s ).rows.first();
t_tmp := null;
while t_row_ind is not null
loop
t_tmp := t_tmp || '<row r="' || t_row_ind || '" spans="' || t_col_min || ':' || t_col_max || '">';
t_len := length( t_tmp );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).first();
while t_col_ind is not null
loop
t_cell := '<c r="' || alfan_col( t_col_ind ) || t_row_ind || '"'
|| ' ' || workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).style
|| '><v>'
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' )
|| '</v></c>';
if t_len > 32000
then
dbms_lob.writeappend( t_xxx, t_len, t_tmp );
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length( t_cell );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
end loop;
t_tmp := t_tmp || '</row>';
t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
end loop;
*/
t_row_ind := lc_rows.first();
t_tmp := null;
while t_row_ind is not null loop
t_tmp := t_tmp || '<row r="' || t_row_ind || '" spans="' ||
t_col_min || ':' || t_col_max || '">';
t_len := length(t_tmp);
t_col_ind := lc_rows(t_row_ind).first();
while t_col_ind is not null loop
t_cell := '<c r="' || alfan_col(t_col_ind) || t_row_ind || '"' || ' ' || workbook.sheets(s)
.rows(t_row_ind)(t_col_ind)
.style || '><v>' ||
to_char(lc_rows(t_row_ind)(t_col_ind).value,
'TM9',
'NLS_NUMERIC_CHARACTERS=.,') || q'[</v></c>]';
if t_len > 32000 then
--modified by jinzhao writeappend->append
--dbms_lob.writeappend( t_xxx, t_len, t_tmp );
dbms_lob.append(t_xxx, t_tmp);
t_tmp := null;
t_len := 0;
end if;
t_tmp := t_tmp || t_cell;
t_len := t_len + length(t_cell);
t_col_ind := lc_rows(t_row_ind).next(t_col_ind);
end loop;
t_tmp := t_tmp || '</row>';
t_row_ind := lc_rows.next(t_row_ind);
end loop;
------------
t_tmp := t_tmp || '</sheetData>';
t_len := length(t_tmp);
--modified by jinzhao writeappend->append
--dbms_lob.writeappend( t_xxx, t_len, t_tmp );
dbms_lob.append(t_xxx, t_tmp);
for a in 1 .. workbook.sheets(s).autofilters.count() loop
t_xxx := t_xxx || '<autoFilter ref="' ||
alfan_col(nvl(workbook.sheets(s).autofilters(a)
.column_start,
t_col_min)) ||
nvl(workbook.sheets(s).autofilters(a).row_start,
workbook.sheets(s).rows.first()) || ':' ||
alfan_col(coalesce(workbook.sheets(s).autofilters(a)
.column_end,
workbook.sheets(s).autofilters(a)
.column_start,
t_col_max)) ||
nvl(workbook.sheets(s).autofilters(a).row_end,
workbook.sheets(s).rows.last()) || '"/>';
end loop;
if workbook.sheets(s).mergecells.count() > 0 then
t_xxx := t_xxx || '<mergeCells count="' ||
to_char(workbook.sheets(s).mergecells.count()) || '">';
for m in 1 .. workbook.sheets(s).mergecells.count() loop
t_xxx := t_xxx || '<mergeCell ref="' || workbook.sheets(s)
.mergecells(m) || '"/>';
end loop;
t_xxx := t_xxx || '</mergeCells>';
end if;
--
if workbook.sheets(s).validations.count() > 0 then
t_xxx := t_xxx || '<dataValidations count="' ||
to_char(workbook.sheets(s).validations.count()) || '">';
for m in 1 .. workbook.sheets(s).validations.count() loop
t_xxx := t_xxx || '<dataValidation' || ' type="' || workbook.sheets(s).validations(m).type || '"' ||
' errorStyle="' || workbook.sheets(s).validations(m)
.errorstyle || '"' || ' allowBlank="' || case
when nvl(workbook.sheets(s).validations(m).allowblank, true) then
'1'
else
'0'
end || '"' || ' sqref="' || workbook.sheets(s).validations(m)
.sqref || '"';
if workbook.sheets(s).validations(m).prompt is not null then
t_xxx := t_xxx || ' showInputMessage="1" prompt="' || workbook.sheets(s).validations(m)
.prompt || '"';
if workbook.sheets(s).validations(m).title is not null then
t_xxx := t_xxx || ' promptTitle="' || workbook.sheets(s).validations(m)
.title || '"';
end if;
end if;
if workbook.sheets(s).validations(m).showerrormessage then
t_xxx := t_xxx || ' showErrorMessage="1"';
if workbook.sheets(s).validations(m).error_title is not null then
t_xxx := t_xxx || ' errorTitle="' || workbook.sheets(s).validations(m)
.error_title || '"';
end if;
if workbook.sheets(s).validations(m).error_txt is not null then
t_xxx := t_xxx || ' error="' || workbook.sheets(s).validations(m)
.error_txt || '"';
end if;
end if;
t_xxx := t_xxx || '>';
if workbook.sheets(s).validations(m).formula1 is not null then
t_xxx := t_xxx || '<formula1>' || workbook.sheets(s).validations(m)
.formula1 || '</formula1>';
end if;
if workbook.sheets(s).validations(m).formula2 is not null then
t_xxx := t_xxx || '<formula2>' || workbook.sheets(s).validations(m)
.formula2 || '</formula2>';
end if;
t_xxx := t_xxx || '</dataValidation>';
end loop;
t_xxx := t_xxx || '</dataValidations>';
end if;
--
if workbook.sheets(s).hyperlinks.count() > 0 then
t_xxx := t_xxx || '<hyperlinks>';
for h in 1 .. workbook.sheets(s).hyperlinks.count() loop
t_xxx := t_xxx || '<hyperlink ref="' || workbook.sheets(s).hyperlinks(h).cell ||
'" r:id="rId' || h || '"/>';
end loop;
t_xxx := t_xxx || '</hyperlinks>';
end if;
t_xxx := t_xxx ||
'<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>';
if workbook.sheets(s).comments.count() > 0 then
t_xxx := t_xxx || '<legacyDrawing r:id="rId' ||
(workbook.sheets(s).hyperlinks.count() + 1) || '"/>';
end if;
--
t_xxx := t_xxx || '</worksheet>';
add1xml(t_excel, 'xl/worksheets/sheet' || s || '.xml', t_xxx);
if workbook.sheets(s)
.hyperlinks.count() > 0 or workbook.sheets(s).comments.count() > 0 then
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
if workbook.sheets(s).comments.count() > 0 then
t_xxx := t_xxx || '<Relationship Id="rId' ||
(workbook.sheets(s).hyperlinks.count() + 2) ||
'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments" Target="../comments' || s ||
'.xml"/>';
t_xxx := t_xxx || '<Relationship Id="rId' ||
(workbook.sheets(s).hyperlinks.count() + 1) ||
'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing' || s ||
'.vml"/>';
end if;
for h in 1 .. workbook.sheets(s).hyperlinks.count() loop
t_xxx := t_xxx || '<Relationship Id="rId' || h ||
'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="' || workbook.sheets(s).hyperlinks(h).url ||
'" TargetMode="External"/>';
end loop;
t_xxx := t_xxx || '</Relationships>';
add1xml(t_excel,
'xl/worksheets/_rels/sheet' || s || '.xml.rels',
t_xxx);
end if;
--
if workbook.sheets(s).comments.count() > 0 then
declare
cnt pls_integer;
author_ind tp_author;
-- t_col_ind := workbook.sheets( s ).widths.next( t_col_ind );
begin
authors.delete();
for c in 1 .. workbook.sheets(s).comments.count() loop
authors(workbook.sheets(s).comments(c).author) := 0;
end loop;
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<authors>';
cnt := 0;
author_ind := authors.first();
while author_ind is not null or
authors.next(author_ind) is not null loop
authors(author_ind) := cnt;
t_xxx := t_xxx || '<author>' || author_ind || '</author>';
cnt := cnt + 1;
author_ind := authors.next(author_ind);
end loop;
end;
t_xxx := t_xxx || '</authors><commentList>';
for c in 1 .. workbook.sheets(s).comments.count() loop
t_xxx := t_xxx || '<comment ref="' ||
alfan_col(workbook.sheets(s).comments(c).column) ||
to_char(workbook.sheets(s).comments(c)
.row || '" authorId="' ||
authors(workbook.sheets(s).comments(c).author)) || '">
<text>';
if workbook.sheets(s).comments(c).author is not null then
t_xxx := t_xxx ||
'<r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">' || workbook.sheets(s).comments(c)
.author || ':</t></r>';
end if;
t_xxx := t_xxx ||
'<r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">' || case
when workbook.sheets(s).comments(c).author is not null then
'
'
end || workbook.sheets(s).comments(c).text ||
'</t></r></text></comment>';
end loop;
t_xxx := t_xxx || '</commentList></comments>';
add1xml(t_excel, 'xl/comments' || s || '.xml', t_xxx);
t_xxx := '<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
<o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="2"/></o:shapelayout>
<v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe"><v:stroke join/><v:path gradientshapeok="t" o:connecttype="rect"/></v:shapetype>';
for c in 1 .. workbook.sheets(s).comments.count() loop
t_xxx := t_xxx || '<v:shape id="_x0000_s' || to_char(c) ||
'" type="#_x0000_t202"
style="position:absolute;margin-left:35.25pt;margin-top:3pt;z-index:' ||
to_char(c) ||
';visibility:hidden;" fillcolor="#ffffe1" o:insetmode="auto">
<v:fill color2="#ffffe1"/><v:shadow on="t" color="black" obscured="t"/><v:path o:connecttype="none"/>
<v:textbox>';
t_w := workbook.sheets(s).comments(c).width;
t_c := 1;
loop
if workbook.sheets(s)
.widths.exists(workbook.sheets(s).comments(c).column + t_c) then
t_cw := 256 * workbook.sheets(s)
.widths(workbook.sheets(s).comments(c).column + t_c);
t_cw := trunc((t_cw + 18) / 256 * 7); -- assume default 11 point Calibri
else
t_cw := 64;
end if;
exit when t_w < t_cw;
t_c := t_c + 1;
t_w := t_w - t_cw;
end loop;
t_h := workbook.sheets(s).comments(c).height;
t_xxx := t_xxx || to_char('<x:Anchor>' || workbook.sheets(s).comments(c)
.column || ',15,' || workbook.sheets(s).comments(c).row ||
',30,' || (workbook.sheets(s).comments(c)
.column + t_c - 1) || ',' || round(t_w) || ',' ||
(workbook.sheets(s).comments(c)
.row + 1 + trunc(t_h / 20)) || ',' ||
mod(t_h, 20) || '</x:Anchor>');
t_xxx := t_xxx ||
to_char('<x:AutoFill>False</x:AutoFill><x:Row>' ||
(workbook.sheets(s).comments(c).row - 1) ||
'</x:Row><x:Column>' ||
(workbook.sheets(s).comments(c).column - 1) ||
'</x:Column></x:ClientData></v:shape>');
end loop;
t_xxx := t_xxx || '</xml>';
add1xml(t_excel, 'xl/drawings/vmlDrawing' || s || '.vml', t_xxx);
end if;
--
end loop;
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>';
for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '
<Relationship Id="rId' || (9 + s) ||
'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' || s ||
'.xml"/>';
end loop;
t_xxx := t_xxx || '</Relationships>';
add1xml(t_excel, 'xl/_rels/workbook.xml.rels', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' ||
workbook.str_cnt || '" uniqueCount="' ||
workbook.strings.count() || '">';
t_tmp := null;
for i in 0 .. workbook.str_ind.count() - 1 loop
t_str := '<si><t>' ||
dbms_xmlgen.convert(substr(workbook.str_ind(i), 1, 32000)) ||
'</t></si>';
if lengthb(t_tmp) + lengthb(t_str) > 32000 then
t_xxx := t_xxx || t_tmp;
t_tmp := null;
end if;
t_tmp := t_tmp || t_str;
end loop;
t_xxx := t_xxx || t_tmp || '</sst>';
add1xml(t_excel, 'xl/sharedStrings.xml', t_xxx);
if g_debug_mode then
debuglog('Stp2 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time := sysdate;
end if;
finish_zip(t_excel);
if g_debug_mode then
debuglog('Stp3 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time := sysdate;
end if;
clear_workbook;
if g_debug_mode then
debuglog('Stp4 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time := sysdate;
debuglog('finish(-)');
end if;
return t_excel;
end;
--
procedure save(p_directory varchar2, p_filename varchar2) is
begin
blob2file(finish, p_directory, p_filename);
end;
--
procedure query2sheet(p_sql varchar2,
p_col_value_tab smt_xlsx_maker_pkg.tab_col_value ---運行的動態SQL的綁定變量
,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true,
x_retcode out number ---0:成功 非0:失敗( 或者：0:成功 1:警告 2:錯誤 ----注意：確定警告的時候要做什麼動作)
,
x_errbuf out varchar2 ---具體的錯誤信息
) is
l_process_phase number; --標識程序的進度
t_sheet pls_integer;
t_c integer;
t_col_cnt integer;
t_desc_tab dbms_sql.desc_tab2;
d_tab dbms_sql.date_table;
n_tab dbms_sql.number_table;
v_tab dbms_sql.varchar2_table;
t_bulk_size pls_integer := 200;
t_r integer;
t_cur_row pls_integer;
---
l_debug_time date;
begin
x_retcode := 0;
x_errbuf := null;
l_process_phase := 0;
if g_debug_mode then
debuglog('query2sheet(+)');
l_debug_time := sysdate;
end if;
if p_sql is null then
x_errbuf := '調用query2sheet的時候，必要的參數不存在，請檢查參數!';
x_retcode := 2;
return;
end if;
if p_sheet is null then
new_sheet;
end if;
l_process_phase := 1;
t_c := dbms_sql.open_cursor;
dbms_sql.parse(t_c, p_sql, dbms_sql.native);
l_process_phase := 2;
if p_col_value_tab.count > 0 then
for i in 1 .. p_col_value_tab.last loop
if p_col_value_tab.exists(i) then
dbms_sql.bind_variable(t_c,
':' || i,
p_col_value_tab(i).col_value);
if g_debug_mode then
debuglog('綁定變量--' || i || ':' || p_col_value_tab(i).col_value);
end if;
end if;
end loop;
end if;
l_process_phase := 3;
dbms_sql.describe_columns2(t_c, t_col_cnt, t_desc_tab);
for c in 1 .. t_col_cnt loop
if p_column_headers then
cell(c,
1,
t_desc_tab(c).col_name,
p_fontid => get_font('Calibri', p_bold => true),
p_sheet => t_sheet,
p_fillid => get_fill('solid', 'cccccc'));
end if;
-- dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
case
when t_desc_tab(c).col_type in (2, 100, 101) then
dbms_sql.define_array(t_c, c, n_tab, t_bulk_size, 1);
when t_desc_tab(c).col_type in (12, 178, 179, 180, 181, 231) then
dbms_sql.define_array(t_c, c, d_tab, t_bulk_size, 1);
when t_desc_tab(c).col_type in (1, 8, 9, 96, 112) then
dbms_sql.define_array(t_c, c, v_tab, t_bulk_size, 1);
else
null;
end case;
end loop;
l_process_phase := 4;
--
t_cur_row := case
when p_column_headers then
2
else
1
end;
t_sheet := nvl(p_sheet, workbook.sheets.count());
l_process_phase := 5;
--
t_r := dbms_sql.execute(t_c);
loop
t_r := dbms_sql.fetch_rows(t_c);
if t_r > 0 then
for c in 1 .. t_col_cnt loop
case
when t_desc_tab(c).col_type in (2, 100, 101) then
dbms_sql.column_value(t_c, c, n_tab);
for i in 0 .. t_r - 1 loop
if n_tab(i + n_tab.first()) is not null then
cell(c,
t_cur_row + i,
n_tab(i + n_tab.first()),
p_sheet => t_sheet);
end if;
end loop;
n_tab.delete;
when t_desc_tab(c).col_type in (12, 178, 179, 180, 181, 231) then
dbms_sql.column_value(t_c, c, d_tab);
for i in 0 .. t_r - 1 loop
if d_tab(i + d_tab.first()) is not null then
cell(c,
t_cur_row + i,
d_tab(i + d_tab.first()),
p_sheet => t_sheet);
end if;
end loop;
d_tab.delete;
when t_desc_tab(c).col_type in (1, 8, 9, 96, 112) then
dbms_sql.column_value(t_c, c, v_tab);
for i in 0 .. t_r - 1 loop
if v_tab(i + v_tab.first()) is not null then
cell(c,
t_cur_row + i,
v_tab(i + v_tab.first()),
p_sheet => t_sheet);
end if;
end loop;
v_tab.delete;
else
null;
end case;
end loop;
end if;
if t_r != t_bulk_size then
t_cur_row := t_cur_row + t_r;
end if;
exit when t_r != t_bulk_size;
t_cur_row := t_cur_row + t_r;
end loop;
g_query2sheet_rows := t_cur_row - case
when p_column_headers then
2
else
1
end;
l_process_phase := 6;
dbms_sql.close_cursor(t_c);
if g_debug_mode then
debuglog('Stp1 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time := sysdate;
end if;
l_process_phase := 7;
if p_footer then
-- set footer
if g_query2sheet_footer is null then
cell(1,
t_cur_row + 2,
'Generated ' || to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') ||
' by ' || user || ', rows:' || g_query2sheet_rows,
p_sheet => t_sheet);
else
cell(1,
t_cur_row + 2,
replace(g_query2sheet_footer, '&ROWS', g_query2sheet_rows),
p_sheet => t_sheet);
end if;
end if;
l_process_phase := 8;
if (p_directory is not null and p_filename is not null) then
save(p_directory, p_filename);
end if;
if g_debug_mode then
debuglog('Stp2 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time := sysdate;
debuglog('query2sheet(-)');
end if;
l_process_phase := 99;
exception
when others then
if dbms_sql.is_open(t_c) then
dbms_sql.close_cursor(t_c);
end if;
clear_workbook;
x_retcode := 2;
x_errbuf := 'p_sql:' || substr(p_sql, 1, 20) ||
' 做自動輸出XLSX文件的時候有異常錯誤!' || chr(10) || '錯誤信息:' ||
to_char(sqlcode) || '-' || sqlerrm || chr(10) || '程序進度:' ||
l_process_phase;
end;
procedure query2sheet(p_sql varchar2,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true,
x_retcode out number ---0:成功 非0:失敗( 或者：0:成功 1:警告 2:錯誤 ----注意：確定警告的時候要做什麼動作)
,
x_errbuf out varchar2 ---具體的錯誤信息
) is
l_sql_statement varchar2(32767);
l_sql_statement_bv varchar2(32767);
l_col_value_tab smt_xlsx_maker_pkg.tab_col_value;
begin
if g_debug_mode then
debuglog('query2sheetII(+)');
end if;
---SQL自動轉換，用上傳說中的綁定變量！
l_sql_statement := p_sql;
----
l_sql_statement_bv := p_sql;
/*--這個要額外的開發，這裡先備注掉。
XYG_FND_COMMON_PKG.SQL_BIND_VARIABLE_CHANGE (
L_SQL_STATEMENT--P_SQL_STATEMENT IN VARCHAR2 ---要改變的SQL語句
,L_SQL_STATEMENT_BV--X_SQL_STATEMENT_BV OUT VARCHAR2--變化之後的SQL語句
,L_COL_VALUE_TAB--X_COL_VALUE_TAB OUT T_COL_VALUE ---綁定參數的輸出
,x_retcode--x_retcode OUT NUMBER ---0:成功 非0:失敗( 或者：0:成功 1:警告 2:錯誤 ----注意：確定警告的時候要做什麼動作)
,x_errbuf--x_errbuf OUT VARCHAR2 ---具體的錯誤信息
);
*/
x_retcode := 0;
if x_retcode <> 0 then
x_errbuf := '產生綁定變量的動態SQL有異常錯誤！錯誤信息:' || x_errbuf;
if g_debug_mode then
debuglog('x_errbuf:' || x_errbuf);
end if;
return;
else
if g_debug_mode then
if l_col_value_tab.count > 0 then
debuglog('LAST綁定變量:' || l_col_value_tab.last);
for i in 1 .. l_col_value_tab.last loop
if l_col_value_tab.exists(i) then
debuglog('綁定變量:' || i || '--' || l_col_value_tab(i)
.col_value);
end if;
end loop;
end if;
debuglog('L_SQL_STATEMENT_BV:' || chr(10) || l_sql_statement_bv);
end if;
end if;
query2sheet(l_sql_statement_bv,
l_col_value_tab ---運行的動態SQL的綁定變量
,
p_column_headers,
p_directory,
p_filename,
p_sheet,
p_footer,
x_retcode,
x_errbuf);
if g_debug_mode then
debuglog('query2sheetII(-)');
end if;
end;
procedure query2sheet(p_sql varchar2,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true) is
l_retcode number;
l_errbuf varchar2(4000);
begin
query2sheet(p_sql,
p_column_headers,
p_directory,
p_filename,
p_sheet,
p_footer,
l_retcode,
l_errbuf);
if l_retcode <> 0 then
raise_application_error(-20008, l_errbuf, true);
end if;
end;
procedure cursor2sheet(p_sql in sys_refcursor,
p_column_headers boolean := true,
p_directory varchar2 := null,
p_filename varchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true) is
ctx dbms_xmlgen.ctxhandle;
tmpxml xmltype;
cursor cdata is
select t2.column_value.getrootelement() colname,
extractvalue(t2.column_value, 'node()') value
from table(xmlsequence(tmpxml)) t,
table(xmlsequence(extract(t.column_value, '/ROWSET/ROW/node()'))) t2
order by rownum;
tscolheaders sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
tsvalues sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
t_sheet pls_integer := 1;
t_cur_row pls_integer := 1;
colid pls_integer := 1;
ncolnumber pls_integer;
n pls_integer;
atmpval sys.anydata;
nnumval number;
ttsval timestamp;
ddateval date;
svarcharval varchar2(4000);
bgottype boolean;
etypeconvert exception;
etypedateformat exception;
etypenonnumeric exception;
etypenotdefined exception;
pragma exception_init(etypeconvert, -6502);
pragma exception_init(etypedateformat, -1830);
pragma exception_init(etypenonnumeric, -1858);
begin
-- XML Creation from the sys_refcursor
ctx := dbms_xmlgen.newcontext(p_sql);
-- this is important in order to get all the column headers, even if all data are null
dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.empty_tag);
dbms_xmlgen.getxmltype(ctx, tmpxml);
if p_sheet is null then
new_sheet;
end if;
-- Load Columns and Values into Arrays
open cdata;
fetch cdata bulk collect
into tscolheaders, tsvalues;
close cdata;
-- get distinct headers
tscolheaders := set(tscolheaders);
-- get number of headers (of columns)
ncolnumber := tscolheaders.count;
-- Create column headers if wanted
if p_column_headers then
-- set headers into sheet
for i in tscolheaders.first .. tscolheaders.last loop
cell(i,
t_cur_row,
tscolheaders(i),
p_fontid => get_font('Calibri', p_bold => true),
p_sheet => t_sheet);
end loop;
t_cur_row := 2;
end if;
t_sheet := nvl(p_sheet, workbook.sheets.count());
-- fill cells
for i in tsvalues.first .. tsvalues.last loop
-- check if we must reset col to 1 and go to next line
if i > ncolnumber and mod(i, ncolnumber) = 1 then
-- reset colId to 1 and go to next line
colid := 1;
t_cur_row := t_cur_row + 1;
end if;
-- find the good type and insert into Cell
-- initialize “checker”
bgottype := false;
-- Number ?
if not bgottype then
begin
atmpval := sys.anydata.convertnumber(tsvalues(i));
bgottype := true;
n := atmpval.getnumber(nnumval);
-- load data into cell
cell(colid, t_cur_row, nnumval, p_sheet => t_sheet);
-- if conversion fails
exception
when etypeconvert or etypedateformat or etypenonnumeric then
bgottype := false;
end;
end if;
-- TimeStamp ?
if not bgottype then
begin
atmpval := sys.anydata.converttimestamp(tsvalues(i));
bgottype := true;
n := atmpval.gettimestamp(ttsval);
-- load data into cell
cell(colid, t_cur_row, to_date(ttsval), p_sheet => t_sheet);
-- if conversion fails
exception
when etypeconvert or etypedateformat or etypenonnumeric then
bgottype := false;
end;
end if;
-- Date ?
if not bgottype then
begin
atmpval := sys.anydata.convertdate(tsvalues(i));
bgottype := true;
n := atmpval.getdate(ddateval);
-- load data into cell
cell(colid, t_cur_row, ddateval, p_sheet => t_sheet);
-- if conversion fails
exception
when etypeconvert or etypedateformat or etypenonnumeric then
bgottype := false;
end;
end if;
-- Varchar2 ?
if not bgottype then
begin
atmpval := sys.anydata.convertvarchar2(tsvalues(i));
bgottype := true;
n := atmpval.getvarchar2(svarcharval);
-- load data into cell
cell(colid, t_cur_row, svarcharval, p_sheet => t_sheet);
-- if conversion fails
exception
when etypeconvert or etypedateformat or etypenonnumeric then
bgottype := false;
end;
end if;
-- unsupported type
if not bgottype then
raise etypenotdefined;
end if;
-- go to next col
colid := colid + 1;
end loop;
if p_footer then
-- set footer
cell(1,
t_cur_row + 2,
'Generated ' || sysdate || ' by ' || user,
p_sheet => t_sheet);
end if;
if (p_directory is not null and p_filename is not null) then
save(p_directory, p_filename);
end if;
exception
when etypenotdefined then
raise_application_error(-20999,
'one data has an unsupported type',
false);
raise;
when others then
raise_application_error(-20999, 'Export to XLSX failed', true);
end;
end;
smt_xlsx_maker_pkg 包體部分
使用方法：
使用方法：
declare
l_sql varchar2(30000);
begin
l_sql := 'select * from all_objects';
smt_xlsx_maker_pkg.query2sheet(l_sql,
true,
'XLS_DIR',
'Export2.xlsx');
end;
除此之外，基本上Excel 中有的效果它都可以生成。
以下一個例子，包括居中，合並單元格，底色，新增工作表等：
begin
smt_xlsx_maker_pkg.clear_workbook;
smt_xlsx_maker_pkg.new_sheet;
smt_xlsx_maker_pkg.cell(5, 1, 5);
smt_xlsx_maker_pkg.cell(3, 1, 3);
smt_xlsx_maker_pkg.cell(2, 2, 45);
smt_xlsx_maker_pkg.cell(3,
2,
'Anton Scheffer',
p_alignment => smt_xlsx_maker_pkg.get_alignment(p_wraptext => true));
smt_xlsx_maker_pkg.cell(1,
4,
sysdate,
p_fontid => smt_xlsx_maker_pkg.get_font('Calibri',
p_rgb => 'FFFF0000'));
smt_xlsx_maker_pkg.cell(2,
4,
sysdate,
p_numfmtid => smt_xlsx_maker_pkg.get_numfmt('dd/mm/yyyy h:mm'));
smt_xlsx_maker_pkg.cell(3,
4,
sysdate,
p_numfmtid => smt_xlsx_maker_pkg.get_numfmt(smt_xlsx_maker_pkg.orafmt2excel('dd/mon/yyyy')));
smt_xlsx_maker_pkg.cell(5,
5,
75,
p_borderid => smt_xlsx_maker_pkg.get_border('double',
'double',
'double',
'double'));
smt_xlsx_maker_pkg.cell(2, 3, 33);
smt_xlsx_maker_pkg.hyperlink(1,
6,
'http://www.cnblogs.com/mellowsmile',
'jinzhao site');
smt_xlsx_maker_pkg.cell(1,
7,
'Some merged cells',
p_alignment => smt_xlsx_maker_pkg.get_alignment(p_horizontal => 'center'));
smt_xlsx_maker_pkg.mergecells(1, 7, 3, 7);
for i in 1 .. 5 loop
smt_xlsx_maker_pkg.comment(3, i + 3, 'Row ' || (i + 3), 'Anton');
end loop;
smt_xlsx_maker_pkg.new_sheet;
smt_xlsx_maker_pkg.set_row(1,
p_fillid => smt_xlsx_maker_pkg.get_fill('solid',
'FFFF0000'));
for i in 1 .. 5 loop
smt_xlsx_maker_pkg.cell(1, i, i);
smt_xlsx_maker_pkg.cell(2, i, i * 3);
smt_xlsx_maker_pkg.cell(3, i, 'x ' || i * 3);
end loop;
smt_xlsx_maker_pkg.query2sheet('select rownum, x.*
, case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
, case when mod( rownum, 2 ) = 1 then ''demo '' || rownum end demo2 from dual x connect by rownum <= 5');
smt_xlsx_maker_pkg.save('XLS_DIR', 'Export3.xlsx');
end;
對比兩種導出excel的方法，第一種方法實際上導出的是html文件格式，且注意set pagesize 最大為50000，超過50000行數據後會自動分頁重新打印出一行標題，不需要創建oracle directory可以導出excel到客戶機但是單元格合並、批注等不可以個性化設置，第二種可設置單元格樣式但是需要創建directory且文件最終在服務器端生成。
