Thursday, 17 May 2018

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:100pxheight: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:

        function() {
            var td    = $(this).closest('td');
            var value = $(this).val();            

Happy APEXing,

Wednesday, 14 February 2018

Easier maintenance of standard APEX buttons

One of the strong points of APEX is the use of templates. Combining templates with template options and attributes you can end up with HTML objects that are declarative and in most cases do exactly what you want. When you want to make general changes to these objects just change the template or the underlying CSS. This is a very good implementation of the DRY (Don't Repeat Yourself) principle.

However when generating pages with APEX you generate many identical buttons (CREATE,SUBMIT,DELETE,CANCEL,NEW) for which the label is a hard coded property for each instance of the button (not so DRY). If you there is the need to change the labels ( maybe for a translation ), you will have to go through the entire application and change all the relevant instances of the buttons.
It gets even harder (or more nagging) when after finishing the application you are asked to apply another look-and-feel including a left-side icon.

So you have to change the button on the left to the button on the right.
For each button you need to:
- select the button
- apply the CSS class
- apply the icon CSS class
- open the template options (default icon position is right)
- change the icon position
- close the template options
A lot of work, and that times the number of buttons in your application. That is a lot of work and by its repetitive nature very error prone.
All these scattered definitions are not very DRY either.

A solution I use is to create specific button templates for the most used buttons (CREATE,SUBMIT,DELETE,CANCEL,NEW). The button templates are based on the Text with icon button:

New button templates are created by copying the Text with icon template and adapting it to your needs.In my applications for example for the Save Button, this looks like:

The label is hard coded to Save and the left icon class fa fa-save refers to the save icon (the floppy disk). As we are using the left icon, the code for the right icon is omitted. The class t-Button--iconLeft is needed to display the left icon. And finally the class delphy-hot defines the specific look-and-feel for this application suite.

Now all that needs to be done is to apply these specific templates to the buttons. This took me about half an hour for a medium sized application.
Instead of the list of actions mentioned above you just need to:
- select the button
- change the button template
Look at the animated GIF below to see how fast the change can be applied.

Once you have got all the button templates referenced, you can easily change the look-and-feel of all the buttons just by changing the corresponding templates.

If you have several applications with the same look-and-feel you can copy the templates from one application to another. After copying you can adapt to the application, for example by translating the labels.

Happy APEXing ;-)

Wednesday, 24 January 2018

Dynamic Navigation Bar

In my current assignment I work on a large number of applications. These applications all share the same UI theme. Once logged in you can switch applications through an application menu. 
The navigation bar in all these applications is (or should be) the same. Until today each application had his own copy of the Desktop Navigation Bar which is in fact an Oracle APEX list. 
As the number of applications is approaching double digits you can imagine I would like one code base for the navigation bar content. 

In the Shared Components > User Interface Components > User Interface Details you can find the details of the Navigation Bar:

Here you see that the list on which the Navigation bar is based can be changed. The template can also be chosen, but the current template is sufficient for this moment. 

Unfortunately we cannot reference lists in other applications, only copying is possible. 
But we can define a dynamic list to populate the Navigation Bar. This opens the door to defining the list as a query or view in the database, which can be shared between applications. 
I could not find the definition of the list elements needed for the Navigation Bar list, so I had to do some experimenting myself. It seems that the meaning of the columns in the query result set is determined by their position. 
The following query results in a correct entry:

select '1'            as  lvl
     , 'Logout'       as  label
     , 'apex_authentication.logout?p_app_id=&p_session_id='  as  target
     , null           as  attr1
     , 'fa-sign-out'  as  icon_css_class
     , null           as  attr3
     , null           as  attr4
     , ''             as  badge
     , ''             as  list_item_css_classes
from   dual
where  nvl(v('APP_USER'),'nobody') != 'nobody'

In this query the label is the text displayed in the entry. 
The target is the URL to be linked to. 
The icon_css_class can contain a reference to a Font Awesome/Apex icon. If omitted no icon is shown. 
For some columns I did not find any use, but as the meaning is positional these columns are needed anyway. 
The entries are implemented as an HTML UL element (unordered list). The list_item_css_classes can contain CSS classes for the list item that contains the entry. These classes can for example be used to apply a color to the entry text and icon. 

You can build a query by connecting a number of selects from dual of the above form. But this results in a long messy query which is hard to maintain. Luckily a APEX list can also be based on a PL/SQL function returning a query. So the query can be composed in PL/SQL which enables more clean coding. A package is defined with procedures to add an navigation bar entry and to return an APEX url. 
The code to generate the query in PL/SQL looks like this: 

  function navigation_bar_query return varchar2 is
    l_target      varchar2(1000) := null;

    -- logout entry
    l_target := 'apex_authentication.logout'
         ( p_label                   =>  'Logout'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-sign-out'

    -- user entry
         ( p_label                   =>  initcap(v('APP_USER'))
         , p_icon_css_classes        =>  'fa-user'

    -- applications entry
    l_target := apex_url
                    ( p_app_id       =>  aut_pck.get_aut_app_id
                    , p_app_page_id  =>  'APP_MENU'
         ( p_label                   =>  'Applications'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-desktop'

    -- DEVELOPMENT entry
    if in_development_environment then
           ( p_label                   =>  'DEVELOPMENT'
           , p_icon_css_classes        =>  'fa-exclamation-circle'
           , p_list_item_css_classes   =>  'yellow'
    end if;


All the messy code is encapsulated in the procedures and functions. 
Two standard entries are defined: the logout entry and the display of the username. Notice that the username is displayed using initcap. It is also possible to display the user's real name here when it is available in the database. 

Next a link to the applications menu is supplied. 

The last entry is used to notify a user that he is working in the development environment. This entry is only shown when working in the development environment which can usually be determined in SQL, for example by retrieving the database SID or service. In this way the application can be deployed to other environments unchanged. 

You can define a dynamic list using this package: 

This list can be chosen in the User Interface attributes to represent the Navigation Bar. 

The resulting Navigation Bar looks like this: 

You can download the full code of the package here.  

Happy APEXing :-)

Friday, 22 December 2017

First impressions on mobile support in APEX 5.2EA

For APEX 5.2 the support of JQuery mobile to create mobile applications will be discontinued. The APEX development team have taken action to enable mobile development using the Universal Theme. In APEX 5.2EA you can already find the List View Region that will replace the equivalent component in JQuery Mobile.

The List View Region is defined with the same properties as in the Query Mobile version:

The region source contains a query and in the Attributes the role of the column is assigned.
After creating a page with a List View it looks like this.

So the data is present, but the formatting is not. So I read the release documentation again, found on the home page of APEX:

In the features document there is a paragraph about the List View Region.

Apparently we have to look into the Known issues. Here we find:

OK, that's easy. So the link is copied and pasted in the page's CSS file attribute. But alas, the file cannot be found :-(.
But it is visible that it is a JQuery mobile file. And of course that file can be found on the internet.
Indeed, a reference is found:

Unfortunately this does not work, because Chrome does not accept mixed content ( mixed being both http and https). Luckily we get a warning in the Browser Inspector, and the https equivalent is available, so we use:

Now our page looks like this:

Much better, isn't it? It can still be improved though:
- there is a bit of wasted space to be recovered
- the search item can be styled
- the divider can use a bit more emphasis

So a bit of CSS is added:

/* remove wasted white space */
.t-Body-contentInner {
    padding: 0;
html .ui-filterable + .ui-listview, html .ui-filterable.ui-listview {
    margin-top: 0em;
.ui-filterable {
    padding: 4px 12px;

/* style search item */
.ui-filterable input {
    border: 1px solid lightgrey;
    width: 100%;
    padding: 0px 12px;
    border-radius: 4px;

/* give list divider background color */
.ui-listview > .ui-li-divider {
    background-color: lightgrey;

And now the page looks like this:

I could not resist also styling the counter as a badge ;-).

My conclusion is that the APEX development team are on the right track to replace the JQuery Mobile functionality.

Happy Apexing!

Monday, 9 October 2017

About a plug-in providing native Apex functionality

This Saturday I published a plug-in on to create subheaders in a classic report. I got the idea while developing a long report and it seemed like a good idea to create a plugin for it to wrap and parameterize the JavaScript code. I searched the internet for the existence of such a plug-in and found nothing.
So I set out to create the plug-in. After finishing it I wrote a blogpost and added it to the plug-in repository on And I proudly tweeted about it.
Pretty soon I got a reply from Peter Raganitz: "Wouldn’t a control break do just that?".

I set out to check this and.. he is absolutely right. This functionality is available in Apex in a declarative way!

The only extra needed is a small CSS definition for the visuals of the headers:

.apex_report_break {
  1. background-colorlightgrey!important;
  2. font-weightbold;
  3. font-size12px;

Saturday, 7 October 2017

Adding Subheaders to reports the easy way

After publishing this plug-in I found out that this functionality is native in Apex. Read more details here.

For an assignment I was working on a page containing a list of dates with the amount of sunlight on that day. This produces a long list of dates with numbers which I think is not very accessible. I applied my Report2columns plug-in which already improved the layout, but I still was not satisfied:

It are still a lot of numbers with little structure. Especially the repetition of the month and year adds a lot of characters, but adds little meaning.

After some experimenting with bold fonts and grey colors I came up with the solution to use subheaders for the months. And suddenly the data made much more sense to me:

This was done by adding an extra column with the subheader text to the report. In a After Refresh  dynamic action a JavaScript function scans the table and adds a subheader at each location where the content of the subheader column differs from the previous row.

In addition to the sub headers the month and year indicators are displayed in grey. This way they have less effect  on the general picture, but they are still readable: you do not have to shift your attention to the sub header to know the month and year of the date. This effect was accomplished by adding the formatting in the query:

SELECT to_char(lgr_date,'dd')
||'<span style="color: lightgrey;">'
||to_char(lgr_date,'-mm-yyyy')||'</span>' AS thedate
FROM mytable

Plug-in for subheaders

I was so enthousiastic about the effect of the subheaders that I have created a plug-in to generate subheaders in a Classic report.

The Report Subheaders plug-in is very easy to use. All you have to do is:
  • import the plug-in ( you can download it from )
  • add a column to your query which contains you subheader content
  • create an After Refresh dynamic action on your report
  • add a true action with a call to the Report Subheaders plug-in
  • fill in the name of your subheader column in the Subheader Column attribute
  • if you want to have your table headings repeated after each subheader, set Repeat Subheaders to Yes
  • The default formatting of the subheaders can be changed by defining the class td.t-Report-cell.subheader.Note that the background-color has to be defined with !important.
You can see the plug-in in action here on my demo site:

Happy Apexing :-)

Sunday, 10 September 2017

ORA-00933 when editing a Apex report

Have you encountered this error before in working with Apex? I do regularly wen editing reports.
At first I have created a nice report. After getting the query right I make some columns invisible, change the header texts and do some formatting.
Then I run the report and it looks nice. No errors on the SQL.

Then I apply a few changes and suddenly the report will not show up anymore. Instead I get this ugly error message:

failed to parse SQL query:
ORA-00933: SQL command not properly ended

What happend? It took me some time to figure it out. In my case there was an order by in the report query. This means that none of the report columns may be sortable.
At first this was the case. But one column was changed from Hidden to Plain Text. And in this change the Sortable attribute was set to Yes:

And Apex gets confused when a report query has an order by and an item is also sortable. So the query Apex creates has an incorrect syntax, hence the error message.

Setting the sortable attribute of the item to No immediately solves the problem. The report executes again without any error message.

Happy Apexing...