8 Adding Interactivity to Your Integrated Excel Workbook

This chapter describes how to add interactivity options to your integrated Excel workbook, how to configure the ribbon tab, creating databound search forms and dependent list of values, and how to use EL expressions in Excel formula.

This chapter includes the following sections:

• Section 8.1, "About Adding Interactivity to an Integrated Excel Workbook"

• Section 8.2, "Using Action Sets"

• Section 8.3, "Configuring the Runtime Ribbon Tab"

• Section 8.4, "Displaying Web Pages from a Fusion Web Application"

• Section 8.5, "Adding a Custom Popup Picker Dialog to an ADF Table Column"

• Section 8.6, "Creating ADF Databound Search Forms in an Integrated Excel Workbook"

• Section 8.7, "Creating a Form in an Integrated Excel Workbook"

• Section 8.8, "Creating Dependent Lists of Values in an Integrated Excel Workbook"

• Section 8.9, "Using EL Expression to Generate an Excel Formula"

• Section 8.10, "Using Calculated Cells in an Integrated Excel Workbook"

• Section 8.11, "Using Macros in an Integrated Excel Workbook"

8.1 About Adding Interactivity to an Integrated Excel Workbook

You can make your integrated 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

Adding interactivity to an integrated Excel workbook permits end users to run 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 run. 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 Filter Customers ribbon command in EditableCustomerSearch-DT.xlsx uses multiple actions sets to filter data matching the search criteria.

Figure 8-2 Action Sets of Filter Customers Ribbon Command

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

Figure 8-3 Runtime Ribbon Tab of EditCustomerSearch.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:

• Display Web Pages: You can display pages from the Fusion web application with which you integrate your Excel workbook. For more information, see Section 8.4, "Displaying Web Pages from a Fusion Web Application."

• Dependent List of Values: You can configure an ADF List of Values component as a dependent list of values component whose values are determined by another list of values component. For more information, see Section 8.8, "Creating Dependent Lists of Values in an Integrated Excel Workbook."

• Styles: You can configure the display of your form-type components using several predefined Excel styles. For more information, see Section 9.2, "Working with Styles."

• Macros: Use macros and Excel formulas to manage the data that you want to download from or upload to your Fusion web application. For more information, see Section 8.10, "Using Calculated Cells in an Integrated Excel Workbook," and Section 8.11, "Using Macros in an Integrated Excel Workbook."

8.2 Using Action Sets

An action set is an ordered list of one or more actions that run in a specified order. The types of actions are as follows:

• 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 Input Text 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 runs the actions you define. For information about opening the Edit Action dialog, see Section 5.12, "Using the Collection Editors."

The Summit sample application for ADF Desktop Integration provides many examples of action sets in use. One example is the ribbon command labeled Upload at runtime in the EditCustomers-DT.xlsx workbook. An action set has been configured for this ribbon command 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 EditCustomers-DT.xlsx Workbook

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.

   
   

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

   • ActionID

     Click the browse (...) icon 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

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.

   
   

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

   • ComponentID

     Click the browse (...) icon 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.

   • DetailStatusMessage

     Specify an optional literal value or EL expression that appears in the Status Message window (see Section 8.2.5, "How to Display a Status Message While an Action Set Runs").

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 Action Dialog

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 list) 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-22. See Table A-17 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 browse (...) icon 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 EditCustomers-DT.xlsx file 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

   
   

5. Click OK.

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

You can display a status message and visual progress bars to end users while an action set runs by specifying values for the Status properties in an action set.

While using the Status properties in an action set, you can provide visual indication of the progress through progress bars. The Mode attribute of the Status properties enables you to choose the visual appearance of the progress bars at runtime. There are two types of progress bars available: main progress bar and detail progress bar. The main progress bar indicates the progress through the actions in an action set, and the detail progress bar indicates the progress of the current action.

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 Status group of properties as described in the Table 8-1.

   Table 8-1 Status Group of Properties

   For this property...	Enter or select this value...
   AllowCancel	True to display the Cancel button in the status dialog box. It indicates whether the action set execution can be canceled by the end user. For more information about the Cancel button, see Section 8.2.8, "What You May Need to Know About Canceling an Action."
   Enabled	True to display a status message. True is the default value.
   Message	An optional EL expression or literal value that resolves to the status message to display at runtime. For example, the Upload button in the EditCustomers-DT.xlsx file has the following EL expression configured for the Message property: #{res['excel.customers.ribbon.upload.message']}
   Title	An optional EL expression or literal value that resolves to the title of the status message to display at runtime. For example, the Upload button in the EditCustomers-DT.xlsx file has the following EL expression configured for the Title property: #{res['excel.customers.ribbon.upload.title']}
   Mode	Choose the visual appearance of progress bars. Automatic: ADF Desktop Integration analyzes the action set to determine which progress bars to display. BothBarsAlways: Shows both main and detail progress bars. MainBarOnly: Shows one progress bar only. The bar displays progress through the list of actions. DetailBarOnly: Shows one progress bar only. The bar displays progress of the current action. MainMessageOnly: None of the progress bars are shown.

   
   

   Figure 8-8 shows the property values, along with their corresponding visual elements, configured for the Status group of properties of the Upload ribbon command in the EditCustomers-DT.xlsx workbook.

   Figure 8-8 Status Message Properties in an Action Set

   
   

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

   You can also use the optional DetailStatusMessage property to provide additional information to the user. For more information about the DetailStatusMessage property, see Section 8.2.2, "How to Invoke Component Actions in an Action Set."

4. Click OK.

8.2.6 What Happens at Runtime: How the Action Set Displays a Status Message

When an action set is invoked, a status message appears if the 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 Upload ribbon command in the EditCustomers-DT.xlsx workbook runs.

Figure 8-9 Runtime View of Status Message

At runtime, if the value of the Message property is empty, ADF Desktop Integration provides a default, localized value. If the Title property is empty, the label from the action set container (such as button or ribbon command) is used. If the label of the container is also empty, then the default value provided by ADF Desktop Integration is used.

If AllowCancel property is set to True, the Cancel button appears allowing the end user to cancel the action set. If the end user cancels the action set, the Cancel button gets disabled, a warning message appears informing the user that the operation has been canceled, and the action set is aborted.

Tip:

To cancel the operation of an action set, the end user can press the Space Bar key on the keyboard.

8.2.7 What You May Need to Know About Progress Bars

Note the following pieces of information about the progress bars:

• The progress bar window hides automatically when an action (such as alert, confirm, dialog, or upload options) prompts for user input.

• Some action types, such as ADFmAction, do not support the display of incremental progress in the detail bar. For example, Figure 8-10 shows the progress bar of the Commit action with Mode set to BothBarsAlways. Notice that the detail bar appears, but does not show any progress.

  Figure 8-10 Progress Bar for ADFmAction Type

  
  

• In the Automatic mode, if the action set has less than three actions, the status message dialog shows the detail progress bar only. If the action set has three or more actions, the dialog always shows the main bar, but the detail progress bar is shown only if any of the actions in the action set is capable of incremental progress. If none of the actions is capable of incremental progress, the detail bar is suppressed.

• If required, you can display the detail progress bar without the displaying the main progress bar. Such a configuration may be useful for an action set with a few quick actions and one long action, for example, run a query and then download data.

• For very quick action sets (for example, DisplayWorksheetErrors), the best practice is to disable the status message.

8.2.8 What You May Need to Know About Canceling an Action

Each action in an action set can be categorized as non-interruptible, interruptible, or dialog.

The non-interruptible actions are atomic and cannot be canceled, or interrupted, during their operation. The following actions are non-interruptible:

• Worksheet actions: UpSync, DownSync

• ADFm action

• Table actions: RowUpSync, RowDownSync, ClearCachedRowAttributes, FlagAllRows, UnflagAllRows, MarkAllRowsChanged, MarkAllRowsUnchanged, Initialize

If the Cancel button is clicked while a non-interruptible action is running, the following happens:

1. The current action completes.

2. The action set is then aborted, and is not treated as a failure.

3. ActionSet.Alert is skipped.

4. The success, or failure, actions configured for the action set do not run.

5. The value in Worksheet.Errors is not changed.

The interruptible actions can be canceled during their operation. The following Table actions are interruptible:

• Upload, UploadAllOrNothing

• Download, DownloadFlaggedRows, DownloadForInsert

• DeleteFlaggedRows

If the Cancel button is clicked while an interruptible action is running, the following happens:

1. The current operation halts without completing.

2. The table is cleaned up:

   • Upload action: For rows that were successfully uploaded before the Cancel button was clicked, the Changed column cell flags are cleared or are left as is, and CommitBatchActionID action runs. If a row failed during upload, the Changed column cell is not affected and error status is displayed. The rows that did not get uploaded continue to display the changed status in the Changed column and the Status column remains untouched.

   • UploadAllOrNothing action: The CommitBatchActionID action does not run. The Changed column flags for all rows remain set. Failed rows display error message. Successfully uploaded rows have Status cells and error rows unpopulated.

   • Download, DownloadForInsert action: Rows that were downloaded before the Cancel button was clicked are left as is and are not removed. The table is then sized accordingly.

   • DownloadFlaggedRows action: Flagged rows that were downloaded before the Cancel button was clicked have their flag cells cleared. The remaining flagged rows continue to display the flag status.

   • DeleteFlaggedRows action: The rows that were deleted on server before the Cancel button was clicked are removed from the worksheet. The remaining flagged rows continue to display the flag status.

3. Table.FailureActionID does not run.

4. Remaining actions in the action set are skipped.

5. Table.Errors and Worksheet.Errors are left as they were.

The dialog actions show modal dialogs which can be canceled or closed. The Action Set Status Message dialog is not displayed during the execution of one of these actions. The following actions are dialog type:

• Confirmation

• Dialog

• DisplayWorksheetErrors, DisplayRowErrors, DisplayTableErrors

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

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 optional EL expression or literal value that evaluates to a message to appear in the dialog if errors occur during execution of the action set. For example, the Download button in the EditCustomers-DT.xlsx workbook has the following value configured for the FailureMessage property: #{res['excel.customers.ribbon.download.alert.failure']} The Download button invokes an action set that, in turn, invokes the ADF Table component's Download action. The EL expression specified for FailureMessage retrieves error messages if the Download action encounters errors. For more information about error handling, see Section 12.4, "Error Reporting in an Integrated Excel Workbook."
   OKButtonLabel	Specify an optional EL expression or literal value that evaluates to a message to appear in the OK button of the dialog.
   SuccessMessage	Specify an optional EL expression or literal value that evaluates to a message to appear in the dialog if no errors occur during the execution of the action set. For example, the Download button in the EditCustomers-DT.xlsx workbook has the following value configured for the SuccessMessage property: #{res['excel.customers.ribbon.download.alert.success']}

   
   

   Figure 8-11 shows the values configured for a ribbon command's Alert group of properties in the EditCustomers-DT.xlsx workbook. This ribbon command is labeled Download at runtime.

   Figure 8-11 Alert Message Properties in an Action Set

   
   

4. Click OK.

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

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

Figure 8-12 Runtime View of an Alert Message

At runtime, if the value of the FailureMessage, OKButtonLabel, or SuccessMessage property is empty, ADF Desktop Integration provides a default, localized value.

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

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

   Table 8-3 ActionOptions Properties

   Set this property...	To...
   AbortOnFailure	True (default value) so that the action set does not run any further actions if the current action fails. When set to False, the action set runs 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 can 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 can specify an action binding that runs a commit action. A value for this property is optional and you can specify a final action, such as an action binding that runs 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.12 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.

   
   

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

   • CancelButtonLabel

     Specify an optional EL expression or literal value that evaluates to a message to appear in the Cancel button of the dialog.

   • OKButtonLabel

     Specify an optional EL expression or literal value that evaluates to a message to appear in the OK button of the dialog.

   • Prompt

     Specify an optional EL expression or literal value that evaluates to a message to appear as the prompt of the dialog.

   • Title

     Specify an optional EL expression or literal value that evaluates to a title of the confirmation dialog to display at runtime.

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-13 shows the Edit Action dialog with default attribute values for the Download ribbon command.

Figure 8-13 Confirmation Action Attributes

8.2.13 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-14 shows a default Confirmation dialog with Yes and No buttons.

Figure 8-14 Confirmation Dialog

At runtime, if the value of the CancelButtonLabel, OKButtonLabel, or Prompt property is empty, ADF Desktop Integration provides a default, localized value. If the Title property is empty, the label from the action set container (such as button or ribbon command) is used. If the label of the container is also empty, then the default value provided by ADF Desktop Integration is used.

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, as illustrated in Figure 8-15.

Figure 8-15 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-16.

Figure 8-16 Runtime View of the Ribbon Tab

Figure 8-17 illustrates the runtime ribbon tab in EditCustomers.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-17 Runtime View of Ribbon Tab in EditCustomers.xlsx

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. 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-18 shows the Worksheet group at runtime where the worksheet actions, that invoke SelectActionSet action sets, appear.

Figure 8-18 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 browse (...) icon beside the Workbook Commands to display the dialog as illustrated in Figure 8-19.

   Figure 8-19 Edit Workbook Commands Dialog

   
   

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 browse (...) icon 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-21.

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

3. In the Edit Worksheet Properties dialog, click the browse (...) icon beside the input field for the Ribbon Commands property to invoke the editor, as illustrated in Figure 8-20.

   Figure 8-20 Edit Ribbon Commands Dialog

   
   

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. To add a tooltip, enter the EL expression in the Tooltip property.

   Note that Ribbon command tooltips have a maximum size of 1024 characters. If a tooltip value exceeds that limit, only the first 1024 characters are shown.

7. Click OK.

Figure 8-18 displays how the commands appear at runtime.

Notes:

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

• The ribbon controls of the toolbar are shared among all open integrated workbooks. If you open two, or more, workbooks using different ribbon buttons occupying the same location in the toolbar, Excel always shows the key tip of the first opened workbook in all open workbooks. This is an Excel limitation.

• When the user hovers the mouse over the ribbon command with the tooltip, a multi-part tooltip appears. The command label appears first in bold followed by the text from the Tooltip property. Below this text, the add-in name appears.

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 EditableCustomerSearch-DT.xlsx workbook specifies the following relative URL as a value for the page to invoke when a user clicks the Filter Customers ribbon command at runtime:

/faces/external/searchForm.jspx

Absolute URLs such as the following are also valid:

http://www.oracle.com/technetwork/middleware/fusion-middleware/overview/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 <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 using the rendered property of the f:verbatim tag.

Example 8-1 Use of Reserved HTML <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-21 shows the searchForm.jspx page hosted by the EditableCustomerSearch-DT.xlsx workbook's browser control.

Figure 8-21 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 the content of the ADFdi_CloseWindow <span> element is:

• 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";

• Abort, the popup dialog closes and the action set stops running. No additional actions are invoked.

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

• The value of the ADFdi_CloseWindow <span> is monitored on every page transition in the browser control. When the value is Continue, the popup dialog closes and the action set continues to run. When the value is Abort, the popup dialog closes and no further actions in the action set run. If the <span> element is not present, or the value is other than Continue or Abort, the popup dialog will remain open.

  On each page transition, if the reserved <span> element is present, client side Javascript can run and change the value of the element. If the value changes to Continue or Abort, the popup dialog also closes and has the same effect on the action set.

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

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

Notes:

• If the Title property is left blank, the task pane's title will also remain blank.

• If the Target property of a Dialog action is set 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:

• If your custom web page is based on ADF Faces and opens a popup window, the web page must be configured in a certain way to work properly. On the command component, set the windowEmbedStyle to inlineDocument. For more information, see Developing Web User Interfaces with Oracle ADF Faces.

• The Dialog.Page property does not accept EL expressions.

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. You may also invoke UpSync to synchronize changes in form components.

• 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. You may also invoke DownSync to synchronize changes in form components.

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 Controls Between Task Flows" section of the Developing Fusion Web Applications with 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 Applications window, expand the Application Resources panel.

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

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 Adding a Custom Popup Picker Dialog to an ADF Table Column

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 runs, 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 Developing Fusion Web Applications with Oracle Application Development Framework.

To invoke a custom 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 browse (...) icon 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-22 shows the Edit Action dialog of DoubleClickActionSet properties.

   Figure 8-22 DoubleClickActionSet Properties

   
   

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-23 shows an example of design time view of the Oracle ADF components used 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-23 Oracle ADF Components Used for Search

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 Developing Fusion Web Applications with Oracle Application Development Framework.

8.6.1 How to Create a 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-24 shows an example 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-24 ADF Input Text Component for a Simple 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. Optionally, add a tooltip.

   For more information about adding tooltips, see Section 6.9, "Displaying Tooltips in ADF Desktop Integration Form-Type Components."

9. Open the Edit Action dialog to configure the array of actions (Actions list) 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.16, "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.
   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.16, "Worksheet Actions and Properties."
   Component	Invoke a Download action from the ADF Table or ADF Read-only Table components to download the results that match the search criteria specified.

   
   

10. Click OK.

    Figure 8-25 shows an example 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-25 ADF Button Component for Simple Search Form

    
    

8.6.2 How to Create a Search Form using a Web Page in an Integrated Excel Workbook

You can use the ADF Button component, or the ribbon command, 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 Developing Fusion Web Applications with 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 a web page from an integrated Excel workbook:

1. Open the integrated Excel workbook.

2. Create an ADF Button component, or a ribbon command, in the Excel worksheet.

3. Set the Label property of the 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 list) in the ClickActionSet properties (SelectActionSet properties if you are configuring a ribbon command) of the 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."
   ComponentAction	Invoke a Download action from the ADF Table or ADF Read-only Table components to download the results that match the search criteria specified.

   
   

5. Click OK.

Figure 8-26 shows an example from the EditableCustomerSearch-DT.xlsx workbook where the ribbon command's SelectActionSet contains a Dialog action followed by the ADF Table component's Download action. When the end user invokes the ribbon command, the Dialog action will show the search page (searchForm.jspx) in a browser window. After the end user specifies search criteria in the search page and selects the Search button there, the ADF Table component's Download action runs. This will retrieve the rows matching the specified search criteria into the integrated worksheet.

Figure 8-26 Ribbon Command Configured to open a Web Page

Figure 8-27 shows the web page search form at runtime.

Figure 8-27 Web Page Search Form

8.7 Creating a Form in 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-28 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-28, 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 EditWarehouses-DT.xlsx workbook uses ADF Label components to display the title of the form. Figure 8-28 shows the label with the title text Edit Summit Warehouses.

Figure 8-28 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-29 shows an example with two illustrations from the EditWarehouses-DT.xlsx workbook, where the Country field (child list of values) changes when the value in the Region field (parent list of values) changes.

Figure 8-29 List of Values and Dependent List of Values

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 Developing Fusion Web Applications with 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.

• ADF List of Values components using date values are not supported.

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.7, "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. In the property inspector for the ADF List of Values component that is to serve as the parent in the dependent list of values, set the value of the ListOfValues.ListID property to the list binding that is the parent.

5. In the property inspector for the ADF List of Values component that is to serve as the child in the dependent list of values, set the following properties:

   • 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-30 shows the property inspector for the child ADF List of Values where the RegionId list binding is specified as the parent list of values and CountryId list is the dependent list of values.

     Figure 8-30 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-31 shows an example where Country, a dependent list value, displays only the states from the selected Region list value.

Figure 8-31 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 Developing Fusion Web Applications with 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.9, "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. In the property inspector for the ADF Table component, invoke the Edit Columns dialog by clicking the browse (...) icon beside the input field for Columns.

4. Add a new column (or modify an existing column) to serve as the parent list of values. Specify TreeNodeList as the column's subcomponent type. For more information about creating a list of values, see Section 7.14, "Creating a List of Values in an ADF Table Component Column."

5. Add a new column (or modify an existing column) to serve as the child list of values. Specify TreeNodeList as the column's subcomponent type. For more information about creating a list of values, see Section 7.14, "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 in the child list of values column.

   Figure 8-32 shows the property inspector for a dependent TreeNodeList subcomponent, where the RegionId tree binding attribute is specified for the parent list of values and the CountryId tree binding attribute is specified for the child list of values.

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

   
   

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-33 shows an example where the value that the end user selects in the Region column list of values results in the corresponding values for sub-category appearing in the Country column list of values.

Figure 8-33 Runtime Dependent List of Values in an ADF Table Component's 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 Developing Fusion Web Applications with 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.7, "ADF List of Values Component Properties." For information about the TreeNodeList subcomponent, see Section A.9, "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. In the property inspector for the ADF List of Values component, 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. Open the property inspector for the ADF Table component and invoke the Edit Columns dialog by clicking the browse (...) icon beside the input field for Columns.

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 browse (...) icon beside the input field for InsertComponent to configure the runtime list of values for insert operations.

   • Click the browse (...) icon 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-34 shows the ADF Table property inspector for a child ADF Desktop Integration Tree Node component of WarehouseLocations-DT.xlsx where the RegionList list binding is specified as the parent list of values.

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

    
    

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-35 shows an example from WarehouseLocations-DT.xlsx where the value that the end user selects in the Region list of values determines the list of values that appears in the Country column of the ADF Table component.

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

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"")")

The EL expression in Example 8-2 uses HYPERLINK function to navigate to http://www.oracle.com/technetwork/developer-tools/adf/overview/index-085534.html when end user clicks the component.

Example 8-2 HYPERLINK Function

=T("=HYPERLINK(""http://www.oracle.com/technetwork/developer-tools/adf/overview/index-085534.html"", ""#{res['excel.workbook.powerby']}"")")

If you write an EL expression using the HYPERLINK function, you should 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.

Note:

When using EL expressions in formulas, ensure that after the EL expression is evaluated, the resulting Excel formula has no more than 255 characters. This applies to formulas used to set conditional values to component properties in the editor.

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 opens the Oracle ADF Desktop Integration home page.

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 Example 8-2, you entered the following EL expression for the Value property:

   =T("=HYPERLINK(""http://www.oracle.com/technetwork/developer-tools/adf/overview/index-085534.html"", ""#{res['excel.workbook.powerby']}"")")
   

   Note:

   Excel requires that you write double double quotes (for example, ""#{res['excel.workbook.powerby']}"") 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 excel.workbook.powerby from the resource file

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

Figure 8-36 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." When the end user clicks the cell that hosts the ADF Output Text component, the Oracle ADF Desktop Integration home page opens in the web browser.

Figure 8-36 ADF 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 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 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, select the cell in which you want to write the Excel formula. For example, 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

   
   

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

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

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

8.11 Using Macros in an Integrated Excel Workbook

You can define and run 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 running of an Excel event when the Excel event is triggered by a macro.