EXCEL UPLOAD IN APEX USING SELECT LIST TEMPLATE

Introduction:

This Process helps the user to upload .XLSX file with multiple tabs with Select List in APEX using PL/SQL.

Procedures:

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

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

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

 

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

 

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

 

  1. Change validation Criteria to List –> Click on source

  1. Go to Select List Tab –> Drag the values and Press Enter and Click OK.

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

xlsx-pkg

Step 5 :

 

  1. a) Create a page process to read the data from Uploaded excel and store it in a temporary table.
  2. b) Change the Process point to ‘On Submit – After Submit’.

  1. c) Copy below code as Source :

DECLARE

CURSOR c1

IS

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;

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

 Summary:

 The User can upload excel files and can able to get the Reports in Oracle Apex.

Recent Posts