OOW Sneak preview of Database Excel-erator – on the ADF Desktop integration

One of the sessions I attended yesterday at OOW was by Juan Camilo Ruiz, Product Manager for Oracle Development Tools. His talk was titled ‘Introduction to Oracle ADF Desktop Integration; An Office front-end for ADF Applications.’

OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi001

He showed how we will be able to use Excel as the client for ADF applications. Instead of having users go into a Swing Desktop client or the ADF Faces Web interface, they can use the tool they are perhaps most familiar with to review, analyze and manipulate the data. Excel is a client to the ADF Model and Business Services through which it downloads the data and uploads the data when the user submits the changes.

Note that the Excel worksheet can taken off-line and the user can work on the data – potentially huge sets of it – in disconnected mode (on the air plane for example where despite all the cloud computing initiatives connectivity still is a scare commodity) only to have it synchronized when it becomes connected again. It is a bit like mobile devices that can work in disconnected mode and synchronize once a connection is available again – but it is Excel behaving in that way. From a developer’s point of view, developing the Excel client for ADF is somewhat different from developing an ADF Faces client inside JDeveloper, but is very similar to. For example, the Data Control palette that lists all available data sources and operations is available in the Excel design time and hot-cells (data bound cells) can quickly be created using the data controls, in much the same way as we do in JDeveloper proper.....

The user’s experience could be something like the following:

Go to web application, find menu option, the page and/or data you want to work on in Excel and open the Excel worksheet

OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi002 

or open the Excel worksheet already on your desktop and login (establishing the connection from Excel to a Servlet over Http communication).


In Excel, the user see on the one hand normal Excel worksheet with cells and data and with all Excel functionality enabled – such as editing cells, copying (blocks of) records, inserting or removing rows, calculating aggregate values or adding formulas or even creating charts – and on the other hand typical ADF application elements such as table, dropdown (choice) lists, input items and buttons for record navigation, querying and data submission.











 OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi004


In Excel, the user can make changes to the data, add rows or remove them, navigate through the data (and have additional records being pulled from the ADF Model on the Server) and at some point potentially submit the changes to the server (press the Save button or the save option from the menu) either for committing to the database or merely posting to the middle tier.

OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi006

Alternatively, the user can save the worksheet to the local file system to work on it later on, for example in disconnected mode.

All in all, the user experience is very much normal Excel. There is the notion of submitting the data in addition to the normal operation of saving the worksheet to the file system, that is perhaps the largest difference. When the data that is manipulated by the user turns out be changed by other users as well, conflicts (when optimistic locking was applied) will be detected by the business tier (frequently ADF BC) and the user will somehow have to deal with such conflicts. At that point, the user will be confronted by the fact that the data he was working on in the worksheet was not really his or her own personal exclusive local set but in fact a shared good.

One of the things this Excel integration will prove very useful for is the ability to do bulk data entry (or manipulation). It is very easy (especially for the many experienced Excel users around the world) to rapidly to data entry into an Excel worksheet, copy and pasting records, importing CSV data etc. Uploading that data to the ADF Server – and posting it to the database after applying the business logic in the business tier is then a very simple next step.

Design Time (developer’s) experience

The design time for creating an ADF/Excel worksheet – a worksheet that basically is the client application – is Excel itself, with a special ADFdi plugin loaded.

OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi008

In the picture above you see the ADF Excel design time. On the right is the ADF Data Control panel with the Collections, Attributes and Operations available to bind cells, tables and buttons in the Excel worksheet. When you take a close look at the cells in the sheet, you may be able to discern the EL expressions that are used – just like in the JSF page editor – to bind the cell to data control elements. Each ADF Excel worksheet will have its own Page Definition with the page/worksheet specific data bindings.

Here a screenshot of adding a new data bound element to an Excel worksheet – equivalent to dragging and dropping an attribute, collection or operation from the data control palette on top of a JSF page:

OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi009

Some facts

ADFdi has Excel for a client that uses Http communication to connect to a servlet that acts as the gateway into the ADF Model and Business Service.

OOW Sneak preview of Database Excel-erator - on the ADF Desktop integration jdev11gdi007

I somehow get the feeling that this servlet is probably not very specific to Excel and could be the gateway for other client technologies as well (including Oracle Forms??).

The ADF Desktop Integration (or ADFdi for short) is not yet available in the first production release of JDeveloper/ADF 11g. It will be part of what is called the Bulldog release – that is the internal nick name at Oracle – the maintenance release to synchronize with Fusion Middleware 11gR1, expected in the first half of 2009 (and my personal bet is that it will be the second half of that first half and probably even the second half of that second half – unfortunately).

Note: there is one small piece of Excel integration embedded in the first 11g production release. It is the option to specify for a table component that it supports Excel download. When that declarative option is selected, the table will be rendered with a little icon that when pressed will cause the data in the table to be downloaded as an XML file that Excel will open. It is very useful – but nowhere near the ADFdi functionality.

The Desktop Integration will only be available for Excel 2007 (and beyond). There are no concrete plans for supporting other components of the Microsoft Office product – though I am personally very much interested in a Powerpoint integration – or other Office products, such as OpenOffice.