ORACLE MASTERMINDS: Read and Import XLSX file in Oracle APEX 4.2

Monday, January 2, 2017

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

TYPE TP_ONE_CELL

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

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

FUNCTION read(

p_xlsx BLOB,

p_sheets VARCHAR2 := NULL,

p_cell VARCHAR2 := NULL )

RETURN tp_all_cells pipelined

t_date1904 BOOLEAN;

type tp_date

TABLE OF BOOLEAN INDEX BY pls_integer;

t_xf_date tp_date;

t_numfmt_date tp_date;

type tp_strings

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

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;

FUNCTION blob2num(

p_blob BLOB,

p_len INTEGER,

p_pos INTEGER )

RETURN NUMBER

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

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

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

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;

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;

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

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

END LOOP;

RETURN;

END;

FUNCTION file2blob(

p_dir VARCHAR2

p_file_name VARCHAR2

)

RETURN BLOB

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:

Karkuvelraja T

1 comment:

BloggerSeptember 27, 2017 at 5:33 PM
In case you are interested in earning cash from your websites/blogs using popup advertisments, you should embed one of the highest paying networks - Clickadu.
ReplyDelete
Replies

Add comment