9 Adding Interactivity to Your Integrated Excel Workbook

This chapter describes how to configure action sets to allow your users invoke actions such as Upload and Download, how to configure the ribbon tab, and how to use EL expressions in Excel formulas.

This chapter includes the following sections:

9.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 9-1 shows some of the interactive features.

Figure 9-1 Interactivity Features in an Integrated Excel Workbook

This image is described in the surrounding text

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 commands that end users use at runtime to invoke action sets.

9.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 9-2, the Download All Customers ribbon command in CustomerSearch-DT.xlsx uses an action set with two actions to reset the query values associated with the worksheet. Figure 9-2 also shows a ribbon command (Search Customers) where end users can invoke search functionality.

Figure 9-2 Action Sets of Download All Customers Ribbon Command

This image is described in the surrounding text

9.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. The following sections describe other functionality that you can use:

9.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 a ribbon command) 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:

  • 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 command, as the end user selects a ribbon command 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 command, 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 progress bar to end users while an action set runs the actions you define. For information about opening the Edit Action dialog, see 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 9-3 which shows the Edit Action dialog in design mode.

By default, an end user cannot open another integrated Excel workbook while an action set runs. If you know an action set will be long running, make it non-blocking, so your end users can do other work while they wait for the long running action set to complete.

Figure 9-3 Action Set for Upload Ribbon Command in EditCustomers-DT.xlsx Workbook

This image is described in the surrounding text

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 can help you manage multiple items more effectively.

Note:

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

9.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 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 Using Action Sets.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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.
    This image is described in the surrounding text
  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. Figure 9-4, for example, shows the Execute action binding that is the first action the Download action set in the Summit sample application's EditCustomers-DT.xlsx workbook 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.

      Figure 9-4 Execute Action Binding

      This image is described in the surrounding text
  4. Click OK.

9.2.2 How to Invoke Component Actions in an Action Set

Some components, such as the ADF Table component, expose actions that can be used to manage the transfer of data between Excel worksheets that you integrate with a Fusion web application. More information about the actions available for ADF Desktop Integration components can be found in ADF Desktop Integration Component Properties and Actions.

You configure action sets to invoke one or more component actions by adding component actions to the array of actions in the action set. For example, Figure 9-5 shows the Choose Component Action dialog where the Download action exposed by the ADF Table component present in the Summit sample application's EditCustomers-DT.xlsx workbook can be selected for invocation by that workbook's Download ribbon command's SelectActionSet action set.

Figure 9-5 Choose Component Method Dialog

This image is described in the surrounding text

Note:

The Choose Component Action dialog appears empty if the current worksheet does not include any components that expose component actions.

Before you begin:

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

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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.
    This image is described in the surrounding text
  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 Action 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 How to Display a Progress Bar while an Action Set Executes).

  4. Click OK.

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

Invoking Action Sets Before Logging In

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 Authenticating the Excel Workbook User.

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 a component. Figure 9-6 shows the Choose Component Action dialog displaying two instances of the ADF 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 9-6 Choose Component Action Dialog

This image is described in the surrounding text

9.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-21. See Table A-16 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 Using Action Sets.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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 9-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 9-7 Worksheet Startup Event Invokes an Action Set

    This image is described in the surrounding text
  5. Click OK.

9.2.5 How to Display a Progress Bar while an Action Set Executes

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 a 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 property inspector for the action set where you want to configure the Status properties in the action set. Use, for example, the Edit Ribbons Command dialog if you want to configure Status properties in the SelectActionSet that a ribbon command invokes at runtime.

Before you begin:

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

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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 Actions dialog of, for example, the ribbon command that invokes the action set.
  3. Set values for the properties in the Status group of properties as described in Table 9-1.

    Table 9-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 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 ribbon command in the EditCustomers-DT.xlsx file has the following EL expression configured for the Message property:

    #{res['excel.customers.ribbon.upload.message']}

    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.

    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 ribbon command in the EditCustomers-DT.xlsx file has the following EL expression configured for the Title property:

    #{res['excel.customers.ribbon.upload.title']}


    Note:

    ADF Desktop Integration renders generic text at runtime if you do not specify values for the Message and Title properties described in Table 9-1. For this reason, we recommend that you provide values for these properties that are specific to the functional context of your action set.

    Figure 9-8 shows the property values, along with their corresponding visual elements, configured for the Status group of properties of an ADF Table component's Upload action.

    Figure 9-8 Status Message Properties in an Action Set

    This image is described in the surrounding text

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

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

  4. Click OK.

9.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 9-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 9-9 Runtime View of Status Message

This image is described in the surrounding text

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 a ribbon command) is used. If the label of the container is also empty, then the default value provided by ADF Desktop Integration is used.

9.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 9-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 9-10 Progress Bar for ADFmAction Type

    This image is described in the surrounding text
  • In the Automatic mode, if the action set has fewer 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 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, Worksheet.DownSync) or action sets that only display a dialog, the best practice is to disable the status message.

9.2.8 How to Allow End Users to Continue Working in Excel While an ActionSet Executes

You can configure your integrated Excel workbook so a long running action set does not prevent end users from using other integrated Excel workbooks or worksheets.

Integrated Excel workbooks that execute long running action sets (for example, an action set that includes a Table.Download action for 100,000 rows) block end users from using Microsoft Excel to access other integrated Excel workbooks, worksheets and non-integrated Excel workbooks and worksheets. You can configure action sets that you know contain long running actions so that end users can continue to use Excel to access other workbooks and worksheets while they wait for the action set to complete. To do this, you set the ActionOptions.NonBlocking property for the ActionSet to True. The default value is False.

If you set the ActionOptions.NonBlocking property to True, also consider displaying a progress dialog with a Cancel button to allow end user to cancel the action set. For more information, see How to Display a Progress Bar while an Action Set Executes.

To allow end users to continue using Microsoft Excel while an ActionSet executes:
  1. Open the integrated Excel workbook.
  2. Open the property inspector of the component that, at runtime, invokes the long running ActionSet and navigate to the ActionOptions group of properties.
  3. Set the NonBlocking property to True.
    For example, Figure 9-11 shows the property inspector where the NonBlocking property for a Table.Download action that a ribbon command invokes has been set to True.

    Figure 9-11 NonBlocking Property to Allow an End User Use Excel While an ActionSet Executes

    This image is described in the surrounding text
  4. Click OK.

9.2.9 What Happens at Runtime: How End Users Continue Working While an ActionSet Executes

At runtime, ADF Desktop Integration starts a background operation when an integrated Excel worksheet invokes an ActionSet for which you have set the NonBlocking property to True.

While the background operation processes the non-blocking ActionSet, an end user can perform other operations, such as

  • Switch workbooks or worksheets

  • Edit cells in other worksheets, including integrated Excel worksheets

The end user cannot edit the integrated Excel worksheet that contains the non-blocking ActionSet until the background operation completes.

If an end user performs another operation that requires communication with the ADF Desktop Integration-enabled Fusion web application, ADF Desktop Integrations sends a notification that a background operation is in progress. For example, the following notification appears to an end user who attempts to save a worksheet while a background operation is in progress.

Figure 9-12 Notification Message that an Operation Cannot Proceed Until Background Operation Completes

This image is described in the surrounding text

Once the background operation completes, the end user can once again edit the integrated Excel worksheet that invoked the background operation. If the end user's currently active worksheet is different than the non-blocking ActionSet worksheet when the background operation completes, he or she receives a notification message that the background operation has completed.

9.2.10 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

  • ADFmAction

  • Table actions: RowUpSync, RowUpSyncNoFail, 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 message content for the worksheet in the Status Viewer (if open) does not change. For more information about the Status Viewer, see Using the Status Viewer to Report Error Messages to End Users.

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. The Status Viewer reflects the status of the rows processed before cancelation.

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

The appearance of a Cancel button that allows end users cancel an action set requires you to set the AllowCancel property set to True, as described in How to Display a Progress Bar while an Action Set Executes. 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.

9.2.11 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. Consider using an alert message for action sets that execute very quickly but have no interactive actions. In these cases, you may want to disable the ActionSet.Status group of properties and enable the ActionSet.Alert properties.

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 Displaying Web Pages from a Fusion Web Application.

Before you begin:

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

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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. In the Workbook group of the Oracle ADF tab, click Worksheet Properties.
  3. In the Edit Worksheet Properties dialog, expand the Ribbon Commands node and select the ribbon command that contains the SelectActionSet for which you want to display an alert.
  4. Expand the Alert group of properties for the action set and set values as described in Table 9-2.

    For example, Figure 9-13 shows values configured for the SuccessMessage property in the Alert group of properties.


    Table 9-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 more information about error handling, see Using the Status Viewer to Report Error Messages to End Users.

    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.


    Figure 9-13 Alert Message Properties in an Action Set

    This image is described in the surrounding text
  5. Click OK.

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

Figure 9-14 shows an alert message configured for the SuccessMessage property in the Alert group of properties that appears at runtime when the action set successfully completes execution.

Figure 9-14 Runtime View of an Alert Message

This image is described in the surrounding text

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

9.2.13 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 Getting Started with the Development Tools . More information about action set properties can be found in Action Set Properties.

Before you begin:

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

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

Integrated Excel workbooks report status information and errors that occur at runtime to the end user in the Status Viewer. For more information, see Using the Status Viewer to Report Error Messages to End Users.

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

    Table 9-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

    (Optional) 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

    (Optional) 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. Optionally, write an EL expression for the action set's FailureMessage property that evaluates to a message to appear to the end user at runtime if the action set fails. This message appears in the worksheet area of the Status Viewer described in Using the Status Viewer to Report Error Messages to End Users.
  4. Click OK.

9.2.14 How to Prompt the User for Confirmation 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 set proceeds 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 Using Action Sets.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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.
    This image is described in the surrounding text
  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.

    Note:

    We recommend that you provide values for the Title and Prompt properties that are specific to your business use case.

Figure 9-15 shows the Edit Action dialog with default attribute values for the Delete flagged rows ribbon command in the Summit sample application's EditAllInventory-DT.xlsx workbook.

Figure 9-15 Confirmation Action Attributes

This image is described in the surrounding text

9.2.15 What Happens at Runtime: How the Action Set Prompts the User for 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 9-16 shows the Confirmation dialog that appears when you click the Delete flagged rows ribbon command in the Summit sample application's EditAllInventory-DT.xlsx workbook.

Figure 9-16 Confirmation Dialog

This image is described in the surrounding text

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 a ribbon command) is used. If the label of the container is also empty, then the default value provided by ADF Desktop Integration is used.

9.3 Configuring the Runtime Ribbon Tab

You can add a runtime ribbon tab to the Excel Ribbon in your integrated Excel workbook with ribbon commands to invoke Oracle ADF functionality. The runtime ribbon tab groups these items into two groups: workbook and worksheet. You configure the workbook group to display ribbon commands to invoke the workbook actions described in Workbook Actions and Properties, while you configure the worksheet group to invoke a range of actions on the active worksheet.

Figure 9-17 shows the Inventory runtime ribbon tab in the EditAllInventory-DT.xlsx workbook that configures ribbon commands in both the workbook and worksheet groups. The workbook group exposes ribbon commands to invoke the standard default workbook actions while the worksheet group exposes ribbon commands that invoke a number of component actions exposed by an ADF Table component that renders in the worksheet (Upload, DeleteFlaggedRows, and so on).

Figure 9-17 Runtime View of Ribbon Tab in EditAllInventory-DT.xlsx

This image is described in the surrounding text

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 the integrated Excel workbook or worksheet is active.

You set the Visible workbook property to True to make the ribbon tab appear in the Excel Ribbon at runtime. The value you specify for the Title property determines the title of the tab that the end user sees at runtime, as illustrated in Figure 9-18.

Figure 9-18 Workbook Properties for Runtime Ribbon Tab

This image is described in the surrounding text

For information about how you define a workbook ribbon command, see How to Define a Workbook Ribbon Command for the Runtime Ribbon Tab. For information about how you configure a worksheet ribbon command, see How to Configure a Worksheet Ribbon Command for the Runtime Ribbon Tab.

9.3.1 How to Define a Workbook Ribbon Command for the Runtime Ribbon Tab

You configure the Runtime Ribbon Tab group of workbook properties to define a workbook ribbon command.

Before you begin:

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

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

To define a workbook ribbon command:

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

    Figure 9-19 Edit Workbook Commands Dialog

    This image is described in the surrounding text
  4. Click Add and specify values for the properties of the workbook ribbon commands as follows:
    • Method

      Specify the workbook action that you want the workbook ribbon command to invoke. For the list of available workbook actions, see Workbook Actions and Properties.

    • Label

      If no label is specified, ADF Desktop Integration uses a default label at runtime.

      (Optionally) 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 Using Resource Bundles in an Integrated Excel Workbook.

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

  5. Click OK.

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

You configure the Ribbon Command group of worksheet properties to define a worksheet ribbon command. By default, no ribbon commands are defined for the worksheet group in the worksheet properties.

Before you begin:

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

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

Make sure to set the Runtime Ribbon Tab.Visible workbook property to True. If the Runtime Ribbon Tab.Visible is set to False, no runtime ribbon tab appears for this workbook. For more information about workbook properties, see Table A-20.

To define a worksheet ribbon command:

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

    Figure 9-20 Edit Ribbon Commands Dialog

    This image is described in the surrounding text
  4. Click Add to add a new ribbon command in the Members list of the collection editor.
  5. Configure the ribbon command properties as described in Table 9-4.

    Table 9-4 Worksheet Ribbon Command Properties

    Set this property to... This value...

    SelectActionSet

    Specify the type of action(s) that the ribbon command invokes. For more information about action sets, see Using Action Sets.

    Image

    Select an appropriate image for the ribbon command from the dropdown list. For example, if the ribbon command's action set invokes an ADF Table component's Download action, select Download. Choose Generic if the other options do not correspond to the action that the ribbon command invokes.

    ADF Desktop Integration provides the images that you can use.

    Label

    Specify text to appear as a label or an EL expression that evaluates to a label at runtime. For information about EL expressions in ADF Desktop Integration, see ADF Desktop Integration EL Expressions. For information about using labels, see Using Labels in an Integrated Excel Workbook.

    Tooltip

    Specify text to appear as a tooltip or an EL expression that evaluates to a tooltip at runtime.

    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.


  6. Click OK.

9.3.3 What Happens at Runtime: Ribbon Commands in the Ribbon Tab

Figure 9-21 shows the Customers ribbon tab from the Summit sample application's EditCustomers-DT.xlsx workbook. The order and grouping of the workbook-level ribbon commands is always the same at runtime. The worksheet commands appear in the same order as you define them in the Edit Ribbon Commands dialog.

Although the Status Viewer is configured once per workbook and appears in the workbook commands at design time, it appears in the worksheet group at runtime. This is because the Status Viewer is worksheet-specific and displays information about the worksheet that is in focus. If your end users navigate to a non-integrated worksheet and click the Status Viewer ribbon command, a message appears that tells the end user the Status Viewer cannot be used in that worksheet.

Figure 9-21 Ribbon Commands in the Ribbon Tab

This image is described in the surrounding text

When the user hovers the mouse over the ribbon command with the tooltip, a multi-part tooltip appears. The ribbon command label appears first in bold followed by the text from the Tooltip property. Below this text, the add-in name appears. Figure 9-22 shows the tooltip that appears when you hover over the Download worksheet ribbon command in the Summit sample application's EditCustomers-DT.xlsx workbook.

Figure 9-22 Ribbon Command Displaying a Tooltip

This image is described in the surrounding text

If you define 5 or fewer worksheet-level ribbon commands, each appears in the worksheet group with a large icon. If you define 6 or more worksheet-level ribbon commands, the first 4 ribbon commands appear with a large icon. The remaining ribbon commands appear in a menu labelled More, as shown in Figure 9-23.

Figure 9-23 Worksheet's More Ribbon Command Displaying Dropdown List

This image is described in the surrounding text

Note:

The ribbon controls of the toolbar are shared among all open integrated workbooks. If you open two, or more, workbooks using different ribbon commands 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.

9.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 that interact with your Fusion web application.

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 CustomerSearch-DT.xlsx workbook specifies the following relative URL as a value for the page to invoke when a user clicks the Search 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

Tip:

If you want to add a model-driven list picker to a table column, see Adding a Model-Driven List Picker to an ADF Table Component for more information.

Note:

The Dialog action does not support ADF task flows.

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

The following example shows how you can automatically set the value of the span element using the rendered property of the f:verbatim tag.

<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 9-24 shows the searchForm.jspx page hosted by the CustomerSearch-DT.xlsx workbook's browser control.

Figure 9-24 Search Popup Dialog

This image is described in the surrounding text

In scenarios where you cannot use the rendered property of the f:verbatim tag, 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 custom web page in a modal popup dialog using a .NET 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 within the browser control until they close the browser control, or ADF Desktop Integration closes it.

You may want to add additional actions after the Dialog action to take advantage of user choices in your custom page. For example, a user is expected to type a country name in a country-based search. In this scenario, the next logical actions to invoke are Execute (a query with the country name the user entered) and the Download action for the ADF Table component.

Note:

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

  • You should avoid configuring the web page that appears in a popup dialog so that it allows the end user to download an integrated Excel workbook. In that case, the Oracle ADF functionality becomes disabled when the end user opens a workbook downloaded from a popup dialog.

  • If you use the HTML <select> components, such as list box or dropdown list, note that <select> components do not follow z-order configuration when the page displays 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.

9.4.2 How to Display a Web Page Search Form in a Popup Dialog

You can use a 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 ribbon command to invoke the Download action for the ADF 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 Developing Fusion Web Applications with Oracle Application Development Framework.

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

Before you begin:

It may be helpful to have an understanding of how web pages render in an integrated Excel workbook. For more information, see Displaying Web Pages from a Fusion Web Application.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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 the ribbon command in the Excel worksheet, as described in How to Configure a Worksheet Ribbon Command for the Runtime Ribbon Tab.
  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 9-5 describes the actions to invoke in sequence.

    Table 9-5 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 How to Display a Web Page in a Popup Dialog.

    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 9-25 shows an example from the CustomerSearch-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 9-25 Ribbon Command Configured to open a Web Page

This image is described in the surrounding text

Figure 9-26 shows the web page search form at runtime.

Figure 9-26 Web Page Search Form

This image is described in the surrounding text

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

Note:

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

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

Note:

  • If your custom web page is based on ADF Faces and opens a popup window, the command component that your web page uses to launch the popup window, must be configured with the windowEmbedStyle attribute set to inlineDocument. For information about ADF Faces dialogs, see About Popup Dialogs, Menus, and Windows in Developing Web User Interfaces with Oracle ADF Faces.

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

9.4.4.1 Sending Data Between an Integrated Excel Worksheet and a Fusion Web Application Page

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

  • One way to capture data state from the web page (if necessary) is for logic in the web page's backing bean to retrieve data from its data bindings and to transfer that data into the bindings for the integrated Excel worksheet.

  • 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 a DoubleClickActionSet, the server-side model must be in the same state after executing the action set as it was before executing the action set. To achieve this, make sure the ADF Table component supports row-level action set model management, as described in How to Enable Row-Level Action Set Model Management.

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

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

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

9.5 Using Row-Level Action Sets in a Table Column

In certain cases, you may want to configure an action set that executes in the context of the current table row whenever the end user double-clicks a column. For example, you might configure an ADF Table component column DoubleClickActionSet to launch a custom dialog that enables the end user to select server-side row attribute values for the current table row, as described in How to Add a Custom Popup Picker Dialog to an ADF Table Column.

Row-Level Action Set Model Management

You can automate the management of the server-side model state when table-based row-level action sets that may alter the model state are invoked. ADF Desktop Integration creates a save point before invoking the actions in the action set and restores to the save point after the action set runs. This ensures that the model state after the action set was invoked remains the same if the action set is aborted or cancelled and reverts back to the same state as it was before the action set was invoked.

For insert worksheet rows, ADF Desktop Integration automatically creates a temporary server-side row that can be used during the action set. For both insert and update worksheet rows, ADF Desktop Integration automatically reverts any model changes that occur during the action set (including the temporary row in the insert case).

This is useful if you have integrated Excel workbooks with ADF Table components configured with row action sets that modify the server-side model. For example, a column component double-click action set that launches a custom dialog to select server-side row attribute values for the current worksheet row, as described in How to Add a Custom Popup Picker Dialog to an ADF Table Column.

9.5.1 How to Enable Row-Level Action Set Model Management

To manage the server-side model state with a row-level action set, set the following workbook property to True:

Compatibility.TableComponents.RowActionSetModelMgmtEnabled

Before you set the RowActionSetModelMgmtEnabled property to True, note that ADF Desktop Integration creates a DataControl savepoint to capture and restore the model state. So, make sure that the DataControl providers of your Fusion web application support savepoints.

To enable row-level action set model management:

  1. Open the integrated Excel workbook.
  2. In the Workbook group of the Oracle ADF tab, click Workbook Properties.
  3. If the ADF Table component supports row inserts (InsertRowEnabled row-level action), set the InsertBeforeRowActionID action to create a temporary server-side row during a row-level action set.

    If your use case requires a separate action to create a temporary row for row-level action sets, configure the InsertTempRowActionID property.

    Note:

    If the InsertBeforeRowActionID action is sufficient for creating a temporary server-side row during a row-level action set, InsertTempRowActionID should be left blank.

  4. In the Edit Workbook Properties dialog, if not set already, set the Workbook.Compatibility.TableComponents.RowActionSetModelMgmtEnabled property to True.

    Note that the RowActionSetModelMgmtEnabled property is set to False in integrated Excel workbooks created with versions of ADF Desktop Integration that did not include this feature.

  5. Click OK to close the Edit Workbook Properties dialog.

Note:

For integrated Excel workbooks created with older versions of ADF Desktop Integration, set the RowActionSetModelMgmtEnabled property to True and remove any custom configuration or code that manages model state during row-level action sets.

9.5.2 What Happens at Runtime: RowActionSetModelMgmtEnabled is Set to True

If RowActionSetModelMgmtEnabled property is set to True, ADF Desktop Integration automatically manages the model state while the row-level action set runs.

For an insert worksheet row, a temporary server-side row is automatically created when the action set runs and is automatically removed after a successful upload. When the InsertTempRowActionID action is configured, it gets invoked to create the temporary server-side row. Otherwise, the InsertBeforeRowActionID action is invoked to create the temporary server-side row instead.

If neither the InsertTempRowActionID nor InsertBeforeRowActionID actions are configured, no action is invoked for insert rows. The InsertTempRowActionID action is ignored if InsertRowEnabled is set to False.

When the end-user invokes a row-level action set configured in an ADF Table component and the row-level action set contains one or more actions that may alter the model state, ADF Desktop Integration does the following:

  1. Positions the server-side row (for update worksheet rows only)

  2. Creates a data control save point

  3. Invokes the InsertTempRowActionID or InsertBeforeRowActionID action to create a temporary server-side row (for insert worksheet rows only)

  4. Invokes the actions in the action set

  5. Restores to the previously created save point after the action set invocation is completed, regardless of how it terminates including:

    • Upload successful

    • Upload failure

    • End user clicks the Cancel button

Note:

The following actions (or action types) may alter the model state:

  • Table.RowUpSync

  • Table.RowDownSync—only applies to insert rows

    RowDownSync for an existing row does not alter the model state.

  • Table.RowUpSyncNoFail

  • Worksheet.UpSync

    This action is also supported in row-level action sets.

  • ADFmAction

  • Dialog

    The Dialog action may change the model state if ShareFrame is set to True and the web page is part of the same web application.

If the RowActionSetModelMgmtEnabled property is set to False, you must explicitly manage the creation and deletion of the temporary server-side row while the action set runs.

9.5.3 How to Synchronize Changes from ADF Table Component Using RowUpSyncNoFail

A row-level action set may contain ADFmAction or Dialog actions that depend on the current state of the model to complete successfully. The Table.RowUpSync action sends the current value of individual table rows from the worksheet to the model layer in the Fusion web application. The Table.RowUpSync action requires all cells in a table row to contain valid data for the action to complete successfully. For example, in a newly-inserted row, all required attributes must have valid values for a Table.RowUpSync action to complete. In contrast, the Table.RowUpSyncNoFail action synchronizes valid values from cells in a table row and ignores any validation failures for invalid values. Like RowUpSync, the RowUpSyncNoFail action is intended for use in the row-level action sets of table columns that supports DoubleClickActionSet.

Enable row-level action set model management when using RowUpSyncNoFail, as described in How to Enable Row-Level Action Set Model Management.

To synchronize changes from ADF Table Component using RowUpSyncNoFail:

  1. Open the integrated Excel workbook.
  2. Select the cell in the Excel worksheet that references the table-type component and click the Edit Properties button in the Oracle ADF tab.
  3. Click the browse (...) icon of the Columns property.
  4. In the Edit Columns dialog, select the column, and click the browse (...) icon of the UpdateComponent property.
  5. Add the ADF Table component RowUpSyncNoFail action to the list of actions of the column's DoubleClickActionSet.
  6. Click OK.

9.5.4 What Happens at Runtime: RowUpSyncNoFail Action is Invoked

When the RowUpSyncNoFail action is invoked, data values from the current table row are uploaded to the server and common failures, error reporting, and error handling are ignored. Fatal errors, such as the server being unavailable, will be reported.

The RowUpSyncNoFail action modifies the state of the model and the changes are not reverted on error. Consequently, it is possible that a call to RowUpSyncNoFail may leave the row in the model with values that would cause row validation to fail. This may in turn impact the behavior of subsequent calls to other methods, such as Table.Upload. For this reason, you should ensure that row-level action set model management is enabled.

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

Before you begin:

It may be helpful to have an understanding of using row-level action sets. For more information, see Using Row-Level Action Sets in a Table Column.

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

Make sure the ADF Table component supports row-level action set model management, as described in How to Enable Row-Level Action Set Model Management, if you want the custom pick dialog to function correctly in an ADF Table component that supports an insert component. Without row-level action set model management enabled, no temporary insert rows will be created at runtime.

To invoke a custom pick dialog from an ADF Table component column:

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

    Table 9-6 DoubleClickActionSet Properties

    Add this action... To...

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


  7. Click OK.

9.6 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 9-1 uses HYPERLINK function to navigate to http://www.oracle.com/technetwork/developer-tools/adf/overview/index-085534.html when end user clicks the component.

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.

Example 9-1 HYPERLINK Function

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

9.6.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 Using EL Expression to Generate an Excel Formula.

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. For more information, see 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 9-1, 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.

9.6.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 hyperlinked cell that a user can click

Figure 9-27 shows the runtime view of the example configured in 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 9-27 ADF Output Text Component Configured to Display a Hyperlink

This image is described in the surrounding text

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

9.7.1 How to Calculate the Sum of a Table-Type Component Column

The following task illustrates how you use the Excel functions AVERAGE and OFFSET to calculate the average of the column labeled Salary 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 AVERAGE function calculates the average value 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 Using Calculated Cells in an Integrated Excel Workbook.

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

Make sure that the ADF Table component's RowActions.AutoConvertNewRowsEnabled property is set to False. For more information about this property, see ADF Table Component Properties.

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, J2.
  2. Write the Excel formula that performs a calculation on a range of cells at runtime. For example:

    =AVERAGE(OFFSET(J2,1,0):OFFSET(J4,-1,0))

    where AVERAGE calculates the average value in the range of cells currently referenced by J2 and J4.

    Figure 9-28 shows the design time view of the Excel formula in the integrated Excel workbook.

    Figure 9-28 Design Time View of Excel Formula in an Integrated Excel Workbook

    This image is described in the surrounding text
  3. Save your changes and switch to runtime mode to test that the Excel formula you entered evaluates correctly.

9.7.2 What Happens at Runtime: How Excel Calculates the Sum of a Table-Type Component Column

Figure 9-29 shows the runtime view in the integrated Excel workbook when the Excel formula shown in Figure 9-28 is evaluated. The Excel formula calculates the average of the values in the range of cells that you specified in design mode.

Figure 9-29 Runtime View of Excel Formula in an Integrated Excel Workbook

This image is described in the surrounding text

9.8 Using Macros in an Integrated Excel Workbook

You can define and run macros based on Excel events in an integrated Excel workbook. ADF Desktop Integration reacts to Excel events. An example of an Excel event is the change event that occurs when something in an Excel worksheet changes.

Excel events can occur when an end user or a macro perform an action (for example, insert a new row). ADF Desktop Integration reacts to the Excel event. While ADF Desktop Integration triggers code in response to the Excel event, all further Excel events are suppressed.

Assume, for example, that you write a macro in your integrated Excel workbook that the workbook triggers when a change event occurs in a particular cell. If an end user changes the cell, the Excel event occurs and the macro executes. However, if ADF Desktop Integration changes the cell, no Excel event occurs and the macro does not execute.

For more information about Excel events, see Microsoft’s documentation.