with JavaScript


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  // 14.15.5 
npm -v // 6.14.11

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 Microsoft Edge 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>
<body>
   <h1>Excel Demo</h1>
   <div id="app"></div>
   <script src="webpackbundle.js"></script>
</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.js'.

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 a Microsoft Edge browser and type in the URL - localhost:8080
SS


Edit Index.html

We need to add a reference to the Office.js file.
Drag the 'index.html' file into NotePad and make the necessary changes.
Add this additional script reference.

<!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>
   <script src="webpackbundle.js"></script>
</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

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="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-excel


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