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 About Adding Interactivity to an Integrated Excel Workbook

You can make your integrated Excel workbook interactive to the end user by using features such as action sets, configuring the runtime ribbon tab, creating dependent list of values, and so on. Figure 8-1 shows some of the interactive features.

Figure 8-1 Interactivity Features in an Integrated Excel Workbook

nteractivity Features in 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 ADF Desktop Integration components that invoke action sets, you can configure workbook and worksheet ribbon buttons that end users use at runtime to invoke action sets.

8.1.1 Adding Interactivity to Integrated Excel Workbook Use Cases and Examples

To make your integrated Excel workbook interactive, you can use action sets that are invoked by the end user's gestures. For example, as shown in Figure 8-2, the Search button of Search Area in AdvEditPriceList-DT.xlsx uses multiple actions sets to search and download data matching the search criteria.

Figure 8-2 Action Sets of Search Button

Action Sets of Search Button

Figure 8-3 shows an example of custom runtime ribbon tab implemented in EditPriceList.xlsx.

Figure 8-3 Runtime Ribbon Tab of EditPriceList.xlsx

Runtime Ribbon Tab of EditPriceList.xlsx

8.1.2 Additional Functionality for Adding Interactivity to an Integrated Excel Workbook

In addition to action sets and runtime ribbon tab, you can add additional functionality to configure your workbook. Following are links to other functionalities that you can use:

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

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 ADF Desktop Integration displays in property inspectors, and what user gesture can invoke an action set:

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

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

  • SelectActionSet for a worksheet ribbon button, as the end user selects a button 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 Edit Action dialog from an ADF component, worksheet ribbon button, 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 Edit Action dialog, see Section 5.12, "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 Upload Data at runtime in the EditPriceList-DT.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-4 which shows the Edit Action dialog in design mode.

Figure 8-4 Action Set for Upload Data Button in the EditPriceList-DT.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 Edit Action dialog. 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 ADF Desktop Integration generates.

Note:

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

8.2.1 How to Invoke a Method Action Binding in an Action Set

You can invoke multiple method action bindings in an action set. 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 Edit Action dialog to specify a method action binding to invoke.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To invoke a method action binding in an action set:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog and invoke the dropdown list from the Add button illustrated here.

    Add button to invoke Action Collection Editor

  3. 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 method action binding that the action set invokes.

    • 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.

  4. 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 ADF Desktop Integration 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 many other actions. More information about the actions for both components can be found in Appendix A, "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-5 shows the Choose Component Action dialog 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-5 Choose Component Method Dialog

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.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To invoke a component action from an action set:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog and invoke the dropdown list from the Add button illustrated here.

    Add button for ActionSets

  3. 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 and select the component action that the action set invokes at runtime. This populates the ComponentID and Method input fields.

    • Action

      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.

  4. 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.

8.2.3.1 Verifying an Action Set Invokes the Correct Component Action

When creating an action set, ensure that you invoke the component action from the correct instance of a component when a worksheet includes multiple instances of an ADF Read-only Table or ADF Table component. Figure 8-6 shows the Choose Component Action dialog 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-6 Choose Component Method Dialog

Choose Component Method Dialog Box

8.2.3.2 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 the 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

ADF Desktop Integration provides several 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-19. See Table A-14 for more information about action sets.

Use the Edit Events dialog to specify an action set to be invoked by a worksheet event.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To invoke an action set from a worksheet event:

  1. Open the integrated Excel workbook.

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

  3. In the Edit Worksheet Properties dialog, click the ellipsis button (...) beside the input field for the Events property.

  4. In the Edit Events dialog, click Add to add a new element that specifies an event and a corresponding action set that the event invokes.

    Figure 8-7 shows an example from the EditPriceList-DT.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-7 Worksheet Startup Event Invokes an Action Set

    Worksheet Startup event invokes an ActionSet

  5. 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 Edit Action dialog to configure values for the ActionSet.Status properties.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To display a status message:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog of the component.

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

    Table 8-1 ActionSet.Status Group of Properties

    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-DT.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-DT.xlsx file has the following value configured for the Title property:

    Query Products

    For this property...

    Enter or select this value...

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

    Figure 8-8 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-14.

  4. Click OK.

8.2.6 What Happens at Runtime: How the 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-9 shows the status message that appears at runtime when the action set configured for the Search button in the EditPriceList-DT.xlsx workbook executes.

Figure 8-9 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 the 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. 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 Edit Action dialog to configure values for the ActionSet.Alert group of properties.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To add an alert to an action set:

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog.

  3. Set values for the properties in the ActionSet.Alert group of properties as described in Table 8-2.

    Table 8-2 ActionSet.Alert Group of Properties

    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 if errors occur during execution of the action set. For example, the Upload to Server button in the Master Price List module's EditPriceList-DT.xlsx workbook has the following value configured for the FailureMessage property:

    #{components.TAB442758137.errors}

    The Upload to Server 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. 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 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-DT.xlsx workbook has the following value configured for the SuccessMessage property:

    Changes saved successfully

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

    Figure 8-10 Alert Message Properties in an Action Set

    Alert Message Properties in an ActionSet

  4. Click OK.

8.2.8 What Happens at Runtime: How the Action Set Provides an Alert

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

Figure 8-11 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

For information about how to invoke these editors, or about 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-13.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To configure error handling for an action set:

  1. Open the integrated Excel workbook.

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

    Table 8-3 ActionOptions Properties

    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 ADF Model action to invoke if an action set does not complete successfully. For example, you could specify an ADF Model action that rolls back changes made during the unsuccessful invocation of the action set.

    Note that calling an action set that changes a record set's currency during the execution of FailureActionID methods is not supported. The Rollback method also should not be specified as the FailureActionID in an action set.

    SuccessActionID

    Specify an ADF Model 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.

    Note that calling an action set that changes a record set's currency during the execution of SuccessActionID methods is not supported.

  3. Click OK.

8.2.10 How to Invoke a Confirmation Action in an Action Set

The Confirmation action presents the end user with a simple message dialog that displays the title and prompt message specified in the Confirmation action properties.

The execution of the action set pauses until the end user clicks one of the two buttons provided. If the user clicks OK, the action sets proceed with the remaining actions in the Action Set. If the user clicks Cancel, the action set is aborted at that point and the remaining actions are not invoked. As there is no error or success, the FailureActionID or SuccessActionID action is not invoked.

Before you begin:

It may be helpful to have an understanding of action sets. For more information, see Section 8.2, "Using Action Sets."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To invoke a Confirmation action from a component

  1. Open the integrated Excel workbook.

  2. Open the Edit Action dialog and click the down arrow in the Add button to open a dropdown list, as illustrated here.

    Add button for ActionSets

  3. Select Confirmation and configure its Data properties as described in the following list:

    • CancelButtonLabel

      Specify an EL expression or string that evaluates to a message to appear in the Cancel button of the dialog. The default EL expression is:

      #{_ADFDIres['DIALOGS_CANCEL_BUTTON_LABEL']}

    • OKButtonLabel

      Specify an EL expression or string that evaluates to a message to appear in the OK button of the dialog. The default EL expression is:

      #{_ADFDIres['DIALOGS_OK_BUTTON_LABEL']}

    • Prompt

      Specify an EL expression or string that evaluates to a message to appear as the prompt of the dialog. The default EL expression is:

      #{_ADFDIres['DIALOGS_ACTION_CONFIRM_PROMPT']}

    • Title

      Specify an EL expression or string that evaluates to a title of the confirmation dialog to display at runtime. The default EL expression is:

      #{_ADFDIres['DIALOGS_ACTION_TITLE']}

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

  5. Click OK.

Figure 8-12 shows the Edit Action dialog with default attribute values for a Delete button.

Figure 8-12 Confirmation Action Attributes

Confirmation Action dilaog with default attributes

8.2.11 What Happens at Runtime: How the Action Set Provides a Confirmation

Once the action set is invoked, the user is prompted with a confirmation dialog. If the user clicks OK, the next action operation is performed; and if the user clicks Cancel, the Action Set execution terminates without an error.

Note:

If the user cancels a Confirmation action, the FailureActionID binding does not run.

Figure 8-13 shows a default Confirmation dialog with OK and Cancel buttons.

Figure 8-13 Confirmation Dialog

Confirmation action dialog at runtime.

8.3 Configuring the Runtime Ribbon Tab

You can configure the runtime ribbon tab in the Excel Ribbon with items that invoke Oracle ADF functionality in your integrated Excel workbook. In the Runtime Ribbon Tab group, setting the Visible workbook property to True makes this tab appear at runtime. The Title property determines the title of the tab that the end user sees at runtime. By default, the title is MyWorkbook, as illustrated in Figure 8-14.

Figure 8-14 Workbook Properties for Runtime Ribbon Tab

Workbook Properties for Runtime Ribbon tab

At runtime, the tab appears as the last tab in the Ribbon and all your configured commands appear in various groups of the tab, as illustrated by Figure 8-15.

Figure 8-15 Runtime View of the Ribbon Tab

Runtime View of Oracle ADF tab

Figure 8-16 illustrates the runtime ribbon tab in EditPriceList.xlsx with two commands configured for worksheet. At runtime, the commands are divided into four groups: items that invoke commands on the workbook, items that invoke commands on the current worksheet, a command group to clear all data, and a command workgroup to display ADF Desktop Integration version information.

Figure 8-16 Runtime View of Ribbon Tab in EditPriceList.xlsx

Runtime view of EditPriceList.xlsx ADFdi tab

You configure the Workbook Commands property in the properties of the workbook so that the runtime ribbon tab contains commands that allow the end user to invoke workbook actions such as Login and Logout. You configure the Ribbon Commands property in the properties of the worksheet so that the ADF Desktop Integration tab contains items allowing a user to invoke an action set. Worksheet command items appear when the worksheet is active. If you remove a workbook command, it does not appear in the runtime tab for that workbook. If you remove all the commands for a given group, the group does not appear when that workbook is active.

Figure 8-17 shows the Worksheet group at runtime where the worksheet actions, that invoke SelectActionSet action sets, appear.

Figure 8-17 Runtime Worksheet Group

Runtime Worksheet group

8.3.1 How to Define a Workbook Command Button for the Runtime Ribbon Tab

To define a workbook command button for the runtime ribbon tab, you configure some workbook properties. The following procedure shows how to create or remove an item in the Workbook group by using the workbook action, Login, as an example.

Before you begin:

It may be helpful to have an understanding of the runtime ribbon tab in Excel. For more information, see Section 8.3, "Configuring the Runtime Ribbon Tab."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To define a workbook command button:

  1. Open the integrated Excel workbook.

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

  3. In the Edit Workbook Properties dialog, expand Runtime Ribbon Tab, and select Workbook Commands. Click the ellipsis button (...) beside the WorkbookMenuItem[] array to display the dialog as illustrated in Figure 8-18.

    Figure 8-18 Edit Workbook Commands Dialog

    The workbook menu item collection displays menu items.

  4. Click Add and specify values for the properties of the workbook command buttons as follows:

    • Method

      Specify the workbook action that you want the workbook command button 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 1024 characters. A runtime value that exceeds 1024 characters is truncated so that only 1024 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."

  5. Click OK.

Note:

The order of workbook commands in the Edit Workbook Commands dialog is ignored at runtime. The order and grouping of the workbook-level commands is always the same.

8.3.2 How to Configure a Worksheet Command for the Runtime Ribbon Tab

To define a worksheet command, you configure properties for the worksheet using the property inspector. By default, no command buttons are defined for the Worksheet group in the worksheet properties. You add members to the list that is referenced by the Ribbon Commands property in the properties of the worksheet.

CAUTION:

Set the Runtime Ribbon Tab.Visible workbook property to TRUE to display command buttons. If the Runtime Ribbon Tab.Visible is set to FALSE, no command buttons appear. For more information about workbook properties, see Table A-18.

Before you begin:

It may be helpful to have an understanding of the runtime ribbon tab in Excel. For more information, see Section 8.3, "Configuring the Runtime Ribbon Tab."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To define a worksheet command button:

  1. Open the integrated Excel workbook.

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

  3. In the Edit Worksheet Properties dialog, click the ellipsis button (...) beside the input field for the Ribbon Commands property to invoke the editor, as illustrated in Figure 8-19. Figure 8-17 displays how the commands appear at runtime.

    Figure 8-19 Edit Ribbon Commands Dialog

    WorksheetMenuItem Collection Editor

  4. Click Add to add a new ribbon button in the Members list of the collection editor.

  5. Configure the properties of SelectActionSet to specify the type of action(s) that the ribbon button invokes.

  6. Click OK.

Note:

At runtime, the worksheet commands appear in the same order as they are defined in the Edit Ribbon Commands dialog.

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

  • Runtime task pane

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-DT.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/technetwork/middleware/index.html

Note:

The Dialog action does not support ADF task flows.

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) element (for example, a span element) that has a case-sensitive 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-20 shows the excelAdvSearch.jspx page hosted by the EditPriceList-DT.xlsx workbook's browser control.

Figure 8-20 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 to the bean property value.

Whichever approach you take, ADF Desktop Integration 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 remains open.

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

    The following example shows ADFdi_CloseWindow assigned a value of "Continue":

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

    closeWindowSpan.innerHTML = "Continue";

  • Some other string value, the popup dialog remains open.

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 task pane, because 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 close the browser control, or ADF Desktop Integration closes it.

To immediately synchronize the changes that the 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.

Notes:

  • If you configure the web page that appears in the popup dialog so that the 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.

  • If you are using the HTML <select> components, such as list box or dropdown list, note that <select> components do not follow z-order configuration when the page is displayed through Dialog actions. In the .NET Web Browser control, on a web page with layered and overlapping components, the <select> components might appear on top of other components.

  • If the Title property is left blank, the web page's title will be used as the dialog's window title.

8.4.2 How to Display a Web Page in ADF Desktop Integration Runtime Task Pane

You set the Dialog.Target property for an action to TaskPane to display a web page specified by the Dialog.Page property in the ADF Desktop Integration task pane. In contrast to displaying a web page in a popup dialog, displaying a web page in the task pane 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, ADF Desktop Integration ignores the value of ADFdi_CloseWindow (and other elements.

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 ensure 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.

Notes:

8.4.3.1 Keeping an Integrated Excel Workbook and a Fusion Web Application Synchronized

To ensure 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:

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

    Invoke the RowUpSync worksheet action to synchronize changes from the current row in the ADF Table component.

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

    Invoke the RowDownSync worksheet action to send changes from the Fusion web application to the current row in the ADF Table component.

For DoubleClickActionSet, you must ensure that the server-side model is in the same state after executing the action set as it was before executing the action set. In most cases, it is sufficient to roll back any and all uncommitted changes at the end of each DoubleClickActionSet, as there are no pending uncommitted changes when the action set execution begins.

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, "ADF Desktop Integration Component Properties and Actions."

8.4.3.2 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 ensure 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, ADF Desktop Integration 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.4.3.3 Configuring a Fusion Web Application for ADF Desktop Integration Frame Sharing

When you add the ADF Desktop Integration feature to your Fusion web application, the application is automatically configured to support ADF Desktop Integration frame sharing. Frame sharing allows each worksheet of an integrated Excel workbook to use a dedicated DataControl frame. Web pages displayed in dialogs invoked from each worksheet can then share the same DataControl frame as the integrated Excel worksheet.

To verify that your Fusion web application is configured to support frame sharing:

  1. Open your Fusion web application project in JDeveloper.

  2. In the Application Navigator, expand the Application Resources panel.

  3. Open the adf-config.xml file available in Descriptors > ADF META-INF folder.

  4. Click the Source tab to open the source editor.

  5. Confirm that the following adf-desktopintegration-servlet-config element is present in the file before the </adf-config> tag:

    <adf-desktopintegration-servlet-config xmlns="http://xmlns.oracle.com/adf/desktopintegration/servlet/config"> 
    <controller-state-manager-class>
       oracle.adf.desktopintegration.controller.impl.ADFcControllerStateManager
    </controller-state-manager-class>
</adf-desktopintegration-servlet-config>

  6. Save the adf-config.xml file and close JDeveloper.

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

You can configure the DoubleClickActionSet of an ADF Table component's column subcomponent (UpdateComponent or InsertComponent) to invoke a Fusion web application page that renders a pick dialog where the 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 may 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. Open the integrated Excel workbook.

  2. Select the cell in the Excel worksheet that anchors the ADF Table component and click the Edit Properties button in the Oracle ADF tab to display the property inspector.

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

  4. Click the ellipsis button (...) beside the input field for Columns to display the Edit Columns dialog.

  5. In the Members list, select the column from which the end user invokes the pick dialog at runtime.

  6. Configure the Actions attribute of DoubleClickActionSet of the column subcomponent (UpdateComponent or InsertComponent), as described in Table 8-4.

    Table 8-4 DoubleClickActionSet Properties

    Add this action... To...

    ADFmAction

    (InsertComponent only) 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.

    ComponentAction

    Invoke the ADF Table component's Table.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."

    ComponentAction

    Invoke the ADF Table component's Table.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.

    ADFmAction

    (InsertComponent only) If you added a CreateInsert action binding, you should also invoke the Delete action binding to remove the placeholder row.

    Figure 8-21 shows the Edit Action dialog of DoubleClickActionSet properties.

    Figure 8-21 DoubleClickActionSet Properties

    The figure illustrates properties of DoubleClickActionSet.

  7. 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. 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-22 shows a design time view of the Oracle ADF components that the EditPriceList-DT.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-22 Oracle ADF Components Used for Search in the EditPriceList-DT.xlsx Workbook

ADF components used for search in EditPriceList.xlsx

Note:

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

Before you begin:

It may be helpful to have an understanding of ADF databound search forms. For more information, see Section 8.6, "Creating ADF Databound Search Forms in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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

  1. Open the integrated Excel workbook.

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

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

    Figure 8-23 shows an example from the EditPriceList-DT.xlsx workbook in the Master Price List module where an ADF Input Text component assigns the user-entered value to the searchTerm attribute binding. The searchTerm, which is a part of variable iterator, is then passed as a NamedData argument to the executeSimpleProductQuery method.

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

    ADF Input Text component for quick query search form

  4. 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.

  5. 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 2.

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

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

  8. Open the Edit Action dialog to configure the array of actions (Action[]Array) in the ClickActionSet properties of the ADF Button component. Table 8-5 describes the actions to invoke in sequence.

    Table 8-5 ClickActionSet Properties of the ADF Button Component

    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 List of Values component to the Fusion web application. For more information about worksheet actions, see Section A.13, "Worksheet Actions and Properties."

    ADFmAction

    Invoke an ADF Model action that is bound to the attribute binding you specified in Step 3. The ADF Model action queries for the end user's search term value referenced by the attribute binding.

    The corresponding example in the EditPriceList-DT.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 List of Values components in the worksheet. For more information about worksheet actions, see Section A.13, "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 ADF Model action retrieved.

  9. Click OK.

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

    Figure 8-24 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.

Before you begin:

It may be helpful to have an understanding of ADF databound search forms. For more information, see Section 8.6, "Creating ADF Databound Search Forms in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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

  1. Open the integrated Excel workbook.

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

  3. 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.

  4. Use the Edit Action dialog to configure the array of actions (Action[]Array) in the ClickActionSet properties of the ADF Button component. Table 8-6 describes the actions to invoke in sequence.

    Table 8-6 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."

    Component

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

  5. Click OK.

    Figure 8-25 shows an example from the EditPriceList-DT.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-25 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

You can use the ADF Desktop Integration components described in Chapter 6, "Working with ADF Desktop Integration Form-Type Components," 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-26 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-26, 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 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-DT.xlsx workbook uses ADF Label components to display an instruction to end users and the number of matches for a search term. Figure 8-26 shows the runtime values of these components. The text Search For: is a label instructing end uses to enter the search string, and 8 records found label displays the number of records found matching the search string.

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

Runtime view of a form in an integrated Excel workbook

You use the ADF Desktop Integration task pane 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 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 Dependent Lists of Values in an Integrated Excel Workbook

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

  • ADF List of Values

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

  • TreeNodeList subcomponent

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

Using these two components, you can create a dependent list of values in your integrated Excel workbook. A dependent list of values is a list of values component (referred to as a child list of values) whose values are determined by another list of values component (referred to as a parent list of values).

The server-side list bindings must be defined such that when the selected item of the parent list of values is changed, the available child list of values items are updated properly. Figure 8-27 shows an example with two illustrations from the AdvEditPriceList-DT.xlsx file of Master Price List module, where the Sub-Category column (child list of values) changes when the value in the Category column (parent list of values) changes.

Figure 8-27 List of Values and Dependent List of Values in Master Price List Module

The figure illustrates LOV and DLOV columns.

Table 8-7 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-7 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-7 Dependent List of Values Configuration Options

Configuration Requirements

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

Both instances of the ADF 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 the end user selects a value from the parent list of values (list binding referenced by the ADF List of Values component's DependsOnListID property), the child list of values (list binding referenced by the ADF 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:

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

  • Use an alternative list binding configuration where you expose multiple iterators and all necessary iterators get refreshed

For more information, see Section 8.8.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 TreeNodeList subcomponents.

Both the parent and child list of values (TreeNodeList subcomponents) must reference tree binding attributes associated with model-driven lists.

For more information, see Section 8.8.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 List of Values component and the child list of values in an ADF Table component column using the TreeNodeList subcomponent.

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

For more information, see Section 8.8.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, ADF Desktop Integration overrides any previous changes to the child component list of values without warning the end user.

  • The dependent list of values does not work unless the list specified in the DependsOnList (or DependsOnListID) property is referenced by a component 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. ADF Desktop Integration 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 in List C (grandparent list of values) updates both Lists A (grandchild list of values) and B (child list of values). If you create a similar scenario, you must ensure that both the grandchild list of values and the child list of values, get refreshed whenever the parent list of values selection is changed. You can do this by specifying the two bind variables on the grandchild list of values to set up an implicit dependency between the view attributes. Another way is to declare explicit attribute dependencies between each of the view attributes that have model-driven lists configured. For example, specify that attribute A depends on attribute B and attribute C, and attribute B depends on attribute C.

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

  • ADF Desktop Integration caches the values that appear in a dependent list of values. Hence, the dependent list item values for a given parent list selection must remain constant across all rows of an ADF Table component.

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

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

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

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

Before you begin:

It may be helpful to have an understanding of dependent list of values. For more information, see Section 8.8, "Creating Dependent Lists of Values in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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

  1. If not 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. Open the integrated Excel workbook.

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

  4. Display the property inspector for the ADF 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.

  5. Display the property inspector for the ADF 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 List of Values component that serves as a parent in Step 4.

      Figure 8-28 shows the property inspector for the child ADF List of Values where the CountryId list binding is specified as the parent list of values and StateId list is the dependent list of values.

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

      Design Time Dependent List of Values in an Excel Worksheet

  6. Click OK.

8.8.2 What Happens at Runtime: How the Excel Worksheet Renders a Dependent List of Values

At runtime, ADF Desktop Integration renders both instances of the ADF List of Values component. When the end user selects a value from the parent list of values, the selected value determines the list of values in the child list.

Figure 8-29 shows an example where StateID, a dependent list value, displays only the states from the selected CountryId list value.

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

Runtime Dependent List of Values in an Excel Worksheet

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

Use instances of the TreeNodeList subcomponent 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 TreeNodeList subcomponent's List property. You also specify a tree binding attribute as a value for the child TreeNodeList subcomponent's List property and the same tree binding attribute referenced by the parent TreeNodeList subcomponent as a value for its DependsOnList property.

Ensure 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 TreeNodeList subcomponent, see Section A.6, "TreeNodeList Subcomponent Properties."

Before you begin:

It may be helpful to have an understanding of dependent list of values. For more information, see Section 8.8, "Creating Dependent Lists of Values in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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

  1. Open the integrated Excel workbook.

  2. If not present, insert an ADF Table component.

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

  3. Display the property inspector for the ADF Table component and invoke the Edit Columns dialog by clicking the ellipsis button (...) beside the input field for TableColumn[] Array.

  4. If not created, click Add to add a new column to serve as the parent list of values. For more information about creating a list of values, see Section 7.13, "Creating a List of Values in an ADF Table Component Column."

  5. Add a new column to the ADF Table component to serve as the child list of values in the runtime-dependent list of values. For more information about creating a list of values, see Section 7.13, "Creating a List of Values in an ADF Table Component Column."

  6. Specify the tree binding attribute of the parent list of values as a value for the DependsOnList property.

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

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

    Design Time Dependent List of Values in Table Component

  7. Click OK.

8.8.4 What Happens at Runtime: How the ADF Table Component Column Renders a Dependent List of Values

At runtime, the ADF Table component renders both instances of the TreeNodeList subcomponent in the columns that you configured to display these instances. When the end user selects a value from the parent list of values, the selected value determines the list of values in the child list.

Figure 8-31 shows an example where the value that the end user selects in the Category column list of values results in the corresponding values for sub-category appearing in the Sub-Category column list of values.

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

Runtime Dependent List of Values in Table Component Columns

Note:

If the child list and the parent list are bound to columns in the same ADF Table component, the child list items are changed for the current row only, when the end user changes the parent list selection.

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

Use an instance of the ADF List of Values component and an instance of the TreeNodeList subcomponent to create a dependent list of values where you render the parent and the child list of values.

  • Parent list of values in the Excel worksheet

    An instance of the ADF 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 TreeNodeList subcomponent renders the child list of values in the ADF Table component column.

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

Ensure 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 List of Values component, see Section A.5, "ADF List of Values Component Properties." For information about the TreeNodeList subcomponent, see Section A.6, "TreeNodeList Subcomponent Properties."

Before you begin:

It may be helpful to have an understanding of dependent list of values. For more information, see Section 8.8, "Creating Dependent Lists of Values in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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

  1. Open the integrated Excel workbook.

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

  3. Display the property inspector for the ADF 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.

  4. Click OK.

  5. Display the property inspector for the ADF Table component and invoke the Edit Columns dialog by clicking the ellipsis button (...) beside the input field for TableColumn[] Array.

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

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

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

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

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

  8. Select TreeNodeList and click OK.

  9. Expand the property that you selected in Step 7 and configure values as follows:

    • Select the same list binding that you specified as a value for the ADF List of Values component's ListID property in Step 3 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.

  10. Click OK.

    Figure 8-32 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-32 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.8.6 What Happens at Runtime: How the Excel Worksheet and the ADF Table Component Column Render a Dependent List of Values

At runtime, the ADF List of Values component renders the parent list of values and the ADF Table component renders the child list of values in the column that you configured to display the TreeNodeList subcomponent. When the end user selects a value from the parent list of values, the selected value determines the list of values in the child list.

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

Figure 8-33 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

Note:

When the parent list is bound to a cell in the worksheet and the child list is bound to an ADF Table Component column, the child list items are updated for all rows in the table when the end user changes the parent list selection.

8.9 Using EL Expression to Generate an Excel Formula

You can use an EL expression to generate an Excel formula as the value of an ADF component. For example, you can use an Excel HYPERLINK function in an EL expression. 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 ADF Desktop Integration interprets the 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. Use the following syntax when writing an EL expression that invokes the HYPERLINK Excel function:

=T("=HYPERLINK(""link_location"",""friendly_name"")")

For example, the following EL expression uses HYPERLINK function to navigate to http://www.oracle.com when end user clicks the component.

=T("=HYPERLINK(""http://www.oracle.com"", ""#{bindings.ProductId.inputValue}"")")

If you write an EL expression using the HYPERLINK function, it is recommended that you select the Locked checkbox in the Protection tab of the Format Cells dialog for the custom style that you apply to prevent error messages appearing.

8.9.1 How to Configure a Cell to Display a Hyperlink Using EL Expression

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.

Before you begin:

It may be helpful to have an understanding of dynamic hyperlink. For more information, see Section 8.9, "Using EL Expression to Generate an Excel Formula."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

To configure a cell to display a hyperlink using EL expression:

  1. Open the integrated Excel workbook.

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

  3. 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.

  4. Click OK.

8.9.2 What Happens at Runtime: How a Cell Displays a Hyperlink using an EL Expression

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

  • 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-34 shows the runtime view of the example configured in Section 8.9.1, "How to Configure a Cell to Display a Hyperlink Using EL Expression," where Zune 30GB is the retrieved value of the ProductName binding. When the 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-34 ADF Output Text Component Configured to Display a Hyperlink

Output Text Component Configured to Display a Hyperlink

8.10 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 (developer of the 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.10.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.

Before you begin:

It may be helpful to have an understanding of how to use calculated cells in an integrated Excel workbook. For more information, see Section 8.10, "Using Calculated Cells in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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.

    For example, the H15 cell of EditPriceList-DT.xlsx contains a formula:

    =G15-F15

    Cell G15 is the design time reference for the ADF Table component column labeled List Price at runtime, and F15 is the design time cell reference for the ADF Table component column labeled Cost Price at runtime.

    The H14 cell marks the header for the formula. It contains an ADF Label component with its Label property set to the following EL expression:

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

    The EL expression retrieves the value of the excel.difference.label string key at runtime.

    Figure 8-35 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-35 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.10.2 What Happens at Runtime: How Values of an Excel Formula Are Displayed in a Column

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-36 shows an extract of the runtime view of the example that appears in Figure 8-35 where Excel adjusted the formula so that it evaluates each corresponding row.

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

Design-time View of Column Displaying Excel Formula Output

8.10.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-DT.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.

Before you begin:

It may be helpful to have an understanding of how to use calculated cells in an integrated Excel workbook. For more information, see Section 8.10, "Using Calculated Cells in an Integrated Excel Workbook."

You may also find it helpful to understand functionality that can be added using other ADF Desktop Integration features. For more information, see Section 8.1.2, "Additional Functionality for Adding Interactivity to an Integrated Excel Workbook."

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 want to write the Excel formula. In EditPriceList-DT.xlsx, this is the cell with the reference, H16.

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

    =SUM(OFFSET(G14,1,0):OFFSET(G15,-1,0))

    where SUM calculates the total of values in the range of cells currently referenced by G14 and G15.

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

    Figure 8-37 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.10.4 What Happens at Runtime: How Excel Calculates the Sum of a Table-Type Component Column

Figure 8-38 shows the runtime view in the integrated Excel workbook when the Excel formula shown in Figure 8-37 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 (H14 and H61) differ from those that appear in the formula bar at design time (G14 and G15) because the ADF Table component has moved and expanded to include the rows of data that it downloads.

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

Runtime View of Excel Formula in Integrated Excel Workbook

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

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