Description
Your IntuiFace experience can write to and import data from an
Excel spreadsheet - stored in the .xlxs format -
thanks to the Excel Interface
Asset (IA). Each tab in a workbook - i.e. each spreadsheet - is
represented by a unique Excel IA. See image below.
The default visual representation of each Excel row is a
horizontally-oriented Group
collection filled with either an Image or Text asset for each
column. These Group collections are placed within a vertical Asset
Grid collection to vertically represent all rows in the
spreadsheet.
This feature is available on all platforms supported by
IntuiFace, including Windows PCs, the iPad and Android
tablets. It is not necessary for Microsoft Excel to be
installed on devices running Player. As for Windows PCs running
Composer, it is only necessary to install Microsoft Excel if you
need to directly edit the contents of worksheets in an Excel
workbook.
Video demo
Have a look at the video below to see how the Excel interface
asset works and how you can change the
data template used to display the contents of each row.
How
to create an Excel spreadsheet for use within IntuiFace
Here are the rules to respect when creating a spreadsheet within
Microsoft Excel:
Each spreadsheet should represent a single data table. If you
have multiple data tables to represent, use one spreadsheet per
table.
The first row of each spreadsheet must contain the column names
of your data structure. These names will appear as properties and
action parameters associated with your worksheet in IntuiFace. Each
column name must be unique; no repeats.
Every other row of the spreadsheet must represent one item in
your data table. There should be no blank rows in the middle of
your list.
The name of the spreadsheet itself must contain at least one
character. A spreadsheet with a name comprised only of numbers will
not
import successfully.
Here are the spreadsheets used in our webinar
"Boost Your IntuiFace Experiences With Excel". You can reuse /
enhance / modify them.
Gallery
Photos: Create a Gallery-Photos folder next to
your Excel workbook. In that folder put images referenced within
the workbook
Product Catalog Template: Create an Images
folder next to your Excel workbook. In that folder put images
referenced within the workbook.
User
form: Add as many columns as you need to
create your form fields.
How to import an Excel
spreadsheet
To import an Excel spreadsheet you can either:
From the Interface Assets panel, click the
Add an Interface Asset button, click
Excel then browse your PC's file system to select
your Excel workbook
OR
Drag-and-drop the Excel workbook from File Explorer into
Composer.
A drag & drop on any of the Composer panels will create
Excel interface assets for each spreadsheet within the
workbook.
A drag & drop on the scene will both create Excel interface
assets for each spreadsheet and will create a
default visual representation - a [data
template[(/kb/interface-assets/data-template-for-collections-populated-by-interface-assets)
- for each spreadsheet.
New with IntuiFace Version 5.2.5
If your Excel spreadsheet references graphic media stored
locally, such as images placed in a folder located next to the
Excel workbook, drag & drop the Excel workbook
and the folder containing your media files at the
same time. Everything will be imported correctly into your
IntuiFace project. See this
section below for a detailed discussion about referencing
external media from within an Excel spreadsheet.
How to
update an already imported Excel spreadsheet
For
Composer, starting with IntuiFace Version 5.2.5
When importing an Excel spreadsheet into Composer, the
corresponding Excel workbook is copied into a sub-folder of your
IntuiFace project. You can easily locate your Excel file or even
edit it in Excel by using the context menu of the Excel interface
asset. Right-click any Excel interface asset in the Interface
Assets panel and select either Edit in Excel or
Open file location.
Note:
The Excel interface asset - and associated data templates -
will update five seconds after the Excel workbook is saved and
closed in Excel.
Microsoft Excel must be preinstalled in order to edit Excel
spreadsheets.
When your Excel file is opened in Microsoft Excel, perform any
of the changes described below. In all cases, you need to close the
file in MS Excel for the changes to be recognized by Composer.
add or remove rows: all existing bindings will
be preserved.
add or remove a column: New properties
corresponding to new columns will appear. Bindings to removed
columns will be lost.
add a new sheet: a new Interface Asset
corresponding to this sheet will be created.
rename a column or a sheet: all existing
bindings on modified columns / sheets will be lost.
remove a sheet: all existing bindings for the
removed sheet will be lost.
For Composer,
prior to IntuiFace Version 5.2.5
The type of change made to an already-imported Excel spreadsheet
determines how the Excel interface asset is notified of the
change.
If you only add or remove rows - i.e. no
columns are added or removed - then take either of the following
two approaches. In both cases, the Excel interface asset will
update five seconds after the change is submitted.
Replace the original spreadsheet with the new one using, for
example, copy/paste. You will find the original spreadsheet within
the IntuiFace project in the subfolder
Files\InterfaceAssets\[ExcelFileName]_xlsx.
OR
Open the existing spreadsheet in Excel, make your changes, then
save the spreadsheet AND close Excel. As noted previously, the
original spreadsheet is located within the IntuiFace project in the
subfolder
Files\InterfaceAssets\[ExcelFileName]_xlsx
If you add / remove one or more columns or
sheets, this fundamental change to the structure of the
Excel file requires you to go through the import process described
above again.
Do not make these changes in the originally
imported Excel file. These changes will not be noticed.
The safest procedure is to copy / paste the current version of
your Excel file from the project folder to another location (e.g.
your desktop), apply your modifications, then import this modified
workbook into Composer.
Keep the same name for the Excel file and each of the
worksheets it contains. This will ensure all bindings created for
the original Excel file will continue to work.
NOTE: If you have already imported an Excel
file and plan to update it, do not rename already imported
spreadsheets. If you still do so, you will have to
recreate every binding made using the original file.
For Player for Windows
You can find the Excel files within the IntuiFace project in the
subfolder Files\InterfaceAssets\[ExcelFileName]_xlsx.
You can open the Excel file and add or remove rows
but you should not change the structure (column /
sheets) of the Excel file for the Experience to remain working
properly.
You can also replace the Excel file with a new one, provided
they have the same structure (column / sheet names).
For Player for
Kiosks & Player for Tablets
The ability to directly update an Excel spreadsheet on iPad,
Android, Chrome, Samsung SSP and webOS Signage devices is not
supported. To update the spreadsheet for these Players, make the
changes using Composer - see previous section - and then redeploy
the experience.
Properties, Triggers &
Actions
Properties
The Excel IA exposes an unlimited number of properties:
Rows: a list representing every row compliant
with all active filters. Each row except the first represents one
item and each column represents a different property of that item.
The first row is ignored as it must contain column titles, text
used to represent each "property" in the spreadsheet. What Composer
refers to as Row #1 is actually the 2nd row of the
spreadsheet.
Number of rows, a read-only property
containing the number of rows in the spreadsheet satisfying all
active filters. In other words, if any filters are active, the
"Number of rows" property will count only the rows satisfying those
filters. As a read-only property, this value is only accessible via
binding.
File path: the location of your Excel file in
the project. You can use this path to add your Excel file in a
sharing queue of a Sharing
Interface Asset. This would enable you to email your
spreadsheet or save it to any network accessible drive.
Actions
The following actions can be
called by any trigger.
Edit
Add row switches from read to write
mode so you can add data into your Excel spreadsheet. The action
parameters correspond to the data content of every column of the
Excel file.
Delete row remove a specified row. All
rows below the deleted row will be moved up - i.e. their row number
will decrease by 1.
Update cell enables overwriting of cell
contents with new content.
Update row: enables overwriting an entire row
with new content.
It is not possible to add columns using an action. Only rows can
be added.
Sort / Filter
Add filter: filters your spreadsheet by
applying one criterion to one column. Here, each column is referred
to as a "property" since this is how they are treated. To filter by
multiple criteria, call the Add filter action
multiple times, once for each criteria. You can call the
Clear filters action (see below) to remove
filters. You can also check the Clear column
filters option to remove filters on this column before
applying the new one. NOTE: Wildcard characters are not supported.
Column: the column of the Excel file on which
the filter will be applied
Filter: the kind of filter to apply. You can
choose among the following options:
Equals
Does not equal
Greater than
Less than
Contains (case sensitive)
Contains (case insensitive)
Value: the value to use within the
filter;
Clear column filter: if checked, any
pre-existing filter on this column is cleared before applying the
new one. If not checked, the filters are accumulated.
Add range filter: same as above with 2
criteria.
Clear all filters remove all filters
set using the Add filter action.
Clear column filters remove all filters
on a specific column set using the Add filter
action.
Sort sorts overall spreadsheet based on
the order applied to a specified column
The image below identifies all actions available for the Excel
interface asset. A reminder that what is referred to here as
"Worksheet 1" is just the name of the spreadsheet in the example
used. For your own projects you will see whatever name you have
provided a given spreadsheet.
(click to enlarge)
Referencing
Media Files Not Listed in the Content Library
You can reference either a Web-hosted or local image, video or
PDF document in an Excel spreadsheet cell.
For a Web-based file: simply enter the media URL in the Excel
cell. You can see this approach in the video at the top of this
article. An example URL:
http://content7.flixster.com/movie/11/16/93/11169357_pro.jpg
For a local file, you should use a relative path, with the
in-project location of your Excel file as the starting reference
point. See this note below for more
information about where the spreadsheet is stored in your
experience. Alternatively you may use an absolute path like
c:\temp\myImage.png. But those exact paths must exist
on all PC running the player - and those paths will never work on
player for Tablet or Kiosk.
We highly recommend to use of relative paths to
make your experiences easier to deploy across multiple devices. A
best practice is to create a media folder next to your Excel file
located in
{Project_Folder}\Files\InterfaceAssets\{YourExcelName_xslx}.
Give this folder any name you wish. For example, "image". You can
then enter the following path in your Excel cells to reference an
image: image/MyImage.png.
(click to enlarge)
Important
Notes
What Composer refers to as Row #1 in your spreadsheet is
actually the 2nd row. This is because the first row contains column
names.
When selecting an Excel workbook while creating an Excel
Interface Asset, the corresponding .xlxs file is
copied and placed into your project at the following location:
C:\[Project_Path]\[Project_Name]\Files\InterfaceAssets\[Excel_Filename]_xlxs
As a result, all modifications performed by third party services
must be applied to this local copy. (The Add row
action also operates on this local copy.) The original file is not
monitored by Composer or Player.
When modifying the local copy of your Excel workbook - either
manually or via an external service - the workbook must be both
saved and closed before changes are taken into account by
IntuiFace. Changes are recognized by IntuiFace approximately five
seconds after closing the workbook.
.xlxs files output by other spreadsheet
software, such as libreOffice, are not supported. You must use
Microsoft Excel 2007 or later.
Do not use "State" or "Row" as a column name. These are a
reserved words.
Only PDF documents can be read from an excel reference.
IntuiFace will not read Office documents referenced in Excel.
Experience Samples
If you want to rebuild the experience in the above video on your
own, you can download the Excel file used in the video from
here.
In Composer's Experiences panel you will find - in the Samples
tab - several examples using the Excel Interface Asset as a data
source:
DIY Shopping Catalog
Restaurant Menu
Endless Aisle
Interactive Maps
Photo Exhibition (designed for iPad and Android tablets)
Local attractions (portrait mode)