Introduction
The purpose of this post is to download data in an excel spreadsheet using the custom template.
Steps to follow
Step:1 Create a new blank page in the application using Customized template. In that customized template, remove all the contents in the page, like navigation menus, APEX page Toolbar, and others, since by putting this in a Before Header process on a page, the entire page will be downloaded as a native Excel Spreadsheet.
Step:2 Create PLSQL Region with no template.
— Should use recursion for a full tree, but we only need executives.
DECLARE
l_dirtotal NUMBER (12, 2);
BEGIN
HTP.p
(‘<table border=1 white-space=nowrap><tr style=”background-color:silver;”>
<th colspan=5>Employee</th><th>Title</th><th>Salary</th>
<th>Direct Report Total</th></tr>’
);
FOR c_exec IN (SELECT *
FROM employee
WHERE direct_report_id IS NULL)
LOOP
SELECT SUM (salary)
INTO l_dirtotal
FROM employee
WHERE direct_report_id = c_exec.ID;
HTP.p
( ‘<tr height=80><th align=right><img src=”‘
|| c_exec.image
|| ‘”></th><th colspan=4 style=”white-space:nowrap;font-size:36px;color:green;background-color:#CCFFCC;”>’
|| c_exec.NAME
|| ‘</th><th>’
|| c_exec.title
|| ‘</th>
<th style=”font-size:36px;”>’
|| TO_CHAR (c_exec.salary, ‘$999G999G999D00’)
|| ‘</th><th>’
|| TO_CHAR (l_dirtotal, ‘$999G999G999G999D00’)
|| ‘</th></tr>’
);
— Layer 1
FOR c_vp IN (SELECT *
FROM employee
WHERE direct_report_id = c_exec.ID)
LOOP
SELECT SUM (salary)
INTO l_dirtotal
FROM employee
WHERE direct_report_id = c_vp.ID;
HTP.p
( ‘<tr height=55><th colspan=3 align=right><img src=”‘
|| c_vp.image
|| ‘”></td><td colspan=2 style=”font-size:24px;color:#0000CC;white-space:nowrap;background-color:#CCCCFF;”>’
|| c_vp.NAME
|| ‘</td><td style=”text-size:18px;text-align:center;”>’
|| c_vp.title
|| ‘</td><td style=”color:red;text-align:right;font-size:20px;”>’
|| TO_CHAR (c_vp.salary, ‘$999G999G999D00’)
|| ‘</td><td>’
|| TO_CHAR (l_dirtotal, ‘$999G999G999G999D00’)
|| ‘</td></tr>’
);
— Layer 2
FOR c_grunt IN (SELECT *
FROM employee
WHERE direct_report_id = c_vp.ID)
LOOP
HTP.p
( ‘<tr height=50><th colspan=4 align=right>
<img src=”‘
|| c_grunt.image
|| ‘”></td><td style=”text-size:14px;white-space:nowrap;”>’
|| c_grunt.NAME
|| ‘</td><td style=”text-align:center;”>’
|| c_grunt.title
|| ‘</td><td style=”text-align:right;”>’
|| TO_CHAR (c_grunt.salary, ‘$999G999G999D00’)
|| ‘</td></tr>’
);
END LOOP;
END LOOP;
END LOOP;
HTP.p (‘</table>’);
END;
(Modify the code as per the requirement)
Step 3. Create Before Header process to set HTTP Headers.
BEGIN
OWA_UTIL.mime_header (‘application/vnd.ms-excel’, FALSE);
HTP.prn (‘Content-Disposition: attachment; filename=”spreadsheet.xls”
‘ );
OWA_UTIL.http_header_close;
END;
Step 4 Redirect the Download button in your screen to this new page.
Sample Output
Call To Action:
For Oracle apex development and customization please do contact our company website https://doyensys.com/
Conclusion
This method would be beneficial to download page values in excel sheet.
