You will use JDeveloper 12.1.2 and Oracle ADF Desktop Integration 12c to develop Excel workbooks that are integrated with an ADF Web application.

You will start with a JDeveloper application predefined with the Business Components and page definition bindings that are required for the Excel workbook. You then, you open an ADF desktop integration-compatible workbook and use the ADF bindings to create ADF Desktop Integration components, including a read-only table. You then add Download table functionality in Excel and, using another page definition, you define a workbook with a List of Values (LOV) and Insert/Update/Delete functionality.

An Excel workbook may contain one or more worksheets. In this tutorial you create two workbooks, each with a single worksheet. It would be possible to combine both worksheets that you develop into a single workbook, since the workbook properties are the same for both.

Lastly, you run and test the application by using it to publish the Excel worksheet.

Before starting this tutorial you should:

  1. Have access to or have installed Oracle JDeveloper 12.1.2. You can download it from Oracle Technology Network. Install it into a directory of your choice, which, in this tutorial is referred to as JDEVELOPER_HOME.

  2. Have access to, or have installed Microsoft Office 2007 or 2010.

  3. Have access to, or have installed an Oracle 12c database, including the HR schema.

  4. Install the pre-defined application. Right-click the following link and select Save Link As... and save the file in a directory of your choice (e.g. Temp).

  5. Unzip the file into a directory of your choice.

Purpose Duration Application

This tutorial shows you how to use JDeveloper and Oracle ADF Desktop Integration 12c to develop Excel workbooks that are integrated with an ADF Web application. To see the complete application you will create, click the Download button to download a zip of the final application, and then unzip it in your JDeveloper mywork folder.

90 minutes Download application
Part 1: Configuring the Environment to Support Desktop Integration

Before starting to work with desktop integration you need to configure the various software pieces to support ADF Desktop Integration. This part guides you through the steps to do this.

In order to be able to follow this tutorial you need to load the startup Fusion Web application and check that the application can successfully connect to the database.

These instructions will guide you through this task.

Step 1: Install the Oracle ADF Desktop Integration 12c Add-in for Excel

You can install the ADF Desktop Integration add-in from JDeveloper, or from the setup tool provided in <JDEVELOPER_HOME>\jdeveloper\adfdi. This part of the tutorial guides you through installing the add-in from JDeveloper.
When you run the ADF Desktop Integration setup tool, it verifies whether the required pieces of software are installed on the system where you want to install the framework.

If one or more of these pieces of software is not installed, the setup tool installs it in the following order:

Windows Installer

Microsoft .NET Framework

Microsoft Visual Studio Tools for Microsoft Office

ADF Desktop Integration add-in

Depending on your machine's configuration, a reboot may be required.

  1. Start JDeveloper by selecting Start - All Programs - Oracle Fusion Middleware - JDeveloper Studio

    If the Migrate User Settings dialog box opens, click NO.

  2. If prompted for a User Role, choose Studio Developer (All Features) .

    Select Role dialog
  3. Close the Tip of the Day window.

  4. The JDeveloper IDE should now be displayed.

    JDeveloper IDE
  5. From the Tools menu select Install ADF Desktop Integration.

    Tools menu  option
  6. Accept the licence agreement.

     licence agreement acceptance

    The ADF Desktop Integration installer is started.

  7. Various verification checks are carried out, and if all is in order, installation can begin. The first step is to unpack the ADF Desktop Integration add-in for Excel.

    Customization installer
  8. Installing the ADF Desktop Integration add-in starts. Notice that depending on software already installed, you might be prompted to install other mandatory prerequisites, and in some cases to reboot your computer.

    Customization installer
  9. Once Microsoft Office customization has been successfully installed, click Close to exit.

    Customization installer

Step 2: Configure Excel to Run an Integrated Excel Workbook

You need to configure Microsoft Excel settings to make it accessible from ADF Desktop Integration. You only need to perform this procedure once for a given installation of Excel. The following steps may be a bit different, depending on the versions of Windows and MS Office on a given machine.
  1. On your desktop, click the Start button.

    Start menu option
  2. In the Start menu, select All Programs - Microsoft Office - Microsoft Office Excel 2007 (or 2010).

    Starting Excel
  3. Once Microsoft Excel has started, click the Office button.

    Excel Office button
  4. In the Office window, click Excel Options.

    Office window
  5. In the Excel Options window, click the Trust Center tab, then click the Trust Center Settings button.

    Excel Options window
  6. In the Trust Center window, click the Macro Settings tab, then check the Trust access to the VBA project object model checkbox. Click OK.

    Trust Center window
  7. Back in the Excel Options window, click OK.

    Excel Options window
  8. Back in Excel, close the wookbook.

Step 3: Load the Startup Fusion Web Application
The startup application is provided to enable you to get started with ADF Desktop Integration as quickly as possible. If you missed loading it earlier, you can use this link to download the zip file. Just unzip it into a temp directory.
The startup application is a standard Fusion web application that contains Model and ViewController projects.

It has been built using ADF Business Components. The page definition bindings that are required for the Excel workbook have already been defined in the application.
  1. Back in JDeveloper, click the Open Application link (or Select File - Open from the main menu).

    Open Application
  2. In the Open Application dialog, open the folder where you unzipped the file, and select the ADFdi_BC.jws file. Click Open.

    Open Application dialog

    If prompted to migrate the application from a previous version, accept to proceed.

  3. Once the application has loaded, you should see two projects in the Applications window. The Applications window should look as follows:

    Application Navigator
  4. Click the Save All Save All Icon icon on the JDeveloper menu bar, or select File - Save All from the menu.

Step 4: Check the Application's Database Connection
Your application needs to be able to connect to a database. A database connection has been created in the startup application. This step guides you through the tasks to check that the database connection in the startup application is able to connect to the database.
  1. Right click the Model project and select Project Properties.

    Project Properties option
  2. Click the Edit icon next to the Connection field.

    Edit Connection

  3. In the Edit Database Connection dialog, check the following values (and amend if necessary):

    Property Value
    Connection Name HRConn
    Connection Type Oracle (JDBC)
    Username hr
    Password hr
    Save Password (checked)
    Driver Thin
    Host Name (your host, example: localhost)
    JDBC Port (your port number, example 1521)
    SID XE (or your database SID)
    Edit Database connection
  4. Click the Test Connection button and verify the success of the connection parameters.

    Test Connection

    Click OK and OK again to return to the Applications window.

  5. Expand the Model project nodes to view all existing Business Components. The Applications window should now look as follows:

    Model project
  6. Right click the AppModule node and select Run from context.

    Run option from context
  7. The Oracle ADF Model Tester launches.

    The Business Component Browser is invaluable for testing and debugging Business Components applications. It is important to verify that the startup application is working before moving further with the tutorial. The browser enables you to test that the Business Components are working correctly and that the application can retrieve data from the database.
    Business Component Browser
  8. Double click the DepartmentsView1 node to display Departments data.

    Business Component Browser
  9. Click the right arrow to browse department information.

    Business Component Browser

    Close the Business Component Browser window.

  10. Click Save All Save all icon on the JDeveloper menu bar, or select File > Save All from the menu.

You have now set up your development environment and you are ready to start developing ADF Desktop Integration-enabled Excel workbooks.

Bookmark Print Expand all | Hide all
Back to top
Copyright © 2013, Oracle. All rights reserved.