16 Using an Integrated Excel Workbook Across Multiple Web Sessions

This chapter describes how to configure the integrated Excel workbook so that your use cases work properly across multiple web application sessions.

This chapter includes the following sections:

16.1 About Using an Integrated Excel Workbook Across Multiple Web Sessions

End users can open an integrated Excel workbook and log on to a Fusion web application from the workbook ribbon command that you configure. The Fusion web application assigns a session to the user. After a connection to the Fusion web application is established and a valid session assigned, end users can download data from the Fusion web application to the workbook. They can then log off from the Fusion web application using the workbook ribbon command or otherwise disconnect from the Fusion web application by, for example, disconnecting from the network that hosts the Fusion web application.

If the user logs off from the Fusion web application using a workbook command, the Fusion web application terminates the session immediately. If the user allows the session to time out by leaving the workbook open and idle, the Fusion web application terminates the session assigned to the user after session timeout expires.

Using integrated Excel workbooks disconnected from the Fusion web application, end users can perform the following actions:

Modify data downloaded from the Fusion web application
Insert new data into the appropriate ADF Table component contained in the workbook
Save changes to data and close and reopen the workbook without having to upload data to the Fusion web application
Track and update changes in the ADF Table component

Test your integrated Excel workbook's behavior across multiple web application sessions. To do this, run the integrated Excel workbook. As you go through the steps of your use case, click the Logout workbook ribbon command at various points to end the current web application session. Make a special point of ending the current session between invocations of the ADF Table component's Download and Upload actions. New web application sessions will be created as needed. If the results are not what you expect, you may need to configure the properties described in subsequent sections of this chapter.

16.1.1 Using an Integrated Excel Workbook Across Multiple Web Sessions Use Cases and Examples

When end users open a published integrated Excel workbook, the workbook downloads required data. Then, if they disconnect from the server, they can continue to edit and update the data in the integrated Excel workbook, and save and close it.

16.1.2 Additional Functionality for Using an Integrated Excel Workbook Across Multiple Web Sessions

After you have validated and tested your integrated Excel workbook across multiple web sessions, you may find that you need to add additional functionality for your workbook. The following sections describe other functionality that you can use:

Troubleshooting integrated Excel workbook: You might encounter some problems while developing or deploying an integrated Excel workbook. For more information, see Troubleshooting an Integrated Excel Workbook.
Installing ADF Desktop Integration: You must install ADF Desktop Integration to enable end users to use ADF Desktop Integration and integrated Excel workbooks. For more information, see End User Actions.

16.2 Restore Server Data Context Between Sessions

For use cases where the behavior of one or more action sets in a worksheet rely on the current model state of the Fusion web application, you must configure your integrated Excel workbook and page definition file to capture and restore the correct model state whenever a new session is established.

A new session can occur whenever:

The user saves, closes, and re-opens the integrated Excel workbook
The user invokes the workbook Logout action
The time between invocation of action sets that contact the Fusion web application exceeds the session timeout value specified for a Fusion web application session

16.2.1 How to Configure an Integrated Excel Workbook to Restore Server Data Context

You specify the attribute bindings that you want to cache in an integrated Excel workbook between sessions as values for the worksheet's ServerContext group of properties. This group of properties also enables you to specify the action binding that uses the cached attribute binding data to restore server-side context when a Fusion web application assigns a new session to the integrated Excel workbook.

Before you can specify values for the ServerContext group of properties, the page definition file that is associated with the worksheet must expose the attribute bindings and action bindings for which you want to restore server context. For information about adding attribute bindings and action bindings to a page definition file, see Working with Page Definition Files for an Integrated Excel Workbook. For information about the ServerContext group of properties, see the entry for ServerContext in Table A-21.

Before you begin:

It may be helpful to have an understanding of how to restore server data context. For more information, see Restore Server Data Context Between Sessions.

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 Using an Integrated Excel Workbook Across Multiple Web Sessions.

To configure an integrated Excel workbook to restore server data context:

Open the integrated Excel workbook.
In the Workbook group of the Oracle ADF tab, click Worksheet Properties.

In the Edit Worksheet Properties dialog, configure values for the ServerContext group of properties as described by Table 16-1.

Table 16-1 ServerContext Properties to Restore Server Data Context

For this property... Enter or select this value...

For this property...	Enter or select this value...
`CacheDataContexts`	Typically, you add an element to this collection to restore a non-trivial query that you cannot configure directly in the page definition file. Adding an element to this collection is optional if you do not have to address this scenario. If you add an element to the collection of `CacheDataContexts`, configure it as follows: `RestoreDataContextActionID` Specify the action binding (for example, the `Execute` action binding) that connects to the Fusion web application to restore the data specified by `CachedServerContexts`. `CachedServerContexts` An array that identifies the attribute binding values to cache and set before the action binding specified by `RestoreDataContextActionID` is invoked. Each element in the array (`CachedServerContext`) supports the `CachedAttributeID` and `RestoredAttributeID` properties. For more information about the `CacheDataContexts` property and its subproperties, see Worksheet Actions and Properties.
`IDAttributeID`	Specify the attribute binding that uniquely identifies the row displayed in the current worksheet. At runtime, the value that this property references determines if the server data context has been correctly restored. Typically, you use this property to handle a form. It may be optional otherwise. For more information about this property and its subproperties, see Worksheet Actions and Properties.

CacheDataContexts

Typically, you add an element to this collection to restore a non-trivial query that you cannot configure directly in the page definition file. Adding an element to this collection is optional if you do not have to address this scenario. If you add an element to the collection of CacheDataContexts, configure it as follows:

RestoreDataContextActionID

Specify the action binding (for example, the Execute action binding) that connects to the Fusion web application to restore the data specified by CachedServerContexts.
CachedServerContexts

An array that identifies the attribute binding values to cache and set before the action binding specified by RestoreDataContextActionID is invoked. Each element in the array (CachedServerContext) supports the CachedAttributeID and RestoredAttributeID properties.

For more information about the CacheDataContexts property and its subproperties, see Worksheet Actions and Properties.

IDAttributeID

Specify the attribute binding that uniquely identifies the row displayed in the current worksheet. At runtime, the value that this property references determines if the server data context has been correctly restored. Typically, you use this property to handle a form. It may be optional otherwise.

For more information about this property and its subproperties, see Worksheet Actions and Properties.

If your integrated Excel workbook uses parameters and you have deployed it by downloading it from your Fusion web application, see How to Configure Parameters Properties in the Integrated Excel Workbook.

Click OK.

Note:

For integrated Excel workbooks that use a worksheet setup action (or a Parameters and <invokeAction> executable), you may not need to configure RestoreDataContextActionID and CachedServerContexts, if the worksheet setup action can restore server data context when a new session is created.

16.2.2 What Happens at Runtime: How the Integrated Excel Workbook Restores Server Data Context

During the initial session (for example, session ID 1), the worksheet caches data using the ServerContext group of properties. In a later session with a different session ID (for example, session ID 2), where the ADF Table component's Upload action is invoked, the data cached in the ServerContext group of properties is sent to the Fusion web application.

16.3 Caching of Static Information in an Integrated Excel Workbook

Certain types of relatively static data are cached in the integrated Excel workbook to allow end users to use the workbook while disconnected from the Fusion web application. Table 16-2 describes the types of data that an integrated Excel workbook caches.

Invoking the ClearAllData workbook action described in Workbook Actions and Properties, refreshes all types of cached data described in Table 16-2. Table 16-2 also describes other scenarios where an integrated Excel workbook refreshes cached data.

Table 16-2 Types of Data an Integrated Excel Workbook Caches

This type of data...	Is cached when...	And refreshed when...
Page definition metadata that is not expected to change between user sessions such as control binding types, IDs, and labels.	An integrated Excel worksheet bound to a page definition file is activated and no cache of the page definition file's metadata exists.	The page definition metadata is not refreshed unless you download a new copy of the integrated Excel workbook.
ADF List of Values component list items	The ADF List of Values component first downloads the list items from the Fusion web application.	The values of the list items hosted by the Fusion web application differ from those cached by the integrated Excel workbook. The cached list items are refreshed only once per workbook session and only if a workbook session exists.
Resource bundle strings	The integrated Excel workbook is first initialized. A workbook is initialized when it is opened for the first time after publishing.	The cache of resource bundle strings is not refreshed unless you download a new copy of the integrated Excel workbook.

16.4 Caching Lists of Values for Use Across Multiple Web Sessions

ADF Desktop Integration caches the values referenced by the ADF List of Values components that you use to create lists of values and dependent lists of values so that these components do not send a request to the Fusion web application when the end user selects a value at runtime.

ADF Desktop Integration caches up to two hundred and fifty values for each component. If a component references a list of values with more than two hundred and fifty values, ADF Desktop Integration caches the first two hundred and fifty values and writes a warning message to the client-side log file for subsequent values. Consider configuring your integrated Excel workbook to use a model-driven list picker, as described in Adding a Model-Driven List Picker to an ADF Table Component, where a list of values references more than two hundred and fifty values. For more information about client-side log files, see Generating Log Files for an Integrated Excel Workbook.

Cached lists of values in an integrated Excel workbook get refreshed once per workbook session. This refresh occurs after the user reestablishes a web session with the Fusion web application and if the values referenced by the Fusion web application have changed since the integrated Excel workbook last cached the list of values.

The upload of a selected value from a list of values causes the upload to fail if the selected value no longer exists in the Fusion web application. This may occur if, for example, one end user deletes the value in the Fusion web application while another end user modifies the selected value in the cached list of values of an integrated Excel workbook and attempts to upload the modified value to the Fusion web application.

Note that if you change the Fusion web application configuration after you have deployed the Fusion web application and the end users have started using the published integrated Excel workbooks, you must inform the end users to download a fresh copy of the integrated Excel workbook, or invoke the ClearAllData workbook action. For more information about the ClearAllData workbook action, see Workbook Actions and Properties.

The changes in your Fusion web application might include changing the definitions of the list bindings associated with the ADF List of Values components exposed in the worksheet. Changing list binding configuration can cause unexpected exceptions in workbooks that have been downloaded and run prior to the change.

Note:

An integrated Excel workbook never caches the values that a ModelDrivenColumnComponent subcomponent displays in a model-driven list picker. For more information about model-driven list pickers, see Adding a Model-Driven List Picker to an ADF Table Component.

For more information about lists of values, see Working with Lists of Values .

16.5 Using Explicit Worksheet Setup Action

ADF Desktop Integration provides several features for configuring a worksheet after the binding container's metadata has been obtained from the server at runtime. However, at times, you might want to configure the data or the binding container before the client retrieves the binding container metadata. For example, at design time, you might want to add a table to the worksheet, but without specifying the view object that will drive that table, until runtime. This would be desirable if the view object to be used depends on some parameter values or settings that are not known until runtime. In addition, you might want to configure the view object based on runtime parameter values (such as add attributes, or indicate which attributes to display). Similarly, you may also want to configure the binding container based on runtime parameter values. Such use cases require performing setup tasks before the binding container metadata is sent from the sever to the worksheet.

Using the Explicit Worksheet Setup Action feature of ADF Desktop Integration, you can specify a setup action that is invoked before the client retrieves the binding container metadata. The EditWarehouseInventory-DT.xlsx workbook in the Summit sample application demonstrates an implementation of this feature where the Warehouse Inventory worksheet invokes a method action binding named FilterWarehouseMasterById.

16.5.1 How to Configure Explicit Worksheet Setup Action

Using the SetupActionID property of the worksheet, you can specify a method that is invoked before the binding container metadata is sent to the worksheet. In the method, you can implement the logic necessary for any configuration on the data and binding container.

Before you begin:

It may be helpful to have an understanding of the Explicit Worksheet Setup Action feature. For more information, see Using Explicit Worksheet Setup Action.

To use the worksheet SetupActionID property:

Open the worksheet in the integrated Excel workbook.
From the Excel Ribbon, click Worksheet Properties.
In the Edit Worksheet Properties dialog, expand Data and click the browse icon (...) beside the input field for the SetupActionID property
In the Select Binding dialog, select the action that you want to invoke before the binding container metadata is sent to the worksheet, and click OK.

Note:

The SetupActionID property accepts ADFmAction only. A validation error is reported if an invalid method is set for the property.
Click OK to close the Edit Worksheet Properties dialog.

Figure 16-1 shows the configuration in the EditWarehouseInventory-DT.xlsx workbook.

Figure 16-1 SetupActionID Property in Edit Worksheet Properties Dialog

16.5.2 What You May Need to Know About Explicit Worksheet Setup Action

After the action specified in the SetupActionID property runs, the binding container metadata that is sent to worksheet reflects the changes configured in the method. ADF Desktop Integration ensures that the setup action runs only once for any binding container instance. If, for any reason, a new binding container instance becomes associated with the worksheet, the setup action will be invoked again, to ensure it is configured.

If any kind of failure occurs during the invoking of the setup action, ADF Desktop Integration is automatically disabled in the worksheet. Logging out, and then logging in, will not enable ADF Desktop Integration in the worksheet. Running Clear All Data command from the Excel Ribbon re-enables ADF Desktop Integration in the worksheet, the setup action runs again on subsequent requests.