You will use JDeveloper 11.1.2 and Oracle ADF 11g Desktop Integration to develop Excel workbooks that are integrated with an ADF Web application.
show more or less

You will start with a JDeveloper application predefined with the Business Components and page definition bindings that are required for the Excel workbook. 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 specify Download table functionality in Excel. Next, using another page definition, you define a workbook with 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.

You run the JDeveloper application to deploy it in the Weblogic server, then you execute the application developed in the Excel workbooks.

Finally, you publish the workbooks.

Before starting this tutorial you should:

  1. Have access to or have installed Oracle JDeveloper 11.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 11g 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 11g Desktop Integration 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.

You also perform these tasks in Part 1 of the tutorial.

Step 1: Enable Microsoft .Net Programmability Support
Before you can install the Oracle ADF Desktop Integration add-in, Microsoft Office Excel 2007 or 2010 must have Microsoft .NET programmability support enabled.
You can enable .NET programmability during the installation of Microsoft Office Excel 2007 or 2010. However if you did not enable it at that time, follow the steps below.
  1. On your desktop click the Start button.

    Start menu option
  2. In the Start Menu, select Control Panel.

    Control Panel option
  3. In the Control Panel window, double-click the Add or Remove Programs option.

    Add or Remove Programs option
  4. Once the list of installed programs is populated, scroll down to the Microsoft Office Standard 2007 (or 2010) entry and select it.

    Microsoft Office Standard 2007
  5. Click the Change button.

    Change button
  6. Select the Add or Remove Features radio button and click Continue.

    Change Microsoft Office Standard 2007
  7. Expand the Microsoft Office | Microsoft Office Excel nodes. Select .NET Programmability Support. Click Continue.

    Installation options
  8. Wait for the Configuration to proceed,

    Configuration progress bar

    and when finished, click Close.

    Closing the configuration process
  9. Close the Add or Remove Programs window.

    Closing the Add or Remove Programs window
  10. Close the Control Panel window.

    Closing the Control Panel window

    You've successfully added .NET programmability support to Excel.

Step 2: Install the Oracle ADF 11g Desktop Integration Add-in for Excel

You can install the ADF Desktop Integration add-in from JDeveloper, or from the setup tool provided in \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. Show more or lessRead more...

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

  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

    Close the Tip of the Day window.

  3. The JDeveloper IDE should now be displayed.

    JDeveloper IDE
  4. From the Tools menu choose Install ADF Desktop Integration.

    Tools menu  option
  5. Accept the licence agreement.

     licence agreement acceptance

    The ADF Desktop Integration installer is started.

  6. Various verification checks are carried out, and if all is in order, installation can begin. Click the Install button to proceed.

    Customization installer
  7. 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
  8. Once Microsoft Office customization has been successfully installed, click Close to exit.

    Customization installer

Step 3: 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.
  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 is 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 application.

    Closing the Excel application
Step 4: 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.
The startup application is a standard Fusion Web application that contains Model and ViewController projects.Show more or lessRead more...

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 Application Navigator. The Application Navigator 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 5: 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 from context.

    Project Properties option
  2. In the Project Properties dialog, select the Business Components node, and click the Load Extension button.

    Business Components node
  3. Once loaded, click the Edit icon next to the Connection field.

    Edit Connection
  4. 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
  5. Click the Test Connection button and verify the success of the connection parameters.

    Test Connection

    Click OK and OK again to return to the Application Navigator.

  6. Expand the Model project nodes to view all existing Business Components. The Application Navigator should now look as follows:

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

    Run option from context
  8. The Business Component Browser launches.

    The Business Component Browser is invaluable for testing and debugging Business Components applications. Show more or lessRead more... 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
  9. Double click the DepartmentsView1 node to display Departments data.

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

    Business Component Browser

    Close the Business Component Browser window.

  11. 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
Copyright © 2011, Oracle. All rights reserved.