JavaScript Excel API - with TypeScript

with TypeScript

Before you read this page you should read with JavaScript

Generate package.json file

Open File Explorer and browse to the (C:\temp\officejs) folder.
Create a new subfolder called "excel-typescript" (C:\temp\officejs\excel-typescript).
Open a command prompt (as administrator).
Change to that folder and initialise npm.

cd c:\temp\officejs\excel-typescript 
npm init

Press Enter to accept all the defaults.
This will create a "package.json" file in the folder.
(C:\temp\officejs\excel-typescript).

Install Webpack

more information

npm install --save-dev webpack 
npm install --save-dev webpack-cli 
npm install --save-dev webpack-dev-server 
npm install --save-dev html-webpack-plugin

Install Babel

more information
This lets you transpile TypeScript ES 2015 (ES6) into JavaScript ES 2015 (ES6).

npm install --save-dev babel-loader 
npm install --save-dev @babel/core 
npm install --save-dev @babel/preset-typescript

Install TypeScript

more information
This lets you use the TypeScript compiler

npm install --save-dev typescript

Install Type Definitions

This lets you

npm install --save-dev @types/office-js

Edit package.json file

Open NotePad++ and drag the "package.json" file onto it.
Delete the main parameter.
Add a script for "start".

{ 
  "name": "excel-typescript", 
  "version": "1.0.0", 
  "description": "", 
  "scripts": { 
    "test": "echo \"Error: no test specified\" && exit 1", 
    "start": "webpack-dev-server --mode development" 
  }, 
  "author": "", 
  "license": "ISC", 
  "devDependencies": { 
    "@babel/core": "^7.14.3", 
    "@babel/preset-typescript": "^7.13.0", 
    "@types/office-js": "^1.0.181", 
    "babel-loader": "^8.2.2", 
    "html-webpack-plugin": "^5.3.1", 
    "typescript": "^4.3.2", 
    "webpack": "^5.38.1", 
    "webpack-cli": "^3.3.12", 
    "webpack-dev-server": "^3.11.2" 
  } 
}

Create index.html

Open File Explorer in the root folder (C:\temp\officejs\excel-typescript).
Create a new subfolder called 'code' (C:\temp\officejs\excel-typescript\code).
Create a NotePad file with the following contents and save it as 'index.html'.
This file contains html code and includes a link to Office.js.

<!DOCTYPE html> 
<html> 
<head> 
   <script src="https://appsforoffice.microsoft.com/lib/1/hosted/Office.js" 
        type="text/javascript"></script> 
</head> 
<body> 
<h1>Excel Demo</h1> 
<div id="app"></div> 
</body> 
</html>

Create index.ts

Open File Explorer in the folder (C:\temp\officejs\excel-typescript\code).
Create a NotePad file with the following contents and save it as 'index.ts'.

window.onload = function Start() { 
   console.log('hello world'); 

   var app_1 = document.getElementById("app"); 
   app_1.innerHTML = app_1.innerHTML + '<br>' +  
      '<input ' +
      'type="button" ' +
      'value="Add Data" ' +
      'onclick="loadExcelData();" />';
   app_1.innerHTML = app_1.innerHTML + '<br><br><br>' +  
      '<textarea ' +
      'id="textareaID" ' +
      'rows="10" ' +
      'cols="35">' +
      'console.log' +
      '</textarea>';
}; 

(function () { 
   Office.initialize = function (reason) { 
   }; 
})(); 

interface Window { 
   loadExcelData(): any; 
} 
window.loadExcelData = loadExcelData; 

function loadExcelData() { 
   window.console.log('excel data loaded'); 

   Excel.run(function (ctx) { 

      let myNumber: any; 
      let wbk: Excel.Workbook = ctx.workbook; 
      let wsh: Excel.Worksheet = wbk.worksheets.getActiveWorksheet(); 
      wsh.load("items/name"); 

      myNumber = [[Math.floor(Math.random() * 1000)]]; 
      wsh.getRange("B2").values = myNumber; 

      AddToListBox1(myNumber.toString()); 

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

            AddToListBox2(wsh); 
         }) 
   }); 
} 

function AddToListBox1( 
   someText: string) { 

   var element2 = document.getElementById("textareaID"); 
   element2.innerText = someText + repeatStringNumTimes(' ', 35 - someText.length) + element2.innerText; 
} 

function AddToListBox2( 
   _wsh: Excel.Worksheet) { 

   var thetext = _wsh.name; 
   var element2 = document.getElementById("textareaID"); 
   element2.innerText = thetext + repeatStringNumTimes(' ', 35 - thetext.length) + element2.innerText; 
} 

function repeatStringNumTimes( 
   _string: string, 
   _times: number): string { 

   let repeatedString: string = ""; 
   while (_times > 0) { 
      repeatedString += _string; 
      _times--; 
   } 
   return repeatedString; 
}

Create webpack.config.js file

Open File Explorer in the root folder (C:\temp\officejs\excel-typescript).
Create a NotePad file with the following contents and save it as 'webpack.config.js'.
This file tells webpack the location and filename of our top level/entry typescript file ("index.ts").
This file tells webpack to bundle all the javascript files into a single file called "webpackbundle.js".

var HTMLWebpackPlugin = require('html-webpack-plugin');    
var HTMLWebpackPluginConfig = new HTMLWebpackPlugin({ 
   template: __dirname + '/code/index.html',    
   filename: 'index.html',    
   inject: 'body'    
}); 

module.exports = { 
   entry: __dirname + '/code/index.ts',    
   output: { 
      filename: 'webpackbundle.js',    
      path: __dirname + '/build'  
   }, 
   module: { 
      rules: [ 
          { 
             test: /.ts$/, 
             exclude: /node_modules/, 
             use: { 
                loader: 'babel-loader',    
                options: { 
                   presets: [ '@babel/preset-typescript' ]  
                } 
             } 
          } 
      ] 
   }, 
   plugins: [HTMLWebpackPluginConfig] 
};

The "webpackbundle.js" file will be automatically generated and used on the local server.
This file is not saved or accessible anywhere in the root folder (C:\temp\officejs\excel-typescript).

Launch Browser

You are now in a position to launch webpack and start the local development server.
Open a command prompt (as administrator).
Change to the corresponding folder and execute the following 2 commands.

node ./node_modules/webpack/bin/webpack --mode development 
npm run start

Open your browser and display the URL - localhost:8080

Create manifest.xml

Create a NotePad file with the following contents and save it as 'manifest.xml'.

<?xml version="1.0" encoding="UTF-8"?> 
<OfficeApp 
  xmlns="http://schemas.microsoft.com/office/appforoffice/1.1" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0" 
  xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides" 
  xsi:type="TaskPaneApp"> 

  <Id> Unique GUID </Id> 
  <Version>1.0.0.0</Version> 
  <ProviderName>Better Solutions Limited</ProviderName> 
  <DefaultLocale>en-US</DefaultLocale> 
  <DisplayName DefaultValue="ExcelTypeScriptAddIn1" /> 
  <Description DefaultValue="ExcelTypeScriptAddIn1"/> 
  <SupportUrl DefaultValue="https://bettersolutions.com"/> 
  <AppDomains> 
    <AppDomain>AppDomain1</AppDomain> 
  </AppDomains> 
  <Hosts> 
    <Host Name="Workbook" /> 
  </Hosts> 
  <DefaultSettings> 
    <SourceLocation DefaultValue="http://localhost:8080/index.html" /> 
  </DefaultSettings> 
  <Permissions>ReadWriteDocument</Permissions> 
</OfficeApp>

You must include a support URL.
Enter a valid GUID Id (visit www.guidgen.com)
Save the changes.
You can then side load this add-in using a Shared Folder.