karatejb: [office.js] Create Excel add-in with Angular

office.js Office 2016 Excel online Angular

▌Introduction

The JavaScript API for Office enables you to create web applications that interact with the object models in Office host applications.

Notice that some functions on latest office.js are only supported by Office 365, Office 2016. I will use Excel online to run the custom add-in in this sample.

▌Environment

▋Excel online

▋office.js 1.1.0

▋browser-sync

▋Microsoft Office Add-in Project Generator

▌Implement

▋Install packages

microsoft/office-js

microsoft/office-js-helpers

office-ui-fabric-core
office-ui-fabric-js

@types/office-js

▋Include office.js types into tsconfig.app.json

▋tsconfig.app.json

"types": [

"@types/office-js"

]

▋Enable polyfills for IE

▋polyfill.ts

▋Update maint.ts

▋maint.ts

import { enableProdMode } from '@angular/core';

import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';

import { AppModule } from './app/app.module';

import { environment } from './environments/environment';

declare const Office: any;

Office.initialize = function () {

const platform = platformBrowserDynamic();

platform.bootstrapModule(AppModule);

};

▋Start writing add-in

Open app.component.ts, we will write the logic codes inside, which will generate a table with row data.

import { Component, ApplicationRef } from '@angular/core';

@Component({

selector: 'app-root',

templateUrl: './app.component.html',

styleUrls: ['./app.component.css']

})

export class AppComponent {

title = 'Excel add-in demo';

data = [];

constructor(private appRef: ApplicationRef) { }

private render() {

Excel.run(function (ctx) {

const worksheet: Excel.Worksheet =

ctx.workbook.worksheets.getActiveWorksheet();

//Clear

worksheet.getRange().clear();

return ctx.sync().then(function () {

var myTable = new Office.TableData();

myTable.headers = ["Name", "Title", "Duty"];

myTable.rows = [["JB", "TPM", "Product & Project management"], ["Lily", " Assistant Manager", "Finance, banking"], ["Leia", "Kid", "Play, eat and sleep :P"]];

//See also : https://dev.office.com/reference/add-ins/shared/document.setselecteddataasync

Office.context.document.setSelectedDataAsync(myTable, {

coercionType: Office.CoercionType.Table

function (asyncResult) {

if (asyncResult.status === Office.AsyncResultStatus.Failed) {

console.log("Error: " + asyncResult.error.message);

}

});

}).catch(function (error) {

console.log("Error: " + error);

if (error instanceof OfficeExtension.Error) {

console.log("Debug info: " + JSON.stringify(error.debugInfo));

}

});

}

So far we completed our excel add-in sample code.

But there are two MUST-DO before we can use it.

1. The web app add-in MUST be HTTPS.

2. Create Manifest.xml

▋Enable HTTPS on localhost

Install BrowserSync

npm install browser-sync --save -dev

Go to \node_modules\browser-sync\lib\server\certs, copy
server.crt

server.key

to \assets\certs or other folder you like.

Start Angular application with the following command to enable HTTPS.

ng serve --ssl --ssl-key \"assets\\certs\\server.key\" --ssl-cert \"assets\\certs\\server.crt\

You can put the command to package.json’s scripts.

"scripts": {

"ng": "ng",

"start": "ng serve",

"startssl": "ng serve --ssl --ssl-key \"assets\\certs\\server.key\" --ssl-cert \"assets\\certs\\server.crt\"",

"build": "ng build",

"test": "ng test",

"lint": "ng lint",

"e2e": "ng e2e"

}

Then start it like this,

$> npm run startssl

Open https://localhost:4200 on IE, and install the certification.

▋Create Manifest.xml

What is Manifest.xml?

The XML manifest file of an Office Add-in describes how your add-in should be activated when an end user installs and uses it with Office documents and applications. (From dev.office.com)

How to create Manifest.xml?

One of the simple way is using Microsoft Office Add-in Project Generator(YO Office).

$> npm install -g yo generator-office

After installing it, use the following command to run the generator.

$> yo office