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:
Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad