Create an integrated Excel workbook to manage data from a REST service

Introduction

In this tutorial, you install 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.

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. After you install the add-in, you enter the service endpoint to download data from the service to the Excel workbook. You also see a new Oracle Visual Builder ribbon tab with commands to manage downloaded data.

Objectives

By completing this tutorial, you will:

Prerequisites

Task 1: Import the sample visual application into Oracle Visual Builder

In this task, you download and import a sample visual application that provides the Employees business object used later in this tutorial.

Before you start this task, make sure that:

Note: If you already have a REST service with OpenAPI metadata, you can skip this task and start with Task 2.

  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 browser, sign in to Oracle Visual Builder.
  3. On the Visual Applications page, click Import, select Application from file, browse to Personnel_Manager.zip, and click Import.
  4. On the Visual Applications page, click Personnel Manager in the Name column.

  5. Click Business Objects Business Objects icon, then click Menu Menu icon, and select Data Manager.

    Visual Builder Data Manager page for the Employees business object

    Description of the illustration vb_data_manager.png

Task 2: Install Oracle Visual Builder Add-in for Excel

  1. Download the latest version of the add-in from the Downloads page.
  2. Quit Excel before running the installer.

  3. Double-click the downloaded installer file to begin installation, click Next, and then click Close after the add-in is installed.

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

    Excel ribbon showing the Oracle Visual Builder tab after add-in installation

    Description of the illustration excel-configure.png

For more details, see Install Oracle Visual Builder Add-in for Excel.

Task 3: Configure an Excel workbook to download data from a REST service

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

  1. In Oracle Visual Builder, open the Business Objects tab, click Employees, then click Endpoints.

  2. Expand Resource APIs, then click the Clipboard icon to copy the entire URL for the metadata resource API.

    Visual Builder Endpoints page showing the metadata resource API URL

    Description of the illustration vb-url-endpoint.png

  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 place the data table.
  5. In the Oracle Visual Builder tab, click Designer to launch the New Layout Setup wizard.

  6. In the first wizard screen, enter the URL that you copied in the Web Address field, then click Next.

    New Layout Setup wizard Web Address page

    Description of the illustration vb_wizard_screen1.png

    Leave the authentication setting as Default.

    Note: If you are using a different REST service, enter the service’s OpenAPI metadata URL. For Visual Builder business objects and ADF REST services, this URL typically ends with /describe.

  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 service metadata.

  8. Click Next to continue.

  9. In the next screen, select Employees, then click Next.

    New Layout Setup wizard Business Object selection page

    Description of the illustration vb_wizard_screen2.png

    The next screen prompts you to choose the layout type.

    New Layout Setup wizard Layout Type selection page

    Description of the illustration vb_wizard_screen3.png

  10. Select Table Layout, then click Next.

    The next screen prompts you to choose the initial field set for the layout.

    New Layout Setup wizard initial field set selection page

  11. Select All fields (except audit fields), then click Next.

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

    New Layout Setup wizard additional business object selection page

    Description of the illustration vb_wizard_screen4.png

  12. Leave this screen blank and click Next.

  13. In the final screen, confirm layout details and click Finish.

    A new table appears in your workbook with a placeholder row, and Layout Designer appears on the right.

    Excel table layout with Layout Designer open

    Description of the illustration excel-layout-designer.png

    Note: If the layout origin cell is in the first 10 rows, the header row is frozen so column headers remain visible while you scroll.

  14. Click Download Data to download data for the Employees business object.

    Excel worksheet after downloading employee data

    Description of the illustration excel-download-data-author.png

Task 4: Customize the table before publishing

Before publishing, customize the generated table. In this task, you reorder columns and add a search query.

  1. In the Oracle Visual Builder tab, click Designer if needed, then click the Columns tab in Layout Designer.

  2. Reorder columns by dragging the icon to the left of Email.

    Tip: Right-click a column for additional reorder options.

    Layout Designer Columns tab showing columns for the table layout

    Description of the illustration excel-layout-designer02.png

    Note: The Change, Status, and Key columns are generated automatically. You cannot customize or remove these columns.

  3. Click the Download tab.

  4. Click the Edit icon next to Search.

    Layout Designer Download tab showing search query settings

    Description of the illustration excel_query_search.png

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

  10. In the Search dialog box, enter 10000 and click OK.

    The add-in downloads only employee records with salary greater than 10,000.

    Search dialog prompting for the Salary query value

    Description of the illustration excel_query_search_download.png

Task 5: Publish and distribute the workbook

After creating and configuring the workbook, publish it for distribution. Publishing can remove design tools (Designer, Delete Layout, and Publish) and enable worksheet protection.

Note: Consider publishing a copy for distribution even if you do not want to disable design tools or enable worksheet protection. Publishing clears visible and hidden data, which can help protect privacy.

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

    Oracle Visual Builder ribbon showing the Publish command

    Description of the illustration excel-publish.png

  2. In the Publish Workbook window, accept the workbook location and file name.

    When the source workbook name ends with -source (for example, employees-source.xlsx), the add-in proposes the same name without -source.

    Publish Workbook window with output file settings

    Description of the illustration excel-publish-workbook.png

  3. Clear Remove the service host from each catalog and leave other publishing options unchanged.

  4. Click Publish.

    A confirmation appears in the Status pane.

    Status Viewer showing published workbook confirmation

    Description of the illustration excel-publishworkbook.png

  5. Save and close the source workbook.

    You can now distribute the published workbook to users for data entry. Each user needs credentials that grant access to the REST service used by the workbook.

For more details, see Publish an Integrated Excel Workbook.

Task 6: Create, read, update, and delete data in the published workbook

Users who receive the published workbook must install Oracle Visual Builder Add-in for Excel. After installation, they can create, read, update, and delete downloaded data before uploading changes.

  1. Open the published workbook from the directory where you saved it.

    Published workbook open in Excel with design tools hidden

    Description of the illustration excel-publish-user.png

    The Design section does not appear in the Oracle Visual Builder tab because design tools were disabled. The table displays column headers but no data because layouts were cleared during publishing.

  2. Click Download Data.
  3. Enter user name and password in the Sign In dialog, then click OK.

  4. Because the layout includes a salary-based search query, enter a query value when prompted. Leave it empty and click OK.

    The add-in downloads all employee data.

    Published workbook after downloading employee data

    Description of the illustration excel-publish-crud1.png

  5. Make the following changes:

    • In the row for Sophia Ren, change First Name from Sophia to Sophie.
    • In the row for Dave Brown, select Manager from the list of values in the Job Title cell.
    • Select the row for John Sieve, click Table Row Changes, and click Mark for Delete.
    • Select the row below John Sieve, right-click, choose Insert, and enter: Email = jmcgahern@example.com, First Name = John, Last Name = McGahern, Hire Date = 2018-01-30, Job Title = Accountant, Salary = 19,000, Department = Accounting.
    • Select the row for Ken Ster at the end of the table, click Table Row Changes, and click Mark for Delete.

    Note: Another way to add rows is to select the row below the last downloaded row, click Table Row Changes, then click Insert Rows.

    After these updates, the table is similar to the following image. An Update, Delete, or Create value appears in the Change column for each modified row.

    Published workbook table showing rows marked for update, delete, and create

    Description of the illustration excel-publish-crud2.png

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

    If prompted that the table has pending deletions, click Yes to confirm. After upload completes, the Status column is refreshed for each modified row.

    The new row creates a new record for John McGahern. The rows marked for deletion (John Sieve and Ken Ster) no longer appear because the service deleted those records.

    Published workbook after upload showing refreshed status and changed rows

    Description of the illustration excel-publish-crud3.png

    Note: All data is validated before upload. If errors occur, fix the errors and upload again. For partial errors (for example, updates and deletes succeed but create fails), you only need to fix data for failed rows.

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

    Visual Builder Data page showing uploaded employee changes

    Description of the illustration excel-publish-crud3-web.png

Related Links

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.