Display the content of a spreadsheet file

As prerequisite for this tutorial, please follow the "Create a master playlist" tutorial.

Introduction

Final result

This tutorial explains how to display the content of a spreadsheet file created with Excel, on top of the existing layouts. For this, we are going to use one of the spreadsheet widgets included in Elementi Library.

You can download the project resulted after following this tutorial from the link below.

Note:

One of the key features of Elementi is to provide ready-to-use widgets to display data from local files or web servers.

Video

For full screen, see this video on Vimeo.

Tutorial

Displaying a Spreadsheet project

Version 1.0 (2015-12-04)

• Difficulty: Easy.
• Total duration: 5-10 minutes.
• Requirements:
  • Elementi 2015 or later. (The latest version is recommended.)
  • (Optional) One or more HMP devices using firmware 3.2.0 or later for the publishing part of the tutorial. (The latest version is recommended.)

Spreadsheet

Spreadsheet editing

Saving csv file

Let's create the spreadsheet to be displayed in the layout.

1. Open Office Excel and create a new documents
   • Any text editor can actually be used to create the spreadsheet. If non Latin characters will be used, then the editor must be UTF-8 compatible.
   • Note that it is not possible to create spreadsheet compatible with the HMP using Office Online.
2. Enter the data you want to be displayed.
   • Do not use any formatting in the document as they will be lost when saving it as a csv file.
3. Open the save dialog and select a location on your local hard drive to save the file. Enter "registration.csv" for the file name
4. Select "CSV (Comma delimited)(*.csv) as document type.
5. Click on the Tools dropdown (next to the Save button) and select "Web Options..."
   • Excel will open the "Web Options" dialog
6. In "Encoding" select "Unicode (UTF-8)" in the "Save this document as" dropdown.
   • This will insure that non ASCII characters are kept and displayed as expected.
7. Press OK to close the dialog and Save your file.

Your spreadsheet in now ready to be imported in the Project.

Getting started

To get started, follow Create a master playlist tutorial. Once done, make a copy of that project, rename the copy to "Datafeed-spreadsheet" and then open it.

• Import the spreadsheet (csv file).
  • The spreadsheet file will be copied from your local hard drive into the project. If you update the spreadsheet file using Excel, it will need to be imported again.
  • Importing a spreadsheet is done the same way as for any media file (See this video about importing media in case of need).

In this tutorial, we will not create a new Layout to display the spreadsheet but add this information to the master layout file. This will display the information contained in the spreadsheet on top of all the content displayed by the Project.

Importing Spreadsheet widget

Text Ticker widget

Next, let's import the Text ticker widgets into the current layout.

1. Click on the "Library" tab within the browse panel. This opens the Elementi Library.
2. Open the "Spreadsheet" folder and find the "Text Ticker.svg" widget.
3. Drag & drop the widget from the browse panel onto the edit panel. A new media layer is added on top of the other layers.
4. Go back to the project by clicking on the "Datafeed-speadsheet" tab in the browse panel.
5. Position the widget to occupy the top of the project master layout.

Note that the widget file was automatically copied into the current project upon import.

Configuring Data source

Data Feed Properties

Testing data feeds

Next, let's change the widget data source from the default one pointing to the SpinetiX server (i.e. displaying the "Lorem ipsum ..." text) to the imported spreadsheet.

1. Double-click on the "Text Ticker.svg" layer within the edit panel. This opens the widget within the main layout context (i.e. without closing index.svg document).
2. Click twice on the "Data source" property. This opens the "Data Feed Properties" dialog.
3. Enter the name of the text file to be displayed ("registration.csv")
4. Click on the "Test..." button to verify the results.
5. Click on the "OK" button to save the changes to the data source.

Note:

The spreadsheet created in Excel has a top column with headers. It is also possible to use a spreadsheet without headers in Elementi - in this case, you need to use the "CSV Parser" tab of the Data Feed Properties dialog to specify the column headers as explained in the Spreadsheet widgets page.

Configuring Spreadsheet widget

Configuring text ticker

Text to be displayed

Next, let's modify the configuration of the widget.

1. Edit the text layout to change the text to be displayed and its color.
   • By default the Text Ticker display the content of a column called title using the syntax [[title]]. As the spreadsheet do not contains a column with this name, nothing is displayed for the moment.
2. Click twice on the "Text template" property. This opens the "Edit Text" dialog.
3. Select the default "[[title]]" and replace it with the column you want to be displayed.
   • To display a specific column use the syntax [[column_name]], i.e to display the column called "Trainer" use the syntax "[[Trainer]]". The names are case sensitive.
   • It is possible to mix text coming from the spreadsheet with default text, i.e. "Trainer: [[Trainer]]".
   • It is possible to format the text to be displayed, i.e. change the color of a subset of the text. However the text size cannot be changed for this widget as it will be automatically computed to fit the size of the widget in the layout. Also note that this widget will only display text on a single line.
4. In this tutorial we will use the following text: "[[Fitness Class]] - [[Trainer]]: [[Registrations]]", with the first element in bold.
5. Click on the "OK" button to save the changes to the text template.

Save your work, the tutorial is completed.

See also

• "Customize your spreadsheet widget" video tutorial which is covering the subject presented on this page.
• Get data from Excel file