CREATE OR REPLACE PACKAGE 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 -- |------- -------- --------------- --------------------------------------------------------------------------------------- TYPE TP_ALIGNMENT IS RECORD ( VERTICAL VARCHAR2 (11), HORIZONTAL VARCHAR2 (16), WRAPTEXT BOOLEAN ); -- PROCEDURE CLEAR_WORKBOOK; -- PROCEDURE NEW_SHEET (P_SHEETNAME VARCHAR2:= NULL); -- FUNCTION ORAFMT2EXCEL (P_FORMAT VARCHAR2:= NULL) RETURN VARCHAR2; -- FUNCTION GET_NUMFMT (P_FORMAT VARCHAR2:= NULL) RETURN PLS_INTEGER; -- FUNCTION GET_FONT (P_NAME VARCHAR2, P_FAMILY PLS_INTEGER:= 2, P_FONTSIZE NUMBER:= 11, P_THEME PLS_INTEGER:= 1, P_UNDERLINE BOOLEAN:= FALSE, P_ITALIC BOOLEAN:= FALSE, P_BOLD BOOLEAN:= FALSE, P_RGB VARCHAR2:= NULL -- this is a hex ALPHA Red Green Blue value ) RETURN PLS_INTEGER; -- FUNCTION GET_FILL (P_PATTERNTYPE VARCHAR2, P_FGRGB VARCHAR2:= NULL -- this is a hex ALPHA Red Green Blue value ) RETURN PLS_INTEGER; -- FUNCTION GET_BORDER (P_TOP VARCHAR2:= 'thin', P_BOTTOM VARCHAR2:= 'thin', P_LEFT VARCHAR2:= 'thin', P_RIGHT VARCHAR2:= 'thin') /* none thin medium dashed dotted thick double hair mediumDashed dashDot mediumDashDot dashDotDot mediumDashDotDot slantDashDot */ RETURN PLS_INTEGER; -- FUNCTION GET_ALIGNMENT (P_VERTICAL VARCHAR2:= NULL, P_HORIZONTAL VARCHAR2:= NULL, P_WRAPTEXT BOOLEAN:= NULL) /* horizontal center centerContinuous distributed fill general justify left right */ /* vertical bottom center distributed justify top */ RETURN TP_ALIGNMENT; -- PROCEDURE CELL (P_COL PLS_INTEGER, P_ROW PLS_INTEGER, P_VALUE NUMBER, P_NUMFMTID PLS_INTEGER:= NULL, P_FONTID PLS_INTEGER:= NULL, P_FILLID PLS_INTEGER:= NULL, P_BORDERID PLS_INTEGER:= NULL, P_ALIGNMENT TP_ALIGNMENT:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE CELL (P_COL PLS_INTEGER, P_ROW PLS_INTEGER, P_VALUE VARCHAR2, P_NUMFMTID PLS_INTEGER:= NULL, P_FONTID PLS_INTEGER:= NULL, P_FILLID PLS_INTEGER:= NULL, P_BORDERID PLS_INTEGER:= NULL, P_ALIGNMENT TP_ALIGNMENT:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE CELL (P_COL PLS_INTEGER, P_ROW PLS_INTEGER, P_VALUE DATE, P_NUMFMTID PLS_INTEGER:= NULL, P_FONTID PLS_INTEGER:= NULL, P_FILLID PLS_INTEGER:= NULL, P_BORDERID PLS_INTEGER:= NULL, P_ALIGNMENT TP_ALIGNMENT:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE HYPERLINK (P_COL PLS_INTEGER, P_ROW PLS_INTEGER, P_URL VARCHAR2, P_VALUE VARCHAR2:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE COMMENT (P_COL PLS_INTEGER, P_ROW PLS_INTEGER, P_TEXT VARCHAR2, P_AUTHOR VARCHAR2:= NULL, P_WIDTH PLS_INTEGER:= 150 -- pixels , P_HEIGHT PLS_INTEGER:= 100 -- pixels , P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE MERGECELLS (P_TL_COL PLS_INTEGER -- top left , P_TL_ROW PLS_INTEGER, P_BR_COL PLS_INTEGER -- bottom right , P_BR_ROW PLS_INTEGER, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE LIST_VALIDATION (P_SQREF_COL PLS_INTEGER, P_SQREF_ROW PLS_INTEGER, P_TL_COL PLS_INTEGER -- top left , P_TL_ROW PLS_INTEGER, P_BR_COL PLS_INTEGER -- bottom right , P_BR_ROW PLS_INTEGER, P_STYLE VARCHAR2:= 'stop' -- stop, warning, information , P_TITLE VARCHAR2:= NULL, P_PROMPT VARCHAR:= NULL, P_SHOW_ERROR BOOLEAN:= FALSE, P_ERROR_TITLE VARCHAR2:= NULL, P_ERROR_TXT VARCHAR2:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE LIST_VALIDATION (P_SQREF_COL PLS_INTEGER, P_SQREF_ROW PLS_INTEGER, P_DEFINED_NAME VARCHAR2, P_STYLE VARCHAR2:= 'stop' -- stop, warning, information , P_TITLE VARCHAR2:= NULL, P_PROMPT VARCHAR:= NULL, P_SHOW_ERROR BOOLEAN:= FALSE, P_ERROR_TITLE VARCHAR2:= NULL, P_ERROR_TXT VARCHAR2:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE DEFINED_NAME (P_TL_COL PLS_INTEGER -- top left , P_TL_ROW PLS_INTEGER, P_BR_COL PLS_INTEGER -- bottom right , P_BR_ROW PLS_INTEGER, P_NAME VARCHAR2, P_SHEET PLS_INTEGER:= NULL, P_LOCALSHEET PLS_INTEGER:= NULL); -- PROCEDURE SET_COLUMN_WIDTH (P_COL PLS_INTEGER, P_WIDTH NUMBER, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE SET_COLUMN (P_COL PLS_INTEGER, P_NUMFMTID PLS_INTEGER:= NULL, P_FONTID PLS_INTEGER:= NULL, P_FILLID PLS_INTEGER:= NULL, P_BORDERID PLS_INTEGER:= NULL, P_ALIGNMENT TP_ALIGNMENT:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE SET_ROW (P_ROW PLS_INTEGER, P_NUMFMTID PLS_INTEGER:= NULL, P_FONTID PLS_INTEGER:= NULL, P_FILLID PLS_INTEGER:= NULL, P_BORDERID PLS_INTEGER:= NULL, P_ALIGNMENT TP_ALIGNMENT:= NULL, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE FREEZE_ROWS (P_NR_ROWS PLS_INTEGER:= 1, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE FREEZE_COLS (P_NR_COLS PLS_INTEGER:= 1, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE FREEZE_PANE (P_COL PLS_INTEGER, P_ROW PLS_INTEGER, P_SHEET PLS_INTEGER:= NULL); -- PROCEDURE SET_AUTOFILTER (P_COLUMN_START PLS_INTEGER:= NULL, P_COLUMN_END PLS_INTEGER:= NULL, P_ROW_START PLS_INTEGER:= NULL, P_ROW_END PLS_INTEGER:= NULL, P_SHEET PLS_INTEGER:= NULL); -- FUNCTION FINISH RETURN BLOB; -- PROCEDURE SAVE (P_DIRECTORY VARCHAR2, P_FILENAME VARCHAR2); -- 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); 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 ); END XXTH_EXPORT_XLSX; /