Using Apps for Excel for Custom Data Access and Reporting


Microsoft Excel is known for being the #1 tool in the world for business intelligence and reporting. Regardless of what format insights are delivered, users often desire the ability to export and work with data in Excel. For that reason, Excel plug-ins are incredibly popular for custom and proprietary data access. Unfortunately, traditional plug-ins can cripple a user’s ability to upgrade Office. PowerPivot and Power Query have made it easier to connect to a variety of data sources without the need for custom plug-ins. However, many data sources aren't supported in these tools, have complex constraints, or are too complex for an end-user to understand. For these scenarios, Excel-based Apps for Office can help close the gap by getting data into Excel. In this post, I’ll illustrate generic patterns for getting almost any data into Excel using the app model. The video below demonstrates the concepts of the post.

[View:https://www.youtube.com/watch?v=kRmkdCqtwts]

Custom Data Access Scenarios

Microsoft has made heavy investment to position Excel as THE premier tool for business intelligence and reporting. Power Query and PowerPivot are big components of this strategy. With these tools, you users can natively connect to a myriad of data sources including those listed below. It is important to note that Power Query can funnel directly into PowerPivot, so the list represents a combination of supported data source across both tools.

  • Web Page
  • Excel File
  • CSV File
  • XML File
  • Text File
  • Folder
  • SQL Server Database
  • Microsoft SQL Azure Database
  • Access Database
  • Oracle Database
  • IBM DB2 Database
  • MySQL Database
  • PostgreSQL Database
  • Sybase Database
  • Teradata Database
  • SharePoint List
  • Odata Feed
  • Microsoft Azure Marketplace
  • Hadoop File (HDFS)
  • Microsoft Azure HDInsight
  • Microsoft Azure Blob Storage
  • Microsoft Azure Table Storage
  • Active Directory
  • Microsoft Exchange
  • Facebook
  • SAP BusinessObjects  BI Universe
  • Microsoft Analysis Services (Multi-dimensional)
  • Microsoft Analysis Services (Tabular)
  • SQL Server Parallel Data Warehouse
  • Informix
  • OLEDB/ODBC
  • Reporting Services Report

Unfortunately, data access isn’t always so black and white. Often, data structures can be too complex or proprietary to expose to end-users. Others might have complex authentication, throttling, or paging constraints that Power Query and PowerPivot struggle to support. For these scenarios (and for data sources not represented in the list), Apps for Office can play a key role in achieving Excel utopia.

My work with Yammer Analytics helped emphasize the need for these alternate data access patterns. Yammer REST APIs are comprehensive and secure. Although the APIs leverage REST/OData, they do so with paging/throttling and require a bearer token in the header of all request. Power Query and PowerPivot support REST/OData, but not with these additional constraints.

Why Apps for Office

Perhaps you have already solved complex data access challenges by developing Excel plug-ins with traditional technologies such as VSTO, VBA/Macros, etc. So why Apps for Office? Legacy development patterns aren’t going away anytime soon. However, the new app model will be the focus of Office extensibility investments and already has some significant advantages:

  • Apps for Office are developed using open web standards like HTML5/JavaScript and the web platform of your choice (like PHP or Ruby…use it)
  • Because they are delivered using web technologies, Apps for Office work in both the Office client and in the browser with Office Online
  • Apps for Office have an incredibly small client footprint. In fact, the only thing installed on the client is an xml file describing the app
  • Apps for Office are easily discoverable through private corporate catalogs or the public Office Store, which provides a marketplace for apps developed by Microsoft Partners

Client-Side Data Access

Excel tables will be the primary data structure we use to populate data from an app. Apps for Office interactive with Excel tables through the Web Extensibility Framework (WEF). WEF provides JavaScript APIs to read, write, and bind to tables using the Office.TableData data type. This data type defines the raw data and header definitions for an Excel table. I’ve developed two Office.TableData extension methods to generically populate it from any JSON array. This is the primary strategy of the pattern...get the data into JSON (regardless of data source) and we can easily inject it into Excel with these two extensions.

The addHeaders extension method initializes the table headers based on an object parameter. This object will typically be the table’s first row of data. The method will loop through and add headers/columns for each property of the object, ignoring complex data types (ex: typeof == object). Complex data types are ignored as they could represent one:many relationships between tables. This would be an interesting enhancement for a v2, but for now I’m keeping it simple with a single table.

addHeaders Extension to Office.TableData

//extension to Office.TableData to add headers
Office.TableData.prototype.addHeaders = function (obj) {
    var h = new Array();
    for (var prop in obj) {
        //ignore complex types empty columns and __type from WCF
        if (typeof (obj[prop]) != 'object' &&
            prop.trim().length > 0 &&
            prop != '__type')
            h.push(prop);
    }
    this.headers = h;
}

 

The addRange extension method appends rows to the Office.TableData based on a JSON array parameter. This method was specifically designed to support multiple appends, as would be common with throttled/paged results. The addRange method only looks at object properties that are defined as headers in the TableData object. As such, the headers should be set (manually or via addHeaders) prior to calling addRange.

addRange Extension to Office.TableData

//extension to Office.TableData to add a range of rows
Office.TableData.prototype.addRange = function (array) {
    for (i = 0; i < array.length; i++) {
        var itemsTemp = new Array();
        $(this.headers[0]).each(function (ii, ee) {
            itemsTemp.push(array[i][ee]);
        });
        this.rows.push(itemsTemp);
    }
}

 

The sample below outlines the use of these extension methods against JSON returned from a REST call.

  1. Initialize a new Office.TableData object
  2. Use the addHeaders extension method to define the columns/headers based on the first row of JSON data
  3. Use addRange extension method to load all the JSON data into the Office.TableData object
  4. Inject the Office.TableData into Excel by calling our setExcelData function
Using Extensions and Loading Excel Table
//wire up client-side processing
$('#btnSubmit1').click(function () {
    $.ajax({
        url: '../Services/Stocks.svc/GetHistory?stock=' + $('#txtSymbol1').val() + '&fromyear=' + $('#cboFromYear1').val(),
        method: 'GET',
        success: function (data) {
            //initalize the Office.TableData and load headers/rows from data
            var officeTable = new Office.TableData();
            officeTable.addHeaders(data.d[0]);
            officeTable.addRange(data.d);
            setExcelData(officeTable);
        },
        error: function (err) {
            showMessage('Error calling Stock Service');
        }
    });
    return false;
});

 

Using setSelectedDataAsync to Load Excel

//write the TableData to Excel
function setExcelData(officeTable) {
    if (officeTable != null) {
        Office.context.document.setSelectedDataAsync(officeTable, { coercionType: Office.CoercionType.Table }, function (asyncResult) {
            if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                showMessage('Set Selected Data Failed');
            }
            else {
                showMessage('Set Selected Data Success');
            }
        });
    }
}

 

Server-Side Data Access

The client-side pattern works great for REST/OData scenarios with unique authentication/paging/throttling constraints. However, many data sources are not or cannot be exposed as REST/OData. For these scenarios, server-side data access methods must be used. Server-side data access opens up the door to connect with anything .NET supports. That said, the APIs available to Apps for Office can only interact with Excel workbooks client-side. Thus, the server-side strategy is to serialize data into JSON, inject the JSON as script on the page, and then use client-side APIs to integrate the JSON with the Excel workbook as the app loads. The code below shows a server-side button click event that retrieves data, serializes the data as a JSON string, and injects the JSON into the page using the Page's ClientScriptManager.

Server-side Data Access and JSON Serialization

protected void btnSubmit2_Click(object sender, EventArgs e)
{
    //use the stock service to get the history
    //although this samples a local service...
    //ANY data access .NET supports could be used
    Services.Stocks s = new Services.Stocks();
    var history = s.GetHistory(txtSymbol2.Text, Convert.ToInt32(cboFromYear2.SelectedValue));
    using (MemoryStream stream = new MemoryStream())
    {
        //serialize the List<StockStats> to a JSON string
        DataContractJsonSerializer ser = new DataContractJsonSerializer(typeof(List<Services.StockStat>));
        ser.WriteObject(stream, history);
        stream.Position = 0;
        StreamReader sr = new StreamReader(stream);
        var json = sr.ReadToEnd();

        //output the json string of stock history as javascript on the page so script can read and process it
        Page.ClientScript.RegisterStartupScript(typeof(Default), "JSONData", String.Format("var jsonData = {0};", json), true);
    }
}

 

The following code shows a client-side check for JSON as the app loads. Notice it handles the JSON identically to our client-side data access sample. That is the beauty of getting all data into JSON format.

Checking for JSON Client-Side on App Load

$(document).ready(function () {
    //check for json data loaded during server-side processing
    if (typeof jsonData == 'object') {
        //initalize the Office.TableData and load headers/rows from data
        var officeTable = new Office.TableData();
        officeTable.addHeaders(jsonData[0]);
        officeTable.addRange(jsonData);
        setExcelData(officeTable);
    }

    //wire up other client-side events...

 

Apps Delivered via Templates

One of the powerful concepts enabled through the app model is the ability to combine an App for Office with an Office template. This allows application logic to be packaged and distributed with pre-configured documents. The PayPal Invoicing App is a showcase example of this app/template packaging pattern. Although the PayPal app is more transactional in nature, the pattern also works in delivering reporting scenarios. Ultimately, the app provides a wizard for retrieving data and inserting it into Excel and the template uses the data with pre-configured models and visualizations. I recently used this pattern to deliver a Yammer Group Analytics app that is illustrated in the video below. Again, this is a very powerful way to deliver self-service reporting with pre-canned visuals.

[View:https://www.youtube.com/watch?v=OlP3hd3XEqo]

Final Thoughts

If you deliver or support an application with reporting needs, chances are you already have demands for getting application data into Excel. I hope this post helped illustrate how to Excel-enable applications that even Power Query and PowerPivot struggle with. Apps for Office can help close these reporting gaps by delivering high-value extensibility to Office without the invasive installation and niche technology of traditional Office plug-ins. Apps for Office are lightweight, work across client/browser, and are easy to develop. In fact, the solution outlined in this post was delivered in well under 100 lines of JavaScript. You can download the solution from the Office 365 Development Patterns and Practices of GitHub.

Comments (3)

  1. Eric Vogelpohl says:

    Nice article, great work as always.  

    "Others might have complex authentication, throttling, or paging constraints that Power Query and PowerPivot struggle to support"…  

    Spot on.  Please impress upon the Power Query folks to focus on this issue.  PQ and PP are killer apps for the power analyst.  It's becoming more and more of an issue that long running queries, queries that require paging (E.g. 1-9999 rows, then 10000-19999, then…) and queries that require OAUTH (Twitter, Google Analytics, etc…) represent a stopping point for enriching BI solutions.

  2. custom developers says:

    Using apps for excel for custom data access and reporting is little bit tricky for new people. I hope by using this video anyone can use app for accessing a custom data…

  3. Doyle Johnson says:

    Thank you so much Richard. I produced my first app for Excel with the aid of your work here. For those that follow, I would add that if you want user friendly column headings you can set them with an array yourself and then on your model class reference System.Runtime.Serialization, mark the class with the DataContract attribute and your properties with [DataMember(Name = "Your Friendly Header Name")].

Skip to main content