Easy table export to XLS (based on HTML)

Von Tobias Arnhold 6.22.2012
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
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:
 - Submit Page:

4. Create a new Branch
Branch to Page or URL - OnSubmit
Page: 1
Request: Export
Conditions - PL/SQL

5. Add a new process
Process: Export
Type: PL/SQL
Process Point: On Load - Before Header
     l_mime        VARCHAR2 (255);
     l_length      NUMBER;
     l_file_name   VARCHAR2 (2000);
     lob_loc       BLOB;
     dbms_lob.createtemporary(lob_loc, TRUE);
     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"');

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

Post Tags: