Before You Begin
In this tutorial, you learn how to install the Oracle Visual Builder Add-in for Excel, create a Microsoft Excel (Excel) workbook that retrieves data from a REST service, and publish the workbook for users to do data entry tasks that send data to the REST service. This tutorial takes approximately 10 minutes to complete.
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 changes back to the REST service. The add-in provides a dialog box where you input the REST endpoint to download data from the REST service to the Excel workbook. The add-in also provides an Excel ribbon tab called Oracle Visual Builder that displays commands to manage the downloaded data.
What Do You Need?
- A computer using the Windows 7 or Windows 10 operating system with an installation of the 32-bit version of Microsoft Excel 2016
- Access to your Oracle Visual Builder instance
Install the Oracle Visual Builder Add-in for Excel
- Right-click this file
and download it to your computer.
ExcelRESTData.zipfile contains a visual application with an Employee business object and sample data.
- In your web browser, sign in to Oracle Visual Builder.
- On the Visual Applications page, click Import,
select Application from file in the dialog
that appears and browse to select the
ExcelRESTData.zipfile, then click Import.
- On the Visual Applications page, click ExcelRESTData in the Name column to open it.
- Click the Business Objects tab, then click the Menu and select Data Manager.
- On the Data Manager page, click the Edit Data in Excel tile to download the installer.
- Close all running instances of Excel before you execute the installer that you downloaded.
- Double-click the downloaded installer file to begin the
installation, click Next, and then Close
after the add-in is installed.
A successful installation enables the add-in in Excel, and a new tab (Oracle Visual Builder) appears the next time you start Excel.
Configure an Excel Workbook to Download Data from a REST Service
You'll create a table in an Excel workbook that downloads employee data from the REST service that the visual application you imported into Oracle Visual Builder exposes.
- In the Business Objects tab of Oracle Visual Builder, click Employee and then click the Endpoints tab.
- In the Endpoints tab, copy the URL for the metadata resource API.
- Create an Excel workbook named
employees.xlsxin a directory on your computer, and open it.
- In the Oracle Visual Builder tab, click Designer and enter the URL for the metadata resource API that you copied in the previous step in the Metadata API dialog that appears, and then click OK.
- In the Sign In dialog that appears, enter the user name and
password that you use to access Oracle Visual Builder, and
then click Sign In.
A new table appears in your Excel workbook. Column headers are displayed, and a placeholder data row appears below the column headers. The Layout Designer opens in the Excel Task Pane on the right.
- To download the data that the Employee business object references, click Download Data.
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 tasks. For example, you might want to change the order of the columns so that the Department name appears before the employee's first name.
Note: The add-in automatically generates the Changed, Status, and Key columns. You can't customize or remove these columns.
- In the Oracle Visual Builder tab, click Designer, and then click the Columns tab in the Layout Designer.
- Re-order the columns by dragging and dropping the icon that appears to the left of the department entry, as is shown in the following image.
Publish and Distribute the Excel Workbook to Users
Now that you created the Excel workbook and configured it to download data from your REST service, you’ll want to distribute it to users for data entry tasks.
- In the Oracle Visual Builder tab of the Excel workbook, click Clear before you publish the Excel workbook.
- In the Clear Table confirmation dialog box, click Yes.
This removes the downloaded data that appears in the Excel workbook that may be out of date when your users first use the published Excel workbook.
- Click Publish and then save the published
Excel workbook on your computer.
By default, the add-in appends
–pubto the current file name of the workbook (for example,
employees-pub.xlsx) to distinguish the published Excel workbook that you distribute to users from the source Excel workbook that you configured to display the data table from your REST service. After you select a valid directory and file name, a confirmation dialog is displayed.
- Save and close the source Excel workbook that you used to
publish the Excel workbook.
Now, you can distribute the published Excel workbook to users for data entry tasks. 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 demonstrates 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
Users who receive a copy of the Excel workbook that you published must install the add-in to download data from the REST service that exposes the metadata API. They can then create, read, update, and delete downloaded data in the workbook before uploading changes to the REST service.
- Open the published Excel workbook from the location that you
saved it to.
The Design section doesn't appear in the Oracle Visual Builder tab because users of a published workbook don't have access to the Designer, Delete Layout, and Publish ribbon commands that you, as a workbook author, can access. The table displays the column headers, but no data, because you clicked the Clear ribbon command before you published the Excel workbook.
- To download the data that the workbook is configured to retrieve, click Download Data.
- Enter a user name and password in the Sign In dialog, and
then click OK.
The add-in downloads the employee data.
- Make the following changes:
Facilitiesas the value for Cell C4 to make Diana Lorentz a member of the Facilities department.
- Select the row immediately below the last downloaded
row in the table, right-click and choose Insert
from the context menu that appears. Enter this data for
the newly-inserted row: Department =
Human Resources, Name =
John McGahern, Email =
email@example.com, Phone Number =
515.127.1245, and Job Title =
- Select the row for Guy Himuro by, for example,
selecting his email address. In the Oracle Visual
Builder tab, click Row Changes,
and then click Mark for Delete.
Once you make the described changes, the table in your Excel workbook is similar to that shown in the following image. That is, an
Createmessage appears in the cells of the Changed column for each row where you made a change.
Note: Another method of inserting new data rows in the table is to right-click within the boundaries of the table, and then choose Insert from the context menu that appears. The add-in creates a new row within the table.
- In the Oracle Visual Builder tab, click Upload Changes.
A confirmation dialog appears with a message that the table has pending deletions. Click Yes to confirm that you want to proceed with the upload. A progress dialog box is displayed. After the upload completes, the table refreshes the Status column to describe the change for each modified row that was uploaded. The row that you marked for deletion, containing the entry for Guy Himuro, no longer appears as the REST service has deleted it from its collection.To verify that the changes to your Excel workbook were successfully uploaded, go to the Data tab of the Business Objects page in your HR application.
Want to Learn More?
- Get Started with the Oracle Visual Builder Add-in for Excel in Using the Oracle Visual Builder Add-in for Excel
- Limit the Data Downloaded by the Oracle Visual Builder Add-in for Excel in Using the Oracle Visual Builder Add-in for Excel