EBS报表excel多页签打印

62 阅读 0 评论 41 点赞

我是靠谱客的博主美好麦片，最近开发中收集的这篇文章主要介绍EBS报表excel多页签打印，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

EBS打印excel报表的时候，由于数据量大或者特殊要求，需要在多个页签中显示数据

1. 建立utl工具包；

2. 报表样例

比较直接的写法可以参考

多sheet页报表_粉红色的猪猪的博客-CSDN博客_styleid=s62

1.utl工具包

utl包头

CREATE OR REPLACE PACKAGE cux_excel IS
/*===============================================
*
Program Name:
cux_excel
*
Description:
for create excel/html content
*
1.0
support HTML/2003 XML
*
2.0
support xlsx
*
3.0
2021-04-02 improve performance(very fast)
*
Author:
dragon.zou
*
Date:
2018/05/15
* ==============================================*/
--
FUNCTION profile(p_key IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE profile(p_key IN VARCHAR2, p_value IN VARCHAR2);
--
PROCEDURE output(p_var IN VARCHAR2);
PROCEDURE set_output_type(p_flag IN VARCHAR2);
PROCEDURE set_output_format(p_format IN VARCHAR2);
--
PROCEDURE workbook_open(p_name
IN VARCHAR2 DEFAULT NULL,
p_style IN VARCHAR2 DEFAULT NULL);
PROCEDURE workbook_close;
PROCEDURE worksheet_open(p_sheetname VARCHAR2 := NULL);
PROCEDURE worksheet_close;
PROCEDURE row_open(p_height IN NUMBER := NULL,
p_style
IN VARCHAR2 := NULL);
PROCEDURE row_close;
FUNCTION get_current_row RETURN NUMBER;
--
PROCEDURE release(p_action IN VARCHAR2 := NULL);
--
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) RETURN PLS_INTEGER;
--
FUNCTION get_fill(p_patterntype VARCHAR2, p_fgrgb VARCHAR2 := NULL)
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 PLS_INTEGER;
--
PROCEDURE add_style(p_id
IN VARCHAR2,
p_fontfamily
IN VARCHAR2 DEFAULT NULL,
p_fontname
IN VARCHAR2 DEFAULT NULL, --字体名称
p_fontsize
IN NUMBER DEFAULT NULL, --字体大小
p_fonttheme
IN NUMBER DEFAULT 1, --THEME
p_fontcolor
IN VARCHAR2 DEFAULT NULL, --字体颜色
p_backcolor
IN VARCHAR2 DEFAULT NULL, --背景颜色
p_pattern
IN VARCHAR2 DEFAULT NULL, --背景填充方式
p_bold
IN NUMBER DEFAULT NULL, --粗体
p_italic
IN NUMBER DEFAULT NULL, --斜体
p_underline
IN NUMBER DEFAULT NULL, --下划线
p_vertical
IN VARCHAR2 DEFAULT NULL, --坚直方向对齐
p_horizontal
IN VARCHAR2 DEFAULT NULL, --水平方向对齐
p_wraptext
IN NUMBER DEFAULT NULL, --自动折行
p_border
IN NUMBER DEFAULT NULL,
p_left
IN NUMBER DEFAULT NULL, --亦可以单独设每边格线
p_top
IN NUMBER DEFAULT NULL,
p_right
IN NUMBER DEFAULT NULL,
p_bottom
IN NUMBER DEFAULT NULL,
p_numberformat IN VARCHAR2 DEFAULT NULL, --显示格式
p_copy
IN VARCHAR2 DEFAULT NULL --COPY已有样式,加上部分样式参数组合成新样式
);
--
PROCEDURE set_default_style(p_style IN VARCHAR2);
FUNCTION get_string(p_type
IN VARCHAR2,
p_row
IN NUMBER := NULL,
p_col
IN NUMBER := NULL,
p_end_row IN NUMBER := NULL,
p_end_col IN NUMBER := NULL) RETURN VARCHAR2;
--
FUNCTION get_style(p_style
IN VARCHAR2,
p_numfmtid
PLS_INTEGER := NULL,
p_fontid
PLS_INTEGER := NULL,
p_fillid
PLS_INTEGER := NULL,
p_borderid
PLS_INTEGER := NULL,
p_alignment PLS_INTEGER := NULL) RETURN NUMBER;
--
PROCEDURE cell(p_content IN NUMBER,
p_style
IN VARCHAR2 := NULL,
ROW
PLS_INTEGER := NULL,
col
PLS_INTEGER := NULL,
rowspan
IN NUMBER := NULL,
colspan
IN NUMBER := NULL);
--
PROCEDURE cell(p_content IN VARCHAR2,
p_style
IN VARCHAR2 := NULL,
ROW
PLS_INTEGER := NULL,
col
PLS_INTEGER := NULL,
rowspan
IN NUMBER := NULL,
colspan
IN NUMBER := NULL);
--
PROCEDURE cell(p_content IN DATE,
p_style
IN VARCHAR2 := NULL,
ROW
PLS_INTEGER := NULL,
col
PLS_INTEGER := NULL,
rowspan
IN NUMBER := NULL,
colspan
IN NUMBER := NULL);
--
PROCEDURE hyperlink(p_col
PLS_INTEGER,
p_row
PLS_INTEGER,
p_url
VARCHAR2,
p_value VARCHAR2 := NULL);
--
PROCEDURE hyperlink_loc(p_col
PLS_INTEGER,
p_row
PLS_INTEGER,
p_location VARCHAR2);
--
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 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 VARCHAR2, p_width NUMBER);
--
PROCEDURE set_column_style(p_col PLS_INTEGER, p_style VARCHAR2);
--
PROCEDURE freeze_pane(p_col
PLS_INTEGER,
p_row
PLS_INTEGER,
p_sheet PLS_INTEGER := NULL);
--
PROCEDURE set_margin(p_left
NUMBER := NULL,
p_right
NUMBER := NULL,
p_top
NUMBER := NULL,
p_bottom NUMBER := NULL,
p_header NUMBER := NULL,
p_footer NUMBER := NULL,
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;
FUNCTION download(p_file_name
IN VARCHAR2 DEFAULT NULL,
p_content_type IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
FUNCTION get_buffer RETURN CLOB;
--
PROCEDURE blob2file(p_blob
BLOB,
p_directory VARCHAR2 := 'MY_DIR',
p_filename
VARCHAR2 := 'my.xlsx');
PROCEDURE SAVE(p_directory VARCHAR2, p_filename VARCHAR2);
--
PROCEDURE query2sheet(p_sql
VARCHAR2,
p_column_headers VARCHAR2 DEFAULT '#COLUMN_NAME#',
p_directory
VARCHAR2 := NULL,
p_filename
VARCHAR2 := NULL,
p_startrow
NUMBER DEFAULT 1);
END;

utl包体

CREATE OR REPLACE PACKAGE BODY cux_excel IS
/*===============================================
*
Program Name:
cux_excel
*
Description:
for create excel/html content
*
1.0
support HTML/2003 XML
*
2.0
support xlsx
*
3.0
2021-04-02 improve performance(very fast)
*
Author:
dragon.zou
*
Date:
2018/05/15
* ==============================================*/
--
c_local_file_header
CONSTANT RAW(4) := hextoraw('504B0304');
c_end_of_central_directory CONSTANT RAW(4) := hextoraw('504B0506');
c_max_cols
CONSTANT NUMBER := 1000;
c_type_base
CONSTANT NUMBER := 100000;
c_type_date
CONSTANT NUMBER := c_type_base * 1;
c_type_string
CONSTANT NUMBER := c_type_base * 2;
c_type_formula
CONSTANT NUMBER := c_type_base * 3;
c_date_base
CONSTANT DATE := to_date('01-01-1904',
'DD-MM-YYYY');
--
g_output_buffer CLOB;
g_filename
VARCHAR2(240);
g_output_type
VARCHAR2(10) := 'OUTPUT';
g_output_format VARCHAR2(10) := 'XML';
g_default_font
VARCHAR2(80) := '宋体';
--
TYPE tp_alignment IS RECORD(
vertical
VARCHAR2(11),
horizontal VARCHAR2(16),
wraptext
BOOLEAN);
TYPE tp_aligns IS TABLE OF tp_alignment INDEX BY PLS_INTEGER;
TYPE tp_xf_fmt IS RECORD(
xfid
PLS_INTEGER,
numfmtid
PLS_INTEGER,
fontid
PLS_INTEGER,
fillid
PLS_INTEGER,
borderid
PLS_INTEGER,
alignment PLS_INTEGER);
TYPE table_number IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE table_integer 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_str_arr IS TABLE OF VARCHAR2(32767 CHAR) INDEX BY VARCHAR2(240);
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),
location 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_mergecell IS RECORD(
ROW
PLS_INTEGER,
col
PLS_INTEGER,
rowspan PLS_INTEGER,
colspan PLS_INTEGER);
TYPE tp_mergecells IS TABLE OF tp_mergecell 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_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_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_row IS RECORD(
height NUMBER,
xfid
NUMBER,
VALUE
table_number,
style
table_integer);
TYPE tp_rows IS TABLE OF tp_row INDEX BY PLS_INTEGER;
TYPE tp_sheet IS RECORD(
init
PLS_INTEGER,
min_row
PLS_INTEGER := 1,
max_row
PLS_INTEGER := 1,
min_col
PLS_INTEGER := 1,
max_col
PLS_INTEGER := 1,
buffer_start
PLS_INTEGER,
buffer_end
PLS_INTEGER,
rows
tp_rows,
widths
table_number,
NAME
VARCHAR2(100),
freeze_rows
PLS_INTEGER,
freeze_cols
PLS_INTEGER,
margin_left
NUMBER := 0.7,
margin_right
NUMBER := 0.7,
margin_top
NUMBER := 0.75,
margin_bottom NUMBER := 0.75,
margin_header NUMBER := 0.3,
margin_footer NUMBER := 0.3,
autofilters
tp_autofilters,
hyperlinks
tp_hyperlinks,
comments
tp_comments,
col_fmts
table_integer,
mergecells
tp_mergecells,
mergecols
table_integer,
validations
tp_validations);
TYPE tp_sheets IS TABLE OF tp_sheet INDEX BY PLS_INTEGER;
--WorkBook移至外层提升执行效率
wb_init
NUMBER;
wb_sheets
tp_sheets;
wb_strings
tp_strings;
wb_str_ind
tp_str_ind;
wb_str_cnt
PLS_INTEGER := 0;
wb_fonts
tp_fonts;
wb_fills
tp_fills;
wb_borders
tp_borders;
wb_numfmts
tp_numfmts;
wb_cellxfs
tp_cellxfs;
wb_xfs_indexes
tp_strings;
wb_styles
tp_cellxfs;
wb_aligns
tp_aligns;
wb_profiles
tp_str_arr;
wb_numfmtindexes tp_numfmtindexes;
wb_defined_names tp_defined_names;
g_cur_sheet
NUMBER;
g_cur_row
NUMBER;
g_cur_col
NUMBER;
g_cur_style
VARCHAR2(240);
wb_activerow tp_row;
wb_col_fmts
table_integer;
--
PROCEDURE output(p_var IN VARCHAR2) AS
BEGIN
IF p_var IS NULL THEN
RETURN;
END IF;
IF g_output_type IN ('B') THEN
dbms_lob.writeappend(g_output_buffer, length(p_var), p_var);
ELSIF g_output_type IN ('O', 'OUTPUT') THEN
fnd_file.put_line(fnd_file.output, p_var);
ELSIF g_output_type IN ('L', 'LOG') THEN
fnd_file.put_line(fnd_file.log, p_var);
ELSIF g_output_type IN ('D') THEN
dbms_output.put_line(p_var);
END IF;
END;
--设置输出容器类型
PROCEDURE set_output_type(p_flag IN VARCHAR2) AS
BEGIN
--BUFFER,OUTPUT,LOG,DEBUG
g_output_type := upper(substr(p_flag, 1, 1));
END;
--设置输出格式
PROCEDURE set_output_format(p_format IN VARCHAR2) AS
BEGIN
g_output_format := upper(p_format);
IF g_output_format = 'XLSX' THEN
set_output_type('B');
END IF;
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 output_row(t_sheet NUMBER, t_row_ind NUMBER, t_row tp_row) IS
t_tmp
VARCHAR2(32767);
t_cell
VARCHAR2(32767);
t_string
VARCHAR2(32767);
t_row_style VARCHAR2(1000);
t_col_style VARCHAR2(1000);
t_mergecell tp_mergecell;
t_len
NUMBER;
t_mindex
NUMBER;
t_col_ind
NUMBER;
t_col_last
NUMBER;
t_value
NUMBER;
t_r_xfid
NUMBER;
t_c_xfid
NUMBER;
s
NUMBER;
l_datatype
VARCHAR2(20);
t_values
table_number;
t_styles
table_integer;
BEGIN
s
:= nvl(t_sheet, g_cur_sheet);
t_values := t_row.value;
t_styles := t_row.style;
IF g_output_format = 'XLSX' THEN
IF t_row.height IS NOT NULL THEN
t_row_style := 'ht="' || t_row.height || '" customHeight="1"';
ELSIF t_row.xfid > 0 THEN
t_row_style := 's="' || t_row.xfid || '"';
ELSE
t_row_style := '';
END IF;
t_tmp
:= '<row r="' || t_row_ind || '" spans="' || wb_sheets(s)
.min_col || --
':' || wb_sheets(s).max_col || '" ' || t_row_style || '>';
t_len
:= length(t_tmp);
t_col_ind := t_values.first();
WHILE t_col_ind IS NOT NULL LOOP
t_c_xfid := t_styles(t_col_ind);
t_value
:= t_values(t_col_ind);
t_cell := '<c r="' || alfan_col(t_col_ind) || t_row_ind || '"' || ' ';
IF t_c_xfid >= c_type_formula THEN
t_cell := t_cell || 's="' || (t_c_xfid - c_type_formula) || '"';
ELSIF t_c_xfid >= c_type_string THEN
t_cell := t_cell || 't="s" ' || 's="' ||
(t_c_xfid - c_type_string) || '"';
ELSIF t_c_xfid >= c_type_date THEN
t_cell := t_cell || 's="' || (t_c_xfid - c_type_date) || '"';
ELSIF t_c_xfid > 0 THEN
t_cell := t_cell || 's="' || t_c_xfid || '"';
END IF;
IF t_c_xfid >= c_type_formula THEN
t_cell := t_cell || '><f>' || substr(wb_str_ind(t_value), 2) ||
'</f></c>';
ELSE
t_cell := t_cell || '><v>' ||
to_char(t_value, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,') ||
'</v></c>';
END IF;
IF t_len > 30000 THEN
output(t_tmp);
t_tmp := NULL;
t_len := 0;
END IF;
t_tmp
:= t_tmp || t_cell;
t_len
:= t_len + lengthb(t_cell);
t_col_ind := t_values.next(t_col_ind);
END LOOP;
t_tmp := t_tmp || '</row>' || chr(13) || chr(10);
output(t_tmp);
RETURN;
END IF;
IF g_output_format = 'XML' THEN
t_r_xfid
:= 0;
t_col_last
:= 0;
t_row_style := NULL;
--
IF t_row.height > 0 THEN
t_row_style := ' ss:AutoFitHeight="0" ss:Height="' ||
round(t_row.height, 1) || '"';
ELSE
t_row_style := ' ss:AutoFitHeight="1"';
END IF;
IF t_row.xfid > 0 THEN
t_row_style := t_row_style || ' ss:StyleID="s' || t_row.xfid || '"';
END IF;
t_tmp := t_tmp || '<Row' || t_row_style || '>';
t_len
:= lengthb(t_tmp);
t_col_ind := t_values.first();
WHILE t_col_ind IS NOT NULL LOOP
--
t_col_style := NULL;
t_string
:= NULL;
t_c_xfid
:= t_styles(t_col_ind);
t_value
:= t_values(t_col_ind);
IF t_c_xfid >= c_type_string THEN
--STRING
l_datatype := 'String';
IF t_value IS NOT NULL THEN
t_string := REPLACE(dbms_xmlgen.convert(wb_str_ind(t_value)),
'&&amp;',
'&&#38;');
END IF;
ELSIF t_c_xfid >= c_type_date THEN
--DATE
l_datatype := 'String'; --'DateTime';
IF t_value IS NOT NULL THEN
t_string := to_char(t_value + c_date_base, 'YYYY-MM-DD');
END IF;
ELSE
--NUMBER
l_datatype := 'Number';
IF t_value IS NOT NULL THEN
t_string := to_char(t_value);
END IF;
END IF;
t_c_xfid := MOD(t_c_xfid, c_type_base);
t_mindex := t_row_ind * c_max_cols + t_col_ind;
IF wb_sheets(s).mergecols.exists(t_mindex) THEN
t_mergecell := wb_sheets(s)
.mergecells(wb_sheets(s).mergecols(t_mindex));
IF t_mergecell.colspan > 1 THEN
t_col_style := t_col_style || ' ss:MergeAcross="' ||
(t_mergecell.colspan - 1) || '"';
END IF;
IF t_mergecell.rowspan > 1 THEN
t_col_style := t_col_style || ' ss:MergeDown="' ||
(t_mergecell.rowspan - 1) || '"';
END IF;
END IF;
--如果单元格不连续
IF t_col_ind <> t_col_last + 1 THEN
t_col_style := t_col_style || ' ss:Index="' || t_col_ind || '"';
END IF;
t_col_last := t_col_ind;
IF nvl(t_r_xfid, 0) <> nvl(t_c_xfid, 0) THEN
t_col_style := t_col_style || ' ss:StyleID="s' || t_c_xfid || '"';
END IF;
--判断是否为EXCEL公式
IF substr(t_string, 1, 1) = '=' THEN
t_col_style := t_col_style || ' ss:Formula="' || t_string || '" ';
l_datatype
:= 'Number';
t_string
:= NULL;
END IF;
t_cell := '<Cell' || t_col_style || '>' || --
'<Data ss:Type="' || nvl(l_datatype, 'String') || '">' || --
t_string || '</Data></Cell>';
IF t_len > 30000 THEN
output(t_tmp);
t_tmp := NULL;
t_len := 0;
END IF;
t_tmp
:= t_tmp || t_cell;
t_len
:= t_len + lengthb(t_cell);
t_col_ind := t_values.next(t_col_ind);
END LOOP;
t_tmp := t_tmp || '</Row>' || chr(13) || chr(10);
output(t_tmp);
RETURN;
END IF;
--
IF g_output_format = 'HTML' THEN
t_r_xfid
:= 0;
t_row_style := NULL;
--
IF t_row.height IS NOT NULL THEN
t_row_style := ' height=' || to_char(t_row.height);
ELSE
t_row_style := '';
END IF;
IF t_row.xfid > 0 THEN
t_r_xfid
:= t_row.xfid;
t_row_style := t_row_style || ' class="s' || t_r_xfid || '"';
END IF;
t_tmp := t_tmp || '<tr' || t_row_style || '>';
t_len
:= lengthb(t_tmp);
t_col_ind := t_values.first();
WHILE t_col_ind IS NOT NULL LOOP
t_col_style := NULL;
t_string
:= NULL;
t_c_xfid
:= t_styles(t_col_ind);
t_value
:= t_values(t_col_ind);
IF t_c_xfid >= c_type_string THEN
--STRING
l_datatype := 'String';
IF t_value IS NOT NULL THEN
t_string := htf.escape_sc(wb_str_ind(t_value));
END IF;
ELSIF t_c_xfid >= c_type_date THEN
--DATE
l_datatype := 'Date';
IF t_value IS NOT NULL THEN
t_string := to_char(t_value + c_date_base,
'YYYY-MM-DD HH24:MI:SS');
END IF;
ELSE
--NUMBER
l_datatype := 'Number';
t_string
:= to_char(t_value);
END IF;
t_c_xfid := MOD(t_c_xfid, c_type_base);
t_mindex := t_row_ind * c_max_cols + t_col_ind;
IF wb_sheets(s).mergecols.exists(t_mindex) THEN
t_mergecell := wb_sheets(s)
.mergecells(wb_sheets(s).mergecols(t_mindex));
IF t_mergecell.colspan > 1 THEN
t_col_style := t_col_style || ' colspan=' ||
t_mergecell.colspan;
END IF;
IF t_mergecell.rowspan > 1 THEN
t_col_style := t_col_style || ' rowspan=' ||
t_mergecell.rowspan;
END IF;
END IF;
IF nvl(t_r_xfid, 0) <> nvl(t_c_xfid, 0) OR l_datatype = 'Number' THEN
t_col_style := t_col_style || ' class="s' || t_c_xfid || CASE
WHEN l_datatype = 'Number' THEN
' n0'
ELSE
''
END || '"';
END IF;
t_cell := '<td' || t_col_style || '>' || t_string || '</td>';
IF t_len > 30000 THEN
output(t_tmp);
t_tmp := NULL;
t_len := 0;
END IF;
t_tmp
:= t_tmp || t_cell;
t_len
:= t_len + lengthb(t_cell);
t_col_ind := t_values.next(t_col_ind);
END LOOP;
t_tmp := t_tmp || '</tr>' || chr(13) || chr(10);
output(t_tmp);
END IF;
END;
--XML模式时,输出头/行/foot等信息
PROCEDURE output_xml(p_type VARCHAR2 := NULL) IS
t_row_ind NUMBER;
t_col_ind NUMBER;
t_rows
tp_rows;
--
FUNCTION iif(p_flag IN BOOLEAN, p_val1 IN VARCHAR2, p_val2 IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF p_flag THEN
RETURN p_val1;
END IF;
RETURN p_val2;
END;
FUNCTION to_border(p_val IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF p_val IN ('thin') THEN
RETURN 1;
ELSIF p_val IN ('double') THEN
RETURN 2;
END IF;
RETURN 0;
END;
FUNCTION to_upper(p_val IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_val IS NOT NULL THEN
RETURN upper(substr(p_val, 1, 1)) || substr(p_val, 2);
END IF;
RETURN '';
END;
FUNCTION to_color(p_val IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF p_val IS NOT NULL THEN
RETURN '#' || substr(p_val, length(p_val) - 5);
END IF;
RETURN '';
END;
FUNCTION to_style(t_xf IN tp_xf_fmt) RETURN VARCHAR2 IS
t_border tp_border;
t_font
tp_font;
t_fill
tp_fill;
t_align
tp_alignment;
t_numfmt tp_numfmt;
l_ret
VARCHAR2(4000);
BEGIN
t_font
:= wb_fonts(t_xf.fontid);
t_fill
:= wb_fills(t_xf.fillid);
t_border := wb_borders(t_xf.borderid);
IF t_xf.numfmtid >= 164 THEN
t_numfmt := wb_numfmts(t_xf.numfmtid - 164 + 1);
ELSE
t_numfmt := NULL;
END IF;
IF t_xf.alignment IS NOT NULL THEN
t_align := wb_aligns(t_xf.alignment);
ELSE
t_align := NULL;
END IF;
IF g_output_format = 'XML' THEN
l_ret := '
<Style ss:ID="s' || t_xf.xfid || '">';
--font
IF t_font.name IS NOT NULL OR t_font.rgb IS NOT NULL OR
t_font.fontsize IS NOT NULL THEN
l_ret := l_ret || '<Font';
IF t_font.name IS NOT NULL THEN
l_ret := l_ret || ' ss:FontName="' || t_font.name || '"';
END IF;
IF t_font.rgb IS NOT NULL THEN
l_ret := l_ret || ' ss:Color="' || to_color(t_font.rgb) || '"';
END IF;
IF t_font.fontsize IS NOT NULL THEN
l_ret := l_ret || ' ss:Size="' || t_font.fontsize || '"';
END IF;
l_ret := l_ret || iif(t_font.bold, ' ss:Bold="1"', '');
l_ret := l_ret || iif(t_font.italic, ' ss:Italic="1"', '');
l_ret := l_ret ||
iif(t_font.underline, ' ss:Underline="Single"', '');
l_ret := l_ret || '/>';
END IF;
--align
IF t_align.vertical IS NOT NULL OR t_align.horizontal IS NOT NULL OR
t_align.wraptext IS NOT NULL THEN
l_ret := l_ret || '
<Alignment';
IF t_align.horizontal IS NOT NULL THEN
l_ret := l_ret || ' ss:Horizontal="' ||
to_upper(t_align.horizontal) || '"';
END IF;
IF t_align.vertical IS NOT NULL THEN
l_ret := l_ret || ' ss:Vertical="' ||
to_upper(t_align.vertical) || '"';
END IF;
IF t_align.wraptext IS NOT NULL THEN
l_ret := l_ret || ' ss:WrapText="' ||
iif(t_align.wraptext, 1, 0) || '"';
END IF;
l_ret := l_ret || '/>';
END IF;
--borders
IF t_border.top IS NOT NULL OR t_border.bottom IS NOT NULL OR
t_border.left IS NOT NULL OR t_border.right IS NOT NULL THEN
l_ret := l_ret || '<Borders>';
IF t_border.top IS NOT NULL THEN
l_ret := l_ret ||
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="' ||
to_border(t_border.top) || '"/>';
END IF;
IF t_border.bottom IS NOT NULL THEN
l_ret := l_ret ||
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="' ||
to_border(t_border.bottom) || '"/>';
END IF;
IF t_border.left IS NOT NULL THEN
l_ret := l_ret ||
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="' ||
to_border(t_border.left) || '"/>';
END IF;
IF t_border.right IS NOT NULL THEN
l_ret := l_ret ||
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="' ||
to_border(t_border.right) || '"/>';
l_ret := l_ret || '</Borders>';
END IF;
IF t_fill.fgrgb IS NOT NULL THEN
l_ret := l_ret || '<Interior ss:Color="' ||
to_color(t_fill.fgrgb) || '" ss:Pattern="' ||
nvl(to_upper(t_fill.patterntype), 'Solid') || '"/>';
END IF;
--number format
IF t_numfmt.formatcode IS NOT NULL THEN
l_ret := l_ret || '<NumberFormat ss:Format="' ||
t_numfmt.formatcode || '"/>';
END IF;
END IF;
l_ret := l_ret || '<Protection/>';
l_ret := l_ret || '</Style>';
ELSIF g_output_format = 'HTML' THEN
l_ret := chr(13) || chr(10) || '.s' || t_xf.xfid || '{';
--font
IF t_font.name IS NOT NULL OR t_font.rgb IS NOT NULL OR
t_font.fontsize IS NOT NULL THEN
IF t_font.name IS NOT NULL THEN
l_ret := l_ret || 'font-family:' || t_font.name || ';';
END IF;
IF t_font.rgb IS NOT NULL THEN
l_ret := l_ret || 'color:' || to_color(t_font.rgb) || ';';
END IF;
IF t_font.fontsize IS NOT NULL THEN
l_ret := l_ret || 'font-size:' || t_font.fontsize || 'pt;';
END IF;
l_ret := l_ret || iif(t_font.bold, 'font-weight:700;', '');
l_ret := l_ret || iif(t_font.italic, 'font-style:italic;', '');
l_ret := l_ret ||
iif(t_font.underline, 'text-decoration:underline;', '');
END IF;
--align
IF t_align.vertical IS NOT NULL OR t_align.horizontal IS NOT NULL OR
t_align.wraptext IS NOT NULL THEN
--
IF t_align.horizontal IS NOT NULL THEN
l_ret := l_ret || 'text-align:' || t_align.horizontal || ';';
END IF;
IF t_align.vertical IS NOT NULL THEN
l_ret := l_ret || 'vertical-align:' || t_align.vertical || ';';
END IF;
IF t_align.wraptext IS NOT NULL THEN
l_ret := l_ret || 'white-space:' ||
iif(t_align.wraptext, 'wrap', 'normal') || ';';
END IF;
END IF;
--borders
IF t_border.top IS NOT NULL OR t_border.bottom IS NOT NULL OR
t_border.left IS NOT NULL OR t_border.right IS NOT NULL THEN
--
l_ret := l_ret || 'border-top:' || nvl(t_border.top, 'none') || ';';
l_ret := l_ret || 'border-left:' || nvl(t_border.left, 'none') || ';';
l_ret := l_ret || 'border-bottom:' ||
nvl(t_border.bottom, 'none') || ';';
l_ret := l_ret || 'border-right:' || nvl(t_border.right, 'none') || ';';
END IF;
IF t_fill.fgrgb IS NOT NULL THEN
l_ret := l_ret || 'background:' || to_color(t_fill.fgrgb) || ';';
END IF;
--number format
l_ret := l_ret || 'mso-number-format:"' ||
nvl(t_numfmt.formatcode, 'General') || '";';
l_ret := l_ret || '}';
END IF;
RETURN l_ret;
END;
--
--
BEGIN
IF g_output_format = 'XML' AND nvl(wb_init, 0) = 0 THEN
wb_init := 1;
output('<?xml version="1.0"?>');
output('<?mso-application progid="Excel.Sheet"?>');
output('<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"');
output(' xmlns:o="urn:schemas-microsoft-com:office:office"');
output(' xmlns:x="urn:schemas-microsoft-com:office:excel"');
output(' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"');
output(' xmlns:html="http://www.w3.org/TR/REC-html40">');
output(' <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">');
output('
<Version>16.00</Version>');
output(' </DocumentProperties>');
output(' <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">');
output('
<Date1904/>');
output('
<ProtectStructure>False</ProtectStructure>');
output('
<ProtectWindows>False</ProtectWindows>');
output(' </ExcelWorkbook>');
output(' <Styles>');
--
IF profile('STYLE') IS NOT NULL THEN
output(profile('STYLE'));
profile('STYLE', NULL);
END IF;
FOR i IN 1 .. wb_cellxfs.count LOOP
output(to_style(wb_cellxfs(i)));
END LOOP;
--
output(' </Styles>');
END IF;
--output HTML
IF g_output_format = 'HTML' AND nvl(wb_init, 0) = 0 THEN
wb_init := 1;
output('<html xmlns:x="urn:schemas-microsoft-com:office:excel">');
output('<head>');
output('<meta http-equiv="Content-Type" content="text/html; charset="UTF-8">');
output('<title>' || g_filename || '</title>');
output('<style type="text/css">');
output(' body{background-color:#FFFFFF;font-family:Verdana;font-size:11pt;color:Black;}
.n0{mso-number-format:"@";text-decoration:none;text-align:right;}
.style0
{mso-number-format:General;
font-size:11 pt;
text-align:left;
vertical-align:top;
white-space:nowrap;
border:none;}
tr {mso-height-source:auto; mso-ruby-visibility:none;}
br {mso-data-placement:same-cell;}
td {mso-style-parent:style0;padding-top:0px;padding-right:0px;padding-left:0px;padding-bottom:0px;}
');
IF profile('STYLE') IS NOT NULL THEN
output(profile('STYLE'));
profile('STYLE', NULL);
END IF;
FOR i IN 1 .. wb_cellxfs.count LOOP
output(to_style(wb_cellxfs(i)));
END LOOP;
output('</style>');
output('</head>');
output('<body>');
END IF;
FOR s IN 1 .. wb_sheets.count() LOOP
--输出sheet头
IF nvl(wb_sheets(s).init, 0) = 0 THEN
IF g_output_format = 'XML' THEN
wb_sheets(s).init := 1;
output('<Worksheet ss:Name="' || wb_sheets(s).name || '">');
output('<Table ss:DefaultRowHeight="15">');
t_col_ind := wb_sheets(s).widths.first();
WHILE t_col_ind IS NOT NULL LOOP
output('<Column ss:AutoFitWidth="0" ss:Index="' || t_col_ind ||
'" ss:Width="' ||
round(wb_sheets(s).widths(t_col_ind) * 5.69, 1) ||
'"/>');
--
t_col_ind := wb_sheets(s).widths.next(t_col_ind);
END LOOP;
ELSIF g_output_format = 'HTML' AND nvl(wb_sheets(s).init, 0) = 0 THEN
wb_sheets(s).init := 1;
output('<table width="100%" border="1" cellspacing="0" cellpadding="2" style="BORDER-COLLAPSE:collapse"
bordercolorlight="#000000" bordercolordark="#000000"
id="' || wb_sheets(s).name || '">');
END IF;
END IF;
--输出Row=3
IF nvl(wb_sheets(s).init, 0) = 1 AND
nvl(p_type, 'ALL') IN ('ROW', 'ALL') THEN
t_rows
:= wb_sheets(s).rows;
t_row_ind := t_rows.first();
WHILE t_row_ind IS NOT NULL LOOP
output_row(s, t_row_ind, t_rows(t_row_ind));
t_row_ind := t_rows.next(t_row_ind);
END LOOP;
wb_sheets(s).rows.delete; --输出后清除行缓存
wb_sheets(s).mergecells.delete; --XML类型可以清除,XLSX需要保留
wb_strings.delete();
wb_str_ind.delete();
wb_str_cnt := 0;
END IF;
--关闭Sheet=2
IF nvl(wb_sheets(s).init, 0) = 1 AND
nvl(p_type, 'ALL') IN ('WORKSHEET', 'ALL') THEN
wb_sheets(s).init := 2;
IF g_output_format = 'XML' THEN
output('</Table>');
output('<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">');
IF wb_sheets(s).freeze_rows > 0 OR wb_sheets(s).freeze_cols > 0 THEN
output('<FreezePanes/><FrozenNoSplit/>');
output('<SplitHorizontal>' || wb_sheets(s).freeze_rows ||
'</SplitHorizontal>');
output('<TopRowBottomPane>' || wb_sheets(s).freeze_rows ||
'</TopRowBottomPane>');
output('<SplitVertical>' || wb_sheets(s).freeze_cols ||
'</SplitVertical>');
output('<LeftColumnRightPane>' || wb_sheets(s).freeze_cols ||
'</LeftColumnRightPane>');
END IF;
IF profile('SHEET_OPTION' || s) IS NOT NULL THEN
output(profile('SHEET_OPTION' || s));
profile('SHEET_OPTION' || s, NULL);
END IF;
IF nvl(profile('SHEET_DISPLAYZERO' || s), '1') = '0' THEN
output('<DoNotDisplayZeros/>');
profile('SHEET_DISPLAYZERO' || s, NULL);
END IF;
output('</WorksheetOptions>');
output(' </Worksheet>');
ELSIF g_output_format = 'HTML' THEN
output('</table>');
END IF;
END IF;
END LOOP;
--关闭book=1
IF nvl(wb_init, 0) = 1 AND nvl(p_type, 'ALL') IN ('WORKBOOK', 'ALL') THEN
wb_init := 2;
IF g_output_format = 'XML' THEN
output('</Workbook>');
ELSIF g_output_format = 'HTML' THEN
output('</body></html>');
END IF;
IF g_output_type <> 'B' THEN
release;
END IF;
END IF;
END;
--请求模式执行时,输出XLSX需要转换处理
PROCEDURE output_xlsx IS
PRAGMA AUTONOMOUS_TRANSACTION;
c_printer
VARCHAR2(240) := 'xlsx';
l_node_name
VARCHAR2(150);
l_outfile
VARCHAR2(1000);
l_printer
VARCHAR2(240);
l_blob
BLOB;
l_request_id
NUMBER;
l_file_len
NUMBER;
l_offset
INTEGER := 1;
l_chunk_size
BINARY_INTEGER := (4800 / 4) * 3;
l_buffer_varchar VARCHAR2(7200);
l_buffer_raw
RAW(7200);
BEGIN
l_request_id := fnd_global.conc_request_id;
IF nvl(l_request_id, 0) <= 0 THEN
RETURN;
END IF;
l_blob
:= finish;
l_file_len := dbms_lob.getlength(l_blob);
--并发程序标准输出只能为TEXT, 故对XLSX做base64编码
FOR i IN 1 .. ceil(dbms_lob.getlength(l_blob) / l_chunk_size) LOOP
dbms_lob.read(l_blob, l_chunk_size, l_offset, l_buffer_raw);
l_buffer_raw
:= utl_encode.base64_encode(l_buffer_raw);
l_buffer_varchar := utl_raw.cast_to_varchar2(l_buffer_raw);
fnd_file.put(fnd_file.output, l_buffer_varchar);
l_offset := l_offset + l_chunk_size;
END LOOP;
IF dbms_lob.istemporary(l_blob) = 1 THEN
dbms_lob.freetemporary(l_blob);
END IF;
SELECT fcr.outfile_name, fcr.outfile_node_name, fcr.printer
INTO l_outfile, l_node_name, l_printer
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = l_request_id;
--写入打印触发事件, 执行OS指令解码base64
IF lower(nvl(l_printer, '$')) <> c_printer THEN
UPDATE fnd_concurrent_requests fcr
SET fcr.printer
= c_printer,
fcr.print_style
= 'A4',
fcr.number_of_copies = 1
WHERE fcr.request_id = l_request_id;
INSERT INTO fnd_conc_pp_actions
(concurrent_request_id,
action_type,
status_s_flag,
status_w_flag,
status_f_flag,
last_update_date,
last_updated_by,
creation_date,
last_update_login,
created_by,
arguments,
completed,
number_of_copies,
sequence,
ops_instance)
SELECT l_request_id,
1,
'Y',
'N',
'N',
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.user_id,
c_printer,
'N',
1,
1,
-1
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM fnd_conc_pp_actions fc
WHERE fc.concurrent_request_id = l_request_id
AND fc.arguments = c_printer);
END IF;
--记录最终输出文件
INSERT INTO fnd_conc_req_outputs
(concurrent_request_id,
output_id,
file_type,
file_name,
file_node_name,
file_size,
action_type,
file_creation_date)
VALUES
(l_request_id,
fnd_conc_req_outputs_s.nextval,
'EXCEL',
l_outfile || '.xlsx',
l_node_name,
l_file_len,
6,
SYSDATE);
COMMIT;
END output_xlsx;
--
PROCEDURE workbook_open(p_name
IN VARCHAR2 DEFAULT NULL,
p_style IN VARCHAR2 DEFAULT NULL) IS
BEGIN
g_filename := p_name;
release('PRESERVE'); --保留open之前所增加样式
wb_init := 0;
IF g_output_type = 'B' THEN
dbms_lob.createtemporary(g_output_buffer, TRUE);
END IF;
IF p_style IS NOT NULL THEN
wb_profiles('STYLE') := p_style;
END IF;
END;
--
PROCEDURE workbook_close IS
BEGIN
IF g_output_format IN ('XML', 'HTML') THEN
output_xml('ALL');
ELSIF g_output_format = 'XLSX' AND fnd_global.conc_request_id > 0 THEN
output_xlsx;
END IF;
END;
--
PROCEDURE worksheet_open(p_sheetname VARCHAR2 := NULL) IS
t_nr PLS_INTEGER := wb_sheets.count() + 1;
BEGIN
g_cur_sheet := t_nr;
g_cur_row
:= 0;
g_cur_col
:= 0;
wb_col_fmts.delete;
wb_sheets(t_nr).name := nvl(dbms_xmlgen.convert(translate(p_sheetname,
'a/[]*:?',
'a')),
'Sheet' || t_nr);
wb_sheets(g_cur_sheet).buffer_start := dbms_lob.getlength(g_output_buffer) + 1;
END;
--兼容旧代码
PROCEDURE worksheet_close IS
BEGIN
wb_sheets(g_cur_sheet).col_fmts := wb_col_fmts;
IF g_output_format IN ('XML', 'HTML') AND
nvl(profile('OUTPUT_CACHE'), 0) = 0 THEN
output_xml('WORKSHEET');
END IF;
wb_sheets(g_cur_sheet).buffer_end := dbms_lob.getlength(g_output_buffer);
END;
--新增一行,为提升效率使用行变量接收后续CELL值
PROCEDURE row_open(p_height IN NUMBER := NULL,
p_style
IN VARCHAR2 := NULL) IS
--
l_style VARCHAR2(500) := p_style;
t_xfid
NUMBER;
BEGIN
g_cur_row := g_cur_row + 1;
g_cur_col := 1;
IF wb_sheets(g_cur_sheet).max_row >= g_cur_row AND g_cur_row > 1 THEN
wb_activerow := wb_sheets(g_cur_sheet).rows(g_cur_row);
ELSE
wb_activerow := NULL;
END IF;
IF p_height <> -9999 THEN
wb_activerow.height := p_height;
END IF;
IF l_style IS NOT NULL AND instr(l_style, '=') <= 0 THEN
t_xfid
:= get_style(p_style);
wb_activerow.xfid := t_xfid;
END IF;
END row_open;
--兼容旧代码
PROCEDURE row_close IS
t_row_ind PLS_INTEGER;
BEGIN
wb_sheets(g_cur_sheet).rows(g_cur_row) := wb_activerow;
--缓存处理: 默认为逐行输出减少内存占用.
IF nvl(profile('OUTPUT_CACHE'), 0) = 0 THEN
IF g_output_format IN ('XML', 'HTML') THEN
output_xml('ROW');
ELSIF g_output_format = 'XLSX' AND
g_cur_row >= wb_sheets(g_cur_sheet).max_row THEN
--如果跨行合并单元格,在合并最大行时一并输
t_row_ind := wb_sheets(g_cur_sheet).rows.first();
WHILE t_row_ind IS NOT NULL LOOP
output_row(g_cur_sheet,
t_row_ind,
wb_sheets(g_cur_sheet).rows(t_row_ind));
t_row_ind := wb_sheets(g_cur_sheet).rows.next(t_row_ind);
END LOOP;
wb_sheets(g_cur_sheet).rows.delete; --输出后清除行缓存
END IF;
END IF;
END row_close;
--取得当前行
FUNCTION get_current_row RETURN NUMBER IS
BEGIN
RETURN g_cur_row;
END;
--设定后续CELL的格式
PROCEDURE set_default_style(p_style IN VARCHAR2) AS
BEGIN
g_cur_style := p_style;
END;
--
--设置输出控制项或自定义输出内容
FUNCTION profile(p_key IN VARCHAR2) RETURN VARCHAR2 IS
l_key VARCHAR2(240) := upper(p_key);
BEGIN
IF wb_profiles.exists(l_key) THEN
RETURN wb_profiles(l_key);
END IF;
RETURN '';
END;
PROCEDURE profile(p_key IN VARCHAR2, p_value IN VARCHAR2) IS
BEGIN
wb_profiles(upper(p_key)) := p_value;
END;
--**************************************************************************************
--以下为xlsx输出代码
--**************************************************************************************
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;
--
--PRESERVE部分清除-保留现有样式
N 全部清除
PROCEDURE release(p_action IN VARCHAR2 := NULL) IS
t_ind NUMBER;
BEGIN
FOR s IN 1 .. wb_sheets.count() LOOP
wb_sheets(s).rows.delete();
wb_sheets(s).widths.delete();
wb_sheets(s).autofilters.delete();
wb_sheets(s).hyperlinks.delete();
wb_sheets(s).comments.delete();
wb_sheets(s).mergecells.delete();
wb_sheets(s).mergecols.delete();
wb_sheets(s).validations.delete();
END LOOP;
wb_init := 0;
wb_sheets.delete;
wb_strings.delete();
wb_str_ind.delete();
wb_str_cnt := 0;
wb_defined_names.delete();
IF dbms_lob.istemporary(g_output_buffer) = 1 THEN
dbms_lob.freetemporary(g_output_buffer);
END IF;
--保留现有样式
IF p_action = 'PRESERVE' THEN
RETURN;
END IF;
wb_profiles.delete();
wb_fonts.delete();
wb_fills.delete();
wb_borders.delete();
wb_numfmts.delete();
wb_cellxfs.delete();
wb_xfs_indexes.delete();
wb_numfmtindexes.delete();
wb_styles.delete();
IF wb_fonts.count() = 0 THEN
t_ind := get_font('Calibri');
END IF;
IF wb_fills.count() = 0 THEN
t_ind := get_fill('none');
t_ind := t_ind + get_fill('gray125');
END IF;
IF wb_borders.count() = 0 THEN
t_ind := get_border('', '', '', '');
END IF;
IF wb_aligns.count() = 0 THEN
t_ind := get_alignment('', '', NULL);
END IF;
IF t_ind IS NULL THEN
NULL;
END IF;
--初始化基础样式, 兼容旧有样式ID
--Default
add_style(p_id
=> 'Default',
p_fontname => nvl(g_default_font, '宋体'),
p_fontsize => 11);
--s21(报表标题)
add_style(p_id
=> 's21',
p_fontsize
=> 14,
p_vertical
=> 'Center',
p_horizontal => 'Center');
--s22(表格标题)
add_style('s22',
p_bold
=> 1,
p_vertical
=> 'Center',
p_horizontal => 'Center');
--s23(表格内容) 分别保留1,2,3小数位
add_style(p_id => 's23', p_numberformat => 'General');
add_style('s231', p_numberformat => '#,##0.0 ', p_copy => 's23');
add_style('s232', p_numberformat => '#,##0.00 ', p_copy => 's23');
add_style('s233', p_numberformat => '#,##0.000 ', p_copy => 's23');
--s62(s22加格线-标题)
add_style(p_id => 's62', p_border => 1, p_copy => 's22');
--s63(s23加格线-内容) 分别保留1,2,3小数位
add_style('s63', p_border => 1, p_copy => 's23');
add_style('s631', p_numberformat => '#,##0.0 ', p_copy => 's63');
add_style('s632', p_numberformat => '#,##0.00 ', p_copy => 's63');
add_style('s633', p_numberformat => '#,##0.000 ', p_copy => 's63');
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 wb_sheets(p_sheet).widths.exists(p_col) THEN
IF (wb_sheets(p_sheet)
.widths(p_col) IS NULL OR wb_sheets(p_sheet).widths(p_col) <= 0) THEN
wb_sheets(p_sheet).widths(p_col) := greatest(wb_sheets(p_sheet)
.widths(p_col),
t_width);
END IF;
ELSE
wb_sheets(p_sheet).widths(p_col) := greatest(t_width, 8.43);
END IF;
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 := wb_numfmts.count();
FOR i IN 1 .. t_cnt LOOP
IF wb_numfmts(i).formatcode = p_format THEN
t_numfmtid := wb_numfmts(i).numfmtid;
EXIT;
END IF;
END LOOP;
IF t_numfmtid IS NULL THEN
t_numfmtid := CASE
WHEN t_cnt = 0 THEN
164
ELSE
wb_numfmts(t_cnt).numfmtid + 1
END;
t_cnt := t_cnt + 1;
wb_numfmts(t_cnt).numfmtid := t_numfmtid;
wb_numfmts(t_cnt).formatcode := p_format;
wb_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;
t_font tp_font;
t_pf
tp_font;
BEGIN
t_pf.name
:= coalesce(p_name, g_default_font, '宋体');
t_pf.family
:= nvl(p_family, 2);
t_pf.fontsize
:= nvl(p_fontsize, 11);
t_pf.theme
:= nvl(p_theme, 1);
t_pf.underline := p_underline;
t_pf.italic
:= p_italic;
t_pf.bold
:= p_bold;
t_pf.rgb
:= p_rgb;
IF wb_fonts.count() > 0 THEN
FOR f IN 0 .. wb_fonts.count() - 1 LOOP
t_font := wb_fonts(f);
IF t_font.name = t_pf.name AND --
t_font.family = t_pf.family AND --
t_font.fontsize = t_pf.fontsize AND --
t_font.theme = t_pf.theme AND --
nvl(t_font.underline, FALSE) = nvl(p_underline, FALSE) AND --
nvl(t_font.italic, FALSE) = nvl(p_italic, FALSE) AND --
nvl(t_font.bold, FALSE) = nvl(p_bold, FALSE) AND --
nvl(t_font.rgb, 'x') = nvl(p_rgb, 'x') THEN
RETURN f;
END IF;
END LOOP;
END IF;
t_ind := wb_fonts.count();
wb_fonts(t_ind) := t_pf;
RETURN t_ind;
END;
--
FUNCTION get_fill(p_patterntype VARCHAR2, p_fgrgb VARCHAR2 := NULL)
RETURN PLS_INTEGER IS
l_patterntype VARCHAR2(80) := p_patterntype;
t_ind
PLS_INTEGER;
BEGIN
IF wb_fills.count() > 0 THEN
FOR f IN 0 .. wb_fills.count() - 1 LOOP
IF (nvl(wb_fills(f).patterntype, 'none') =
nvl(p_patterntype, 'none') AND
nvl(wb_fills(f).fgrgb, 'x') = nvl(upper(p_fgrgb), 'x')) THEN
RETURN f;
END IF;
END LOOP;
END IF;
l_patterntype := nvl(p_patterntype, 'none');
IF l_patterntype = 'none' AND nvl(p_fgrgb, 'x') <> 'x' THEN
l_patterntype := 'solid';
END IF;
t_ind := wb_fills.count();
wb_fills(t_ind).patterntype := l_patterntype;
wb_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 wb_borders.count() > 0 THEN
FOR b IN 0 .. wb_borders.count() - 1 LOOP
IF (nvl(wb_borders(b).top, 'x') = nvl(p_top, 'x') AND
nvl(wb_borders(b).bottom, 'x') = nvl(p_bottom, 'x') AND
nvl(wb_borders(b).left, 'x') = nvl(p_left, 'x') AND
nvl(wb_borders(b).right, 'x') = nvl(p_right, 'x')) THEN
RETURN b;
END IF;
END LOOP;
END IF;
t_ind := wb_borders.count();
wb_borders(t_ind).top := p_top;
wb_borders(t_ind).bottom := p_bottom;
wb_borders(t_ind).left := p_left;
wb_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 PLS_INTEGER IS
t_ind
NUMBER;
l_vertical
VARCHAR2(50) := p_vertical;
l_horizontal VARCHAR2(50) := p_horizontal;
BEGIN
IF l_vertical IS NOT NULL THEN
l_vertical := lower(substr(l_vertical, 1, 1)) ||
substr(l_vertical, 2);
END IF;
IF l_horizontal IS NOT NULL THEN
l_horizontal := lower(substr(l_horizontal, 1, 1)) ||
substr(l_horizontal, 2);
END IF;
IF wb_aligns.count() > 0 THEN
FOR f IN 0 .. wb_aligns.count() - 1 LOOP
IF (nvl(wb_aligns(f).vertical, 'x') = nvl(l_vertical, 'x') AND
nvl(wb_aligns(f).horizontal, 'x') = nvl(l_horizontal, 'x') AND
nvl(wb_aligns(f).wraptext, FALSE) = nvl(p_wraptext, FALSE)) THEN
RETURN f;
END IF;
END LOOP;
END IF;
t_ind := wb_aligns.count();
wb_aligns(t_ind).vertical := l_vertical;
wb_aligns(t_ind).horizontal := l_horizontal;
wb_aligns(t_ind).wraptext := p_wraptext;
RETURN t_ind;
END;
--获取或合成样式,返回的ID作为后续样式传送
--与add_style功能类同,但可以接收更复杂的参数
FUNCTION get_style(p_style
IN VARCHAR2,
p_numfmtid
PLS_INTEGER := NULL,
p_fontid
PLS_INTEGER := NULL,
p_fillid
PLS_INTEGER := NULL,
p_borderid
PLS_INTEGER := NULL,
p_alignment PLS_INTEGER := NULL) RETURN NUMBER IS
t_xfid
PLS_INTEGER;
t_xf
tp_xf_fmt;
l_new_index VARCHAR2(240);
l_id
NUMBER;
l_empty
BOOLEAN;
BEGIN
IF p_numfmtid IS NULL AND p_fontid IS NULL AND p_fillid IS NULL AND
p_borderid IS NULL AND p_alignment IS NULL THEN
l_empty := TRUE;
ELSE
l_empty := FALSE;
END IF;
IF p_style = 'Default' THEN
l_id := 0;
ELSIF substr(p_style, 1, 1) = 's' THEN
--传递样式ID
l_id := to_number(REPLACE(p_style, 's', ''));
ELSIF p_style IS NOT NULL THEN
--传递xfid,必须为数值,即直接调用get_style返回的值
t_xfid := to_number(p_style);
IF wb_cellxfs.exists(t_xfid) THEN
IF l_empty THEN
RETURN t_xfid;
END IF;
t_xf := wb_cellxfs(t_xfid);
END IF;
END IF;
--以ID名取样式
IF l_id IS NOT NULL AND wb_styles.exists(l_id) THEN
t_xf := wb_styles(l_id);
--如果没有其它参数,直接返回xfid
IF l_empty THEN
RETURN t_xf.xfid;
END IF;
END IF;
t_xf.numfmtid
:= coalesce(p_numfmtid, t_xf.numfmtid, 0);
t_xf.fontid
:= coalesce(p_fontid, t_xf.fontid, 0);
t_xf.fillid
:= coalesce(p_fillid, t_xf.fillid, 0);
t_xf.borderid
:= coalesce(p_borderid, t_xf.borderid, 0);
t_xf.alignment := coalesce(p_alignment, t_xf.alignment, 0);
/*
IF p_sheet > 0 AND
(t_xf.numfmtid > 0 AND wb_numfmtindexes.exists(t_xf.numfmtid)) THEN
set_col_width(p_sheet,
p_col,
wb_numfmts(wb_numfmtindexes(t_xf.numfmtid)).formatcode);
END IF;*/
l_new_index := t_xf.numfmtid || '_' || t_xf.fontid || '_' ||
t_xf.fillid || '_' || t_xf.borderid || '_' ||
t_xf.alignment;
IF wb_xfs_indexes.exists(l_new_index) THEN
t_xfid := wb_xfs_indexes(l_new_index);
ELSE
t_xfid := wb_cellxfs.count() + 1;
t_xf.xfid := t_xfid;
wb_cellxfs(t_xfid) := t_xf;
wb_xfs_indexes(l_new_index) := t_xfid;
END IF;
RETURN t_xfid;
END;
--新增样式
PROCEDURE add_style(p_id
IN VARCHAR2,
p_fontfamily
IN VARCHAR2 DEFAULT NULL,
p_fontname
IN VARCHAR2 DEFAULT NULL, --字体名称
p_fontsize
IN NUMBER DEFAULT NULL, --字体大小
p_fonttheme
IN NUMBER DEFAULT 1, --THEME
p_fontcolor
IN VARCHAR2 DEFAULT NULL, --字体颜色
p_backcolor
IN VARCHAR2 DEFAULT NULL, --背景颜色
p_pattern
IN VARCHAR2 DEFAULT NULL, --背景填充方式
p_bold
IN NUMBER DEFAULT NULL, --粗体
p_italic
IN NUMBER DEFAULT NULL, --斜体
p_underline
IN NUMBER DEFAULT NULL, --下划线
p_vertical
IN VARCHAR2 DEFAULT NULL, --坚直对齐
p_horizontal
IN VARCHAR2 DEFAULT NULL, --水平对齐
p_wraptext
IN NUMBER DEFAULT NULL, --自动折行
p_border
IN NUMBER DEFAULT NULL, --四边
p_left
IN NUMBER DEFAULT NULL, --或单设每边
p_top
IN NUMBER DEFAULT NULL,
p_right
IN NUMBER DEFAULT NULL,
p_bottom
IN NUMBER DEFAULT NULL,
p_numberformat IN VARCHAR2 DEFAULT NULL, --显示格式
p_copy
IN VARCHAR2 DEFAULT NULL --COPY已有样式,加上部分样式参数组合成新样式
) IS
--
l_xf
tp_xf_fmt;
l_src_xf
tp_xf_fmt;
l_src_font
tp_font;
l_src_fill
tp_fill;
l_src_border tp_border;
l_src_align
tp_alignment;
l_src_numfmt tp_numfmt;
l_id
NUMBER;
l_src_id NUMBER;
FUNCTION to_boolean(p_val IN VARCHAR2, p_default IN BOOLEAN)
RETURN BOOLEAN IS
BEGIN
IF p_val IS NULL THEN
RETURN nvl(p_default, FALSE);
ELSIF p_val IN ('1', 'TRUE') THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;
FUNCTION to_border(p_val IN NUMBER, p_default IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF p_val IS NULL THEN
RETURN p_default;
ELSIF p_val IN (1) THEN
RETURN 'thin';
ELSIF p_val > 0 THEN
RETURN 'double';
END IF;
RETURN '';
END;
FUNCTION to_color(p_val IN VARCHAR2, p_default IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
IF p_val IS NULL THEN
RETURN p_default;
ELSE
RETURN 'FF' || REPLACE(p_val, '#', '');
END IF;
END;
BEGIN
IF p_id IN ('Default') THEN
l_id := 0;
ELSE
l_id := to_number(REPLACE(p_id, 's', ''));
IF p_copy IS NOT NULL THEN
l_src_id := to_number(REPLACE(p_copy, 's', ''));
ELSE
l_src_id := 0;
END IF;
END IF;
IF wb_styles.exists(l_src_id) THEN
l_src_xf := wb_styles(l_src_id);
IF l_src_xf.fontid IS NOT NULL THEN
l_src_font := wb_fonts(l_src_xf.fontid);
END IF;
IF l_src_xf.fillid IS NOT NULL THEN
l_src_fill := wb_fills(l_src_xf.fillid);
END IF;
IF l_src_xf.borderid IS NOT NULL THEN
l_src_border := wb_borders(l_src_xf.borderid);
END IF;
IF l_src_xf.alignment IS NOT NULL THEN
l_src_align := wb_aligns(l_src_xf.alignment);
END IF;
IF l_src_xf.numfmtid > 0 THEN
l_src_numfmt := wb_numfmts(wb_numfmtindexes(l_src_xf.numfmtid));
END IF;
END IF;
l_xf.fontid := get_font(p_name
=> nvl(p_fontname, l_src_font.name),
p_family
=> nvl(p_fontfamily,
l_src_font.family),
p_fontsize
=> nvl(p_fontsize,
l_src_font.fontsize),
p_theme
=> nvl(p_fonttheme, l_src_font.theme),
p_underline => to_boolean(p_underline,
l_src_font.underline),
p_italic
=> to_boolean(p_italic,
l_src_font.italic),
p_bold
=> to_boolean(p_bold, l_src_font.bold),
p_rgb
=> to_color(p_fontcolor,
l_src_font.rgb));
l_xf.borderid := get_border(p_top
=> to_border(nvl(p_top, p_border),
l_src_border.top),
p_bottom => to_border(nvl(p_bottom, p_border),
l_src_border.bottom),
p_left
=> to_border(nvl(p_left, p_border),
l_src_border.left),
p_right
=> to_border(nvl(p_right, p_border),
l_src_border.right));
l_xf.alignment := get_alignment(p_vertical
=> nvl(p_vertical,
l_src_align.vertical),
p_horizontal => nvl(p_horizontal,
l_src_align.horizontal),
p_wraptext
=> to_boolean(p_wraptext,
l_src_align.wraptext));
l_xf.numfmtid := get_numfmt(p_format => nvl(p_numberformat,
l_src_numfmt.formatcode));
l_xf.fillid := get_fill(p_patterntype => nvl(p_pattern,
l_src_fill.patterntype),
p_fgrgb
=> to_color(p_backcolor,
l_src_fill.fgrgb));
l_xf.xfid := get_style(p_id,
l_xf.numfmtid,
l_xf.fontid,
l_xf.fillid,
l_xf.borderid,
l_xf.alignment);
l_xf.numfmtid
:= nullif(l_xf.numfmtid, 0);
l_xf.fontid
:= nullif(l_xf.fontid, 0);
l_xf.fillid
:= nullif(l_xf.fillid, 0);
l_xf.borderid
:= nullif(l_xf.borderid, 0);
l_xf.alignment := nullif(l_xf.alignment, 0);
wb_styles(l_id) := l_xf;
END;
--新增字符串,内建列表
FUNCTION add_string(p_string VARCHAR2) RETURN PLS_INTEGER IS
t_cnt PLS_INTEGER;
BEGIN
IF (p_string IS NULL) THEN
RETURN NULL;
END IF;
IF wb_strings.exists(p_string) THEN
t_cnt := wb_strings(p_string);
ELSE
t_cnt := wb_strings.count();
wb_str_ind(t_cnt) := p_string;
wb_strings(nvl(p_string, '')) := t_cnt;
END IF;
wb_str_cnt := wb_str_cnt + 1;
RETURN t_cnt;
END;
--取计算公式
FUNCTION get_string(p_type
IN VARCHAR2,
p_row
IN NUMBER := NULL,
p_col
IN NUMBER := NULL,
p_end_row IN NUMBER := NULL,
p_end_col IN NUMBER := NULL) RETURN VARCHAR2 IS
--
l_range VARCHAR2(100);
BEGIN
IF g_output_format = 'XML' THEN
IF p_col IS NULL OR p_end_col IS NULL THEN
--相对地址
l_range := 'R[' || -p_row || ']C:R[' || -nvl(p_end_row, 1) || ']C';
ELSIF p_row IS NULL OR p_end_row IS NULL THEN
l_range := 'RC[' || -p_col || ']:RC[' || -nvl(p_end_col, 1) || ']';
ELSE
l_range := 'R' || p_row || 'C' || p_col || ':R' || p_end_row || 'C' ||
p_end_col;
END IF;
ELSIF g_output_format IN ('XLSX', 'HTML') THEN
l_range := alfan_col(p_col) || p_row || ':' || alfan_col(p_end_col) ||
p_end_row;
END IF;
IF l_range IS NOT NULL AND p_type = 'SUM' THEN
l_range := '=SUM(' || l_range || ')';
END IF;
RETURN l_range;
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;
l_style PLS_INTEGER;
t_sheet PLS_INTEGER := nvl(p_sheet, wb_sheets.count());
BEGIN
t_ind := wb_sheets(t_sheet).mergecells.count() + 1;
wb_sheets(t_sheet).mergecells(t_ind).row := p_tl_row;
wb_sheets(t_sheet).mergecells(t_ind).col := p_tl_col;
wb_sheets(t_sheet).mergecells(t_ind).rowspan := p_br_row - p_tl_row + 1;
wb_sheets(t_sheet).mergecells(t_ind).colspan := p_br_col - p_tl_col + 1;
wb_sheets(t_sheet).mergecols(p_tl_row * c_max_cols + p_tl_col) := t_ind;
--补齐
IF g_output_format = 'XLSX' THEN
FOR i IN p_tl_row .. p_br_row LOOP
FOR j IN p_tl_col .. p_br_col LOOP
--当前行
IF i = p_tl_row THEN
IF j = p_tl_col THEN
l_style := wb_activerow.style(j);
ELSE
wb_activerow.value(j) := NULL;
wb_activerow.style(j) := l_style;
END IF;
ELSE
--跨行
wb_sheets(t_sheet).rows(i).value(j) := NULL;
wb_sheets(t_sheet).rows(i).style(j) := l_style;
END IF;
END LOOP;
END LOOP;
END IF;
IF p_br_row > wb_sheets(t_sheet).max_row THEN
wb_sheets(t_sheet).max_row := p_br_row;
END IF;
END;
--
PROCEDURE cell(p_content IN NUMBER,
p_style
IN VARCHAR2 := NULL,
ROW
PLS_INTEGER := NULL,
col
PLS_INTEGER := NULL,
rowspan
IN NUMBER := NULL,
colspan
IN NUMBER := NULL) IS
l_style
VARCHAR2(100) := p_style;
l_rowspan NUMBER := rowspan;
l_colspan NUMBER := colspan;
t_sheet
PLS_INTEGER := nvl(g_cur_sheet, wb_sheets.count());
BEGIN
IF nvl(ROW, 0) > 0 THEN
g_cur_row := ROW;
END IF;
IF nvl(col, 0) > 0 THEN
g_cur_col := col;
END IF;
--
IF l_style IS NULL THEN
IF wb_col_fmts.exists(g_cur_col) THEN
l_style := wb_col_fmts(g_cur_col);
ELSE
l_style := nvl(to_char(wb_activerow.xfid), g_cur_style);
END IF;
END IF;
wb_activerow.value(g_cur_col) := p_content;
wb_activerow.style(g_cur_col) := get_style(l_style);
--
IF l_colspan > 0 OR l_rowspan > 0 THEN
mergecells(g_cur_col,
g_cur_row,
g_cur_col + nvl(l_colspan, 1) - 1,
g_cur_row + nvl(l_rowspan, 1) - 1);
END IF;
--
g_cur_col := g_cur_col + nvl(l_colspan, 1);
IF g_cur_row > wb_sheets(t_sheet).max_row THEN
wb_sheets(t_sheet).max_row := g_cur_row;
END IF;
IF g_cur_col > wb_sheets(t_sheet).max_col THEN
wb_sheets(t_sheet).max_col := g_cur_col;
END IF;
END;
--
PROCEDURE cell(p_content IN VARCHAR2,
p_style
IN VARCHAR2 := NULL,
ROW
PLS_INTEGER := NULL,
col
PLS_INTEGER := NULL,
rowspan
IN NUMBER := NULL,
colspan
IN NUMBER := NULL) IS
l_style
VARCHAR2(100) := p_style;
t_sheet
PLS_INTEGER := nvl(g_cur_sheet, wb_sheets.count());
l_rowspan
NUMBER := rowspan;
l_colspan
NUMBER := colspan;
t_alignment tp_alignment;
t_xfid
PLS_INTEGER;
l_alignment NUMBER;
BEGIN
IF nvl(ROW, 0) > 0 THEN
g_cur_row := ROW;
END IF;
IF nvl(col, 0) > 0 THEN
g_cur_col := col;
END IF;
--
IF l_style IS NULL THEN
IF wb_col_fmts.exists(g_cur_col) THEN
l_style := wb_col_fmts(g_cur_col);
ELSE
l_style := nvl(to_char(wb_activerow.xfid), g_cur_style);
END IF;
END IF;
--回车折行
IF instr(p_content, chr(13)) > 0 THEN
t_xfid := get_style(l_style);
IF wb_cellxfs.exists(t_xfid) THEN
l_alignment := wb_cellxfs(t_xfid).alignment;
IF l_alignment > 0 THEN
t_alignment := wb_aligns(l_alignment);
l_alignment := get_alignment(p_vertical
=> t_alignment.vertical,
p_horizontal => t_alignment.horizontal,
p_wraptext
=> TRUE);
END IF;
END IF;
END IF;
--
t_xfid := get_style(l_style, p_alignment => l_alignment);
IF substr(p_content, 1, 1) = '=' THEN
t_xfid := t_xfid + c_type_formula;
ELSE
t_xfid := t_xfid + c_type_string;
END IF;
wb_activerow.value(g_cur_col) := add_string(nvl(p_content, ''));
wb_activerow.style(g_cur_col) := t_xfid;
IF l_colspan > 0 OR l_rowspan > 0 THEN
mergecells(g_cur_col,
g_cur_row,
g_cur_col + nvl(l_colspan, 1) - 1,
g_cur_row + nvl(l_rowspan, 1) - 1);
END IF;
--
g_cur_col := g_cur_col + nvl(l_colspan, 1);
IF g_cur_row > wb_sheets(t_sheet).max_row THEN
wb_sheets(t_sheet).max_row := g_cur_row;
END IF;
IF g_cur_col > wb_sheets(t_sheet).max_col THEN
wb_sheets(t_sheet).max_col := g_cur_col;
END IF;
END;
--
PROCEDURE cell(p_content IN DATE,
p_style
IN VARCHAR2 := NULL,
ROW
PLS_INTEGER := NULL,
col
PLS_INTEGER := NULL,
rowspan
IN NUMBER := NULL,
colspan
IN NUMBER := NULL) IS
l_style
VARCHAR2(100) := p_style;
t_numfmtid PLS_INTEGER;
l_rowspan
NUMBER := rowspan;
l_colspan
NUMBER := colspan;
t_sheet
PLS_INTEGER := nvl(g_cur_sheet, wb_sheets.count());
BEGIN
IF nvl(ROW, 0) > 0 THEN
g_cur_row := ROW;
END IF;
IF nvl(col, 0) > 0 THEN
g_cur_col := col;
END IF;
--
IF l_style IS NULL THEN
IF wb_col_fmts.exists(g_cur_col) THEN
l_style := wb_col_fmts(g_cur_col);
ELSE
l_style
:= nvl(to_char(wb_activerow.xfid), g_cur_style);
t_numfmtid := get_numfmt('yyyy/mm/dd');
END IF;
END IF;
--
wb_activerow.value(g_cur_col) := p_content - c_date_base;
wb_activerow.style(g_cur_col) := c_type_date +
get_style(l_style,
p_numfmtid => t_numfmtid);
IF l_colspan > 0 OR l_rowspan > 0 THEN
mergecells(g_cur_col,
g_cur_row,
g_cur_col + nvl(l_colspan, 1) - 1,
g_cur_row + nvl(l_rowspan, 1) - 1);
END IF;
--
g_cur_col := g_cur_col + nvl(l_colspan, 1);
IF g_cur_row > wb_sheets(t_sheet).max_row THEN
wb_sheets(t_sheet).max_row := g_cur_row;
END IF;
IF g_cur_col > wb_sheets(t_sheet).max_col THEN
wb_sheets(t_sheet).max_col := g_cur_col;
END IF;
END;
--超链接
PROCEDURE hyperlink(p_col
PLS_INTEGER,
p_row
PLS_INTEGER,
p_url
VARCHAR2,
p_value VARCHAR2 := NULL) IS
t_ind
PLS_INTEGER;
t_xfid
PLS_INTEGER;
t_sheet PLS_INTEGER := nvl(g_cur_sheet, wb_sheets.count());
BEGIN
t_xfid := get_style(p_style
=> g_cur_style,
p_fontid => get_font('Calibri',
p_theme
=> 10,
p_underline => TRUE));
cell(p_content => nvl(p_value, p_url),
p_style
=> to_char(t_xfid),
ROW
=> p_row,
col
=> p_col);
t_ind := wb_sheets(t_sheet).hyperlinks.count() + 1;
wb_sheets(t_sheet).hyperlinks(t_ind).cell := alfan_col(p_col) || p_row;
wb_sheets(t_sheet).hyperlinks(t_ind).url := p_url;
END;
--
PROCEDURE hyperlink_loc(p_col
PLS_INTEGER,
p_row
PLS_INTEGER,
p_location VARCHAR2) IS
t_ind
PLS_INTEGER;
t_xfid
PLS_INTEGER;
t_sheet PLS_INTEGER := nvl(g_cur_sheet, wb_sheets.count());
BEGIN
t_xfid := get_style(p_style
=> g_cur_style,
p_fontid => get_font('Calibri',
p_theme
=> 10,
p_underline => TRUE));
cell(p_content => '',
p_style
=> to_char(t_xfid),
ROW
=> p_row,
col
=> p_col);
t_ind := wb_sheets(t_sheet).hyperlinks.count() + 1;
wb_sheets(t_sheet).hyperlinks(t_ind).cell := alfan_col(p_col) || p_row;
wb_sheets(t_sheet).hyperlinks(t_ind).location := p_location;
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, wb_sheets.count());
BEGIN
t_ind := wb_sheets(t_sheet).comments.count() + 1;
wb_sheets(t_sheet).comments(t_ind).row := p_row;
wb_sheets(t_sheet).comments(t_ind).column := p_col;
wb_sheets(t_sheet).comments(t_ind).text := dbms_xmlgen.convert(p_text);
wb_sheets(t_sheet).comments(t_ind).author := dbms_xmlgen.convert(p_author);
wb_sheets(t_sheet).comments(t_ind).width := p_width;
wb_sheets(t_sheet).comments(t_ind).height := p_height;
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, wb_sheets.count());
BEGIN
t_ind := wb_sheets(t_sheet).validations.count() + 1;
wb_sheets(t_sheet).validations(t_ind).type := p_type;
wb_sheets(t_sheet).validations(t_ind).errorstyle := p_style;
wb_sheets(t_sheet).validations(t_ind).sqref := p_sqref;
wb_sheets(t_sheet).validations(t_ind).formula1 := p_formula1;
wb_sheets(t_sheet).validations(t_ind).error_title := p_error_title;
wb_sheets(t_sheet).validations(t_ind).error_txt := p_error_txt;
wb_sheets(t_sheet).validations(t_ind).title := p_title;
wb_sheets(t_sheet).validations(t_ind).prompt := p_prompt;
wb_sheets(t_sheet).validations(t_ind).showerrormessage := p_show_error;
IF p_formula2 IS NOT NULL THEN
NULL;
END IF;
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, wb_sheets.count());
t_sheet_name VARCHAR(100);
BEGIN
IF (wb_sheets.exists(wb_sheets.count) AND wb_sheets(wb_sheets.count)
.name IS NOT NULL) THEN
t_sheet_name := wb_sheets(wb_sheets.count).name;
ELSE
t_sheet_name := 'Sheet' || t_sheet;
END IF;
t_sheet_name := '''' || t_sheet_name || '''';
t_ind := wb_defined_names.count() + 1;
wb_defined_names(t_ind).name := p_name;
wb_defined_names(t_ind).ref := t_sheet_name || '!$' ||
alfan_col(p_tl_col) || '$' || p_tl_row || ':$' ||
alfan_col(p_br_col) || '$' || p_br_row;
wb_defined_names(t_ind).sheet := p_localsheet;
END;
--设置列宽度,支持多例eg:'1-10'
PROCEDURE set_column_width(p_col VARCHAR2, p_width NUMBER) IS
l_start NUMBER;
l_end
NUMBER;
l_pos
NUMBER;
l_col
NUMBER;
BEGIN
l_pos := instr(p_col, '-');
IF l_pos > 0 THEN
l_start := to_number(TRIM(substr(p_col, 1, l_pos - 1)));
l_end
:= to_number(TRIM(substr(p_col, l_pos + 1)));
FOR i IN l_start .. l_end LOOP
set_column_width(i, p_width);
END LOOP;
RETURN;
ELSE
l_col := to_number(p_col);
END IF;
IF l_col > 0 THEN
g_cur_col := l_col;
ELSE
g_cur_col := g_cur_col + 1;
END IF;
wb_sheets(g_cur_sheet).widths(g_cur_col) := round(p_width / 5.69, 2);
END;
--在worksheet_open之后执行,设置列样式
PROCEDURE set_column_style(p_col PLS_INTEGER, p_style VARCHAR2) IS
t_xfid PLS_INTEGER;
BEGIN
t_xfid := get_style(p_style);
wb_col_fmts(p_col) := t_xfid;
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, wb_sheets.count());
BEGIN
wb_sheets(t_sheet).freeze_rows := p_row;
wb_sheets(t_sheet).freeze_cols := p_col;
END;
--设置边界
PROCEDURE set_margin(p_left
NUMBER := NULL,
p_right
NUMBER := NULL,
p_top
NUMBER := NULL,
p_bottom NUMBER := NULL,
p_header NUMBER := NULL,
p_footer NUMBER := NULL,
p_sheet
PLS_INTEGER := NULL) IS
t_sheet PLS_INTEGER := nvl(p_sheet, wb_sheets.count());
BEGIN
wb_sheets(t_sheet).margin_left := p_left;
wb_sheets(t_sheet).margin_right := p_right;
wb_sheets(t_sheet).margin_top := p_top;
wb_sheets(t_sheet).margin_bottom := p_bottom;
wb_sheets(t_sheet).margin_header := p_header;
wb_sheets(t_sheet).margin_footer := p_footer;
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, wb_sheets.count());
BEGIN
t_ind := 1;
wb_sheets(t_sheet).autofilters(t_ind).column_start := p_column_start;
wb_sheets(t_sheet).autofilters(t_ind).column_end := p_column_end;
wb_sheets(t_sheet).autofilters(t_ind).row_start := p_row_start;
wb_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 SAVE(p_directory VARCHAR2, p_filename VARCHAR2) IS
c_amount CONSTANT BINARY_INTEGER := 32767;
l_buffer
VARCHAR2(32767);
l_chr10
PLS_INTEGER;
l_cloblen
PLS_INTEGER;
l_fhandler utl_file.file_type;
l_pos
PLS_INTEGER := 1;
BEGIN
IF g_output_format = 'XLSX' THEN
blob2file(finish, p_directory, p_filename);
ELSIF g_output_format IN ('XML', 'HTML') THEN
l_cloblen
:= dbms_lob.getlength(g_output_buffer);
l_fhandler := utl_file.fopen(p_directory, p_filename, 'W', c_amount);
WHILE l_pos < l_cloblen LOOP
l_buffer := dbms_lob.substr(g_output_buffer, c_amount, l_pos);
EXIT WHEN l_buffer IS NULL;
l_chr10 := instr(l_buffer, chr(10), -1);
IF l_chr10 != 0 THEN
l_buffer := substr(l_buffer, 1, l_chr10 - 1);
END IF;
utl_file.put_line(l_fhandler, l_buffer, TRUE);
l_pos := l_pos + least(length(l_buffer) + 1, c_amount);
END LOOP;
utl_file.fclose(l_fhandler);
release;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF utl_file.is_open(l_fhandler) THEN
utl_file.fclose(l_fhandler);
END IF;
dbms_output.put_line(dbms_utility.format_error_backtrace);
RAISE;
END SAVE;
--直接输出SQL结果,输出前可以设置标题,更宽
PROCEDURE query2sheet(p_sql
VARCHAR2,
p_column_headers VARCHAR2 DEFAULT '#COLUMN_NAME#',
p_directory
VARCHAR2 := NULL,
p_filename
VARCHAR2 := NULL,
p_startrow
NUMBER DEFAULT 1) IS
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;
l_startrow
PLS_INTEGER;
t_cur_row
PLS_INTEGER;
BEGIN
t_c := dbms_sql.open_cursor;
dbms_sql.parse(t_c, p_sql, dbms_sql.native);
dbms_sql.describe_columns2(t_c, t_col_cnt, t_desc_tab);
--Set sheet header
l_startrow := nvl(p_startrow, 1);
IF p_column_headers = '#COLUMN_NAME#' THEN
FOR c IN 1 .. t_col_cnt LOOP
cell(t_desc_tab(c).col_name, '', l_startrow, c);
END LOOP;
ELSIF p_column_headers IS NOT NULL THEN
FOR rec IN (WITH t1 AS
(SELECT rownum AS row_number
FROM dual
CONNECT BY rownum <= t_col_cnt),
t2 AS
(SELECT t1.row_number AS line_no,
regexp_substr(p_column_headers,
'[^;]+',
1,
t1.row_number) AS line_text
FROM t1)
SELECT line_no, line_text FROM t2 ORDER BY line_no) LOOP
cell(rec.line_text, NULL, l_startrow, rec.line_no);
END LOOP;
END IF;
FOR c IN 1 .. t_col_cnt LOOP
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;
--
t_cur_row := CASE
WHEN p_column_headers IS NOT NULL THEN
l_startrow + 1
ELSE
l_startrow
END;
--
t_r := dbms_sql.execute(t_c);
LOOP
t_r := dbms_sql.fetch_rows(t_c);
IF t_r > 0 THEN
row_open();
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(n_tab(i + n_tab.first()), NULL, t_cur_row + i, c);
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(d_tab(i + d_tab.first()), NULL, t_cur_row + i, c);
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(v_tab(i + v_tab.first()), NULL, t_cur_row + i, c);
END IF;
END LOOP;
v_tab.delete;
ELSE
NULL;
END CASE;
END LOOP;
row_close();
END IF;
EXIT WHEN t_r != t_bulk_size;
t_cur_row := t_cur_row + t_r;
END LOOP;
dbms_sql.close_cursor(t_c);
IF (p_directory IS NOT NULL AND p_filename IS NOT NULL) THEN
SAVE(p_directory, p_filename);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(t_c) THEN
dbms_sql.close_cursor(t_c);
END IF;
END;
--完成XLSX内容
FUNCTION finish RETURN BLOB IS
t_excel
BLOB;
t_xxx
CLOB;
t_tmp
VARCHAR2(32767 CHAR);
t_str
VARCHAR2(32767 CHAR);
t_c
NUMBER;
t_h
NUMBER;
t_w
NUMBER;
t_cw
NUMBER;
t_row_ind
PLS_INTEGER;
t_col_min
PLS_INTEGER;
t_col_max
PLS_INTEGER;
t_col_ind
PLS_INTEGER;
t_len
PLS_INTEGER;
t_alignment tp_alignment;
t_mergecell tp_mergecell;
active_rows tp_rows;
--
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 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;
--
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;
--
BEGIN
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 .. wb_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 .. wb_sheets.count() LOOP
IF wb_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>' || wb_sheets.count() || '</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="' || wb_sheets.count() ||
'" baseType="lpstr">';
FOR s IN 1 .. wb_sheets.count() LOOP
t_xxx := t_xxx || '
<vt:lpstr>' || wb_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 wb_numfmts.count() > 0 THEN
t_xxx := t_xxx || '<numFmts count="' || wb_numfmts.count() || '">';
FOR n IN 1 .. wb_numfmts.count() LOOP
t_xxx := t_xxx || '<numFmt numFmtId="' || wb_numfmts(n).numfmtid ||
'" formatCode="' || wb_numfmts(n).formatcode || '"/>';
END LOOP;
t_xxx := t_xxx || '</numFmts>';
END IF;
t_xxx := t_xxx || '<fonts count="' || wb_fonts.count() ||
'" x14ac:knownFonts="1">';
FOR f IN 0 .. wb_fonts.count() - 1 LOOP
t_xxx := t_xxx || '<font>' || CASE
WHEN wb_fonts(f).bold THEN
'<b/>'
END || CASE
WHEN wb_fonts(f).italic THEN
'<i/>'
END || CASE
WHEN wb_fonts(f).underline THEN
'<u/>'
END || '<sz val="' ||
to_char(wb_fonts(f).fontsize,
'TM9',
'NLS_NUMERIC_CHARACTERS=.,') || '"/>
<color ' || CASE
WHEN wb_fonts(f).rgb IS NOT NULL THEN
'rgb="' || wb_fonts(f).rgb
ELSE
'theme="' || wb_fonts(f).theme
END || '"/>
<name val="' || wb_fonts(f).name || '"/>
<family val="' || wb_fonts(f).family || '"/>
<scheme val="none"/>
</font>';
END LOOP;
t_xxx := t_xxx || '</fonts>
<fills count="' || wb_fills.count() || '">';
FOR f IN 0 .. wb_fills.count() - 1 LOOP
t_xxx := t_xxx || '<fill><patternFill patternType="' || wb_fills(f)
.patterntype || '">' || CASE
WHEN wb_fills(f).fgrgb IS NOT NULL THEN
'<fgColor rgb="' || wb_fills(f).fgrgb || '"/>'
END || '</patternFill></fill>';
END LOOP;
t_xxx := t_xxx || '</fills>
<borders count="' || wb_borders.count() || '">';
FOR b IN 0 .. wb_borders.count() - 1 LOOP
t_xxx := t_xxx || '<border>' || CASE
WHEN wb_borders(b).left IS NULL THEN
'<left/>'
ELSE
'<left style="' || wb_borders(b).left || '"/>'
END || CASE
WHEN wb_borders(b).right IS NULL THEN
'<right/>'
ELSE
'<right style="' || wb_borders(b).right || '"/>'
END || CASE
WHEN wb_borders(b).top IS NULL THEN
'<top/>'
ELSE
'<top style="' || wb_borders(b).top || '"/>'
END || CASE
WHEN wb_borders(b).bottom IS NULL THEN
'<bottom/>'
ELSE
'<bottom style="' || wb_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="' || (wb_cellxfs.count() + 1) || '">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>';
FOR x IN 1 .. wb_cellxfs.count() LOOP
t_xxx := t_xxx || '<xf numFmtId="' || wb_cellxfs(x).numfmtid ||
'" fontId="' || wb_cellxfs(x).fontid || '" fillId="' || wb_cellxfs(x)
.fillid || '" borderId="' || wb_cellxfs(x).borderid ||
'" applyBorder="1">';
IF (wb_cellxfs(x).alignment IS NOT NULL) THEN
t_alignment := wb_aligns(wb_cellxfs(x).alignment);
t_xxx := t_xxx || '<alignment' || CASE
WHEN t_alignment.horizontal IS NOT NULL THEN
' horizontal="' || t_alignment.horizontal || '"'
END || CASE
WHEN t_alignment.vertical IS NOT NULL THEN
' vertical="' || t_alignment.vertical || '"'
END || CASE
WHEN t_alignment.wraptext THEN
' wrapText="true"'
END || '/>';
END IF;
t_xxx := t_xxx || '</xf>';
END LOOP;
t_xxx := t_xxx || '</cellXfs>
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
<dxfs count="0"/>
<tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16"/>
<extLst>
<ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>
</ext>
</extLst>
</styleSheet>';
add1xml(t_excel, 'xl/styles.xml', t_xxx);
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="true" defaultThemeVersion="124226"/>
<bookViews>
<workbookView xWindow="120" yWindow="45" windowWidth="19155" windowHeight="4935"/>
</bookViews>
<sheets>';
FOR s IN 1 .. wb_sheets.count() LOOP
t_xxx := t_xxx || '
<sheet name="' || wb_sheets(s).name || '" sheetId="' || s ||
'" r:id="rId' || (9 + s) || '"/>';
END LOOP;
t_xxx := t_xxx || '</sheets>';
IF wb_defined_names.count() > 0 THEN
t_xxx := t_xxx || '<definedNames>';
FOR s IN 1 .. wb_defined_names.count() LOOP
t_xxx := t_xxx || '<definedName name="' || wb_defined_names(s).name || '"' || --
CASE
WHEN wb_defined_names(s).sheet IS NOT NULL THEN
' localSheetId="' || to_char(wb_defined_names(s).sheet) || '"'
END || '>' || wb_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="ＭＳ Ｐゴシック"/>
<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="ＭＳ Ｐゴシック"/>
<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);
FOR s IN 1 .. wb_sheets.count() LOOP
t_col_min
:= nvl(wb_sheets(s).min_col, 1);
t_col_max
:= nvl(wb_sheets(s).max_col, 1);
active_rows := wb_sheets(s).rows;
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) || wb_sheets(s).min_row || ':' ||
alfan_col(t_col_max) || wb_sheets(s).max_row || '"/>
<sheetViews>
<sheetView' || CASE
WHEN s = 1 THEN
' tabSelected="1"'
END || ' workbookViewId="0">';
IF wb_sheets(s).freeze_rows > 0 AND wb_sheets(s).freeze_cols > 0 THEN
t_xxx := t_xxx || ('<pane xSplit="' || wb_sheets(s).freeze_cols || '" ' ||
'ySplit="' || wb_sheets(s).freeze_rows || '" ' ||
'topLeftCell="' ||
alfan_col(wb_sheets(s).freeze_cols + 1) ||
(wb_sheets(s).freeze_rows + 1) || '" ' ||
'activePane="bottomLeft" state="frozen"/>');
ELSE
IF wb_sheets(s).freeze_rows > 0 THEN
t_xxx := t_xxx || '<pane ySplit="' || wb_sheets(s).freeze_rows ||
'" topLeftCell="A' || (wb_sheets(s).freeze_rows + 1) ||
'" activePane="bottomLeft" state="frozen"/>';
END IF;
IF wb_sheets(s).freeze_cols > 0 THEN
t_xxx := t_xxx || '<pane xSplit="' || wb_sheets(s).freeze_cols ||
'" topLeftCell="' ||
alfan_col(wb_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 wb_sheets(s).widths.count() > 0 THEN
t_xxx
:= t_xxx || '<cols>';
t_col_ind := wb_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(wb_sheets(s).widths(t_col_ind)) ||
'" customWidth="1"/>';
t_col_ind := wb_sheets(s).widths.next(t_col_ind);
END LOOP;
t_xxx := t_xxx || '</cols>';
END IF;
t_xxx := t_xxx || '<sheetData>';
--
IF nvl(wb_sheets(s).init, 0) = 0 AND active_rows.count > 0 THEN
wb_sheets(s).init := 1;
wb_sheets(s).buffer_start := dbms_lob.getlength(g_output_buffer) + 1;
t_row_ind := active_rows.first();
WHILE t_row_ind IS NOT NULL LOOP
output_row(s, t_row_ind, active_rows(t_row_ind));
t_row_ind := active_rows.next(t_row_ind);
END LOOP;
wb_sheets(s).buffer_end := dbms_lob.getlength(g_output_buffer);
END IF;
IF wb_sheets(s).buffer_end > wb_sheets(s).buffer_start THEN
dbms_lob.copy(dest_lob
=> t_xxx,
src_lob
=> g_output_buffer,
amount
=> wb_sheets(s)
.buffer_end - wb_sheets(s).buffer_start + 1,
dest_offset => dbms_lob.getlength(t_xxx) + 1,
src_offset
=> wb_sheets(s).buffer_start);
END IF;
dbms_lob.append(t_xxx, '</sheetData>');
--
FOR a IN 1 .. wb_sheets(s).autofilters.count() LOOP
t_xxx := t_xxx || '<autoFilter ref="' ||
alfan_col(nvl(wb_sheets(s).autofilters(a).column_start,
t_col_min)) ||
nvl(wb_sheets(s).autofilters(a).row_start,
wb_sheets(s).min_row) || ':' ||
alfan_col(coalesce(wb_sheets(s).autofilters(a).column_end,
wb_sheets(s).autofilters(a).column_start,
t_col_max)) ||
nvl(wb_sheets(s).autofilters(a).row_end,
wb_sheets(s).max_row) || '"/>';
END LOOP;
IF wb_sheets(s).mergecells.count() > 0 THEN
t_xxx := t_xxx || '<mergeCells count="' ||
to_char(wb_sheets(s).mergecells.count()) || '">';
FOR m IN 1 .. wb_sheets(s).mergecells.count() LOOP
t_mergecell := wb_sheets(s).mergecells(m);
t_xxx
:= t_xxx || '<mergeCell ref="' || --
alfan_col(t_mergecell.col) ||
to_char(t_mergecell.row) || ':' || --
alfan_col(t_mergecell.col + t_mergecell.colspan - 1) || --
to_char(t_mergecell.row + t_mergecell.rowspan - 1) ||
'"/>';
END LOOP;
t_xxx := t_xxx || '</mergeCells>';
END IF;
--
IF wb_sheets(s).validations.count() > 0 THEN
t_xxx := t_xxx || '<dataValidations count="' ||
to_char(wb_sheets(s).validations.count()) || '">';
FOR m IN 1 .. wb_sheets(s).validations.count() LOOP
t_xxx := t_xxx || '<dataValidation' || ' type="' || wb_sheets(s).validations(m).type || '"' ||
' errorStyle="' || wb_sheets(s).validations(m)
.errorstyle || '"' || ' allowBlank="' || CASE
WHEN nvl(wb_sheets(s).validations(m).allowblank, TRUE) THEN
'1'
ELSE
'0'
END || '"' || ' sqref="' || wb_sheets(s).validations(m)
.sqref || '"';
IF wb_sheets(s).validations(m).prompt IS NOT NULL THEN
t_xxx := t_xxx || ' showInputMessage="1" prompt="' || wb_sheets(s).validations(m)
.prompt || '"';
IF wb_sheets(s).validations(m).title IS NOT NULL THEN
t_xxx := t_xxx || ' promptTitle="' || wb_sheets(s).validations(m)
.title || '"';
END IF;
END IF;
IF wb_sheets(s).validations(m).showerrormessage THEN
t_xxx := t_xxx || ' showErrorMessage="1"';
IF wb_sheets(s).validations(m).error_title IS NOT NULL THEN
t_xxx := t_xxx || ' errorTitle="' || wb_sheets(s).validations(m)
.error_title || '"';
END IF;
IF wb_sheets(s).validations(m).error_txt IS NOT NULL THEN
t_xxx := t_xxx || ' error="' || wb_sheets(s).validations(m)
.error_txt || '"';
END IF;
END IF;
t_xxx := t_xxx || '>';
IF wb_sheets(s).validations(m).formula1 IS NOT NULL THEN
t_xxx := t_xxx || '<formula1>' || wb_sheets(s).validations(m)
.formula1 || '</formula1>';
END IF;
IF wb_sheets(s).validations(m).formula2 IS NOT NULL THEN
t_xxx := t_xxx || '<formula2>' || wb_sheets(s).validations(m)
.formula2 || '</formula2>';
END IF;
t_xxx := t_xxx || '</dataValidation>';
END LOOP;
t_xxx := t_xxx || '</dataValidations>';
END IF;
--
IF wb_sheets(s).hyperlinks.count() > 0 THEN
t_xxx := t_xxx || '<hyperlinks>';
FOR h IN 1 .. wb_sheets(s).hyperlinks.count() LOOP
IF (wb_sheets(s).hyperlinks(h).location IS NOT NULL) THEN
t_xxx := t_xxx || '<hyperlink ref="' || wb_sheets(s).hyperlinks(h).cell ||
'" location="' || wb_sheets(s).hyperlinks(h).location ||
'"/>';
ELSE
t_xxx := t_xxx || '<hyperlink ref="' || wb_sheets(s).hyperlinks(h).cell ||
'" r:id="rId' || to_char(h) || '"/>';
END IF;
END LOOP;
t_xxx := t_xxx || '</hyperlinks>';
END IF;
t_xxx := t_xxx || '<pageMargins left="' ||
to_char(nvl(wb_sheets(s).margin_left, 0.7)) || '" right="' ||
to_char(nvl(wb_sheets(s).margin_right, 0.7)) || '" top="' ||
to_char(nvl(wb_sheets(s).margin_top, 0.75)) || '" bottom="' ||
to_char(nvl(wb_sheets(s).margin_bottom, 0.75)) ||
'" header="' ||
to_char(nvl(wb_sheets(s).margin_header, 0.3)) ||
'" footer="' ||
to_char(nvl(wb_sheets(s).margin_footer, 0.3)) || '"/>';
IF wb_sheets(s).comments.count() > 0 THEN
t_xxx := t_xxx || '<legacyDrawing r:id="rId' ||
(wb_sheets(s).hyperlinks.count() + 1) || '"/>';
END IF;
--
t_xxx := t_xxx || '</worksheet>';
add1xml(t_excel, 'xl/worksheets/sheet' || s || '.xml', t_xxx);
IF wb_sheets(s)
.hyperlinks.count() > 0 OR wb_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 wb_sheets(s).comments.count() > 0 THEN
t_xxx := t_xxx || '<Relationship Id="rId' ||
(wb_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' ||
(wb_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 .. wb_sheets(s).hyperlinks.count() LOOP
t_xxx := t_xxx || '<Relationship Id="rId' || to_char(h) ||
'" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="' || wb_sheets(s).hyperlinks(h).url ||
'" TargetMode="External"/>';
END LOOP;
t_xxx := t_xxx || '</Relationships>';
add1xml(t_excel,
'xl/worksheets/_rels/sheet' || to_char(s) || '.xml.rels',
t_xxx);
END IF;
--
IF wb_sheets(s).comments.count() > 0 THEN
DECLARE
cnt
PLS_INTEGER;
author_ind tp_author;
BEGIN
authors.delete();
FOR c IN 1 .. wb_sheets(s).comments.count() LOOP
authors(wb_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 .. wb_sheets(s).comments.count() LOOP
t_xxx := t_xxx || '<comment ref="' ||
alfan_col(wb_sheets(s).comments(c).column) ||
to_char(wb_sheets(s).comments(c)
.row || '" authorId="' ||
authors(wb_sheets(s).comments(c).author)) || '">
<text>';
IF wb_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">' || wb_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 wb_sheets(s).comments(c).author IS NOT NULL THEN
'
'
END || wb_sheets(s).comments(c).text ||
'</t></r></text></comment>';
END LOOP;
t_xxx := t_xxx || '</commentList></comments>';
add1xml(t_excel, 'xl/comments' || to_char(s) || '.xml', t_xxx);
t_xxx := '<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
<o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="2"/></o:shapelayout>
<v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe"><v:stroke joinstyle="miter"/><v:path gradientshapeok="t" o:connecttype="rect"/></v:shapetype>';
FOR c IN 1 .. wb_sheets(s).comments.count() LOOP
t_xxx := t_xxx || '<v:shape id="_x0000_s' || to_char(c) ||
'" type="#_x0000_t202"
style="position:absolute;margin-left:35.25pt;margin-top:3pt;z-index:' ||
to_char(c) ||
';visibility:hidden;" fillcolor="#ffffe1" o:insetmode="auto">
<v:fill color2="#ffffe1"/><v:shadow on="t" color="black" obscured="t"/><v:path o:connecttype="none"/>
<v:textbox style="mso-direction-alt:auto"><div style="text-align:left"></div></v:textbox>
<x:ClientData ObjectType="Note"><x:MoveWithCells/><x:SizeWithCells/>';
t_w
:= wb_sheets(s).comments(c).width;
t_c
:= 1;
LOOP
IF wb_sheets(s)
.widths.exists(wb_sheets(s).comments(c).column + t_c) THEN
t_cw := 256 * wb_sheets(s)
.widths(wb_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
:= wb_sheets(s).comments(c).height;
t_xxx := t_xxx ||
to_char('<x:Anchor>' || wb_sheets(s).comments(c).column ||
',15,' || wb_sheets(s).comments(c).row || ',30,' ||
(wb_sheets(s).comments(c).column + t_c - 1) || ',' ||
round(t_w) || ',' || (wb_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>' ||
(wb_sheets(s).comments(c).row - 1) ||
'</x:Row><x:Column>' ||
(wb_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 .. wb_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="' ||
wb_str_cnt || '" uniqueCount="' || wb_strings.count() || '">';
t_tmp := NULL;
FOR i IN 0 .. wb_str_ind.count() - 1 LOOP
/* if left space */
IF (substr(wb_str_ind(i), 1, 1) = chr(32)) THEN
t_str := '<si><t xml:space="preserve">';
ELSE
t_str := '<si><t>';
END IF;
t_str := t_str ||
dbms_xmlgen.convert(substr(wb_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);
finish_zip(t_excel);
release;
RETURN t_excel;
END;
--下载
FUNCTION download(p_file_name
IN VARCHAR2 DEFAULT NULL,
p_content_type IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
--
l_db_file
NUMBER;
l_return_status VARCHAR2(30);
l_msg_count
NUMBER;
l_msg_data
VARCHAR2(4000);
l_file_name
VARCHAR2(240) := p_file_name;
l_content_type
VARCHAR2(240) := nvl(p_content_type,
'application/excel');
l_url
VARCHAR2(1000);
l_bfile
BLOB;
BEGIN
IF l_file_name IS NULL THEN
l_file_name := nvl(g_filename, 'export') ||
to_char(SYSDATE, 'MMDDHH24MISS') || '.xls';
END IF;
l_db_file := fnd_lobs_s.nextval;
IF g_output_format = 'XLSX' THEN
l_bfile := finish;
INSERT INTO fnd_lobs
(file_id,
file_name,
file_content_type,
upload_date,
expiration_date,
program_name,
program_tag,
file_data,
LANGUAGE,
oracle_charset,
file_format)
VALUES
(l_db_file,
l_file_name,
l_content_type,
SYSDATE,
SYSDATE + 0.1,
'EXPORT',
NULL,
l_bfile,
userenv('LANG'),
fnd_gfm.iana_to_oracle(fnd_gfm.get_iso_charset),
fnd_gfm.set_file_format(p_content_type));
--
COMMIT;
--
l_url := fnd_gfm.construct_download_url2(fnd_web_config.gfm_agent,
l_db_file,
TRUE);
IF dbms_lob.istemporary(l_bfile) = 1 THEN
dbms_lob.freetemporary(l_bfile);
END IF;
ELSE
--
BEGIN
fnd_gfm.clob_to_blob(my_clob
=> g_output_buffer,
file_name
=> p_file_name,
fid
=> l_db_file,
file_content_type => p_content_type,
x_return_status
=> l_return_status,
x_msg_count
=> l_msg_count,
x_msg_data
=> l_msg_data);
release;
UPDATE fnd_lobs t
SET t.program_name
= 'cux_excel',
t.expiration_date = SYSDATE + 0.1
WHERE t.file_id = l_db_file;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
l_url := fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,
l_db_file,
TRUE);
END IF;
RETURN l_url;
END;
--取当前Buffer
FUNCTION get_buffer RETURN CLOB IS
BEGIN
RETURN g_output_buffer;
END;
BEGIN
release('N');
END;

2. 样例

 BEGIN
Cux_Excel.Set_Output_Format('XLSX');
--创建workbook
Cux_Excel.Workbook_Open;
Cux_Excel.Add_Style(p_Id => 's628', p_Border => 1, p_Copy => 's22', p_Horizontal => 'Right');
FOR a IN Cur_Org LOOP
Cux_Excel.Worksheet_Open(a.Organization_Code);
--设置列宽,可以为单列或范围eg: 5 或 5-20z
Cux_Excel.Set_Column_Width(1, 150);
Cux_Excel.Set_Column_Width(2, 150);
Cux_Excel.Set_Column_Width(3, 120);
Cux_Excel.Set_Column_Width(4, 150);
Cux_Excel.Set_Column_Width(5, 150);
Cux_Excel.Set_Column_Width(6, 150);
Cux_Excel.Set_Column_Width(7, 150);
Cux_Excel.Set_Column_Width(8, 150);
Cux_Excel.Set_Column_Width(9, 150);
Cux_Excel.Set_Column_Width(10, 150);
Cux_Excel.Set_Column_Width(11, 150);
Cux_Excel.Row_Open;
Cux_Excel.Cell('XXCUS_采购订单信息汇总统计', 's22', Colspan => 11);
Cux_Excel.Row_Close;
Cux_Excel.Set_Default_Style('s62');
Cux_Excel.Row_Open;
Cux_Excel.Cell('物料编码');
Cux_Excel.Cell('物料描述');
Cux_Excel.Cell('供应商');
Cux_Excel.Cell('库存组织');
Cux_Excel.Cell('子库存');
Cux_Excel.Cell('单价');
Cux_Excel.Cell('接收时间段');
Cux_Excel.Cell('到货数量');
Cux_Excel.Cell('退货数量');
Cux_Excel.Cell('ASN数量');
Cux_Excel.Cell('可接收数量');
Cux_Excel.Row_Close;
Cux_Excel.Set_Default_Style('s63');
FOR Rec IN Cur_Data(a.Organization_Id) LOOP
Cux_Excel.Row_Open;
Cux_Excel.Cell(Rec.Item_Code);
Cux_Excel.Cell(Rec.Item_Desc);
Cux_Excel.Cell(Rec.Vendor_Name);
Cux_Excel.Cell(Rec.Organization_Code);
Cux_Excel.Cell(Rec.Subinventory);
Cux_Excel.Cell(Rec.Unit_Price);
Cux_Excel.Cell((To_Char(Rec.Transaction_Date_From, 'YYYY-MM-DD') || '~' ||
To_Char(Rec.Transaction_Date_To, 'YYYY-MM-DD')));
Cux_Excel.Cell(Rec.Receive_Quantity);
Cux_Excel.Cell(Rec.Return_Quantity);
Cux_Excel.Cell(Rec.Quantity_Shipped);
Cux_Excel.Cell((Nvl(Rec.Po_Quantity, 0) - Nvl(Rec.Receive_Quantity, 0) +
Nvl(Rec.Return_Quantity, 0) - Nvl(Rec.Quantity_Shipped, 0)));
Cux_Excel.Row_Close;
END LOOP;
--结束worksheet
Cux_Excel.Worksheet_Close;
END LOOP;
--结束workbook
Cux_Excel.Workbook_Close;
EXCEPTION
WHEN OTHERS THEN
Errbuf
:= Dbms_Utility.Format_Error_Backtrace || ';' || SQLERRM;
Retcode := '2';
END;