Displaying data from a Microsoft Excel worksheet

Problem:

You want to build a Klip that uses a Microsoft Excel worksheet (.xls, .xlsx) as its data source.

Solution:

In Klipfolio Dashboard version 5.2 and above, Klips can display data from an Excel workbook just like Klips that use XML or CSV data sources.

The general structure of Klips that display data from an Excel worksheet is the same as Klips that use other data sources. See Klip Development Fundamentals for details if this is your first time developing a Klip.

---------------- Important Notes ----------------

• Klipfolio Dashboard version 5.2 supports Excel workbooks with the XLS file extension.
• From Klipfolio Dashboard version 5.3, Excel 2007 workbooks with the XLSX file extension are also supported.
• The Excel engine of Klipfolio Dashboard first looks for the Microsoft Excel 2007 driver that is installed with MS Office 2007. This Microsoft driver supports both the XLS and XLSX extensions; therefore, an Excel Klip can access either of these extensions as long as your users have Microsoft Office 2007 installed. If this driver is not found on the user's computer, Klipfolio Dashboard's Excel engine will look for the 2003 driver, which supports only XLS.
• Klipfolio Dashboard gets the displayed values from the worksheet, whether they were directly input or are results of formulas.
• An Excel workbook file is locked when it is opened in Excel, which prevents others from accessing it fully. In order to allow access to it by multiple Excel Klips, a) make sure no one has it opened, or b) share your workbook. For details on how and its implications, refer to Microsoft's documentation. (Excel 2007, Excel 2003)
• The workbook file must be located on a local or network drive, not on a web server.
• Empty rows and columns are ignored and are not displayed in the Klip.
• In a given column, the majority data type of the first 8 rows is used as the default type, so any cell in the column that is not of the the majority data type is ignored. For example, if the majority data type of the first 8 rows of column heading A was Number, any cell under column A that is not a Number is ignored. See Microsoft Support page for a workaround.
• Embedded objects in the worksheet, such as charts, images, and diagrams, are ignored.
• Klipfolio Dashboard treats links in the worksheet as string data.

A sample Excel workbook used in this article can be downloaded. The screenshot below displays its content and the terminology used in the article:

Summary of Development Steps

1. Specify the following information about the worksheet using XML or JavaScript:

• file path to the desired Excel workbook
• name of the worksheet
• data range that includes the header row.

2. Write the <style> section that matches your data. Its format is the same whether you used XML or JavaScript to specify the content source.

• The first row within the specified data range is counted as a header row; therefore, its content is not considered as an item in a Klip.
• Use the column headings (A, B, C, etc.) as the selector.
• Specify "row" as "type: item" at the bottom of the <style> block.

1. Should I use XML or JavaScript?

If this is your first time developing an Excel Klip, it is recommended that you use the JavaScript template included in the Sample Excel Klip using JavaScript, as it provides you with ways to troubleshoot your Klip.

Here are some pros and cons of using XML and JavaScript to specify the data source:
XML:

• You add information about the worksheet in the <contentsource> block using <excel>, <file>, <sheet>, and <range> parameters. Klipfolio Dashboard uses that information to open the Excel worksheet, converts it to XML, and displays it in the Klip according to its <style> settings.
• It requires less coding, but the returned raw XML data cannot be reviewed in the Debug Window.

JavaScript:

• You write the entire steps from setting the file path, sheet and range to returning the data in XML format using the Engines.Excel API.
• There is a template in which you can simply replace the file path, sheet and range.
• Use JavaScript if you want to change any part of the process dynamically. This would be useful when, for example, you want to ask your users to supply their own path to the worksheet (be sure to write a <style> block that supports all possible worksheet ranges in this case).
• When uncertain about the returned raw XML data format, trace output can be studied in the Debug Window.

Using XML (<contentsource>) to specify content source

<html>

<contentsource>
    <excel>
        <file>C:\temp\example.xls</file>
        <sheet>2009</sheet>
        <range>B5:G12</range>
    </excel>
</contentsource>
</html>

<html>

<file>c:\folder1\example.xls</file>
</html>

Take a closer look at the Sample Excel Klip using XML by downloading it from klipfolio.com and opening it in a text editor such as Notepad.

To change the sample Klip to use your own file,

• Change <file> location
• Change <sheet> name
• Change <range>
• Read the Stylesheet section below to update the <style> section.

Using JavaScript (<klipscript>) to specify content source

The following klipscript template opens a worksheet, converts it to XML, and passes it to Klipfolio Dashboard's data engine, which will then parse it using the <style> settings of the Klip.

<klipscript>
    <![CDATA[

// Don't forget to escape the backslashes
var g_file = "C:\\temp\\example.xls";

function onRefresh() 
{
    // Open and get data from the worksheet.
    // Parameters are file, sheet and range.
    var excel = Engines.Excel.open (g_file, "2009", "B5:G12");

    // Check if the Excel Engine was able to successfully convert the data to XML format.
    if (! excel.xml)
    {
        // If unsuccessful, output the lasterror into the Debug Window for further investigation.
        traceln ("EXCEL ERROR - > " + excel.lasterror);

        return false;
    }

    // Output the XML data as a reference when writing <style> settings.
    traceln ("EXCEL RESPONSE - >");
    traceln (excel.xml);

    // Use Klipfolio Dashboard's Data Engine to parse the XML data using <style> settings.
    return Engines.Data.process (excel.xml);
}
    ]]>
</klipscript>  

Take a closer look at the Sample Excel Klip using JavaScript by downloading it from klipfolio.com and opening it in a text editor such as Notepad.

To change the sample Klip to use your own file,

• Change var g_file = "C:\\temp\\example.xls"; to point to your own file's location.
• Change "2009" to your sheet name and "B5:G12" to the desired range in the following line:

var excel = Engines.Excel.open (g_file, "2009", "B5:G12");

• Read the Stylesheet section below to update the <style> section.

2. What if the data range might change over time?

In some cases, you may not know the exact range of the data at the time you develop your Excel Klip. In such cases, whether you are using JavaScript or XML to specify the content source, keep the following rules in mind:

• You can specify just the file location. The Klip will assume that the sheet name is Sheet1 and gather all the data available in this sheet whenever the Klip is refreshed.
• You can specify just the file location and the sheet name. The Klip will retrieve all the data available in that sheet whenever the Klip is refreshed.
• If you are specifying the data range, you must also specify the file location and sheet name.

3. Stylesheet

<style>
    C {
        type: number;
        itemcol: 1;
        label: "Q1";    
        format: "currency($)";
    }
    D {
        type: number;
        itemcol: 2;
        label: "Q2";    
        format: "currency($)";
    }
    ...
    row {
        type: item;
        definition: all;    
    }
</style>

Be sure to define row at the bottom of the <style> block to tell Klipfolio Dashboard that each row of data in the worksheet is an item.

4. Troubleshooting

For troubleshooting tips, see the how-to article Troubleshooting Excel Klips.