Wednesday, January 16, 2013

Office apps development - Getting started building an Excel task pane app

Apps for Office are a new type of apps which allow you to extend Office 2013 client applications  using a combination of web technologies (Javascript, CSS and HTML) and the new Javascript API for Office. If you are new to this I recommend that you check out these apps for Office and SharePoint 90 second videos. 

Afterwards you can immediately dive in and check out Build apps for Office. In this series of blog posts I will explain how I learned to built an Excel Content app and issues I faced when trying to built this type of app. There are different types of Office 2013 apps – check out.

A good place to start building a task pane app is Sample task pane and content app walkthroughs – which shows you how to build a Bing Maps content app.

The Excel content app I will build is a variant of the Bing Finance apps – which is basically aimed at integrating stock data into Excel. Where the Excel 2013 app which I will illustrate will differ in a first version:

  • Allow the app to work with Excel sheets which already have tables to add and bind to these existing tables and refresh data in these apps
  • Incorporate financial data from multiple sources (not only Bing Finance)

Usage scenario is the following:

  • User selects an existing Excel table which contains stock information – every row is representing a stock. For each stock there should be a column which contains the stock symbol code. User defines which column contains this stockdata and in which column he wants to update the stock price.
  • Provide a button to update the table with the latest stock price for each stock in the table
  • When a row is selected in the Excel table – show extra information in the task pane.

In the sample code from the Bing Maps content app - you notice that you need to create bindings to interact with specific sections of your Office apps – see Binding to regions in a document or spreadsheet . This is something I will need as well for the Excel table with stocks.

There are 3 types of bindings which you can create and from which you can read data  ( see BindingType enumeration) – in this case I want to use Office.BindingType.Table and read information from that binding afterwards. First create the binding:

Office.context.document.bindings.addFromSelectionAsync(Office.BindingType.Table, { id: 'stockdata' }, function (asyncResult) {
        if (asyncResult.status == Office.AsyncResultStatus.Failed) {
            write('Action failed. Error: ' + asyncResult.error.message);
        } else {
            write('Added new binding for table with' asyncResult.value.columnCount + ' columns');

Afterwards you can retrieve the binding again and read data from that binding. Unfortunately the sample code in the MSDN article only showed how to read data from a text binding – so it took me some time to find out how to read it from TableData object.

//Retrieve binding - if no binding - provide warning
Office.context.document.bindings.getByIdAsync("stockdata", function (asyncResult) {
   if (asyncResult.status == Office.AsyncResultStatus.Succeeded) 
         //Loop over table rows
           if (asyncResult2.status == Office.AsyncResultStatus.Succeeded)
               var rows = asyncResult2.value.rows;
               for (i = 0; i < rows.length; i++)

In a next blog post I will explain how you can you use events in combination with the binding object.

1 comment:

Velma said...

This is cool!