Use Workbook Parameters for Download

You can use workbook parameters stored in your workbook to control which rows are retrieved during download. Workbook parameters are name-value pairs that are embedded in your workbook programmatically, typically by a web developer.

An organization may choose to embed different values in an integrated workbook—for example, when it is retrieved—based on the context. Let's consider an example. Suppose your organization has a web app that includes an Employees page and that this page includes a button for downloading an integrated workbook. The web developer could use a server-side mechanism to write the same filter values to the workbook used by the business user in the web app.

For example, a business user may filter the employees list in the web app to show only employees in the Sales department with a salary greater than or equal to $7000. If this user then clicks the download button on the page, the web server embeds the filter values— Dept=Sales and MinSal=7000—in the integrated workbook before sending it to the user.

If you configure a search, a row finder, or a download parameter that references these workbook parameters, then the user sees the same data as in the web page when they open the downloaded workbook and click Download Data. For more information about using expressions that reference workbook parameters, see Workbook Parameters in Expressions.

How these workbook parameters are written to the workbook is up to you. However, if your organization plans to take advantage of this technology, your web developers will need to know where to write these workbook parameters and in what format. You can point them to Embedding Workbook Parameters in a Workbook for these details.

Test a Download with Workbook Parameters

Before you distribute the workbook, it is recommended that you test the search behavior of a configured search, row finder, or download parameter that references a workbook parameter.

Use the Network Monitor to review the requests and responses to and from the REST service during a download. The Network Monitor shows the URLs that the add-in constructs using the search or row finder settings. See Network Monitor.

You can also view or modify workbook parameters while testing using the Workbook Parameters dialog.

Note:

The Workbook Parameters dialog should only be used for testing purposes. Any parameters added or edited using this dialog are removed when the workbook is published.

To test the search behavior:

Open the Network Monitor from the Oracle Visual Builder ribbon, then click the Download Data button.
Check the results of the download in the workbook. If you experience issues, review the requests and responses in the Network Monitor.
When troubleshooting issues, try these steps:
- Check the syntax of the expression in the Search Editor, Row Finder Variable Editor, or Download Parameter Editor. Anything from a typo in the parameter name to a missing bracket may be causing the error. See About Expressions.
- Modify the value of the workbook parameter and then click Download Data again. The next download will reflect any changes made to these parameters.
  Open the Workbook Parameters dialog from the Advanced menu.
  
  Description of the illustration edit-workbook-parameters.png
  
  Note:
  The Edit Workbook Parameters menu command is only available when the designer tools are enabled.
  
  To change a value, select the workbook parameter from the list and click the Edit icon ().

Embedding Workbook Parameters in a Workbook

Write workbook parameters to a workbook using a custom solution such as a J2EE servlet or browser-based JavaScript. Workbook parameters are name-value pairs that are stored in a hidden worksheet of the workbook using a packed string-value format. Workbook developers can create searches that reference these parameters.

Workbook parameters might be name-value pairs that correspond to URL arguments passed when the workbook is downloaded from a web app. They may also represent some state from a web app page. Or they might be arbitrary named values.

Oracle Visual Builder Add-in for Excel does not include functionality for embedding workbook parameters programmatically. An organization that wants to use this functionality must provide their own custom solution for embedding these parameters in a given workbook. This topic is intended for the programmer or web developer tasked with coding this solution.

Workbook parameters must be written to a specific location and in a specific format. Before you proceed, review the parameter requirements carefully to make sure you embed your workbook parameters correctly.

Sample Methods

Here are some possible methods to consider when planning a solution:

A server-side mechanism using a J2EE servlet or filter to examine and update the workbook during download
A client-side/browser-based (JavaScript) mechanism that can examine and update the contents of the workbook after it is retrieved from the server and before it is written to the local computer file system

Location

All workbook parameters are stored as a string on a hidden worksheet named _VBCS_WorkbookInfo at cell address $B$15.

Format

The format for the packed string is:

<paramName1>=<paramValue1>/<paramName2>=<paramValue2>/...

with these conditions:

Each paramName and paramValue must be separately URL-encoded.
Each name must be separated from its value using the equals character (=).
When there are multiple name and value pairs in the string, each name value pair is separated from others by the forward slash (/) character.
The equals (=) and forward slash (/) characters must not be encoded.

Notes

The total size for the packed string format for the set of embedded parameters is limited to 32,759 characters.
The specific worksheet cell (_VBCS_WorkbookInfo!$B$15) may:
- Be empty (no parameters)
- Contain a packed string of 1 or more parameter name and value pairs
- Contain the special placeholder string, $$VbafeWorkbookParameters$$
  This value is set by default when a workbook is first integrated using the add-in, and also when the workbook is published.
  
  The add-in treats this value, if found, the same as an empty string (no parameters available)
Workbook parameters are removed when the workbook is published. This applies to all workbook parameters embedded as described here or created using the Edit Workbook Parameters dialog.