Excel Download Using Button in Oracle Apex

Topic Introduction: In this tutorial, we will discuss how to download an Excel report using the process against the button. To complete this work you can follow the instructions below.

01. Create a Page 

02. Create a Region

      Identification ==>Type: Interactive Report

      Source Type: SQL Query

      SQL Query: Write your SQL Query on this box, here I have used a query of employee information.

select EMPLOYEE_ID,

       FIRST_NAME,

       LAST_NAME,

       EMAIL,

       PHONE_NUMBER,

       HIRE_DATE,

       (select "JOB_TITLE" from "OEHR_JOBS" x where x."JOB_ID" = a."JOB_ID") "JOB_ID",

       SALARY,

       COMMISSION_PCT,

       (select "FIRST_NAME" from "OEHR_EMPLOYEES" x where x."EMPLOYEE_ID" = a."MANAGER_ID") "MANAGER_ID",

       (select "DEPARTMENT_NAME" from "OEHR_DEPARTMENTS" x where x."DEPARTMENT_ID" = a."DEPARTMENT_ID") "DEPARTMENT_ID"

from "OEHR_EMPLOYEES" a

03. Take A Button

04. Create a Process against the button

      Write the PL/SQL Code for the Excel Download

declare

    vSAL   NUMBER;

    vCOMM_PCT NUMBER;

begin

sys.htp.init;

-- Set the MIME type

sys.owa_util.mime_header( 'application/octet', FALSE, 'UTF-8' );

-- Set the name of the file

sys.htp.p('Content-Disposition: attachment; filename="Employee Information.csv"');

-- Close the HTTP Header

sys.owa_util.http_header_close;

-- Set the name of the column header

 sys.htp.prn('Employee ID'||','||'First Name'||','||'Last Name'||','||'Email'||','||'Phone No'||','||'Join Date'||','||'Job ID'||','||'Salary'||','||'Commission PCT'||','||'Manager'||','||'Department'|| chr(13));

-- Loop through all rows in Data

for x in (

select EMPLOYEE_ID,

       FIRST_NAME,

       LAST_NAME,

       EMAIL,

       PHONE_NUMBER,

       HIRE_DATE,

       (select "JOB_TITLE" from "OEHR_JOBS" x where x."JOB_ID" = a."JOB_ID") "JOB_ID",

       SALARY,

       COMMISSION_PCT,

       (select "FIRST_NAME" from "OEHR_EMPLOYEES" x where x."EMPLOYEE_ID" = a."MANAGER_ID") "MANAGER_ID",

       (select "DEPARTMENT_NAME" from "OEHR_DEPARTMENTS" x where x."DEPARTMENT_ID" = a."DEPARTMENT_ID") "DEPARTMENT_ID"

from "OEHR_EMPLOYEES" a

)

loop

 -- Print out a portion of a row,

 -- separated by commas and ended by a CR

    sys.htp.prn('"'||x.EMPLOYEE_ID||'","'|| x.FIRST_NAME||'","'|| x.LAST_NAME||'","'|| x.EMAIL||'","'|| x.PHONE_NUMBER ||'","'|| x.HIRE_DATE||'","'|| x.JOB_ID||'","'|| x.SALARY||'","'|| x.COMMISSION_PCT||'","'|| x.MANAGER_ID||'","'|| x.DEPARTMENT_ID ||'"'|| chr(13));

    vSAL   := NVL(vSAL,0) + NVL(x.SALARY,0);

    

end loop;

-- Set the total of the column footer

    sys.htp.prn('"Total","'|| null ||'","'|| null ||'","'|| null ||'","'|| null||'","'|| null ||'","'|| null ||'","'|| null ||'","'|| vSAL   ||'","'|| null ||'","'|| null ||'","'|| null ||'"'|| chr(13));

   

-- Send an error code so that the

-- rest of the HTML does not render

    htmldb_application.g_unrecoverable_error := true;

    apex_application.stop_apex_engine;

end;

Remember here column heading no and column value number will be the same.

05. Go To Page Property

      Advance ==>Reload on Submit: Always