There is a quite easy way to generate nice looking Excel files based on your APEX reports. It doesn't require any special printing engine and should work even with Excel 2003.
It supports all kind of report views as long as they are build as tables. In my case I created an export based on this pivot table output: http://gumption.org/2004/pivot_table/test_page.html
Of course my pivot table is build up as plug-in and selects data from my project data tables.
All you need to do is to get the table element (including all html code) from your rendered page and write it into a blank XLS-file.
Those are the steps you have to follow:
1. Create a new APEX item: textarea
Textarea - P1_EXPORT_DATA
HTML Form Element Attributes: style="display:none;"
2. Create a button
Button: P1_EXPORT
Action: Defined by Dynamic Action
3. Edit your table region
Static ID: YOUR_APEX_TABLE_ID
Info: Check your report template. Your table element should look similar to this:
<table border="0" cellpadding="0" cellspacing="0" summary="" id="report_#REGION_STATIC_ID#"
3. Create a Dynamic Action
Event: Click
Selection Type: Button
Button: P1_EXPORT
Actions: Execute Javascript Code & Submit Page
- Execute Javascript Code:
$('#P1_EXPORT_DATA').val($('#YOUR_APEX_TABLE_ID').wrap('<p/>').parent().html());
$('#YOUR_APEX_TABLE_ID').unwrap();
- Submit Page:
EXPORT
4. Create a new Branch
Branch to Page or URL - OnSubmit
Page: 1
Request: Export
Conditions - PL/SQL
:REQUEST = 'EXPORT'
5. Add a new process
Process: Export
Type: PL/SQL
Process Point: On Load - Before Header
Process:
DECLARE
l_mime VARCHAR2 (255);
l_length NUMBER;
l_file_name VARCHAR2 (2000);
lob_loc BLOB;
BEGIN
dbms_lob.createtemporary(lob_loc, TRUE);
DBMS_LOB.WRITE(lob_loc, LENGTH(:P1_EXPORT_DATA), 1, UTL_RAW.CAST_TO_RAW(:P1_EXPORT_DATA));
OWA_UTIL.mime_header ('application/xls', FALSE);
HTP.p ('Content-Length: ' || DBMS_LOB.GETLENGTH(lob_loc));
HTP.p ('Content-Disposition: attachment; filename="my_xls_doc.xls"');
OWA_UTIL.http_header_close;
WPG_DOCLOAD.download_file(lob_loc);
END;
The output will look like this:
It also supports images but they must include the complete URL.
Btw.: APEX 4.2 EA is out. New Application Builder looks mostly nice but I think the region headers are far to big. Check it out yourself: https://apexea.oracle.com/i/index.html
New feature list: http://www.grassroots-oracle.com/2012/06/oracle-apex-42-early-adopter-announced.html
It supports all kind of report views as long as they are build as tables. In my case I created an export based on this pivot table output: http://gumption.org/2004/pivot_table/test_page.html
Of course my pivot table is build up as plug-in and selects data from my project data tables.
All you need to do is to get the table element (including all html code) from your rendered page and write it into a blank XLS-file.
Those are the steps you have to follow:
1. Create a new APEX item: textarea
Textarea - P1_EXPORT_DATA
HTML Form Element Attributes: style="display:none;"
2. Create a button
Button: P1_EXPORT
Action: Defined by Dynamic Action
3. Edit your table region
Static ID: YOUR_APEX_TABLE_ID
Info: Check your report template. Your table element should look similar to this:
<table border="0" cellpadding="0" cellspacing="0" summary="" id="report_#REGION_STATIC_ID#"
3. Create a Dynamic Action
Event: Click
Selection Type: Button
Button: P1_EXPORT
Actions: Execute Javascript Code & Submit Page
- Execute Javascript Code:
$('#P1_EXPORT_DATA').val($('#YOUR_APEX_TABLE_ID').wrap('<p/>').parent().html());
$('#YOUR_APEX_TABLE_ID').unwrap();
- Submit Page:
EXPORT
4. Create a new Branch
Branch to Page or URL - OnSubmit
Page: 1
Request: Export
Conditions - PL/SQL
:REQUEST = 'EXPORT'
5. Add a new process
Process: Export
Type: PL/SQL
Process Point: On Load - Before Header
Process:
DECLARE
l_mime VARCHAR2 (255);
l_length NUMBER;
l_file_name VARCHAR2 (2000);
lob_loc BLOB;
BEGIN
dbms_lob.createtemporary(lob_loc, TRUE);
DBMS_LOB.WRITE(lob_loc, LENGTH(:P1_EXPORT_DATA), 1, UTL_RAW.CAST_TO_RAW(:P1_EXPORT_DATA));
OWA_UTIL.mime_header ('application/xls', FALSE);
HTP.p ('Content-Length: ' || DBMS_LOB.GETLENGTH(lob_loc));
HTP.p ('Content-Disposition: attachment; filename="my_xls_doc.xls"');
OWA_UTIL.http_header_close;
WPG_DOCLOAD.download_file(lob_loc);
END;
The output will look like this:
It also supports images but they must include the complete URL.
Btw.: APEX 4.2 EA is out. New Application Builder looks mostly nice but I think the region headers are far to big. Check it out yourself: https://apexea.oracle.com/i/index.html
New feature list: http://www.grassroots-oracle.com/2012/06/oracle-apex-42-early-adopter-announced.html
