8 Adding Interactivity to Your Integrated Excel Workbook

This chapter describes how to add interactivity options to your integrated Excel workbook.

This chapter includes the following sections:

8.1 Introduction to Adding Interactivity to an Integrated Excel Workbook

Adding interactivity to an integrated Excel workbook permits end users to execute action sets that invoke Oracle ADF functionality in the workbook. It also provides status messages, alert messages, and error handling in the integrated Excel workbook while these action sets execute. In addition to end-user gestures (double-click, click, select) on the Oracle ADF Desktop Integration components that invoke action sets, you can configure workbook and worksheet menu items that end users use at runtime to invoke action sets.

The action sets that end users invoke can make use of functionality defined in the Excel workbook and in pages of the Fusion web application with which you integrate the Excel workbook. For example, the EditPriceList.xlsx workbook in the Master Price List module renders an ADF Button component that, at runtime, invokes a page from the Fusion web application. The invoked page allows end users to specify additional search criteria to what can be specified in the workbook's search form which is rendered using ADF Button, ADF Input Text, and ADF Label components.

In addition to action sets, you can configure Excel functionality, such as macros and Excel formulas, to manage the data that you want to download from or upload to your Fusion web application.

8.2 Using Action Sets

An action set is an ordered list of one or more of the following actions that execute in a specified order:

  • ADFmAction

  • ComponentAction

  • WorksheetMethod

  • Confirmation

  • Dialog

Invoking Action Sets

An action set can be invoked by an end-user's gesture (for example, clicking an ADF Button) or an Excel worksheet event. Where an end-user gesture invokes an action set, the name of the action set property in the ADF component's property inspector is prefaced by the name of the gesture required. The following list describes the property names that the Oracle ADF Desktop Integration module displays in property inspectors and what user gesture can invoke an action set:

  • ClickActionSet for an ADF Button component, as an end user clicks the button to invoke the associated action set

  • DoubleClickActionSet for an ADF InputText or ADF Output Text component, as an end user double-clicks these components to invoke the associated action set

  • SelectActionSet for a worksheet menu item, as an end user selects a menu item to invoke the associated action set

  • ActionSet for a worksheet event, as no explicit end-user gesture is required to invoke the action set

You invoke the Action Collection Editor from an ADF component, worksheet menu item, or worksheet event to define or configure an action set. In addition to defining the actions that an action set invokes, you can configure the action set's Alert properties to provide feedback on the result of invocation of an action set. You configure the Status properties for an action set to display a status message to end users while an action set executes the actions you define. For information about opening the Action Collection Editor, see Section 5.10, "Using the Collection Editors".

The Master Price List module provides many examples of action sets in use. One example is the ADF Button component labeled Save Changes at runtime in the EditPriceList.xlsx workbook. An action set has been configured for this ADF Button component that invokes the ADF Table component's Upload action illustrated by Figure 8-1 which shows the Action Collection Editor in design mode.

Figure 8-1 Action Set for Save Changes Button in the EditPriceList.xlsx Workbook

ActionSet for Save Changes Button in EditPriceList.xls

Tip:

Write a description in the Annotation field for each action that you add to the Action Collection Editor. The description you write appears in the Members list view and, depending on how you write it, may be more meaningful than the default entry that the Oracle ADF Desktop Integration module generates.

Execution Sequence for an Action Set

The Oracle ADF Desktop Integration module invokes the actions in an action set in the order that you specify in the Members list view.

8.2.1 How to Invoke an ADFm Action in an Action Set

You can invoke one or more ADFm actions in an action set. An ADFm action is also known as an action binding in the JDeveloper project where you develop your Fusion web application. Page definition files define what action bindings are available to invoke in a worksheet that you integrate with your Fusion web application. For more information about page definition files and action bindings in an integrated Excel workbook, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook".

You use the Action Collection Editor to specify an ADFm action to invoke.

To invoke an ADFm action in an action set:

  1. Open the Action Collection Editor and invoke the dropdown list from the Add button illustrated here.

    Add button to invoke Action Collection Editor

  2. Select ADFmAction and configure its properties as described in the following list:

    • ActionID

      Click the ellipsis button (...) beside the input field for ActionID to invoke the Binding ID picker and select the ADFm action that the action set invokes at runtime.

    • Annotation

      Optionally, enter a comment about the purpose of the action that you are configuring. The value you set for this property has no functional impact.

  3. Click OK.

8.2.2 How to Invoke Component Actions in an Action Set

The ADF Table and the ADF Read-only Table components in the Oracle ADF Desktop Integration module expose actions that can be used to manage the transfer of data between Excel worksheets that you integrate with a Fusion web application. The ADF Read-only Table component exposes one component action, Download, while the ADF Table component exposes a number of other actions. More information about the actions for both components can be found in Appendix A, "Oracle ADF Desktop Integration Component Properties and Actions".

You configure action sets to invoke one or more component actions by referencing the component action in the array of actions. For example, Figure 8-2 shows the Choose Component Method dialog box where the actions exposed by the ADF Table and ADF Read-only Table components present in a worksheet can be invoked by a SelectActionSet action set.

Figure 8-2 Choose Component Method Dialog Box

The Choose Component Method Dialog Box

Note:

An Excel worksheet must include an ADF Table or ADF Read-only Table component before one or more of these components' actions can be invoked by an action set.

To invoke a component action from an action set:

  1. Open the Action Collection Editor and invoke the dropdown list from the Add button illustrated here.

    Add button for ActionSets

  2. Select ComponentAction and configure its properties as described in the following list:

    • ComponentID

      Click the ellipsis button (...) beside the input field for ComponentID to invoke the Choose Component Method dialog box and select the component action that the action set invokes at runtime. This populates the ComponentID and Method input fields.

    • Method

      The component's action that the action set invokes at runtime.

    • Annotation

      Optionally, enter a comment about the purpose of the action that you are configuring. The value you set for this property has no functional impact.

  3. Click OK.

8.2.3 What You May Need to Know About an Action Set Invoking a Component Action

Note the following pieces of information about the behavior of action sets in integrated Excel workbooks.

Verifying an Action Set Invokes the Correct Component Action

When creating an action set, make sure that you invoke the component action from the correct instance of a component when a worksheet includes more than one instance of an ADF Read-only Table or ADF Table component. Figure 8-3 shows the Choose Component Method dialog box displaying two instances of the ADF Read-only Table component. Use the value of the ComponentID property described in Table A-1 to correctly identify the instance of a component on which you want to invoke a component action.

Figure 8-3 Choose Component Method Dialog Box

Choose Component Method Dialog Box

Invoking Action Sets in a Disconnected Workbook

End users can use integrated Excel workbooks while disconnected from a Fusion web application, as described in Chapter 15, "Using an Integrated Excel Workbook Across Multiple Web Sessions and in Disconnected Mode". Some component actions, such as the Download action of the ADF Table component, require a connection to the Fusion web application to complete successfully. If an end user invokes an action set that includes such a component action, the integrated Excel workbook attempts to connect to the Fusion web application and, if necessary, invokes the authentication process described in Section 11.2, "Authenticating the Excel Workbook User".

8.2.4 How to Invoke an Action Set from a Worksheet Event

The Oracle ADF Desktop Integration module provides a number of worksheet events that, when triggered, can invoke an action set. The following worksheet events can invoke an action set:

  • Startup

  • Shutdown

    Do not invoke a Dialog action from this event if the Dialog action's Target property is set to TaskPane.

  • Activate

  • Deactivate

You add an element to the array of events (WorksheetEvent[] Array) referenced by the Events worksheet property. You specify an event and the action set that it invokes in the element that you add. For more information about the Events worksheet property and the worksheet events that can invoke an action set, see Table A-18. See Table A-13 for more information about action sets.

Use the WorkSheetEvent Collection Editor to specify an action set to be invoked by a worksheet event.

To invoke an action set from a worksheet event:

  1. In the Oracle ADF Desktop Integration Designer, click Worksheet Properties to display the Edit Worksheet Properties dialog box.

  2. Click the ellipsis button (...) beside the input field for the Events property to display the WorksheetEvent Collection Editor.

  3. Click Add to add a new element that specifies an event and a corresponding action set that the event invokes.

    Figure 8-4 shows an example from the EditPriceList.xlsx file in the Master Price List module where the worksheet event, Startup, invokes an action set that invokes the ADF Table component's Download action.

    Figure 8-4 Worksheet Startup Event Invokes an Action Set

    Worksheet Startup event invokes an ActionSet

  4. Click OK.

8.2.5 How to Display a Status Message While an Action Set Executes

You can display a status message to end users while an action set executes by specifying values for the Status properties in an action set.

Some of the default values for properties in the ActionSet.Status group are EL expressions that resolve to strings defined in the reserved resource bundle at runtime. You can replace these default values with EL expressions that refer to your custom resource bundles. For more information, see Section 10.2, "Using Resource Bundles in an Integrated Excel Workbook".

You use the Action Collection Editor to configure values for the ActionSet.Status properties.

To display a status message:

  1. Open the Action Collection Editor.

  2. Set values for the properties in the ActionSet.Status group of properties as described in the following table.

    For this property... Enter or select this value...
    Enabled True to display a status message. True is the default value.
    Message An EL expression or string that resolves to the status message to display at runtime. For example, the Search button in the Master Price List module's EditPriceList.xlsx file has the following value configured for the Message property:

    Searching and downloading...
    Title An EL expression or string that resolves to the title of the status message to display at runtime. For example, the Search button in the Master Price List module's EditPriceList.xlsx file has the following value configured for the Title property:

    Query Products

    Figure 8-5 shows the values configured for the ActionSet.Status group of properties of the ADF Button component in the EditPriceList.xlsx workbook of the Master Price List module that is labeled Search at runtime.

    Figure 8-5 Status Message Properties in an Action Set

    Status message properties in an ActionSet

    For more information about the ActionSet.Status group of properties, see the entry for Status in Table A-13.

  3. Click OK.

8.2.6 What Happens at Runtime When an Action Set Displays a Status Message

Once an action set is invoked, a status message appears if the ActionSet.Status properties are configured to display a status message. Figure 8-6 shows the status message that appears at runtime when the action set configured for the Search button in the EditPriceList.xlsx workbook commences execution.

Figure 8-6 Runtime View of Status Message

Runtime view of status message

8.2.7 How to Provide an Alert After the Invocation of an Action Set

You can display an alert message to end users that notifies them when an action set operation completes successfully or fails. For example, you can display a message when all actions in an action set succeed or when there was at least one failure. The ActionSet.Alert group of properties configures this behavior.

Note:

An alert message does not appear if an end user cancels the execution of an action set. For example, you configure an alert message to appear after an action set that invokes a web page in a popup dialog completes execution. At runtime, the end user cancels execution of the action set by closing the popup dialog using the close button of the Excel web browser control that hosts the popup dialog. In this scenario, no alert message appears if configured. For more information about displaying web pages, see Section 8.4, "Displaying Web Pages from a Fusion Web Application".

Many of the default values for properties in the ActionSet.Alert group are EL expressions that resolve to strings defined in the reserved resource bundle at runtime. You can replace these default values with EL expressions that refer to your custom resource bundles. For more information, see Section 10.2, "Using Resource Bundles in an Integrated Excel Workbook".

You use the Action Collection Editor to configure values for the ActionSet.Alert group of properties.

To add an alert to an action set:

  1. Open the Action Collection Editor.

  2. Set values for the properties in the ActionSet.Alert group of properties as described in the following table.

    For this property... Enter or select this value...
    Enabled Select True from the dropdown list to display an alert message once the action set completes. The default value is False.
    FailureMessage Specify an EL expression or string that evaluates to a message to appear in the dialog box if errors occur during execution of the action set. For example, the Save Changes button in the Master Price List module's EditPriceList.xlsx workbook has the following value configured for the FailureMessage property:

    #{components.TAB442758137.errors}

    The Save Changes button invokes an action set that, in turn, invokes the ADF Table component's Upload action. The EL expression specified for FailureMessage retrieves error messages if the Upload action encounters errors. For more information about error handling, see Section 12.4, "Error-Reporting in an Integrated Excel Workbook".
    OKButtonLabel Specify an EL expression that evaluates to a message to appear in the OK button of the dialog box. The default EL expression is:

    #{_ADFDIres['DIALOGS_OK_BUTTON_LABEL']}
    SuccessMessage Specify an EL expression that evaluates to a message to appear in the dialog box if no errors occur during the execution of the action set. For example, the Save Changes button in the Master Price List module's EditPriceList.xlsx workbook has the following value configured for the SuccessMessage property:

    Changes saved successfully
    Title Specify an EL expression that evaluates to a message to appear in the title area of the dialog box. The default EL expression is:

    #{_ADFDIres['DIALOGS_ACTION_TITLE']}

    Figure 8-7 shows the values configured for an ADF Button component's ActionSet.Alert group of properties in the EditPriceList.xlsx workbook of the Master Price List module. This ADF Button component is labeled Save Changes at runtime.

    Figure 8-7 Alert Message Properties in an Action Set

    Alert Message Properties in an ActionSet

  3. Click OK.

8.2.8 What Happens at Runtime When an Action Set Provides an Alert

Figure 8-8 shows the alert message that appears at runtime when the action set invoked by the ADF Button component labeled Save Changes successfully completes execution.

Figure 8-8 Runtime View of an Alert Message

Runtime View of an Alert Message

8.2.9 How to Configure Error Handling for an Action Set

You specify values for an action set's ActionOptions properties to determine what an action set does if one of the following events occurs:

  • An action in the action set fails

  • All actions in the action set complete successfully

Where you configure an action set's ActionOptions properties depends on whether an ADF component, worksheet event, or worksheet menu item invokes the action set. Configure the ActionOptions properties from the:

  • ADF component's property inspector, if an ADF component invokes the action set

  • WorksheetMenuItem Collection Editor, if a worksheet menu item invokes the action set

  • WorksheetEvent Collection Editor, if a worksheet event invokes the action set

For information about how to invoke these editors or an ADF component's property inspector, see Chapter 5, "Getting Started with the Development Tools". More information about action set properties can be found in Table A-12.

To configure error handling for an action set:

  1. Open the appropriate editor or property inspector and configure values for the action set's ActionOptions properties as described in the following table.

    Set this property... To...
    AbortOnFailure True (default value) so that the action set does not any execute any further actions if the current action fails. When set to False, the action set executes all actions regardless of the success or failure of previous actions.
    FailureActionID Specify an ADFm action to invoke if an action set does not complete successfully. For example, you could specify an ADFm action that rolls back changes made during the unsuccessful invocation of the action set.
    SuccessActionID Specify an ADFm action to invoke if an action set completes successfully. For example, you could specify an action binding that executes a commit action. A value for this property is optional and you can specify a final action, such as an action binding that executes a commit action, in the action set itself.

  2. Click OK.

8.3 Creating Menu Items

You can create a menu with items that invoke Oracle ADF functionality in your integrated Excel workbook. Setting the ToolbarMenu.Enabled workbook property to True makes this menu appear. The ToolbarMenu.Title property determines the title of the menu that the end user sees at runtime. At runtime, end users must click Excel's Add-Ins tab to view any menu that you configure using the ToolbarMenu properties. Any menu that you configure appears in the Custom Toolbar section of the Add-Ins tab illustrated by Figure 8-9.

Figure 8-9 Runtime View of Toolbar Menu in Excel's Add-Ins Tab

Runtime View of Toolbar Menu in Excel’s Add-Ins Tab

Two groups of items can appear in the menu at runtime: items that invoke commands on the workbook and items that invoke commands on the current worksheet. You configure the WorkbookMenuItems[] array in the properties of the workbook so that the menu contains items allowing an end user to invoke workbook actions such as Login and Logout. You configure the WorksheetMenuItem[] array in the properties of the worksheet so that the menu contains items allowing a user to invoke an action set.

A menu separator appears between the workbook menu items and the worksheet menu items. If a worksheet has no items defined for the menu, no menu separator appears. Worksheet menu items appear only if the worksheet is in focus.

Figure 8-10 shows a menu at runtime where the workbook actions appear first, followed by a menu separator, and finally the worksheet menu items that invoke SelectActionSet action sets.

Figure 8-10 Runtime Toolbar Menu

Runtime Toolbar Menu

8.3.1 How to Configure a Workbook Menu Item

To define a workbook menu item, you configure a number of workbook properties. By default, menu items for all workbook actions are defined in the workbook toolbar. The following procedure shows how to create or remove an item in the menu by using the workbook action, Login, as an example.

To define a workbook menu item:

  1. Click Workbook Properties in the Oracle ADF Desktop Integration Designer.

  2. Click ToolbarMenu and then click the ellipsis button (...) beside the WorkbookMenuItem[] array to display the WorkbookMenuItem Collection Editor illustrated in Figure 8-11.

    Figure 8-11 WorkbookMenuItem Collection Editor

    The workbook menu item collection displays menu items.

  3. Click Add and specify values for the properties of the workbook menu item as follows:

    • Method

      Specify the workbook action that you want the menu item to invoke.

    • Label

      Enter a value in the input field that appears as the label at runtime. Alternatively, invoke the expression builder by clicking the ellipsis button (...) and write an EL expression that resolves to a string value in a resource bundle. Note that the runtime value that appears in the label cannot exceed 255 characters. A runtime value that exceeds 255 characters is truncated so that only 255 characters appear.

      For more information about using resource bundles, see Section 10.2, "Using Resource Bundles in an Integrated Excel Workbook".

      For more information about labels, see Section 9.4, "Using Labels in an Integrated Excel Workbook".

      Tip:

      Use the arrow controls to determine the order in which menu items appear at runtime.

  4. Click OK.

8.3.2 How to Configure a Worksheet Menu Item

To define a worksheet menu item, you configure properties for the worksheet using the property inspector. By default, no menu items are defined for the toolbar in the worksheet properties. You add members to the array that is referenced by the MenuItems property in the properties of the worksheet.

WARNING:

Set the ToolbarMenu.Enabled workbook property to TRUE to display menu items. If ToolbarMenu.Enabled is set to FALSE, no menu items appear. For more information about workbook properties, see Table A-17.

To define a worksheet menu item:

  1. Click Worksheet Properties in the Oracle ADF Desktop Integration Designer.

  2. Click the ellipsis button (...) beside the input field for the MenuItems property to invoke the WorksheetMenuItem Collection Editor illustrated in Figure 8-12.

    Figure 8-12 WorksheetMenuItem Collection Editor

    WorksheetMenuItem Collection Editor

  3. Click Add to add a new menu item in the Members list of the collection editor.

  4. Configure the properties of SelectActionSet to specify the type of action(s) that the menu item invokes.

  5. Click OK.

8.4 Displaying Web Pages from a Fusion Web Application

You configure a Dialog action in an action set to display pages from the Fusion web application with which you integrate your Excel workbook. These pages provide additional functionality for your integrated Excel workbook. Examples of additional functionality that you can provide include search dialogs and display pick dialogs that interact with your Fusion web application. You can also configure upload options.

The Dialog action in an action set can be configured to display in one of the following two types of dialog:

  • Popup dialog

  • Excel's Document Actions

The value for the Dialog.Target property (Popup or TaskPane) of the component's action set determines where a web page is rendered.

The value for the Dialog.Page property specifies the web page to display when the action is invoked. Valid values include a URL relative to the value of the WebAppRoot property or an absolute URL. For example, the EditPriceList.xlsx workbook in the Master Price List module specifies the following relative URL as a value for the page to invoke when a user clicks the Advanced Search button at runtime:

/faces/secured/excelAdvSearch.jspx

Absolute URLs such as the following are also valid:

http://www.oracle.com/technology/products/middleware/index.html

Note:

The Dialog.Page property does not accept EL expressions

8.4.1 How to Display a Web Page in a Popup Dialog

You can configure a Dialog action in an action set to invoke a web page from your Fusion web application in a modal popup dialog hosted by Excel's web browser control. This feature provides end users with functionality that allows them to, for example, input values displayed by a page from the Fusion web application into the integrated Excel workbook.

The web page that the action set invokes must contain a reserved HTML Document Object Model (DOM) span element that has an ID attribute set to ADFdi_CloseWindow. Example 8-1 shows how you can automatically set the value of the span element in the excelAdvSearch.jspx page of the Master Price List module using the rendered property of the f:verbatim tag.

Example 8-1 Use of HTML Document Object Model Span Element

<f:verbatim rendered="#{requestScope.searchAction eq 'search'}">
                <span id="ADFdi_CloseWindow">Continue</span>
        </f:verbatim>
<f:verbatim rendered="#{requestScope.searchAction eq 'cancel'}">
                <span id="ADFdi_CloseWindow">Abort</span>
        </f:verbatim>

Figure 8-13 shows the excelAdvSearch.jspx page hosted by the EditPriceList.xlsx workbook's browser control.

Figure 8-13 Advanced Search Popup Dialog

Advanced Search Popup Dialog

In scenarios where you cannot use the rendered property of the f:verbatim tag as outlined in Example 8-1, you may need to:

  1. Create a backing bean that exposes the Dialog action's result value as a property

  2. Use an action listener to invoke the backing bean and an EL expression in the span element to set the value ADFdi_CloseWindow

Whatever approach you take, the Oracle ADF Desktop Integration module monitors the value of ADFdi_CloseWindow to determine when to close the popup dialog. If ADFdi_CloseWindow references:

  • An empty string or is not present, the popup dialog cannot close

  • "Continue", the popup dialog closes and the action set invokes its next action

    The following example shows ADFdi_CloseWindow referencing "Continue":

    var closeWindowSpan = document.getElementById("ADFDI_CloseWindow");

    closeWindowSpan.innerHTML = "Continue";

  • Some other string value, the popup dialog closes and the action set ceases execution

You set the Target property for a Dialog action to Popup to display a web page from the Fusion web application in a modal popup dialog hosted by Excel's web browser control. Displaying a web page in a modal popup dialog differs from displaying a web page in Excel's Document Actions, as the Dialog action that the action set invokes cannot continue execution until it receives user input. While the popup dialog is open, the end user cannot interact with any other part of the integrated Excel workbook, as the popup dialog retains focus.

End users can navigate between multiple web pages from the Fusion web application within the browser control until they or the Oracle ADF Desktop Integration module close the browser control.

To immediately synchronize the changes that an end user makes to a data control through a popup dialog, specify the next action in the action set after the Dialog action to download all modified bindings to the worksheet (use the DownSync worksheet action) or ADF Table component (use the Download action). This scenario assumes that you specify "Continue" as the value for ADFdi_CloseWindow.

Note:

If you configure the web page that appears in the popup dialog so that an end user can download an integrated Excel workbook, the Oracle ADF functionality in the integrated Excel workbook is disabled when the end user opens the workbook after download.

8.4.2 How to Display a Web Page in Excel's Document Actions

You set the Dialog.Target property for an action to TaskPane to display a web page specified by the Dialog.Page property in Excel's Document Actions. In contrast to displaying a web page in a popup dialog, displaying a web page in Excel's Document Actions allows an action set to continue executing actions while the web page displays. End users can access and interact with other parts of the integrated Excel workbook while the web page displays.

Note the following if you set the Target property of a Dialog action to TaskPane:

  • The Oracle ADF Desktop Integration module ignores the value of ADFdi_CloseWindow (and other span elements).

  • You cannot configure the worksheet Shutdown event to invoke the Dialog action.

8.4.3 What You May Need to Know About Displaying Pages from a Fusion Web Application

You can keep the data an integrated Excel workbook contains synchronized with a Fusion web application by specifying additional actions in the action set that invokes the Dialog action. You can make sure that the Fusion web application page and the integrated Excel worksheet both use the same data control frame by setting the ShareFrame property of the Dialog action.

Keeping an Integrated Excel Workbook and a Fusion Web Application Synchronized

To make sure that data in the integrated Excel workbook and the Fusion web application remains synchronized while end users use pages from the Fusion web application, configure the action set that invokes the Dialog action to:

  • Upload changes from the integrated Excel workbook to the Fusion web application before invoking the Dialog action

    Invoke the UpSync worksheet action to synchronize changes from the ADF Input Text and ADF Desktop Integration List of Values components in the worksheet. Invoke the ADF Table component's Upload action to upload changes from the ADF Table component.

  • Download changes from the Fusion web application to the integrated Excel workbook after invoking the Dialog action

    Invoke the DownSync worksheet action to download changes from the Fusion web application to ADF Input Text and ADF Desktop Integration List of Values components in the worksheet. Invoke the ADF Table component's Download action to download changes from the Fusion web application to the ADF Table component.

For more information about synchronizing data between an integrated Excel workbook and a Fusion web application, see Chapter 15, "Using an Integrated Excel Workbook Across Multiple Web Sessions and in Disconnected Mode". For information about worksheet actions and ADF Table component actions, see Chapter A, "Oracle ADF Desktop Integration Component Properties and Actions".

Sharing Data Control Frames Between Integrated Excel Worksheets and Fusion Web Application Pages

Fusion web applications and integrated Excel workbooks both use data control frames to manage the transactions and state of view objects and, by extension, the bindings exposed in a page definition file. When you invoke a Fusion web application's page from an integrated Excel worksheet, you can make sure that the page and the integrated Excel worksheet both use the same data control frame by setting the ShareFrame property of the Dialog action that invokes the page to True.

The Page property in the Dialog action specifies the page that the Dialog action invokes. If the Dialog action invokes an absolute URL or a page that is not part of your Fusion web application, the Oracle ADF Desktop Integration module ignores the value of ShareFrame if ShareFrame is set to True.

Set ShareFrame to False in the following scenarios:

  • The Dialog.Page property in the action set references an absolute URL or a page that is not part of your Fusion web application.

  • The Dialog.Page property in the action set references a page that is part of your Fusion web application, but that does not need to share information with the integrated Excel worksheet. For example, a page that displays online help information.

For more information about data control frames in a Fusion web application, see the "Sharing Data Control Instances" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

8.5 Inserting Values in an ADF Table Component from a Web Page Pick Dialog

You can configure the DoubleClickActionSet of an ADF Table component's column to invoke a Fusion web application page that renders a pick dialog where an end user selects a value to insert in the ADF Table component column.

This functionality is useful when you want to constrain the values that end users can enter in an ADF Table component. For example, you may want a runtime ADF Table component column to be read-only in the Excel worksheet so that end users cannot manually modify values to prevent them from introducing errors. Invoking a pick dialog rendered by a Fusion web application page allows the end user to change values in the ADF Table component without entering incorrect data.

In addition to configuring the DoubleClickActionSet, you configure the ADF Table component's RowData.CachedAttributes property to reference attribute binding values if you want:

  • End users to modify values in the Fusion web application's page that you do not want to appear in the ADF Table component of the integrated Excel workbook

  • An ADF Table component's column to be read-only in the integrated Excel workbook

  • Cache data in an ADF Table component over one or more user sessions that is not visible to end users but is modified by a pick dialog

    For example, an ADF Table component displays a list of product names to end users. A pick dialog is invoked that refreshes the list of product names in the ADF Table component and, as part of the process, sets the value of product IDs. In this scenario, you specify the attribute binding value for the product ID in the ADF Table component's RowData.CachedAttributes property. After the action set executes, the ADF Table component displays the refreshed list of product names in the rows of the Excel worksheet and references the associated product IDs in its RowData.CachedAttributes property.

For information about populating values in the pick dialog, see the "Creating Databound Selection Lists and Shuttles" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

To invoke a pick dialog from an ADF Table component:

  1. Select the cell in the Excel worksheet that anchors the ADF Table component and click the property inspector button in the Oracle ADF Desktop Integration Designer toolbar to display the property inspector.

  2. Configure the ADF Table component's RowData.CachedAttributes property to reference attribute binding values.

  3. Click the ellipsis button (...) beside the input field for Columns to display the TableColumn Collection Editor.

  4. In the Members list select the column from which an end user will invoke the pick dialog at runtime.

  5. Configure the DoubleClickActionSet of the UpdateComponent property as described in the following table.

    Add this action... To...
    ADFmAction (Optional) Invoke the CreateInsert action binding if the end user invokes the DoubleClickActionSet from a newly created row in the Excel worksheet's ADF Table component. In this scenario, the ADF Table component's RowUpSync action (invoked in the next action) fails if the Fusion web application does not contain a placeholder row.
    Component Invoke the ADF Table component's RowUpSync action to synchronize any pending changes in the current row of the ADF Table component to the Fusion web application.
    Dialog Configure the Dialog action to invoke the pick dialog page from the Fusion web application. Set the Dialog action's ShareFrame property to True. For more information, see Section 8.4, "Displaying Web Pages from a Fusion Web Application".
    Component Invoke the ADF Table component's RowDownSync action to synchronize data from the row in the ADF Table component's iterator in the Fusion web application that corresponds to the current ADF Table component row in the worksheet.

  6. Click OK.

8.6 Creating ADF Databound Search Forms in an Integrated Excel Workbook

You can create forms in your integrated Excel workbooks using ADF Input Text and ADF Button components. End users can use the forms you create to insert data or query for information. This section uses the latter example to demonstrate how you create forms.

End users can enter a search term in the ADF Input Text component and retrieve matching results by clicking an ADF Button component. If you want to present a more sophisticated user interface to end users for a search operation, you can invoke search forms from your Fusion web application. Results from these search operations can be downloaded to the ADF Table or ADF Read-only Table components in your integrated Excel workbook.

Figure 8-14 shows a design time view of the Oracle ADF components that the EditPriceList.xlsx workbook in the Master Price List module uses to configure search options where:

  1. ADF Label component is used in a simple search form

  2. ADF Input Text component is used in a simple search form

  3. ADF Button component is used in a simple search form

  4. ADF Button component is used to invoke an advanced search form

Figure 8-14 Oracle ADF Components Used for Search in the EditPriceList.xlsx Workbook

ADF components used for search in EditPriceList.xlsx

Note:

The Oracle ADF Desktop Integration module does not support usage of the FindMode attribute in page definition files. For more information about the FindMode attribute, see the "pageNamePageDef.xml" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

8.6.1 How to Create a Simple Search Form in an Integrated Excel Workbook

You insert an ADF Input Text component and configure it so that an end user can enter a search term. Insert an ADF Button component and configure its action set to:

  1. Take the value the end user enters in the ADF Input Text component.

  2. Query for the value.

  3. Download the results to an ADF Table or ADF Read-only Table component in the integrated Excel workbook.

To create a simple search form in an integrated Excel workbook:

  1. Insert an ADF Input Text component in the Excel worksheet cell where you want the end user to enter the search criteria.

  2. Configure the ADF Input Text component so that it assigns the search term a user enters to an attribute binding.

    Figure 8-15 shows an example from the EditPriceList.xlsx workbook in the Master Price List module where an ADF Input Text component assigns the value the end user enters to the searchTerm attribute binding.

    Figure 8-15 ADF Input Text Component for a Simple Search Form

    ADF Input Text component for quick query search form

  3. Optionally, apply a style to the ADF Input Text component to indicate to end users that they can enter a search term in the cell.

  4. Optionally, create an ADF Label component in an adjoining cell to indicate to end users that they can enter a search term in the ADF Input Text component you created in Step 1.

  5. Create an ADF Button component in the Excel worksheet.

  6. Set the Label property of the ADF Button component so that it displays a string at runtime to indicate to end users that they can start a search operation by clicking the button.

  7. Open the Action Collection Editor to configure the array of actions (Action[]Array) in the ClickActionSet properties of the ADF Button component. The following table describes the actions to invoke in sequence.

    Add this action... To...
    Worksheet Invoke the UpSync worksheet action to copy the value entered in the cell that hosts an ADF Input Text or ADF Desktop Integration List of Values component to the Fusion web application. For more information about worksheet actions, see Section A.12, "Worksheet Actions and Properties".
    ADFmAction Invoke an ADFm action that is bound to the attribute binding you specified in Step 2. The ADFm action queries for the end user's search term value referenced by the attribute binding.

    The corresponding example in the EditPriceList.xlsx workbook is the executeSimpleProductQuery action binding, which is bound to the searchTerm attribute binding.
    Worksheet Invoke the DownSync worksheet action to synchronize any pending changes from the ­Fusion web application to the ADF Input Text, ADF Output Text, and ADF Desktop Integration List of Values components in the worksheet. For more information about worksheet actions, see Section A.12, "Worksheet Actions and Properties".
    Component Invoke a Download action from the ADF Table or ADF Read-only Table components to download the results that the ADFm action retrieved.

  8. Click OK.

    Figure 8-16 shows an example from the EditPriceList.xlsx workbook in the Master Price List module where an ADF Button component invokes the executeSimpleProductQuery action binding using the search term an end user entered in the ADF Input Text component.

    Figure 8-16 ADF Button Component for Simple Search Form

    ADF Button component for quick query search form

8.6.2 How to Create an Advanced Search Form in an Integrated Excel Workbook

You use the ADF Button component to invoke a page from the Fusion web application that displays a search form to the end user. Configure the action set for the ADF Button component to invoke the Download action for the ADF Table or ADF Read-only Table component so that the search results from the search operation are downloaded to the integrated Excel workbook.

For information about creating a search form in a Fusion web application, see the "Creating ADF Databound Search Forms" chapter in the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

To invoke an advanced search form in an integrated Excel workbook:

  1. Create an ADF Button component in the Excel worksheet.

  2. Set the Label property of the ADF Button component so that it displays a string at runtime to indicate to end users that they can start a search operation by clicking the button.

  3. Use the Action Collection Editor to configure the array of actions (Action[]Array) in the ClickActionSet properties of the ADF Button component. Table 8-1 describes the actions to invoke in sequence.

    Table 8-1 Actions to Invoke an Advanced Search Form

    Add this action... To...

    Dialog

    Display the page from your Fusion web application that contains the search form. For more information about displaying pages from a Fusion web application, see Section 8.4, "Displaying Web Pages from a Fusion Web Application".

    ADFmAction

    Invoke an ADFm action to query for the values that the end user entered in the search form.

    Component

    Invoke a Download action from the ADF Table or ADF Read-only Table components to download the results that the ADFm action retrieved.

  4. Click OK.

    Figure 8-17 shows an example from the EditPriceList.xlsx workbook in the Master Price List module where an ADF Button component invokes the Execute action binding to retrieve the values specified by the end user in the Master Price List's module Search page (excelAdvSearch.jspx). The ADF Table component's Download action downloads the returned values to the integrated Excel workbook.

    Figure 8-17 ADF Button Component for an Advanced Search Form

    ADF Button component for query search form

8.7 Adding a Form to an Integrated Excel Workbook

The Oracle ADF Desktop Integration components described in Chapter 6, "Working with Oracle ADF Desktop Integration Form-type Components" can be used to create forms in your integrated Excel workbook. These components can be useful when you want to provide end users with functionality that allows them to view and edit individual fields rather than use the functionality provided by the table-type components to download rows of data from the Fusion web application. Use one or more of the following components to create a form:

  • ADF Button

    Use this component to provide end users with a button that can invoke a ClickActionSet. Figure 8-18 shows an ADF Button labeled Search that invokes a search operation using the search term entered by the end user in the ADF Input Text component.

  • ADF Input Text

    Use this component to provide end users with a read/write field where the current value of a binding appears. This component can also be used to input a value, as in the example illustrated in Figure 8-18, where users enter a search term in the ADF Input Text component.

  • ADF Output Text

    Use this component to provide end users with a read-only field where the current value of a binding appears.

  • ADF Desktop Integration List of Values

    Use this component to provide end users with a dropdown menu from which a user can select a value from a list binding.

  • ADF Label

    Use this component to provide end users with instructions or other information on how to use the form you create. For example, the Master Price List module's EditPriceList.xlsx workbook uses ADF Label components to display an instruction to end users (Search For:) and the number of matches for a search term. Figure 8-18 shows the runtime values of these components (Search For: and 8 matches).

Figure 8-18 Runtime View of a Form in an Integrated Excel Workbook

Runtime view of a form in an integrated Excel workbook

You use the Oracle ADF Desktop Integration Designer to insert the components you require into a worksheet.

To create a form in an integrated Excel workbook:

  1. Decide which ADF form components you require for the finalized form and insert them in the Excel worksheet.

    For more information about these components, see Chapter 6, "Working with Oracle ADF Desktop Integration Form-type Components".

  2. Configure the layout and appearance of the components you insert.

    For more information about configuring the appearance of components, see Chapter 9, "Configuring the Appearance of an Integrated Excel Workbook".

  3. Test your form.

    For more information about testing an integrated Excel workbook, see Chapter 13, "Testing Your Integrated Excel Workbook".

8.8 Creating a List of Values in an ADF Table Component Column

Use the ADF Desktop Integration Tree Node List component when you want to render a dropdown list of values in an ADF Table component column. The list of values can display a maximum of two hundred and fifty values at runtime. Unlike other Oracle ADF Desktop Integration module components, the ADF Desktop Integration Tree Node List component does not appear in the components palette described in Section 5.3, "Using the Components Palette". Instead, you invoke it as a subcomponent when you specify values for the InsertComponent or UpdateComponent properties of an ADF Table component column. For information about the properties of an ADF Table component column, see Section A.8.2, "ADF Table Component Column Properties".

Once you invoke the ADF Desktop Integration Tree Node List component as a subcomponent, you must specify a tree binding attribute associated with a model-driven list as a value for the ADF Desktop Integration Tree Node List component's List property. The tree binding attribute associated with a model-driven list populates the dropdown menu in the Table component's column with a list of values after invocation of the Table component's Download action.

For more information about creating a model-driven list, see the "How to Create a Model-Driven List" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. For information about the properties of an ADF Desktop Integration Tree Node List component, see Section A.6, "ADF Desktop Integration Tree Node List Component Properties".

Figure 8-19 shows the property inspector for an ADF Table component column after TreeNodeList was selected as the subcomponent for the column's UpdateComponent property.

Figure 8-19 ADF Table Component Column Configured to Display a List of Values

Table Component Column to Display a List of Values

8.8.1 How to Create a List of Values in an ADF Table Component Column

You add a column to the ADF Table component column and select TreeNodeList as the subcomponent. You then specify a tree binding attribute as the value for the TreeNodeList subcomponent's List property. A model-driven list must be associated with the tree binding attribute that you specify.

To create a list of values in an ADF Table component column:

  1. Select the cell in the Excel worksheet that references the ADF Table component and click the property inspector button in the Oracle ADF Desktop Integration Designer toolbar to display the Edit Component: ADF Table dialog box.

  2. Click the ellipsis button (...) beside the input field for Columns to invoke the TableColumn Collection Editor.

  3. Click Add to add a new column.

  4. Choose the appropriate option for the newly created column:

    • Click the ellipsis button (...) beside the input field for InsertComponent if you want to configure the runtime list of values for insert operations.

    • Click the ellipsis button (...) beside the input field for UpdateComponent if you want to configure the runtime list of values for update and download operations.

    In both options, the Select subcomponent to create dialog appears.

  5. Select TreeNodeList and click OK.

  6. Expand the property that you selected in Step 4 and configure values as follows:

  7. Click OK.

8.8.2 What Happens at Runtime When a Column Renders a List of Values

At runtime, the ADF Table component invokes the Download action and populates each column. This action also populates the list of values in the column that you configure to render a list of values. Figure 8-20 shows an example where StateId is the column configured to display a list of values.

Figure 8-20 Runtime View of an ADF Table Component Column Displaying a List of Values

Table Component Column Displaying a List of Values

8.9 Creating Dependent Lists of Values in an Integrated Excel Workbook

The Oracle ADF Desktop Integration module provides the following components that you use to create lists of values in an integrated Excel workbook:

  • ADF Desktop Integration List of Values

    You configure properties for this component when you want to create a list of values in the Excel worksheet.

  • ADF Desktop Integration Tree Node List component

    You configure properties for this component when you want to create a list of values in an ADF Table component column.

Using the previously listed components, you can create a dependent list of values in your integrated Excel workbook. A dependent list of values is where the value selected in one of the previously listed components (referred to here as a parent list of values) determines the values that appear in a second list of values component (referred to here as a child list of values).

Table 8-2 describes the dependent list of values implementations you can create using the previously listed components and the requirements to achieve each implementation.

Some of the implementations described in Table 8-2 require model-driven lists. For information about creating a model-driven list, see the "How to Create a Model-Driven List" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

Table 8-2 Dependent List of Values Configuration Options

Configuration Requirements

Render both the parent and child list of values in the Excel worksheet using ADF Desktop Integration List of Values components.

Both instances of the ADF Desktop Integration List of Values component must reference a list binding. One or both of the list bindings that you reference can be model-driven lists.

Both list bindings can reference model-driven lists only if the underlying iterator has at least one row of data. At runtime, if the underlying iterator has zero rows of data and an end user selects a value from the parent list of values (list binding referenced by the ADF Desktop Integration List of Values component's DependsOnListID property), the child list of values (list binding referenced by the ADF Desktop Integration List of Values component's ListID property) does not get filtered based on the value the end user selects.

To work around this scenario, choose one of the following options:

  • Make sure that the underlying iterator has at least one row of data

  • Use an alternative list binding configuration where you expose more than one iterator and all necessary iterators get refreshed

For more information, see Section 8.9.1, "How to Create a Dependent List of Values in an Excel Worksheet".

Render both the parent and child list of values in ADF Table component columns using ADF Desktop Integration Tree Node List components.

Both the parent and child list of values (ADF Desktop Integration Tree Node List components) must reference tree binding attributes associated with model-driven lists.

For more information, see Section 8.9.3, "How to Create a Dependent List of Values in an ADF Table Component's Columns".

Render the parent list of values in an ADF Desktop Integration List of Values component and the child list of values in an ADF Table component column using the ADF Desktop Integration Tree Node List component.

The child list of values (ADF Desktop Integration Tree Node List component) must reference a tree binding attribute associated with a model-driven list. The parent list of values (ADF Desktop Integration List of Values component) must reference a list binding.

For more information, see Section 8.9.5, "Creating a Dependent List of Values in an Excel Worksheet and an ADF Table Component Column".

Note the following points if you plan to create a dependent list of values:

  • When the cell value referenced by DependsOnList or DependsOnListID is changed, the Oracle ADF Desktop Integration module overrides any previous changes to the child component list of values without warning the end user.

  • The dependent list of values will not work if either the DependsOnList or DependsOnListID properties reference a component not present in the Excel worksheet.

  • If a circular dependency is defined (List A depends on List B, and List B depends on List A), the first dependency (List A depends on List B) triggers the expected behavior. The Oracle ADF Desktop Integration module considers other dependencies to be misconfigurations.

  • You can create a chain of dependencies as follows:

    • List A depends on List B

    • List B depends on List C

    In this scenario, a change to List C updates both Lists A and B.

  • Caching in a dependent list of values is discussed in Section 15.4, "Caching Lists of Values for Use in Disconnected Mode".

8.9.1 How to Create a Dependent List of Values in an Excel Worksheet

Use two instances of the ADF Desktop Integration List of Values component to create a dependent list of values in an Excel worksheet.

Specify the list binding referenced by the parent ADF Desktop Integration List of Values component as a value for the child ADF Desktop Integration List of Values component's ListOfValues.DependsOnListID property.

For more information about ADF Desktop Integration List of Values, see Section A.5, "ADF Desktop Integration List of Values Component Properties".

To create a dependent list of values in an Excel worksheet:

  1. If not already present, add the required list bindings to your page definition file.

    For more information about adding bindings to page definition files, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook".

  2. Insert two ADF Desktop Integration List of Values components into your integrated Excel workbook, as described in Section 6.6, "Inserting an ADF Desktop Integration List of Values Component".

  3. Display the property inspector for the ADF Desktop Integration List of Values component that is to serve as the parent in the dependent list of values and set the value of the ListOfValues.ListID property to the list binding that is the parent.

  4. Display the property inspector for the ADF Desktop Integration List of Values component that is to serve as the child in the dependent list of values and set its values as follows:

    • ListOfValues.ListID

      Specify the list binding that is the child in the dependent list of values.

    • ListOfValues.DependsOnListID

      Select the list binding that you specified for the ADF Desktop Integration List of Values component that serves as a parent in Step 3.

      Figure 8-21 shows the property inspector for the child ADF Desktop Integration List of Values where the CountryId list binding is specified as the parent list of values.

      Figure 8-21 Design Time Dependent List of Values in an Excel Worksheet

      Design Time Dependent List of Values in an Excel Worksheet

  5. Click OK.

8.9.2 What Happens at Runtime When a Dependent List of Values Renders in an Excel Worksheet

At runtime, the Oracle ADF Desktop Integration module renders both instances of the ADF Desktop Integration List of Values component. When an end user selects a value from the parent list of values, it filters the list of values that appears in the child list of values.

Figure 8-22 shows an example where the value that an end user selects in the CountryId list of values results in the matching state names appearing in the StateId list of values.

Figure 8-22 Runtime Dependent List of Values in an Excel Worksheet

Runtime Dependent List of Values in an Excel Worksheet

8.9.3 How to Create a Dependent List of Values in an ADF Table Component's Columns

Use instances of the ADF Desktop Integration Tree Node List component to render both lists of values in a dependent list of values in ADF Table component columns at runtime.

Specify a tree binding attribute as a value for the parent ADF Desktop Integration Tree Node List component's List property. You also specify a tree binding attribute as a value for the child ADF Desktop Integration Tree Node List component's List property and the same tree binding attribute referenced by the parent ADF Desktop Integration Tree Node List component as a value for its DependsOnList property.

Make sure that both tree binding attributes are associated with model-driven lists before you add the tree binding to your page definition file. For information about creating a model-driven list, see the "How to Create a Model-Driven List" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. For information about adding a tree binding to your page definition file, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook".

For information about the ADF Desktop Integration Tree Node List component, see Section A.6, "ADF Desktop Integration Tree Node List Component Properties".

To create a dependent list of values in an ADF Table component:

  1. If not already present, insert an ADF Table component into your integrated Excel workbook.

    For more information, see Section 7.3, "Inserting an ADF Table Component into an Excel Worksheet".

  2. Display the property inspector for the ADF Table component and invoke the TableColumn Collection Editor by clicking the ellipsis button (...) beside the input field for TableColumn[] Array.

  3. Click Add to add a new column to the ADF Table component to serve as the parent list of values in the runtime dependent list of values.

  4. Choose the appropriate option for the newly created column:

    • Click the ellipsis button (...) beside the input field for InsertComponent if you want to configure the runtime list of values for insert operations.

    • Click the ellipsis button (...) beside the input field for UpdateComponent if you want to configure the runtime list of values for update and download operations.

    In both options, the Select subcomponent to create dialog appears.

  5. Select TreeNodeList and click OK.

  6. Expand the property that you selected in Step 4 and configure values as follows:

    • Select a tree binding attribute associated with a model-driven list for the List property.

    • Configure the ReadOnly property as desired.

  7. Click Add to add a new column to the ADF Table component to serve as the child list of values in the runtime dependent list of values.

  8. Choose the appropriate option for the newly created column:

    • Click the ellipsis button (...) beside the input field for InsertComponent if you want to configure the runtime list of values for insert operations.

    • Click the ellipsis button (...) beside the input field for UpdateComponent if you want to configure the runtime list of values for update and download operations.

    In both options, the Select subcomponent to create dialog appears.

  9. Select TreeNodeList and click OK.

  10. Expand the property that you selected in Step 8 and configure values as follows:

    • Select the same tree binding attribute that you selected in Step 6 as a value for the DependsOnList property.

    • Select a tree binding attribute associated with a model-driven list for the List property.

    • Configure the ReadOnly property as desired.

      Figure 8-23 shows the property inspector for a child ADF Desktop Integration Tree Node component where the CountryId tree binding attribute is specified as the parent list of values.

      Figure 8-23 Design Time Dependent List of Values in an ADF Table Component's Columns

      Design Time Dependent List of Values in Table Component

  11. Click OK.

8.9.4 What Happens at Runtime When a Dependent List of Values Renders in an ADF Table Component's Columns

At runtime, the ADF Table component renders both instances of the ADF Desktop Integration Tree Node List component in the columns that you configured to display these instances. When an end user selects a value in the column that is configured as the parent list of values, it filters the list of values that appears in the column that is configured as the child list of values.

Figure 8-24 shows an example where the value that an end user selects in the CountryId column list of values results in the corresponding values for state appearing in the StateId column list of values.

Figure 8-24 Runtime Dependent List of Values in an ADF Table Component's Columns

Runtime Dependent List of Values in Table Component Columns

8.9.5 Creating a Dependent List of Values in an Excel Worksheet and an ADF Table Component Column

Use an instance of the ADF Desktop Integration List of Values component and an instance of the ADF Desktop Integration Tree Node List component to create a dependent list of values where you render the

  • Parent list of values in the Excel worksheet

    An instance of the ADF Desktop Integration List of Values component renders the parent list of values in the Excel worksheet.

  • Child list of values in an ADF Table component column

    An instance of the ADF Desktop Integration Tree Node List component renders the child list of values in the ADF Table component column.

Specify a list binding as a value for the parent ADF Desktop Integration List of Values component's ListID property. You specify a tree binding attribute as a value for the child ADF Desktop Integration Tree Node List component's List property and the same list binding referenced by the parent ADF Desktop Integration List of Values component as a value for its DependsOnList property.

Make sure that the tree binding attribute is associated with a model-driven list before you add the tree binding to your page definition file. For information about creating a model-driven list, see the "How to Create a Model-Driven List" section of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework. For information about adding a list and tree binding to your page definition file, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook".

For more information about the ADF Desktop Integration List of Values, see Section A.5, "ADF Desktop Integration List of Values Component Properties" and for information about the ADF Desktop Integration Tree Node List component, see Section A.6, "ADF Desktop Integration Tree Node List Component Properties".

To create a dependent list of values in an Excel worksheet and an ADF Table component column:

  1. Insert an ADF Desktop Integration List of Values component into your integrated Excel workbook, as described in Section 6.6, "Inserting an ADF Desktop Integration List of Values Component".

  2. Display the property inspector for the ADF Desktop Integration List of Values component and set the value of the ListID property to the list binding that is to serve as the parent list of values in the dependent list of values.

  3. Click OK.

  4. Display the property inspector for the ADF Table component and invoke the TableColumn Collection Editor by clicking the ellipsis button (...) beside the input field for TableColumn[] Array.

  5. Click Add to add a new column to the ADF Table component to serve as the child list of values in the runtime dependent list of values.

  6. Choose the appropriate option for the newly created column:

    • Click the ellipsis button (...) beside the input field for InsertComponent if you want to configure the runtime list of values for insert operations.

    • Click the ellipsis button (...) beside the input field for UpdateComponent if you want to configure the runtime list of values for update and download operations.

    In both options, the Select subcomponent to create dialog appears.

  7. Select TreeNodeList and click OK.

  8. Expand the property that you selected in Step 6 and configure values as follows:

    • Select the same list binding that you specified as a value for the ADF Desktop Integration List of Values component's ListID property in Step 2 as a value for the DependsOnList property.

    • Select a tree binding attribute associated with a model-driven list for the List property.

    • Configure the ReadOnly property as desired.

  9. Click OK.

    Figure 8-25 shows the property inspector for a child ADF Desktop Integration Tree Node component where the countryList list binding is specified as the parent list of values.

    Figure 8-25 Design Time Dependent List of Values in an Excel Worksheet and an ADF Table Component's Column

    Design Time Dependent List of Values in Table and Worksheet

8.9.6 What Happens at Runtime When a Dependent List of Values Renders in an Excel Worksheet and an ADF Table Component Column

At runtime, the ADF Table component invokes the Download action and populates each column in the ADF Table component. This includes the column that references the ADF Desktop Integration Tree Node List component. The ADF Desktop Integration List of Values component renders the parent list of values.

The value that an end user selects in the parent list of values (rendered by the ADF Desktop Integration List of Values component) determines what values appear in the child list of values (rendered by the ADF Tree Node List component referenced by the ADF Table component column).

Figure 8-26 shows an example where the value that an end user selects in the CountryId list of values determines the list of values that appears the StateId column of the ADF Table component.

Figure 8-26 Runtime Dependent List of Values in an Excel Worksheet and an ADF Table Component's Column

Runtime Dependent List of Values in Table and Worksheet

8.10 Configuring a Cell to Display a Dynamically Generated Hyperlink

If you use the Excel HYPERLINK function in an EL expression, you must enclose the HYPERLINK function within an Excel T function if you want an Oracle ADF component, such as an ADF Output Text component, to display a hyperlink at runtime. You enclose the HYPERLINK function because the Oracle ADF Desktop Integration module interprets Excel formula. To work around this, you wrap the T function around the HYERLINK function so that the value of the HYPERLINK function is evaluated by the T function. The resulting value is inserted into the Excel cell that the ADF component references.

8.10.1 How to Configure a Cell to Display a Dynamically Generated Hyperlink

You write an EL expression that uses the Excel T function to evaluate the output of the Excel HYERLINK function. The following task illustrates how you configure an ADF Output Text component to display a hyperlink that, when clicked, invokes a search operation on the Oracle OTN Discussion Forum for Developer Tools using the value of the ProductName binding as the search term.

To configure a cell to display a dynamically generated hyperlink:

  1. Insert an ADF Output Text component into the Excel worksheet.

  2. Write an EL expression for the Value property of the ADF Output Text component.

    The EL expression that you write invokes the Excel HYPERLINK function and uses the Excel T function to evaluate the output. In our example, we entered the following EL expression for the Value property:

    =T("=HYPERLINK(""http://forums.oracle.com/forums/search.jspa?objID=c19&q=#{bindings.ProductName}"", ""#{bindings.ProductName}"")")

    Note:

    Excel requires that you write double quotes (for example, ""#{bindings.ProductName}"") in the EL expression so that it can evaluate the expression correctly.

  3. Click OK.

8.10.2 What Happens at Runtime When a Cell Displays Dynamically Generated Hyperlink

The Oracle ADF Desktop Integration module evaluates the EL expression that you write at runtime. In the following example, the Oracle ADF Desktop Integration module:

  • Retrieves the value of the ProductName binding

  • Inserts the value of the ProductName binding into a URL

  • Inserts the result into a hyerlinked cell that a user can click to invoke a search

Figure 8-27 shows the runtime view of the example configured in Section 8.10.1, "How to Configure a Cell to Display a Dynamically Generated Hyperlink", where Zune 30GB is the retrieved value of the ProductName binding. When an end user clicks the cell that hosts the ADF Output Text component, he or she invokes a search operation for Zune 30GB on the Oracle OTN Discussion Forum for Developer Tools.

Figure 8-27 ADF Output Text Component Configured to Display a Hyperlink

Output Text Component Configured to Display a Hyperlink

8.11 Passing Server Data Context from a Fusion Web Application Page to a Workbook

You can configure a page in your Fusion web application to pass its current server data context to an integrated Excel workbook when an end user downloads the workbook from the page. For example, if an end user attempts to download a workbook from a page that displays a list of products, the list of products that appears in the workbook will correspond to the list of products displayed in the page when the end user invoked the download. Subsequent changes that the end user makes to data in one location (the worksheet or the Fusion web application's page) do not affect data in the other location.

To configure this functionality, you must:

  • Verify that the HTTP filter is configured to allow end users to download integrated Excel workbooks from the Fusion web application. By default, JDeveloper configures the HTTP filter with appropriate values when you add Oracle ADF Desktop Integration to the technology scope of your Oracle ADF Desktop Integration project. To verify the parameter values of the HTTP filter, see Section E.2, "Configuring HTTP Filter Parameters".

  • Configure the page in your Fusion web application from which the end user downloads the integrated Excel workbook so that it passes its current server data context through URL arguments to the integrated Excel workbook when the end user downloads it.

  • Configure the page definition file associated with the worksheet in the integrated Excel workbook so that the worksheet is initialized with the server data context of the page in the Fusion web application from which the end user downloads the workbook.

  • Configure workbook and worksheet properties in the integrated Excel workbook that end users will download so that the workbook contains the server data context of the page in the Fusion web application from which the end invokes download.

8.11.1 How to Configure the Fusion Web Application's Page to Pass Server Data Context

You insert an <af:goLink> tag and specify property values for it that reference the integrated Excel workbook the end user downloads and the values to download. You also specify the commands on the page that, when invoked, require the Fusion web application to refresh the values referenced by the <af:goLink> tag and its property values.

To configure the page in the Fusion web application:

  1. In JDeveloper, insert the af:goLink tag into the page from which the end user downloads the integrated Excel workbook.

  2. In the Structure window, right-click the af:goLink node and choose Go to Properties.

  3. Expand the Common section and set values for the properties described in the following table.

    Property Value
    Text Write the text that appears to end users at runtime.

    For example, write text such as the following to appear at runtime:

    Download to Excel
    Destination Invoke the expression builder to write an EL expression that specifies the integrated Excel workbook and the values to download as a URL argument:

    For example, write an EL expression such as the following:

    "/excel/workbook.xlsx?productName=#{bindings.productName.attributeValue}"

    Note that the runtime URL-encoded value of the EL expression to the right of ? must be less than 2048 bytes. If the runtime value exceeds 2048 bytes, the integrated Excel workbook downloads the URL arguments in the first 2048 bytes. Subsequent URL arguments do not get downloaded to the integrated Excel workbook. Instead, the Fusion web application writes log entries for these URL arguments identifying them as having not been downloaded.

    For example, the runtime URL-encoded value of productName=#{bindings.productName.attributeValue} must be less than 2048 bytes.

  4. Optionally, expand the Behavior section and specify component IDs for the partialTriggers property that, when invoked, update the values of the af:goLink tag and its Destination property.

    For example, if you have navigation buttons with the IDs NextButton, PreviousButton, FirstButton, and LastButton, specify them as follows:

    :NextButton :PreviousButton :FirstButton :LastButton

  5. Save the page.

    The following example shows the entries that JDeveloper generates in a JSF page using the examples in this procedure:

    <af:goLink text="Download to Excel"
destination="/excel/workbook.xlsx?productName=#{bindings.productName.attributeValue}"
partialTriggers=":NextButton :PreviousButton :FirstButton :LastButton"/>

8.11.2 How to Configure the Page Definition File for the Worksheet to Receive Server Data Context

You configure the page definition file associated with the worksheet in the integrated Excel workbook as follows:

  • Add one or more parameter elements that initialize the worksheet with the values specified by the workbook Parameters property that you configure in Section 8.11.3, "How to Configure Properties in the Integrated Excel Workbook to Receive Server Data Context".

    The following example shows a parameter element in a page definition file that is associated with a worksheet in an integrated Excel workbook:

    <parameters>
    <parameter id="ProductNameParam" />
</parameters>

  • Add an invokeAction and a method action binding so that the page definition file associated with the worksheet initializes correctly.

    The following example shows the initializeProductTable invokeAction invoking the filterByProductName method action binding. The invokeAction is refreshed only when a value for ProductNameParam is supplied.

    <executables>
    <invokeAction Binds="filterByProductName" id="initializeProductTable"
                  Refresh="deferred"
                  RefreshCondition="${bindings.ProductNameParam != null}"/>
...
</executables>

    The method action binding invokes a view object method (filterByProductName). The view object method takes a single String argument (ProductNameArg) that references the value of ProductNameParam.

    <bindings>
    <methodAction id="filterByProductName" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="filterByProductName"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.ProductVO1">
      <NamedData NDName="ProductNameArg" NDValue="${bindings.ProductNameParam}"
                 NDType="java.lang.String"/>
</methodAction>
. . .
</bindings>

For more information about configuring a page definition file, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook".

8.11.3 How to Configure Properties in the Integrated Excel Workbook to Receive Server Data Context

You configure the workbook Parameters property and the worksheet Parameters property so that the integrated Excel workbook the end user downloads from the Fusion web application receives the current server data context of the page in the Fusion web application.

To configure the workbook Parameters property:

  1. Click Workbook Properties in the Oracle ADF Desktop Integration Designer.

  2. Click the ellipsis button (...) beside the input field for Parameters to invoke the WorkbookParameter Collection Editor.

  3. Click Add to add a new workbook initialization parameter and configure its properties as follows:

  4. Repeat Step 3 as necessary to add other workbook initialization parameters.

  5. Click OK.

    For more information about the workbook Parameters property, see Table A-17.

To configure the worksheet Parameters property:

  1. Click Worksheet Properties in the Oracle ADF Desktop Integration Designer.

  2. Click the ellipsis button (...) beside the input field for Parameters to invoke the WorksheetParameter Collection Editor.

  3. Click Add to add a new worksheet parameter and configure it as follows:

    • (Optional) In the Annotation field, enter a description of the worksheet parameter.

    • In the Parameter field, specify a parameter element that you added to the page definition file associated with the worksheet, as described in Section 8.11.2, "How to Configure the Page Definition File for the Worksheet to Receive Server Data Context".

    • In the Value field, write an EL expression that references the value of the Parameter property you specified for the workbook initialization parameter (workbook Parameters array). Use the following syntax when writing the EL expression:

      #{workbook.params.productName}

      where productName references the value of the Parameter property you specified for the workbook initialization parameter.

  4. Repeat Step 3 as necessary to add other workbook initialization parameters.

  5. Click OK.

    For more information about the worksheet Parameters property, see Table A-18.

8.12 Using Calculated Cells in an Integrated Excel Workbook

You can write Excel formulas that perform calculations on values in an integrated Excel workbook. Before you write an Excel formula that calculates values in an integrated Excel workbook, note the following points:

  • Formulas can be entered in cells that reference Oracle ADF bindings and cells that do not reference Oracle ADF bindings

  • End users of an integrated Excel workbook can enter formulas at runtime

  • You (author of an integrated Excel workbook) can enter formulas at design time

  • During invocation, the ADF Table component actions Upload and RowUpSync send the results of a formula calculation to the Fusion web application and not the formula itself

  • Excel recalculates formulas in cells that reference Oracle ADF bindings when these cells are modified by:

    • Invocation of the ADF Table component RowDownSync and Download actions

    • Rendering of Oracle ADF components

  • The ADF Table and ADF Read-only Table components insert or remove rows as they expand or contract to accommodate data downloaded from the Fusion web application. Formulas are replicated according to Excel's own rules.

  • You can enter formulas above or below a cell that references an ADF Table or ADF Read-only Table component. A formula that you enter below one of these components maintains its position relative to the component as the component expands or contracts to accommodate the number of rows displayed.

For more information about Excel functions, see the Function reference section in Excel's online help documentation.

8.12.1 How to Create a Column That Displays Values Generated by an Excel Formula

You insert a column that displays values calculated by an Excel formula directly into a worksheet using the menu options on Excel's Ribbon. You cannot add a column that displays calculated values using the collection editor that manages columns for an ADF Table or ADF Read-only Table component.

To create a column that displays values generated by an Excel formula:

  1. In design mode in the Excel worksheet, select the cell in which you want the column that displays the values generated by the Excel formula to appear at runtime.

    In this example, the workbook author inserted the following formula into the Excel worksheet cell G7:

    =F7-E7

    where F7 is the design time cell reference for the ADF Table component column labeled List Price at runtime and E7 is the design time cell reference for the ADF Table component column labeled Cost Price at runtime.

    The workbook author also inserted an ADF Label component into the Excel cell referenced G6. He or she set the value of the Label property to the following EL expression:

    #{res['excel.difference.label']}

    that retrieves the value of the excel.difference.label string key at runtime.

    Figure 8-28 shows the design time view of the manually inserted column, with the Excel formula appearing in the formula bar, and the ADF Label component that retrieves the string key value from the resource bundle at runtime.

    Figure 8-28 Design Time View of Column That Displays Values Generated by an Excel Formula

    Design-time View of Column Displaying Excel Formula Output

  2. Save your changes using Excel's Save button.

8.12.2 What Happens at Runtime When a Column Displays Values Generated by an Excel Formula

At runtime, Excel replicates and adjusts its formula as the ADF Table and ADF Read-only components expand or contract so that the correct value appears in each row of a manually inserted column. Figure 8-29 shows an extract of the runtime view of the example that appears in Figure 8-28 where Excel adjusted the formula so that it evaluates each corresponding row.

Figure 8-29 Runtime View of Column That Displays Values Generated by an Excel Formula

Design-time View of Column Displaying Excel Formula Output

8.12.3 How to Calculate the Sum of a Table-type Component Column

The following task illustrates how you use the Excel functions SUM and OFFSET to calculate the total of the column labeled Difference in the EditPriceList.xlsx of the Master Price List module at runtime. You use the OFFSET function in an Excel formula that you write where you want to reference a range of cells that expands or contracts based on the number of rows that an ADF Table or ADF Read-only Table component downloads. The SUM function calculates the total in a range of Excel cells.

To calculate the sum of a column in an ADF Table component:

  1. In design mode in the Excel worksheet, select the cell in which you will write the Excel formula. In our example, this is the cell with the reference, G8.

  2. Write the Excel formula that performs a calculation on a range of cells at runtime. For example, enter the following formula:

    =SUM(OFFSET(G6,1,0):OFFSET(G8,-1,0))

    where SUM calculates the total of values in the range of cells currently referenced by G6 and G8.

    Figure 8-30 shows the design time view of the Excel formula in the integrated Excel workbook.

    Figure 8-30 Design Time View of Excel Formula in an Integrated Excel Workbook

    Design-time View of Excel Formula in an Integrated Workbook

  3. Save your changes and switch to runtime mode to test that the Excel formula you entered evaluates correctly.

8.12.4 What Happens at Runtime When Excel Calculates the Sum of a Table-type Component Column

Figure 8-31 shows the runtime view in the integrated Excel workbook when the Excel formula shown in Figure 8-30 is evaluated. The Excel formula calculates the total of the values in the range of cells that you specified in design mode. The cell references that appear in Excel's formula bar at runtime (H6 and H54) differ from those that appear in the formula bar at design time (G5 and G7) because the ADF Table component has moved and expanded to include the rows of data that it downloads.

Figure 8-31 Runtime View of Excel Formula in an Integrated Excel Workbook

Runtime View of Excel Formula in Integrated Excel Workbook

8.13 Using Macros in an Integrated Excel Workbook

You can define and execute macros based on Excel events in an integrated Excel workbook.

Note the following points:

  • Macros triggered by an Excel event do not get triggered if the Excel event is invoked by the Oracle ADF Desktop Integration module.

  • Oracle ADF Desktop Integration module code invoked by an Excel event is executed when the Excel event is triggered by a macro.