Before You Begin

In this tutorial, you learn how to install the Oracle Visual Builder Add-in for Excel, create a Microsoft Excel workbook that retrieves data from a REST service, and publish the workbook so business users can perform data entry tasks. This tutorial takes approximately 10 minutes to complete.

Background

Oracle Visual Builder Add-in for Excel helps you download data from a REST service to a table in an Excel workbook, where users can modify, create, or delete data before sending those changes back to the service. Once you install the add-in, you'll enter the service's endpoint to download data from the service to the Excel workbook. You'll also see a new Oracle Visual Builder ribbon tab with commands to help you manage the downloaded data.

What Do You Need?

• A Windows 10 or 11 operating system with a 32-bit installation of Microsoft Excel 365 or other supported version

  (See Supported Platforms in Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel.)

• Access to your Oracle Visual Builder instance

  Note: You may be able to complete the tutorial using a REST service of your choice. Make sure you have the OpenAPI service metadata for the target service (some Oracle services provide such a description by adding /describe to the URL). If you go this route, some steps in this tutorial may be different for your service.

  For example, you can skip substeps 1 to 6 of step 1 but you'll still need to complete substeps 7 and 8 to install the add-in. Download the installer from the Downloads page before you begin.

Install the Oracle Visual Builder Add-in for Excel

1. Right-click this file and download it to your computer.

   The Personnel_Manager.zip file contains a visual application with an Employees business object and sample data.

2. In your web browser, sign in to Oracle Visual Builder.
3. On the Visual Applications page, click Import, then select Application from file and browse to select the Personnel_Manager.zip file. Click Import.
4. On the Visual Applications page, click Personnel Manager in the Name column.
5. Click Business Objects , then click Menu and select Data Manager.

   

6. On the Data Manager page, click Edit Data in Excel to download the installer.

   Tip: Check for a newer version on the Downloads page.

7. Quit Excel before running the installer.
8. Double-click the downloaded installer file to begin the installation, click Next, and then Close after the add-in is installed. See Install Oracle Visual Builder Add-in for Excel in Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel.

   A successful installation enables the add-in in Excel, and a new Oracle Visual Builder tab appears when you start Excel.

   

Configure an Excel Workbook to Download Data from a REST Service

In this step, you'll create a table in an Excel workbook that downloads employee data from the REST service associated with the visual application you just imported.

1. In the Business Objects tab of Oracle Visual Builder, click Employees, then Endpoints.
2. Expand Resource APIs, then click the Clipboard icon to copy the entire URL for the metadata resource API:

   

3. Create an Excel workbook named employees-source.xlsx in a directory on your computer, and open it.
4. Click the cell where you want to locate the data table.
5. In the Oracle Visual Builder tab of the Excel workbook, click Designer to launch the New Layout Setup wizard.
6. In the first screen of the wizard, enter the URL that you copied previously in the Web Address field, and click Next.

   

   Leave the authentication setting set to "Default".

   Note: If you are using a different REST service, enter the URL for that service and append /describe to the URL.

7. In the Sign In dialog, enter the user name and password that you use to access Oracle Visual Builder, and click Sign In.

   The add-in connects to your Oracle Visual Builder instance and retrieves the service metadata. The results of the operation are displayed in the wizard.

8. Click Next to proceed.
9. In the next screen, select Employees, then click Next.

   

   The next screen prompts you to choose the type of layout you want to create: either Table or Form-over-Table.

   

10. Select Table Layout, then click Next.

    The next screen prompts you to choose additional business objects for one or more dependent layouts.

    

11. Leave this screen blank and click Next to proceed.
12. In the final screen, confirm the details of your new layout, and click Finish.

    A new table appears in your Excel workbook. Column headers are shown, with a placeholder data row below the headers. You'll also see the Layout Designer on the right.

    

    Note: If the origin cell of the layout is in the first 10 rows, the header row is frozen so that you always see the column headers when you scroll up and down in the worksheet.

13. Click Download Data to download the data that the Employee business object references.

    

Customize the Table in the Excel Workbook Before Publishing

You may want to customize the table that the add-in creates in the Excel workbook before you publish and distribute it to users for data entry. For example, you might want to change the order of the columns so that the Email column appears before the employee's first name. You can also add a search query to filter the data that is downloaded from the service. For example, you can add the Salary field from the Employees business object as a search field to let users enter search criteria that lets them download only the records of employees who match that criteria.

1. In the Oracle Visual Builder tab, click Designer if necessary. Then, click the Columns tab in the Layout Designer.
2. Re-order the columns by dragging and dropping the icon that appears to the left of the Email entry, as shown here:

   Tip: Right-click a column to see more options for re-ordering.

   

   Note: The Changed, Status, and Key columns are automatically generated. You can't customize or remove these columns.

3. Click the Query tab to add a search query.
4. Click the Edit icon next to Search.

   

5. Select Salary in the Available Business Objects Field Editor and click OK.
6. In the Search Editor, select greater than in the comparator list and leave the value empty.
7. Clear Editor enabled at download to ensure the search editor controls in the Search prompt are disabled at download.
8. Click OK to save the search settings and close the Search Editor.
9. Click Download Data.
10. In the Search dialog box, enter 10000 and click OK.

    The add-in downloads only the records of employees whose salary exceeds 10,000.

    

Publish and Distribute the Excel Workbook to Users

Now that you've created the Excel workbook and configured it to download data from your REST service, you may want to publish it. This optional step is useful if you want to create a copy of your workbook that you can distribute to other users for data entry. It also lets you remove the Design tools (Designer, Delete Layout, and Publish) and turn on worksheet protection. That said, you can distribute the source Excel workbook without publishing it.

1. In the Oracle Visual Builder tab, click Publish.

   

2. In the Publish Workbook window, accept the workbook's location and file name. When the name of the original workbook ends with -source (as in our example, employees-source.xlsx), the add-in offers the same name without -source.

   

3. Clear the Remove the service host from each catalog check box but leave the other publishing options unchanged.
4. Click Publish. See Publish an Integrated Excel Workbook in Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel.

   A confirmation is displayed in the Status pane.

   

5. Save and close the source Excel workbook that you used to publish the Excel workbook.

   Now, you can distribute the published workbook to other users for data entry. Each user must have a user name and password that enables access to the REST service you entered when creating the Excel workbook.

   The next step in this tutorial shows how users complete data entry tasks using the Excel workbook that you just published.

Create, Read, Update, and Delete Data in a Published Excel Workbook

Business users who receive a copy of the Excel workbook that you published must install the add-in to download data from the REST service. (They can download the installer directly from the Downloads page.) Once the add-in is installed, they can create, read, update, and delete downloaded data in the workbook before uploading changes to the service.

1. Open the published Excel workbook from the directory that you saved it to.

   

   The Design section doesn't appear in the Oracle Visual Builder tab because you disabled the Design tools, preventing a published workbook's users from accessing the Designer, Delete Layout, and Publish commands that you, as a workbook developer, can access. The table displays the column headers, but no data, because you chose to clear all layouts when you published the Excel workbook.

2. Click Download Data to download the data that the workbook is configured to retrieve.
3. Enter a user name and password in the Sign In dialog, and click OK.
4. Because you set up a search query to limit data downloads based on employee salary, you'll be prompted to enter a value for the query. Leave the value empty and click OK.

   The add-in downloads all employee data.

   

5. Make the following changes:
   • In Row 1, change Sophia to Sophie.
   • Click cell G2 and select Manager from the list of values to change Dave Brown's Job Title.
   • Select the row for John Sieve (Row 4) by, for example, clicking his email address. In the Oracle Visual Builder tab, click Table Row Changes, and then click Mark for Delete.
   • Select the entire row 5, right-click, and choose Insert from the context menu. Enter this data for the newly-inserted row: Email = jmcgahern@example.com, First Name = John, Last Name = McGahern, Hire Date = 2018-01-30, Job Title = Accountant, Salary = 19,000, and Department = Accounting.

     Note: Another way to add new rows in the table is to select the row immediately below the last downloaded row in the table, click Table Row Changes and Insert Rows . The add-in creates a new row within the table, which you can then edit.

   • Select the row for Ken Ster at the very end of the table (Row 102) by, for example, selecting his email address. In the Oracle Visual Builder tab, click Table Row Changes, and then click Mark for Delete.

     Once you make these changes, your table is similar to that shown in the following image. That is, an Update, Delete, or Create message appears in the Changed column for each row where you made a change.

     

6. In the Oracle Visual Builder tab, click Upload Changes.

   When prompted with a message that the table has pending deletions, click Yes to confirm. After the upload completes, the table refreshes the Status column to describe the change for each modified row that was uploaded.

   The new row you created results in a new record for John McGahern being created in the collection.

   The rows that you marked for deletion, the records for John Sieve and Ken Ster, no longer appear as the REST service has deleted them from its collection.

   

   Note: All data is validated before it is uploaded to the REST service. If you run into errors, you can always fix the errors and upload your changes again. If you have partial errors, for example, if update and delete rows were successful but create rows failed, you'll only need to fix data for the failed create row. 

   To verify that the changes to your Excel workbook were successfully uploaded, go to the Data tab of the Business Objects page in your visual application.

   

Want to Learn More?

• Introduction to Oracle Visual Builder Add-in for Excel in Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
• Configure Search Options for Download in Developing Integrated Spreadsheets Using Oracle Visual Builder Add-in for Excel
• Managing Data Using Oracle Visual Builder Add-in for Excel. This guide helps your business users become successful using your integrated workbooks.