TECNOLOBO

No recuerdas tu codigo?
Se te olvido como se hace?

Aqui podras guardar lo que necesiten
Y cuando sea necesesario

Creado por julian gomez
iiiiii

crear archivo XLS oracle plsql



Descripcion

Crear un archivo xlsx en un blob, este paquete a s

html


CREATE OR REPLACE PACKAGE BODY REMUNER.AS_XLSX_JULIAN IS

  C_LOCAL_FILE_HEADER        CONSTANT RAW(4) := HEXTORAW( '504B0304' ); -- LOCAL FILE HEADER SIGNATURE
  C_END_OF_CENTRAL_DIRECTORY CONSTANT RAW(4) := HEXTORAW( '504B0506' ); -- END OF CENTRAL DIRECTORY SIGNATURE

  TYPE TP_XF_FMT 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' );
    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();
    FOR I IN 1 .. T_CNT
    LOOP
      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 := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>';
    FOR S IN 1 .. WORKBOOK.SHEETS.COUNT()
    LOOP
      T_XXX := T_XXX || '
<Override PartName="/xl/worksheets/sheet' || S || '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>';
    END LOOP;
    T_XXX := T_XXX || '
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>';
    FOR S IN 1 .. WORKBOOK.SHEETS.COUNT()
    LOOP
      IF WORKBOOK.SHEETS( S ).COMMENTS.COUNT() > 0
      THEN
        T_XXX := T_XXX || '
<Override PartName="/xl/comments' || S || '.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"/>';
      END IF;
    END LOOP;
    T_XXX := T_XXX || '
</Types>';
    ADD1XML( T_EXCEL, '[Content_Types].xml', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<dc:creator>' || SYS_CONTEXT( 'userenv', 'os_user' ) || '</dc:creator>
<cp:lastModifiedBy>' || SYS_CONTEXT( 'userenv', 'os_user' ) || '</cp:lastModifiedBy>
<dcterms:created xsi:type="dcterms:W3CDTF">' || TO_CHAR( CURRENT_TIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM' ) || '</dcterms:created>
<dcterms:modified xsi:type="dcterms:W3CDTF">' || TO_CHAR( CURRENT_TIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM' ) || '</dcterms:modified>
</cp:coreProperties>';
    ADD1XML( T_EXCEL, 'docProps/core.xml', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">
<Application>Microsoft Excel</Application>
<DocSecurity>0</DocSecurity>
<ScaleCrop>false</ScaleCrop>
<HeadingPairs>
<vt:vector size="2" baseType="variant">
<vt:variant>
<vt:lpstr>Worksheets</vt:lpstr>
</vt:variant>
<vt:variant>
<vt:i4>' || WORKBOOK.SHEETS.COUNT() || '</vt:i4>
</vt:variant>
</vt:vector>
</HeadingPairs>
<TitlesOfParts>
<vt:vector size="' || WORKBOOK.SHEETS.COUNT() || '" baseType="lpstr">';
    FOR S IN 1 .. WORKBOOK.SHEETS.COUNT()
    LOOP
      T_XXX := T_XXX || '
<vt:lpstr>' || WORKBOOK.SHEETS( S ).NAME || '</vt:lpstr>';
    END LOOP;
    T_XXX := T_XXX || '</vt:vector>
</TitlesOfParts>
<LinksUpToDate>false</LinksUpToDate>
<SharedDoc>false</SharedDoc>
<HyperlinksChanged>false</HyperlinksChanged>
<AppVersion>14.0300</AppVersion>
</Properties>';
    ADD1XML( T_EXCEL, 'docProps/app.xml', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>';
    ADD1XML( T_EXCEL, '_rels/.rels', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">';
    IF WORKBOOK.NUMFMTS.COUNT() > 0
    THEN
      T_XXX := T_XXX || '<numFmts count="' || WORKBOOK.NUMFMTS.COUNT() || '">';
      FOR N IN 1 .. WORKBOOK.NUMFMTS.COUNT()
      LOOP
        T_XXX := T_XXX || '<numFmt numFmtId="' || WORKBOOK.NUMFMTS( N ).NUMFMTID || '" formatCode="' || WORKBOOK.NUMFMTS( N ).FORMATCODE || '"/>';
      END LOOP;
      T_XXX := T_XXX || '</numFmts>';
    END IF;
    T_XXX := T_XXX || '<fonts count="' || WORKBOOK.FONTS.COUNT() || '" x14ac:knownFonts="1">';
    FOR F IN 0 .. WORKBOOK.FONTS.COUNT() - 1
    LOOP
      T_XXX := T_XXX || '<font>' ||
        CASE WHEN WORKBOOK.FONTS( F ).BOLD THEN '<b/>' END ||
        CASE WHEN WORKBOOK.FONTS( F ).ITALIC THEN '<i/>' END ||
        CASE WHEN WORKBOOK.FONTS( F ).UNDERLINE THEN '<u/>' END ||
'<sz val="' || TO_CHAR( WORKBOOK.FONTS( F ).FONTSIZE, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' )  || '"/>
<color ' || CASE WHEN WORKBOOK.FONTS( F ).RGB IS NOT NULL
              THEN 'rgb="' || WORKBOOK.FONTS( F ).RGB
              ELSE 'theme="' || WORKBOOK.FONTS( F ).THEME
            END || '"/>
<name val="' || WORKBOOK.FONTS( F ).NAME || '"/>
<family val="' || WORKBOOK.FONTS( F ).FAMILY || '"/>
<scheme val="none"/>
</font>';
    END LOOP;
    T_XXX := T_XXX || '</fonts>
<fills count="' || WORKBOOK.FILLS.COUNT() || '">';
    FOR F IN 0 .. WORKBOOK.FILLS.COUNT() - 1
    LOOP
      T_XXX := T_XXX || '<fill><patternFill patternType="' || WORKBOOK.FILLS( F ).PATTERNTYPE || '">' ||
         CASE WHEN WORKBOOK.FILLS( F ).FGRGB IS NOT NULL THEN '<fgColor rgb="' || WORKBOOK.FILLS( F ).FGRGB || '"/>' END ||
         '</patternFill></fill>';
    END LOOP;
    T_XXX := T_XXX || '</fills>
<borders count="' || WORKBOOK.BORDERS.COUNT() || '">';
    FOR B IN 0 .. WORKBOOK.BORDERS.COUNT() - 1
    LOOP
      T_XXX := T_XXX || '<border>' ||
         CASE WHEN WORKBOOK.BORDERS( B ).LEFT   IS NULL THEN '<left/>'   ELSE '<left style="'   || WORKBOOK.BORDERS( B ).LEFT   || '"/>' END ||
         CASE WHEN WORKBOOK.BORDERS( B ).RIGHT  IS NULL THEN '<right/>'  ELSE '<right style="'  || WORKBOOK.BORDERS( B ).RIGHT  || '"/>' END ||
         CASE WHEN WORKBOOK.BORDERS( B ).TOP    IS NULL THEN '<top/>'    ELSE '<top style="'    || WORKBOOK.BORDERS( B ).TOP    || '"/>' END ||
         CASE WHEN WORKBOOK.BORDERS( B ).BOTTOM IS NULL THEN '<bottom/>' ELSE '<bottom style="' || WORKBOOK.BORDERS( B ).BOTTOM || '"/>' END ||
         '</border>';
    END LOOP;
    T_XXX := T_XXX || '</borders>
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
</cellStyleXfs>
<cellXfs count="' || ( WORKBOOK.CELLXFS.COUNT() + 1 ) || '">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>';
    FOR X IN 1 .. WORKBOOK.CELLXFS.COUNT()
    LOOP
      T_XXX := T_XXX || '<xf numFmtId="' || WORKBOOK.CELLXFS( X ).NUMFMTID || '" fontId="' || WORKBOOK.CELLXFS( X ).FONTID || '" fillId="' || WORKBOOK.CELLXFS( X ).FILLID || '" borderId="' || WORKBOOK.CELLXFS( X ).BORDERID || '">';
      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 || '<alignment' ||
          CASE WHEN WORKBOOK.CELLXFS( X ).ALIGNMENT.HORIZONTAL IS NOT NULL THEN ' horizontal="' || WORKBOOK.CELLXFS( X ).ALIGNMENT.HORIZONTAL || '"' END ||
          CASE WHEN WORKBOOK.CELLXFS( X ).ALIGNMENT.VERTICAL IS NOT NULL THEN ' vertical="' || WORKBOOK.CELLXFS( X ).ALIGNMENT.VERTICAL || '"' END ||
          CASE WHEN WORKBOOK.CELLXFS( X ).ALIGNMENT.WRAPTEXT THEN ' wrapText="true"' END || '/>';
      END IF;
      T_XXX := T_XXX || '</xf>';
    END LOOP;
    T_XXX := T_XXX || '</cellXfs>
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>
<dxfs count="0"/>
<tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16"/>
<extLst>
<ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>
</ext>
</extLst>
</styleSheet>';
    ADD1XML( T_EXCEL, 'xl/styles.xml', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
<fileVersion appName="xl" lastEdited="5" lowestEdited="5" rupBuild="9302"/>
<workbookPr date1904="true" defaultThemeVersion="124226"/>
<bookViews>
<workbookView xWindow="120" yWindow="45" windowWidth="19155" windowHeight="4935"/>
</bookViews>
<sheets>';
    FOR S IN 1 .. WORKBOOK.SHEETS.COUNT()
    LOOP
      T_XXX := T_XXX || '
<sheet name="' || WORKBOOK.SHEETS( S ).NAME || '" sheetId="' || S || '" r:id="rId' || ( 9 + S ) || '"/>';
    END LOOP;
    T_XXX := T_XXX || '</sheets>';
    IF WORKBOOK.DEFINED_NAMES.COUNT() > 0
    THEN
      T_XXX := T_XXX || '<definedNames>';
      FOR S IN 1 .. WORKBOOK.DEFINED_NAMES.COUNT()
      LOOP
        T_XXX := T_XXX || '
<definedName name="' || WORKBOOK.DEFINED_NAMES( S ).NAME || '"' ||
            CASE WHEN WORKBOOK.DEFINED_NAMES( S ).SHEET IS NOT NULL THEN ' localSheetId="' || TO_CHAR( WORKBOOK.DEFINED_NAMES( S ).SHEET ) || '"' END ||
            '>' || WORKBOOK.DEFINED_NAMES( S ).REF || '</definedName>';
      END LOOP;
      T_XXX := T_XXX || '</definedNames>';
    END IF;
    T_XXX := T_XXX || '<calcPr calcId="144525"/></workbook>';
    ADD1XML( T_EXCEL, 'xl/workbook.xml', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme">
<a:themeElements>
<a:clrScheme name="Office">
<a:dk1>
<a:sysClr val="windowText" lastClr="000000"/>
</a:dk1>
<a:lt1>
<a:sysClr val="window" lastClr="FFFFFF"/>
</a:lt1>
<a:dk2>
<a:srgbClr val="1F497D"/>
</a:dk2>
<a:lt2>
<a:srgbClr val="EEECE1"/>
</a:lt2>
<a:accent1>
<a:srgbClr val="4F81BD"/>
</a:accent1>
<a:accent2>
<a:srgbClr val="C0504D"/>
</a:accent2>
<a:accent3>
<a:srgbClr val="9BBB59"/>
</a:accent3>
<a:accent4>
<a:srgbClr val="8064A2"/>
</a:accent4>
<a:accent5>
<a:srgbClr val="4BACC6"/>
</a:accent5>
<a:accent6>
<a:srgbClr val="F79646"/>
</a:accent6>
<a:hlink>
<a:srgbClr val="0000FF"/>
</a:hlink>
<a:folHlink>
<a:srgbClr val="800080"/>
</a:folHlink>
</a:clrScheme>
<a:fontScheme name="Office">
<a:majorFont>
<a:latin typeface="Cambria"/>
<a:ea typeface=""/>
<a:cs typeface=""/>
<a:font script="Jpan" typeface="MS P????"/>
<a:font script="Hang" typeface="?? ??"/>
<a:font script="Hans" typeface="??"/>
<a:font script="Hant" typeface="????"/>
<a:font script="Arab" typeface="Times New Roman"/>
<a:font script="Hebr" typeface="Times New Roman"/>
<a:font script="Thai" typeface="Tahoma"/>
<a:font script="Ethi" typeface="Nyala"/>
<a:font script="Beng" typeface="Vrinda"/>
<a:font script="Gujr" typeface="Shruti"/>
<a:font script="Khmr" typeface="MoolBoran"/>
<a:font script="Knda" typeface="Tunga"/>
<a:font script="Guru" typeface="Raavi"/>
<a:font script="Cans" typeface="Euphemia"/>
<a:font script="Cher" typeface="Plantagenet Cherokee"/>
<a:font script="Yiii" typeface="Microsoft Yi Baiti"/>
<a:font script="Tibt" typeface="Microsoft Himalaya"/>
<a:font script="Thaa" typeface="MV Boli"/>
<a:font script="Deva" typeface="Mangal"/>
<a:font script="Telu" typeface="Gautami"/>
<a:font script="Taml" typeface="Latha"/>
<a:font script="Syrc" typeface="Estrangelo Edessa"/>
<a:font script="Orya" typeface="Kalinga"/>
<a:font script="Mlym" typeface="Kartika"/>
<a:font script="Laoo" typeface="DokChampa"/>
<a:font script="Sinh" typeface="Iskoola Pota"/>
<a:font script="Mong" typeface="Mongolian Baiti"/>
<a:font script="Viet" typeface="Times New Roman"/>
<a:font script="Uigh" typeface="Microsoft Uighur"/>
<a:font script="Geor" typeface="Sylfaen"/>
</a:majorFont>
<a:minorFont>
<a:latin typeface="Calibri"/>
<a:ea typeface=""/>
<a:cs typeface=""/>
<a:font script="Jpan" typeface="MS P????"/>
<a:font script="Hang" typeface="?? ??"/>
<a:font script="Hans" typeface="??"/>
<a:font script="Hant" typeface="????"/>
<a:font script="Arab" typeface="Arial"/>
<a:font script="Hebr" typeface="Arial"/>
<a:font script="Thai" typeface="Tahoma"/>
<a:font script="Ethi" typeface="Nyala"/>
<a:font script="Beng" typeface="Vrinda"/>
<a:font script="Gujr" typeface="Shruti"/>
<a:font script="Khmr" typeface="DaunPenh"/>
<a:font script="Knda" typeface="Tunga"/>
<a:font script="Guru" typeface="Raavi"/>
<a:font script="Cans" typeface="Euphemia"/>
<a:font script="Cher" typeface="Plantagenet Cherokee"/>
<a:font script="Yiii" typeface="Microsoft Yi Baiti"/>
<a:font script="Tibt" typeface="Microsoft Himalaya"/>
<a:font script="Thaa" typeface="MV Boli"/>
<a:font script="Deva" typeface="Mangal"/>
<a:font script="Telu" typeface="Gautami"/>
<a:font script="Taml" typeface="Latha"/>
<a:font script="Syrc" typeface="Estrangelo Edessa"/>
<a:font script="Orya" typeface="Kalinga"/>
<a:font script="Mlym" typeface="Kartika"/>
<a:font script="Laoo" typeface="DokChampa"/>
<a:font script="Sinh" typeface="Iskoola Pota"/>
<a:font script="Mong" typeface="Mongolian Baiti"/>
<a:font script="Viet" typeface="Arial"/>
<a:font script="Uigh" typeface="Microsoft Uighur"/>
<a:font script="Geor" typeface="Sylfaen"/>
</a:minorFont>
</a:fontScheme>
<a:fmtScheme name="Office">
<a:fillStyleLst>
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="50000"/>
<a:satMod val="300000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="35000">
<a:schemeClr val="phClr">
<a:tint val="37000"/>
<a:satMod val="300000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:tint val="15000"/>
<a:satMod val="350000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="16200000" scaled="1"/>
</a:gradFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:shade val="51000"/>
<a:satMod val="130000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="80000">
<a:schemeClr val="phClr">
<a:shade val="93000"/>
<a:satMod val="130000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="94000"/>
<a:satMod val="135000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:lin ang="16200000" scaled="0"/>
</a:gradFill>
</a:fillStyleLst>
<a:lnStyleLst>
<a:ln w="9525" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr">
<a:shade val="95000"/>
<a:satMod val="105000"/>
</a:schemeClr>
</a:solidFill>
<a:prstDash val="solid"/>
</a:ln>
<a:ln w="25400" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:prstDash val="solid"/>
</a:ln>
<a:ln w="38100" cap="flat" cmpd="sng" algn="ctr">
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:prstDash val="solid"/>
</a:ln>
</a:lnStyleLst>
<a:effectStyleLst>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="38000"/>
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
</a:effectStyle>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="35000"/>
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
</a:effectStyle>
<a:effectStyle>
<a:effectLst>
<a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
<a:srgbClr val="000000">
<a:alpha val="35000"/>
</a:srgbClr>
</a:outerShdw>
</a:effectLst>
<a:scene3d>
<a:camera prst="orthographicFront">
<a:rot lat="0" lon="0" rev="0"/>
</a:camera>
<a:lightRig rig="threePt" dir="t">
<a:rot lat="0" lon="0" rev="1200000"/>
</a:lightRig>
</a:scene3d>
<a:sp3d>
<a:bevelT w="63500" h="25400"/>
</a:sp3d>
</a:effectStyle>
</a:effectStyleLst>
<a:bgFillStyleLst>
<a:solidFill>
<a:schemeClr val="phClr"/>
</a:solidFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="40000"/>
<a:satMod val="350000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="40000">
<a:schemeClr val="phClr">
<a:tint val="45000"/>
<a:shade val="99000"/>
<a:satMod val="350000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="20000"/>
<a:satMod val="255000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:path path="circle">
<a:fillToRect l="50000" t="-80000" r="50000" b="180000"/>
</a:path>
</a:gradFill>
<a:gradFill rotWithShape="1">
<a:gsLst>
<a:gs pos="0">
<a:schemeClr val="phClr">
<a:tint val="80000"/>
<a:satMod val="300000"/>
</a:schemeClr>
</a:gs>
<a:gs pos="100000">
<a:schemeClr val="phClr">
<a:shade val="30000"/>
<a:satMod val="200000"/>
</a:schemeClr>
</a:gs>
</a:gsLst>
<a:path path="circle">
<a:fillToRect l="50000" t="50000" r="50000" b="50000"/>
</a:path>
</a:gradFill>
</a:bgFillStyleLst>
</a:fmtScheme>
</a:themeElements>
<a:objectDefaults/>
<a:extraClrSchemeLst/>
</a:theme>';
    ADD1XML( T_EXCEL, 'xl/theme/theme1.xml', T_XXX );
    FOR S IN 1 .. 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 := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<dimension ref="' || ALFAN_COL( T_COL_MIN ) || WORKBOOK.SHEETS( S ).ROWS.FIRST() || ':' || ALFAN_COL( T_COL_MAX ) || WORKBOOK.SHEETS( S ).ROWS.LAST() || '"/>
<sheetViews>
<sheetView' || CASE WHEN S = 1 THEN ' tabSelected="1"' END || ' workbookViewId="0">';
      IF WORKBOOK.SHEETS( S ).FREEZE_ROWS > 0 AND WORKBOOK.SHEETS( S ).FREEZE_COLS > 0
      THEN
        T_XXX := T_XXX || ( '<pane xSplit="' || WORKBOOK.SHEETS( S ).FREEZE_COLS || '" '
                          || 'ySplit="' || WORKBOOK.SHEETS( S ).FREEZE_ROWS || '" '
                          || 'topLeftCell="' || ALFAN_COL( WORKBOOK.SHEETS( S ).FREEZE_COLS + 1 ) || ( WORKBOOK.SHEETS( S ).FREEZE_ROWS + 1 ) || '" '
                          || 'activePane="bottomLeft" state="frozen"/>'
                          );
      ELSE
        IF WORKBOOK.SHEETS( S ).FREEZE_ROWS > 0
        THEN
          T_XXX := T_XXX || '<pane ySplit="' || WORKBOOK.SHEETS( S ).FREEZE_ROWS || '" topLeftCell="A' || ( WORKBOOK.SHEETS( S ).FREEZE_ROWS + 1 ) || '" activePane="bottomLeft" state="frozen"/>';
        END IF;
        IF WORKBOOK.SHEETS( S ).FREEZE_COLS > 0
        THEN
          T_XXX := T_XXX || '<pane xSplit="' || WORKBOOK.SHEETS( S ).FREEZE_COLS || '" topLeftCell="' || ALFAN_COL( WORKBOOK.SHEETS( S ).FREEZE_COLS + 1 ) || '1" activePane="bottomLeft" state="frozen"/>';
        END IF;
      END IF;
      T_XXX := T_XXX || '</sheetView>
</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>';
      IF WORKBOOK.SHEETS( S ).WIDTHS.COUNT() > 0
      THEN
        T_XXX := T_XXX || '<cols>';
        T_COL_IND := WORKBOOK.SHEETS( S ).WIDTHS.FIRST();
        WHILE T_COL_IND IS NOT NULL
        LOOP
          T_XXX := T_XXX ||
             '<col min="' || T_COL_IND || '" max="' || T_COL_IND || '" width="' || TO_CHAR( WORKBOOK.SHEETS( S ).WIDTHS( T_COL_IND ), 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' ) || '" customWidth="1"/>';
          T_COL_IND := WORKBOOK.SHEETS( S ).WIDTHS.NEXT( T_COL_IND );
        END LOOP;
        T_XXX := T_XXX || '</cols>';
      END IF;
      T_XXX := T_XXX || '<sheetData>';
      T_ROW_IND := WORKBOOK.SHEETS( S ).ROWS.FIRST();
      T_TMP := NULL;
      WHILE T_ROW_IND IS NOT NULL
      LOOP
        T_TMP :=  T_TMP || '<row r="' || T_ROW_IND || '" spans="' || T_COL_MIN || ':' || T_COL_MAX || '">';
        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 := '<c r="' || ALFAN_COL( T_COL_IND ) || T_ROW_IND || '"'
                 || ' ' || WORKBOOK.SHEETS( S ).ROWS( T_ROW_IND )( T_COL_IND ).STYLE
                 || '><v>'
                 || TO_CHAR( WORKBOOK.SHEETS( S ).ROWS( T_ROW_IND )( T_COL_IND ).VALUE, 'TM9', 'NLS_NUMERIC_CHARACTERS=.,' )
                 || '</v></c>';
          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 || '</row>';
        T_ROW_IND := WORKBOOK.SHEETS( S ).ROWS.NEXT( T_ROW_IND );
      END LOOP;
      T_TMP :=  T_TMP || '</sheetData>';
      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 || '<autoFilter ref="' ||
            ALFAN_COL( NVL( WORKBOOK.SHEETS( S ).AUTOFILTERS( A ).COLUMN_START, T_COL_MIN ) ) ||
            NVL( WORKBOOK.SHEETS( S ).AUTOFILTERS( A ).ROW_START, WORKBOOK.SHEETS( S ).ROWS.FIRST() ) || ':' ||
            ALFAN_COL( COALESCE( WORKBOOK.SHEETS( S ).AUTOFILTERS( A ).COLUMN_END, WORKBOOK.SHEETS( S ).AUTOFILTERS( A ).COLUMN_START, T_COL_MAX ) ) ||
            NVL( WORKBOOK.SHEETS( S ).AUTOFILTERS( A ).ROW_END, WORKBOOK.SHEETS( S ).ROWS.LAST() ) || '"/>';
      END LOOP;
      IF WORKBOOK.SHEETS( S ).MERGECELLS.COUNT() > 0
      THEN
        T_XXX := T_XXX || '<mergeCells count="' || TO_CHAR( WORKBOOK.SHEETS( S ).MERGECELLS.COUNT() ) || '">';
        FOR M IN 1 ..  WORKBOOK.SHEETS( S ).MERGECELLS.COUNT()
        LOOP
          T_XXX := T_XXX || '<mergeCell ref="' || WORKBOOK.SHEETS( S ).MERGECELLS( M ) || '"/>';
        END LOOP;
        T_XXX := T_XXX || '</mergeCells>';
      END IF;
--
      IF WORKBOOK.SHEETS( S ).VALIDATIONS.COUNT() > 0
      THEN
        T_XXX := T_XXX || '<dataValidations count="' || TO_CHAR( WORKBOOK.SHEETS( S ).VALIDATIONS.COUNT() ) || '">';
        FOR M IN 1 ..  WORKBOOK.SHEETS( S ).VALIDATIONS.COUNT()
        LOOP
          T_XXX := T_XXX || '<dataValidation' ||
              ' type="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).TYPE || '"' ||
              ' errorStyle="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).ERRORSTYLE || '"' ||
              ' allowBlank="' || CASE WHEN NVL( WORKBOOK.SHEETS( S ).VALIDATIONS( M ).ALLOWBLANK, TRUE ) THEN '1' ELSE '0' END || '"' ||
              ' sqref="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).SQREF || '"';
          IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).PROMPT IS NOT NULL
          THEN
            T_XXX := T_XXX || ' showInputMessage="1" prompt="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).PROMPT || '"';
            IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).TITLE IS NOT NULL
            THEN
              T_XXX := T_XXX || ' promptTitle="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).TITLE || '"';
            END IF;
          END IF;
          IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).SHOWERRORMESSAGE
          THEN
            T_XXX := T_XXX || ' showErrorMessage="1"';
            IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).ERROR_TITLE IS NOT NULL
            THEN
              T_XXX := T_XXX || ' errorTitle="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).ERROR_TITLE || '"';
            END IF;
            IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).ERROR_TXT IS NOT NULL
            THEN
              T_XXX := T_XXX || ' error="' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).ERROR_TXT || '"';
            END IF;
          END IF;
          T_XXX := T_XXX || '>';
          IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).FORMULA1 IS NOT NULL
          THEN
            T_XXX := T_XXX || '<formula1>' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).FORMULA1 || '</formula1>';
          END IF;
          IF WORKBOOK.SHEETS( S ).VALIDATIONS( M ).FORMULA2 IS NOT NULL
          THEN
            T_XXX := T_XXX || '<formula2>' || WORKBOOK.SHEETS( S ).VALIDATIONS( M ).FORMULA2 || '</formula2>';
          END IF;
          T_XXX := T_XXX || '</dataValidation>';
        END LOOP;
        T_XXX := T_XXX || '</dataValidations>';
      END IF;
--
      IF WORKBOOK.SHEETS( S ).HYPERLINKS.COUNT() > 0
      THEN
        T_XXX := T_XXX || '<hyperlinks>';
        FOR H IN 1 ..  WORKBOOK.SHEETS( S ).HYPERLINKS.COUNT()
        LOOP
          T_XXX := T_XXX || '<hyperlink ref="' || WORKBOOK.SHEETS( S ).HYPERLINKS( H ).CELL || '" r:id="rId' || H || '"/>';
        END LOOP;
        T_XXX := T_XXX || '</hyperlinks>';
      END IF;
      T_XXX := T_XXX || '<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>';
      IF WORKBOOK.SHEETS( S ).COMMENTS.COUNT() > 0
      THEN
        T_XXX := T_XXX || '<legacyDrawing r:id="rId' || ( WORKBOOK.SHEETS( S ).HYPERLINKS.COUNT() + 1 ) || '"/>';
      END IF;
--
      T_XXX := T_XXX || '</worksheet>';
      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 := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">';
        IF WORKBOOK.SHEETS( S ).COMMENTS.COUNT() > 0
        THEN
          T_XXX := T_XXX || '<Relationship Id="rId' || ( WORKBOOK.SHEETS( S ).HYPERLINKS.COUNT() + 2 ) || '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments" Target="../comments' || S || '.xml"/>';
          T_XXX := T_XXX || '<Relationship Id="rId' || ( WORKBOOK.SHEETS( S ).HYPERLINKS.COUNT() + 1 ) || '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing" Target="../drawings/vmlDrawing' || S || '.vml"/>';
        END IF;
        FOR H IN 1 ..  WORKBOOK.SHEETS( S ).HYPERLINKS.COUNT()
        LOOP
          T_XXX := T_XXX || '<Relationship Id="rId' || H || '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="' || WORKBOOK.SHEETS( S ).HYPERLINKS( H ).URL || '" TargetMode="External"/>';
        END LOOP;
        T_XXX := T_XXX || '</Relationships>';
        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 := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<authors>';
          owa_util.mime_header('application/vnd.ms-excel', FALSE, 'WE8MSWIN1252' );
          CNT := 0;
          AUTHOR_IND := AUTHORS.FIRST();
          WHILE AUTHOR_IND IS NOT NULL OR AUTHORS.NEXT( AUTHOR_IND ) IS NOT NULL
          LOOP
            AUTHORS( AUTHOR_IND ) := CNT;
            T_XXX := T_XXX || '<author>' || AUTHOR_IND || '</author>';
            CNT := CNT + 1;
            AUTHOR_IND := AUTHORS.NEXT( AUTHOR_IND );
          END LOOP;
        END;
        T_XXX := T_XXX || '</authors><commentList>';
        FOR C IN 1 .. WORKBOOK.SHEETS( S ).COMMENTS.COUNT()
        LOOP
          T_XXX := T_XXX || '<comment ref="' || ALFAN_COL( WORKBOOK.SHEETS( S ).COMMENTS( C ).COLUMN ) ||
             TO_CHAR( WORKBOOK.SHEETS( S ).COMMENTS( C ).ROW || '" authorId="' || AUTHORS( WORKBOOK.SHEETS( S ).COMMENTS( C ).AUTHOR ) ) || '">
<text>';
          IF WORKBOOK.SHEETS( S ).COMMENTS( C ).AUTHOR IS NOT NULL
          THEN
            T_XXX := T_XXX || '<r><rPr><b/><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">' ||
               WORKBOOK.SHEETS( S ).COMMENTS( C ).AUTHOR || ':</t></r>';
          END IF;
          T_XXX := T_XXX || '<r><rPr><sz val="9"/><color indexed="81"/><rFont val="Tahoma"/><charset val="1"/></rPr><t xml:space="preserve">' ||
             CASE WHEN WORKBOOK.SHEETS( S ).COMMENTS( C ).AUTHOR IS NOT NULL THEN '
' END || WORKBOOK.SHEETS( S ).COMMENTS( C ).TEXT || '</t></r></text></comment>';
        END LOOP;
        T_XXX := T_XXX || '</commentList></comments>';
        ADD1XML( T_EXCEL, 'xl/comments' || S || '.xml', T_XXX );
        T_XXX := '<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
<o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="2"/></o:shapelayout>
<v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe"><v:stroke joinstyle="miter"/><v:path gradientshapeok="t" o:connecttype="rect"/></v:shapetype>';
        FOR C IN 1 .. WORKBOOK.SHEETS( S ).COMMENTS.COUNT()
        LOOP
          T_XXX := T_XXX || '<v:shape id="_x0000_s' || TO_CHAR( C ) || '" type="#_x0000_t202"
style="position:absolute;margin-left:35.25pt;margin-top:3pt;z-index:' || TO_CHAR( C ) || ';visibility:hidden;" fillcolor="#ffffe1" o:insetmode="auto">
<v:fill color2="#ffffe1"/><v:shadow on="t" color="black" obscured="t"/><v:path o:connecttype="none"/>
<v:textbox style="mso-direction-alt:auto"><div style="text-align:left"></div></v:textbox>
<x:ClientData ObjectType="Note"><x:MoveWithCells/><x:SizeWithCells/>';
          T_W := 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( '<x:Anchor>' || 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 ) || '</x:Anchor>' );
          T_XXX := T_XXX || TO_CHAR( '<x:AutoFill>False</x:AutoFill><x:Row>' ||
            ( WORKBOOK.SHEETS( S ).COMMENTS( C ).ROW - 1 ) || '</x:Row><x:Column>' ||
            ( WORKBOOK.SHEETS( S ).COMMENTS( C ).COLUMN - 1 ) || '</x:Column></x:ClientData></v:shape>' );
        END LOOP;
        T_XXX := T_XXX || '</xml>';
        ADD1XML( T_EXCEL, 'xl/drawings/vmlDrawing' || S || '.vml', T_XXX );
      END IF;
--
    END LOOP;
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>';
    FOR S IN 1 .. WORKBOOK.SHEETS.COUNT()
    LOOP
      T_XXX := T_XXX || '
<Relationship Id="rId' || ( 9 + S ) || '" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet' || S || '.xml"/>';
    END LOOP;
    T_XXX := T_XXX || '</Relationships>';
    ADD1XML( T_EXCEL, 'xl/_rels/workbook.xml.rels', T_XXX );
    T_XXX := '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="' || WORKBOOK.STR_CNT || '" uniqueCount="' || WORKBOOK.STRINGS.COUNT() || '">';
    T_TMP := NULL;
    FOR I IN 0 .. WORKBOOK.STR_IND.COUNT() - 1
    LOOP
      T_STR := '<si><t>' || DBMS_XMLGEN.CONVERT( SUBSTR( WORKBOOK.STR_IND( I ), 1, 32000 ) ) || '</t></si>';
      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 || '</sst>';
    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 CLOB
    , P_COLUMN_HEADERS BOOLEAN := TRUE
    , P_DIRECTORY VARCHAR2 := NULL
    , P_FILENAME VARCHAR2 := NULL
    , P_SHEET PLS_INTEGER := NULL
    , PO_FILE OUT BLOB
    )
  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;
  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 );
    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 );
      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 );
                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 );
                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 );
                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 );
    IF ( P_DIRECTORY IS NOT NULL AND  P_FILENAME IS NOT NULL )
    THEN
      /*SAVE( P_DIRECTORY, P_FILENAME );*/
      PO_FILE :=FINISH; 
    END IF;
  EXCEPTION
    WHEN OTHERS
    THEN
      IF DBMS_SQL.IS_OPEN( T_C )
      THEN
        DBMS_SQL.CLOSE_CURSOR( T_C );
      END IF;
  END;
END;
/

						

css


CREATE OR REPLACE PACKAGE REMUNER.AS_XLSX_JULIAN IS

  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 CLOB
    , P_COLUMN_HEADERS BOOLEAN := TRUE
    , P_DIRECTORY VARCHAR2 := NULL
    , P_FILENAME VARCHAR2 := NULL
    , P_SHEET PLS_INTEGER := NULL
    , PO_FILE  OUT BLOB
    );
  PROCEDURE BLOB2FILE ( P_BLOB BLOB, P_DIRECTORY VARCHAR2 := 'MY_DIR', P_FILENAME VARCHAR2 := 'my.xlsx');

/* EXAMPLE
BEGIN
  AS_XLSX.CLEAR_WORKBOOK;
  AS_XLSX.NEW_SHEET;
  AS_XLSX.CELL      (5,1,5);
  AS_XLSX.CELL      (3,1,3);
  AS_XLSX.CELL      (2,2,45);
  AS_XLSX.CELL      (3,2,'ANTON SCHEFFER',P_ALIGNMENT     => AS_XLSX.GET_ALIGNMENT (P_WRAPTEXT => TRUE));
  AS_XLSX.CELL      (1,4,SYSDATE,P_FONTID       => AS_XLSX.GET_FONT ('CALIBRI',P_RGB     => 'FFFF0000'));
  AS_XLSX.CELL      (2,4,SYSDATE,P_NUMFMTID     => AS_XLSX.GET_NUMFMT ('DD/MM/YYYY H:MM'));
  AS_XLSX.CELL      (3,4,SYSDATE,P_NUMFMTID     => AS_XLSX.GET_NUMFMT(AS_XLSX.ORAFMT2EXCEL ('DD/MON/YYYY')));
  AS_XLSX.CELL      (5,5,75,P_BORDERID          => AS_XLSX.GET_BORDER ('DOUBLE','DOUBLE','DOUBLE','DOUBLE'));
  AS_XLSX.CELL      (2,3,33);
  AS_XLSX.HYPERLINK (1,6,'HTTP://WWW.AMIS.NL', 'AMIS SITE');
  AS_XLSX.CELL      (1,7,'SOME MERGED CELLS',P_ALIGNMENT     => AS_XLSX.GET_ALIGNMENT(P_HORIZONTAL     => 'CENTER'));
  AS_XLSX.MERGECELLS(1,7,3,7);

  FOR I IN 1 .. 5
  LOOP
    AS_XLSX.COMMENT (3, I + 3, 'ROW ' || (I + 3), 'ANTON');
  END LOOP;

  AS_XLSX.NEW_SHEET;
  AS_XLSX.SET_ROW (1, P_FILLID => AS_XLSX.GET_FILL ('SOLID', 'FFFF0000'));

  FOR I IN 1 .. 5
  LOOP
    AS_XLSX.CELL (1, I, I);
    AS_XLSX.CELL (2, I, I * 3);
    AS_XLSX.CELL (3, I, 'X ' || I * 3);
  END LOOP;

  AS_XLSX.QUERY2SHEET
    ('SELECT  ROWNUM,
              X.*,
              CASE WHEN MOD( ROWNUM, 2 ) = 0 THEN ROWNUM * 3 END DEMO,
              CASE WHEN MOD( ROWNUM, 2 ) = 1 THEN ''DEMO '' || ROWNUM END DEMO2
       FROM   DUAL X CONNECT BY ROWNUM <= 5'
    );
  AS_XLSX.SAVE ('MY_DIR', 'MY.XLSX');

END;


BEGIN
  AS_XLSX.CLEAR_WORKBOOK;
  AS_XLSX.NEW_SHEET;
  AS_XLSX.CELL (1,6,5);
  AS_XLSX.CELL (1,7,3);
  AS_XLSX.CELL (1,8,7);
  AS_XLSX.NEW_SHEET;
  AS_XLSX.CELL (2,6,15, P_SHEET => 2);
  AS_XLSX.CELL (2,7,13, P_SHEET => 2);
  AS_XLSX.CELL (2,8,17, P_SHEET => 2);
  AS_XLSX.LIST_VALIDATION (6,3,1,6,1,8,P_SHOW_ERROR => TRUE,P_SHEET  => 1);
  AS_XLSX.DEFINED_NAME (2,6,2,8,'ANTON',2);
  AS_XLSX.LIST_VALIDATION (6,
                           1,
                           'ANTON',
                           P_STYLE           => 'INFORMATION',
                           P_TITLE           => 'VALID VALUES ARE',
                           P_PROMPT          => '13, 15 AND 17',
                           P_SHOW_ERROR      => TRUE,
                           P_ERROR_TITLE     => 'ARE YOU SURE?',
                           P_ERROR_TXT       => 'VALID VALUES ARE: 13, 15 AND 17',
                           P_SHEET           => 1
                          );
  AS_XLSX.SAVE ('MY_DIR', 'MY.XLSX');
END;


BEGIN
  AS_XLSX.CLEAR_WORKBOOK;
  AS_XLSX.NEW_SHEET;
  AS_XLSX.CELL (1, 6, 5);
  AS_XLSX.CELL (1, 7, 3);
  AS_XLSX.CELL (1, 8, 7);
  AS_XLSX.SET_AUTOFILTER (1, 1, P_ROW_START => 5, P_ROW_END => 8);
  AS_XLSX.NEW_SHEET;
  AS_XLSX.CELL (2, 6, 5);
  AS_XLSX.CELL (2, 7, 3);
  AS_XLSX.CELL (2, 8, 7);
  AS_XLSX.SET_AUTOFILTER (2, 2, P_ROW_START => 5, P_ROW_END => 8);
  AS_XLSX.SAVE ('MY_DIR', 'MY.XLSX');
END;


BEGIN
  AS_XLSX.CLEAR_WORKBOOK;
  AS_XLSX.NEW_SHEET;

  FOR C IN 1 .. 10
  LOOP
    AS_XLSX.CELL (C, 1, 'COL' || C);
    AS_XLSX.CELL (C, 2, 'VAL' || C);
    AS_XLSX.CELL (C, 3, C);
  END LOOP;

  AS_XLSX.FREEZE_ROWS (1);
  AS_XLSX.NEW_SHEET;

  FOR R IN 1 .. 10
  LOOP
    AS_XLSX.CELL (1, R, 'ROW' || R);
    AS_XLSX.CELL (2, R, 'VAL' || R);
    AS_XLSX.CELL (3, R, R);
  END LOOP;

  AS_XLSX.FREEZE_COLS (3);
  AS_XLSX.NEW_SHEET;
  AS_XLSX.CELL (3, 3, 'START FREEZE');
  AS_XLSX.FREEZE_PANE (3, 3);
  AS_XLSX.SAVE ('MY_DIR', 'MY.XLSX');
END;
*/
END;
/