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 || ''); 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; /