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
▌Implement
▋Install packages
▋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.
▋Enable HTTPS on localhost
Go to \node_modules\browser-sync\lib\server\certs,
copy
server.crt
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?
$> npm install -g yo generator-office
|
After
installing it, use the following command to run the generator.
$>
yo office
|
▋Update
Manifest.xml
1.
Replace localhost:3000 to localhost:4200
2.
Update the information, such as Description,
ProviderName, …
3.
Notice that if you have send request to cross domain, add
the domain names on AppDomains.
▋For Excel 2016
If
you are using Excel 2016, set a share folder and copy the Manifest.xml into it.
Open
the Excel, and add the share folder’s path into trusted application directory.
PS.
Sorry, I don’t have Office 2016, so I use Excel 2013 for the screen printing.
However, the steps are the same on Excel 2016.
And
now you can use the add-in like following.
▋For
Excel online
▋Demo
▌Debug
▋For Excel
Ø Visual Studio is required
First close all IE.
In Visual Studio, open
【Attach to process】(Ctrl+Alt+P) and select attach to “script”.
Choose
the two iexplore.exe processes and
attach them.
Now we can debug our add-in in Visual Studio.
▋For Excel online
Use the browser Dev tools.
▌Reference
