Building an Excel 2016 Add-In with Angular and Enhanced office.js

Scot Hillier

by Scot Hillier on 9/28/2015

Share this:
Print

Article Details

Date Revised:
9/30/2015

Applies to:
Angular, Excel add-in, office.js


Recently, Microsoft announced the public preview of an enhanced version of office.js, which is the JavaScript library used to communicate from an Office add-in back to the hosting Office product. The enhanced version of the office.js library adds new functionality for Word and Excel document manipulation to meet developer demands. The Office team has provided documentation for the new APIs on GitHub along with a snippet explorer sample that shows you much of the new functionality in action.

For this article, I am going to build a simple Excel 2016 add-in that uses the new office.js library. I’ll build this sample from scratch and wrap it in the Angular framework so you can get a good feel for the patterns to follow when you develop. You can find the complete code for this article in the IT Unity GitHub repo. To keep it easy, the add-in simply reads and writes information from cells in the current spreadsheet. Figure 1 shows the user interface.

An Excel 2016 Add-in created using office.js and Angular

Figure 1, The add-in for the article

Getting started

I developed the add-in for this article with Visual Studio 2015. To get started, I simply created a new App for Office project and named it HelloExcel2016. While creating the project, I was prompted to specify the type of add-in and the host application. For this project, I selected a Task Pane add-in for Excel 2016.

When you create an Office add-in using Visual Studio 2015, you get a bunch of starter code contained in the Home folder. This code is great for starting quickly, but unfortunately, it is not written using the Angular framework. Therefore, I simply deleted this folder so I could start from scratch. I also deleted all the code contained in the app.js file, although I kept the empty file so I could use it later.

Since I deleted the default home page for the add-in, I had to add my own new page. So, I added a page named index.html. I then changed the startup page in the add-in manifest to use my new page. Figure 2 shows the change.

Adding an Excel 2016 Add-in to the manifest

Figure 2, Updating the add-in manifest

In the index.html page, I had to set references to style sheets and libraries I wanted to use. This includes referencing the enhanced office.js library as well as the Office UI Fabric. Listing 1 shows the complete page. Examine Listing 1 and you will see that it uses Content Delivery Network (CDN) references for both the core Angular library and the enhanced office.js library.

Listing 1, index.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />

    <title>Hello Excel 2016</title>

    <!-- Office UI Fabric -->
    <link rel="stylesheet" href="//appsforoffice.microsoft.com/fabric/1.0/fabric.min.css">
    <link rel="stylesheet" href="//appsforoffice.microsoft.com/fabric/1.0/fabric.components.min.css">

    <!-- Other style sheets -->
    <link href=" ../Content/Office.css" rel="stylesheet" type="text/css" />
    <link href="content/app.css" rel="stylesheet" type="text/css" />

</head>
<body>

    <!-- UI -->
    <div data-ng-controller="mainCtrl">
        <div class="ms-Grid">
            <div class="ms-Grid-row" style="padding-bottom:5px;">
                <div class="ms-Grid-col ms-u-md6">
                    <button class="ms-Button ms-Button--primary" data-ng-click="getCells()">
                        <span class="ms-Button-label">Get A1:B2</span>
                    </button>
                </div>
                <div class="ms-Grid-col ms-u-md6">
                    <button class="ms-Button ms-Button--primary" data-ng-click="copyCells()">
                        <span class="ms-Button-label">Set C1:D2</span>
                    </button>
                </div>
            </div>
            <div class="ms-Grid-row" data-ng-repeat="cell in cells">
                <div class="ms-Grid-col ms-u-md4">
                    {{cell.col}}
                </div>
                <div class="ms-Grid-col ms-u-md4">
                    {{cell.row}}
                </div>
                <div class="ms-Grid-col ms-u-md4">
                    {{cell.value}}
                </div>
            </div>
        </div>
    </div>

    <!-- third-party libs -->
    <script src="//ajax.googleapis.com/ajax/libs/angularjs/1.4.5/angular.min.js"></script>
    <script src="//appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>

    <!-- custom code -->
    <script src="app.js" type="text/javascript"></script>

</body>
</html>

In Listing 1, you should also note that a single Angular controller named mainCtrl is in command of the div where the add-in UI is created. You’ll also notice the familiar “mustache” syntax binding some controller data to the HTML. What you won’t see in the page is the ng-app attribute. Normally, you would expect to see this attribute to automatically initialize the single-page application. When creating office add-ins, however, automatic initialization can cause the subsequent initialization of office.js to fail. The office.js library must respond within 5 seconds after add-in loading or an error will occur. Automatic initialization may take longer than 5 seconds, so Office add-ins must perform manual initialization or “bootstrapping” after the office.js library is initialized. Listing 2 shows how this works in the sample. Once the manual initialization code is in place, you can move on to creating services and controllers.

Listing 2, Manual initialization of Angular in an Office add-in

//define ng app
var myapp = angular.module("HelloExcel2016", [])

    .controller("mainCtrl", ["$scope", "$q", "excelService",
        function mainCtrl($scope, $q, excelService) {
        }])

    .factory("excelService", ["$q", 
        function ($q) {
        }]);

//manually initialize ng app when office.js is ready
Office.initialize = function (reason) {
    angular.element(document).ready(function () {
        angular.bootstrap(document, ["HelloExcel2016"]);
    });
};

Defining a service

When creating Angular applications, you must always be mindful to make data changes inside the Angular context to allow proper data binding. Calls from libraries like office.js occur outside of the Angular context, so it is best to wrap them in an Angular service, which brings the changes back into the Angular context.

For this sample, I placed all of my office.js code into a separate service named excelService. Within this service, I created methods to get and set values from a given spreadsheet range. This code reflects the new programming paradigm in the enhanced office.js library that uses asynchronous calls, returns promises, and provides object tracking for cleanup. The pattern for working with the APIs in the enhanced office.js is to utilize a run method for setting up asynchronous calls and a sync method for committing changes. Listing 3 shows an example that reads data from a range of cells.

Listing 3, Using the run and sync methods.

Excel.run(function (context) {
    var range = context.workbook.worksheets.getActiveWorksheet().getRange(selector);
    range.load("address, values, range/format");
    return context.sync().then(function () {
        //success
    });
}).then(function () {
    //done
}).catch(function (error) {
    //failure
});

The Excel object is used as the entry point into the API. Similarly, the Word object is used as an entry point in Word add-ins. The run method sets up batch operations; in this case, it retrieves values from the spreadsheet. The context object represents the context of the current user in the Office application. The sync method synchronizes the JavaScript objects and the underlying Excel document. The programming paradigm provides success, failure, and completion branches where you can hook dependent code such as databinding to the Angular $scope object.

Although the run method returns a promise, I needed to bring the changes back into the Angular context. So, I wrapped the office.js code is an additional layer using the $q service. This gave me a good separation between controller and service that you can investigate in more detail if you download the complete sample.

Defining the controller

Once the service was in place, then I just needed to create the controller to handle the button clicks and perform the reads and writes. Since I wrapped the office.js functions in a service, this code became fairly simple. Listing 4 shows how I handle the button click, read the spreadsheet and bind the spreadsheet data to the $scope for display.

Listing 4, Handling user events

$scope.getCells = function () {
    excelService.getRangeValues("A1:B2").then(function (rows) {
        $scope.cells = [];
        var cells = [];
        for (var r = 0; r < rows.length; r++) {
            for (var c = 0; c < rows[r].length; c++) {
                cells.push({
                    row: r + 1,
                    col: c + 1,
                    value: rows[r][c]
                });
            }
        }
        $scope.cells = cells;
    }).catch(function (err) {
        console.log(err);
    }).finally(function () {
        console.log("done");
    });
};

Summary

The new enhanced office.js library offers a lot of new functionality for developers of Office add-ins. The programming paradigm introduces an asynchronous pattern that returns promises. For Angular developers, the new functionality is easily wrapped in a service making it feel familiar. If you’re working with Office 2016, I recommend checking out the complete sample for this article as well as the references mentioned in this article.


Topic: JavaScript and jQuery

Sign in with

Or register