Create formatted Excel file from your APEX cloud application

This post describes generating an Excel file with formatting from an Oracle APEX webpage without the use of printing engines. That makes it perfectly suitable for any cloud solution because you only need Oracle APEX.

At the moment I am redeveloping an Excel application into an Oracle APEX application. One of the pages generates an order list to be sent to a supplier. The report is created in APEX using a PL/SQL region, so the HTML is generated in PL/SQL.
This is the result for one week:

An order list can contain several weeks.


The original Excel application generates order lists as Excel files. That was a piece of cake, because the page was an Excel file. These files were sent to suppliers and looked a lot like the image above. You see the use of background colors, larger cells, colored fonts, cell width.

A PDF was not acceptable, because the suppliers could not cut and paste the data easily.
So it had to be a 'real' Excel file. There are lots of solutions on the internet to generate Excel files. But most of them require a good deal of coding. And I had done my coding in PL/SQL already, and I did not want to duplicate that.

Generate Excel from APEX application

Standard it is not possible to generate an Excel file with formatting from an APEX application.
Yes, you can download a CSV file and import that file into Excel. But you will get a plain list of data without any colors, borders or bold characters. You will not get near to the requested output.

After some time searching the internet I stumbled upon a blogpost of Tobias Arnhold: "Easy table export to XLS (based on HTML)".
He describes how you can create an XLS file containing an HTML table. Opening this file from Excel will show the table including formatted headers.

So I took this direction: create an HTML table with the formatting needed and write it to an XLS file.
JavaScript will be needed to assemble the HTML table to be written.
Then this content will be written to a file as described in the post of Tobias:
- put the content into a hidden APEX item
- use JavaScript to change the content om the hidden APEX item
- submit the page (saving the value of the hidden item to session state)
- write the file in a before header process, getting the content from the hidden item

As base for the table I used the HTML table for the week orders show above.
And it worked partly. The result I got was a bit disappointing:

So the most of the data was there, but without formatting. Data in input items was not shown.
The width of some cells is too small so the content is wrapped. A good point was that Excel did recognize the row and col span of the top left cell.

HTML to Excel limitations

With trial and error I found:

1. you can add styles at the beginning of the file
2. Excel only recognizes the first style assigned to an object
3. You can define CSS on element types like td or th
4. Excel does not recognize input items
5. Excel does apply width and height as style attributes
6. Excel does not support named colors ( only black and white) so use the RGB hex notation to define colors

Formatting the Excel

1. Styles. When you embed the HTML table in a normal framework of html, head, and body elements, you can define your styles in the styles element in the head.

2. Multiple styles: you can only use a single style. Remove 'unused' styles, in my case t-Report-cell and t-Report-colHead used in the APEX universal theme. Having only one style per object means that you will probably have some duplicates in your style definitions.

4. Input items: input items need to be converted to plain text.

5. Dimensions: for sizing a cell apply the style, for example: style="width:100px; height:30px;"

Above points mean that the HTML captured from the page should be changed.

This can be done using JavaScript. In order to be able to use jQuery a copy of the table is created in a div in a hidden APEX region. The ID of the table is changed. Now the table and its elements can be processed.

With the above knowledge and some JS coding the result is:

JavaScript snippets

Remove all instances of a class:

function remove_class(table,p_class)
{
    $(table).find('.'+p_class).each( function() { $(this).removeClass(p_class);})

}

Convert input items into plain text:

    $(table).find('input').each(
        function() {
            var td    = $(this).closest('td');
            var value = $(this).val();            
            $(td).text(value);
        }
    );

Happy APEXing,