Skip to main content

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 <>

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

<< PACKAGE BODY <>

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

Step 3: Create the Interactive Report in APEX.

Step 4: Paste below query in IR source.

<<SQL>>

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;

Output:



Comments

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Oracle Application Express Views (APEX)

Application Express Views Search SELECT * FROM apex_dictionary WHERE column_id = 0; View Comment Parent View APEX_APPLICATIONS Applications defined in the current workspace or database user. APEX_WORKSPACES APEX_APPLICATION_ALL_AUTH All authorization schemes for all components by Application APEX_APPLICATIONS ...
Post a Comment
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  ...
9 comments
Read more