Skip Headers
Oracle® Fusion Middleware Desktop Integration Developer's Guide for Oracle Application Development Framework
11g Release 1 (11.1.1.5.0)

Part Number E10139-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 Preparing Your Integrated Excel Workbook

This chapter describes preparatory tasks that you must perform in developing your Fusion web application so that you can integrate an Excel workbook with the finalized application. This chapter also describes how you configure an Excel workbook before you add Oracle ADF functionality, using the tools provided in the ADF Desktop Integration module.

This chapter includes the following sections:

4.1 Introduction to Preparing Your Integrated Excel Workbooks

This chapter, and this guide as a whole, assumes that you have developed a functioning Fusion web application, as described in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Having developed your Fusion web application, you perform the tasks described in this chapter and elsewhere in this guide to configure an integrated Excel workbook with your Fusion web application. These tasks include adding the bindings that you require at runtime in the Excel workbook, preparing the Excel workbook for configuration with Oracle ADF functionality, and configuring the workbook by adding the Oracle ADF components that provide the functionality you require at runtime.

Note:

Before you start, ensure that you have installed the Designer edition of ADF Desktop Integration. For more information about the ADF Desktop Integration editions, see Section 3.5, "Installing ADF Desktop Integration."

4.2 Adding ADF Desktop Integration to a Fusion Web Application

You enable Excel desktop integration for your Fusion web application by adding ADF Desktop Integration to the technology scope of the JDeveloper project where you develop the Fusion web application.

4.2.1 How to Add ADF Desktop Integration to Your JDeveloper Project

Use the Project Properties dialog in JDeveloper to add ADF Desktop Integration to the technology scope of your project.

To add ADF Desktop Integration to your project:

  1. Open your project in JDeveloper.

  2. In the Application Navigator, right-click the project to which you want to add ADF Desktop Integration and choose Project Properties.

    If your application uses the Fusion Web Application (ADF) application template, you select the ViewController project. If your application uses another application template, select the project that corresponds to the web application.

  3. In the Project Properties dialog, select Technology Scope to view the list of available technologies.

  4. Select the ADF Desktop Integration and ADF Library Web Application Support (optional) project technologies and add them to the Selected Technologies list.

  5. Click OK.

Note:

You must add ADF Library Web Application Support to the technology scope if you plan to distribute integrated Excel workbooks by adding them to ADF library files through EAR and JAR files.

4.2.2 What Happens When You Add ADF Desktop Integration to Your JDeveloper Project

When you add the ADF Desktop Integration module to the technology scope of your project, the following events occur:

  • The project adds the ADF Desktop Integration runtime library. This library references the following .jar files in its class path:

    • adf-desktop-integration.jar

    • adf-desktop-integration-model-api.jar

    • resourcebundle.jar

  • The project's deployment descriptor (web.xml) is modified to include the following entries:

    • An ADF bindings filter (adfBindings)

    • A servlet named adfdiRemote

      Note:

      The value for the url-pattern attribute of the servlet-mapping element for adfdiRemote must match the value of the RemoteServletPath workbook property described in Table A-18.
    • A filter named adfdiExcelDownload

    • A MIME mapping for Excel files (.xlsx and .xlsm)

    The previous list is not exhaustive. Adding ADF Desktop Integration to a project makes other changes to web.xml. Note that some entries in web.xml are added only if they do not appear in the file.

While updating filter and filter mapping information in the web.xml file, ensure that the filter for ADF Library Web Application Support (<filter-name>ADFLibraryFilter</filter-name>) appears below the adfdiExcelDownload filter entries, so that integrated Excel workbooks can be downloaded from the Fusion web application.

Figure 4-1 shows the Filters tab of the web.xml editor in JDeveloper.

Figure 4-1 Filters tab of web.xml

Filters tab of web.xml

For more information about web.xml, see Appendix E, "ADF Desktop Integration Settings in the Web Application Deployment Descriptor."

4.2.3 What Happens When You Deploy ADF Desktop Integration Enabled Fusion Web Application from JDeveloper

When you deploy your 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.

4.2.3.1 Deploying your Fusion Web Application on Oracle WebLogic Server

When you deploy your Fusion Web application on Oracle WebLogic Server, the following happens:

  • The META-INF/weblogic-application.xml file of the deployed application EAR contains a library reference to oracle.adf.desktopintegration.model.

    For example:

    <library-ref>
      <library-name>oracle.adf.desktopintegration.model</library-name>
    </library-ref>
    

    The shared library is delivered in MW_HOME/oracle_common/modules/oracle.adf.desktopintegration.model_11.1.1, in the oracle.adf.desktopintegration.model.ear.

  • The WEB-INF/weblogic.xml file of the deployed web application WAR contains a library reference to oracle.adf.desktopintegration.

    For example:

    <library-ref>
      <library-name>oracle.adf.desktopintegration</library-name>
    </library-ref>
    

    The shared library is delivered in MW_HOME/oracle_common/modules/oracle.adf.desktopintegration_11.1.1, in the oracle.adf.desktopintegration.war.

4.2.3.2 Deploying your Web Application on IBM WebSphere Application Server

When you deploy your web application on IBM WebSphere Application Server, the following happens:

  • For applications requiring the ADF Desktop Integration Model API library, or the ADF Desktop Integration Runtime library, the deployment procedure inserts a reference to the com/oracle/adfdimodel extension into the META-INF/MANIFEST.MF file of the application EAR file.

    For example:

    Manifest-Version: 1.0
    Extension-List: adfm adfdimodel
    adfm-Extension-Name: com/oracle/adfm
    adfm-Specification-Version: 1.0
    adfdimodel-Extension-Name: com/oracle/adfdimodel
    adfdimodel-Specification-Version: 1.0
    UseWSFEP61ScanPolicy: false
    
  • The deployment.xml file for web applications with projects that refer to the ADF Desktop Integration Runtime library contains a library reference inserted during deployment.

    For example:

    <libraries xmi:id="LibraryRef_1274886542330_oracle.adf.desktopintegration_1.0_11.1.1.2.0" libraryName="oracle.adf.desktopintegration_1.0_11.1.1.2.0"           sharedClassloader="true"/>
    

Note:

For more information about system administration tasks and the specifics about shared libraries for these platforms, refer to the Oracle WebLogic Server and IBM WebSphere Application Server documentation.

4.3 Working with Page Definition Files for an Integrated Excel Workbook

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 ADF Desktop Integration task pane displays only those bindings that ADF Desktop Integration supports in the bindings palette. If a page definition file references a binding that ADF Desktop Integration does not support (for example, a graph binding), it is not displayed.

Table 4-1 lists and describes the binding types that the ADF Desktop Integration module supports.

Table 4-1 Binding Requirements for ADF Desktop Integration Components

ADF Desktop Integration component Supported Binding Additional comments

ADF Input Text

Attribute binding

 

ADF Output Text

Attribute binding

 

ADF Label

Attribute and list bindings

This ADF Desktop Integration component uses the label property of a control binding.

ADF List of Values

List binding

 

Tree Node List

Tree binding attributes and list binding

Tree binding attributes must be associated with a model-driven list.

ADF Button

Various

The ADF Button component in ADF Desktop Integration can invoke action sets. Action sets can reference action bindings, method action bindings, or actions exposed by components in ADF Desktop Integration. For more information about action sets, see Section 8.2, "Using Action Sets."

ADF Read-only Table

Tree binding

 

ADF Table

Tree binding

 

For information about the bindings that components in ADF Desktop Integration use, see Appendix A, "ADF Desktop Integration Component Properties and Actions."

For information about the elements and attributes in page definition files, see "pageNamePageDef.xml" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

For information about ADF data binding and page definition files in a Fusion web application, see the "Using Oracle ADF Model in a Fusion Web Application" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

4.3.1 How to Create a Page Definition File for an Integrated Excel Workbook

You create and configure a page definition file that determines the Oracle ADF bindings to expose in your JDeveloper project.

To create a page definition file for an integrated Excel workbook:

  1. In JDeveloper, add a new JSF page in your ADF Desktop Integration application's project.

    Tip:

    Add an ADF Faces Table component to the JSF page. JDeveloper generates the tree bindings in the JSF page that the ADF Table-type components use in the page definition file.

    Note:

    JDeveloper creates a page definition file's name based on the name of the JSF page you choose. If you want a page definition file's name to indicate an association with a particular workbook or worksheet, choose this name when creating the JSF page.
  2. In the Application Navigator, select the page, right-click and select Go to Page Definition.

  3. In the Confirm Create New Page Definition dialog, click Yes.

  4. Add the bindings that you require for your integrated Excel workbook to the page definition file.

  5. Save the page definition file.

    Figure 4-2 shows the ExcelPriceListPageDef.xml page definition file that the Price List worksheet in the EditPriceList-DT.xlsx workbook references.

    Figure 4-2 Page Definition File with Bindings for an Integrated Excel Workbook

    Page definition file for a worksheet with its bindings

    For information about working with page definition files, see the "Working with Page Definition Files" section in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

  6. Make and run your Fusion web application if you plan to run the integrated Excel workbook in test mode or publish it.

4.3.2 What Happens When You Create a Page Definition File

JDeveloper creates the DataBindings.cpx file the first time that you add a page definition file in your JDeveloper project using the procedure described in Section 4.3.1, "How to Create a Page Definition File for an Integrated Excel Workbook."

The DataBindings.cpx file defines the binding context for the Fusion web application and provides the metadata from which the Oracle ADF bindings are created at runtime. Information about working with this file can be found in the "Working with the DataBindings.cpx File" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. Information about the elements and attributes in the file can be found the "DataBindings.cpx" section of the same guide.

4.3.3 Reloading a Page Definition File in an Excel Workbook

If you make changes in your JDeveloper desktop integration project to a page definition file that is associated with an Excel worksheet, rebuild the JDeveloper desktop integration project and reload the page definition file in the Excel worksheet to ensure that the changes appear in the ADF Desktop Integration task pane. You associate a page definition file with an Excel worksheet when you choose the page definition file, as described in Section 4.4.3, "How to Configure a New Integrated Excel Workbook."

The Oracle ADF tab provides a button that reloads all page definition files in an Excel workbook.

Errors may occur when you switch an integrated Excel workbook from design mode to runtime if you do not rebuild the JDeveloper desktop integration project and restart the application after making changes to a page definition file. For example, if you:

  • Remove an element in a page definition file

  • Do not rebuild and restart the Fusion web application

  • Or do not reload the page definition file in the integrated Excel workbook

an error message such as the following may appear when you attempt to switch a workbook to test mode:

[ADFDI-05530] unable to initialize worksheet: MyWorksheet
[ADFDI-05517] unable to find control MyBindingThatWasRemoved

To reload page definition files in an Excel workbook:

  1. Ensure that you have saved the updated page definition file in JDeveloper.

  2. In the Excel workbook, click the Refresh Bindings button in the Components group of the Oracle ADF tab.

    For information about the Refresh Bindings button, see Section 5.1, "Introduction to Development Tools."

4.3.4 What You May Need to Know About Page Definition Files in an Integrated Excel Workbook

Note the following points about page definition files in an ADF Desktop Integration project.

  • Integrating Multiple Excel Worksheets – You can integrate multiple worksheets in an Excel workbook with a Fusion web application. You associate a page definition file with each worksheet as described in Section 4.4.4, "How to Add Additional Worksheets to an Integrated Excel Workbook."

  • EL Expressions in a Page Definition File – Use the following syntax to write EL expressions in a page definition file:

    Dynamic (${})
    

    Do not use the syntax Deferred (#{}) to write EL expressions. EL expressions using this syntax generate errors as they attempt to access the ADF Faces context which is not available

    Note:

    EL expressions that you write for ADF Desktop Integration components in the integrated Excel workbook, such as ADF Input Text, must use the Deferred (#{}) syntax.

4.4 Adding Integrated Excel Workbook to a Fusion Web Application

Before you start using an Excel workbook with Oracle ADF functionality, you must integrate and configure the workbook with your Fusion web application in the following way:

  1. Create a new integrated Excel workbook, or enable an existing workbook to integrate it with Fusion web application. You can choose either of the two methods.

  2. Configure your integrated Excel workbook by setting several properties after the workbook is enabled for ADF Desktop Integration.

  3. Add additional worksheets, if required.

After you complete these steps, you can add Oracle ADF functionality using the tools provided by the ADF Desktop Integration module.

You can add an integrated Excel workbook to your Fusion web application in JDeveloper, or create an Excel workbook and enable it for ADF Desktop Integration.

4.4.1 How to Add an Integrated Excel Workbook in JDeveloper

You can add an integrated Excel workbook to a Fusion web application in JDeveloper from the New Gallery.

To add an integrated Excel workbook in JDeveloper:

  1. Open your Fusion web application in JDeveloper.

    Ensure that the application is configured for ADF Desktop Integration. For more information about how to configure your application with ADF Desktop Integration, see Section 4.2, "Adding ADF Desktop Integration to a Fusion Web Application."

  2. In the Application Navigator, select the project, such as ViewController, to which you want to add your new workbook.

  3. From the File menu, choose New.

  4. In the New Gallery dialog, expand Client Tier, select ADF Desktop Integration, and then select Microsoft Excel Workbook, and click OK.

  5. In the Create ADF Desktop Integration-Enabled Excel Workbook dialog, if required, edit the file name of the workbook and its location. By default, the integrated Excel workbook is saved as adfdi-workbook.xlsx in the \src\excel directory of the selected project.

  6. Click OK.

JDeveloper adds an integrated Excel workbook into your Fusion web application. In Application Navigator, double-click the workbook to open and configure it as desired. For more information about how to configure the integrated Excel workbook, see Section 4.4.3, "How to Configure a New Integrated Excel Workbook."

Tip:

If you create and add an integrated Excel workbook in JDeveloper, you may not be required to configure various workbook properties, as described in Section 4.4.3, "How to Configure a New Integrated Excel Workbook."

4.4.2 How to Enable ADF Desktop Integration in a Workbook

If you want to integrate an existing workbook with your Fusion web application, you must enable ADF Desktop Integration for the workbook. For information about the file formats of Excel workbooks that you can convert for integration with a Fusion web application, see Section 3.2, "Required Oracle ADF Modules and Third-Party Software."

To enable ADF Desktop Integration in an Excel workbook:

  1. In Excel, open your workbook, or create a new blank workbook.

  2. In the Oracle ADF tab, click Workbook Properties.

  3. In the Enable Workbook dialog, click Yes.

    The ADF Desktop Integration framework prepares your workbook and initializes the ADF Desktop Integration Designer task pane.

  4. Save the workbook.

Although you can store the Excel workbooks that you integrate with Fusion web applications anywhere you choose, there are several advantages to storing them with the other files of your Fusion web application. Some of these advantages are:

  • Source control of the workbooks

  • Facilitating the download of workbooks from web pages

  • The file system folder picker that appears the first time a workbook is opened defaults to the location where you store the workbook

For example, the Master Price List module of the Fusion Order Demo stores the Excel workbooks it integrates in the following subdirectory:

FOD_HOME\MasterPriceList\ViewController\src\oracle\foddemo\masterpricelist\excel

where FOD_HOME is the root directory that stores the source files for the Fusion Order Demo application.

4.4.3 How to Configure a New Integrated Excel Workbook

After the workbook is integrated with ADF Desktop Integration, you must configure it.

To configure a new integrated Excel workbook:

  1. When you enable ADF Desktop Integration in a workbook, the Browse for Folder dialog automatically appears, as illustrated in Figure 4-3.

    Figure 4-3 Browse For Folder Dialog

    Dialog box to select JDev project.

    Use the Browse for Folder dialog to select the JDeveloper application home directory. In a typical JDeveloper project, the JDeveloper application home directory stores the application_name.jws file. The value you select is assigned to the ApplicationHomeFolder workbook property.

    Note:

    The Browse for Folder dialog does not appear if the workbook is located within the JDeveloper application workspace. In such a case, the value of ApplicationHomeFolder workbook property is assigned automatically.
  2. In the Workbook group of the Oracle ADF tab, click Workbook Properties.

  3. In the Edit Workbook Properties dialog, set or verify the values for the following properties so that you can switch between design mode and test mode as you configure your workbook:

    • ApplicationHomeFolder

      The value for this property corresponds to the absolute path for the root directory of the JDeveloper application workspace (.jws). You specified this value in Step 1.

      Note:

      If you are opening the Excel file after moving your application directory, ensure that the ApplicationHomeFolder property's value reflects the correct path.
    • Project

      The value for this property corresponds to the name of JDeveloper project (.jpr) in the JDeveloper application workspace. To change the project, click the ellipsis (...) button and choose the project from the Project dialog that lists the projects defined in the JDeveloper application workspace.

      By default, Project is set to the name of the project that contains the Excel document. ADF Desktop Integration loads the names of the available projects from the application_name.jws specified as a value for ApplicationHomeFolder.

    • WebAppRoot

      Set the value for this property to the fully qualified URL for the web context root that you want to integrate your desktop application with. The fully qualified URL has the following format:

      http://<hostname>:<portnumber>/context-root

      In JDeveloper, you specify the web context root (context-root) in the Java EE Application page of the Project Properties dialog. Figure 4-4 shows the web context root for the Master Price List module.

      Figure 4-4 Setting Web Context Root in JDeveloper

      Project Properties dialog dhowing web context root

      Note that the fully qualified URL is similar to the following if you set up a test environment on your system using the Master Price List module:

      http://127.0.0.1:7101/FODMasterPriceList

      For information about how to verify that the Fusion web application is online and that it supports ADF Desktop Integration, see Section C.1, "Verifying That Your Fusion Web Application Supports ADF Desktop Integration."

      If you are integrating an Excel file with a secure Fusion web application, it is recommended that you use the https protocol while entering WebAppRoot's value. For more information about securing your Fusion web application, see Oracle Fusion Middleware Programming Security for Oracle WebLogic Server.

    • WebPagesFolder

      Set the value for this property to the directory that contains web pages for the Fusion web application. The directory path should be relative to the value of ApplicationHomeFolder. For example, in the EditPriceList-DT.xlsx workbook, WebPagesFolder is set to ViewController\public_html.

    Figure 4-5 shows an implementation of workbook properties in the Edit Workbook Properties dialog of Master Price List module's EditPriceList-DT.xlsx workbook.

    Figure 4-5 Edit Workbook Properties Dialog

    Workbook Properties dialog
  4. Click OK.

  5. In the Workbook group of the Oracle ADF tab, click Worksheet Properties.

  6. In the Edit Worksheet Properties dialog, click the ellipsis button (...) beside the Page Definition input field and select a page definition file from the dialog that appears.

  7. Click OK.

    The Excel worksheet appears with ADF Desktop Integration in Excel's task pane. The bindings of the page definition file, you selected in Step 6, appear in the Bindings tab.

  8. Save the Excel workbook.

4.4.4 How to Add Additional Worksheets to an Integrated Excel Workbook

To use Oracle ADF functionality, you must associate each worksheet with a page definition file. You associate a page definition file with a worksheet when you add a worksheet to the integrated Excel workbook. You can integrate multiple worksheets in an integrated Excel workbook with a Fusion web application. Use a different page definition file for each worksheet in the integrated Excel workbook.

To associate a page definition file with an Excel worksheet:

  1. While the Excel workbook is in design mode, click the Home tab in Excel ribbon, and then choose Insert > Insert Sheet in the Cells group.

  2. In the Choose Page Definition dialog, select the page definition file.

    This populates the bindings palette in the ADF Desktop Integration task pane with the bindings contained in the page definition file. You can now configure the worksheet with Oracle ADF functionality.