14 Deploying Your Integrated Excel Workbook

This chapter describes how to publish and deploy a workbook integrated with a Fusion web application to end users, and how to pass parameters from the Fusionweb application to the integrated Excel workbook.

This chapter includes the following sections:

Section 14.1, "Introduction to Deploying Your Integrated Excel Workbook"
Section 14.2, "Making ADF Desktop Integration Available to End Users"
Section 14.3, "Publishing Your Integrated Excel Workbook"
Section 14.4, "Deploying a Published Workbook with Your Fusion Web Application"
Section 14.5, "Passing Parameter Values from a Fusion Web Application Page to a Workbook"

14.1 Introduction to Deploying Your Integrated Excel Workbook

After you finish development of your integrated Excel workbook, you make the final integrated Excel workbook available to end users by deploying the resulting Fusion web application to an application server. Before you deploy a finalized Excel workbook that integrates with the Fusion web application, you must publish it as described in Section 14.3, "Publishing Your Integrated Excel Workbook." After you have published the Excel workbook, you can deploy it using one of the methods outlined in the "Deploying Fusion Web Applications" chapter of the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework.

The end users that you deploy an integrated Excel workbook to must do the following:

Set up ADF Desktop Integration on their machines.

Make the ADF Desktop Integration setup.exe tool available to end users from, for example, a directory on your network. For more information, see Section 14.2, "Making ADF Desktop Integration Available to End Users."
If required, configure the security settings for their Excel application.

14.2 Making ADF Desktop Integration Available to End Users

End users who want to use the functionality that you configure in an integrated Excel workbook must install th e Runtime edition of ADF Desktop Integration. The setup.exe tool is located in the adfdi-excel-runtime-client-installer.zip file available in MW_HOME\oracle_common\modules\oracle.adf.desktopintegration_11.1.1 directory, where MW_HOME is the Middleware Home directory.

For information about using the setup.exe tool, see Section I.1, "Installing the Runtime Edition of ADF Desktop Integration."

For more information about Microsoft ClickOnce installer, see the following:

http://msdn.microsoft.com/en-us/library/71baz9ah.aspx

14.3 Publishing Your Integrated Excel Workbook

After you finish configuring the Excel workbook with Oracle ADF functionality, you must publish it. Publishi ng a workbook makes it available to the end users for whom you configured the integrated Excel workbook.

ADF Desktop Integration also provides you with two methods to publish your workbook. You can publish your integrated Excel workbook directly from Excel, or you can use the publish tool available in JDeveloper to publish the workbook from the command line. The command-line publish tool enables you to use ANT build scripts to publish an integrated Excel workbook from your Fusion web application.

14.3.1 How to Publish an Integrated Excel Workbook from Excel

You publish a workbook by clicking a button on the Oracle ADF tab and specifying values in the dialogs that appear, or by using the command-line publish tool. You can use the command line publish tool to publish a workbook from your Fusion web application.

To publish a workbook from Excel:

Open the integrated Excel workbook.
Ensure that the ApplicationHomeFolder and WebPagesFolder properties in the Edit Workbook Properties dialog are correct. If these properties are not set, ADF Desktop Integration prompts you to set them when you publish the integrated Excel workbook.

For more information, see Section 4.4.3, "How to Configure a New Integrated Excel Workbook."
In the Oracle ADF tab, click the Publish button.
Specify the directory and file name for the published workbook in the Publish Workbook dialog that appears. The directory and file name that you specify for the published workbook must be different from the directory and file name for the design time workbook.
Click Save to save changes.

14.3.2 How to Publish an Integrated Excel Workbook Using the Command Line Publish Tool

The publish tool is run from the command line, and is available in the MW_HOME\jdeveloper\adfdi\bin\excel\tools\publish directory as publish-workbook.exe. Before you run the publish tool, open the source integrated Excel workbook and ensure that the ApplicationHomeFolder and WebPagesFolder properties in the Edit Workbook Properties dialog are correct.

Now, run the publish tool using the following syntax:

publish-workbook -workbook (-w) <source-workbook-path> -out (-o) <destination-workbook-path>

where source-workbook-path is the path of sthe ource workbook, and destination-workbook-path is the path where the published workbook is saved. Note that the destination workbook cannot have the same name as the source, even if the directory locations are different.

For example:

publish-workbook -workbook D:\Application1\Project1\ViewController\src\oracle\foddemo\masterpricelist\excel\workbook-src.xlsx -out D:\Application1\Project1\ViewController\public_html\excel\published\workbook.xlsx

Tip:

For more information about the arguments required by the publish tool, run the following command:

publish-workbook -help (-h)

After publishing the integrated Excel workbook successfully, the publish tool displays a success message. If there is any error while publishing the workbook, the publish tool aborts the process and the error messages are displayed on the command line console.

If you are using the command line publish tool, note that by default the publish tool logs messages to the command line console at information level.

Using the Publish Tool with ANT

You can create ANT scripts to run the publish tool from JDeveloper when you build your Fusion web application. You can use either of the following methods to run the utility using ANT:

Generate an ANT build script for the project and add a target to run the workbook command line publish tool
Generate or create a separate ANT build script for running the workbook command line publish tool

A sample ANT build script (publish-workbook.xml) to run the publish tool is available in the MW_HOME\jdeveloper\adfdi\bin\excel\samples directory. The sample ANT script demonstrates the invocation of the command-line workbook publishing tool.

14.3.3 What Happens When You Publish an Integrated Excel Workbook

When you click the Publish button in design mode, ADF Desktop Integration performs the following actions:

Removes binding expressions that are visible in the worksheet while the workbook is in design mode.
Changes the workbook mode to runtime mode.
Clears the ApplicationHomeFolder, WebAppRoot, and WebPagesFolder properties from the workbook settings of the published workbook.
Creates the published workbook with the file name you specified in the directory that you specified.
Updates the client registry. For more information, see Section 11.3, "Checking the Integrity of an Integrated Excel Workbook's Metadata."

14.4 Deploying a Published Workbook with Your Fusion Web Application

Add the integrated Excel workbook to the JDeveloper project for your Fusion web application if it is not packaged with the other files that constitute your JDeveloper project. This makes sure that the Excel workbooks you integrate with your Fusion web application get deployed when you deploy your finalized Fusion web application. For example, the Master Price List module stores the Excel workbooks that it integrates at the following location:

FOD_HOME\MasterPriceList\ViewController\src\oracle\foddemo\masterpricelist\excel\excel

where FOD_HOME is the installation directory for the Fusion Order Demo application.

After you decide on a location to store your integrated Excel workbooks, you can configure web pages in your Fusion web application allowing end users to access the integrated Excel workbooks. For example, Figure 14-1 shows Internet Explorer's File Download dialog, which was invoked by clicking the Excel > Edit Using Live Spreadsheet menu options on the PriceListSummary.jspx page displayed by the Master Price List module.

Figure 14-1 Invoking an Integrated Excel Workbook from a Fusion Web Application

Invoking a Excel Workbook from a Fusion Web Application

To enable the functionality illustrated in Figure 14-1, the HTTP filter parameters for your Fusion web application must be configured to recognize Excel workbooks. JDeveloper automatically configures these parameters for you when you add ADF Desktop Integration to the technology scope of your Fusion web application, as explained in Section 4.2, "Adding ADF Desktop Integration to a Fusion Web Application." If you want to manually configure the HTTP filter parameters, see Appendix E, "ADF Desktop Integration Settings in the Web Application Deployment Descriptor."

After you have configured the HTTP filter for your Fusion web application, you configure the web pages that the Fusion web application displays to end users to allow them to invoke Excel workbooks. A basic method of invoking an Excel workbook that you have integrated with a Fusion web application is to provide a hyperlink that invokes the workbook. For example, you could write the following HTML in a web page:

<a href="/excel/EditPriceList.xlsx">Open the Master Price List in Excel</a>

where excel is a subdirectory of the directory specified by the WebPagesFolder workbook property and EditPriceList.xlsx is the Excel workbook that the end user invokes.

You can provide functiona lity that allows end users to invoke Excel workbooks from buttons, lists and ribbon command buttons. The following list provides some examples:

Button

Display a button on the web page that, when clicked, invokes the integrated Excel workbook.
Selection list

Use the ADF Faces selectOneChoice component with a button to invoke an integrated Excel workbook.
Menu

Use the ADF Faces goMenuItem component.

The View and Query Using Live Spreadsheet menu, as illustrated in Figure 14-1, uses the goMenuItem component. The PriceListSummary.jspx page displays this menu. The following entry appears in the PriceListSummary.jspx page of the Master Price List module and demonstrates the goMenuItem component:
```
<af:goMenuItem id="goReadOnly"
textAndAccessKey="#{res['pls.productList.menu.lsr.label']}"
destination="/excel/published/ReadOnlyPriceList.xlsx"/>
```

For more information about creating web pages for a Fusion web application, see the Oracle Fusion Middleware Web User Interface Developer's Guide for Oracle Application Development Framework.

14.5 Passing Parameter Values from a Fusion Web Application Page to a Workbook

You can configure a page in your Fusion web application to pass parameter values to an integrated Excel workbook when the end user downloads the workbook from the page. For example, if the end user attempts to download a workbook from a page that displays a list of products, the list of products that appears in the workbook corresponds to the list of products displayed in the page when the end user invoked the download. Subsequent changes that the end user makes to data in one location (the worksheet or the Fusion web application's page) do not affect data in the other location.

To configure this functionality, you must:

Verify that the HTTP filter is configured to allow end users to download integrated Excel workbooks from the Fusion web application. By default, JDeveloper configures the HTTP filter with appropriate values when you add ADF Desktop Integration to the technology scope of your Oracle ADF Desktop Integration project. To verify the parameter values of the HTTP filter, see Section E.2, "Configuring the ADF Desktop Integration Excel Download Filter."
Configure the page in your Fusion web application from which the end user downloads the integrated Excel workbook so that it passes its parameters through URL arguments to the integrated Excel workbook when the end user downloads it.
Configure the page definition file associated with the worksheet in the integrated Excel workbook so that the worksheet is initialized with the parameters from the page in the Fusion web application from which the end user downloads the workbook.
Configure workbook and worksheet properties in the integrated Excel workbook that end users download so that the workbook contains the parameters from the page in the Fusion web application from which the end invokes download.

14.5.1 How to Configure the Fusion Web Application's Page to Pass Parameters

You insert an <af:goLink> tag and specify property values for it that reference the integrated Excel workbook the end user downloads and the values to download. You also specify the commands on the page that, when invoked, require the Fusion web application to refresh the values referenced by the <af:goLink> tag and its property values.

To configure the page in the Fusion web application:

In JDeveloper, insert the af:goLink tag into the page from which the end user downloads the integrated Excel workbook.
In the Structure window, right-click the af:goLink node and choose Go to Properties.

Expand the Common section and set values for the properties, as described in Table 14-1.

Table 14-1 Properties for af:goLink Tag

Property Value

Property	Value
`Text`	Write the text that appears to end users at runtime. For example, write text such as the following to appear at runtime: `Download to Excel`
`Destination`	Invoke the expression builder to write an EL expression that specifies the integrated Excel workbook and the values to download as a URL argument: For example, write an EL expression such as the following: `"/excel/workbook.xlsx?productName=#{bindings.productName.attributeValue}"` Note that the runtime URL-encoded value of the EL expression to the right of `?` must be less than 2048 bytes. If the runtime value exceeds 2048 bytes, the integrated Excel workbook downloads the URL arguments in the first 2048 bytes. Subsequent URL arguments do not get downloaded to the integrated Excel workbook. Instead, the Fusion web application writes log entries for these URL arguments identifying them as having not been downloaded. For example, the runtime URL-encoded value of `productName=#{bindings.productName.attributeValue}` must be less than 2048 bytes. Also note that if the URL contains more than 256 characters, an exception is raised when the end user downloads and opens the integrated Excel workbook without saving it. To resolve this problem, you must limit your URL length to 256 characters, or instruct the end user to save the workbook before opening it.

Text

Write the text that appears to end users at runtime.

For example, write text such as the following to appear at runtime:

Download to Excel

Destination

Invoke the expression builder to write an EL expression that specifies the integrated Excel workbook and the values to download as a URL argument:

For example, write an EL expression such as the following:

"/excel/workbook.xlsx?productName=#{bindings.productName.attributeValue}"

Note that the runtime URL-encoded value of the EL expression to the right of ? must be less than 2048 bytes. If the runtime value exceeds 2048 bytes, the integrated Excel workbook downloads the URL arguments in the first 2048 bytes. Subsequent URL arguments do not get downloaded to the integrated Excel workbook. Instead, the Fusion web application writes log entries for these URL arguments identifying them as having not been downloaded.

For example, the runtime URL-encoded value of productName=#{bindings.productName.attributeValue} must be less than 2048 bytes.

Also note that if the URL contains more than 256 characters, an exception is raised when the end user downloads and opens the integrated Excel workbook without saving it. To resolve this problem, you must limit your URL length to 256 characters, or instruct the end user to save the workbook before opening it.

Optionally, expand the Behavior section and specify component IDs for the partialTriggers property that, when invoked, update the values of the af:goLink tag and its Destination property.

For example, if you have navigation buttons with the IDs NextButton, PreviousButton, FirstButton, and LastButton, specify them as follows:

:NextButton :PreviousButton :FirstButton :LastButton

Save the page.

The following example shows the entries that JDeveloper generates in a JSF page using the examples in this procedure:

<af:goLink text="Download to Excel"
destination="/excel/workbook.xlsx?productName=#{bindings.productName.attributeValue}"
partialTriggers=":NextButton :PreviousButton :FirstButton :LastButton"/>

14.5.2 How to Configure the Page Definition File for the Worksheet to Receive Parameters

You configure the page definition file associated with the worksheet in the integrated Excel workbook as follows:

Add one or more parameter elements that initialize the worksheet with the values specified by the workbook Parameters property that you configure in Section 14.5.3, "How to Configure Parameters Properties in the Integrated Excel Workbook."

The following example shows a parameter element in a page definition file that is associated with a worksheet in an integrated Excel workbook:
```
<parameters>
    <parameter id="ProductNameParam" />
</parameters>
```

Add an invokeAction and a method action binding so that the page definition file associated with the worksheet initializes correctly.

The following example shows the initializeProductTable invokeAction invoking the filterByProductName method action binding. The invokeAction is refreshed only when a value for ProductNameParam is supplied.

<executables>
    <invokeAction Binds="filterByProductName" id="initializeProductTable"
                  Refresh="deferred"
                  RefreshCondition="${bindings.ProductNameParam != null}"/>
...
</executables>

The method action binding invokes a view object method (filterByProductName). The view object method takes a single String argument (ProductNameArg) that references the value of ProductNameParam.

<bindings>
    <methodAction id="filterByProductName" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="filterByProductName"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.ProductVO1">
      <NamedData NDName="ProductNameArg" NDValue="${bindings.ProductNameParam}"
                  NDType="java.lang.String"/>
    </methodAction>
. . .
</bindings>

For more information about configuring a page definition file, see Section 4.3, "Working with Page Definition Files for an Integrated Excel Workbook."

14.5.3 How to Configure Parameters Properties in the Integrated Excel Workbook

You configure the workbook Parameters property and the worksheet Parameters property so that the integrated Excel workbook that the end user downloads from the Fusion web application receives parameter values included in the query string of the workbook download URL.

To configure the workbook Parameters property:

Open the integrated Excel workbook.
Click Workbook Properties in the Oracle ADF tab.
Click the ellipsis button (...) beside the input field for Parameters to invoke WorkbookParameter Collection Editor.
Click Add to add a new workbook initialization parameter and configure its properties as follows:
- (Optional) In the Annotation field, enter a description of the workbook initialization parameter.
- In the Parameter field, specify the name of the URL argument that you configured for the af:goLink tag's Destination property as described in Section 14.5.1, "How to Configure the Fusion Web Application's Page to Pass Parameters."
Repeat Step 4 as necessary to add other workbook initialization parameters.
Click OK.

For more information about the workbook Parameters property, see Table A-18.

To configure the worksheet Parameters property:

Open the integrated Excel workbook.
Click Worksheet Properties in the Oracle ADF tab.
Click the ellipsis button (...) beside the input field for Parameters to invoke the WorksheetParameter Collection Editor.
Click Add to add a new worksheet parameter and configure it as in Figure 14-2:
- (Optional) In the Annotation field, enter a description of the worksheet parameter.
- In the Parameter field, specify a parameter element that you added to the page definition file associated with the worksheet, as described in Section 14.5.2, "How to Configure the Page Definition File for the Worksheet to Receive Parameters."
- In the Value field, write an EL expression that references the value of the Parameter property you specified for the workbook initialization parameter (workbook Parameters array). Use the following syntax when writing the EL expression:
  
  #{workbook.params.productName}
  
  where productName references the value of the Parameter property you specified for the workbook initialization parameter.
Figure 14-2 Worksheet Parameters
Repeat Step 4 as necessary to add other workbook initialization parameters.
Click OK.

For more information about the worksheet Parameters property, see Table A-19.

By default, the workbook parameters are not sent every time the workbook connects to the server to request metadata, the end user logs out, or the session expires. If required, you can configure the workbook to send the initialization parameters by configuring the SendParameters property.

To configure the worksheet SendParameters property:

Open the integrated Excel workbook.
Click Worksheet Properties in the Oracle ADF tab.

In the property inspector, set the value of SendParameters as shown in the following table and Figure 14-2:

Set this property to...	This value...
`SendParameters`	`True` to send workbook parameters when the workbook connects to the server to request metadata or data. When set to `True`, parameters are sent everytime when the metadata is requested and the first time when data is requested, during each user session. `False` is the default value. For more information, see Section 15.2, "Restore Server Data Context Between Sessions."

Click OK.

14.5.4 What Happens at Runtime When a Fusion Web Application Page Passes Parameters to an Integrated Excel Workbook

When the end user downloads the integrated Excel workbook from the Fusion web application, the af:goLink tag is evaluated and the current product name is captured and included on the URL. The adfdiExcelDownload filter embeds the names and values of all the parameters from the URL into the downloaded integrated Excel workbook.

After downloading the workbook, when the end user opens it for the first time, the active worksheet of the integrated Excel workbook is initialized. The initialization process includes fetching metadata from the web application. As part of retrieving the worksheet metadata, the stored workbook parameters (if any) are sent to the ADF Desktop Integration remote servlet and are available for application logic such as <invokeAction> executables. Specifically, the parameters are set into BindingContainer DCParameters before the binding container is refreshed. The action set in the worksheet Startup event is also executed during initialization. After initialization, the initialization status for each worksheet is recorded when the integrated Excel workbook is saved to disk.

After the integrated Excel workbook has been saved, closed, and reopened , the first-time initialization is skipped for any worksheets that were previously initialized. If workbook parameters were captured when the integrated Excel workbook was first downloaded, and those parameters are required to set up server context, then the Worksheet.ServerContext.SendParameters property should be set to True. When the SendParameters property is True, workbook parameters are sent on every request for metadata, and also on the first request for data in each user session.

To reset the initialization state for all worksheets in the workbook, invoke the ClearAllData action. For more information about the ClearAllData action, see Table A-17.

Note:

Parameter values passed to the server might reset when a web dialog is invoked in an action set where the ShareFrame property is True. Custom code, which uses the parameters and requires that values be maintained across the invocation of a web dialog, should ensure that the values in the user session data structures are saved.