Lab.md

Build an Office Add-in using modern JavaScript tools and techniques

In this lab, you will walk through building Office Add-ins using a variety of popular JavaScript tools and frameworks, including TypeScript, React, Angular, and VueJS.

In this lab

1. Build an Office Add-in using React
2. Build an Office Add-in using Angular
3. Build an Office Add-in using VueJS
4. Sideload and Test the Office Add-in

Prerequisites

To complete this lab, you need the following:

• A consumer OneDrive account. OneDrive is used to test the Office Add-in.

• Node.js - (the latest LTS version)

• NPM (installed with Node.js) - v6.x (or higher)

• Yeoman - v3.x (or higher)

• Yeoman Generator for Microsoft Office - v1.6.x (or higher)

• Visual Studio Code

  npm install -g yo generator-office

• A free API key from Alpha Vantage: Registration is free and you will use the API key when creating stock quote requests.

Note: All three exercises in this lab build the same Microsoft Excel stock portfolio solution so you can compare the experience building Office Add-ins with different modern JavaScript tools and frameworks. The add-in will allow you to get real-time stock quotes and manage a portfolio in an Microsoft Excel table. You will have the ability to add, delete, and refresh stocks. Additionally, the add-in checks for an existing portfolio upon opening and (if found) reads the stocks out of the worksheet.

Note: If you are developing on Windows you may encounter the following error when building the project: TypeError: process.getuid is not a function. This stems from a change in a package that webpack depends on. You can resolve this by upgrading the version of webpack-cli with the command: npm install webpack-cli@3.3.5.

Exercise 1: Build an Office Add-in using React

In this exercise, you will develop an Office Add-in using React and TypeScript. You will provision a new project using the Office Yeoman generator, develop the add-in using Office.js, and test the add-in in Microsoft Office Online.

Provision the Office Add-in

1. Open a terminal/command prompt, and change directories to the location where you want to create the project.

2. Run the Office Yeoman generator using the command yo office.

   yo office

3. The Office Yeoman generator will ask a number of question. Use the following responses:

   • Choose a project type? Office Add-in Task Pane project using React framework
   • Choose a script type? TypeScript
   • What do you want to name your add-in? Excel Portfolio
   • Which Office client application would you like to support? Excel

   

4. When the Yeoman generator completes, change directories to the project folder and open the folder in your favorite code editor (you can use the command code . for Visual Studio Code).

   Note: You should be able to run and sideload the add-in at this point. To do that, follow the steps outlined in Sideload and Test the Office Add-in. In the next section, you will add additional functionality to the add-in.

Develop the Office Add-in

1. Open src/taskpane/taskpane.css and add the following styles to the end of the file.

   .overlay {
       position: absolute;
       top: 0px;
       bottom: 0px;
       left: 0px;
       right: 0px;
       z-index: 1000;
       display: block;
   }
   .overlay .spinner {
       position: absolute;
       top: 50%;
       left: 50%;
       margin-top: -20px;
       margin-left: -20px;
       z-index: 1100;
   }
   .header {
       padding-left: 10px;
   }
   .ms-MessageBar-content {
       padding: 2px !important;
   }
   .pct100 {
       width: 100%;
       float: left;
   }
   .padding10 {
       padding: 10px;
   }
   .right {
       float: right;
   }
   .left {
       float: left;
   }
   .icon {
       padding-left: 8px;
       cursor: pointer;
   }
   .itemRow {
       padding-top: 4px;
       padding-bottom: 4px;
   }
   .itemRow:hover {
       background-color: #eeeeee;
   }
   .tbl-head {
       margin-bottom: 5px;
   }

2. Open the src/taskpane/index.tsx file and update the title const to Excel Portfolio:

   const title = 'Excel Portfolio';

3. The project template that the Office Yeoman generator created include a number of React components that need to be updated or deleted.

   Delete the src/taskpane/components/HeroList.tsx and src/taskpane/components/Progress.tsx files.

4. Open the src/taskpane/components/Header.tsx file and replace the contents with the following code:

   import * as React from 'react';
   
   export interface HeaderProps {
     title: string;
   }
   
   export class Header extends React.Component<HeaderProps, any> {
     constructor(props, context) {
       super(props, context);
     }
   
     render() {
       return (
         <div className="ms-bgColor-greenDark header">
           <span className="ms-font-xxl ms-fontColor-white">{this.props.title}</span>
         </div>
       );
     }
   }

5. Create a new React component named Waiting.tsx in the src/taskpane/components folder and add the following code.

   This component uses the Office UI Fabric React Components for Overlay and Spinner.

   import * as React from 'react';
   import { Overlay, Spinner, SpinnerSize } from 'office-ui-fabric-react';
   
   export class Waiting extends React.Component<any, any> {
     constructor(props, context) {
       super(props, context);
     }
   
     render() {
       return (
         <Overlay isDarkThemed={true} className="overlay">
           <div className="spinner">
             <Spinner size={SpinnerSize.large} />
           </div>
         </Overlay>
       );
     }
   }

6. Create a new React component named StockItem.tsx in the src/taskpane/components folder and add the following code.

   This component will display a stock with commands for refresh and delete. The component has properties for stock symbol, its index in the list, and the handlers for refresh and delete.

   import * as React from 'react';
   
   export interface StockItemProps {
     symbol: string;
     index: number;
     onDelete: any;
     onRefresh: any;
   }
   
   export class StockItem extends React.Component<StockItemProps, any> {
     constructor(props, context) {
       super(props, context);
     }
   
     render() {
       return (
         <div className="pct100 itemRow">
           <div className="left">{this.props.symbol}</div>
           <div className="right">
             <div className="left icon">
               <i className="ms-Icon ms-Icon--Refresh" aria-hidden="true" onClick={this.props.onRefresh} />
             </div>
             <div className="left icon">
               <i className="ms-Icon ms-Icon--Delete" aria-hidden="true" onClick={this.props.onDelete} />
             </div>
           </div>
         </div>
       );
     }
   }

7. Open src/taskpane/components/App.tsx and replace it's contents with the following code.

   import * as React from 'react';
   import { MessageBar, MessageBarType, TextField, TextFieldBase } from 'office-ui-fabric-react';
   import { Header } from './header';
   import { Waiting } from './waiting';
   import { StockItem } from './StockItem';
   
   export interface AppProps {
     title: string;
   }
   
   export interface AppState {
     listItems: string[];
     waiting: boolean;
     error: string;
   }
   
   export default class App extends React.Component<AppProps, AppState> {
     newSymbol:any = React.createRef();
     constructor(props, context) {
       super(props, context);
       this.state = {
         listItems: [],
         waiting: false,
         error: ''
       };
     }
   
     componentDidMount() {
       // Sync stocks already in Excel table
       this.syncTable().then(() => {});
     }
   
     // Adds symbol
     addSymbol = async event => {
       //TODO
       console.log(event);
     }
   
     // Delete symbol
     deleteSymbol = async index => {
       //TODO
       console.log(index);
     }
   
     // Refresh symbol
     refreshSymbol = async (index: number) => {
       //TODO
       console.log(index);
     }
   
     // Reads symbols from an existing Excel workbook and pre-populates them in the add-in
     syncTable = async () => {
       //TODO
       console.log('sync');
     }
   
     // Gets a quote by calling into the stock service
     getQuote = async () => {
       //TODO
       console.log('sync');
     }
   
     render() {
       const stocks = this.state.listItems.map((symbol, index) => (
         <StockItem
           symbol={symbol}
           index={index}
           onDelete={this.deleteSymbol.bind(this, index)}
           onRefresh={this.refreshSymbol.bind(this, index)}
         />
       ));
       return (
         <div className="container ms-Fabric">
           {this.state.waiting && <Waiting />}
           <Header title={this.props.title} />
           {this.state.error != '' && (
             <MessageBar
               messageBarType={MessageBarType.error}
               isMultiline={false}
               onDismiss={() => {
                 this.setState({ error: '' });
               }}>
               {this.state.error}
             </MessageBar>
           )}
           <div className="padding10">
             <div className="pct100 tbl-head">
               <span className="ms-font-l">Stock Symbols</span>
             </div>
             <div className="pct100">
               <TextField componentRef={this.newSymbol} onKeyPress={this.addSymbol.bind(this)} placeholder="Enter a stock symbol (ex: MSFT)" />
             </div>
             {stocks}
           </div>
         </div>
       );
     }
   }

8. Although the app's functionality isn't complete, the visual markup is. You can see it by saving all your work and returning to Excel Online. It should look similar to the following image.

   If you previously closed the Excel Online window or if your Office Online session has expired (the add-in doesn't seem to load), follow the Sideload the Office Add-in steps.

   

9. The App.tsx file has a number of placeholder functions that you will complete to get the add-in functioning. Start by locating the getQuote function. This function calls a REST API to get real-time stock statistics on a specific stock symbol. Update it as seen below.

   getQuote = async (symbol: string) => {
     return new Promise((resolve, reject) => {
       const queryEndpoint = `https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=${escape(symbol)}&apikey=${ALPHAVANTAGE_APIKEY}`;
   
       fetch(queryEndpoint)
         .then((res: any) => {
           if (!res.ok) {
             reject('Error getting quote');
           }
           return res.json();
         })
         .then((jsonResponse: any) => {
           const quote: any = jsonResponse['Global Quote'];
           resolve(quote);
         });
     });
   }

10. Create new utils folder in the src folder, then create a file named ExcelTableUtil.tsx. This TypeScript class will contain helper functions for working with Microsoft Excel tables with office.js. Notice the ExcelTableUtil constructor accepts details about the Excel table, including the name, location, and header details.

    export class ExcelTableUtil {
      tableName: string;
      location: string;
      headers: string[];
      constructor(tableName: string, location: string, headers: string[]) {
        this.tableName = tableName;
        this.location = location;
        this.headers = headers;
      }
    }

11. Implement the ExcelTableUtil utility class:

    1. Locate and open the file src/utils/ExcelTableUtil.tsx.

    2. Add the following methods ExcelTableUtil class. These methods access the table in Excel, or creates the table if it doesn't exist.

       // Create the StocksTable and defines the header row
       createTable = async () => {
         return new Promise(async (resolve, reject) => {
           await Excel.run(async context => {
             // Create a proxy object for the active worksheet and create the table
             const sheet = context.workbook.worksheets.getActiveWorksheet();
             const tableRef = sheet.tables.add(this.location, true);
             tableRef.name = this.tableName;
             tableRef.getHeaderRowRange().values = [this.headers];
             return context.sync().then(() => {
               resolve(tableRef);
             });
           }).catch(createError => {
             reject(createError);
           });
         });
       }
       
       // Ensures the Excel table is created and tries to get a table reference
       ensureTable = async (forceCreate: boolean) => {
         return new Promise(async (resolve, reject) => {
           await Excel.run(async context => {
             // Create a proxy object for the active worksheet and try getting table reference
             const sheet = context.workbook.worksheets.getActiveWorksheet();
             const tableRef = sheet.tables.getItem(this.tableName);
             return context.sync().then(() => {
               resolve(tableRef);
             });
           }).catch(() => {
             if (forceCreate) {
               // Create a new table because an existing table was not found.
               this.createTable().then(
                 async tableRef => {
                   resolve(tableRef);
                 },
                 createError => {
                   reject(createError);
                 }
               );
             } else {
               resolve(null);
             }
           });
         });
       }

    3. Add the following method to the ExcelTableUtil class.

       Notice that it calls the ensureTable function we just created to ensure the Excel table has been created.

       // Appends a row to the table
       addRow = async (data) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(
             async (tableRef: Excel.Table) => {
               await Excel.run(async context => {
                 const sheet = context.workbook.worksheets.getActiveWorksheet();
                 // Add the new row
                 tableRef = sheet.tables.getItem(this.tableName);
                 tableRef.rows.add(null, [data]);
                 // Autofit columns and rows if your Office version supports the API.
                 if (Office.context.requirements.isSetSupported('ExcelApi', 1.2)) {
                   sheet.getUsedRange().format.autofitColumns();
                   sheet.getUsedRange().format.autofitRows();
                 }
                 sheet.activate();
                 return context.sync().then(() => {
                   resolve();
                 });
               }).catch(err => {
                 reject(err);
               });
             },
             err => {
               reject(err);
             }
           );
         });
       }

12. Update the App component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/components/App.tsx file.

    2. Add the following import statement after the existing import statements for the the new ExcelTableUtil class.

       import { ExcelTableUtil } from '../../utils/excelTableUtil';

    3. Add the following constant after the import statements and update the {{REPLACE_WITH_ALPHAVANTAGE_APIKEY}} to use your API key.

       const ALPHAVANTAGE_APIKEY: string = '{{REPLACE_WITH_ALPHAVANTAGE_APIKEY}}';

    4. Add the following private member to the App class:

       tableUtil: ExcelTableUtil = new ExcelTableUtil('Portfolio', 'A1:H1', [
         'Symbol',
         'Last Price',
         'Timestamp',
         'Quantity',
         'Price Paid',
         'Total Gain',
         'Total Gain %',
         'Value'
       ]);

    5. Update the addSymbol() method to the following code:

       // Adds symbol
       addSymbol = async (event) => {
         if (event.key === 'Enter') {
           const element = this.newSymbol.current as TextFieldBase;
           const symbol = element.value.toUpperCase();
       
           // Get quote and add to Excel table
           this.setState({ waiting: true });
           this.getQuote(symbol).then(
             (res: any) => {
               let cnt = this.state.listItems.length;
               const data = [
                 res['01. symbol'], //Symbol
                 res['05. price'], //Last Price
                 res['07. latest trading day'], // Timestamp of quote,
                 0, // quantity (manually entered)
                 0, // price paid (manually entered)
                 `=(B${cnt+2} * D${cnt+2}) - (E${cnt+2} * D${cnt+2})`, //Total Gain $
                 `=H${cnt+2} / (E${cnt+2} * D${cnt+2}) * 100 - 100`, //Total Gain %
                 `=B${cnt+2} * D${cnt+2}` //Value
               ];
               this.tableUtil.addRow(data).then(
                 () => {
                   let symbols = this.state.listItems;
                   symbols.unshift(element.value.toUpperCase());
                   this.setState({ listItems: symbols });
                   element.setState({ uncontrolledValue: '' });
                   this.setState({ waiting: false });
                 },
                 err => {
                   this.setState({ error: err });
                   this.setState({ waiting: false });
                 }
               );
             },
             err => {
               this.setState({ error: err });
               this.setState({ waiting: false });
             }
           );
         }
       }

       Note: This is a good time to test the add symbol function of your add-in.

13. Update the ExcelTableUtil utility to add support for accessing and deleting rows:

    1. Locate and open the src/components/ExcelTableUtil.tsx file.

    2. Add the following methods to the ExcelTableUtil class:

       // Gets data for a specific named column
       getColumnData = async (column: string) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(false).then(
             async (tableRef: Excel.Table) => {
               if (tableRef == null) {
                 resolve([]);
               } else {
                 await Excel.run(async context => {
                   // Get column range by column name
                   const sheet = context.workbook.worksheets.getActiveWorksheet();
                   tableRef = sheet.tables.getItem(this.tableName);
                   var colRange = tableRef.columns.getItem(column).getDataBodyRange().load("values");
       
                   // Sync to populate proxy objects with data from Excel
                   return context.sync().then(async () => {
                     let data: string[] = [];
                     for (let i = 0; i < colRange.values.length; i++) {
                       data.push(colRange.values[i].toString());
                     }
                     resolve(data);
                   });
                 }).catch(err => {
                   reject(err);
                 });
               }
             },
             err => {
               reject(err);
             }
           );
         });
       }
       
       // Deletes a column based by row index
       deleteRow = async (index: number) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(
             async (tableRef: Excel.Table) => {
               await Excel.run(async context => {
                 const range = tableRef.rows.getItemAt(index).getRange();
                 range.delete(Excel.DeleteShiftDirection.up);
                 return context.sync().then(async () => {
                   resolve();
                 });
               }).catch(err => {
                 reject(err);
               });
             },
             err => {
               reject(err);
             }
           );
         });
       }

14. Update the App component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/components/App.tsx file.

    2. Locate and update the deleteSymbol() method in the App class to delete specifying symbol from the Excel table

       // Delete symbol
       deleteSymbol = async index => {
         // Delete from Excel table by index number
         let symbols = this.state.listItems;
         let symbol = symbols[index];
         this.setState({ waiting: true });
         this.tableUtil.getColumnData('Symbol').then(
           async (columnData: string[]) => {
             // Ensure the symbol was found in the Excel table
             if (columnData.indexOf(symbol) !== -1) {
               this.tableUtil.deleteRow(columnData.indexOf(symbol)).then(
                 async () => {
                   symbols.splice(index, 1);
                   this.setState({ listItems: symbols });
                   this.setState({ waiting: false });
                 },
                 err => {
                   this.setState({ error: err });
                   this.setState({ waiting: false });
                 }
               );
             } else {
               symbols.splice(index, 1);
               this.setState({ waiting: false });
             }
           },
           err => {
             this.setState({ error: err });
             this.setState({ waiting: false });
           }
         );
       }

    Note: This is a good time to test the delete symbol function of your add-in.

15. Update the ExcelTableUtil utility to add support for refreshing rows in the table:

    1. Locate and open the src/components/ExcelTableUtil.tsx file.

    2. Add the following methods to the ExcelTableUtil class:

       // Updates a specific cell in the table
       updateCell = async (address: string, value: any) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(
             async () => {
               await Excel.run(async context => {
                 const sheet = context.workbook.worksheets.getActiveWorksheet();
                 const range = sheet.getRange(address);
                 range.values = [[value]];
                 return context.sync().then(async () => {
                   resolve();
                 });
               }).catch(err => {
                 reject(err);
               });
             },
             err => {
               reject(err);
             }
           );
         });
       }

16. Update the App component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/components/App.tsx file.

    2. Locate and update the refreshSymbol() method in the App class to refresh specifying symbol in the Excel table

       // Refresh symbol
       refreshSymbol = async (index: number) => {
         // Refresh stock quote and update Excel table
         let symbols = this.state.listItems;
         let symbol = symbols[index];
         this.setState({ waiting: true });
         this.tableUtil.getColumnData('Symbol').then(
           async (columnData: string[]) => {
             // Ensure the symbol was found in the Excel table
             const rowIndex = columnData.indexOf(symbol);
             if (rowIndex !== -1) {
               this.getQuote(symbol).then((res: any) => {
                 // "last trade" is in column B with a row index offset of 2 (row 0 + the header row)
                 this.tableUtil
                   .updateCell(`B${rowIndex + 2}:B${rowIndex + 2}`, res["05. price"])
                   .then(
                     async () => {
                       this.setState({ waiting: false });
                     },
                     err => {
                       this.setState({ error: err });
                       this.setState({ waiting: false });
                     }
                   );
               });
             } else {
               this.setState({ error: 'Symbol not in table' });
               symbols.splice(index, 1);
               this.setState({ waiting: false });
             }
           },
           err => {
             this.setState({ error: err });
             this.setState({ waiting: false });
           }
         );
       }

    Note: This is a good time to test the refresh symbol function of your add-in.

17. Update the syncTable function, which is called when the add-in is launched in the constructor of App.tsx to pull in any stock symbols that might already exist in the worksheet. It calls getColumnData to get this data.

    syncTable = async () => {
      this.setState({ waiting: true });
      this.tableUtil.getColumnData('Symbol').then(
        async (columnData: string[]) => {
          this.setState({ listItems: columnData });
          this.setState({ waiting: false });
        },
        err => {
          this.setState({ error: err });
          this.setState({ waiting: false });
        }
      );
    }

The Excel Portfolio Add-in written with React and TypeScript is complete. You should now follow the steps to Sideload and Test the Office Add-in.

Exercise 2: Build an Office Add-in using Angular

In this exercise, you will develop an Office Add-in using Angular and TypeScript. You will provision a new project using the Angular CLI and Office Yeoman generator, develop the add-in using Office.js, and test the add-in in Office Online.

Provision the Office Add-in

1. Open a terminal/command prompt, and change directories to the location where you want the project provisioned.

2. Run the Office Yeoman generator using the command yo office.

   yo office

3. The Office Yeoman generator will ask a number of question. Use the following responses:

   • Choose a project type? Office Add-in Task Pane project using Angular framework
   • Choose a script type? TypeScript
   • What do you want to name your add-in? Excel Portfolio
   • Which Office client application would you like to support? Excel

   

4. Change directories to the project folder and open the project in a code editor (use . code if using Visual Studio Code).

Develop the Office Add-in

1. Open src/taskpane/taskpane.css and replace the entire file with the contents shown below.

   /* You can add global styles to this file, and also import other style files */
   .header {
       padding: 10px;
   }
   
   .content {
       margin-top: 10px;
   }
   
   .hover:hover {
       background: #f8f8f8;
   }
   
   .overlay {
       position: absolute;
       top: 0px;
       bottom: 0px;
       left: 0px;
       right: 0px;
       background: hsla(0,0%,100%,.4);
       z-index: 1000;
   }
   
   .spinner {
       position: absolute;
       top: 50%;
       left: 50%;
       margin-top: -20px;
       margin-left: -20px;
       z-index: 1100;
   }
   
   .ms-MessageBar-content {
       padding: 2px !important;
   }
   
   .pct100 {
       width: 100%;
       float: left;
   }
   
   .padding10 {
       padding: 10px;
   }
   
   .right {
       float: right;
   }
   
   .left {
       float: left;
   }
   
   .icon {
       padding-left: 8px;
       cursor: pointer;
   }
   
   .itemRow {
       padding-top: 4px;
       padding-bottom: 4px;
   }
   
   .itemRow:hover {
       background-color: #eeeeee;
   }
   
   .tbl-head {
       margin-bottom: 5px;
   }
   
   input {
       font-size: 16px;
       width: 100%;
   }

2. Open the src/taskpane/taskpane.html file and add the ms-Fabric style class to the other style classes of the body element.

   <body class="ms-font-m ms-welcome ms-Fabric">

3. Copy the spinner.gif image from the LabFiles folder into assets of your project directory.

   

4. Angular allows you to break your solution up into components. The Angular CLI already created an app component. Open src/taskpane/app/app.component.html to update it's markup as seen below.

   <!--The content below is only a placeholder and can be replaced.-->
   <div>
     <div *ngIf="waiting">
       <div class="overlay"></div>
       <img class="spinner" src="/assets/spinner.gif" />
     </div>
     <div class="ms-bgColor-greenDark header">
       <span class="ms-font-su ms-fontColor-white">Excel Portfolio</span>
     </div>
     <div>
       <div class="ms-MessageBanner" *ngIf="error">
         <div class="ms-MessageBanner-content" style="text-align: left; margin-left: 40px;">
           <div class="ms-MessageBanner-text ms-font-s-plus">
             <div class="ms-MessageBanner-clipper">
               <i class="ms-Icon ms-Icon--Error"></i>
               <span style="vertical-align: top;">&nbsp;{{error}}</span>
             </div>
           </div>
         </div>
         <button class="ms-MessageBanner-close" (click)="error = null;">
           <i class="ms-Icon ms-Icon--Clear"></i>
         </button>
       </div>
       <div class="padding10">
         <div class="pct100 tbl-head">
           <span class="ms-font-l">Stock Symbols</span>
         </div>
         <div class="pct100">
           <input class="ms-TextField-field" #newSymbol (keyup.enter)="addSymbol(newSymbol.value); newSymbol.value = '';"
             placeholder="Enter a stock symbol (ex: MSFT)" />
         </div>
         <div class="pct100 itemRow" *ngFor="let symbol of symbols; let i = index">
           <div class="left ms-font-l">{{symbol}}</div>
           <div class="right">
             <div class="left icon" (click)="refreshSymbol(i)"><i class="ms-Icon ms-Icon--Refresh" aria-hidden="true"></i>
             </div>
             <div class="left icon" (click)="deleteSymbol(i)"><i class="ms-Icon ms-Icon--Delete" aria-hidden="true"></i>
             </div>
           </div>
         </div>
         <div class="pct100 itemRow" *ngIf="symbols.length == 0">
           <em class="ms-font-l">No symbols added</em>
         </div>
       </div>
     </div>
   </div>

5. Next, open src/taskpane/app/app.component.ts and update it as follows.

   import { Component, NgZone } from '@angular/core';
   import * as OfficeHelpers from '@microsoft/office-js-helpers';
   
   const template = require('./app.component.html');
   
   @Component({
     selector: 'app-home',
     template
   })
   
   export default class AppComponent {
     constructor() {
       this.syncTable().then(() => {});
     }
   
     // Adds symbol
     addSymbol = async (symbol) => {
       //TODO
       console.log(symbol);
     }
   
     // Delete symbol
     deleteSymbol = async (index) => {
       //TODO
       console.log(index);
     }
   
     // Refresh symbol
     refreshSymbol = async (index) => {
       //TODO
       console.log(index);
     }
   
     // Reads symbols from an existing Excel workbook and pre-populates them in the add-in
     syncTable = async () => {
       //TODO
       console.log('syncTable');
     }
   
     // Gets a quote by calling into the stock service
     getQuote = async (symbol) => {
       //TODO
       console.log(symbol);
     }
   }

6. Although the app's functionality isn't complete, the visual markup is. You can see it by saving all your work and returning to Office Online. It should look similar to below. If you previously closed the Excel Online window or if your Office Online session has expired (the add-in doesn't seem to load), follow the Sideload the Office Add-in steps above.

   

7. The app.component.ts file has a number of placeholder functions that you will complete to get the add-in functioning.

   1. Locate & open the src/taskpane/app/app.component.ts file.

   2. Add the following constant after the import statements and update the {{REPLACE_WITH_ALPHAVANTAGE_APIKEY}} to use your API key.

      const ALPHAVANTAGE_APIKEY = '{{REPLACE_WITH_ALPHAVANTAGE_APIKEY}}';

   3. Locate the getQuote() method. This function calls a REST API to get real-time stock statistics on a specific stock symbol. Update it as seen below.

      // Gets a quote by calling into the stock service
      getQuote = async (symbol) => {
        return new Promise((resolve, reject) => {
          const queryEndpoint = `https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=${escape(symbol)}&apikey=${ALPHAVANTAGE_APIKEY}`;
      
          fetch(queryEndpoint)
            .then((res) => {
              if (!res.ok) {
                reject('Error getting quote');
              }
              return res.json();
            })
            .then((jsonResponse) => {
              const quote = jsonResponse['Global Quote'];
              resolve(quote);
            });
        });
      }

8. Create new utils folder under src and then create a file named excelTableUtil.ts in it (src/utils/excelTableUtil.ts). This TypeScript class will contain helper functions for working with Excel tables with office.js. Notice the ExcelTableUtil constructor accepts details about the Excel table, including the name, location, and header details.

   /// <reference path="../../node_modules/@types/office-js/index.d.ts" />
   
   export class ExcelTableUtil {
     tableName;
     location;
     headers;
     constructor(tableName, location, headers) {
       this.tableName = tableName;
       this.location = location;
       this.headers = headers;
     }
   
     // ExcelTableUtil functions here
   }

9. Implement the ExcelTableUtil utility class:

   1. Locate and open the file src/utils/excelTableUtil.ts.

   2. Add the following methods ExcelTableUtil class. These methods access the table in Excel, or creates the table if it doesn't exist.

      // Create the StocksTable and defines the header row
      createTable = async () => {
        return new Promise(async (resolve, reject) => {
          await Excel.run(async context => {
            // Create a proxy object for the active worksheet and create the table
            const sheet = context.workbook.worksheets.getActiveWorksheet();
            const tableRef = sheet.tables.add(this.location, true);
            tableRef.name = this.tableName;
            tableRef.getHeaderRowRange().values = [this.headers];
            return context.sync().then(() => {
              resolve(tableRef);
            });
          }).catch(createError => {
            reject(createError);
          });
        });
      }
      
      // Ensures the Excel table is created and tries to get a table reference
      ensureTable = async (forceCreate) => {
        return new Promise(async (resolve, reject) => {
          await Excel.run(async context => {
            // Create a proxy object for the active worksheet and try getting table reference
            const sheet = context.workbook.worksheets.getActiveWorksheet();
            const tableRef = sheet.tables.getItem(this.tableName);
            return context.sync().then(() => {
              resolve(tableRef);
            });
          }).catch(() => {
            if (forceCreate) {
              // Create a new table because an existing table was not found.
              this.createTable().then(
                async tableRef => {
                  resolve(tableRef);
                },
                createError => {
                  reject(createError);
                }
              );
            } else {
              resolve(null);
            }
          });
        });
      }

   3. Add the following method to the ExcelTableUtil class.

      Notice that it calls the ensureTable function we just created to ensure the Excel table has been created.

      // Appends a row to the table
      addRow = async (data) => {
        return new Promise(async (resolve, reject) => {
          this.ensureTable(true).then(
            async (tableRef: Excel.Table) => {
              await Excel.run(async context => {
                const sheet = context.workbook.worksheets.getActiveWorksheet();
                // Add the new row
                tableRef = sheet.tables.getItem(this.tableName);
                tableRef.rows.add(null, [data]);
                // Autofit columns and rows if your Office version supports the API.
                if (Office.context.requirements.isSetSupported('ExcelApi', 1.2)) {
                  sheet.getUsedRange().format.autofitColumns();
                  sheet.getUsedRange().format.autofitRows();
                }
                sheet.activate();
                return context.sync().then(() => {
                  resolve();
                });
              }).catch(err => {
                reject(err);
              });
            },
            err => {
              reject(err);
            }
          );
        });
      }

10. Update the App component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/app/app.component.ts file.

    2. Add the following import statement after the existing import statements for the the new ExcelTableUtil class.

       import { ExcelTableUtil } from './../../utils/excelTableUtil';

    3. Add the following private members to the AppComponent class:

       symbols = [];
       error = null;
       waiting = false;
       zone = new NgZone({});
       
       tableUtil = new ExcelTableUtil('Portfolio', 'A1:H1', [
         'Symbol',
         'Last Price',
         'Timestamp',
         'Quantity',
         'Price Paid',
         'Total Gain',
         'Total Gain %',
         'Value'
       ]);

    4. Update the addSymbol() method to the following code:

       // Adds symbol
       addSymbol = async (symbol) => {
         this.waiting = true;
       
         // Get quote and add to Excel table
         this.getQuote(symbol).then(
           (res) => {
             let cnt = this.symbols.length;
             const data = [
               res['01. symbol'], //Symbol
               res['05. price'], //Last Price
               res['07. latest trading day'], // Timestamp of quote,
               0, // quantity (manually entered)
               0, // price paid (manually entered)
               `=(B${cnt+2} * D${cnt+2}) - (E${cnt+2} * D${cnt+2})`, //Total Gain $
               `=H${cnt+2} / (E${cnt+2} * D${cnt+2}) * 100 - 100`, //Total Gain %
               `=B${cnt+2} * D${cnt+2}` //Value
             ];
             this.tableUtil.addRow(data).then(
               () => {
                 this.symbols.unshift(symbol.toUpperCase());
                 this.waiting = false;
               },
               (err) => {
                 this.error = err;
               }
             );
           },
           err => {
             this.error = err;
             this.waiting = false;
           }
         );
       }

11. Update the ExcelTableUtil utility to add support for accessing and deleting rows:

    1. Locate and open the src/utils/excelTableUtil.ts file.

    2. Add the following methods to the ExcelTableUtil class:

       // Gets data for a specific named column
       getColumnData = async (column) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(false).then(
             async (tableRef: Excel.Table) => {
               if (tableRef == null) {
                 resolve([]);
               } else {
                 await Excel.run(async context => {
                   // Get column range by column name
                   const sheet = context.workbook.worksheets.getActiveWorksheet();
                   tableRef = sheet.tables.getItem(this.tableName);
                   var colRange = tableRef.columns.getItem(column).getDataBodyRange().load("values");
       
                   // Sync to populate proxy objects with data from Excel
                   return context.sync().then(async () => {
                     let data: string[] = [];
                     for (let i = 0; i < colRange.values.length; i++) {
                       data.push(colRange.values[i].toString());
                     }
                     resolve(data);
                   });
                 }).catch(err => {
                   reject(err);
                 });
               }
             },
             err => {
               reject(err);
             }
           );
         });
       }
       
       // Deletes a column based by row index
       deleteRow = async (index) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(
             async (tableRef: Excel.Table) => {
               await Excel.run(async context => {
                 const range = tableRef.rows.getItemAt(index).getRange();
                 range.delete(Excel.DeleteShiftDirection.up);
                 return context.sync().then(async () => {
                   resolve();
                 });
               }).catch(err => {
                 reject(err);
               });
             },
             err => {
               reject(err);
             }
           );
         });
       }

12. Update the AppComponent component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/app/app.component.ts file.

    // Delete symbol
    deleteSymbol = async (index) => {
      // Delete from Excel table by index number
      const symbol:string = this.symbols[index];
      this.waiting = true;
      this.tableUtil.getColumnData('Symbol').then(
        async (columnData:string[]) => {
          // Ensure the symbol was found in the Excel table
          if (columnData.indexOf(symbol) !== -1) {
            this.tableUtil.deleteRow(columnData.indexOf(symbol))
            .then(async () => {
                this.symbols.splice(index, 1);
                this.waiting = false;
            }, err => {
              this.error = err;
              this.waiting = false;
            });
          } else {
            this.symbols.splice(index, 1);
            this.waiting = false;
          }
        }, (err) => {
          this.error = err;
          this.waiting = false;
        }
      );
    }

    Note: This is a good time to test the delete symbol function of your add-in.

13. Update the ExcelTableUtil utility to add support for refreshing rows in the table:

    1. Locate and open the src/utils/excelTableUtil.ts file.

    2. Add the following methods to the ExcelTableUtil class:

       // Updates a specific cell in the table
       updateCell = async (address, value) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(
             async () => {
               await Excel.run(async context => {
                 const sheet = context.workbook.worksheets.getActiveWorksheet();
                 const range = sheet.getRange(address);
                 range.values = [[value]];
                 return context.sync().then(async () => {
                   resolve();
                 });
               }).catch(err => {
                 reject(err);
               });
             },
             err => {
               reject(err);
             }
           );
         });
       }

14. Update the AppComponent component to leverage the methods you added to the ExcelTableUtil class.

    1. Update the App component to leverage the methods you added to the ExcelTableUtil class.

    2. Locate and open the src/taskpane/app/app.component.ts file.

    3. Locate and update the refreshSymbol() method to specify a symbol to refresh in the Excel table.

       // Refresh symbol
       refreshSymbol = async (index) => {
         // Refresh stock quote and update Excel table
         const symbol = this.symbols[index];
         this.waiting = true;
         this.tableUtil.getColumnData('Symbol')
           .then(async (columnData:string[]) => {
             // Ensure the symbol was found in the Excel table
             const rowIndex = columnData.indexOf(symbol);
             if (rowIndex !== -1) {
               this.getQuote(symbol).then((res) => {
                 // "last trade" is in column B with a row index offset of 2 (row 0 + the header row)
                 this.tableUtil.updateCell(`B${rowIndex + 2}:B${rowIndex + 2}`, res["05. price"])
                 .then(async () => {
                   this.waiting = false;
                 }, (err) => {
                   this.error = err;
                   this.waiting = false;
                 });
               });
             } else {
               this.error = `${symbol} not found in Excel`;
               this.symbols.splice(index, 1);
               this.waiting = false;
             }
           }, (err) => {
             this.error = err;
             this.waiting = false;
           });
       }

    Note: This is a good time to test the refresh symbol function of your add-in.

    1. Finally, update the syncTable() method to the following:

       // Reads symbols from an existing Excel workbook and pre-populates them in the add-in
       syncTable = async () => {
         this.waiting = true;
         this.tableUtil.getColumnData('Symbol')
           .then(async (columnData:string[]) => {
             this.symbols = columnData;
             this.waiting = false;
           }, (err) => {
             this.error = err;
             this.waiting = false;
           });
       }

The Excel Portfolio Add-in written with Angular and TypeScript is complete. You should now follow the steps to Sideload and Test the Office Add-in.

Exercise 3: Build an Office Add-in using VueJS

In this exercise, you will develop an Office Add-in using Vue.js and TypeScript. You will provision a new project using the Office Yeoman generator, develop the add-in using Office.js, and test the add-in in Office Online.

Provision the Office Add-in

1. Open a terminal/command prompt, and change directories to the location where you want to create the project.

2. Run the Office Yeoman generator using the command yo office.

   yo office

3. The Office Yeoman generator will ask a number of question. Use the following responses:

   • Choose a project type? Office Add-in Task Pane project
   • Choose a script type? TypeScript
   • What do you want to name your add-in? Excel Portfolio
   • Which Office client application would you like to support? Excel

   

4. When the Yeoman generator completes, change directories to the project folder and open the folder in your favorite code editor (you can use the command code . for Visual Studio Code).

   Note: You should be able to run and sideload the add-in at this point. To do that, follow the steps outlined in Sideload and Test the Office Add-in. In the next section, you will add additional functionality to the add-in.

   If you elect to run & sideload the project as a test, make sure you terminate the process before proceeding with the lab. The local development server must be restarted after modifying the package.json file to add Vue.js to the project.

5. The Office Yeoman generator does not have a Vue.js template. In a previous step, you selected the no web framework project template as the starting point so you need to convert the project to leverage Vue.js.

   1. Open a command prompt and change directory to the root folder of the project.

      1. Execute the following commands to install the necessary Vue dependency packages:

         npm install vue vue-class-component --save

      2. Execute the following command to install the necessary dev dependency packages:

         npm install vue-loader vue-template-compiler --save-dev

   2. Locate and open the webpack.config.js file in the project root directory. It needs to be updated to support Vue JS.

      1. Locate the section resolve and add .vue to the array of extensions:

      2. Add the following constant to the top of the file, after the existing plugin imports:

         const VueLoaderPlugin = require('vue-loader/lib/plugin');

      3. Add an alias for Vue:

         resolve: {
           extensions: ['.ts', '.tsx', '.html', '.js', '.vue'],
           alias: {
             vue$: 'vue/dist/vue.js'
           }
         },

      4. Locate the existing TypeScript loader in the modules.rules... it is the one with the test: /\.tsx?$/ as the test rule entry. Replace the entire TypeScript loader with the following so TypeScript code in Vue templates is also transpiled to JavaScript:

         {
           test: /\.tsx?$/,
           exclude: /node_modules/,
           use: [{
             loader: 'ts-loader',
             options: {
               appendTsSuffixTo: [/\.vue$/],
               transpileOnly: true
             }
           }]
         },

      5. Add a new object to the module.rules array so Webpack will use the Vue-specific loader:

         module: {
           rules: [
             ..
             {
               test: /\.vue$/,
               loader: 'vue-loader',
               options: { esModule: true }
             },
             ..
           ]
         },

      6. Add the Vue loader plugin into the collection of plugins:

         plugins: [
           ...
           new VueLoaderPlugin()
         ]

   3. Update the project so that .vue files will be treated like TypeScript. Create a vue-shim.d.ts file in the src folder, and add the following code.

      declare module "*.vue" {
        import Vue from 'vue'
        export default Vue
      }

   4. Locate and open the src/taskpane/taskpane.html file.

      1. Replace the <body> element with the following:

      <body class="ms-font-m ms-welcome ms-Fabric">
        <div id="app">{{welcome}}</div>
      </body>

   5. Locate and open the src/taskpane/taskpane.ts file.

      1. Add the following import statement after the existing import:

         import * as Vue from "vue";

      2. Remove the existing run() function and update Office.onReady as follows:

         Office.onReady(info => {
           if (info.host === Office.HostType.Excel) {
             var app = new Vue({
               el: "#app",
               data: {
                 welcome: "Hello Office!!!"
               }
             });
             console.log(app);
           }
         });

   OPTIONAL: You should be able to run and sideload the add-in at this point. To do that, follow the steps outlined in Sideload and Test the Office Add-in. In the next section, you will add additional functionality to the add-in.

   

Develop the Office Add-in

1. Open src/taskpane/taskpane.css and replace the entire file with following:

   body {
     margin: 0px;
   }
   
   input {
     width: 100%;
     font-size: 14px;
   }
   
   .header {
       padding: 10px;
   }
   
   .content {
       margin-top: 10px;
   }
   
   .hover:hover {
       background: #f8f8f8;
   }
   
   .overlay {
       position: absolute;
       top: 0px;
       bottom: 0px;
       left: 0px;
       right: 0px;
       background: hsla(0,0%,100%,.4);
       z-index: 1000;
   }
   
   .spinner {
       position: absolute;
       top: 50%;
       left: 50%;
       margin-top: -20px;
       margin-left: -20px;
       z-index: 1100;
   }
   
   .ms-MessageBar-content {
       padding: 2px !important;
   }
   
   .pct100 {
       width: 100%;
       float: left;
   }
   
   .padding10 {
       padding: 10px;
   }
   
   .right {
       float: right;
   }
   
   .left {
       float: left;
   }
   
   .icon {
       padding-left: 8px;
       cursor: pointer;
   }
   
   .itemRow {
       padding-top: 4px;
       padding-bottom: 4px;
   }
   .itemRow:hover {
       background-color: #eeeeee;
   }
   
   .tbl-head {
       margin-bottom: 5px;
   }

2. Copy the spinner.gif image from the LabFiles folder into assets folder.

3. Create a new folder components in the existing src/taskpane folder to hold the Vue components.

4. Create a src/taskpane/components/Waiting.vue file and add the following code to it:

   <template>
   <div>
     <div class="overlay"></div>
     <img class="spinner" src="/assets/spinner.gif"/>
   </div>
   </template>
   
   <script lang="ts">
   import * as Vue from "vue";
   import Component from 'vue-class-component';
   
   @Component({})
   export default class waiting extends Vue {
     name: 'waiting'
   }
   </script>

5. Creating a new file src/taskpane/components/HeaderComponent.vue and ad the following code:

   <template>
     <div>
       <div class="ms-bgColor-greenDark header">
         <span class="ms-font-su ms-fontColor-white">{{title}}</span>
       </div>
       <div class="ms-MessageBanner" v-if="error">
         <div class="ms-MessageBanner-content" style="text-align: left; margin-left: 40px;">
           <div class="ms-MessageBanner-text ms-font-s-plus">
             <div class="ms-MessageBanner-clipper">
               <i class="ms-Icon ms-Icon--Error"></i>
               <span style="vertical-align: top;">&nbsp;{{error}}</span>
             </div>
           </div>
         </div>
         <button class="ms-MessageBanner-close" v-on:click="error = null;">
           <i class="ms-Icon ms-Icon--Clear"></i>
         </button>
       </div>
     </div>
   </template>
   
   <script lang="ts">
   import * as Vue from "vue";
   import Component from 'vue-class-component';
   
   @Component({
     props: {
       title: String,
       error: String
     }
   })
   export default class headerComponent extends Vue {
     name: 'headerComponent';
     title: String;
     error: String;
   }
   </script>

6. Creating a new file src/taskpane/components/Stock.vue and ad the following code:

   <template>
     <div class="pct100 itemRow">
       <div class="left ms-font-l">{{symbol}}</div>
       <div class="right">
         <div class="left icon"><i class="ms-Icon ms-Icon--Refresh" aria-hidden="true" v-on:click="refreshSymbol(index)"></i></div>
         <div class="left icon"><i class="ms-Icon ms-Icon--Delete" aria-hidden="true" v-on:click="deleteSymbol(index)"></i></div>
       </div>
     </div>
   </template>
   
   <script lang="ts">
   import * as Vue from "vue";
   import Component from 'vue-class-component';
   
   @Component({
     props: {
       symbol: String,
       index: Number
     },
     methods: {
       refreshSymbol(index:Number) {
         this.$emit("refreshSymbol", index);
       },
       deleteSymbol(index:Number) {
         this.$emit("deleteSymbol", index);
       }
     }
   })
   export default class stock extends Vue {
     name: 'stock';
     symbol: string;
     index: Number;
   }
   </script>

7. Creating a new file src/taskpane/components/Root.vue and ad the following code:

   <template>
   <div>
     <waiting v-if="waiting"></waiting>
     <header-component v-bind:error="error" title="Excel Portfolio"></header-component>
     <div class="padding10">
       <div class="pct100 tbl-head">
         <span class="ms-font-l">Stock Symbols</span>
       </div>
       <div class="pct100">
         <input class="ms-TextField-field"
             v-model="newSymbol"
             v-on:keyup="addSymbol(newSymbol)"
             placeholder="Enter a stock symbol (ex: MSFT)" />
       </div>
       <stock v-for="(symbol, index) in symbols"
           v-bind:key="symbol"
           v-bind:symbol="symbol"
           v-bind:index="index"
           v-on:refreshSymbol="refreshSymbol(index)"
           v-on:deleteSymbol="deleteSymbol(index)"></stock>
       <div class="pct100 itemRow" v-if="symbols.length == 0">
         <em class="ms-font-l">No symbols added</em>
       </div>
     </div>
   </div>
   </template>
   
   <script lang="ts">
     import * as Vue from "vue";
     import Component from 'vue-class-component';
     import waiting from "./Waiting.vue";
     import headerComponent from "./HeaderComponent.vue";
     import stock from "./Stock.vue";
   
     @Component({
       data: function () {
         return {
           symbols: [],
           waiting: false,
           error: "",
           newSymbol: ""
         }
       },
       components: {
         waiting,
         headerComponent,
         stock
       },
       methods: {
         getQuote(symbol:string) {
           //TODO
           console.log(symbol);
         },
         addSymbol(symbol:string) {
           //TODO
           console.log(symbol);
         },
         deleteSymbol(index:number) {
           //TODO
           console.log(index);
         },
         refreshSymbol(index:number) {
           //TODO
           console.log(index);
         },
         syncTable() {
           //TODO
           console.log("sync table");
         }
       },
       mounted: function () {
         (<any>this).syncTable();
       }
     })
     export default class root extends Vue {
       name: 'root'
     }
   </script>

8. Locate and open src/taskpane/taskpane.ts and update it to load the components that you just created in the project.

   1. Add the following import statement after the existing import statements:

      import root from './components/Root.vue';

   2. Update the Office.onReady function to load the Vue root component as follows:

      import * as Vue from 'vue';
      import root from './components/Root.vue';
      
      Office.onReady(info => {
        if (info.host === Office.HostType.Excel) {
          var app = new Vue({
            el: "#app",
            render: h => h(root, {})
          });
          console.log(app);
        }
      });

9. Although the app's functionality isn't complete, the visual markup is complete. To review your changes, save all files and then return to Office Online. Your add-in should look similar to the following screenshot. If you previously closed your browser or if your Office Online session expired (the add-in doesn't load), follow the steps in Sideload the Office Add-in.

   

10. The src/taskpane/components/Root.vue file has a number of placeholder functions that you will complete to get the add-in functioning. Start by locating the getQuote() function. This function calls a REST API to get real-time stock statistics on a specific stock symbol. Update it as seen below.

    getQuote(symbol:string) {
      return new Promise((resolve, reject) => {
        const queryEndpoint = `https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=${escape(symbol)}&apikey=${ALPHAVANTAGE_APIKEY}`;
    
        fetch(queryEndpoint)
          .then((res: any) => {
            if (!res.ok) {
              reject('Error getting quote');
            }
            return res.json();
          })
          .then((jsonResponse: any) => {
            const quote: any = jsonResponse['Global Quote'];
            resolve(quote);
          });
      });
    },

11. Create new utils folder in the src folder, then create a file named ExcelTableUtil.ts. This TypeScript class will contain helper functions for working with Microsoft Excel tables with office.js. Notice the ExcelTableUtil constructor accepts details about the Excel table, including the name, location, and header details.

    export class ExcelTableUtil {
      tableName: string;
      location: string;
      headers: string[];
      constructor(tableName: string, location: string, headers: string[]) {
        this.tableName = tableName;
        this.location = location;
        this.headers = headers;
      }
    }

12. Implement the ExcelTableUtil utility class:

    1. Locate and open the file src/utils/ExcelTableUtil.ts.

    2. Add the following methods ExcelTableUtil class. These methods access the table in Excel, or creates the table if it doesn't exist.

       // Create the StocksTable and defines the header row
       createTable = async () => {
         return new Promise(async (resolve, reject) => {
           await Excel.run(async context => {
             // Create a proxy object for the active worksheet and create the table
             const sheet = context.workbook.worksheets.getActiveWorksheet();
             const tableRef = sheet.tables.add(this.location, true);
             tableRef.name = this.tableName;
             tableRef.getHeaderRowRange().values = [this.headers];
             return context.sync().then(() => {
               resolve(tableRef);
             });
           }).catch(createError => {
             reject(createError);
           });
         });
       }
       
       // Ensures the Excel table is created and tries to get a table reference
       ensureTable = async (forceCreate: boolean) => {
         return new Promise(async (resolve, reject) => {
           await Excel.run(async context => {
             // Create a proxy object for the active worksheet and try getting table reference
             const sheet = context.workbook.worksheets.getActiveWorksheet();
             const tableRef = sheet.tables.getItem(this.tableName);
             return context.sync().then(() => {
               resolve(tableRef);
             });
           }).catch(() => {
             if (forceCreate) {
               // Create a new table because an existing table was not found.
               this.createTable().then(
                 async tableRef => {
                   resolve(tableRef);
                 },
                 createError => {
                   reject(createError);
                 }
               );
             } else {
               resolve(null);
             }
           });
         });
       }

    3. Add the following method to the ExcelTableUtil class.

       Notice that it calls the ensureTable() function we just created to ensure the Excel table has been created.

       // Appends a row to the table
       addRow = async (data) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(
             async (tableRef: Excel.Table) => {
               await Excel.run(async context => {
                 const sheet = context.workbook.worksheets.getActiveWorksheet();
                 // Add the new row
                 tableRef = sheet.tables.getItem(this.tableName);
                 tableRef.rows.add(null, [data]);
                 // Autofit columns and rows if your Office version supports the API.
                 if (Office.context.requirements.isSetSupported('ExcelApi', 1.2)) {
                   sheet.getUsedRange().format.autofitColumns();
                   sheet.getUsedRange().format.autofitRows();
                 }
                 sheet.activate();
                 return context.sync().then(() => {
                   resolve();
                 });
               }).catch(err => {
                 reject(err);
               });
             },
             err => {
               reject(err);
             }
           );
         });
       }

13. Update the Root component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/components/Root.vue file.

    2. Add the following import statement after the existing import statements for the the new ExcelTableUtil class.

       import { ExcelTableUtil } from '../../utils/ExcelTableUtil';

    3. Add the following constant after the import statements and update the {{REPLACE_WITH_ALPHAVANTAGE_APIKEY}} to use your API key.

       const ALPHAVANTAGE_APIKEY: string = '{{REPLACE_WITH_ALPHAVANTAGE_APIKEY}}';

    4. Locate the return statement for the data: function () in the @Component. Add a new property tableUtil to the object returned, as shown int he following code:

       data: function () {
         return {
           symbols: [],
           waiting: false,
           error: "",
           newSymbol: "",
           tableUtil: new ExcelTableUtil('Portfolio', 'A1:H1', [
             'Symbol',
             'Last Price',
             'Timestamp',
             'Quantity',
             'Price Paid',
             'Total Gain',
             'Total Gain %',
             'Value'
           ])
         };
       },

    5. Update the addSymbol() method to the following code:

       // Adds symbol
       addSymbol(symbol: string) {
         if ((<KeyboardEvent>event).key == "Enter") {
           this.waiting = true;
           this.getQuote(symbol).then((res:any) => {
             let cnt = this.symbols.length;
             const data = [
               res['01. symbol'], //Symbol
               res['05. price'], //Last Price
               res['07. latest trading day'], // Timestamp of quote,
               0, // quantity (manually entered)
               0, // price paid (manually entered)
               `=(B${cnt+2} * D${cnt+2}) - (E${cnt+2} * D${cnt+2})`, //Total Gain $
               `=H${cnt+2} / (E${cnt+2} * D${cnt+2}) * 100 - 100`, //Total Gain %
               `=B${cnt+2} * D${cnt+2}` //Value
             ];
             this.tableUtil.addRow(data).then(() => {
               this.symbols.unshift(symbol.toUpperCase());
               this.waiting = false;
               this.newSymbol = "";
             }, (err) => {
               this.error = err;
             });
           }, (err) => {
             this.error = err;
             this.waiting = false;
           });
         }
       },

       Note: This is a good time to test the addSymbol() function of your add-in.

14. Update the ExcelTableUtil utility to add support for accessing and deleting rows:

    1. Locate and open the src/utils/ExcelTableUtil.ts file.

    2. Add the following methods to the ExcelTableUtil class:

       // Gets data for a specific named column
       getColumnData = async (column:string) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(false).then(async (tableRef:Excel.Table) => {
             if (tableRef == null)
               resolve([]);
             else {
               await Excel.run(async (context) => {
                 // Get column range of values by column name.
                 const sheet = context.workbook.worksheets.getActiveWorksheet();
                 tableRef = sheet.tables.getItem(this.tableName);
                 var colRange = tableRef.columns.getItem(column).getDataBodyRange().load("values");
       
                 // Sync to populate proxy objects with data from Excel
                 return context.sync().then(async () => {
                   let data:string[] = [];
                   for (var i = 0; i < colRange.values.length; i++) {
                     data.push(colRange.values[i].toString());
                   }
                   resolve(data);
                 });
               }).catch((err) => {
                 reject(err);
               });
             }
           }, (err) => {
             reject(err);
           });
         });
       }
       
       // Deletes a column using the row index.
       deleteRow = async (index:number) => {
         return new Promise(async (resolve, reject) => {
           this.ensureTable(true).then(async (tableRef:Excel.Table) => {
             await Excel.run(async (context) => {
               var range = tableRef.rows.getItemAt(index).getRange();
               range.delete(Excel.DeleteShiftDirection.up);
               return context.sync().then(async () => {
                 resolve();
               });
             }).catch((err) => {
               reject(err);
             });
           }, (err) => {
               reject(err);
           });
         });
       }

15. Update the Root component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/components/Root.vue file.

    deleteSymbol(index:number) {
      // Delete from the Excel table using the index number.
      let symbol = (<any>this).symbols[index];
      (<any>this).waiting = true;
      (<any>this).tableUtil.getColumnData("Symbol").then(async (columnData:string[]) => {
          // Make sure the symbol was found in the Excel table
          if (columnData.indexOf(symbol) != -1) {
              (<any>this).tableUtil.deleteRow(columnData.indexOf(symbol)).then(async () => {
                  (<any>this).symbols.splice(index, 1);
                  (<any>this).waiting = false;
              }, (err) => {
                  (<any>this).error = err;
                  (<any>this).waiting = false;
              });
          }
          else {
              (<any>this).symbols.splice(index, 1);
              (<any>this).waiting = false;
          }
      }, (err) => {
          (<any>this).error = err;
          (<any>this).waiting = false;
      });
    },

    Optional: This is a good time to test the deleteSymbol function of your add-in.

16. Update the ExcelTableUtil utility to add support for refreshing rows in the table:

    1. Locate and open the src/utils/ExcelTableUtil.ts file.

    2. Add the following methods to the ExcelTableUtil class:

       // Updates a specific cell in the table
       updateCell = async (address:string, value:any) => {
           return new Promise(async (resolve, reject) => {
               this.ensureTable(true).then(async () => {
                   await Excel.run(async (context) => {
                       var sheet = context.workbook.worksheets.getActiveWorksheet();
                       var range = sheet.getRange(address);
                       range.values = [[value]];
                       return context.sync().then(async () => {
                           resolve();
                       });
                   }).catch((err) => {
                       reject(err);
                   });
               }, (err) => {
                   reject(err);
               });
           });
       }

17. Update the Root component to leverage the methods you added to the ExcelTableUtil class.

    1. Locate and open the src/taskpane/components/Root.vue file.

    2. Locate and update the refreshSymbol() method to specify a symbol to refresh in the Excel table.

       refreshSymbol(index:number) {
         // Refresh stock quote and update the Excel table.
         let symbol = this.symbols[index];
         this.waiting = true;
         this.tableUtil.getColumnData("Symbol").then(async (columnData:string[]) => {
           // Ensure the symbol was found in the Excel table
           var rowIndex = columnData.indexOf(symbol);
           if (rowIndex != -1) {
             this.getQuote(symbol).then((res:any) => {
                 // "last trade" is in column B with a row index offset of 2 (row 0 + the header row)
                 this.tableUtil.updateCell(`B${rowIndex + 2}:B${rowIndex + 2}`, res["05. price"]).then(async () => {
                 this.waiting = false;
               }, (err) => {
                 this.error = err;
                 this.waiting = false;
               });
             });
           }
           else {
             this.error = `${symbol} not found in Excel`;
             this.symbols.splice(index, 1);
             this.waiting = false;
           }
         }, (err) => {
           this.error = err;
           this.waiting = false;
         });
       },

    Optional: This is a good time to test the refreshSymbol function of your add-in.

    1. Finally, update the syncTable() method to the following:

       syncTable() {
         this.waiting = true;
         this.tableUtil.getColumnData("Symbol").then(async (columnData:string[]) => {
           this.symbols = columnData;
           this.waiting = false;
         }, (err) => {
           this.error = err;
           this.waiting = false;
         });
       }

The Excel Portfolio add-in written with Vue.js and TypeScript is now complete. Follow the steps to Sideload and Test the Office Add-in.

Exercise 4: Sideload and Test the Office Add-in

This section will outline how to sideload and test an Office Add-in using OneDrive and Office Online.

Note: The instructions below outline how to sideload an Office Add-in into Office Online, which works in almost any developer environment. If you are working from a PC, you can also sideload the add-in for testing in the full Win32 Office client. For more information on this approach, see the Sideloading Office Add-ins into Office Desktop or Office Online.

Note: Office Add-ins are required to be secured by SSL. These labs leverage self-signed certificates for this that may be blocked by your browser as an untrusted certificate. If so, follow the steps for Adding Self-Signed Certificates as Trusted Root Certificate.

1. Open a terminal/command prompt in the location where the project is provisioned.

2. Execute the command npm run start to which will build and start the solution.

   This command is setup to perform a lot of complex tasks, including:

   • compiling all the TypeScript files to JavaScript
   • using Webpack to create a single bundle of all script and CSS resources
   • copy all relevant files to the dist folder

   For the React and Angular labs, the TypeScript compiler will also stay in a "watch mode" to refresh the add-in when code changes are made.

   If you need to exit "watch mode", run npm run stop in the command prompt / terminal.

3. Use one of these methods to sideload and test the Office Add-in.

   • Windows: Sideload Office Add-ins on Windows
   • Office Online: Sideload Office Add-ins in Office Online
   • iPad and Mac: Sideload Office Add-ins on iPad and Mac

4. Select the Show Taskpane button to bring up your Office Add-in in a task pane.

5. You should test all the different operations you created:

   • Add a symbol by typing the symbol and pressing enter or return
   • Refresh a symbol (helps to clear out the Last Price cell when outside trading hours)
   • Delete a symbol
   • Reload the add-in with an existing portfolio table and see if the add-in pulls in the symbols