For Merging cells in Excel and for other CSS in download property, i.e to create
down loadable XLS formatted sheets from any version of APEX use the following steps:
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.
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)
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;
4. Redirect the Download button in your screen to this new page.
Sample Output:
Recommended Posts
