Upload
and Download
, how to configure the ribbon tab, and how to use EL expressions in Excel formulas.This chapter includes the following sections:
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
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.
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
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:
Display Web Pages: You can display pages from the Fusion web application with which you integrate your Excel workbook. For more information, see Displaying Web Pages from a Fusion Web Application.
Dependent List of Values: You can configure an ADF List of Values component as a dependent list of values component whose values are determined by another list of values component. For more information, see Creating Dependent Lists of Values in an Integrated Excel Workbook.
Styles: You can configure the display of your form-type components using several predefined Excel styles. For more information, see Working with Styles.
Macros: Use macros and Excel formulas to manage the data that you want to download from or upload to your Fusion web application. For more information, see Using Calculated Cells in an Integrated Excel Workbook and Using Macros in an Integrated Excel Workbook .
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
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.
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:
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. 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:
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
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:
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:
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
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
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.
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.
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
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:
The current action completes.
The action set is then aborted, and is not treated as a failure.
ActionSet.Alert
is skipped.
The success or failure actions configured for the action set do not run.
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:
The current operation halts without completing.
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.
Table.FailureActionID
does not run.
Remaining actions in the action set are skipped.
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.
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:
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
At runtime, if the value of the FailureMessage
, OKButtonLabel
, or SuccessMessage
property is empty, ADF Desktop Integration provides a default, localized value.
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:
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
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
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
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.
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
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
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.
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:
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:
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
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
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.
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.
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
In scenarios where you cannot use the rendered
property of the f:verbatim
tag, you may need to:
Dialog
action's result value as a property<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.
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:
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
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).
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 web page must be configured in a certain way to work properly. On the command component, set the windowEmbedStyle
to inlineDocument. For more information, see Developing Web User Interfaces with Oracle ADF Faces.
The Dialog.Page
property does not accept EL expressions.
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.
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.
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:
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.
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:
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.
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:
Positions the server-side row (for update worksheet rows only)
Creates a data control save point
Invokes the InsertTempRowActionID
or InsertBeforeRowActionID
action to create a temporary server-side row (for insert worksheet rows only)
Invokes the actions in the action set
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.
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:
Columns
property.UpdateComponent
property.RowUpSyncNoFail
action to the list of actions of the column's DoubleClickActionSet
.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.
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:
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']}"")")
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:
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
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.
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:
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
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.