© Sten Vesterli 2019
Sten VesterliOracle Visual Builder Cloud Service Revealedhttps://doi.org/10.1007/978-1-4842-4929-1_8

8. Using the Visual Builder Add-in for Excel

Sten Vesterli1 
(1)
Vaerloese, Denmark
 

So far, you have seen how to build web and mobile applications to interact with data from REST web services. But Oracle also offers another way to interact with this data: using the Visual Builder Add-in for Excel. Using this add-in, your users can simply download REST data into an Excel spreadsheet, work with it, and upload it back to the REST service.

The Visual Builder Add-in for Excel is currently only officially supported for 32-bit versions of Microsoft Excel 2016, but I have found it to work in newer versions as well.

Installing the Visual Builder Add-in for Excel

The Visual Builder Add-in for Excel is installed by running an .exe file you download from the Data Manager. To access the download, open the Business Objects tab and select Data Manager from the business objects menu. In the Data Manager window, select Edit Data in Excel. See Figure 8-1.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig1_HTML.jpg
Figure 8-1

Downloading the Visual Builder Add-in for Excel

Clicking the box downloads the vbcs-excel-addin-installer.exe file; clicking the “Learn more…” link takes you to the Using the Oracle Visual Builder Add-in for Excel in Oracle Integration manual.

When you run the downloaded installer, it might install some required additional Microsoft software. While you don’t need administrator rights to install the add-in itself, you might need them to install the required support software.

When you have installed the add-in, it should automatically appear as a new ribbon tab in Excel as shown in Figure 8-2.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig2_HTML.jpg
Figure 8-2

The Visual Builder Add-in for Excel on the Excel ribbon

If you don’t see the Oracle VBCS tab, check the add-in settings in Excel under File ➤ Options ➤ Add-ins. If the Oracle Visual Builder Add-in for Excel has been installed correctly but is not active, it will be listed under Inactive Application Add-ins. In that case, click the Manage drop-down at the bottom of the Excel Options dialog as shown in Figure 8-3.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig3_HTML.jpg
Figure 8-3

Managing add-ins in Excel

When you select COM Add-ins and click Go, you will be shown a dialog box where you can select which COM Add-ins to activate. Make sure there is a checkmark next to Oracle Visual Builder Add-in for Excel.

Connecting an Excel Spreadsheet

Once you have the add-in active, you can connect an Excel worksheet with a REST service.

Start by creating a blank standard Excel format workbook (.xlsx format). Place the cursor in the A1 cell and click Designer on the Oracle VBCS tab in Excel. You will be prompted to provide the Metadata API URL as shown in Figure 8-4.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig4_HTML.jpg
Figure 8-4

The prompt for Metadata API in Excel

You get this URL from the Endpoints tab in a business object under the Resource APIs heading in the Metadata column. See Figure 8-5.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig5_HTML.jpg
Figure 8-5

Getting the metadata URL from VBCS

Depending on which environment (Development, Staging, Live) you want your users to access from Excel, choose one of the three URLs. The icon next to the URL copies it to the clipboard. They have one of the following formats:

  • https://<server>/ic/builder/design/<app name> /<vers>/resources/data/<obj>/describe

    (development)

  • https://<server>/ic/builder/rt/<app name> /<vers>/resources/data/<obj>/describe

    (staging)

  • https://<server>/ic/builder/rt/<app name> /live/resources/data/<obj>/describe

    (development)

For example, the URL to the Vehicle business object in the staging environment for version 1.2 of the XDM application looks like this:
https://<server>/ic/builder/rt/XDM/1.2/resources/data/Vehicle/describe
When you paste this URL into the Metadata API dialog and click OK, you will be prompted to sign in to the Oracle Cloud (unless you have enabled anonymous access to your data). When you have signed in, you see a formatted table matching your REST service with an empty placeholder row. See Figure 8-6.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig6_HTML.jpg
Figure 8-6

A table in Excel connected to a REST service

Working with Data

Once you have the connection established, you can click Download Data to get the data from your REST service into Excel and work with them. As you work with data, you will see the Change column indicate the operation to be done.

  • Update data by just changing the values. The Change column shows Update.

  • Create new data by inserting a new row between the existing rows. The Change column shows Create. If the row contains mandatory fields, the Status column will show Invalid until you have provided all the values.

  • Delete data by selecting the row and choosing Mark for Delete from the Row Changes drop-down. The Change column will show Delete.

    Caution Don’t use standard Excel functionality to delete a row. If you do, the add-in loses knowledge about the row and can’t send a delete instruction.

When you are done, click Upload Changes. This will send your changes to the REST service to be effectuated. If you have deleted records, you will receive an extra confirmation prompt “The table has pending deletions. Do you wish to continue?” Afterward, the status column is updated with the result of the operation. See Figure 8-7.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig7_HTML.jpg
Figure 8-7

The Change and Status markers in Excel

Note that there is no feedback for deleted rows – because the rows are gone, there is no place for Excel to show feedback.

Limiting Data

If you want to limit the data you download, you can use the Query tab in the layout designer. When you click the pencil next to the Search field, a list of all fields in the object is shown. Select one and click OK to add it to the Search dialog shown in Figure 8-8.
../images/464713_1_En_8_Chapter/464713_1_En_8_Fig8_HTML.jpg
Figure 8-8

The Search dialog in Visual Builder Add-in for Excel

If you want to limit by multiple fields, click Add Field to select another field and then add a criterion for that field. When you have defined a search, the Search dialog appears each time you click Download Data, allowing you to download different subsets of the data from the REST service.

If your REST service supports Finders, you can also use these to limit the number of rows returned. Refer to Chapter 5 of the Using the Oracle Visual Builder Add-in for Excel in Oracle Integration manual.

Resetting Excel

If you have made changes in Excel that you don’t want to apply, you can select the row and choose Clear from the Row Changes drop-down.

If you want to clear the whole table in Excel, you can click the Clear button on the Oracle VBCS tab. You will be asked to confirm you want to clear the table, and then all data is removed and can be redownloaded.

Publishing a VBCS Excel Workbook

When you have verified that your workbook functions as you wish, you can decide to publish it. When you click Publish on the Oracle VBCS tab in the Excel ribbon, you are prompted for a name, and your published workbook is saved.

The idea of publishing a VBCS-connected Excel workbook is that the published workbook doesn’t have the design tools. It is also protected using standard Excel functionality so your users can’t make changes outside the VBCS-connected table.

They can still download data, work with row change markings, and upload changes.

Caution

Only users who have the Visual Builder Add-in for Excel installed will be able to work meaningfully with a published VBCS Excel workbook. While the Excel file is an ordinary Excel file and can be edited without the add-in, any changes can’t be handled once you move the file back to an Excel instance with the add-in.

Troubleshooting the VBCS Excel Add-in

If you are unable to connect to your REST service, try one of the other environments. Note that all the URLs are valid, but, for example, the Live URL doesn’t make sense until you have set the application live.

To see the communication between Excel and your REST service, you can choose Advanced and then REST Console from the Oracle VBCS tab on the Excel ribbon. This will show you the HTTP communication and will look something like this:
Sending POST request to https://.../ic/builder/design/XDM/1.2/resources/data
Headers:
User-Agent: Oracle VBCS (1.5.0.18452) .NET
Authorization: Basic c3RlbkB2ZXN0ZXJsaS5jb206YmFzMDNESzQ=
REST-Framework-Version: 4
accept-language: en-US
Content-Type: application/vnd.oracle.adf.batch+json; charset=utf-8
Content-Encoding: gzip
Host: ...
Content-Length: 288
Accept-Encoding: gzip
Connection: Keep-Alive
Request Body:
{"parts":[{"id":"part1","path":"/Vehicle","operation":"create","payload":{"vehicle":"Kayak"}},{"id":"part2","path":"/Vehicle/310","operation":"update","ifMatch":["ACED0005737200136A6176612E7574696C2E41727261794C6973747881D21D99C7619D03000149000473697A65787000000001770400000001737200106A6176612E6C616E672E446F75626C6580B3C24A296BFB0402000144000576616C7565787200106A6176612E6C616E672E4E756D62657286AC951D0B94E08B02000078703FF000000000000078"],"payload":{"vehicle":"On foot"}}]}
---
Response: 200: OK
...

The log is much longer than this, even for a single update operation. If you have someone on your team who is skilled at HTTP and REST troubleshooting, this can be very useful.

For more technical troubleshooting of the add-in itself, you will want to open a Service Request with Oracle support. To help them, you can choose Enable Logging from the Advanced drop-down. You can also click the About icon and then click Diagnostic Report to save a file with your configuration and version numbers of various components.

Conclusion

In this chapter, you learned how to install and use the Visual Builder Add-in for Excel to maintain data in a familiar Excel environment. You also saw how it is possible to publish an Excel sheet without the configuration information so that it can be distributed to end users who prefer to maintain data using Excel.

In the next chapter, you will see how to build mobile applications as yet another way to interact with REST data.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.137.240