Excel Connection

Owned by Christopher Dawes
Last updated: Apr 18, 2022 by Marty Lechleider
5 min read

In this example we have an Excel file that has a series of rows that we want to use to populate a dropdown in our Volt app. I created an Excel file with a list of Colors and a second file with a list of States. Using Link we can create a service that will enable this content to be pulled into our Volt applications. This is a great alternative to hard-coding dropdown options.

Create Link Connection

  1. Launch the HCL Link admin page, http://localhost:9990/login (In my deployment I changed the port).

  2. Make sure that you are working with the “_app_volt” project and “deploy_action=true” in the url. I set a bookmark that has the relevant url suffix, http://localhost:9990/workspace?_p=60a4454d8f331f240bfea01b&app=_app_volt&deploy_action=true.

  3. Close the popup dialog that shows when the page first loads.

  4. On the bottom of the screen click “Connections”, and then “New”.

5. Select “Excel” from the list of available types and click “Next”:

6. Enter a meaningful name and description for the Connection.

The name will appear in the HCL Volt Service Catalog dropdown, therefore it should be something that is easily recognized and understood by those that will use it.

Create an Action

7. The Connection now appears in the Connection List. Expand the Connection List, hover over “Excel”, click on the 3 vertical dots and click “New Action”.

8. We are create an action where the content in the Excel file is the source. Select “Source” for the Action Type and click Next.

9. In the Properties section:

i) Select “--- Upload File ---” from the Excel File dropdown.

ii) Read Mode and WorkSheet Identifier can be left to their default values. For more information regarding these properties refer to the HCL Link documentation.

iii) Click the “Fetch” button. This will detect the worksheets in the provided Excel file. Select the worksheet that contains the content for the service.

iv) The rest of the properties can be left to their default values.

v) Click Next.

10. In the Schema section, select New, enter a name (i.e. Excel Colors Schema), and click “Generate”. The Schema Type dropdown will show you the structure of the file. The value that you select here will change the output of the service. In this case we want all the rows to be returned, therefore select “Row” as the Schema Type and click Next.

11. In the Identification section provide a meaningful name and description.

This detail will be shown in the Volt UI and used to identify the service. Note: As a service is updated in Link, Volt will report the version of that service and the last time that it was updated. It is important to note that all previous versions of a Link service can be executed, therefore updating a Link service will not break any apps that are using it.

12. When you click OK, as long as you have “deploy_action=true” in the URL, a popup will appear and show the progress of the Action being deployed.

13. The Connection and Action will now appear in Volt. There is a task that runs every 6 minutes to update the content from Link to Volt. You may also restart the Domino HTTP task if you want the Link content to appear in Volt immediately.

14. You are now ready to use the Link Connector/Action!

Setup Service in Volt

15. Create a dropdown on your form canvas. Set the label to “Colors from Excel File”. In the field properties, under Options click the Edit button. Select “Use a Service” for the option type and then click “Add/Edit Service Configuration”

Note: You may also create the service from the Settings tab. Under Services select the form that will contain the service. Click Add Service Configuration.

16. Select “Select from Service Catalog”, select “Excel” from the Service Catalog dropdown, select “List Colors 2” and click Next.

17. Click Next as there are no Inputs for this service.

18. Map “COLA” to the Saved and Display value of the dropdown.

19. Save and Preview the form. When the form loads you should see all the colors appear in the dropdown.

20. You are all done! You have successfully pulled data from an Excel file into your Volt application from HCL Link.