Spreadsheet widgets in Elementi 2017

This page is about the spreadsheet data-driven widgets included into Elementi 2017. For other versions, see Spreadsheet widgets page.

Introduction

Spreadsheets are commonly used to store data in tabular form (i.e data cells arranged into rows and columns), like sport scores, employee birthdays, production results, price lists, poll results, restaurant menus, etc. The spreadsheet widgets can be used to display the content of such files within digital signage projects.

The following spreadsheet data source types (further called "channels") are supported:

CSV spreadsheet file
This is a delimited text file that uses a comma (though other separators can be used as well) to separate values. Almost all spreadsheets and database management systems can export data as CSV file - some examples include: Microsoft Excel, Google Sheets, OpenOffice.org Calc, LibreOffice Calc, Numbers (spreadsheet) etc.

The CSV file can either be imported into the Elementi project (total autonomy) or be located on a web server and retrieved from there by the HMP via Ethernet (requires a reliable network connection). The referenced CSV file cannot be located on your PC or on a network shared folder.
Excel file
This is part of the Microsoft Office suite of applications, servers, and services developed by Microsoft; a web-based version is also available. Both newer and older Excel file formats are supported as data source.

The data is retrieved by Elementi from the Excel source file and included (in CSV format) with the content at the time of publish on the HMP; to synchronize further changes within the Excel file, you need to re-publish the project on the HMP. The Excel file itself cannot be used by the HMP, thus it cannot be imported into the Elementi project, nor be referenced from a web server.

A tutorial about how to display an Excel file is available.
Google Sheets
This is part of a free, web-based software office suite offered by Google within its Google Drive service. The suite allows users to create and edit documents online while collaborating with other users in real-time. See more about Google Sheets.

The Google Sheet data is retrieved from Google servers by the HMP via Ethernet (requires a reliable network connection).
Excel Online
New in Elementi 2017

This is part of the Microsoft Office Online suite of applications, servers, and services developed by Microsoft; This allows users to create and edit documents online while collaborating with other users in real-time. Only xlsx online documents are supported.

The Excel Online data is retrieved from Microsoft servers by the HMP via Ethernet (requires a reliable network connection).

Video tutorial

Spreadsheet widgets

Under the "Spreadsheets" folder of Elementi 2017 Widgets, you can find many widgets that automatically display data extracted from a spreadsheet data source:

Spreadsheet widgets in Elementi 2017

Menu
Displays a menu based on data retrieved from a CSV spreadsheet, a google sheet, and Excel file or Excel online.

New in Elementi 2017
Media Menu
Displays a menu with images based on data retrieved from a CSV spreadsheet, a google sheet, and Excel file or Excel online.

New in Elementi 2017
Price List
Displays some price list based on data retrieved from a CSV spreadsheet, a google sheet, and Excel file or Excel online.

New in Elementi 2017
Way Finding
Displays some way finding information based on data retrieved from a CSV spreadsheet, a google sheet, and Excel file or Excel online.

New in Elementi 2017
Opening Hours, Today Opening Hours
Displays opening hours.svg information based on data retrieved from a CSV spreadsheet, a google sheet, and Excel file or Excel online.

New in Elementi 2017
Text Bar
Displays text content using a fixed number of lines and applying a bottom-to-top & line-by-line scrolling effect whenever the entire text doesn't fit into the widget area.
Text Ticker
Displays text content using a continuous horizontal scrolling effect. The font size is adjusted automatically for the text to fit into the widget area.
Text Roll
Displays text content using a continuous vertical scrolling effect. The font size is adjusted automatically for the selected number of lines to fit into the predefined area.
Slideshow
Displays slides composed of text & media layers, which are automatically filled out with data retrieved from the CSV spreadsheet. The slides are displayed one after another, with or without a transition effect in between, similarly to a playlist.
Horizontal Table, Vertical Table, Table, and Grouped Table
Displays slides composed of multiple cells of text & media layers, which are automatically filled out with data retrieved from the CSV spreadsheet. The slides are displayed one after another, with or without a transition effect in between, similarly to a playlist.

Notes:

For proper display of the data, make sure that the spreadsheet file is encoded using UTF-8 - otherwise, non-Latin characters might not display correctly.
The current version of these widgets is not compatible with multiscreen content.

Configuration

Each of the widgets above can be customized using the options provided under "Properties" tab - for more details go to the dedicated page of each widget type, linked above.

To edit the properties related to the data source, click on the button. This opens the "Data Properties" dialog, which offers different parameters depending on the selected channel:

CSV File (default)
Excel File
Google Sheets
Excel Online (New in Elementi 2017)

Note:

At any point, the data source configuration can be tested by clicking on the "Test..." button.

CSV file channel

When the channel option is set to "CSV File", the "Data Properties" dialog offers the following parameters:

"Data Properties" dialog for spreadsheet widgets - CVS file channel

Channel
Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".
URI
Enter the URI to the CSV file - this can be a local path into the project or a remote HTTP(S) address.
Refresh
Specify the period after which the widget should refresh the spreadsheet data (this is useful especially when the widget is running in a loop); either select one of the provided options ("When document opens", 5m, 10m, ... , 24h) or enter a number, optionally followed by s (implicit), m or h; the minimum accepted value is 60 seconds. Using "When document opens" will refresh the data only when the document is re-opened.
Separator
Select the column separator used within the CSV data, from the following: comma (,), semicolon (;), pipe (|) and tab.
CSV fields are not quoted
Enable this when the data fields inside the CSV file are not quoted. When not checked, automatic quote detection is done.
Cell range
Enable this option and specify the range of cells (e.g., A1:B3) to be kept from the data set.
Transpose
Enable this to convert a vertical range of cells to a horizontal range, or vice versa.
The table has a header
Specify whether the first row of data contains the column headers.
Specify output
Enable this when the spreadsheet file does not have column headers on the first row, and enter the column names and their index in the table below.

Excel file channel

Using this channel requires that the Excel application is installed on the PC running Elementi 2016 or later.

"Data Properties" dialog for spreadsheet widgets - Excel file channel

When the channel option is set to "Excel File", the "Data Properties" dialog offers the following parameters:

Channel
Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".
Workbook
Click on the browse button and select the Excel file to import.
Sheet
Select the sheet containing the data to import.
Refresh
This is set to "On publish" and cannot be changed. For more details, see the note below.
Cell range, Transpose, The table has a header, Specify output
Same as above.

Note:

The local changes to the Excel file cannot be automatically synchronized on the HMP - the spreadsheet data is retrieved from the Excel source file and included with the content at the time of publish on the HMP; to synchronize further changes of the Excel file, you need to re-publish the project on the HMP.

Google sheet channel

Using this channel requires that having your Google account connected with the Cockpit account.

Moreover, your Elementi license must be registered into Cockpit and your target HMP must be added into Cockpit.

"Data Properties" dialog for spreadsheet widgets - Google sheet channel

When the channel option is set to "Google Sheets", the "Data Properties" dialog offers the following parameters:

Channel
Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".
Account
Select one of the connected Google accounts or click "Connect account..." if none was previously connected.
Workbook
Click on the browse button and select the Google sheet to use.
Sheet
Select the sheet containing the data to import.
Refresh
Specify the period after which the widget should refresh the spreadsheet data (this is useful especially when the widget is running in a loop); either select one of the provided options ("When document opens", 5m, 10m, ... , 24h) or enter a number, optionally followed by s (implicit), m or h; the minimum accepted value is 60 seconds. Using "When document opens" will refresh the data only when the document is re-opened.
Cell range, Transpose, The table has a header, Specify output
Same as above.

Google channel connection

Using the Google Sheets channel within the spreadsheet widgets requires having your Google account connected with the Cockpit account where your Elementi license is registered. For that, follow these steps:

Select the "Channel" option as "Google Sheets".
Select the "Account" option as "Connect account...".
Click the URL displayed within the "Connect Account..." dialog.
- If you are not signed into your Cockpit account, you are asked to do that.
- If the your Elementi license is not registered into Cockpit, you are asked to do that.
The Google sign-in page is opened;
- Follow the steps on that page to sign into your Google account.
Allow the SpinetiX app to access the specified information of your Google account.
Return to Elementi and click the "OK" button within the "Connect Account..." dialog.
At this point, your Google account appears in the "Account" drop-down box and your Google sheets automatically appear in the "Workbook" drop-down box.

Note:

The Google account can be disconnected at any time from Cockpit from the "Channels" page.

Excel Online channel

Added in Elementi 2017.

Using this channel requires that having your Microsoft Work account connected with the Cockpit account.

Moreover, your Elementi license must be registered into Cockpit and your target HMP must be added into Cockpit.

"Data Properties" dialog for spreadsheet widgets - Excel Online channel

When the channel option is set to "Excel Online", the "Data Properties" dialog offers the following parameters:

Channel
Select the spreadsheet data source type between "Excel File", "CSV File" (default), and "Google Sheets".
Account
Select one of the connected Microsoft online accounts or click "Connect account..." if none was previously connected.
Workbook
Click on the browse button and select the Excel sheet to use.
Sheet
Select the sheet containing the data to import.
Refresh
Specify the period after which the widget should refresh the spreadsheet data (this is useful especially when the widget is running in a loop); either select one of the provided options ("When document opens", 5m, 10m, ... , 24h) or enter a number, optionally followed by s (implicit), m or h; the minimum accepted value is 60 seconds. Using "When document opens" will refresh the data only when the document is re-opened.
Cell range, Transpose, The table has a header, Specify output
Same as above.

Microsoft Online channel connection

Using the Excel Online channel within the spreadsheet widgets requires having your Microsoft Work account connected with the Cockpit account where your Elementi license is registered. For that, follow these steps:

Select the "Channel" option as "Excel Online".
Select the "Account" option as "Connect account...".
Click the URL displayed within the "Connect Account..." dialog.
- If you are not signed into your Cockpit account, you are asked to do that.
- If the your Elementi license is not registered into Cockpit, you are asked to do that.
The Microsoft online sign-in page is opened;
- Follow the steps on that page to sign into your Microsoft online account.
Allow the SpinetiX app to access the specified information of your Microsoft online account.
Return to Elementi and click the "OK" button within the "Connect Account..." dialog.
At this point, your Microsoft online account appears in the "Account" drop-down box and your Excel Online sheets automatically appear in the "Workbook" drop-down box.

Notes:

Microsoft Work or School accounts are supported, but not Microsoft Personal accounts.
The Microsoft online account can be disconnected at any time from Cockpit from the "Channels" page.

Blue-Pack spreadsheet widgets

Added in Elementi 2017.

Download the sample csv file from
https://download.spinetix.com/content/elementi/feeds/sample_feeds_menu.csv
Select your data source channel (CSV file, Excel file, Excel online or Google sheet)
Create a new document (a CSV file, an Excel file, an Excel online document or a Google sheet).
Copy the data from the csv file into you document and replace the "lorem ipsum..." by your own data.
Update the data source channel and location to use your data instead of the sample file.

Properties

Menu widget properties

The following properties, found under "Properties" tab, can be used to customize the widget:

Data
Click on the button (it opens the Data Properties dialog for Spreadsheet). This let the user select the location of the data source.
Locale
Select the locale used for displaying dates and numbers.
Slide duration
Enter the number of seconds each tweet is displayed on the screen.
Transition
Click on the button (it opens the "Transition Properties" dialog) and select a transition effect and its properties.
Looping
Select whether the widget ends after the last tweet or loops to the first one.
Number of rows
Enter the number of rows of data to display on a single slide.
Number of columns
Enter the number of columns of data to display on a single slide.
Item per page
Number of menu item to be displayed on a single page.
Delay per cell
Enter the number of seconds to wait before displaying the next cell.

Data structure

Menu widget Data structure

The data is composed of five columns:

category
Category to be displayed on the top of the page.

It is possible to use a dedicated line for the category to increase readability of the data, or to repeat the category each line if desired.
title
Name of the item. The name should fit on a single line.
description
Description of the item. Up to three lines of text are supported.

Price of the item. The currency and formatting will be automatically derived from the chosen locale.

Media Menu

Description

Menu widget

The Menu widget let you display a menu on the screen. The menu is composed of images with a description and a price for each item.

The data can be stored in any of the spreadsheet data source format (i.e. CSV file, Excel file on the computer, Excel online or Google sheet) and will be updated automatically by the widget once published on the HMP.

To use your own data source instead of the sample file:

Download the sample csv file from
https://download.spinetix.com/content/elementi/feeds/sample_feeds_media_menu.csv
Select your data source channel (CSV file, Excel file, Excel online or Google sheet)
Create a new document (a CSV file, an Excel file, an Excel online document or a Google sheet).
Copy the data from the csv file into you document and replace the "lorem ipsum..." by your own data.
Update the data source channel and location to use your data instead of the sample file.

Properties

Media Menu widget properties

The following properties, found under "Properties" tab, can be used to customize the widget:

Data
Click on the button (it opens the Data Properties dialog for Spreadsheet). This let the user select the location of the data source.
Locale
Select the locale used for displaying dates and numbers.
Slide duration
Enter the number of seconds each tweet is displayed on the screen.
Transition
Click on the button (it opens the "Transition Properties" dialog) and select a transition effect and its properties.
Looping
Select whether the widget ends after the last tweet or loops to the first one.
Height
Height of the widgets
Number of rows
Enter the number of rows of data to display on a single slide.
Number of columns
Enter the number of columns of data to display on a single slide.
Delay per cell
Enter the number of seconds to wait before displaying the next cell.

Data structure

Menu widget Data structure

The data is composed of five columns:

category
Category to be displayed on the top of the page.

It is possible to use a dedicated line for the category to increase readability of the data, or to repeat the category each line if desired. A new cell will be created for each category.
title
Name of the item. The name should fit on a single line.
description
Description of the item. Up to three lines of text are supported.
price
Price of the item. The currency and formatting will be automatically derived from the chosen locale.
uri
Address of the image to be displayed. This column is only used for the category with name "[media]".

It is possible to use absolute uri (i.e. http://myserver/path/img.jpg) or local file from the project (i.e. /media/img.jpg)

Price List

Description

Price List widget

The Price List widget let you display a set of price list on the screen. The price list is composed of a name and a price and it can be grouped into categories.

The data can be stored in any of the spreadsheet data source format (i.e. CSV file, Excel file on the computer, Excel online or Google sheet) and will be updated automatically by the widget once published on the HMP.

To use your own data source instead of the sample file:

Download the sample csv file from
https://download.spinetix.com/content/elementi/feeds/sample_feeds_price_list.csv
Select your data source channel (CSV file, Excel file, Excel online or Google sheet)
Create a new document (a CSV file, an Excel file, an Excel online document or a Google sheet).
Copy the data from the csv file into you document and replace the "lorem ipsum..." by your own data.
Update the data source channel and location to use your data instead of the sample file.

Properties

Price List widget properties

The following properties, found under "Properties" tab, can be used to customize the widget:

Data
Click on the button (it opens the Data Properties dialog for Spreadsheet). This let the user select the location of the data source.
Locale
Select the locale used for displaying dates and numbers.
Slide duration
Enter the number of seconds each tweet is displayed on the screen.
Transition
Click on the button (it opens the "Transition Properties" dialog) and select a transition effect and its properties.
Looping
Select whether the widget ends after the last tweet or loops to the first one.
Number of rows
Enter the number of rows of data to display on a single slide.
Number of columns
Enter the number of columns of data to display on a single slide.
Item per page
Number of menu item to be displayed on a single page.
Delay per cell
Enter the number of seconds to wait before displaying the next cell.

Data structure

Price List widget Data structure

The data is composed of five columns:

category
Category to be displayed on the top of the page.

It is possible to use a dedicated line for the category to increase readability of the data, or to repeat the category each line if desired.
title
Name of the item. The name should fit on a single line.
price
Price of the item. The currency and formatting will be automatically derived from the chosen locale.

Way Finding

Description

Way Finding widget

The Way Finding widget let you display a list of events taking places in various locations/rooms on screen. Each event has a name, a description and some locations. Moreover arrows icons are used to indicate the direction of the room

The data can be stored in any of the spreadsheet data source format (i.e. CSV file, Excel file on the computer, Excel online or Google sheet) and will be updated automatically by the widget once published on the HMP.

To use your own data source instead of the sample file:

Download the sample csv file from
https://download.spinetix.com/content/elementi/feeds/sample_feeds_way_finding.csv
Select your data source channel (CSV file, Excel file, Excel online or Google sheet)
Create a new document (a CSV file, an Excel file, an Excel online document or a Google sheet).
Copy the data from the csv file into you document and replace the "lorem ipsum..." by your own data.
Update the data source channel and location to use your data instead of the sample file.

Properties

Way Finding widget properties

The following properties, found under "Properties" tab, can be used to customize the widget:

Data
Click on the button (it opens the Data Properties dialog for Spreadsheet). This let the user select the location of the data source.
Slide duration
Enter the number of seconds each tweet is displayed on the screen.
Transition
Click on the button (it opens the "Transition Properties" dialog) and select a transition effect and its properties.
Looping
Select whether the widget ends after the last tweet or loops to the first one.
Number of rows
Enter the number of rows of data to display on a single slide.
Number of columns
Enter the number of columns of data to display on a single slide.
Delay per cell
Enter the number of seconds to wait before displaying the next cell.

Data structure

Way Finding widget Data structure

The data is composed of five columns:

location
Name of the location/room
title
Title of the event.
description
Description of the event. Up to two lines of text are supported. If the description is longer, an animation will be used to display all the text.
direction
One of the following: left, right, bottom, top, bottom-right, bottom-left, top-left,top-right, controlling the arrow to be displayed. The notation north,west, etc and N, W, etc are also supported.

Opening Hours

Description

Opening Hours widget

The Opening Hours widget allows displaying the opening hours information on the screen.

The widget default data is a CSV file stored on SpinetiX server, yet any other spreadsheet format (CSV file, local Excel file, Google Sheets, Excel Online) can be used as data source - for that follow these steps:

Create a new document (a CSV file, an Excel file, an Excel online document or a Google sheet).
Download the sample csv file from
https://download.spinetix.com/content/elementi/feeds/sample_feeds_opening_hours.csv
Copy the data from the sample csv file into you document.
Change the opening and closing times with your own data.
- Make sure to set these column type as plain text to avoid automatic change to data type.
Within the widget, select your data source channel (CSV file, Excel file, Excel online or Google sheet) and the location of your file.

Properties

Opening Hours widget properties

The following properties, found under "Properties" tab, can be used to customize the widget:

Data
Click on the button (it opens the Data Properties dialog for Spreadsheet). This let the user select the location of the data source.
Closed Text
Text to be displayed next to closed days
Header text
Text to be displayed on top of the widgets

Data structure

Opening Hours widget Data structure

The data is composed of five columns:

days
Days of the week to be displayed. The name will be displayed as entered in the spreadsheet by the widget.
open
Flag indicating whether this is an open or a closed day,

The following values are supported: 0, 1
opening_time
Opening time, the hours will be displayed as entered in the spreadsheet by the widget.
closing_time
Closing time, the hours will be displayed as entered in the spreadsheet by the widget.

Today Opening Hours

Description

Today Opening Hours widget

The "Today Opening Hours" widget allows displaying the opening hours information on the screen. The opening hours for the current day are automatically detected and displayed on top of the widget.

The widget default data is a CSV file stored on SpinetiX server, yet any other spreadsheet format (CSV file, local Excel file, Google Sheets, Excel Online) can be used as data source - for that follow these steps:

Create a new document (a CSV file, an Excel file, an Excel online document or a Google sheet).
Download the sample csv file from
https://download.spinetix.com/content/elementi/feeds/sample_feeds_opening_hours.csv
Copy the data from the sample csv file into you document.
Change the opening and closing times with your own data.
- Make sure to set these column type as plain text to avoid automatic change to data type.
Within the widget, select your data source channel (CSV file, Excel file, Excel online or Google sheet) and the location of your file.

Properties

Today Opening Hours widget properties

The following properties, found under "Properties" tab, can be used to customize the widget:

Data
Click on the button (it opens the Data Properties dialog for Spreadsheet). This let the user select the location of the data source.
Locale
Select the locale used for displaying dates and numbers.
Closed Text
Text to be displayed next to closed days
Header text
Text to be displayed on top of the widgets

Data structure

Today Opening Hours widget Data structure

The data is composed of five columns:

days
Days of the week to be displayed. The name will be displayed as entered in the spreadsheet by the widget.

The following formats are supported: single day ("Mon" or "Monday"), Multiple days ("Mon, Wed" or "Monday, Wednesday") or range of days ("Mon-Wed" or "Monday-Wednesday").

Name can be lower case or upper case.

Name can be in any languages. The locale must match the language used in the spreadsheet for today's data to be detected correctly.
open
Flag indicating whether this is an open or a closed day,

The following values are supported: 0, 1
opening_time
Opening time, the hours will be displayed as entered in the spreadsheet by the widget.
closing_time
Closing time, the hours will be displayed as entered in the spreadsheet by the widget.

Spreadsheet widgets in Elementi 2017

From SpinetiX Support Wiki

Contents

Introduction

Video tutorial

Spreadsheet widgets

Configuration

CSV file channel

Excel file channel

Google sheet channel

Google channel connection

Excel Online channel

Microsoft Online channel connection

Blue-Pack spreadsheet widgets

Menu

Description

Properties

Data structure

Media Menu

Description

Properties

Data structure

Price List

Description

Properties

Data structure

Way Finding

Description

Properties

Data structure

Opening Hours

Description

Properties

Data structure

Today Opening Hours

Description

Properties

Data structure

See also