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\notepad) folder.
Create a new subfolder called "excel-typescript" (C:\temp\notepad\excel-typescript).
Open a command prompt (as administrator).
Change to that folder and initialise npm.
cd c:\temp\notepad\excel-typescript
npm init
Press Enter to accept all the defaults.
This will create a "package.json" file in the folder.
(C:\temp\notepad\excel-typescript).
Install Webpack
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". Remembering to add a comma to the line above.
{
"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.17.7",
"@babel/preset-typescript": "^7.16.7",
"@types/office-js": "^1.0.240",
"babel-loader": "^8.2.3",
"html-webpack-plugin": "^5.5.0",
"typescript": "^4.6.2",
"webpack": "^5.70.0",
"webpack-cli": "^4.9.2",
"webpack-dev-server": "^4.7.4"
}
}
Create index.html
Open File Explorer in the root folder (C:\temp\notepad\excel-typescript).
Create a new subfolder called 'code' (C:\temp\notepad\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\notepad\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>';
};
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\notepad\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 + '/index.html',
filename: 'index.html',
inject: 'body'
});
module.exports = {
entry: __dirname + '/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\notepad\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.
Load the Add-in - Desktop
The add-in can be sideloaded into your Excel client.
You can then side load this add-in using a Shared Folder.
Select the Developer Tab and select "Add-ins".
SS
The task pane will be displayed as soon as you press Add.
Select cell "B2" and press the "Add Data" button.
SS
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext