Oracle by Example brandingManage Data from a REST Service Using an Excel Workbook

section 0Before 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.

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 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

section 1Install the Oracle Visual Builder Add-in for Excel

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

    The ExcelRESTData.zip file contains a visual application with an Employee business object and sample data.

  2. In your web browser, sign in to Oracle Visual Builder.
  3. On the Visual Applications page, click Import, select Application from file in the dialog that appears and browse to select the ExcelRESTData.zip file, then click Import.
  4. On the Visual Applications page, click ExcelRESTData in the Name column to open it.
  5. Click the Business Objects Business Objects icon tab, then click the Menu Menu icon and select Data Manager.
    Data Manager menu item
    Description of the illustration vbcssp_imp_s3.png
  6. On the Data Manager page, click the Edit Data in Excel tile to download the installer.
  7. Close all running instances of Excel before you execute the installer that you downloaded.
  8. 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.

    The Oracle VBCS tab that appears in Excel after you install the add-in
    Description of the illustration vbcs-excel-configure.png

section 2Configure 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.

  1. In the Business Objects tab of Oracle Visual Builder, click Employee and then click the Endpoints tab.
  2. In the Endpoints tab, copy the URL for the metadata resource API.
    The metadata URL that you copy in Oracle Visual Builder
    Description of the illustration vb-url-endpoint.png
  3. Create an Excel workbook named employees.xlsx in a directory on your computer, and open it.
  4. 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.
  5. 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.

    The data table and Layout Designer that appear in the Excel workbook after you specify the business object from which you
				want to download data.
    Description of the illustration vbcs-excel-layout-designer.png
  6. To download the data that the Employee business object references, click Download Data.
  7. The image shows the shows the Oracle VBCS tab and the initial data table in the Excel workbook.
    Description of the illustration vbcs-excel-download-data-author.png

section 3Customize 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.

  1. In the Oracle Visual Builder tab, click Designer, and 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 department entry, as is shown in the following image.
  3. The image shows initial data table in the Excel workbook and the Layout Designer where you configure the data table.
    Description of the illustration vbcs-excel-layout-designer02.png

section 4Publish 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.

  1. In the Oracle Visual Builder tab of the Excel workbook, click Clear before you publish the Excel workbook.
  2. The image shows the shows the Oracle VBCS tab and the initial data table in the Excel workbook.
    Description of the illustration vbcs-excel-clear.png
  3. 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.

  4. Click Publish and then save the published Excel workbook on your computer.

    By default, the add-in appends –pub to 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.

  5. The Publish Workbook information dialog with a message that the workbook has been successfully published.
    Description of the illustration vbcs-excel-publishworkbook.png
  6. 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.


section 5Create, 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.

  1. Open the published Excel workbook from the location that you saved it to.
    Shows an Excel workbook that is configured to use the add-in.
    Description of the illustration vbcs-excel-publish-user.png

    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.

  2. To download the data that the workbook is configured to retrieve, click Download Data.
  3. Enter a user name and password in the Sign In dialog, and then click OK.

    The add-in downloads the employee data.

  4. Shows a data table in the Excel workbook with employee data, such as name, email, department, and hire date.
    Description of the illustration vbcs-excel-publish-crud1.png
  5. Make the following changes:
    • Change Steven King to Sean King.
    • Enter Facilities as 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 = jmcgahern@example.com, Phone Number = 515.127.1245, and Job Title = Manager.
    • 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 Update, Delete, or Create message 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.

      A data table in the Excel workbook with employee data, such as name, email, department, and hire date.
      Description of the illustration vbcs-excel-publish-crud2.png
  6. 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.

    A data table in the Excel workbook with employee data, such as name, email, department, and hire date.
    Description of the illustration vbcs-excel-publish-crud3.png
    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.
    A data table in the Excel workbook with employee data, such as name, email, department, and hire date.
    Description of the illustration vbcs-excel-publish-crud3-web.png

more informationWant to Learn More?