This chapter describes how to prepare Excel workbooks and integrate them with Fusion web applications using ADF Desktop Integration, how to use the page definition files with an integrated Excel workbook, and how to enable ADF Desktop Integration manually to integrate an existing workbook with the Fusion web application.
This chapter includes the following sections:
Working with Page Definition Files for an Integrated Excel Workbook
Adding an Integrated Excel Workbook to a Fusion Web Application
Using an Integrated Excel Workbook with Older Versions of ADF Desktop Integration
This chapter (and the guide as a whole) assumes that you have developed a functioning Fusion web application, as described in Fusion Developer's Guide for Oracle Application Development Framework.
Having developed the Fusion web application, you perform the tasks described in this chapter to configure an integrated Excel workbook with the Fusion web application. The subsequent chapters of the guide enable you to configure the integrated workbook with Oracle ADF components that provide the functionality you require at runtime.
Note:
Before you start, ensure that designer tools of ADF Desktop Integration are enabled. For more information, see Section 3.4, "Installing ADF Desktop Integration."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 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 |
|
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 9.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 the "pageNamePageDef.xml" section of 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 ADF Model in a Fusion Web Application" chapter of Fusion Developer's Guide for Oracle Application Development Framework.
You create and configure a page definition file that determines the Oracle ADF bindings to expose in the JDeveloper project.
It may be helpful to have an understanding of page definition files. For more information, see Section 4.2, "Working with Page Definition Files for an Integrated Excel Workbook."
To create an ADF Desktop Integration page definition file:
In JDeveloper, add a new JSF page in the ADF Desktop Integration application's project.
Tip:
Add an ADF FacesTable
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.In the Application Navigator, right-click the page and choose Go to Page Definition.
In the Confirm Create New Page Definition dialog, click Yes.
Add the bindings that you require for the integrated Excel workbook to the page definition file.
Save the page definition file.
Figure 4-1 shows the ExcelCustomers.xml
page definition file that the Customers worksheet in the EditCustomers-DT.xlsx
workbook references.
Make and run the Fusion web application if you plan to run the integrated Excel workbook in test mode or to publish it.
For information about working with page definition files, see the "Working with Page Definition Files" section in Fusion Developer's Guide for Oracle Application Development Framework.
JDeveloper creates the DataBindings.cpx
file the first time you add a page definition file in the JDeveloper project using the procedure described in Section 4.2.1, "How to Create ADF Desktop Integration Page Definition File."
The DataBindings.cpx
file defines the binding context for the Fusion web application and provides the configuration 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 Fusion Developer's Guide for Oracle Application Development Framework. Information about the elements and attributes in the file can be found in the "DataBindings.cpx" section of the same guide.
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.3.2, "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
It may be helpful to have an understanding of page definition files. For more information, see Section 4.2, "Working with Page Definition Files for an Integrated Excel Workbook."
To reload page definition files in an Excel workbook:
Ensure that you have saved the updated page definition file in JDeveloper.
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, "About Development Tools."
After reloading the page definition file, the ADF Desktop Integration task pane of the worksheet displays the same bindings that are available in its associated page of the Fusion web application. For example, Figure 4-2 shows the bindings in the ExcelCustomers.xml
page definition file and the same bindings in the worksheet of the EditCustomers-DT.xlsx
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 separate page definition file with each worksheet as described in Section 4.3.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:
Dynamic (${})
Do not use the syntax Deferred (#{})
to write EL expressions. EL expressions using this syntax generate errors because they attempt to access the ADF Faces context, which is not available.
Note:
EL expressions that you write for ADF Desktop Integration component in the integrated Excel workbook, such as the Input Text component, must use theDeferred (#{})
syntax.The Fusion web application is automatically enabled with ADF Desktop Integration when you add an integrated Excel workbook to a project. An integrated Excel workbook enables you to add ADF components and ADF data bindings.
To add an integrated Excel workbook, open the Fusion web application in JDeveloper and add an Excel workbook to the project from New Gallery.
It may be helpful to have an understanding of adding ADF Desktop Integration to a Fusion web application. For more information, see Section 4.3, "Adding an Integrated Excel Workbook to a Fusion Web Application."
To add an integrated Excel workbook in JDeveloper:
Open the Fusion web application in JDeveloper.
In the Application Navigator, select the user interface project, such as ViewController, to which you want to add the new integrated Excel workbook.
From the File menu, choose New > From Gallery.
In the New Gallery, expand Client Tier, select ADF Desktop Integration, then Microsoft Excel Workbook, and then click OK.
Figure 4-3 shows the New Gallery with ADF Desktop Integration category and the Microsoft Excel Workbook option.
Click OK.
In the Create ADF Desktop Integration-Enabled Excel Workbook dialog, verify the desired location and type a unique workbook name. Consider adding a suffix of -DT
to help with publishing later. For example, MyWorkbook-DT.xlsx
.
By default, the integrated Excel workbook is saved as adfdi-workbook.xlsx
in the <
PROJECT_HOME
>\src\excel
directory of the selected project. Although you can save the workbook anywhere you choose, you should save the workbook with the other files of the Fusion web application.
Click OK.
JDeveloper adds the integrated Excel workbook into the Fusion web application, and automatically enables the project with ADF Desktop Integration. Figure 4-4 shows the ViewController project in the Application Navigator.
After adding the integrated Excel workbook, you must configure it.
It may be helpful to have an understanding of adding an integrated Excel workbook to a Fusion web application. For more information, see Section 4.3, "Adding an Integrated Excel Workbook to a Fusion Web Application."
To configure a new integrated Excel workbook:
Open the integrated Excel workbook.
If you have saved the workbook with other files of the Fusion web application, the Page Definition dialog automatically appears, as illustrated in Figure 4-5.
Select the page definition file for the active worksheet from the Page Definition dialog, and click OK.
If you have saved the workbook elsewhere, configure the workbook as described in Section 4.4.2, "How to Manually Configure a New Integrated Excel Workbook."
In the Workbook group of the Oracle ADF tab, click Workbook Properties.
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 the workbook:
ApplicationHomeFolder
The value for this property corresponds to the absolute path for the root directory of the JDeveloper application workspace (.jws
). If the workbook is located within the JDeveloper application workspace, the value of the ApplicationHomeFolder
workbook property is assigned automatically.
Note:
If you are opening the Excel file after moving the application directory, ensure that theApplicationHomeFolder
property's value reflects the correct path.Project
The value for this property corresponds to the name of the JDeveloper project (.jpr
) in the JDeveloper application workspace. To change the project, click the browse (...) icon and choose the project from the Project dialog, which 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 the Fusion web 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-6 shows the web context root used for the Summit sample application for ADF Desktop Integration in JDeveloper and integrated Excel workbook.
Note that the fully qualified URL is similar to the following if you set up a test environment on your system using the Summit sample application for ADF Desktop Integration:
http://localhost:7101/summit
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, you should use the https
protocol while entering the value for WebAppRoot
. For more information about securing the Fusion web application, see 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 EditCustomers-DT.xlsx
workbook, WebPagesFolder
is set to ViewController\public_html
.
Figure 4-7 shows an example of workbook properties in the Edit Workbook Properties dialog of the Summit sample application for ADF Desktop Integration EditCustomers-DT.xlsx
workbook.
Click OK.
Note:
In Step 1, if the fully qualified path of the selected page definition file exceeds the Windows path length limit, a warning message appears when the Workbook Properties dialog is closed, and the page definition will not load.Save the Excel workbook.
To use Oracle ADF functionality, 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.
It may be helpful to have an understanding of adding an integrated Excel workbook to a Fusion web application. For more information, see Section 4.3, "Adding an Integrated Excel Workbook to a Fusion Web Application."
To associate a page definition file with an Excel worksheet:
While the Excel workbook is in design mode, click the Home tab in the Excel ribbon, and then choose Insert > Insert Sheet in the Cells group.
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.
Note:
If you get an error message Programmatic access to Visual Basic Project is not trusted when you run an integrated Excel workbook after inserting a new worksheet, enable the Trust access to the VBA project object model checkbox in Excel Options. For more information, see Section 3.3, "Configuring Excel to work with ADF Desktop Integration."Workbooks that you create, as described in Section 4.3, "Adding an Integrated Excel Workbook to a Fusion Web Application,"are automatically configured to use ADF Desktop Integration functionality. For existing Excel workbooks, you must enable ADF Desktop Integration in the workbook to make it an integrated Excel workbook and configure a number of properties in the newly-integrated Excel workbook.
To integrate an existing workbook with the ADF Desktop Integration enabled Fusion web application, you must manually enable ADF Desktop Integration for the workbook. For information about the supported file formats of Excel workbooks that you can use for integration with a Fusion web application, see Section 3.2, "Required Oracle ADF Modules and Third-Party Software."
It may be helpful to have an understanding of adding integrated Excel workbook to a Fusion web application. For more information, see Section 4.3, "Adding an Integrated Excel Workbook to a Fusion Web Application."
To enable ADF Desktop Integration in an existing Excel workbook:
In Excel, open the workbook.
In the Workbook group of the Oracle ADF tab, click Workbook Properties.
In the Enable Workbook dialog, click Yes, as shown in Figure 4-8.
ADF Desktop Integration prepares your workbook, displays the ADF Desktop Integration Designer task pane, and opens the Browse For Folder dialog. For more information, see Section 4.4.2, "How to Manually Configure a New Integrated Excel Workbook."
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 the 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 Summit sample application for ADF Desktop Integration stores the Excel workbooks it integrates in the following subdirectory:
Summit_HOME
\ViewController\src\oracle\summitdi\excel
where Summit_HOME
is the root directory that stores the source files for the Summit sample application for ADF Desktop Integration.
After enabling ADF Desktop Integration manually in a workbook, you would need to configure it.
It may be helpful to have an understanding of adding an integrated Excel workbook to a Fusion web application. For more information, see Section 4.5, "Enabling ADF Desktop Integration Manually."
To manually configure a new integrated Excel workbook:
Open the integrated Excel workbook.
The Browse For Folder dialog automatically appears, as illustrated in Figure 4-9.
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 theApplicationHomeFolder
workbook property is assigned automatically.In the Workbook group of the Oracle ADF tab, click Workbook Properties.
In the Edit Workbook Properties dialog, configure the properties as described in Step 3 of Section 4.3.2, "How to Configure a New Integrated Excel Workbook."
Click OK.
In the Workbook group of the Oracle ADF tab, click Worksheet Properties.
In the Edit Worksheet Properties dialog, click the browse (...) icon beside the Page Definition input field and select a page definition file from the Page Definition dialog, as shown in Figure 4-10.
Click OK.
The Excel worksheet appears with ADF Desktop Integration in the task pane. The bindings of the page definition file that you selected in Step 6, appear in the Bindings tab.
Save the Excel workbook.
To enable ADF Desktop Integration in the Fusion web application without adding the integrated Excel workbook, you must add ADF Desktop Integration manually.
When you add the ADF Desktop Integration feature to a 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 theurl-pattern
attribute of the servlet-mapping
element for adfdiRemote
must match the value of the RemoteServletPath
workbook property described in Table A-20.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 already appear in the file.
If you want to distribute integrated workbooks by adding them to ADF library files, add ADF Library web application support to the Fusion web application. For more information, see the "Packaging a Reusable ADF Component into an ADF Library" section in Fusion Developer's Guide for Oracle Application Development Framework.
When 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-11 shows the Filters tab of the overview editor of the web.xml
in JDeveloper.
You should also update the include-extension-list
initialization parameter to add the Excel file extensions (such as .xlsx
and .xlsm
), as shown in Figure 4-12.
For more information about web.xml
, see Appendix D, "ADF Desktop Integration Settings in the Web Application Deployment Descriptor."
When you or your end users open an integrated Excel workbook created, or last updated, by a newer version of ADF Desktop Integration on a system running an older version of ADF Desktop Integration, a dialog appears if the integrated Excel workbook contains features that are incompatible with the older version of ADF Desktop Integration.
When you click OK on this dialog, ADF Desktop Integration disables the integrated Excel workbook and the end user cannot interact with the ADF Desktop Integration features in the workbook. The data in the workbook is not removed, but ADF Desktop Integration treats the workbook as a non-integrated workbook.
If the integrated Excel workbook does not contain incompatible features, no dialog appears and the workbook functions normally. For integrated Excel workbooks that contain incompatible features, upgrade the client version of ADF Desktop Integration, as described in Section 3.6, "Upgrading ADF Desktop Integration." End users can upgrade their client version, as described in Section H.1.3, "How to Upgrade ADF Desktop Integration On a Local System."
Integrated Excel workbooks created using 11.1.1.7.5 or earlier of ADF Desktop Integration do not have features that are incompatible with the ADF Desktop Integration 11.1.1.7.5 client. A future release of ADF Desktop Integration may introduce features that will be incompatible with clients using 11.1.1.7.5 or earlier of ADF Desktop Integration. A possible example is a change that would lead to a deserialization error, such as a new workbook ribbon command.
Note:
When the integrated Excel workbook is not compatible with the installed version of the ADF Desktop Integration client, a message is displayed when you open the workbook. In such a case, you should install the newer version of the ADF Desktop Integration client in order to interact with the newer workbook.