Interface Asset - Excel

    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.

    Excel-categories.jpg

    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.

    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.

    Excel_D_d.jpg

    How to update an already imported Excel spreadsheet

    For Composer

    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 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)

    Excel-2.jpg

    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)
    referencemedia.PNG

    Important Notes

    1. What Composer refers to as Row #1 in your spreadsheet is actually the 2nd row. This is because the first row contains column names.
    2. 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.
    3. 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.
    4. .xlxs files output by other spreadsheet software, such as libreOffice, are not supported. You must use Microsoft Excel 2007 or later.
    5. Do not use "State" or "Row" as a column name. These are a reserved words.
    6. Only PDF documents can be read from an excel reference. IntuiFace will not read Office documents referenced in Excel.
    7. The name of a spreadsheet must contain at least one character.

    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)