ORACLE MASTERMINDS: EXCEL UPLOAD IN APEX USING SELECT LIST TEMPLATE

Follow the below steps to achieve, the process of uploading a .XLSX file with multiple tabs with Select List in APEX using PL/SQL.

ü Step 1 : Create an .XLSX file with multiple tabs.

a) Tab 1(Data) will have Data and Tab2(Select List) will have Select List Values.

a) Provide List of values in the Select List to be shown in the Data Tab.

Note : The List of values should be started in the column where there are no values in the columns in Data Tab. Refer Below Images.

ü The columns in the above image I.e. in the Data tab are ended at Column ‘H’.

ü The Columns in the above image (Select List Tab) are starting at Column ‘I’.

ü Values may interchange if values are present in same columns in both tabs.

a) Create a select list for Job Column in Data Sheet.

1. Click on Job --> Go to data tab --> Click on Validation

2. Change validation Criteria to List --> Click on source

3. Go to Select List Tab --> Drag the values and Press Enter and Click OK.

4. You can see Drop down for Job Column in Data tab.

Sample Excel :

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1001	Doyen	MANAGER	7698	22-Jun-18	2500		30
1002	Doyen	SALESMAN	7566	23-Jun-18	2501	350	10
1003	Doyen	CLERK	7782	24-Jun-18	2502	0	20

ü Step 2 : Create a Page browse item and provide storage type as

Table APEX_APPLICATION_TEMP_FILES.

ü Step 3 : Create a Page Button and Action when Button Clicked as Submit Page.

ü Step 4: Execute the below package scripts to read the contents of the uploaded .XLSX file.

CREATE OR REPLACE PACKAGE as_read_xlsx

-- ********************************************************************************************

-- Program : AS_READ_XLSX

-- Purpose : Package for Reading data from Excel.

-- |------- -------- --------------- ---------------------------------------------------------------------------------------

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;

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 IS TABLE OF BOOLEAN

INDEX BY PLS_INTEGER;

t_xf_date tp_date;

t_numfmt_date tp_date;

t_numfmt_text 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_nl DBMS_XMLDOM.domnodelist;

t_nl2 DBMS_XMLDOM.domnodelist;

t_nl3 DBMS_XMLDOM.domnodelist;

t_one_cell tp_one_cell;

t_ndoc DBMS_XMLDOM.domdocument;

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;

IF NOT DBMS_XMLDOM.isnull (t_ndoc)

THEN

DBMS_XMLDOM.freedocument (t_ndoc);

END IF;

t_ndoc :=

DBMS_XMLDOM.newdomdocument (XMLTYPE (p_blob,

NLS_CHARSET_ID ('AL32UTF8')

)

);

RETURN DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (t_ndoc));

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, DBMS_LOB.CALL);

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_ndoc := DBMS_XMLDOM.newdomdocument (t_clob);

t_nd :=

DBMS_XMLDOM.makenode (DBMS_XMLDOM.getdocumentelement (t_ndoc)

);

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

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,

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,

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,

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.CALL);

DBMS_LOB.COPY

(t_tmp,

p_zipped_blob,

t_len,

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;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

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_val := DBMS_XMLDOM.getnodevalue (DBMS_XMLDOM.item (t_nl, i));

t_xf_date (i) := t_numfmt_date.EXISTS (t_val);

t_numfmt_text (i) := t_val = '49';

END LOOP;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

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,

)

);

END LOOP;

END IF;

END LOOP;

END IF;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

t_nd := blob2node (get_file (p_xlsx, 'xl/_rels/workbook.xml.rels'));

FOR i IN 1 .. t_sheet_ids.COUNT

LOOP

t_sheet_ids (i) :=

DBMS_XSLPROCESSOR.valueof

(t_nd,

'/Relationships/Relationship[@Id="'

|| t_sheet_ids (i)

|| '"]/@Target',

'xmlns="http://schemas.openxmlformats.org/package/2006/relationships"'

);

IF SUBSTR (t_sheet_ids (i), 1, 4) = '/xl/'

THEN -- thanks Lilian Arnaud

t_sheet_ids (i) := SUBSTR (t_sheet_ids (i), 5);

END IF;

END LOOP;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

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) || ':') >

)

THEN

t_one_cell.sheet_nr := i;

t_one_cell.sheet_name := t_sheet_names (i);

t_nd := blob2node (get_file (p_xlsx, 'xl/' || t_sheet_ids (i)));

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,

INSTR (t_val, 'E')

- 1

'.012345678,-+',

'D999999999'

)

|| 'EEEE'

END,

'NLS_NUMERIC_CHARACTERS=.,'

);

IF t_s IS NOT NULL

AND t_xf_date.EXISTS (TO_NUMBER (t_s))

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

+ t_nr;

ELSE

t_one_cell.date_val :=

TO_DATE ('01-03-1900', 'DD-MM-YYYY')

+ (t_nr - 61);

END IF;

ELSE

IF t_s IS NOT NULL

AND t_numfmt_text.EXISTS (TO_NUMBER (t_s))

AND t_numfmt_text (TO_NUMBER (t_s))

THEN

t_one_cell.cell_type := 'S';

t_one_cell.string_val := t_val;

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;

DBMS_XMLDOM.freedocument (DBMS_XMLDOM.getownerdocument (t_nd));

END IF;

END LOOP;

DBMS_XMLDOM.freedocument (t_ndoc);

t_xf_date.DELETE;

t_numfmt_date.DELETE;

t_strings.DELETE;

t_sheet_ids.DELETE;

t_sheet_names.DELETE;

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.CALL);

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

a) Create a page process to read the data from Uploaded excel and store it in a temporary table.

b) Change the Process point to ‘On Submit - After Submit’.

c) Copy below code as Source :

DECLARE

CURSOR c1

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

-- We can change the Data format - In my Case '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

((SELECT blob_content

FROM apex_application_temp_files

WHERE ID =

(SELECT MAX (ID)

FROM apex_application_temp_files))

)

)))

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno

FROM xlsx

PIVOT ( MAX ( cell_val )

FOR col_nr

IN ( 1 AS EMPNO,2 AS ENAME,3 AS JOB,4 AS MGR,5 AS HIREDATE,6 AS SAL,7 AS COMM

,8 AS DEPTNO)

) -- To notify AS_READ_XLS package as 1st Column is EMPNO and 2 Column is ENAME ....

WHERE row_nr > 1;

BEGIN

FOR i IN c1

LOOP

INSERT INTO emp

(empno, ename, job, mgr, hiredate, sal,

comm, deptno

)

VALUES (i.empno, i.ename, i.job, i.mgr, i.hiredate, i.sal,

i.comm, i.deptno

);

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

raise_application_error (-20035, SQLERRM);

END;

a) Provide Process Condition as When Button Pressed to ‘P_BUTTON_NAME’.

Thursday, 5 July 2018

EXCEL UPLOAD IN APEX USING SELECT LIST TEMPLATE

2 comments: