CREATE OR REPLACE PACKAGE AS_READ_XLSX
IS
TYPE TP_ONE_CELL
IS
  RECORD
  (
    SHEET_NR   NUMBER(2),
    SHEET_NAME VARCHAR(4000),
    ROW_NR     NUMBER(10),
    COL_NR     NUMBER(10),
    CELL       VARCHAR2(100),
    CELL_TYPE  VARCHAR2(1),
    STRING_VAL VARCHAR2(4000),
    NUMBER_VAL NUMBER,
    DATE_VAL   DATE,
    FORMULA    VARCHAR2(4000)
  );
TYPE TP_ALL_CELLS
IS
  TABLE OF TP_ONE_CELL;
  FUNCTION READ(
      P_XLSX BLOB,
      P_SHEETS VARCHAR2 := NULL,
      P_CELL   VARCHAR2 := NULL )
    RETURN TP_ALL_CELLS PIPELINED;
  FUNCTION FILE2BLOB(
      P_DIR       VARCHAR2,
      P_FILE_NAME VARCHAR2
    )
    RETURN BLOB;
END AS_READ_XLSX;

<< End >>

<< Package Body <>
<< Begins >>

CREATE OR REPLACE PACKAGE body as_read_xlsx
IS
  --
FUNCTION read(
    p_xlsx BLOB,
    p_sheets VARCHAR2 := NULL,
    p_cell   VARCHAR2 := NULL )
  RETURN tp_all_cells pipelined
IS
  t_date1904 BOOLEAN;
type tp_date
IS
  TABLE OF BOOLEAN INDEX BY pls_integer;
  t_xf_date tp_date;
  t_numfmt_date tp_date;
type tp_strings
IS
  TABLE OF VARCHAR2(32767) INDEX BY pls_integer;
  t_strings tp_strings;
  t_sheet_ids tp_strings;
  t_sheet_names tp_strings;
  t_r   VARCHAR2(32767);
  t_s   VARCHAR2(32767);
  t_val VARCHAR2(32767);
  t_t   VARCHAR2(400);
  t_nr  NUMBER;
  t_c pls_integer;
  t_x pls_integer;
  t_xx pls_integer;
  t_ns VARCHAR2(200) :=
  'xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
  t_nd dbms_xmldom.domnode;
  t_nd2 dbms_xmldom.domnode;
  t_nl dbms_xmldom.domnodelist;
  t_nl2 dbms_xmldom.domnodelist;
  t_nl3 dbms_xmldom.domnodelist;
  t_one_cell tp_one_cell;
  --
FUNCTION blob2node(
    p_blob BLOB )
  RETURN dbms_xmldom.domnode
IS
BEGIN
  IF p_blob IS NULL OR dbms_lob.getlength( p_blob ) = 0
    THEN
    RETURN NULL;
  END IF;
  RETURN dbms_xmldom.makenode( dbms_xmldom.getdocumentelement(
  dbms_xmldom.newdomdocument( xmltype( p_blob, nls_charset_id( 'AL32UTF8' ) ) )
  ) );
EXCEPTION
WHEN OTHERS
  THEN
  DECLARE
    t_nd dbms_xmldom.domnode;
    t_clob CLOB;
    t_dest_offset  INTEGER;
    t_src_offset   INTEGER;
    t_lang_context NUMBER := dbms_lob.default_lang_ctx;
    t_warning      INTEGER;
  BEGIN
    dbms_lob.createtemporary( t_clob, true );
    t_dest_offset := 1;
    t_src_offset  := 1;
    dbms_lob.converttoclob( t_clob
    , p_blob
    , dbms_lob.lobmaxsize
    , t_dest_offset
    , t_src_offset
    , nls_charset_id('AL32UTF8')
    , t_lang_context
    , t_warning
    );
    t_nd := dbms_xmldom.makenode( dbms_xmldom.getdocumentelement(
    dbms_xmldom.newdomdocument( t_clob ) ) );
    dbms_lob.freetemporary( t_clob );
    RETURN t_nd;
  END;
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;
--
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 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;
--
FUNCTION get_file(
    p_zipped_blob BLOB
    ,
    p_file_name VARCHAR2
  )
  RETURN BLOB
IS
  t_tmp BLOB;
  t_ind      INTEGER;
  t_hd_ind   INTEGER;
  t_fl_ind   INTEGER;
  t_encoding VARCHAR2(10);
  t_len      INTEGER;
BEGIN
  t_ind := dbms_lob.getlength( p_zipped_blob ) - 21;
  LOOP
    EXIT
  WHEN t_ind < 1 OR dbms_lob.substr( p_zipped_blob, 4, t_ind ) = hextoraw(
    '504B0506' ); -- End of central directory signature
    t_ind := t_ind - 1;
  END LOOP;
  --
  IF t_ind <= 0
    THEN
    RETURN NULL;
  END IF;
  --
  t_hd_ind := blob2num( p_zipped_blob, 4, t_ind + 16 ) + 1;
  FOR i                                        IN 1 .. blob2num( p_zipped_blob,
  2, t_ind                                      + 8 )
  LOOP
    IF utl_raw.bit_and( dbms_lob.substr( p_zipped_blob, 1, t_hd_ind + 9 ),
      hextoraw( '08' ) ) = hextoraw( '08' )
      THEN
      t_encoding := 'AL32UTF8'; -- utf8
    ELSE
      t_encoding := 'US8PC437'; -- IBM codepage 437
    END IF;
    IF p_file_name = utl_i18n.raw_to_char
      ( dbms_lob.substr( p_zipped_blob
      , blob2num( p_zipped_blob, 2, t_hd_ind + 28 )
      , t_hd_ind                             + 46
      )
      , t_encoding
      )
      THEN
      t_len := blob2num( p_zipped_blob, 4, t_hd_ind + 24 ); -- uncompressed
      -- length
      IF t_len = 0
        THEN
        IF SUBSTR( p_file_name, -1 ) IN ( '/', '\' )
          THEN -- directory/folder
          RETURN NULL;
        ELSE -- empty file
          RETURN empty_blob();
        END IF;
      END IF;
      --
      IF dbms_lob.substr( p_zipped_blob, 2, t_hd_ind + 10 ) = hextoraw( '0800'
        ) -- deflate
        THEN
        t_fl_ind := blob2num( p_zipped_blob, 4, t_hd_ind + 42 );
        t_tmp    := hextoraw( '1F8B0800000000000003' ); -- gzip header
        dbms_lob.copy( t_tmp
        , p_zipped_blob
        , blob2num( p_zipped_blob, 4, t_hd_ind + 20 )
        , 11
        , t_fl_ind + 31
                   + blob2num( p_zipped_blob, 2, t_fl_ind + 27 ) -- File name
        -- length
        + blob2num( p_zipped_blob, 2, t_fl_ind + 29 ) -- Extra field length
        );
        dbms_lob.append( t_tmp, utl_raw.concat( dbms_lob.substr( p_zipped_blob,
        4, t_hd_ind + 16 )       -- CRC32
        , little_endian( t_len ) -- uncompressed length
        )
        );
        RETURN utl_compress.lz_uncompress( t_tmp );
      END IF;
      --
      IF dbms_lob.substr( p_zipped_blob, 2, t_hd_ind + 10 ) = hextoraw( '0000'
        ) -- The file is stored (no compression)
        THEN
        t_fl_ind := blob2num( p_zipped_blob, 4, t_hd_ind + 42 );
        dbms_lob.createtemporary( t_tmp, true );
        dbms_lob.copy( t_tmp
        , p_zipped_blob
        , t_len
        , 1
        , t_fl_ind + 31
                   + blob2num( p_zipped_blob, 2, t_fl_ind + 27 ) -- File name
        -- length
        + blob2num( p_zipped_blob, 2, t_fl_ind + 29 ) -- Extra field length
        );
        RETURN t_tmp;
      END IF;
    END IF;
    t_hd_ind := t_hd_ind + 46
                         + blob2num( p_zipped_blob, 2, t_hd_ind + 28 ) -- File
    -- name length
    + blob2num( p_zipped_blob, 2, t_hd_ind + 30 )  -- Extra field length
    + blob2num( p_zipped_blob, 2, t_hd_ind + 32 ); -- File comment length
  END LOOP;
  --
  RETURN NULL;
END;
--
BEGIN
  t_one_cell.cell_type  := 'S';
  t_one_cell.sheet_name :=
  'This doesn''t look like an Excel (xlsx) file to me!';
  t_one_cell.string_val              := t_one_cell.sheet_name;
  IF dbms_lob.substr( p_xlsx, 4, 1 ) != hextoraw( '504B0304' )
    THEN
    pipe row( t_one_cell );
    RETURN;
  END IF;
  t_nd := blob2node( get_file( p_xlsx, 'xl/workbook.xml' ) );
  IF dbms_xmldom.isnull( t_nd )
    THEN
    pipe row( t_one_cell );
    RETURN;
  END IF;
  t_date1904 := lower( dbms_xslprocessor.valueof( t_nd,
  '/workbook/workbookPr/@date1904', t_ns ) ) IN ( 'true', '1' );
  t_nl := dbms_xslprocessor.selectnodes( t_nd, '/workbook/sheets/sheet', t_ns )
  ;
  FOR i IN 0 .. dbms_xmldom.getlength( t_nl ) - 1
  LOOP
    t_sheet_ids( i + 1 ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl,
    i ), '@r:id',
    'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"'
    );
    t_sheet_names( i + 1 ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl
    , i ), '@name' );
  END LOOP;
  t_nd := blob2node( get_file( p_xlsx, 'xl/styles.xml' ) );
  t_nl := dbms_xslprocessor.selectnodes( t_nd, '/styleSheet/numFmts/numFmt',
  t_ns );
  FOR i IN 0 .. dbms_xmldom.getlength( t_nl ) - 1
  LOOP
    t_val := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ),
    '@formatCode' );
    IF ( instr( t_val, 'dd' ) > 0
      OR instr( t_val, 'mm' ) > 0
      OR instr( t_val, 'yy' ) > 0
      )
      THEN
      t_numfmt_date( dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl, i ),
      '@numFmtId' ) ) := true;
    END IF;
  END LOOP;
  t_numfmt_date( 14 ) := true;
  t_numfmt_date( 15 ) := true;
  t_numfmt_date( 16 ) := true;
  t_numfmt_date( 17 ) := true;
  t_numfmt_date( 22 ) := true;
  t_nl                := dbms_xslprocessor.selectnodes( t_nd,
  '/styleSheet/cellXfs/xf/@numFmtId', t_ns );
  FOR i IN 0 .. dbms_xmldom.getlength( t_nl ) - 1
  LOOP
    t_xf_date( i ) := t_numfmt_date.exists( dbms_xmldom.getnodevalue(
    dbms_xmldom.item( t_nl, i ) ) );
  END LOOP;
  t_nd := blob2node( get_file( p_xlsx, 'xl/sharedStrings.xml' ) );
  IF NOT dbms_xmldom.isnull( t_nd )
    THEN
    t_x  := 0;
    t_xx := 5000;
    LOOP
      t_nl := dbms_xslprocessor.selectnodes( t_nd, '/sst/si[position()>="' ||
      TO_CHAR( t_x * t_xx + 1 ) || '" and position()<=" ' || TO_CHAR( ( t_x + 1
      )            * t_xx ) || '"]', t_ns );
      EXIT
    WHEN dbms_xmldom.getlength( t_nl ) = 0;
      t_x                             := t_x + 1;
      FOR i                                 IN 0 .. dbms_xmldom.getlength( t_nl
      )                                      - 1
      LOOP
        t_c              := t_strings.count;
        t_strings( t_c ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl,
        i ), '.' );
        IF t_strings( t_c ) IS NULL
          THEN
          t_strings( t_c ) := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl
          , i ), '*/text()' );
          IF t_strings( t_c ) IS NULL
            THEN
            t_nl2 := dbms_xslprocessor.selectnodes( dbms_xmldom.item( t_nl, i )
            , 'r/t/text()' );
            FOR j IN 0 .. dbms_xmldom.getlength( t_nl2 ) - 1
            LOOP
              t_strings( t_c ) := t_strings( t_c ) || dbms_xmldom.getnodevalue(
              dbms_xmldom.item( t_nl2, j ) );
            END LOOP;
          END IF;
        END IF;
      END LOOP;
    END LOOP;
  END IF;
  t_nd2 := blob2node( get_file( p_xlsx, 'xl/_rels/workbook.xml.rels' ) );
  FOR i IN 1 .. t_sheet_ids.count
  LOOP
    IF ( p_sheets                                                         IS NULL
      OR instr( ':' || p_sheets || ':', ':' || TO_CHAR( i ) || ':' )       > 0
      OR instr( ':' || p_sheets || ':', ':' || t_sheet_names( i ) || ':' ) > 0
      )
      THEN
      t_val := dbms_xslprocessor.valueof( t_nd2,
      '/Relationships/Relationship[@Id="' || t_sheet_ids( i ) || '"]/@Target',
      'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"' );
      t_one_cell.sheet_nr   := i;
      t_one_cell.sheet_name := t_sheet_names( i );
      t_nd                  := blob2node( get_file( p_xlsx, 'xl/' || t_val ) );
      t_nl3                 := dbms_xslprocessor.selectnodes( t_nd,
      '/worksheet/sheetData/row' );
      FOR r IN 0 .. dbms_xmldom.getlength( t_nl3 ) - 1
      LOOP
        t_nl2 := dbms_xslprocessor.selectnodes( dbms_xmldom.item( t_nl3, r ),
        'c' );
        FOR j IN 0 .. dbms_xmldom.getlength( t_nl2 ) - 1
        LOOP
          t_one_cell.date_val   := NULL;
          t_one_cell.number_val := NULL;
          t_one_cell.string_val := NULL;
          t_r                   := dbms_xslprocessor.valueof( dbms_xmldom.item(
          t_nl2, j ), '@r', t_ns );
          t_val := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl2, j ), 'v'
          );
          -- see Changelog 2013-02-19 formula column
          t_one_cell.formula := dbms_xslprocessor.valueof( dbms_xmldom.item(
          t_nl2, j ), 'f' );
          -- see Changelog 2013-02-18 type='str'
          t_t := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl2, j ), '@t'
          );
          IF t_t IN ( 'str', 'inlineStr', 'e' )
            THEN
            t_one_cell.cell_type  := 'S';
            t_one_cell.string_val := t_val;
          elsif t_t                = 's'
            THEN
            t_one_cell.cell_type := 'S';
            IF t_val             IS NOT NULL
              THEN
              t_one_cell.string_val := t_strings( to_number( t_val ) );
            END IF;
          ELSE
            t_s := dbms_xslprocessor.valueof( dbms_xmldom.item( t_nl2, j ),
            '@s' );
            t_nr := to_number( t_val
            ,
            CASE
            WHEN instr( t_val, 'E' ) = 0
              THEN
              TRANSLATE( t_val, '.012345678,-+', 'D999999999' )
            ELSE
              TRANSLATE( SUBSTR( t_val, 1, instr( t_val, 'E' ) - 1 ),
              '.012345678,-+', 'D999999999' ) || 'EEEE'
            END
            , 'NLS_NUMERIC_CHARACTERS=.,'
            );
            IF t_s IS NOT NULL AND t_xf_date( to_number( t_s ) )
              THEN
              t_one_cell.cell_type := 'D';
              IF t_date1904
                THEN
                t_one_cell.date_val := to_date('01-01-1904','DD-MM-YYYY') +
                to_number( t_nr );
              ELSE
                t_one_cell.date_val := to_date('01-03-1900','DD-MM-YYYY') + (
                to_number( t_nr )                                         - 61
                );
              END IF;
            ELSE
              t_one_cell.cell_type := 'N';
              t_nr                 := ROUND( t_nr, 14 - SUBSTR( TO_CHAR( t_nr,
              'TME' ),                                -3 ) );
              t_one_cell.number_val := t_nr;
            END IF;
          END IF;
          t_one_cell.row_nr := ltrim( t_r, rtrim( t_r, '0123456789' ) );
          t_one_cell.col_nr := col_alfan( rtrim( t_r, '0123456789' ) );
          t_one_cell.cell   := t_r;
          IF p_cell         IS NULL OR t_r = upper( p_cell )
            THEN
            pipe row( t_one_cell );
          END IF;
        END LOOP;
      END LOOP;
    END IF;
  END LOOP;
  RETURN;
END;
--
FUNCTION file2blob(
    p_dir VARCHAR2
    ,
    p_file_name VARCHAR2
  )
  RETURN BLOB
IS
  file_lob bfile;
  file_blob BLOB;
BEGIN
  file_lob := bfilename( p_dir, p_file_name );
  dbms_lob.open( file_lob, dbms_lob.file_readonly );
  dbms_lob.createtemporary( file_blob, true );
  dbms_lob.loadfromfile( file_blob, file_lob, dbms_lob.lobmaxsize );
  dbms_lob.close( file_lob );
  RETURN file_blob;
EXCEPTION
WHEN OTHERS THEN
  IF dbms_lob.isopen( file_lob ) = 1
    THEN
    dbms_lob.close( file_lob );
  END IF;
  IF dbms_lob.istemporary( file_blob ) = 1
    THEN
    dbms_lob.freetemporary( file_blob );
  END IF;
  raise;
END;
--
END as_read_xlsx;