with JavaScript

You can sideload an Office Add-in written using NotePad.
We can sideload this add-in in two different ways.
Either using Visual Studio or by creating a Trusted Catalog.


Install node.js and npm

more information
visit nodejs.org and download for Windows.
When you download node.js you automatically get npm installed as well.
This installs into the following folder:

C:\program Files\nodejs\ 

You will need to restart your computer.
Check that node and npm are installed.

node -v 
npm -v

Generate package.json file

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

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

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


Install Webpack

more information
This lets you bundle all your javascript files into a single file and provides a local development server.

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

This will create a new subfolder called "node_modules".
(C:\temp\officejs\excel-javascript\node_modules).
The '--save-dev' switch will add these modules as a devDependency in the package.json file.


Install Babel

more information
This lets you transpile ES 2015 (ES6) Javascript into ES 2009 (ES5) Javascript code.

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

View package.json file

Open a Chrome browser and drag the "package.json" file onto a new tab to view the contents.
Alternatively open NotePad++ and drag the "package.json" file onto it.

{ 
  "name": "excel-javascript",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "devDependencies": {
    "@babel/core": "^7.4.5",
    "babel-loader": "^8.0.6",
    "webpack": "^4.33.0",
    "webpack-cli": "^3.3.4",
    "webpack-dev-server": "^3.7.1"
  },
}

Create index.html

Open File Explorer in the root folder (C:\temp\officejs\excel-javascript).
Create a NotePad file with the following contents and save it as 'index.html'.
This file contains html code that links to webpackbundle.js.

<!DOCTYPE html> 
<html>
<head>
   <script src="webpackbundle.js"></script>
</head>
<body>
<h1>Excel Demo</h1>
<div id="app"></div>
</body>
</html>

Create index.js

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

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

   var app_1 = document.getElementById("app");
   app_1.innerHTML = '<b>hello world</b>';
}

Create webpack.config.js file

Open File Explorer in the root folder (C:\temp\officejs\excel-javascript).
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 javascript file ("index.js").
This file tells webpack to bundle all the javascript files into a single file called "webpackbundle.js".

module.exports = { 
  devtool: "source-map",
  entry: __dirname + '/index.js',
  output: {
      filename: 'webpackbundle.js'
   },
  module: {
    rules: [
      {
        test: /.js?$/,
        exclude: /node_modules/,
        use: {
          loader: 'babel-loader'
        }
      }
    ]
  }
};

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-javascript).


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.

cd c:\temp\officejs\excel-javascript 

Execute the following 2 commands.

node ./node_modules/webpack/bin/webpack --mode development 
node ./node_modules/webpack-dev-server/bin/webpack-dev-server --mode development

Open your browser and display the URL - localhost:8080


Edit Index.html

We need to add a reference to the Office.js file
Add this additional script reference.

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

Edit index.js

Include some javascript that will interact with the active workbook.
This code will add a random number to cell "B2".
Add another line to the window.onload event.
Include an empty Office.initialize function.
Include a loadExcelData function that will run when the button is pressed.

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

   var app_1 = document.getElementById("app");
   app_1.innerHTML = '<b>hello world</b>';

   app_1.innerHTML = app_1.innerHTML +
'<br><input type="button" value="Add Data" onclick="loadExcelData();" />';
}

Office.initialize = function (reason) {
}

window.loadExcelData = loadExcelData;

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

    funWrite = function fun_write(message) {
        console.log('fun_write called');

        var app_1 = document.getElementById("message");
        app_1.innerHTML += message + '<br>';
    };

   Excel.run(function (ctx) {
      var sheet = ctx.workbook.worksheets.getActiveWorksheet();
      sheet.getRange("B2").values = [ [Math.floor(Math.random() * 1000)] ];
      return ctx.sync();
   });

   funWrite('initial text');
}

Create manifest.xml

Open File Explorer in the root folder (C:\temp).
Create a new subfolder called 'shared-catalog' (C:\temp\shared-catalog).
Right mouse click and select Properties
Click on the Sharing tab and press Share
The Network access dialog box appears
Add yourself and press Share. Press Done.
Open Excel, File > Options > Trust Center
Press Trust Center Settings and choose Trusted Add-in Catalogs
In the Catalog Url enter the full folder path (\\machinename\shared-catalog)
press Add Catalog, Tick "Show in Menu" and press OK
Close Excel.
Open File Explorer in the folder (C:\temp\shared-catalog).
Create a NotePad file with the following contents and save it as 'manifest.xml'.
For more information on individual elements refer to the page under Manifest > Elements.

<?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="ExcelJavaScriptAddIn1" />
  <Description DefaultValue="ExcelJavaScriptAddIn1"/>
  <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.


GitHub Repo

You can view the project at - github.com/OfficeAddins/helloworld


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext