CREATE OR REPLACE PACKAGE BODY APEXHPPL.XXTH_EXPORT_XLSX
IS
-- Program : XXTH_EXPORT_XLSX
-- Purpose : Package to Generate the excel output
-- |Version Date Author Remarks
-- |------- -------- --------------- ---------------------------------------------------------------------------------------
-- |
-- |V1.0 04-Nov-22 Ajith Initial Version
--|V2.0 05-Nov-22 Ajith include the number formar id and row start,text bold parameters
-- |------- -------- --------------- ---------------------------------------------------------------------------------------
C_LOCAL_FILE_HEADER CONSTANT RAW (4) := HEXTORAW ('504B0304');
-- Local file header signature
C_END_OF_CENTRAL_DIRECTORY CONSTANT RAW (4) := HEXTORAW ('504B0506');
-- End of central directory signature
--
TYPE TP_XF_FMT IS RECORD
(
NUMFMTID PLS_INTEGER,
FONTID PLS_INTEGER,
FILLID PLS_INTEGER,
BORDERID PLS_INTEGER,
ALIGNMENT TP_ALIGNMENT
);
TYPE TP_COL_FMTS IS TABLE OF TP_XF_FMT
INDEX BY PLS_INTEGER;
TYPE TP_ROW_FMTS IS TABLE OF TP_XF_FMT
INDEX BY PLS_INTEGER;
TYPE TP_WIDTHS IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE TP_CELL IS RECORD
(
VALUE NUMBER,
STYLE VARCHAR2 (50)
);
TYPE TP_CELLS IS TABLE OF TP_CELL
INDEX BY PLS_INTEGER;
TYPE TP_ROWS IS TABLE OF TP_CELLS
INDEX BY PLS_INTEGER;
TYPE TP_AUTOFILTER IS RECORD
(
COLUMN_START PLS_INTEGER,
COLUMN_END PLS_INTEGER,
ROW_START PLS_INTEGER,
ROW_END PLS_INTEGER
);
TYPE TP_AUTOFILTERS IS TABLE OF TP_AUTOFILTER
INDEX BY PLS_INTEGER;
TYPE TP_HYPERLINK IS RECORD
(
CELL VARCHAR2 (10),
URL VARCHAR2 (1000)
);
TYPE TP_HYPERLINKS IS TABLE OF TP_HYPERLINK
INDEX BY PLS_INTEGER;
SUBTYPE TP_AUTHOR IS VARCHAR2 (32767 CHAR);
TYPE TP_AUTHORS IS TABLE OF PLS_INTEGER
INDEX BY TP_AUTHOR;
AUTHORS TP_AUTHORS;
TYPE TP_COMMENT IS RECORD
(
TEXT VARCHAR2 (32767 CHAR),
AUTHOR TP_AUTHOR,
ROW PLS_INTEGER,
COLUMN PLS_INTEGER,
WIDTH PLS_INTEGER,
HEIGHT PLS_INTEGER
);
TYPE TP_COMMENTS IS TABLE OF TP_COMMENT
INDEX BY PLS_INTEGER;
TYPE TP_MERGECELLS IS TABLE OF VARCHAR2 (21)
INDEX BY PLS_INTEGER;
TYPE TP_VALIDATION IS RECORD
(
TYPE VARCHAR2 (10),
ERRORSTYLE VARCHAR2 (32),
SHOWINPUTMESSAGE BOOLEAN,
PROMPT VARCHAR2 (32767 CHAR),
TITLE VARCHAR2 (32767 CHAR),
ERROR_TITLE VARCHAR2 (32767 CHAR),
ERROR_TXT VARCHAR2 (32767 CHAR),
SHOWERRORMESSAGE BOOLEAN,
FORMULA1 VARCHAR2 (32767 CHAR),
FORMULA2 VARCHAR2 (32767 CHAR),
ALLOWBLANK BOOLEAN,
SQREF VARCHAR2 (32767 CHAR)
);
TYPE TP_VALIDATIONS IS TABLE OF TP_VALIDATION
INDEX BY PLS_INTEGER;
TYPE TP_SHEET IS RECORD
(
ROWS TP_ROWS,
WIDTHS TP_WIDTHS,
NAME VARCHAR2 (100),
FREEZE_ROWS PLS_INTEGER,
FREEZE_COLS PLS_INTEGER,
AUTOFILTERS TP_AUTOFILTERS,
HYPERLINKS TP_HYPERLINKS,
COL_FMTS TP_COL_FMTS,
ROW_FMTS TP_ROW_FMTS,
COMMENTS TP_COMMENTS,
MERGECELLS TP_MERGECELLS,
VALIDATIONS TP_VALIDATIONS
);
TYPE TP_SHEETS IS TABLE OF TP_SHEET
INDEX BY PLS_INTEGER;
TYPE TP_NUMFMT IS RECORD
(
NUMFMTID PLS_INTEGER,
FORMATCODE VARCHAR2 (100)
);
TYPE TP_NUMFMTS IS TABLE OF TP_NUMFMT
INDEX BY PLS_INTEGER;
TYPE TP_FILL IS RECORD
(
PATTERNTYPE VARCHAR2 (30),
FGRGB VARCHAR2 (8)
);
TYPE TP_FILLS IS TABLE OF TP_FILL
INDEX BY PLS_INTEGER;
TYPE TP_CELLXFS IS TABLE OF TP_XF_FMT
INDEX BY PLS_INTEGER;
TYPE TP_FONT IS RECORD
(
NAME VARCHAR2 (100),
FAMILY PLS_INTEGER,
FONTSIZE NUMBER,
THEME PLS_INTEGER,
RGB VARCHAR2 (8),
UNDERLINE BOOLEAN,
ITALIC BOOLEAN,
BOLD BOOLEAN
);
TYPE TP_FONTS IS TABLE OF TP_FONT
INDEX BY PLS_INTEGER;
TYPE TP_BORDER IS RECORD
(
TOP VARCHAR2 (17),
BOTTOM VARCHAR2 (17),
LEFT VARCHAR2 (17),
RIGHT VARCHAR2 (17)
);
TYPE TP_BORDERS IS TABLE OF TP_BORDER
INDEX BY PLS_INTEGER;
TYPE TP_NUMFMTINDEXES IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
TYPE TP_STRINGS IS TABLE OF PLS_INTEGER
INDEX BY VARCHAR2 (32767 CHAR);
TYPE TP_STR_IND IS TABLE OF VARCHAR2 (32767 CHAR)
INDEX BY PLS_INTEGER;
TYPE TP_DEFINED_NAME IS RECORD
(
NAME VARCHAR2 (32767 CHAR),
REF VARCHAR2 (32767 CHAR),
SHEET PLS_INTEGER
);
TYPE TP_DEFINED_NAMES IS TABLE OF TP_DEFINED_NAME
INDEX BY PLS_INTEGER;
TYPE TP_BOOK IS RECORD
(
SHEETS TP_SHEETS,
STRINGS TP_STRINGS,
STR_IND TP_STR_IND,
STR_CNT PLS_INTEGER := 0,
FONTS TP_FONTS,
FILLS TP_FILLS,
BORDERS TP_BORDERS,
NUMFMTS TP_NUMFMTS,
CELLXFS TP_CELLXFS,
NUMFMTINDEXES TP_NUMFMTINDEXES,
DEFINED_NAMES TP_DEFINED_NAMES
);
WORKBOOK TP_BOOK;
--
PROCEDURE BLOB2FILE (P_BLOB BLOB,
P_DIRECTORY VARCHAR2:= 'MY_DIR',
P_FILENAME VARCHAR2:= 'my.xlsx')
IS
T_FH UTL_FILE.FILE_TYPE;
T_LEN PLS_INTEGER := 32767;
BEGIN
T_FH := UTL_FILE.FOPEN (P_DIRECTORY, P_FILENAME, 'wb');
FOR I IN 0 .. TRUNC ((DBMS_LOB.GETLENGTH (P_BLOB) - 1) / T_LEN)
LOOP
UTL_FILE.PUT_RAW (T_FH,
DBMS_LOB.SUBSTR (P_BLOB, T_LEN, I * T_LEN + 1));
END LOOP;
UTL_FILE.FCLOSE (T_FH);
END;
--
FUNCTION RAW2NUM (P_RAW RAW, P_LEN INTEGER, P_POS INTEGER)
RETURN NUMBER
IS
BEGIN
RETURN UTL_RAW.CAST_TO_BINARY_INTEGER (
UTL_RAW.SUBSTR (P_RAW, P_POS, P_LEN),
UTL_RAW.LITTLE_ENDIAN);
END;
--
FUNCTION LITTLE_ENDIAN (P_BIG NUMBER, P_BYTES PLS_INTEGER:= 4)
RETURN RAW
IS
BEGIN
RETURN UTL_RAW.SUBSTR (
UTL_RAW.CAST_FROM_BINARY_INTEGER (P_BIG,
UTL_RAW.LITTLE_ENDIAN),
1,
P_BYTES);
END;
--
FUNCTION BLOB2NUM (P_BLOB BLOB, P_LEN INTEGER, P_POS INTEGER)
RETURN NUMBER
IS
BEGIN
RETURN UTL_RAW.CAST_TO_BINARY_INTEGER (
DBMS_LOB.SUBSTR (P_BLOB, P_LEN, P_POS),
UTL_RAW.LITTLE_ENDIAN);
END;
--
PROCEDURE ADD1FILE (P_ZIPPED_BLOB IN OUT BLOB,
P_NAME VARCHAR2,
P_CONTENT BLOB)
IS
T_NOW DATE;
T_BLOB BLOB;
T_LEN INTEGER;
T_CLEN INTEGER;
T_CRC32 RAW (4) := HEXTORAW ('00000000');
T_COMPRESSED BOOLEAN := FALSE;
T_NAME RAW (32767);
BEGIN
T_NOW := SYSDATE;
T_LEN := NVL (DBMS_LOB.GETLENGTH (P_CONTENT), 0);
IF T_LEN > 0
THEN
T_BLOB := UTL_COMPRESS.LZ_COMPRESS (P_CONTENT);
T_CLEN := DBMS_LOB.GETLENGTH (T_BLOB) - 18;
T_COMPRESSED := T_CLEN < T_LEN;
T_CRC32 := DBMS_LOB.SUBSTR (T_BLOB, 4, T_CLEN + 11);
END IF;
IF NOT T_COMPRESSED
THEN
T_CLEN := T_LEN;
T_BLOB := P_CONTENT;
END IF;
IF P_ZIPPED_BLOB IS NULL
THEN
DBMS_LOB.CREATETEMPORARY (P_ZIPPED_BLOB, TRUE);
END IF;
T_NAME := UTL_I18N.STRING_TO_RAW (P_NAME, 'AL32UTF8');
DBMS_LOB.APPEND (P_ZIPPED_BLOB,
UTL_RAW.CONCAT (
C_LOCAL_FILE_HEADER -- Local file header signature
,
HEXTORAW ('1400') -- version 2.0
,
CASE
WHEN T_NAME =
UTL_I18N.STRING_TO_RAW (P_NAME,
'US8PC437')
THEN
HEXTORAW ('0000') -- no General purpose bits
ELSE
HEXTORAW ('0008') -- set Language encoding flag (EFS)
END,
CASE
WHEN T_COMPRESSED THEN HEXTORAW ('0800') -- deflate
ELSE HEXTORAW ('0000') -- stored
END,
LITTLE_ENDIAN (
TO_NUMBER (TO_CHAR (T_NOW, 'ss')) / 2
+ TO_NUMBER (TO_CHAR (T_NOW, 'mi')) * 32
+ TO_NUMBER (TO_CHAR (T_NOW, 'hh24')) * 2048,
2) -- File last modification time
,
LITTLE_ENDIAN (
TO_NUMBER (TO_CHAR (T_NOW, 'dd'))
+ TO_NUMBER (TO_CHAR (T_NOW, 'mm')) * 32
+ ( TO_NUMBER (TO_CHAR (T_NOW, 'yyyy'))
- 1980)
* 512,
2) -- File last modification date
,
T_CRC32 -- CRC-32
,
LITTLE_ENDIAN (T_CLEN) -- compressed size
,
LITTLE_ENDIAN (T_LEN) -- uncompressed size
,
LITTLE_ENDIAN (UTL_RAW.LENGTH (T_NAME), 2) -- File name length
,
HEXTORAW ('0000') -- Extra field length
,
T_NAME -- File name
));
IF T_COMPRESSED
THEN
DBMS_LOB.COPY (P_ZIPPED_BLOB,
T_BLOB,
T_CLEN,
DBMS_LOB.GETLENGTH (P_ZIPPED_BLOB) + 1,
11); -- compressed content
ELSIF T_CLEN > 0
THEN
DBMS_LOB.COPY (P_ZIPPED_BLOB,
T_BLOB,
T_CLEN,
DBMS_LOB.GETLENGTH (P_ZIPPED_BLOB) + 1,
1); -- content
END IF;
IF DBMS_LOB.ISTEMPORARY (T_BLOB) = 1
THEN
DBMS_LOB.FREETEMPORARY (T_BLOB);
END IF;
END;
--
PROCEDURE FINISH_ZIP (P_ZIPPED_BLOB IN OUT BLOB)
IS
T_CNT PLS_INTEGER := 0;
T_OFFS INTEGER;
T_OFFS_DIR_HEADER INTEGER;
T_OFFS_END_HEADER INTEGER;
T_COMMENT RAW (32767)
:= UTL_RAW.CAST_TO_RAW ('Implementation by Anton Scheffer');
BEGIN
T_OFFS_DIR_HEADER := DBMS_LOB.GETLENGTH (P_ZIPPED_BLOB);
T_OFFS := 1;
WHILE DBMS_LOB.SUBSTR (P_ZIPPED_BLOB,
UTL_RAW.LENGTH (C_LOCAL_FILE_HEADER),
T_OFFS) =
C_LOCAL_FILE_HEADER
LOOP
T_CNT := T_CNT + 1;
DBMS_LOB.APPEND (P_ZIPPED_BLOB,
UTL_RAW.CONCAT (
HEXTORAW ('504B0102')-- Central directory file header signature
,
HEXTORAW ('1400') -- version 2.0
,
DBMS_LOB.SUBSTR (P_ZIPPED_BLOB,
26,
T_OFFS + 4),
HEXTORAW ('0000') -- File comment length
,
HEXTORAW ('0000') -- Disk number where file starts
,
HEXTORAW ('0000') -- Internal file attributes =>
-- 0000 binary file
-- 0100 (ascii)text file
,
CASE
WHEN DBMS_LOB.SUBSTR (
P_ZIPPED_BLOB,
1,
T_OFFS
+ 30
+ BLOB2NUM (P_ZIPPED_BLOB,
2,
T_OFFS + 26)
- 1) IN
(HEXTORAW ('2F') -- /
,
HEXTORAW ('5C') -- \
)
THEN
HEXTORAW ('10000000') -- a directory/folder
ELSE
HEXTORAW ('2000B681') -- a file
END -- External file attributes
,
LITTLE_ENDIAN (T_OFFS - 1)-- Relative offset of local file header
,
DBMS_LOB.SUBSTR (
P_ZIPPED_BLOB,
BLOB2NUM (P_ZIPPED_BLOB, 2, T_OFFS + 26),
T_OFFS + 30) -- File name
));
T_OFFS :=
T_OFFS
+ 30
+ BLOB2NUM (P_ZIPPED_BLOB, 4, T_OFFS + 18) -- compressed size
+ BLOB2NUM (P_ZIPPED_BLOB, 2, T_OFFS + 26) -- File name length
+ BLOB2NUM (P_ZIPPED_BLOB, 2, T_OFFS + 28); -- Extra field length
END LOOP;
T_OFFS_END_HEADER := DBMS_LOB.GETLENGTH (P_ZIPPED_BLOB);
DBMS_LOB.APPEND (P_ZIPPED_BLOB,
UTL_RAW.CONCAT (
C_END_OF_CENTRAL_DIRECTORY -- End of central directory signature
,
HEXTORAW ('0000') -- Number of this disk
,
HEXTORAW ('0000') -- Disk where central directory starts
,
LITTLE_ENDIAN (T_CNT, 2)-- Number of central directory records on this disk
,
LITTLE_ENDIAN (T_CNT, 2)-- Total number of central directory records
,
LITTLE_ENDIAN (
T_OFFS_END_HEADER - T_OFFS_DIR_HEADER)-- Size of central directory
,
LITTLE_ENDIAN (T_OFFS_DIR_HEADER)-- Offset of start of central directory, relative to start of archive
,
LITTLE_ENDIAN (
NVL (UTL_RAW.LENGTH (T_COMMENT), 0),
2)-- ZIP file comment length
,
T_COMMENT));
END;
--
FUNCTION ALFAN_COL (P_COL PLS_INTEGER)
RETURN VARCHAR2
IS
BEGIN
RETURN CASE
WHEN P_COL > 702
THEN
CHR (64 + TRUNC ((P_COL - 27) / 676))
|| CHR (65 + MOD (TRUNC ((P_COL - 1) / 26) - 1, 26))
|| CHR (65 + MOD (P_COL - 1, 26))
WHEN P_COL > 26
THEN
CHR (64 + TRUNC ((P_COL - 1) / 26))
|| CHR (65 + MOD (P_COL - 1, 26))
ELSE
CHR (64 + P_COL)
END;
END;
--
FUNCTION COL_ALFAN (P_COL VARCHAR2)
RETURN PLS_INTEGER
IS
BEGIN
RETURN ASCII (SUBSTR (P_COL, -1))
- 64
+ NVL ((ASCII (SUBSTR (P_COL, -2, 1)) - 64) * 26, 0)
+ NVL ((ASCII (SUBSTR (P_COL, -3, 1)) - 64) * 676, 0);
END;
--
PROCEDURE CLEAR_WORKBOOK
IS
T_ROW_IND PLS_INTEGER;
BEGIN
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
T_ROW_IND := WORKBOOK.SHEETS (S).ROWS.FIRST ();
WHILE T_ROW_IND IS NOT NULL
LOOP
WORKBOOK.SHEETS (S).ROWS (T_ROW_IND).DELETE ();
T_ROW_IND := WORKBOOK.SHEETS (S).ROWS.NEXT (T_ROW_IND);
END LOOP;
WORKBOOK.SHEETS (S).ROWS.DELETE ();
WORKBOOK.SHEETS (S).WIDTHS.DELETE ();
WORKBOOK.SHEETS (S).AUTOFILTERS.DELETE ();
WORKBOOK.SHEETS (S).HYPERLINKS.DELETE ();
WORKBOOK.SHEETS (S).COL_FMTS.DELETE ();
WORKBOOK.SHEETS (S).ROW_FMTS.DELETE ();
WORKBOOK.SHEETS (S).COMMENTS.DELETE ();
WORKBOOK.SHEETS (S).MERGECELLS.DELETE ();
WORKBOOK.SHEETS (S).VALIDATIONS.DELETE ();
END LOOP;
WORKBOOK.STRINGS.DELETE ();
WORKBOOK.STR_IND.DELETE ();
WORKBOOK.FONTS.DELETE ();
WORKBOOK.FILLS.DELETE ();
WORKBOOK.BORDERS.DELETE ();
WORKBOOK.NUMFMTS.DELETE ();
WORKBOOK.CELLXFS.DELETE ();
WORKBOOK.DEFINED_NAMES.DELETE ();
WORKBOOK := NULL;
END;
--
PROCEDURE NEW_SHEET (P_SHEETNAME VARCHAR2:= NULL)
IS
T_NR PLS_INTEGER := WORKBOOK.SHEETS.COUNT () + 1;
T_IND PLS_INTEGER;
BEGIN
WORKBOOK.SHEETS (T_NR).NAME :=
NVL (
DBMS_XMLGEN.CONVERT (
TRANSLATE (P_SHEETNAME, 'a/\[]*:?', 'a')),
'Sheet' || T_NR);
IF WORKBOOK.STRINGS.COUNT () = 0
THEN
WORKBOOK.STR_CNT := 0;
END IF;
IF WORKBOOK.FONTS.COUNT () = 0
THEN
T_IND := GET_FONT ('Calibri');
END IF;
IF WORKBOOK.FILLS.COUNT () = 0
THEN
T_IND := GET_FILL ('none');
T_IND := GET_FILL ('gray125');
END IF;
IF WORKBOOK.BORDERS.COUNT () = 0
THEN
T_IND :=
GET_BORDER ('',
'',
'',
'');
END IF;
END;
--
PROCEDURE SET_COL_WIDTH (P_SHEET PLS_INTEGER,
P_COL PLS_INTEGER,
P_FORMAT VARCHAR2)
IS
T_WIDTH NUMBER;
T_NR_CHR PLS_INTEGER;
BEGIN
IF P_FORMAT IS NULL
THEN
RETURN;
END IF;
IF INSTR (P_FORMAT, ';') > 0
THEN
T_NR_CHR :=
LENGTH (
TRANSLATE (
SUBSTR (P_FORMAT, 1, INSTR (P_FORMAT, ';') - 1),
'a\"',
'a'));
ELSE
T_NR_CHR := LENGTH (TRANSLATE (P_FORMAT, 'a\"', 'a'));
END IF;
T_WIDTH := TRUNC ((T_NR_CHR * 7 + 5) / 7 * 256) / 256;
-- assume default 11 point Calibri
IF WORKBOOK.SHEETS (P_SHEET).WIDTHS.EXISTS (P_COL)
THEN
WORKBOOK.SHEETS (P_SHEET).WIDTHS (P_COL) :=
GREATEST (WORKBOOK.SHEETS (P_SHEET).WIDTHS (P_COL), T_WIDTH);
ELSE
WORKBOOK.SHEETS (P_SHEET).WIDTHS (P_COL) :=
GREATEST (T_WIDTH, 8.43);
END IF;
END;
--
FUNCTION ORAFMT2EXCEL (P_FORMAT VARCHAR2:= NULL)
RETURN VARCHAR2
IS
T_FORMAT VARCHAR2 (1000) := SUBSTR (P_FORMAT, 1, 1000);
BEGIN
T_FORMAT := REPLACE (REPLACE (T_FORMAT, 'hh24', 'hh'), 'hh12', 'hh');
T_FORMAT := REPLACE (T_FORMAT, 'mi', 'mm');
T_FORMAT :=
REPLACE (REPLACE (REPLACE (T_FORMAT, 'AM', '~~'), 'PM', '~~'),
'~~',
'AM/PM');
T_FORMAT :=
REPLACE (REPLACE (REPLACE (T_FORMAT, 'am', '~~'), 'pm', '~~'),
'~~',
'AM/PM');
T_FORMAT := REPLACE (REPLACE (T_FORMAT, 'day', 'DAY'), 'DAY', 'dddd');
T_FORMAT := REPLACE (REPLACE (T_FORMAT, 'dy', 'DY'), 'DAY', 'ddd');
T_FORMAT := REPLACE (REPLACE (T_FORMAT, 'RR', 'RR'), 'RR', 'YY');
T_FORMAT :=
REPLACE (REPLACE (T_FORMAT, 'month', 'MONTH'), 'MONTH', 'mmmm');
T_FORMAT := REPLACE (REPLACE (T_FORMAT, 'mon', 'MON'), 'MON', 'mmm');
T_FORMAT := REPLACE (T_FORMAT, '9', '#');
T_FORMAT := REPLACE (T_FORMAT, 'D', '.');
T_FORMAT := REPLACE (T_FORMAT, 'G', ',');
T_FORMAT := REPLACE (T_FORMAT, '0', '0');
RETURN T_FORMAT;
END;
--
FUNCTION GET_NUMFMT (P_FORMAT VARCHAR2:= NULL)
RETURN PLS_INTEGER
IS
T_CNT PLS_INTEGER;
T_NUMFMTID PLS_INTEGER;
BEGIN
IF P_FORMAT IS NULL
THEN
RETURN 0;
END IF;
T_CNT := WORKBOOK.NUMFMTS.COUNT ();
DBMS_OUTPUT.PUT_LINE ('TEST COUNT-' || WORKBOOK.NUMFMTS.COUNT ());
FOR I IN 1 .. T_CNT
LOOP
DBMS_OUTPUT.PUT_LINE (
'TEST1-' || WORKBOOK.NUMFMTS (I).FORMATCODE);
IF WORKBOOK.NUMFMTS (I).FORMATCODE = P_FORMAT
THEN
T_NUMFMTID := WORKBOOK.NUMFMTS (I).NUMFMTID;
EXIT;
END IF;
END LOOP;
IF T_NUMFMTID IS NULL
THEN
T_NUMFMTID :=
CASE
WHEN T_CNT = 0 THEN 164
ELSE WORKBOOK.NUMFMTS (T_CNT).NUMFMTID + 1
END;
T_CNT := T_CNT + 1;
WORKBOOK.NUMFMTS (T_CNT).NUMFMTID := T_NUMFMTID;
WORKBOOK.NUMFMTS (T_CNT).FORMATCODE := P_FORMAT;
WORKBOOK.NUMFMTINDEXES (T_NUMFMTID) := T_CNT;
END IF;
RETURN T_NUMFMTID;
END;
--
FUNCTION GET_FONT (P_NAME VARCHAR2,
P_FAMILY PLS_INTEGER:= 2,
P_FONTSIZE NUMBER:= 11,
P_THEME PLS_INTEGER:= 1,
P_UNDERLINE BOOLEAN:= FALSE,
P_ITALIC BOOLEAN:= FALSE,
P_BOLD BOOLEAN:= FALSE,
P_RGB VARCHAR2:= NULL -- this is a hex ALPHA Red Green Blue value
)
RETURN PLS_INTEGER
IS
T_IND PLS_INTEGER;
BEGIN
IF WORKBOOK.FONTS.COUNT () > 0
THEN
FOR F IN 0 .. WORKBOOK.FONTS.COUNT () - 1
LOOP
IF ( WORKBOOK.FONTS (F).NAME = P_NAME
AND WORKBOOK.FONTS (F).FAMILY = P_FAMILY
AND WORKBOOK.FONTS (F).FONTSIZE = P_FONTSIZE
AND WORKBOOK.FONTS (F).THEME = P_THEME
AND WORKBOOK.FONTS (F).UNDERLINE = P_UNDERLINE
AND WORKBOOK.FONTS (F).ITALIC = P_ITALIC
AND WORKBOOK.FONTS (F).BOLD = P_BOLD
AND ( WORKBOOK.FONTS (F).RGB = P_RGB
OR (WORKBOOK.FONTS (F).RGB IS NULL AND P_RGB IS NULL)))
THEN
RETURN F;
END IF;
END LOOP;
END IF;
T_IND := WORKBOOK.FONTS.COUNT ();
WORKBOOK.FONTS (T_IND).NAME := P_NAME;
WORKBOOK.FONTS (T_IND).FAMILY := P_FAMILY;
WORKBOOK.FONTS (T_IND).FONTSIZE := P_FONTSIZE;
WORKBOOK.FONTS (T_IND).THEME := P_THEME;
WORKBOOK.FONTS (T_IND).UNDERLINE := P_UNDERLINE;
WORKBOOK.FONTS (T_IND).ITALIC := P_ITALIC;
WORKBOOK.FONTS (T_IND).BOLD := P_BOLD;
WORKBOOK.FONTS (T_IND).RGB := P_RGB;
RETURN T_IND;
END;
--
FUNCTION GET_FILL (P_PATTERNTYPE VARCHAR2, P_FGRGB VARCHAR2:= NULL)
RETURN PLS_INTEGER
IS
T_IND PLS_INTEGER;
BEGIN
IF WORKBOOK.FILLS.COUNT () > 0
THEN
FOR F IN 0 .. WORKBOOK.FILLS.COUNT () - 1
LOOP
IF ( WORKBOOK.FILLS (F).PATTERNTYPE = P_PATTERNTYPE
AND NVL (WORKBOOK.FILLS (F).FGRGB, 'x') =
NVL (UPPER (P_FGRGB), 'x'))
THEN
RETURN F;
END IF;
END LOOP;
END IF;
T_IND := WORKBOOK.FILLS.COUNT ();
WORKBOOK.FILLS (T_IND).PATTERNTYPE := P_PATTERNTYPE;
WORKBOOK.FILLS (T_IND).FGRGB := UPPER (P_FGRGB);
RETURN T_IND;
END;
--
FUNCTION GET_BORDER (P_TOP VARCHAR2:= 'thin',
P_BOTTOM VARCHAR2:= 'thin',
P_LEFT VARCHAR2:= 'thin',
P_RIGHT VARCHAR2:= 'thin')
RETURN PLS_INTEGER
IS
T_IND PLS_INTEGER;
BEGIN
IF WORKBOOK.BORDERS.COUNT () > 0
THEN
FOR B IN 0 .. WORKBOOK.BORDERS.COUNT () - 1
LOOP
IF ( NVL (WORKBOOK.BORDERS (B).TOP, 'x') =
NVL (P_TOP, 'x')
AND NVL (WORKBOOK.BORDERS (B).BOTTOM, 'x') =
NVL (P_BOTTOM, 'x')
AND NVL (WORKBOOK.BORDERS (B).LEFT, 'x') =
NVL (P_LEFT, 'x')
AND NVL (WORKBOOK.BORDERS (B).RIGHT, 'x') =
NVL (P_RIGHT, 'x'))
THEN
RETURN B;
END IF;
END LOOP;
END IF;
T_IND := WORKBOOK.BORDERS.COUNT ();
WORKBOOK.BORDERS (T_IND).TOP := P_TOP;
WORKBOOK.BORDERS (T_IND).BOTTOM := P_BOTTOM;
WORKBOOK.BORDERS (T_IND).LEFT := P_LEFT;
WORKBOOK.BORDERS (T_IND).RIGHT := P_RIGHT;
RETURN T_IND;
END;
--
FUNCTION GET_ALIGNMENT (P_VERTICAL VARCHAR2:= NULL,
P_HORIZONTAL VARCHAR2:= NULL,
P_WRAPTEXT BOOLEAN:= NULL)
RETURN TP_ALIGNMENT
IS
T_RV TP_ALIGNMENT;
BEGIN
T_RV.VERTICAL := P_VERTICAL;
T_RV.HORIZONTAL := P_HORIZONTAL;
T_RV.WRAPTEXT := P_WRAPTEXT;
RETURN T_RV;
END;
--
FUNCTION GET_XFID (P_SHEET PLS_INTEGER,
P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_NUMFMTID PLS_INTEGER:= NULL,
P_FONTID PLS_INTEGER:= NULL,
P_FILLID PLS_INTEGER:= NULL,
P_BORDERID PLS_INTEGER:= NULL,
P_ALIGNMENT TP_ALIGNMENT:= NULL)
RETURN VARCHAR2
IS
T_CNT PLS_INTEGER;
T_XFID PLS_INTEGER;
T_XF TP_XF_FMT;
T_COL_XF TP_XF_FMT;
T_ROW_XF TP_XF_FMT;
BEGIN
IF WORKBOOK.SHEETS (P_SHEET).COL_FMTS.EXISTS (P_COL)
THEN
T_COL_XF := WORKBOOK.SHEETS (P_SHEET).COL_FMTS (P_COL);
END IF;
IF WORKBOOK.SHEETS (P_SHEET).ROW_FMTS.EXISTS (P_ROW)
THEN
T_ROW_XF := WORKBOOK.SHEETS (P_SHEET).ROW_FMTS (P_ROW);
END IF;
T_XF.NUMFMTID :=
COALESCE (P_NUMFMTID,
T_COL_XF.NUMFMTID,
T_ROW_XF.NUMFMTID,
0);
T_XF.FONTID :=
COALESCE (P_FONTID,
T_COL_XF.FONTID,
T_ROW_XF.FONTID,
0);
T_XF.FILLID :=
COALESCE (P_FILLID,
T_COL_XF.FILLID,
T_ROW_XF.FILLID,
0);
T_XF.BORDERID :=
COALESCE (P_BORDERID,
T_COL_XF.BORDERID,
T_ROW_XF.BORDERID,
0);
T_XF.ALIGNMENT :=
COALESCE (P_ALIGNMENT, T_COL_XF.ALIGNMENT, T_ROW_XF.ALIGNMENT);
IF ( T_XF.NUMFMTID + T_XF.FONTID + T_XF.FILLID + T_XF.BORDERID = 0
AND T_XF.ALIGNMENT.VERTICAL IS NULL
AND T_XF.ALIGNMENT.HORIZONTAL IS NULL
AND NOT NVL (T_XF.ALIGNMENT.WRAPTEXT, FALSE))
THEN
RETURN '';
END IF;
IF T_XF.NUMFMTID > 0
THEN
SET_COL_WIDTH (
P_SHEET,
P_COL,
WORKBOOK.NUMFMTS (WORKBOOK.NUMFMTINDEXES (T_XF.NUMFMTID)).FORMATCODE);
END IF;
T_CNT := WORKBOOK.CELLXFS.COUNT ();
FOR I IN 1 .. T_CNT
LOOP
IF ( WORKBOOK.CELLXFS (I).NUMFMTID = T_XF.NUMFMTID
AND WORKBOOK.CELLXFS (I).FONTID = T_XF.FONTID
AND WORKBOOK.CELLXFS (I).FILLID = T_XF.FILLID
AND WORKBOOK.CELLXFS (I).BORDERID = T_XF.BORDERID
AND NVL (WORKBOOK.CELLXFS (I).ALIGNMENT.VERTICAL, 'x') =
NVL (T_XF.ALIGNMENT.VERTICAL, 'x')
AND NVL (WORKBOOK.CELLXFS (I).ALIGNMENT.HORIZONTAL, 'x') =
NVL (T_XF.ALIGNMENT.HORIZONTAL, 'x')
AND NVL (WORKBOOK.CELLXFS (I).ALIGNMENT.WRAPTEXT, FALSE) =
NVL (T_XF.ALIGNMENT.WRAPTEXT, FALSE))
THEN
T_XFID := I;
EXIT;
END IF;
END LOOP;
IF T_XFID IS NULL
THEN
T_CNT := T_CNT + 1;
T_XFID := T_CNT;
WORKBOOK.CELLXFS (T_CNT) := T_XF;
END IF;
RETURN 's="' || T_XFID || '"';
END;
--
PROCEDURE CELL (P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_VALUE NUMBER,
P_NUMFMTID PLS_INTEGER:= NULL,
P_FONTID PLS_INTEGER:= NULL,
P_FILLID PLS_INTEGER:= NULL,
P_BORDERID PLS_INTEGER:= NULL,
P_ALIGNMENT TP_ALIGNMENT:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).VALUE := P_VALUE;
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).STYLE := NULL;
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).STYLE :=
GET_XFID (T_SHEET,
P_COL,
P_ROW,
P_NUMFMTID,
P_FONTID,
P_FILLID,
P_BORDERID,
P_ALIGNMENT);
END;
--
FUNCTION ADD_STRING (P_STRING VARCHAR2)
RETURN PLS_INTEGER
IS
T_CNT PLS_INTEGER;
BEGIN
IF WORKBOOK.STRINGS.EXISTS (P_STRING)
THEN
T_CNT := WORKBOOK.STRINGS (P_STRING);
ELSE
T_CNT := WORKBOOK.STRINGS.COUNT ();
WORKBOOK.STR_IND (T_CNT) := P_STRING;
WORKBOOK.STRINGS (NVL (P_STRING, '')) := T_CNT;
END IF;
WORKBOOK.STR_CNT := WORKBOOK.STR_CNT + 1;
RETURN T_CNT;
END;
--
PROCEDURE CELL (P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_VALUE VARCHAR2,
P_NUMFMTID PLS_INTEGER:= NULL,
P_FONTID PLS_INTEGER:= NULL,
P_FILLID PLS_INTEGER:= NULL,
P_BORDERID PLS_INTEGER:= NULL,
P_ALIGNMENT TP_ALIGNMENT:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
T_ALIGNMENT TP_ALIGNMENT := P_ALIGNMENT;
BEGIN
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).VALUE :=
ADD_STRING (P_VALUE);
IF T_ALIGNMENT.WRAPTEXT IS NULL AND INSTR (P_VALUE, CHR (13)) > 0
THEN
T_ALIGNMENT.WRAPTEXT := TRUE;
END IF;
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).STYLE :=
't="s" '
|| GET_XFID (T_SHEET,
P_COL,
P_ROW,
P_NUMFMTID,
P_FONTID,
P_FILLID,
P_BORDERID,
T_ALIGNMENT);
END;
--
PROCEDURE CELL (P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_VALUE DATE,
P_NUMFMTID PLS_INTEGER:= NULL,
P_FONTID PLS_INTEGER:= NULL,
P_FILLID PLS_INTEGER:= NULL,
P_BORDERID PLS_INTEGER:= NULL,
P_ALIGNMENT TP_ALIGNMENT:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_NUMFMTID PLS_INTEGER := P_NUMFMTID;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).VALUE :=
P_VALUE - TO_DATE ('01-01-1904', 'DD-MM-YYYY');
IF T_NUMFMTID IS NULL
AND NOT ( WORKBOOK.SHEETS (T_SHEET).COL_FMTS.EXISTS (P_COL)
AND WORKBOOK.SHEETS (T_SHEET).COL_FMTS (P_COL).NUMFMTID
IS NOT NULL)
AND NOT ( WORKBOOK.SHEETS (T_SHEET).ROW_FMTS.EXISTS (P_ROW)
AND WORKBOOK.SHEETS (T_SHEET).ROW_FMTS (P_ROW).NUMFMTID
IS NOT NULL)
THEN
T_NUMFMTID := GET_NUMFMT ('dd/mm/yyyy');
END IF;
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).STYLE :=
GET_XFID (T_SHEET,
P_COL,
P_ROW,
T_NUMFMTID,
P_FONTID,
P_FILLID,
P_BORDERID,
P_ALIGNMENT);
END;
--
PROCEDURE HYPERLINK (P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_URL VARCHAR2,
P_VALUE VARCHAR2:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_IND PLS_INTEGER;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).VALUE :=
ADD_STRING (NVL (P_VALUE, P_URL));
WORKBOOK.SHEETS (T_SHEET).ROWS (P_ROW) (P_COL).STYLE :=
't="s" '
|| GET_XFID (
T_SHEET,
P_COL,
P_ROW,
'',
GET_FONT ('Calibri', P_THEME => 10, P_UNDERLINE => TRUE));
T_IND := WORKBOOK.SHEETS (T_SHEET).HYPERLINKS.COUNT () + 1;
WORKBOOK.SHEETS (T_SHEET).HYPERLINKS (T_IND).CELL :=
ALFAN_COL (P_COL) || P_ROW;
WORKBOOK.SHEETS (T_SHEET).HYPERLINKS (T_IND).URL := P_URL;
END;
--
PROCEDURE COMMENT (P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_TEXT VARCHAR2,
P_AUTHOR VARCHAR2:= NULL,
P_WIDTH PLS_INTEGER:= 150,
P_HEIGHT PLS_INTEGER:= 100,
P_SHEET PLS_INTEGER:= NULL)
IS
T_IND PLS_INTEGER;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
T_IND := WORKBOOK.SHEETS (T_SHEET).COMMENTS.COUNT () + 1;
WORKBOOK.SHEETS (T_SHEET).COMMENTS (T_IND).ROW := P_ROW;
WORKBOOK.SHEETS (T_SHEET).COMMENTS (T_IND).COLUMN := P_COL;
WORKBOOK.SHEETS (T_SHEET).COMMENTS (T_IND).TEXT :=
DBMS_XMLGEN.CONVERT (P_TEXT);
WORKBOOK.SHEETS (T_SHEET).COMMENTS (T_IND).AUTHOR :=
DBMS_XMLGEN.CONVERT (P_AUTHOR);
WORKBOOK.SHEETS (T_SHEET).COMMENTS (T_IND).WIDTH := P_WIDTH;
WORKBOOK.SHEETS (T_SHEET).COMMENTS (T_IND).HEIGHT := P_HEIGHT;
END;
--
PROCEDURE MERGECELLS (P_TL_COL PLS_INTEGER -- top left
,
P_TL_ROW PLS_INTEGER,
P_BR_COL PLS_INTEGER -- bottom right
,
P_BR_ROW PLS_INTEGER,
P_SHEET PLS_INTEGER:= NULL)
IS
T_IND PLS_INTEGER;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
T_IND := WORKBOOK.SHEETS (T_SHEET).MERGECELLS.COUNT () + 1;
WORKBOOK.SHEETS (T_SHEET).MERGECELLS (T_IND) :=
ALFAN_COL (P_TL_COL)
|| P_TL_ROW
|| ':'
|| ALFAN_COL (P_BR_COL)
|| P_BR_ROW;
END;
--
PROCEDURE ADD_VALIDATION (P_TYPE VARCHAR2,
P_SQREF VARCHAR2,
P_STYLE VARCHAR2:= 'stop' -- stop, warning, information
,
P_FORMULA1 VARCHAR2:= NULL,
P_FORMULA2 VARCHAR2:= NULL,
P_TITLE VARCHAR2:= NULL,
P_PROMPT VARCHAR:= NULL,
P_SHOW_ERROR BOOLEAN:= FALSE,
P_ERROR_TITLE VARCHAR2:= NULL,
P_ERROR_TXT VARCHAR2:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_IND PLS_INTEGER;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
T_IND := WORKBOOK.SHEETS (T_SHEET).VALIDATIONS.COUNT () + 1;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).TYPE := P_TYPE;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).ERRORSTYLE := P_STYLE;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).SQREF := P_SQREF;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).FORMULA1 := P_FORMULA1;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).ERROR_TITLE :=
P_ERROR_TITLE;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).ERROR_TXT :=
P_ERROR_TXT;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).TITLE := P_TITLE;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).PROMPT := P_PROMPT;
WORKBOOK.SHEETS (T_SHEET).VALIDATIONS (T_IND).SHOWERRORMESSAGE :=
P_SHOW_ERROR;
END;
--
PROCEDURE LIST_VALIDATION (P_SQREF_COL PLS_INTEGER,
P_SQREF_ROW PLS_INTEGER,
P_TL_COL PLS_INTEGER -- top left
,
P_TL_ROW PLS_INTEGER,
P_BR_COL PLS_INTEGER -- bottom right
,
P_BR_ROW PLS_INTEGER,
P_STYLE VARCHAR2:= 'stop' -- stop, warning, information
,
P_TITLE VARCHAR2:= NULL,
P_PROMPT VARCHAR:= NULL,
P_SHOW_ERROR BOOLEAN:= FALSE,
P_ERROR_TITLE VARCHAR2:= NULL,
P_ERROR_TXT VARCHAR2:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
BEGIN
ADD_VALIDATION (
'list',
ALFAN_COL (P_SQREF_COL) || P_SQREF_ROW,
P_STYLE => LOWER (P_STYLE),
P_FORMULA1 =>
'$'
|| ALFAN_COL (P_TL_COL)
|| '$'
|| P_TL_ROW
|| ':$'
|| ALFAN_COL (P_BR_COL)
|| '$'
|| P_BR_ROW,
P_TITLE => P_TITLE,
P_PROMPT => P_PROMPT,
P_SHOW_ERROR => P_SHOW_ERROR,
P_ERROR_TITLE => P_ERROR_TITLE,
P_ERROR_TXT => P_ERROR_TXT,
P_SHEET => P_SHEET);
END;
--
PROCEDURE LIST_VALIDATION (P_SQREF_COL PLS_INTEGER,
P_SQREF_ROW PLS_INTEGER,
P_DEFINED_NAME VARCHAR2,
P_STYLE VARCHAR2:= 'stop' -- stop, warning, information
,
P_TITLE VARCHAR2:= NULL,
P_PROMPT VARCHAR:= NULL,
P_SHOW_ERROR BOOLEAN:= FALSE,
P_ERROR_TITLE VARCHAR2:= NULL,
P_ERROR_TXT VARCHAR2:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
BEGIN
ADD_VALIDATION ('list',
ALFAN_COL (P_SQREF_COL) || P_SQREF_ROW,
P_STYLE => LOWER (P_STYLE),
P_FORMULA1 => P_DEFINED_NAME,
P_TITLE => P_TITLE,
P_PROMPT => P_PROMPT,
P_SHOW_ERROR => P_SHOW_ERROR,
P_ERROR_TITLE => P_ERROR_TITLE,
P_ERROR_TXT => P_ERROR_TXT,
P_SHEET => P_SHEET);
END;
--
PROCEDURE DEFINED_NAME (P_TL_COL PLS_INTEGER -- top left
,
P_TL_ROW PLS_INTEGER,
P_BR_COL PLS_INTEGER -- bottom right
,
P_BR_ROW PLS_INTEGER,
P_NAME VARCHAR2,
P_SHEET PLS_INTEGER:= NULL,
P_LOCALSHEET PLS_INTEGER:= NULL)
IS
T_IND PLS_INTEGER;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
T_IND := WORKBOOK.DEFINED_NAMES.COUNT () + 1;
WORKBOOK.DEFINED_NAMES (T_IND).NAME := P_NAME;
WORKBOOK.DEFINED_NAMES (T_IND).REF :=
'Sheet'
|| T_SHEET
|| '!$'
|| ALFAN_COL (P_TL_COL)
|| '$'
|| P_TL_ROW
|| ':$'
|| ALFAN_COL (P_BR_COL)
|| '$'
|| P_BR_ROW;
WORKBOOK.DEFINED_NAMES (T_IND).SHEET := P_LOCALSHEET;
END;
--
PROCEDURE SET_COLUMN_WIDTH (P_COL PLS_INTEGER,
P_WIDTH NUMBER,
P_SHEET PLS_INTEGER:= NULL)
IS
BEGIN
WORKBOOK.SHEETS (NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ())).WIDTHS (
P_COL) :=
P_WIDTH;
END;
--
PROCEDURE SET_COLUMN (P_COL PLS_INTEGER,
P_NUMFMTID PLS_INTEGER:= NULL,
P_FONTID PLS_INTEGER:= NULL,
P_FILLID PLS_INTEGER:= NULL,
P_BORDERID PLS_INTEGER:= NULL,
P_ALIGNMENT TP_ALIGNMENT:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).COL_FMTS (P_COL).NUMFMTID := P_NUMFMTID;
WORKBOOK.SHEETS (T_SHEET).COL_FMTS (P_COL).FONTID := P_FONTID;
WORKBOOK.SHEETS (T_SHEET).COL_FMTS (P_COL).FILLID := P_FILLID;
WORKBOOK.SHEETS (T_SHEET).COL_FMTS (P_COL).BORDERID := P_BORDERID;
WORKBOOK.SHEETS (T_SHEET).COL_FMTS (P_COL).ALIGNMENT := P_ALIGNMENT;
END;
--
PROCEDURE SET_ROW (P_ROW PLS_INTEGER,
P_NUMFMTID PLS_INTEGER:= NULL,
P_FONTID PLS_INTEGER:= NULL,
P_FILLID PLS_INTEGER:= NULL,
P_BORDERID PLS_INTEGER:= NULL,
P_ALIGNMENT TP_ALIGNMENT:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).ROW_FMTS (P_ROW).NUMFMTID := P_NUMFMTID;
WORKBOOK.SHEETS (T_SHEET).ROW_FMTS (P_ROW).FONTID := P_FONTID;
WORKBOOK.SHEETS (T_SHEET).ROW_FMTS (P_ROW).FILLID := P_FILLID;
WORKBOOK.SHEETS (T_SHEET).ROW_FMTS (P_ROW).BORDERID := P_BORDERID;
WORKBOOK.SHEETS (T_SHEET).ROW_FMTS (P_ROW).ALIGNMENT := P_ALIGNMENT;
END;
--
PROCEDURE FREEZE_ROWS (P_NR_ROWS PLS_INTEGER:= 1,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).FREEZE_COLS := NULL;
WORKBOOK.SHEETS (T_SHEET).FREEZE_ROWS := P_NR_ROWS;
END;
--
PROCEDURE FREEZE_COLS (P_NR_COLS PLS_INTEGER:= 1,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).FREEZE_ROWS := NULL;
WORKBOOK.SHEETS (T_SHEET).FREEZE_COLS := P_NR_COLS;
END;
--
PROCEDURE FREEZE_PANE (P_COL PLS_INTEGER,
P_ROW PLS_INTEGER,
P_SHEET PLS_INTEGER:= NULL)
IS
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
WORKBOOK.SHEETS (T_SHEET).FREEZE_ROWS := P_ROW;
WORKBOOK.SHEETS (T_SHEET).FREEZE_COLS := P_COL;
END;
--
PROCEDURE SET_AUTOFILTER (P_COLUMN_START PLS_INTEGER:= NULL,
P_COLUMN_END PLS_INTEGER:= NULL,
P_ROW_START PLS_INTEGER:= NULL,
P_ROW_END PLS_INTEGER:= NULL,
P_SHEET PLS_INTEGER:= NULL)
IS
T_IND PLS_INTEGER;
T_SHEET PLS_INTEGER := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
BEGIN
T_IND := 1;
WORKBOOK.SHEETS (T_SHEET).AUTOFILTERS (T_IND).COLUMN_START :=
P_COLUMN_START;
WORKBOOK.SHEETS (T_SHEET).AUTOFILTERS (T_IND).COLUMN_END :=
P_COLUMN_END;
WORKBOOK.SHEETS (T_SHEET).AUTOFILTERS (T_IND).ROW_START :=
P_ROW_START;
WORKBOOK.SHEETS (T_SHEET).AUTOFILTERS (T_IND).ROW_END := P_ROW_END;
DEFINED_NAME (P_COLUMN_START,
P_ROW_START,
P_COLUMN_END,
P_ROW_END,
'_xlnm._FilterDatabase',
T_SHEET,
T_SHEET - 1);
END;
--
/*
procedure add1xml
( p_excel in out nocopy blob
, p_filename varchar2
, p_xml clob
)
is
t_tmp blob;
c_step constant number := 24396;
begin
dbms_lob.createtemporary( t_tmp, true );
for i in 0 .. trunc( length( p_xml ) / c_step )
loop
dbms_lob.append( t_tmp, utl_i18n.string_to_raw( substr( p_xml, i * c_step + 1, c_step ), 'AL32UTF8' ) );
end loop;
add1file( p_excel, p_filename, t_tmp );
dbms_lob.freetemporary( t_tmp );
end;
*/
--
PROCEDURE ADD1XML (P_EXCEL IN OUT NOCOPY BLOB,
P_FILENAME VARCHAR2,
P_XML CLOB)
IS
T_TMP BLOB;
DEST_OFFSET INTEGER := 1;
SRC_OFFSET INTEGER := 1;
LANG_CONTEXT INTEGER;
WARNING INTEGER;
BEGIN
LANG_CONTEXT := DBMS_LOB.DEFAULT_LANG_CTX;
DBMS_LOB.CREATETEMPORARY (T_TMP, TRUE);
DBMS_LOB.CONVERTTOBLOB (T_TMP,
P_XML,
DBMS_LOB.LOBMAXSIZE,
DEST_OFFSET,
SRC_OFFSET,
NLS_CHARSET_ID ('AL32UTF8'),
LANG_CONTEXT,
WARNING);
ADD1FILE (P_EXCEL, P_FILENAME, T_TMP);
DBMS_LOB.FREETEMPORARY (T_TMP);
END;
--
FUNCTION FINISH
RETURN BLOB
IS
T_EXCEL BLOB;
T_XXX CLOB;
T_TMP VARCHAR2 (32767 CHAR);
T_STR VARCHAR2 (32767 CHAR);
T_C NUMBER;
T_H NUMBER;
T_W NUMBER;
T_CW NUMBER;
T_CELL VARCHAR2 (1000 CHAR);
T_ROW_IND PLS_INTEGER;
T_COL_MIN PLS_INTEGER;
T_COL_MAX PLS_INTEGER;
T_COL_IND PLS_INTEGER;
T_LEN PLS_INTEGER;
TS TIMESTAMP := SYSTIMESTAMP;
BEGIN
DBMS_LOB.CREATETEMPORARY (T_EXCEL, TRUE);
T_XXX :=
'
';
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '
';
END LOOP;
T_XXX :=
T_XXX
|| '
';
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
IF WORKBOOK.SHEETS (S).COMMENTS.COUNT () > 0
THEN
T_XXX :=
T_XXX
|| '
';
END IF;
END LOOP;
T_XXX := T_XXX || '
';
ADD1XML (T_EXCEL, '[Content_Types].xml', T_XXX);
T_XXX :=
'
'
|| SYS_CONTEXT ('userenv', 'os_user')
|| '
'
|| SYS_CONTEXT ('userenv', 'os_user')
|| '
'
|| TO_CHAR (CURRENT_TIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM')
|| '
'
|| TO_CHAR (CURRENT_TIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM')
|| '
';
ADD1XML (T_EXCEL, 'docProps/core.xml', T_XXX);
T_XXX :=
'
Microsoft Excel
0
false
Worksheets
'
|| WORKBOOK.SHEETS.COUNT ()
|| '
';
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
T_XXX := T_XXX || '
' || WORKBOOK.SHEETS (S).NAME || '';
END LOOP;
T_XXX := T_XXX || '
false
false
false
14.0300
';
ADD1XML (T_EXCEL, 'docProps/app.xml', T_XXX);
T_XXX :=
'
';
ADD1XML (T_EXCEL, '_rels/.rels', T_XXX);
T_XXX :=
'
';
IF WORKBOOK.NUMFMTS.COUNT () > 0
THEN
T_XXX :=
T_XXX
|| '';
FOR N IN 1 .. WORKBOOK.NUMFMTS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
END LOOP;
T_XXX := T_XXX || '';
END IF;
T_XXX :=
T_XXX
|| '';
FOR F IN 0 .. WORKBOOK.FONTS.COUNT () - 1
LOOP
T_XXX :=
T_XXX
|| ''
|| CASE WHEN WORKBOOK.FONTS (F).BOLD THEN '' END
|| CASE WHEN WORKBOOK.FONTS (F).ITALIC THEN '' END
|| CASE WHEN WORKBOOK.FONTS (F).UNDERLINE THEN '' END
|| '
';
END LOOP;
T_XXX := T_XXX || '
';
FOR F IN 0 .. WORKBOOK.FILLS.COUNT () - 1
LOOP
T_XXX :=
T_XXX
|| ''
|| CASE
WHEN WORKBOOK.FILLS (F).FGRGB IS NOT NULL
THEN
''
END
|| '';
END LOOP;
T_XXX := T_XXX || '
';
FOR B IN 0 .. WORKBOOK.BORDERS.COUNT () - 1
LOOP
T_XXX :=
T_XXX
|| ''
|| CASE
WHEN WORKBOOK.BORDERS (B).LEFT IS NULL
THEN
''
ELSE
''
END
|| CASE
WHEN WORKBOOK.BORDERS (B).RIGHT IS NULL
THEN
''
ELSE
''
END
|| CASE
WHEN WORKBOOK.BORDERS (B).TOP IS NULL
THEN
''
ELSE
''
END
|| CASE
WHEN WORKBOOK.BORDERS (B).BOTTOM IS NULL
THEN
''
ELSE
''
END
|| '';
END LOOP;
T_XXX := T_XXX || '
';
FOR X IN 1 .. WORKBOOK.CELLXFS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
IF ( WORKBOOK.CELLXFS (X).ALIGNMENT.HORIZONTAL IS NOT NULL
OR WORKBOOK.CELLXFS (X).ALIGNMENT.VERTICAL IS NOT NULL
OR WORKBOOK.CELLXFS (X).ALIGNMENT.WRAPTEXT)
THEN
T_XXX :=
T_XXX
|| '';
END IF;
T_XXX := T_XXX || '';
END LOOP;
T_XXX :=
T_XXX
|| '
';
ADD1XML (T_EXCEL, 'xl/styles.xml', T_XXX);
T_XXX :=
'
';
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '
';
END LOOP;
T_XXX := T_XXX || '';
IF WORKBOOK.DEFINED_NAMES.COUNT () > 0
THEN
T_XXX := T_XXX || '';
FOR S IN 1 .. WORKBOOK.DEFINED_NAMES.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '
'
|| WORKBOOK.DEFINED_NAMES (S).REF
|| '';
END LOOP;
T_XXX := T_XXX || '';
END IF;
T_XXX := T_XXX || '';
ADD1XML (T_EXCEL, 'xl/workbook.xml', T_XXX);
T_XXX :=
'
';
ADD1XML (T_EXCEL, 'xl/theme/theme1.xml', T_XXX);
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
T_COL_MIN := 16384;
T_COL_MAX := 1;
T_ROW_IND := WORKBOOK.SHEETS (S).ROWS.FIRST ();
WHILE T_ROW_IND IS NOT NULL
LOOP
T_COL_MIN :=
LEAST (T_COL_MIN,
WORKBOOK.SHEETS (S).ROWS (T_ROW_IND).FIRST ());
T_COL_MAX :=
GREATEST (T_COL_MAX,
WORKBOOK.SHEETS (S).ROWS (T_ROW_IND).LAST ());
T_ROW_IND := WORKBOOK.SHEETS (S).ROWS.NEXT (T_ROW_IND);
END LOOP;
T_XXX :=
'
';
IF WORKBOOK.SHEETS (S).FREEZE_ROWS > 0
AND WORKBOOK.SHEETS (S).FREEZE_COLS > 0
THEN
T_XXX :=
T_XXX
|| ( '');
ELSE
IF WORKBOOK.SHEETS (S).FREEZE_ROWS > 0
THEN
T_XXX :=
T_XXX
|| '';
END IF;
IF WORKBOOK.SHEETS (S).FREEZE_COLS > 0
THEN
T_XXX :=
T_XXX
|| '';
END IF;
END IF;
T_XXX := T_XXX || '
';
IF WORKBOOK.SHEETS (S).WIDTHS.COUNT () > 0
THEN
T_XXX := T_XXX || '';
T_COL_IND := WORKBOOK.SHEETS (S).WIDTHS.FIRST ();
WHILE T_COL_IND IS NOT NULL
LOOP
T_XXX :=
T_XXX
|| '';
T_COL_IND := WORKBOOK.SHEETS (S).WIDTHS.NEXT (T_COL_IND);
END LOOP;
T_XXX := T_XXX || '';
END IF;
T_XXX := T_XXX || '';
T_ROW_IND := WORKBOOK.SHEETS (S).ROWS.FIRST ();
T_TMP := NULL;
WHILE T_ROW_IND IS NOT NULL
LOOP
T_TMP :=
T_TMP
|| '';
T_LEN := LENGTH (T_TMP);
T_COL_IND := WORKBOOK.SHEETS (S).ROWS (T_ROW_IND).FIRST ();
WHILE T_COL_IND IS NOT NULL
LOOP
T_CELL :=
''
|| TO_CHAR (
WORKBOOK.SHEETS (S).ROWS (T_ROW_IND) (
T_COL_IND).VALUE,
'TM9',
'NLS_NUMERIC_CHARACTERS=.,')
|| '';
IF T_LEN > 32000
THEN
DBMS_LOB.WRITEAPPEND (T_XXX, T_LEN, T_TMP);
T_TMP := NULL;
T_LEN := 0;
END IF;
T_TMP := T_TMP || T_CELL;
T_LEN := T_LEN + LENGTH (T_CELL);
T_COL_IND :=
WORKBOOK.SHEETS (S).ROWS (T_ROW_IND).NEXT (T_COL_IND);
END LOOP;
T_TMP := T_TMP || '
';
T_ROW_IND := WORKBOOK.SHEETS (S).ROWS.NEXT (T_ROW_IND);
END LOOP;
T_TMP := T_TMP || '';
T_LEN := LENGTH (T_TMP);
DBMS_LOB.WRITEAPPEND (T_XXX, T_LEN, T_TMP);
FOR A IN 1 .. WORKBOOK.SHEETS (S).AUTOFILTERS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
END LOOP;
IF WORKBOOK.SHEETS (S).MERGECELLS.COUNT () > 0
THEN
T_XXX :=
T_XXX
|| '';
FOR M IN 1 .. WORKBOOK.SHEETS (S).MERGECELLS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
END LOOP;
T_XXX := T_XXX || '';
END IF;
--
IF WORKBOOK.SHEETS (S).VALIDATIONS.COUNT () > 0
THEN
T_XXX :=
T_XXX
|| '';
FOR M IN 1 .. WORKBOOK.SHEETS (S).VALIDATIONS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
IF WORKBOOK.SHEETS (S).VALIDATIONS (M).FORMULA1
IS NOT NULL
THEN
T_XXX :=
T_XXX
|| ''
|| WORKBOOK.SHEETS (S).VALIDATIONS (M).FORMULA1
|| '';
END IF;
IF WORKBOOK.SHEETS (S).VALIDATIONS (M).FORMULA2
IS NOT NULL
THEN
T_XXX :=
T_XXX
|| ''
|| WORKBOOK.SHEETS (S).VALIDATIONS (M).FORMULA2
|| '';
END IF;
T_XXX := T_XXX || '';
END LOOP;
T_XXX := T_XXX || '';
END IF;
--
IF WORKBOOK.SHEETS (S).HYPERLINKS.COUNT () > 0
THEN
T_XXX := T_XXX || '';
FOR H IN 1 .. WORKBOOK.SHEETS (S).HYPERLINKS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
END LOOP;
T_XXX := T_XXX || '';
END IF;
T_XXX :=
T_XXX
|| '';
IF WORKBOOK.SHEETS (S).COMMENTS.COUNT () > 0
THEN
T_XXX :=
T_XXX
|| '';
END IF;
--
T_XXX := T_XXX || '';
ADD1XML (T_EXCEL, 'xl/worksheets/sheet' || S || '.xml', T_XXX);
IF WORKBOOK.SHEETS (S).HYPERLINKS.COUNT () > 0
OR WORKBOOK.SHEETS (S).COMMENTS.COUNT () > 0
THEN
T_XXX :=
'
';
IF WORKBOOK.SHEETS (S).COMMENTS.COUNT () > 0
THEN
T_XXX :=
T_XXX
|| '';
T_XXX :=
T_XXX
|| '';
END IF;
FOR H IN 1 .. WORKBOOK.SHEETS (S).HYPERLINKS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '';
END LOOP;
T_XXX := T_XXX || '';
ADD1XML (T_EXCEL,
'xl/worksheets/_rels/sheet' || S || '.xml.rels',
T_XXX);
END IF;
--
IF WORKBOOK.SHEETS (S).COMMENTS.COUNT () > 0
THEN
DECLARE
CNT PLS_INTEGER;
AUTHOR_IND TP_AUTHOR;
-- t_col_ind := workbook.sheets( s ).widths.next( t_col_ind );
BEGIN
AUTHORS.DELETE ();
FOR C IN 1 .. WORKBOOK.SHEETS (S).COMMENTS.COUNT ()
LOOP
AUTHORS (WORKBOOK.SHEETS (S).COMMENTS (C).AUTHOR) :=
0;
END LOOP;
T_XXX :=
'
';
CNT := 0;
AUTHOR_IND := AUTHORS.FIRST ();
WHILE AUTHOR_IND IS NOT NULL
OR AUTHORS.NEXT (AUTHOR_IND) IS NOT NULL
LOOP
AUTHORS (AUTHOR_IND) := CNT;
T_XXX :=
T_XXX || '' || AUTHOR_IND || '';
CNT := CNT + 1;
AUTHOR_IND := AUTHORS.NEXT (AUTHOR_IND);
END LOOP;
END;
T_XXX := T_XXX || '';
FOR C IN 1 .. WORKBOOK.SHEETS (S).COMMENTS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '
';
IF WORKBOOK.SHEETS (S).COMMENTS (C).AUTHOR IS NOT NULL
THEN
T_XXX :=
T_XXX
|| ''
|| WORKBOOK.SHEETS (S).COMMENTS (C).AUTHOR
|| ':';
END IF;
T_XXX :=
T_XXX
|| ''
|| CASE
WHEN WORKBOOK.SHEETS (S).COMMENTS (C).AUTHOR
IS NOT NULL
THEN
'
'
END
|| WORKBOOK.SHEETS (S).COMMENTS (C).TEXT
|| '';
END LOOP;
T_XXX := T_XXX || '';
ADD1XML (T_EXCEL, 'xl/comments' || S || '.xml', T_XXX);
T_XXX :=
'
';
FOR C IN 1 .. WORKBOOK.SHEETS (S).COMMENTS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '
';
T_W := WORKBOOK.SHEETS (S).COMMENTS (C).WIDTH;
T_C := 1;
LOOP
IF WORKBOOK.SHEETS (S).WIDTHS.EXISTS (
WORKBOOK.SHEETS (S).COMMENTS (C).COLUMN + T_C)
THEN
T_CW :=
256
* WORKBOOK.SHEETS (S).WIDTHS (
WORKBOOK.SHEETS (S).COMMENTS (C).COLUMN
+ T_C);
T_CW := TRUNC ((T_CW + 18) / 256 * 7);
-- assume default 11 point Calibri
ELSE
T_CW := 64;
END IF;
EXIT WHEN T_W < T_CW;
T_C := T_C + 1;
T_W := T_W - T_CW;
END LOOP;
T_H := WORKBOOK.SHEETS (S).COMMENTS (C).HEIGHT;
T_XXX :=
T_XXX
|| TO_CHAR (
''
|| WORKBOOK.SHEETS (S).COMMENTS (C).COLUMN
|| ',15,'
|| WORKBOOK.SHEETS (S).COMMENTS (C).ROW
|| ',30,'
|| ( WORKBOOK.SHEETS (S).COMMENTS (C).COLUMN
+ T_C
- 1)
|| ','
|| ROUND (T_W)
|| ','
|| ( WORKBOOK.SHEETS (S).COMMENTS (C).ROW
+ 1
+ TRUNC (T_H / 20))
|| ','
|| MOD (T_H, 20)
|| '');
T_XXX :=
T_XXX
|| TO_CHAR (
'False'
|| (WORKBOOK.SHEETS (S).COMMENTS (C).ROW - 1)
|| ''
|| ( WORKBOOK.SHEETS (S).COMMENTS (C).COLUMN
- 1)
|| '');
END LOOP;
T_XXX := T_XXX || '';
ADD1XML (T_EXCEL,
'xl/drawings/vmlDrawing' || S || '.vml',
T_XXX);
END IF;
--
END LOOP;
T_XXX :=
'
';
FOR S IN 1 .. WORKBOOK.SHEETS.COUNT ()
LOOP
T_XXX :=
T_XXX
|| '
';
END LOOP;
T_XXX := T_XXX || '';
ADD1XML (T_EXCEL, 'xl/_rels/workbook.xml.rels', T_XXX);
T_XXX :=
'
';
T_TMP := NULL;
FOR I IN 0 .. WORKBOOK.STR_IND.COUNT () - 1
LOOP
T_STR :=
''
|| DBMS_XMLGEN.CONVERT (
SUBSTR (WORKBOOK.STR_IND (I), 1, 32000))
|| '';
IF LENGTH (T_TMP) + LENGTH (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 || '';
ADD1XML (T_EXCEL, 'xl/sharedStrings.xml', T_XXX);
FINISH_ZIP (T_EXCEL);
CLEAR_WORKBOOK;
RETURN T_EXCEL;
END;
--
PROCEDURE SAVE (P_DIRECTORY VARCHAR2, P_FILENAME VARCHAR2)
IS
BEGIN
BLOB2FILE (FINISH, P_DIRECTORY, P_FILENAME);
END;
--
PROCEDURE QUERY2SHEET (P_SQL IN VARCHAR2,
P_BLOB OUT BLOB,
P_COLUMN_HEADERS IN BOOLEAN := TRUE,
P_DIRECTORY IN VARCHAR2 := NULL,
P_FILENAME IN VARCHAR2 := NULL,
P_SHEET IN PLS_INTEGER := NULL,
P_BORDER IN BOOLEAN := FALSE)
IS
T_SHEET PLS_INTEGER;
T_C INTEGER;
T_COL_CNT INTEGER;
T_DESC_TAB DBMS_SQL.DESC_TAB2;
D_TAB DBMS_SQL.DATE_TABLE;
N_TAB DBMS_SQL.NUMBER_TABLE;
V_TAB DBMS_SQL.VARCHAR2_TABLE;
T_BULK_SIZE PLS_INTEGER := 200;
T_R INTEGER;
T_CUR_ROW PLS_INTEGER;
LB_BLOB BLOB;
--p_blob blob;
BEGIN
IF P_SHEET IS NULL
THEN
NEW_SHEET;
END IF;
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);
DBMS_OUTPUT.PUT_LINE (T_COL_CNT);
FOR C IN 1 .. T_COL_CNT
LOOP
IF P_COLUMN_HEADERS
THEN
CELL (
C,
1,
T_DESC_TAB (C).COL_NAME,
P_SHEET => T_SHEET,
P_FONTID =>
XXTH_EXPORT_XLSX.GET_FONT ('Calibri', P_BOLD => TRUE),
P_BORDERID => CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thick',
'thick',
'thick',
'thick')
ELSE
NULL
END);
END IF;
-- dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
CASE
WHEN T_DESC_TAB (C).COL_TYPE IN (2, 100, 101)
THEN
DBMS_SQL.DEFINE_ARRAY (T_C,
C,
N_TAB,
T_BULK_SIZE,
1);
WHEN T_DESC_TAB (C).COL_TYPE IN (12,
178,
179,
180,
181,
231)
THEN
DBMS_SQL.DEFINE_ARRAY (T_C,
C,
D_TAB,
T_BULK_SIZE,
1);
WHEN T_DESC_TAB (C).COL_TYPE IN (1,
8,
9,
96,
112)
THEN
DBMS_SQL.DEFINE_ARRAY (T_C,
C,
V_TAB,
T_BULK_SIZE,
1);
ELSE
NULL;
END CASE;
END LOOP;
--
T_CUR_ROW := CASE WHEN P_COLUMN_HEADERS THEN 2 ELSE 1 END;
T_SHEET := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
--
T_R := DBMS_SQL.EXECUTE (T_C);
LOOP
T_R := DBMS_SQL.FETCH_ROWS (T_C);
IF T_R > 0
THEN
FOR C IN 1 .. T_COL_CNT
LOOP
CASE
WHEN T_DESC_TAB (C).COL_TYPE IN (2, 100, 101)
THEN
DBMS_SQL.COLUMN_VALUE (T_C, C, N_TAB);
FOR I IN 0 .. T_R - 1
LOOP
IF N_TAB (I + N_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
N_TAB (I + N_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thick',
'thick',
'thick',
'thick')
ELSE
NULL
END);
END IF;
END LOOP;
N_TAB.DELETE;
WHEN T_DESC_TAB (C).COL_TYPE IN (12,
178,
179,
180,
181,
231)
THEN
DBMS_SQL.COLUMN_VALUE (T_C, C, D_TAB);
FOR I IN 0 .. T_R - 1
LOOP
IF D_TAB (I + D_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
D_TAB (I + D_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thick',
'thick',
'thick',
'thick')
ELSE
NULL
END);
END IF;
END LOOP;
D_TAB.DELETE;
WHEN T_DESC_TAB (C).COL_TYPE IN (1,
8,
9,
96,
112)
THEN
DBMS_SQL.COLUMN_VALUE (T_C, C, V_TAB);
FOR I IN 0 .. T_R - 1
LOOP
IF V_TAB (I + V_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
V_TAB (I + V_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thick',
'thick',
'thick',
'thick')
ELSE
NULL
END);
END IF;
END LOOP;
V_TAB.DELETE;
ELSE
NULL;
END CASE;
END LOOP;
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);
P_BLOB := FINISH;
-- 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;
PROCEDURE QUERY2SHEET_WITHOUT_BLOB (
P_SQL IN VARCHAR2,
P_COLUMN_HEADERS IN BOOLEAN := TRUE,
P_DIRECTORY IN VARCHAR2 := NULL,
P_FILENAME IN VARCHAR2 := NULL,
P_SHEET IN PLS_INTEGER := NULL,
P_BORDER IN BOOLEAN := FALSE,
P_ROW_START IN NUMBER := 1, --V2.0
P_FORMAT IN VARCHAR2 := NULL, --V2.0
P_BOLD IN BOOLEAN := FALSE --V2.0
)
IS
T_SHEET PLS_INTEGER;
T_C INTEGER;
T_COL_CNT INTEGER;
T_DESC_TAB DBMS_SQL.DESC_TAB2;
D_TAB DBMS_SQL.DATE_TABLE;
N_TAB DBMS_SQL.NUMBER_TABLE;
V_TAB DBMS_SQL.VARCHAR2_TABLE;
T_BULK_SIZE PLS_INTEGER := 200;
T_R INTEGER;
T_CUR_ROW PLS_INTEGER;
LB_BLOB BLOB;
--p_blob blob;
BEGIN
IF P_SHEET IS NULL
THEN
NEW_SHEET;
END IF;
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);
DBMS_OUTPUT.PUT_LINE (T_COL_CNT);
FOR C IN 1 .. T_COL_CNT
LOOP
IF P_COLUMN_HEADERS
THEN
CELL (
C,
1,
T_DESC_TAB (C).COL_NAME,
P_SHEET => T_SHEET,
P_FONTID =>
XXTH_EXPORT_XLSX.GET_FONT ('Calibri', P_BOLD => TRUE),
P_BORDERID => CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thin',
'thin',
'thin',
'thin')
ELSE
NULL
END);
END IF;
-- dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
CASE
WHEN T_DESC_TAB (C).COL_TYPE IN (2, 100, 101)
THEN
DBMS_SQL.DEFINE_ARRAY (T_C,
C,
N_TAB,
T_BULK_SIZE,
1);
WHEN T_DESC_TAB (C).COL_TYPE IN (12,
178,
179,
180,
181,
231)
THEN
DBMS_SQL.DEFINE_ARRAY (T_C,
C,
D_TAB,
T_BULK_SIZE,
1);
WHEN T_DESC_TAB (C).COL_TYPE IN (1,
8,
9,
96,
112)
THEN
DBMS_SQL.DEFINE_ARRAY (T_C,
C,
V_TAB,
T_BULK_SIZE,
1);
ELSE
NULL;
END CASE;
END LOOP;
--
T_CUR_ROW := CASE WHEN P_COLUMN_HEADERS THEN 2 ELSE P_ROW_START END;
T_SHEET := NVL (P_SHEET, WORKBOOK.SHEETS.COUNT ());
--
T_R := DBMS_SQL.EXECUTE (T_C);
LOOP
T_R := DBMS_SQL.FETCH_ROWS (T_C);
IF T_R > 0
THEN
FOR C IN 1 .. T_COL_CNT
LOOP
CASE
WHEN T_DESC_TAB (C).COL_TYPE IN (2, 100, 101)
THEN
DBMS_SQL.COLUMN_VALUE (T_C, C, N_TAB);
IF (P_FORMAT IS NULL)
THEN
FOR I IN 0 .. T_R - 1
LOOP
IF N_TAB (I + N_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
N_TAB (I + N_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thin',
'thin',
'thin',
'thin')
ELSE
NULL
END);
END IF;
END LOOP;
ELSE
FOR I IN 0 .. T_R - 1
LOOP
IF N_TAB (I + N_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
N_TAB (I + N_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thin',
'thin',
'thin',
'thin')
ELSE
NULL
END,
P_NUMFMTID =>
CASE
WHEN N_TAB (
I
+ N_TAB.FIRST ()) >
0
THEN
XXTH_EXPORT_XLSX.GET_NUMFMT (
XXTH_EXPORT_XLSX.ORAFMT2EXCEL (
'999G999D00'))
ELSE
NULL
END);
END IF;
END LOOP;
END IF;
N_TAB.DELETE;
WHEN T_DESC_TAB (C).COL_TYPE IN (12,
178,
179,
180,
181,
231)
THEN
DBMS_SQL.COLUMN_VALUE (T_C, C, D_TAB);
FOR I IN 0 .. T_R - 1
LOOP
IF D_TAB (I + D_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
D_TAB (I + D_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thin',
'thin',
'thin',
'thin')
ELSE
NULL
END,
P_FONTID =>
XXTH_EXPORT_XLSX.GET_FONT (
'Calibri',
P_BOLD => P_BOLD));
END IF;
END LOOP;
D_TAB.DELETE;
WHEN T_DESC_TAB (C).COL_TYPE IN (1,
8,
9,
96,
112)
THEN
DBMS_SQL.COLUMN_VALUE (T_C, C, V_TAB);
FOR I IN 0 .. T_R - 1
LOOP
IF V_TAB (I + V_TAB.FIRST ()) IS NOT NULL
THEN
CELL (
C,
T_CUR_ROW + I,
V_TAB (I + V_TAB.FIRST ()),
P_SHEET => T_SHEET,
P_BORDERID =>
CASE
WHEN P_BORDER
THEN
XXTH_EXPORT_XLSX.GET_BORDER (
'thin',
'thin',
'thin',
'thin')
ELSE
NULL
END,
P_FONTID =>
XXTH_EXPORT_XLSX.GET_FONT (
'Calibri',
P_BOLD => P_BOLD));
END IF;
END LOOP;
V_TAB.DELETE;
ELSE
NULL;
END CASE;
END LOOP;
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);
-- p_blob := FINISH;
-- 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;
END XXTH_EXPORT_XLSX;
/