Description:
For Merging cells in Excel and for other CSS in download property, i.e., to create downloadable XLS formatted sheets from any version of APEX use the following steps:
-
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.
- 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)
- 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;
- Redirect the Download button on your screen to this new page.
Sample Output:
Summary: This post explains about Excel Merge and Download using Custom Template in APEX
Queries?
Do drop a note by writing us at contact@staging.doyensys.com or use the comment section below to ask your questions.
