3 Create a Table Layout in an Excel Workbook

Create a Table layout in the Excel worksheet when you want to render the data that the add-in downloads from the REST service to the Excel workbook in a tabular format.

Run Excel and create a blank workbook using the standard Excel workbook file format type (.XLSX) or the macro-enabled workbook type (.XLSM). The Oracle Visual Builder Add-in for Excel does not support other Excel formats (.XLS, and so on).

1. In the Excel ribbon, select the Oracle Visual Builder tab.

   
   Description of the illustration excel-configure-table.png

2. Click on the cell where you want to locate the table.

3. In the Oracle Visual Builder tab, click Designer.

4. When prompted, provide the service description document. Use the Web Address option (the default) if you access the service description from a URL. Use the Select a file option if the service description document is a local file on your computer.

   
   Description of the illustration excel-apiinput.png

   Note:

   If multiple business objects are found, the Choose a Business Object window prompts you to choose from the available business objects.
   Description of the illustration excel-available-bo.png

   The add-in creates a data table in the Excel workbook that renders Column headers for the data, a placeholder data row, and the Layout Designer opens in the Excel Task Pane.

   
   Description of the illustration layout-designer-table.png

5. Click the Columns tab to add or remove columns, or change the order in which columns appear. Review the list of columns to verify that the add-in only creates the desired columns. After making changes in the Layout Designer, click Redraw Layout to see your changes reflected in the worksheet.

   Note:

   In the Columns tab of the Layout Designer, right-click a column to see choices for changing the order.

The workbook is now complete and ready to be published. We recommend you test the table by performing various operations, such as download, update, and upload before you publish the workbook to distribute it to users. Alternatively, you can configure the workbook further so that the add-in limits the data that it downloads, as described in Configure Search Options for Download.

Path Parameters

Some service paths include path parameters. The add-in includes support for that parameters that are defined in an OpenAPI service description document, and have a data type property. The add-in support path parameters that have string and integer data types. Other data types are not supported. For string-typed path parameters, values that users enter into the Download Path Parameter Editor are used verbatim in the resultant URL path that the REST request uses. For integer-typed values, certain culture-specific formatting is removed (for example, commas for thousands separators, parentheses for negative). In all cases the values used on the URL path are not URL-encoded, so the values entered must be acceptable by the REST service.

The following is an example of a service path with an embedded parameter:

/invoices/{invoices_Id}/child/invoiceLines

Note {invoices_Id} in the middle of the service path.

If you try to access this path as is, you get an error. Instead, you must substitute a proper value for {invoices_Id}, as in the following example:

/invoices/123456/child/invoiceLines

Accessing this service path should provide all the invoice lines for invoice number 123456.

The add-in provides direct support for configuring a Table layout using a parameterized service path. The add-in extracts the path parameters automatically and prompts the user to provide the corresponding values at download time.

To configure this implementation in your Table layout, provide an OpenAPI-compliant service description. When prompted, choose a child business object or any parameterized path from the business object picker.

Tip:

When working with Oracle RAMP REST services, you should start with the web address to the parent BO description (and not the child address). In the example above, you would provide the address to the invoices description (not invoice lines). Oracle RAMP REST services cannot provide OpenAPI service descriptions for parameterized service paths.

Complete the layout configuration. When users click Download in the Oracle Visual Builder tab, the add-in displays the Download Path Parameter Editor where users provide the required path parameter values that enables the download of data to complete.

The Download Path Parameter Editor does not validate the value(s) that users enter. The value(s) that users provide must be valid. If the path includes multiple embedded parameters, the Download Path Parameter Editor prompts the user to provide a value for each embedded parameter.