You will use JDeveloper 11.1.1.4.0 (Release1) and Oracle ADF 11g Desktop Integration to develop Excel workbooks that are integrated with an ADF Web application.
show more or lessRead more...

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 mappings 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.1.4.0 (Release 1). 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 ADFdi_BC.zip file in a directory of your choice (e.g Temp).

  5. Unzip the ADFdi_BC.zip 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 applicationname.zip
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. Show more or lessRead more...

    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.

    alt text
  2. In the Start Menu, select Control Panel.

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

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

    alt text
  5. Click the Change button.

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

    alt text
  7. Expand the Microsoft Office | Microsoft Office Excel nodes. Select .NET Programmability Support. Click Continue.

    alt text
  8. Wait for the Configuration to proceed, and when finished, close the window.

    alt text
  9. Close the Add or Remove Programs window.

    alt text
  10. Close the Control Panel window.

    alt text

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

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

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

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.
  1. Start JDeveloper by selecting Start > Programs > Oracle Fusion Middleware > JDeveloper Studio 11.1.1.4.0.

    alt text

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

  2. If prompted for a User Role, choose Default Role.

    alt text

    Close the Tip of the Day window.

  3. The JDeveloper IDE should now be displayed.

    alt text
  4. From the Tools menu choose Install ADF Desktop Integration.

    alt text

    The ADF Desktop Integration installer is started.

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

    alt text
  6. 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.

    alt text
  7. Once Microsoft Office customization has been successfully installed, click Close to exit.

    alt text

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. Show more or lessRead more...

    You only need to perfom this procedure once for a given installation of Excel.
  1. On your desktop, click the Start button.

    alt text
  2. In the Start menu, select All Programs > Microsoft Office > Microsoft Office Excel 2007 (or 2010).

    alt text
  3. Once Microsoft Excel is started, click the Office button.

    alt text
  4. In the Office window, click Excel Options.

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

    alt text
  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.

    alt text
  7. Back in the Excel Options window, click OK.

    alt text
  8. Back in Excel, close the application.

    alt text
Step 4: Load the Startup Fusion Web Application
    The startup application is provided to enable you to get started with ADFDesktop Integration as quickly as possible.Show more or lessRead more...

    It 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).

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

    alt text

    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:

    alt text
  4. Right-click the ViewController project and select Project Properties from context.

    alt text
  5. In the Project Properties dialog, select the Technology Scope node and, using the Add button , shuttle the following technology from the Available Project Technologies pane, into the Selected pane:

  6. alt text

    Click OK. Your ViewController project is now ready to support ADF Desktop Integration.

  7. Click the Save All 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. Show more or lessRead more...

    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.

    alt text
  2. In the Project Properties dialog, select the Business Components node, and click the Edit icon next to the Connection field.

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

    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)


    alt text
  4. Click the Test Connection button and verify the success of the connection parameters.

    alt text

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

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

    alt text
  6. Right click the AppModule node and select Run from context.

    alt text
  7. 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.
    alt text
  8. Double click the DepartmentsView1 node to display Departments data.

    alt text
  9. Click the right arrow to browse department information.

    alt text

    Close the Business Component Browser window.

  10. Click Save All 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 © 2011, Oracle. All rights reserved.