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 isrecord(
numfmtid pls_integer,
fontid pls_integer,
fillid pls_integer,
borderid pls_integer,
alignment tp_alignment);
type tp_col_fmtsis table of tp_xf_fmt index bypls_integer;
type tp_row_fmtsis table of tp_xf_fmt index bypls_integer;
type tp_widthsis table of number index bypls_integer;
type tp_cellisrecord(
valuenumber,
stylevarchar2(50));
type tp_cellsis table of tp_cell index bypls_integer;
type tp_rowsis table of tp_cells index bypls_integer;
type tp_autofilterisrecord(
column_start pls_integer,
column_end pls_integer,
row_start pls_integer,
row_end pls_integer);
type tp_autofiltersis table of tp_autofilter index bypls_integer;
type tp_hyperlinkisrecord(
cellvarchar2(10),
urlvarchar2(1000));
type tp_hyperlinksis table of tp_hyperlink index bypls_integer;
subtype tp_authoris varchar2(32767 char);
type tp_authorsis table of pls_integer index bytp_author;
authors tp_authors;
type tp_commentisrecord(text varchar2(32767 char),
author tp_author,
row pls_integer,columnpls_integer,
width pls_integer,
height pls_integer);
type tp_commentsis table of tp_comment index bypls_integer;
type tp_mergecellsis table of varchar2(21) index bypls_integer;
type tp_validationisrecord(
typevarchar2(10),
errorstylevarchar2(32),
showinputmessage boolean,
promptvarchar2(32767 char),
titlevarchar2(32767 char),
error_titlevarchar2(32767 char),
error_txtvarchar2(32767 char),
showerrormessage boolean,
formula1varchar2(32767 char),
formula2varchar2(32767 char),
allowblank boolean,
sqrefvarchar2(32767 char));
type tp_validationsis table of tp_validation index bypls_integer;
type tp_sheetisrecord(
rows tp_rows,
widths tp_widths,
namevarchar2(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_sheetsis table of tp_sheet index bypls_integer;
type tp_numfmtisrecord(
numfmtid pls_integer,
formatcodevarchar2(100));
type tp_numfmtsis table of tp_numfmt index bypls_integer;
type tp_fillisrecord(
patterntypevarchar2(30),
fgrgbvarchar2(8));
type tp_fillsis table of tp_fill index bypls_integer;
type tp_cellxfsis table of tp_xf_fmt index bypls_integer;
type tp_fontisrecord(
namevarchar2(100),
family pls_integer,
fontsizenumber,
theme pls_integer,
rgbvarchar2(8),
underline boolean,
italic boolean,
bold boolean);
type tp_fontsis table of tp_font index bypls_integer;
type tp_borderisrecord(top varchar2(17),
bottomvarchar2(17),left varchar2(17),right varchar2(17));
type tp_bordersis table of tp_border index bypls_integer;
type tp_numfmtindexesis table of pls_integer index bypls_integer;
type tp_stringsis table of pls_integer index by varchar2(32767 char);
type tp_str_indis table of varchar2(32767 char) index bypls_integer;
type tp_defined_nameisrecord(
namevarchar2(32767 char),
refvarchar2(32767 char),
sheet pls_integer);
type tp_defined_namesis table of tp_defined_name index bypls_integer;
type tp_bookisrecord(
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' thendbms_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;enddebuglog;procedureblob2file(p_blob blob,
p_directoryvarchar2 := 'MY_DIR',
p_filenamevarchar2 := 'my.xlsx') ist_fh utl_file.file_type;
t_len pls_integer := 32767;begint_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));endloop;
utl_file.fclose(t_fh);end;-- function raw2num(p_raw raw, p_len integer, p_pos integer) return number is
begin
returnutl_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
returnutl_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
returnutl_raw.cast_to_binary_integer(dbms_lob.substr(p_blob,
p_len,
p_pos),
utl_raw.little_endian);end;-- procedure add1file(p_zipped_blob inout blob,
p_namevarchar2,
p_content blob)ist_now date;
t_blob blob;
t_leninteger;
t_cleninteger;
t_crc32raw(4) := hextoraw('00000000');
t_compressed boolean :=false;
t_nameraw(32767);begint_now :=sysdate;
t_len := nvl(dbms_lob.getlength(p_content), 0);if t_len > 0 thent_blob :=utl_compress.lz_compress(p_content);
t_clen := dbms_lob.getlength(t_blob) - 18;
t_compressed := t_clen
t_crc32 := dbms_lob.substr(t_blob, 4, t_clen + 11);end if;if not t_compressed thent_clen :=t_len;
t_blob :=p_content;end if;if p_zipped_blob is null thendbms_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 whent_name=utl_i18n.string_to_raw(p_name,'US8PC437') thenhextoraw('0000') --no General purpose bits
else hextoraw('0008') --set Language encoding flag (EFS)
end,case when t_compressed thenhextoraw('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 thendbms_lob.copy(p_zipped_blob,
t_blob,
t_clen,
dbms_lob.getlength(p_zipped_blob)+ 1,11); --compressed content
elsif t_clen > 0 thendbms_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 thendbms_lob.freetemporary(t_blob);end if;end;-- procedure finish_zip(p_zipped_blob in out blob) ist_cnt pls_integer := 0;
t_offsinteger;
t_offs_dir_headerinteger;
t_offs_end_headerinteger;
t_commentraw(32767) := utl_raw.cast_to_raw('Implementation by Anton Scheffer');begint_offs_dir_header :=dbms_lob.getlength(p_zipped_blob);
t_offs := 1;whiledbms_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 whendbms_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
endloop;
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();endloop;
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) ist_nr pls_integer := workbook.sheets.count() + 1;
t_ind pls_integer;beginworkbook.sheets(t_nr).name := nvl(dbms_xmlgen.convert(translate(p_sheetname,'a/\[]*:?','a')),'Sheet' ||t_nr);if workbook.strings.count() = 0 thenworkbook.str_cnt := 0;end if;if workbook.fonts.count() = 0 thent_ind := get_font('Calibri');end if;if workbook.fills.count() = 0 thent_ind := get_fill('none');
t_ind := get_fill('gray125');end if;if workbook.borders.count() = 0 thent_ind := get_border('', '', '', '');end if;end;-- procedureset_col_width(p_sheet pls_integer,
p_col pls_integer,
p_formatvarchar2) ist_widthnumber;
t_nr_chr pls_integer;begin
if p_format is null then
return;end if;if instr(p_format, ';') > 0 thent_nr_chr :=length(translate(substr(p_format,1,
instr(p_format,';') - 1),'a\"','a'));elset_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) thenworkbook.sheets(p_sheet).widths(p_col) :=greatest(workbook.sheets(p_sheet)
.widths(p_col),
t_width);elseworkbook.sheets(p_sheet).widths(p_col) := greatest(t_width, 8.43);end if;end;-- function orafmt2excel(p_format varchar2 := null) return varchar2 ist_formatvarchar2(1000) := substr(p_format, 1, 1000);begint_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');returnt_format;end;-- function get_numfmt(p_format varchar2 := null) return pls_integer ist_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 loopif workbook.numfmts(i).formatcode = p_format thent_numfmtid :=workbook.numfmts(i).numfmtid;exit;end if;endloop;if t_numfmtid is null thent_numfmtid := case
when t_cnt = 0 then
164
elseworkbook.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;returnt_numfmtid;end;-- function get_font(p_name varchar2,
p_family pls_integer := 2,
p_fontsizenumber := 11,
p_theme pls_integer := 1,
p_underline boolean :=false,
p_italic boolean :=false,
p_bold boolean :=false,
p_rgbvarchar2 := null --this is a hex ALPHA Red Green Blue value
) return pls_integer ist_ind pls_integer;begin
if workbook.fonts.count() > 0 then
for f in 0 .. workbook.fonts.count() - 1loopif(workbook.fonts(f)
.name= p_name and workbook.fonts(f).family = p_family andworkbook.fonts(f)
.fontsize= p_fontsize and workbook.fonts(f).theme = p_theme andworkbook.fonts(f)
.underline= p_underline and workbook.fonts(f).italic = p_italic andworkbook.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
returnf;end if;endloop;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;returnt_ind;end;-- function get_fill(p_patterntype varchar2, p_fgrgb varchar2 := null)return pls_integer ist_ind pls_integer;begin
if workbook.fills.count() > 0 then
for f in 0 .. workbook.fills.count() - 1loopif(workbook.fills(f)
.patterntype= p_patterntype andnvl(workbook.fills(f).fgrgb,'x') = nvl(upper(p_fgrgb), 'x')) then
returnf;end if;endloop;end if;
t_ind := workbook.fills.count();
workbook.fills(t_ind).patterntype :=p_patterntype;
workbook.fills(t_ind).fgrgb := upper(p_fgrgb);returnt_ind;end;-- function get_border(p_top varchar2 := 'thin',
p_bottomvarchar2 := 'thin',
p_leftvarchar2 := 'thin',
p_rightvarchar2 := 'thin') return pls_integer ist_ind pls_integer;begin
if workbook.borders.count() > 0 then
for b in 0 .. workbook.borders.count() - 1loopif (nvl(workbook.borders(b).top, 'x') = nvl(p_top, 'x') andnvl(workbook.borders(b).bottom,'x') = nvl(p_bottom, 'x') andnvl(workbook.borders(b).left, 'x') = nvl(p_left, 'x') andnvl(workbook.borders(b).right, 'x') = nvl(p_right, 'x')) then
returnb;end if;endloop;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;returnt_ind;end;-- function get_alignment(p_vertical varchar2 := null,
p_horizontalvarchar2 := null,
p_wraptext boolean := null) return tp_alignment ist_rv tp_alignment;begint_rv.vertical :=p_vertical;
t_rv.horizontal :=p_horizontal;
t_rv.wraptext :=p_wraptext;returnt_rv;end;-- functionget_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 ist_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) thent_col_xf :=workbook.sheets(p_sheet).col_fmts(p_col);end if;if workbook.sheets(p_sheet).row_fmts.exists(p_row) thent_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 andt_xf.alignment.verticalis null andt_xf.alignment.horizontalis null and
not nvl(t_xf.alignment.wraptext, false)) then
return '';end if;if t_xf.numfmtid > 0 thenset_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 loopif(workbook.cellxfs(i)
.numfmtid= t_xf.numfmtid andworkbook.cellxfs(i)
.fontid= t_xf.fontid and workbook.cellxfs(i).fillid = t_xf.fillid andworkbook.cellxfs(i)
.borderid= t_xf.borderid andnvl(workbook.cellxfs(i).alignment.vertical,'x') =nvl(t_xf.alignment.vertical,'x') andnvl(workbook.cellxfs(i).alignment.horizontal,'x') =nvl(t_xf.alignment.horizontal,'x') andnvl(workbook.cellxfs(i).alignment.wraptext, false)=nvl(t_xf.alignment.wraptext, false))thent_xfid :=i;exit;end if;endloop;if t_xfid is null thent_cnt := t_cnt + 1;
t_xfid :=t_cnt;
workbook.cellxfs(t_cnt) :=t_xf;end if;return 's="' || t_xfid || '"';end;-- procedurecell(p_col pls_integer,
p_row pls_integer,
p_valuenumber,
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) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.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 ist_cnt pls_integer;begin
if workbook.strings.exists(p_string) thent_cnt :=workbook.strings(p_string);elset_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;returnt_cnt;end;-- procedurecell(p_col pls_integer,
p_row pls_integer,
p_valuevarchar2,
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) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());
t_alignment tp_alignment :=p_alignment;beginworkbook.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 thent_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;-- procedurecell(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) ist_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) andworkbook.sheets(t_sheet).col_fmts(p_col)
.numfmtidis not null) and
not (workbook.sheets(t_sheet).row_fmts.exists(p_row) andworkbook.sheets(t_sheet).row_fmts(p_row)
.numfmtidis not null) thent_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;-- procedurehyperlink(p_col pls_integer,
p_row pls_integer,
p_urlvarchar2,
p_valuevarchar2 := null,
p_sheet pls_integer := null) ist_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.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;-- procedurecomment(p_col pls_integer,
p_row pls_integer,
p_textvarchar2,
p_authorvarchar2 := null,
p_width pls_integer := 150,
p_height pls_integer := 100,
p_sheet pls_integer := null) ist_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());begint_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) ist_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());begint_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_sqrefvarchar2,
p_stylevarchar2 := 'stop' --stop, warning, information
,
p_formula1varchar2 := null,
p_formula2varchar2 := null,
p_titlevarchar2 := null,
p_promptvarchar := null,
p_show_error boolean :=false,
p_error_titlevarchar2 := null,
p_error_txtvarchar2 := null,
p_sheet pls_integer := null) ist_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());begint_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;-- procedurelist_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_stylevarchar2 := 'stop' --stop, warning, information
,
p_titlevarchar2 := null,
p_promptvarchar := null,
p_show_error boolean :=false,
p_error_titlevarchar2 := null,
p_error_txtvarchar2 := null,
p_sheet pls_integer := null) is
beginadd_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;-- procedurelist_validation(p_sqref_col pls_integer,
p_sqref_row pls_integer,
p_defined_namevarchar2,
p_stylevarchar2 := 'stop' --stop, warning, information
,
p_titlevarchar2 := null,
p_promptvarchar := null,
p_show_error boolean :=false,
p_error_titlevarchar2 := null,
p_error_txtvarchar2 := null,
p_sheet pls_integer := null) is
beginadd_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_namevarchar2,
p_sheet pls_integer := null,
p_localsheet pls_integer := null) ist_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());begint_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;-- procedureset_column_width(p_col pls_integer,
p_widthnumber,
p_sheet pls_integer := null) is
beginworkbook.sheets(nvl(p_sheet, workbook.sheets.count())).widths(p_col) :=p_width;end;-- procedureset_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) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.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;-- procedureset_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) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.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) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.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) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.sheets(t_sheet).freeze_rows := null;
workbook.sheets(t_sheet).freeze_cols :=p_nr_cols;end;-- procedurefreeze_pane(p_col pls_integer,
p_row pls_integer,
p_sheet pls_integer := null) ist_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());beginworkbook.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) ist_ind pls_integer;
t_sheet pls_integer := nvl(p_sheet, workbook.sheets.count());begint_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 inout nocopy blob,
p_filenamevarchar2,
p_xml clob)ist_tmp blob;
dest_offsetinteger := 1;
src_offsetinteger := 1;
lang_contextinteger;
warninginteger;beginlang_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 ist_excel blob;
t_xxx clob;
t_tmpvarchar2(32767 char);--t_tmp clob;
t_str varchar2(32767 char);
t_cnumber;
t_hnumber;
t_wnumber;
t_cwnumber;
t_cellvarchar2(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;
tstimestamp :=systimestamp;---
l_debug_time date;begin
if g_debug_mode thendebuglog('finish(+)');
l_debug_time :=sysdate;end if;
dbms_lob.createtemporary(t_excel, true);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '';endloop;
t_xxx := t_xxx || '
';for s in 1 .. workbook.sheets.count() loopif workbook.sheets(s).comments.count() > 0 thent_xxx := t_xxx || '';end if;endloop;
t_xxx := t_xxx || '';
add1xml(t_excel,'[Content_Types].xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
' || 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/core.xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
Microsoft Excel
0
false
Worksheets
' || workbook.sheets.count() || '
';for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '' || workbook.sheets(s).name || '';endloop;
t_xxx := t_xxx || '
false
false
false
14.0300
';
add1xml(t_excel,'docProps/app.xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';
add1xml(t_excel,'_rels/.rels', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';if workbook.numfmts.count() > 0 thent_xxx := t_xxx || '';for n in 1 .. workbook.numfmts.count() loop
t_xxx := t_xxx || '';endloop;
t_xxx := t_xxx || '';end if;
t_xxx := t_xxx || '';for f in 0 .. workbook.fonts.count() - 1loop
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 || '
when workbook.fonts(f).rgb is not null then
'rgb="' ||workbook.fonts(f).rgbelse
'theme="' ||workbook.fonts(f).themeend || '"/>
';endloop;
t_xxx := t_xxx || '
';for f in 0 .. workbook.fills.count() - 1loop
t_xxx := t_xxx || '' || case
when workbook.fills(f).fgrgb is not null then
''
end || '';endloop;
t_xxx := t_xxx || '
';for b in 0 .. workbook.borders.count() - 1loop
t_xxx := t_xxx || '' || case
when workbook.borders(b).left is null then
''
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 || '';endloop;
t_xxx := t_xxx || '
';for x in 1 .. workbook.cellxfs.count() loop
t_xxx := t_xxx || '';if (workbook.cellxfs(x).alignment.horizontal is not null orworkbook.cellxfs(x)
.alignment.verticalis not null orworkbook.cellxfs(x)
.alignment.wraptext)thent_xxx := t_xxx || '
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 || '';endloop;
t_xxx := t_xxx || '
';
add1xml(t_excel,'xl/styles.xml', t_xxx);--
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '';endloop;
t_xxx := t_xxx || '';if workbook.defined_names.count() > 0 thent_xxx := t_xxx || '';for s in 1 .. workbook.defined_names.count() loop
t_xxx := t_xxx || '
when workbook.defined_names(s).sheet is not null then
'localSheetId="' || to_char(workbook.defined_names(s).sheet) || '"'
end || '>' || workbook.defined_names(s).ref ||
'
';endloop;t_xxx := t_xxx || '';end if;
t_xxx := t_xxx || '';
add1xml(t_excel,'xl/workbook.xml', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';
add1xml(t_excel,'xl/theme/theme1.xml', t_xxx);if g_debug_mode thendebuglog('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();
loopexit 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);endloop;---
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
when s = 1 then
'tabSelected="1"'
end || 'workbookViewId="0">';ifworkbook.sheets(s)
.freeze_rows> 0 and workbook.sheets(s).freeze_cols > 0 thent_xxx := t_xxx || ('
.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 thent_xxx := t_xxx || '';end if;if workbook.sheets(s).freeze_cols > 0 thent_xxx := t_xxx || '';end if;end if;
t_xxx := t_xxx ||
'
';if workbook.sheets(s).widths.count() > 0 thent_xxx := t_xxx || '';
t_col_ind :=workbook.sheets(s).widths.first();while t_col_ind is not nullloop
t_xxx := t_xxx || '
';t_col_ind := workbook.sheets(s).widths.next(t_col_ind);endloop;
t_xxx := t_xxx || '';end if;
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 );
t_col_ind := workbook.sheets( s ).rows( t_row_ind ).first();
while t_col_ind is not null
loop
t_cell := '
|| ' ' || workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).style
|| '>'
|| to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' )
|| '
';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 || '';
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 nullloop
t_tmp := t_tmp || '';
t_len :=length(t_tmp);
t_col_ind :=lc_rows(t_row_ind).first();while t_col_ind is not nullloop
t_cell := '
.rows(t_row_ind)(t_col_ind)
.style|| '>' ||to_char(lc_rows(t_row_ind)(t_col_ind).value,'TM9','NLS_NUMERIC_CHARACTERS=.,') || q'[
]';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);endloop;
t_tmp := t_tmp || '';
t_row_ind := lc_rows.next(t_row_ind);endloop;------------
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 || '';endloop;if workbook.sheets(s).mergecells.count() > 0 thent_xxx := t_xxx || '';for m in 1 .. workbook.sheets(s).mergecells.count() loop
t_xxx := t_xxx || '';endloop;
t_xxx := t_xxx || '';end if;-- if workbook.sheets(s).validations.count() > 0 thent_xxx := t_xxx || '';for m in 1 .. workbook.sheets(s).validations.count() loop
t_xxx := t_xxx || '
'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 thent_xxx := t_xxx || 'showInputMessage="1" prompt="' ||workbook.sheets(s).validations(m)
.prompt|| '"';if workbook.sheets(s).validations(m).title is not null thent_xxx := t_xxx || 'promptTitle="' ||workbook.sheets(s).validations(m)
.title|| '"';end if;end if;if workbook.sheets(s).validations(m).showerrormessage thent_xxx := t_xxx || 'showErrorMessage="1"';if workbook.sheets(s).validations(m).error_title is not null thent_xxx := t_xxx || 'errorTitle="' ||workbook.sheets(s).validations(m)
.error_title|| '"';end if;if workbook.sheets(s).validations(m).error_txt is not null thent_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 thent_xxx := t_xxx || '' ||workbook.sheets(s).validations(m)
.formula1|| '';end if;if workbook.sheets(s).validations(m).formula2 is not null thent_xxx := t_xxx || '' ||workbook.sheets(s).validations(m)
.formula2|| '';end if;
t_xxx := t_xxx || '
';endloop;t_xxx := t_xxx || '';end if;-- if workbook.sheets(s).hyperlinks.count() > 0 thent_xxx := t_xxx || '';for h in 1 .. workbook.sheets(s).hyperlinks.count() loop
t_xxx := t_xxx || '';endloop;
t_xxx := t_xxx || '';end if;
t_xxx := t_xxx ||
'';if workbook.sheets(s).comments.count() > 0 thent_xxx := t_xxx || '';end if;-- t_xxx := t_xxx || '';
add1xml(t_excel,'xl/worksheets/sheet' || s || '.xml', t_xxx);ifworkbook.sheets(s)
.hyperlinks.count() > 0 or workbook.sheets(s).comments.count() > 0 thent_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';if workbook.sheets(s).comments.count() > 0 thent_xxx := t_xxx || '';
t_xxx := t_xxx || '';end if;for h in 1 .. workbook.sheets(s).hyperlinks.count() loop
t_xxx := t_xxx || '';endloop;
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
declarecnt pls_integer;
author_ind tp_author;--t_col_ind := workbook.sheets( s ).widths.next( t_col_ind );
beginauthors.delete();for c in 1 .. workbook.sheets(s).comments.count() loop
authors(workbook.sheets(s).comments(c).author) := 0;endloop;
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';
cnt := 0;
author_ind :=authors.first();while author_ind is not null orauthors.next(author_ind) is not nullloop
authors(author_ind) :=cnt;
t_xxx := t_xxx || '' || author_ind || '';
cnt := cnt + 1;
author_ind := authors.next(author_ind);endloop;end;
t_xxx := t_xxx || '';for c in 1 .. workbook.sheets(s).comments.count() loop
t_xxx := t_xxx || '
';if workbook.sheets(s).comments(c).author is not null thent_xxx := t_xxx ||
'' ||workbook.sheets(s).comments(c)
.author|| ':';end if;
t_xxx := t_xxx ||
'' || case
when workbook.sheets(s).comments(c).author is not null then
'
'
end || workbook.sheets(s).comments(c).text ||
'';endloop;
t_xxx := t_xxx || '';
add1xml(t_excel,'xl/comments' || s || '.xml', t_xxx);
t_xxx := '
';for c in 1 .. workbook.sheets(s).comments.count() loop
t_xxx := t_xxx || '
style="position:absolute;margin-left:35.25pt;margin-top:3pt;z-index:' ||to_char(c)||
';visibility:hidden;" fillcolor="#ffffe1" o:insetmode="auto">
';
t_w :=workbook.sheets(s).comments(c).width;
t_c := 1;
loopifworkbook.sheets(s)
.widths.exists(workbook.sheets(s).comments(c).column + t_c) thent_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
elset_cw := 64;end if;exit when t_w
t_c := t_c + 1;
t_w := t_w -t_cw;endloop;
t_h :=workbook.sheets(s).comments(c).height;
t_xxx := t_xxx || to_char('' ||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) ||
'
');endloop;t_xxx := t_xxx || '';
add1xml(t_excel,'xl/drawings/vmlDrawing' || s || '.vml', t_xxx);end if;-- endloop;
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';for s in 1 .. workbook.sheets.count() loop
t_xxx := t_xxx || '';endloop;
t_xxx := t_xxx || '';
add1xml(t_excel,'xl/_rels/workbook.xml.rels', t_xxx);
t_xxx := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
';
t_tmp := null;for i in 0 .. workbook.str_ind.count() - 1loop
t_str := '' ||dbms_xmlgen.convert(substr(workbook.str_ind(i), 1, 32000)) ||
'';if lengthb(t_tmp) + lengthb(t_str) > 32000 thent_xxx := t_xxx ||t_tmp;
t_tmp := null;end if;
t_tmp := t_tmp ||t_str;endloop;
t_xxx := t_xxx || t_tmp || '';
add1xml(t_excel,'xl/sharedStrings.xml', t_xxx);if g_debug_mode thendebuglog('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 thendebuglog('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 thendebuglog('Stp4 run time(sec):' ||
round((sysdate - l_debug_time) * 24 * 60 * 60, 2));
l_debug_time :=sysdate;
debuglog('finish(-)');end if;returnt_excel;end;-- procedure save(p_directory varchar2, p_filename varchar2) is
beginblob2file(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_directoryvarchar2 := null,
p_filenamevarchar2 := null,
p_sheet pls_integer := null,
p_footer boolean :=true,
x_retcode outnumber ---0:成功 非0:失败( 或者：0:成功 1:警告 2:错误 ----注意：确定警告的时候要做什么动作)
,
x_errbuf outvarchar2 ---具体的错误信息
) isl_process_phasenumber; --标识程序的进度
t_sheet pls_integer;
t_cinteger;
t_col_cntinteger;
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_rinteger;
t_cur_row pls_integer;---
l_debug_time date;beginx_retcode := 0;
x_errbuf := null;
l_process_phase := 0;if g_debug_mode thendebuglog('query2sheet(+)');
l_debug_time :=sysdate;end if;if p_sql is null thenx_errbuf := '调用query2sheet的时候，必要的参数不存在，请检查参数!';
x_retcode := 2;return;end if;if p_sheet is null thennew_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 loopif p_col_value_tab.exists(i) thendbms_sql.bind_variable(t_c,':' ||i,
p_col_value_tab(i).col_value);if g_debug_mode thendebuglog('绑定变量--' || i || ':' ||p_col_value_tab(i).col_value);end if;end if;endloop;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 loopif p_column_headers thencell(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) thendbms_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) thendbms_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) thendbms_sql.define_array(t_c, c, v_tab, t_bulk_size,1);else
null;end case;endloop;
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 loopcase
when t_desc_tab(c).col_type in (2, 100, 101) thendbms_sql.column_value(t_c, c, n_tab);for i in 0 .. t_r - 1loopif n_tab(i + n_tab.first()) is not null thencell(c,
t_cur_row+i,
n_tab(i+n_tab.first()),
p_sheet=>t_sheet);end if;endloop;
n_tab.delete;when t_desc_tab(c).col_type in (12, 178, 179, 180, 181, 231) thendbms_sql.column_value(t_c, c, d_tab);for i in 0 .. t_r - 1loopif d_tab(i + d_tab.first()) is not null thencell(c,
t_cur_row+i,
d_tab(i+d_tab.first()),
p_sheet=>t_sheet);end if;endloop;
d_tab.delete;when t_desc_tab(c).col_type in (1, 8, 9, 96, 112) thendbms_sql.column_value(t_c, c, v_tab);for i in 0 .. t_r - 1loopif v_tab(i + v_tab.first()) is not null thencell(c,
t_cur_row+i,
v_tab(i+v_tab.first()),
p_sheet=>t_sheet);end if;endloop;
v_tab.delete;else
null;end case;endloop;end if;if t_r != t_bulk_size thent_cur_row := t_cur_row +t_r;end if;exit when t_r !=t_bulk_size;
t_cur_row := t_cur_row +t_r;endloop;
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 thendebuglog('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 thencell(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);elsecell(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 thendebuglog('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;
exceptionwhen others then
if dbms_sql.is_open(t_c) thendbms_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_directoryvarchar2 := null,
p_filenamevarchar2 := null,
p_sheet pls_integer := null,
p_footer boolean :=true,
x_retcode outnumber ---0:成功 非0:失败( 或者：0:成功 1:警告 2:错误 ----注意：确定警告的时候要做什么动作)
,
x_errbuf outvarchar2 ---具体的错误信息
) isl_sql_statementvarchar2(32767);
l_sql_statement_bvvarchar2(32767);
l_col_value_tab smt_xlsx_maker_pkg.tab_col_value;begin
if g_debug_mode thendebuglog('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 thenx_errbuf := '产生绑定变量的动态SQL有异常错误！错误信息:' ||x_errbuf;if g_debug_mode thendebuglog('x_errbuf:' ||x_errbuf);end if;return;else
if g_debug_mode then
if l_col_value_tab.count > 0 thendebuglog('LAST绑定变量:' ||l_col_value_tab.last);for i in 1.. l_col_value_tab.last loopif l_col_value_tab.exists(i) thendebuglog('绑定变量:' || i || '--' ||l_col_value_tab(i)
.col_value);end if;endloop;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 thendebuglog('query2sheetII(-)');end if;end;procedure query2sheet(p_sql varchar2,
p_column_headers boolean :=true,
p_directoryvarchar2 := null,
p_filenamevarchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true) isl_retcodenumber;
l_errbufvarchar2(4000);beginquery2sheet(p_sql,
p_column_headers,
p_directory,
p_filename,
p_sheet,
p_footer,
l_retcode,
l_errbuf);if l_retcode <> 0 thenraise_application_error(-20008, l_errbuf, true);end if;end;procedure cursor2sheet(p_sql insys_refcursor,
p_column_headers boolean :=true,
p_directoryvarchar2 := null,
p_filenamevarchar2 := null,
p_sheet pls_integer := null,
p_footer boolean := true) isctx dbms_xmlgen.ctxhandle;
tmpxml xmltype;cursor cdata is
selectt2.column_value.getrootelement() colname,
extractvalue(t2.column_value,'node()') valuefrom table(xmlsequence(tmpxml)) t,table(xmlsequence(extract(t.column_value, '/ROWSET/ROW/node()'))) t2order byrownum;
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;
nnumvalnumber;
ttsvaltimestamp;
ddateval date;
svarcharvalvarchar2(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 thennew_sheet;end if;--Load Columns and Values into Arrays
opencdata;fetch cdata bulkcollectintotscolheaders, tsvalues;closecdata;--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 intscolheaders.first .. tscolheaders.last loop
cell(i,
t_cur_row,
tscolheaders(i),
p_fontid=> get_font('Calibri', p_bold =>true),
p_sheet=>t_sheet);endloop;
t_cur_row := 2;end if;
t_sheet := nvl(p_sheet, workbook.sheets.count());--fill cells
for i intsvalues.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
beginatmpval :=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
exceptionwhen etypeconvert or etypedateformat or etypenonnumeric thenbgottype :=false;end;end if;--TimeStamp ?
if not bgottype then
beginatmpval :=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
exceptionwhen etypeconvert or etypedateformat or etypenonnumeric thenbgottype :=false;end;end if;--Date ?
if not bgottype then
beginatmpval :=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
exceptionwhen etypeconvert or etypedateformat or etypenonnumeric thenbgottype :=false;end;end if;--Varchar2 ?
if not bgottype then
beginatmpval :=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
exceptionwhen etypeconvert or etypedateformat or etypenonnumeric thenbgottype :=false;end;end if;--unsupported type
if not bgottype thenraise etypenotdefined;end if;--go to next col
colid := colid + 1;endloop;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;
exceptionwhen etypenotdefined thenraise_application_error(-20999,'one data has an unsupported type',
false);
raise;when others thenraise_application_error(-20999, 'Export to XLSX failed', true);end;end;
315
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
