Read and Import XLSX file in Oracle APEX 4.2

Objective:
To
read and import XLSX file in Oracle APEX 4.2
Scenario:
Customer
wants to see their investor details in APEX (in the form of IR). Though investor
details are in Oracle Directory (XLSX file format), But what we have done is,
we read the XLSX file from Oracle directory and import data in APEX
(Interactive Report).
Solution:

Step 1: Create the
process in APEX to Call the PL/SQL procedure to generate excel file in oracle
directory



Step 2: Install the AS_READ_XLSX Package
<< Package
Specification <>
<< Begins >>
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;
<< Ends>>
Step 3: Create the
Interactive Report in APEX
Step 4: Paste below
query in IR source
<<SQL>>
<< Begins>>
WITH
  xlsx AS
  (
    SELECT
      ROW_NR,
      COL_NR,
      CASE CELL_TYPE
        WHEN ‘S’
        THEN STRING_VAL
        WHEN ‘N’
        THEN TO_CHAR(NUMBER_VAL)
        WHEN ‘D’
        THEN TO_CHAR(DATE_VAL, ‘DD-MON-YYYY’)
        ELSE FORMULA
      END CELL_VAL — to make PIVOT works we
have to have one data type for
      — this column – in our case CHAR
    FROM
      (
        SELECT
          *
        FROM
          TABLE( as_read_xlsx.read(
as_read_xlsx.file2blob( ‘BROKERREPORTS’,
          ‘Unitholderdetails_report.xls’ ) ) )
      )
  )
SELECT
  ad.*
FROM
  xlsx PIVOT ( MAX(CELL_VAL) FOR COL_NR IN (1
AS UNITHOLDERID,2 AS AUTH_STAT,3
                                             
AS REFERENCENUMBER,4 AS
  ACCOUNTOPENINGDATE,5                        AS AMCID)) ad
WHERE
  row_nr >1;

<< Ends>>

Output:
By
Karkuvelraja T
  • January 2, 2017 | 25 views