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

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

     
     Description of "Figure 9-4 Execute Action Binding"
4. Click OK.

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

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

You may also find it helpful to understand the functionality that can be added using other ADF Desktop Integration features. 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.
   
   Description of the illustration act_col_edit_ddlist.png
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.

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

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

   
5. Click OK.

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

   
   Description of "Figure 9-8 Status Message Properties in an Action Set"

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

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

4. Click OK.

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

Description of "Figure 9-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 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.

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

  
  Description of "Figure 9-10 Progress Bar for ADFmAction Type"

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

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.

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

   
4. Click OK.

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

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

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.

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

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

   
5. Click OK.

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

Description of "Figure 9-14 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.

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.

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.

   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

Description of "Figure 9-15 Confirmation Action Attributes"

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.

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

Description of "Figure 9-16 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 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.

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

   
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 information about using resource bundles, see Using Resource Bundles in an Integrated Excel Workbook.

5. Click OK.

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

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

   Tip:

   You can use the & character in the Label property value to render a key tip in Excel for the ribbon command. The key tip appears when a user types the Alt key to view available key tips. If, for example, the Label property evaluates to U&pload, Excel displays an Upload label for the ribbon command and attempts to assign p as the key tip. In this example, the successful display of p as the key tip requires that no other ribbon command at the same scope (level) tries to use p as its key tip.
6. Click OK.

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

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

Description of "Figure 9-22 Ribbon Command Displaying a Tooltip"

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

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

Description of "Figure 9-24 Search Popup Dialog"

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.

   The following example illustrates what the backing bean code might look like:

   public class PickDialogBackingBean {
       private String dialogResult = null;
       
       public PickDialogBackingBean () {
       }
   
       public void pickerOkListener(ActionEvent actionEvent) {
           setDialogResult("Continue");
           return;
       }
       
       public String getDialogResult() {
           return dialogResult;
       }
       
       public void setDialogResult(String result) {
           dialogResult = result;
       }
   }
   

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.

   The following example illustrates how you use an EL expression to set the value ADFdi_CloseWindow to the bean property value.

   <af:outputText id="otx1" value="<span id="ADFDI_CloseWindow">#{backing_PickDialog.dialogResult}</span>" escape="false"/>  

   The following example illustrates how the actionListener attribute for a button component in your dialog’s JSF page invokes the backing bean:

   <af:button actionListener="#{backing_PickDialog.pickerOkListener}"
                       text="OK"/>
   

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

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

Figure 9-26 Web Page Search Form

Description of "Figure 9-26 Web Page Search Form"

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.

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.

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

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.

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.

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.

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

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.

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

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

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

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

ADF Desktop Integration invokes the custom method that you write for every row involved in the automatic row refresh.

You can give the method an arbitrary name, but the method signature must include a java.util.ArrayList parameter that identifies the attributes (ID property of the ADF Table component’s columns) to perform a refresh of row data, as shown by the following example:

public void refreshRow (ArrayList attrIds)

The custom method that you write should:

• Determine what to do with null values.

• Update other attributes in the same row to achieve the business goal.

• Handle exceptions properly:

  • You can throw exceptions in the method. The message that you associate with the exception you throw will be reported to the end user, so write a message that the end user understands.

  • Catch and handle unexpected exceptions in the custom method.

Do not write code in the custom method that changes the iterator status or that commits ADF Model layer changes.

ADF Desktop Integration handles the following changes for you, so you do not need to write code in your custom method.

• Positions the iterator on the target row.

• Transfers attribute values from the ADF Table component to the Fusion web application.

• Automatically reverts server-side changes.

Once you complete your custom method, expose it as a method action binding in the page definition file that is associated with your integrated Excel worksheet. Verify that the method action binding appears in the bindings palette of the ADF Desktop Integration task pane. You may need to reload the page definition file, as described in How to Reload a Page Definition File in an Excel Workbook, to accomplish this.

Configure the ADF Table component to enable the automatic row refresh of data, specify the method action to invoke when a refresh occurs, and the column(s) that trigger a refresh.

1. Open the integrated Excel workbook.
2. Select the cell in the Excel worksheet that references the ADF Table component, and then click the Edit Properties button in the Oracle ADF tab.
3. Set values for the following RowActions properties in the Edit Component: ADF Table dialog that appears.

   Table 9-7 RowAction Properties to Enable Automatic Row Refresh

   Set this property...	To...
   AutoRefreshRowActionID	Specify the method action binding that invokes the custom method with the business logic that executes when an end user triggers an automatic row refresh. You do not need to specify a method action binding if you want to perform an automatic row refresh to trigger server-side validation or refresh the value of a transient attribute. For information about the custom method, see How to Write Custom Methods for Use in Automatic Row Refresh.
   AutoRefreshRowEnabled	When True, ADF Desktop Integration evaluates the TriggersRowRefresh property of the columns in this table.

4. In the Edit Component: ADF Table dialog, click the browse (...) icon of the Columns property.
   The Edit Columns dialog appears, listing all the columns of the ADF Table component.
5. Set the TriggersRowRefresh property to True or to an EL expression that evaluates to True for each column that contains rows where you want to trigger an automatic row refresh if an end user edits a value.

   Figure 9-30 shows the dialog where you set the TriggersRowRefresh property.

   Figure 9-30 TriggersRowRefresh Property to Configure Automatic Row Refresh

   
   Description of "Figure 9-30 TriggersRowRefresh Property to Configure Automatic Row Refresh"
6. Click OK.

ADF Desktop Integration performs automatic row refresh on ADF Table component cells in columns that you have configured to enable this behavior by setting the column’s TriggersRowRefresh property to true.

ADF Desktop Integration inspects the TriggersRowRefresh property of each ADF Table component column involved in the user edit and terminates the automatic row refresh operation if it does not find any columns where the property is set to True. Otherwise, it adds the value of the ID property of those columns that have the TriggersRowRefresh property set to True to the ArrayList parameter of the custom method.

A progress bar appears if the estimated processing time is significant.

ADF Desktop Integration creates a save point before processing changes. It then proceeds to perform the following changes for each row that needs to be refreshed:

1. Positions the tree binding iterator to the correct row for pending update rows and creates a temporary row for pending insert rows.

2. Transfers all attributes in the row from the worksheet into the ADF Model layer’s iterator row. Exceptions (if any) are recorded.

3. Invokes the custom method that you specified as the method action binding for the ADF Table component’s RowActions.AutoRefreshRowActionID property.

4. Refreshes the row data and reports exceptions (if any).

   1. If no exceptions occur in Steps 2 and 3, the Status column and the error details for the refreshed row are cleared.

   2. If exceptions reported in Steps 2 and 3 are oracle.jbo.attrValException for columns with the TriggersRowRefresh property set to False, the following occurs:

      1. The Status column for the row updates with an “Invalid” entry. Details appear in the Status Viewer.

      2. Row data refreshes only in columns that do not report errors.

   3. Otherwise, the following occurs:

      1. The Status column for the row updates with an “Invalid” entry and details appear in the Status Viewer.

      2. The row does not refresh.

5. ADF Desktop Integration restores the save point that it created before it began the automatic row refresh. It restores the save point irrespective of the result of the automatic row refresh operation (succeeds or fails). End users have to invoke the ADF Table component's Upload action to commit the changes to the server side that are visible in the ADF Table component after the automatic row refresh.

Note the following points if you enable automatic row refresh in an ADF Table component:

• It can impact data entry performance in the ADF Table component because automatic row refresh makes a request to the Fusion web application and executes the business logic in the custom method. The degree to which it affects data entry performance depends on the computational complexity of the business logic and other factors, such as network latency. If, for example, network latency is high, end users may see a significant delay before the row refresh completes after data entry.

• ADF Desktop Integration sends all attributes in a row from the client to the server and then from the server to the client. This includes modified and unmodified attributes.

• Many end user gestures in an ADF Table component can alter the contents of multiple cells at the same time. When an end user performs a multiple cell edit, ADF Desktop Integration examines the cells in the modified range with the set of columns in the ADF Table component for which the TriggersRowRefresh property is set to True. An automatic row refresh is performed for each table row that contains a modified cell in a column with the TriggersRowRefresh property set to True.

• The errors reported by automatic row refresh do not block the ADF Table component’s Upload action. The values used by the Upload action are those in the row after automatic row refresh completes, including the values that caused errors. For this reason, apply required constraints during invocation of the Upload action.