7 ADF Desktop Integration

This chapter provides a high-level overview of the ADF Desktop Integration technology, which enables you to integrate Fusion web applications with Microsoft Excel workbooks.

This chapter includes the following sections:

7.1 About ADF Desktop Integration

ADF Desktop Integration provides a framework for Oracle ADF developers to extend the functionality provided by a Fusion web application to desktop applications in the form of Microsoft Excel workbooks.

As shown in Figure 7-1, ADF Desktop Integration can be used with other parts of the ADF technology stack, such as ADF Model, and ADF Business Components.

Figure 7-1 ADF Architecture with ADF Desktop Integration

This image is described in the surrounding text

7.2 Core Benefits of ADF Desktop Integration

The following are the core benefits of ADF Desktop Integration:

  • It allows end users to work with Oracle ADF-based applications offline.

  • It allows end users to work within Microsoft Excel's user interface, with which many users are already familiar and comfortable.

  • Bulk entry and update of data may be easier to accomplish through a spreadsheet-style interface.

  • It allows end users to use native Excel features such as macros, calculation, validation, and styles.

7.3 Key Concepts of ADF Desktop Integration

This section outlines the key concepts of ADF Desktop Integration.

7.3.1 Integration with Microsoft Excel

The ADF Desktop Integration components allow end users to manage data retrieved from a Fusion web application in an integrated Excel workbook. You configure the ADF components and the worksheet that hosts it them so that the end user can upload changes they make to data in worksheet to a Fusion web application.

When using a workbook with ADF Desktop Integration components, you can take advantage of standard Excel features such as the following:

  • Validation. You can use Excel's data validation features to control the type of data or the values that end users enter into a cell. These features allow you to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered in a cell.

  • Styles. You can configure the appearance the application's data by using Excel's style and formatting features, including some predefined styles provided by ADF Desktop Integration. You can also use EL expressions to have styles applied dynamically.

  • Formulas and calculated cells. You can write Excel formulas that perform calculations on values in an integrated Excel workbook. Formulas can be entered both in cells that reference Oracle ADF bindings and cells that do not reference Oracle ADF bindings.

    Also, you can use an EL expression to generate an Excel formula as the value of an ADF component.

  • Macros. You can define and execute macros based on Excel events in an integrated Excel workbook.

7.3.2 Integration with ADF Page Definition Files

ADF Desktop Integration components are linked to page definition files in the Fusion web application. Page definition files define the bindings that populate the data in the Oracle ADF components at runtime. Page definition files also reference the action bindings and method action bindings that define the operations or actions to use on this data. You must define a separate page definition file for each Excel worksheet that you are going to integrate with a Fusion web application. The integrated Excel workbook can include worksheets that do not reference page definition files.

The link with the page definition files enables the integrated worksheets to also benefit from any validation that you have set up in the binding layer. Data that the end user enters or edits in one of the ADF Desktop Integration components, such as the ADF Table component, can be validated against these rules and conditions that are set the server side in the Fusion web application.

7.3.3 Runtime Synchronization with Fusion Web Applications

An ADF Desktop Integration integrated workbook extends and runs within the context of an ADF Fusion web application. Offline use of an ADF Desktop Integration integrated workbook is possible, but data synchronization and some user interface interactions require that a valid user session be established with the web application on which it is based at some point. ADF Desktop Integration relies on HTTP cookie-based session management for all of its interactions with the ADF Fusion web application, regardless of whether the web application is configured to enforce authentication or not.

When you deploy an ADF Desktop Integration-enabled Fusion web application from JDeveloper, references to the ADF Desktop Integration shared libraries are added to the appropriate descriptor files. For any Fusion web application that contains one or more projects referencing the ADF Desktop Integration Model API library or the ADF Desktop Integration Runtime library, a platform-dependent reference to the ADF Desktop Integration Model API shared library is added during deployment.

For any web application module (WAR) project that contains a reference to the ADF Desktop Integration Runtime library, a platform-dependent reference to the ADF Desktop Integration Runtime shared library is added during deployment.

The ADF Desktop Integration framework is composed of a client-side portion running on top of Microsoft .NET and two server components: the ADF Desktop Integration remote servlet and the ADF Desktop Integration download filter. The server components run within the context of an ADF Fusion web application. The client component acts as the view and controller and communicates with the servlet to synchronize data and execute business logic in the web application's model project. Communication between the client and server takes the form of HTTP requests and responses.

7.3.4 Security for Integrated Excel Workbooks

Whenever an integrated Excel workbook connects to a Fusion web application, the integrated workbook makes sure that a valid, authenticated user session is established before downloading any data. If you are using a Fusion web application that does not enforce authentication, the integrated Excel workbook verifies and creates a valid user session when it connects to the Fusion web application.

When the corresponding Fusion web application has ADF Security enabled, the ADF Desktop Integration enforces any security policies set for the page definitions that correspond to the integrated workbook. At runtime, end users without proper permissions for a page definition (binding container) are prevented from interacting with the associated integrated Excel worksheet. Any attempt to interact with an unauthorized binding container (for example, to download or submit data) is aborted, the end user is informed of the authorization failure, and all ADF Desktop Integration activity on the worksheet is disabled. No further interaction with the ADF Desktop Integration-disabled worksheet is possible until a new user session is established. To allow end users to interact with the integrated Excel worksheet, assign them the roles that have been granted access to the page definition.

If you save an Excel workbook containing data downloaded from a Fusion web application to a location, such as a network directory, where other users can access the Excel workbook, the data stored in the Excel workbook is accessible to other users.

You can enhance the security of an integrated Excel workbook using Excel's functionality to set a password on a workbook. It prevents unauthorized users from opening or modifying the workbook. For more information about Excel security features, see Excel's documentation.

7.4 Key Components of ADF Desktop Integration

ADF Desktop Integration functionality consists of the add-in features to Microsoft Excel that are outlined in this section.

7.4.1 Table-Type Components

ADF Desktop Integration contains table and read-only table components, which enable you to display and edit large sets of structured information, such as entire database tables. These components are analogous to ADF Faces components with the same name but with properties that are specific to the Excel workbook environment. These components also have associated actions, with which you can do things such as download from and upload to the associated Fusion web application.

After you add an ADF Table component to a worksheet, you configure it and the worksheet that hosts it, so that the ADF Table component downloads data from the Fusion web application. To achieve this, you configure an Oracle ADF component, such as ADF Button, a worksheet ribbon button, or a worksheet event to invoke an action set. The action set that is invoked must include the ADF Table component Download action among the actions that it invokes.

7.4.2 Form-Type Components

ADF Desktop Integration contains basic UI components for input text fields, output text fields, labels, buttons, and lists of values (LOVs), which enable you to create forms for displaying and entering data. These components are analogous to ADF Faces components with the same name but with properties that are specific to the Excel workbook environment.

To enable the user to commit those changes back to the Fusion web application, you configure an ADF component to invoke an action set that handles the transferring of the changes back to the web application and committing them to the data source.

7.4.3 Action Sets

To enable you to string together multiple actions that can be invoked with a single user gesture in the workbook, ADF Desktop Integration provides action sets. An action set is an ordered list of one or more actions that execute in a specified order. You can associate an action set with a UI element in the worksheet or with a worksheet event, such as Startup or Shutdown.

The following types of actions are available:

  • ADFmAction - an action binding or method action binding in the underlying page definition file.

  • ComponentAction - an action that a component on the worksheet exposes. The ADF Table and ADF Read-only Table components are the only components in ADF Desktop Integration that expose actions.

  • WorksheetMethod - an action provided by ADF Desktop Integration that handles coordination between the data in the worksheet and the Fusion web application. The available actions are UpSync, DownSync, and DisplayWorksheetErrors.

  • Confirmation - Invokes a confirmation dialog.

  • Dialog - Invokes a web page in a popup dialog or Excel's task pane.

7.5 Overview of the ADF Desktop Integration Process Flow

Developing with ADF Desktop Integration consists of the following steps:

  1. On a Microsoft Windows system, make sure Internet Explorer is installed, and install Microsoft Excel and JDeveloper.

  2. Configure the Microsoft Excel installation to trust access to the VBA project object model in order to make it accessible from ADF Desktop Integration.

  3. In JDeveloper, install the ADF Desktop Integration add-in.

    The ADF Desktop Integration add-in is available in two editions, the Designer edition and the Runtime edition. Use the Designer edition to create and test integrated Excel workbooks, and the Runtime edition to enable end users to use ADF Desktop Integration and integrated Excel workbooks. However, do not install both editions of ADF Desktop Integration on the same system.

  4. Create a Fusion web application.

  5. In the application's model project, add data controls that expose the elements you require in Microsoft Excel.

  6. In the application's user interface project, create page definition files that expose the Oracle ADF bindings to use in Excel.

  7. Create the Excel workbooks that you intend to configure with Oracle ADF functionality.

  8. Configure the Excel workbook using the Oracle ADF bindings that you exposed in the page definition files and the Oracle ADF components that ADF Desktop Integration provides.

  9. Add the integrated Excel workbook to the JDeveloper project for your Fusion web application if it is not already packaged there. This makes sure that the Excel workbooks you integrate with your Fusion web application get deployed when you deploy your finalized Fusion web application.

  10. Publish the completed workbook so that it is available to users when the application is deployed.

  11. Configure one or more web pages in your Fusion web application to allow end users to access the integrated Excel workbooks.

  12. Deploy the Fusion web application that contains the integrated workbook.

  13. Make sure that end users who want to use the functionality that you configure in an integrated Excel workbook install the Runtime edition of ADF Desktop Integration.

7.6 Learning More About ADF Desktop Integration

For more information on using ADF Desktop Integration, see the following resources: