How to develop an Excel Task Pane App

Instead of an article, this is more like a memoir…
“Ty Anderson: An Apps for Office developer memoir”.

Apps for Office – Not your typical Office Object Model

The new Apps for Office development model provides plenty of new possibilities for Microsoft Office developers. Although new, they give the appearance of familiarity. Do you think I’m crazy? Well maybe, but consider the following:

• The content apps are akin to dropping an ActiveX control (for example, a calendar control) onto an Excel spreadsheet.
• Mail apps are not too different from Outlook form regions.
• Task pane apps are just like… well… task pane apps.

Or are they? Certainly they are similar…

• They have the same name as the VSTO and COM-based counterparts.
• They are vertical UI elements.
• They provide access to the Office host’s object model.
• Etc.

Despite these obvious similarities, the Task Pane app is familiar but is very different from anything Office developers have built previously. The main difference is the Office Javascript library (Office.js). This library servers as the gateway to the Office object model and is severely limited. So limited that you find yourself not believing the depths of its limits and attempting unreasonable workarounds.

When I say ‘you’… I mean ‘me’.

A good idea but impossible

Originally conceived, the Excel Table Templates app was meant to be something I dubbed “Easy Rows”. The idea of the app was to:

1. Recognize anytime the user selected a cell that comprised part of an Excel table.
2. Auto-populate an Excel task pane with a label and text box for each cell in the current table row.
3. Allow the user to edit the row by editing the data in the task pane.

The idea was to create an Excel app that would make it easier to work with really, really, really long rows of data in a spreadsheet. The trouble began instantly. The details are boring so I’ll summarize. Using the Office.js library…

• You can recognize when the user has selected a cell within a table. BUT…
• You cannot navigate within the table.
• You cannot read any data outside the selected range of cells.
• You can determine if the table has a header row.
• You cannot actually read the header row’s column titles.

I refused to believe the “cannots” and spent too many hours reading and re-reading the Office.js documentation on MSDN. I finally conceded and moved on to what became today’s article.

The Table Templates Excel task pane app

Despite all the frustration, I discovered it is really easy to insert data into an Excel file. And, given the API’s limitations, I decided one of the best applications of this new development model is to use for inserting content into a file. So, why not build an app that makes it possible to quickly select and insert a table from a library of templates? What’s the worst that could happen?

Here’s the design:

To build this app, you need the following tools:

• Visual Studio 2012
• With Apps for Office project templates
• Excel 2013

Let’s build this thing.

Creating the Table Templates App for Excel 2013

The Table Templates app uses the Apps for Office 2013 Visual Studio 2012 template. If you don’t see this template in the New Project dialog box, be sure you installed all the pre-requisites listed above. To create Table Templates, just follow these steps:

1. With Visual Studio 2012 open, select File > New Project from the main menu.
2. In the New Project dialog box, select the Templates > Office/SharePoint > Apps node.
3. Click the App for Office 2013 project template and name the project TableTemplates. Click the OK button.
4. The Create App for Office dialog box will display. Select the Task pane app in option and only enable Excel as a host Office application. Click Finish to create the project.

Visual Studio will create the project and display the TaskTemplates.html file in the code window. This file is the HTML that defines the task pane display. We want this file to contain a couple of tables:

• Table 1: This table contains the app icon and title.
• Table 2: This table contains the images along with their labels. The images will act as buttons. When the user clicks them, we will call a custom JavaScript method.

Both tables reside within the <Body> tag. Listing 1 contains the HTML and is ready for you to copy and paste into your TableTemplates.html file. Go ahead and paste it over the existing content in the <Body> tag.

Listing 1.

<div id="Content">
       <table class="maintable">
        <tr style="background-color: #0072C6;height: 30px;">
            <td style="height: 60px;width: 55px;text-align: center;vertical-align: middle;">
                <img src="../Images/TableTemplateIcon.png" alt="logo" width="50" height="50" style="float: right;padding-right: 5px;padding-top: 3px; padding-left: 5px" />
            </td>
            <td style="background-color: #0072C6;font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;font-size: 18px;font-weight: bold;color: #FDFDFD;text-align: left;vertical-align: middle;padding: 0;margin: 0;height: 30px;">TABLE TEMPLATES</td>
        </tr>
 
    </table>
 
    <table style="height:100%;" id="tableContent">
		<tr><td height="35" colspan="2" >Select a template to insert into your spreadsheet.</td></tr>
        <tr>
            <td width="17%" height="52" > <img src="../Images/TableTemplateIcon-Tasks.png" width="50" height="50" style="cursor: pointer;" onclick="setData('task');"></td>
          <td width="83%" >Tasks Template</td>
        </tr>
 
 
        <tr>
            <td height="52"><img src="../Images/TableTemplateIcon-Contacts.png" width="50" height="50"  style="cursor: pointer;" onclick="setData('contact');"></td>
          <td >Contacts Template</td>
        </tr>
 
 
        <tr>
            <td height="52" ><img src="../Images/TableTemplateIcon-Expenses.png" width="50" height="50" style="cursor: pointer;" onclick="setData('expense');"></td>
          <td >Expenses Template</td>
        </tr>
 
        <tr>
            <td height="52" ><img src="../Images/TableTemplateIcon-Issues.png" width="50" height="50" style="cursor: pointer;" onclick="setData('issue');"></td>
          <td >Issues Template</td>
        </tr>
 
      <tr><td colspan="2">&nbsp;</td></tr>
  </table>
 </div>

The key component is the content in the <IMG> tags:

<img src="../Images/TableTemplateIcon-Tasks.png" width="50" 
    height="50" style="cursor: pointer;" onclick="setData('task');">

Each image tag has a method defined in its onclick attribute. When the user clicks the image, the app will execute the setData method. Listing 2 contains the code for setData.

By the way, feel free to use your own images (or for that matter buttons). At the same time, you are more than welcome to use the images I created. They are part of the sample code for this article.

Listing 2.

// Writes data to current selection.
function setData(tableType) {
    Office.context.document.setSelectedDataAsync(createTablefromTemplate(tableType));
}

I cheated and used this method as it 1) was included in the app by default and 2) did almost exactly what I needed it to do. The only changes I made from the original version included by Visual Studio is to change the method’s parameter name to tableType and then call the createTablefromTemplate method. By calling createTablefromTemplate, we can create the table template we want and then insert it into the spreadsheet.

So how does createTablefromTemplate work? Magic. Listing 3 reveals its secrets. Open the TableTemplates.js file and insert its code.

Listing 3.

function createTablefromTemplate(tableType) {
    var tableData = new Office.TableData();
    switch (tableType) {
        case "task":
            tableData.headers = [
                ['Task Title', 'Start Date', 'Due Date',
                'Status', 'Category', 'Assigned To']];
            tableData.rows = [
                ['Sample Task #1', '=Today()', '=Today()+1', 'Not Started', 'Work', 'Me'],
                ['Sample Task #2', '=Today()', '=Today()', 'Not Started', 'Work', 'Me'],
                ['Sample Task #3', '=Today()', '=Today()', 'Not Started', 'Work', 'Me']];
            return tableData;
            break;
 
        case "contact":
            tableData.headers = [
                ['First Name', 'Last Name', 'Title', 'Phone Number', 'Email',
                    'Street', 'City', 'State', 'Zip']];
            tableData.rows = [
                ['John', 'Doe', 'Generic Person', '212-555-1212', 'john@doe.com',
                    '1234 Main', 'Oakland', 'CA', '94601']];
            return tableData;
            break;
 
        case "expense":
            tableData.headers = [['Expense Date', 'Category', 'Client/Project', 'Amount']];
            tableData.rows = [['=Today()', 'Meals', 'Custom App for Office', '$55.00']];
            return tableData;
            break;
 
        case "issue":
            tableData.headers = [
                ['Issue', 'Status', 'Priority', 'Area', 'Due Date',
                    'Date Resoled', 'Notes']];
            tableData.rows = [
                ['Severely limited Office.js API', 'In-Progress', 'Normal',
                    'Development', '=today()+7', '', 'I wish the API was more extensive.']];
            return tableData;
            break;
    }
}

This is a simple method that branches based on the value of the tableType parameter. We have four template choices (task, contact, expense, & issue tables). And it doesn’t matter what template we specify, the steps are the same. First, the method creates an array that will serve as the table’s header row. Second, the method creates a sample data row so the user gets a little more bang for the buck and can see how they should use the table. Third, and last, the method returns the table as the function value.

Demo Time!

That’s all the code we need. Let’s demo it!

Close your eyes…
Cross your fingers…
Stand on one toe (this is the tricky part)…
—AND—
CLICK F5!

If all goes well, Excel will open and your task pane app will look like this:

***
The Apps for Office developer model has plenty to offer. The trick is to take the APIs at face value. They do what they do. Nothing more… nothing less. Don’t try to trick them out or convince yourself a scenario should be possible because it’s possible with VBA, COM add-ins, and VSTO. The Office JavaScript library is a new animal. We haven’t seen it before. So read about it. Observe it. And see how it acts in the wild.

You may also be interested in:

• Excel 2013 development – 7 questions developers should ask
• Office UX guidelines for Excel 2013 content apps and Word 2013 task pane apps

Available Downloads:

Table Template app for Excel 2013 (C#)