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 Oracle ADF Desktop Integration module.

This chapter includes the following sections:

Section 4.1, "Introduction to Preparing Your Integrated Excel Workbooks"
Section 4.2, "Adding Oracle ADF Desktop Integration to a Fusion Web Application"
Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook"
Section 4.4, "Preparing Your Workbook"

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.

4.2 Adding Oracle ADF Desktop Integration to a Fusion Web Application

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

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.1 How to Add Desktop Integration to Your JDeveloper Project

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

To add Oracle ADF Desktop Integration to your project:

In the Application Navigator, right-click the project to which you want to add the Oracle ADF Desktop Integration module and choose Project Properties from the context menu.

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.
In the Project Properties dialog, select Technology Scope to view the list of available technologies.
Select the ADF Desktop Integration and ADF Library Web Application Support (optional) project technologies and add them to the Selected Technologies list.
Click OK.

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

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

The project adds the Oracle ADF Desktop Integration runtime library. This library references the following .jar files in its class path:
- wsclient.jar
- adf-desktop-integration.jar
- resourcebundle.jar
The project adds an ADF bindings filter (adfBindings).
The project's deployment descriptor (web.xml) is modified to include the following entries:
- 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 Oracle ADF Desktop Integration to a project makes other changes to web.xml. Note that some entries in web.xml will be added only if they do not exist.

When you add ADF Library Web Application Support to the technology scope of your project, the project's web.xml file is modified to include the following entries:

    <filter>
        <filter-name>ADFLibraryFilter</filter-name>
        <filter-class>oracle.adf.library.webapp.LibraryFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>ADFLibraryFilter</filter-name>
        <url-pattern>/*</url-pattern>
        <dispatcher>FORWARD</dispatcher>
        <dispatcher>REQUEST</dispatcher>
    </filter-mapping>
    <servlet>
        <servlet-name>adflibResources</servlet-name>
        <servlet-class>oracle.adf.library.webapp.ResourceServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>adflibResources</servlet-name>
        <url-pattern>/adflib/*</url-pattern>
    </servlet-mapping>

Ensure that you observe the following points while updating filter and filter mapping information in the web.xml file:

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.
the adfdiExcelDownload <filter> element appears before the <filter> element for ADFLibraryFilter, and the <filter-mapping> elements for adfdiExcelDownload appears before the <filter-mapping> elements for ADFLibraryFilter.

The following example shows the <filter> and <filter-mapping> elements in the web.xml file:

    <filter>
        <filter-name>adfdiExcelDownload</filter-name>
        <filter-class>
            oracle.adf.desktopintegration.filter.DIExcelDownloadFilter
        </filter-class>
    </filter>
    <filter>
        <filter-name>ADFLibraryFilter</filter-name>
        <filter-class>oracle.adf.library.webapp.LibraryFilter</filter-class>
    </filter>
    ...
    <filter-mapping>
        <filter-name>adfdiExcelDownload</filter-name>
        <url-pattern>*.xlsx</url-pattern>
    </filter-mapping>
    <filter-mapping>
        <filter-name>adfdiExcelDownload</filter-name>
        <url-pattern>*.xlsm</url-pattern>
    </filter-mapping>
    ...
    <filter-mapping>
        <filter-name>ADFLibraryFilter</filter-name>
        <url-pattern>/*</url-pattern>
        <dispatcher>FORWARD</dispatcher>
        <dispatcher>REQUEST</dispatcher>
    </filter-mapping>

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

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

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

Table 4-1 Binding Requirements for Oracle ADF Desktop Integration Components

Oracle 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 Oracle ADF Desktop Integration component uses the label property of a control binding.
ADF Desktop Integration 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 the Oracle ADF Desktop Integration module can invoke action sets. Action sets can reference action bindings, method action bindings, or actions exposed by components in the Oracle ADF Desktop Integration module. 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 the Oracle ADF Desktop Integration module use, see Appendix A, "Oracle ADF Desktop Integration Component Properties and Actions".

More information about the elements and attributes in page definition files can be found in the "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 creat e 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:

In JDeveloper, create a new JSF page in your Oracle 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 Oracle ADF Desktop Integration 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.
In the Application Navigator, select the page, right-click it, and select Go to Page Definition from the context menu.
In the Confirm Create New Page Definition dialog box, click Yes.
Add the bindings that you require for your integrated Excel workbook to the page definition file.
Save the page definition file.

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

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

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.
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 Oracle ADF Desktop Integration Designer. You associate a page definition file with an Excel worksheet when you choose the page definition file, as described in Section 4.4.2, "How to Open an Excel Workbook for the First Time".

The Oracle ADF Desktop Integration Designer toolbar 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:

Rebuild and run the Fusion web application that the Excel workbook is integrated with.
In the Excel workbook, click the Reload button on the Oracle ADF Desktop Integration Designer toolbar.

For information about the Reload button, see Section 5.1, "Introduction to the 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 Oracle 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.3, "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:

${}

Do not use the following syntax to write EL expressions:

#{}

EL expressions using this syntax generate errors as they attempt to access the Oracle ADF Faces context which is not available.

Note:

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

4.4 Preparing Your Workbook

Before you can configure an Excel workbook with Oracle ADF functionality, you need to prepare the workbook by:

Running the conversion utility so that it can execute the Oracle ADF Desktop Integration client framework
Setting several properties when you open the workbook for the first time
(Optional) adding additional worksheets

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

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 in your Oracle ADF Desktop Integration project. These advantages include source control of the workbooks, facilitating the download of workbooks from web pages, and the fact that 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\public_html\excel

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

4.4.1 How to Run the Conversion Utility

The conversion utility is a command-line executable (convert-adfdi-excel-solution.exe) provided with the Oracle ADF Desktop Integration module that prepares the specified Excel workbook so that you can integrate it with a Fusion web application by configuring it with Oracle ADF functionality. 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".

The Oracle ADF Desktop Integration module stores convert-adfdi-excel-solution.exe in the following directory:

ADFDI_HOME\bin\excel\convert

where ADFDI_HOME is the folder in which you set up the Oracle ADF Desktop Integration module, as described in Section 3.8, "Using the Oracle ADF Desktop Integration Module on a System with Multiple Instances of JDeveloper".

To run the conversion utility to convert an Excel workbook:

Open a command window and navigate to the following directory location:
```
ADFDI_HOME\bin\excel\convert
```
where ADFDI_HOME refers to the directory in which you set up the Oracle ADF Desktop Integration module.
Execute the following command to convert the Excel workbook:
```
convert-adfdi-excel-solution.exe directory\workbook.xlsx -attach 
```
where directory refers to the directory that stores your Excel workbook and workbook.xlsx is the file name of the Excel workbook.

If the command executes successfully, a message similar to the following appears in the command window:
```
Solution id is: fb43e1d8-0595-4c3f-8926-0de0494c37d3
Deployment manifest full path is: file:///C:/jdev/JDEVADF_MAIN_GENERIC_4773/adfdi/bin/excel/client/adfdi-excel-client.vsto
Attaching customization...
Using relative path: adfdi-excel-client.vsto
VSTO manifest attached successfully
```
You can now open the Excel workbook and set several properties that allow you to start configuring the workbook with Oracle ADF functionality. For more information about the supported arguments of the conversion utility, see Appendix D, "Using Workbook Management Tools".

4.4.2 How to Open an Excel Workbook for the First Time

After you convert your workbook using the conversion utility, you can open it. When you open a workbook for the first time, you must set several properties, as described in the following procedure.

Note:

The procedure assumes that you store the workbook in a folder external to the Oracle ADF Desktop Integration project. If you store the workbook in a subfolder of the Oracle ADF Desktop Integration project, many properties (for example, the ApplicationHomeFolder workbook property) get populated automatically.

To open an Excel workbook for the first time:

Navigate to the directory that stores your Excel workbook and open it.
Use the dialog box that appears to select the JDeveloper application home folder.

In a typical JDeveloper project, the JDeveloper application home folder stores the application_name.jws file. The value you select is assigned to the ApplicationHomeFolder workbook property.
In the Oracle ADF Desktop Integration Designer, click Workbook Properties to display the Edit Workbook Properties dialog box.
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 folder of the JDeveloper application workspace (.jws). You specified this value in Step 2.
  
  Note:
  If you are opening the Excel file after moving your application directory, ensure that the ApplicationHomeFolder property's value reflects the correct path.
- Login.ProtectedWebPage
  
  Set the value for this property to a protected page with a directory path that is relative to the value of WebAppRoot. For example, in the Master Price List module's EditPriceList-DT.xlsx workbook, the value of WebAppRoot is http://127.0.0.1:7101/FODMasterPriceList and the value of ProtectedWebPage is /faces/secured/LandingPad.jspx.
  
  Create a blank protected page that you specify as the value for this property. Apply a security constraint to this protected page. For more information about securing an integrated Excel workbook, see Chapter 11, "Securing Your Integrated Excel Workbook".
- Project
  
  Specify the folder that contains a JDeveloper project (.jpr) in the JDeveloper application workspace. Typically the folder name is ViewController in a Fusion web application that uses the model-view-controller architecture. The Oracle ADF Desktop Integration module loads the names of the available projects from the application_name.jws specified as a value for ApplicationHomeFolder. The folder path specified as a value for Project should be relative to the value of 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 Java EE Application page of the Project Properties dialog. Figure 4-2 shows the web context root for the Master Price List module.
  
  Figure 4-2 Setting Web Context Root in JDeveloper
  
  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 supports Oracle ADF Desktop Integration, see Section C.1, "Verifying That Your Fusion Web Application Supports 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 folder that contains web pages for the Fusion web application. The folder path should be relative to the value of ApplicationHomeFolder. You must set a value for this property before you can set a value for Login.ProtectedWebPage.
For example, Figure 4-3 shows an implementation of workbook properties in the Edit Workbook Properties dialog of Master Price List module's EditPriceList-DT.xlsx workbook.

Figure 4-3 Edit Workbook Properties Dialog
Click OK.
In the Oracle ADF Desktop Integration Designer, click Worksheet Properties to display the Edit Worksheet Properties dialog box.
Click the ellipsis button (...) beside the Page Definition input field and select a page definition file from the dialog box that appears.
Click OK.

The Excel worksheet appears with the Oracle ADF Desktop Integration Designer in Excel's Document Actions. The bindings in the page definition file you selected in Step 7 appear in the Bindings view tab.
Save the Excel workbook.

4.4.3 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:

While the Excel workbook is in design mode, click Home, and then click Insert > Insert Sheet in the Cells group.
Select the page definition file from the Choose Page Definition dialog box that appears.

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