visualstudio

Testing Office.JS: The Power of Choice

December 22, 2016July 3, 2017 David E. CraigLeave a comment

github

You know, sometimes the way you want to do things is not necessarily the right way. And even then the right way makes life so hard you want to cry. With Office.JS I recently blogged about “a way” you can test multiple sprints of your add-in in your development environment: Testing a Web Add-in with Multiple Manifest. In this article I explain a method of creating multiple manifests to point to each “version or sprint” of your add-in. Recently, I found that one of my customers threw a wrinkle into that idea. Not that creating multiple manifests was impossible, but they were needing two conflating factors:

Each sprint points to a different folder of HTML/JS.
And they needed to use query string parameters (like this post) to load one of maybe 20 different configuration settings.

With up to 20 configurations per sprint and lets say 4 sprints, you end up with 80 manifest files. So, this is untenable. You need more choices, then enter necessity…

So, lets say you need to test multiple configurations and multiple sprints. Here is how you will set this up:

You will publish each sprint of your Web Site Project into a different folder in IIS. Like this:

Then, you will create a new HTML page. I called mine Launcher.html. This page will contain a submit button and two drop-down select controls:
- One for sprints
- One for configs
Next, you will modify your manifest. You can add a new button called “Sprint Testing” for example and have it launch: Launcher.html.
In the Launcher.JS, populate those dropdowns. You can populate these hard coded into the HTML, from your client side JS or call your service controller. In my example, I call my service controller.
When the user clicks the submit button after choosing the desired settings, you will build the URL to the proper sprint folder and then append the config values to the current query string (that last item is critical so that your sprint Compose/Read page will get the important Office related query string values passed to it). You will see I do this below by replacing my “launcher.html”, replacing it will the new path, then append the config setting:

[code language=”javascript”]
url = window.location.href.replace("launcher.html", sprint + "/ComposeMessage.html");
url += "&Config=" + config;
[/code]

Once done your code will perform a location.replace() on the url and you will have your proper sprint and configuration loaded. You will only have a single manifest file and you will be able to test/regress test as much as needed with different sprints and configurations.

Here is what the HTML page I created looks like when loaded:

Here is the HTML:

[code lang=”javascript” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
<!DOCTYPE html>
<html>
<head>
<title>Select Which Sprint to Launch</title>
<meta charset="utf-8" />
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
<script src="Scripts/jquery-3.1.1.js" type="text/javascript"></script>
<script src="launcher.js" type="text/javascript"></script>
</head>
<body>
<h3>Please select the sprint you wish to launch:</h3>
<select id="selectSprintList">
</select>
<h3>Please select the configuration:</h3>
<select id="selectConfigList">
</select>
<button id="launchSprintButton">Submit</button>
</body>
</html>
[/code]

	<!DOCTYPE html>
	<html>
	<head>
	<title>Select Which Sprint to Launch</title>
	<meta charset="utf-8" />
	<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
	<script src="Scripts/jquery-3.1.1.js" type="text/javascript"></script>
	<script src="launcher.js" type="text/javascript"></script>
	</head>
	<body>
	<h3>Please select the sprint you wish to launch:</h3>
	<select id="selectSprintList"></select>
	<h3>Please select the configuration:</h3>
	<select id="selectConfigList"></select>
	<button id="launchSprintButton">Submit</button>
	</body>
	</html>

view raw

Launcher.html

hosted with ❤ by GitHub

Here is the code behind for the page:

[code lang=”javascript” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
/// <reference path="Scripts/jquery-3.1.1.js" />
(function () {
‘use strict’;

Office.initialize = function (reason) {
$(document).ready(function (reason) {
/// Upon load connect to the server and request
/// the sprints so that we can fill the select
/// field on the form.
makeAjaxCall("GetSprints", null, function (response) {
/** @type {String} */
var data = response.Message.toString();
/** @type {String[]} */
var results = data.split(",");
$.each(results, function (index, value) {
$("#selectSprintList").append("
<option>" + value + "</option>");
})
}, function (error) {
$(document).append("

" + error + "

");
});

makeAjaxCall("GetConfigs", null, function (response) {
/** @type {String} */
var data = response.Message.toString();
/** @type {String[]} */
var results = data.split(",");
$.each(results, function (index, value) {
$("#selectConfigList").append("
<option>" + value + "</option>");
})
});

$("#launchSprintButton").click(function (event) {
/// The user clicked the submit button. Build the URL
/// from the selection in the select control and
/// change the location
var sprint = $("#selectSprintList").val();
var config = $("#selectConfigList").val();
var url = "";
if (sprint != null && sprint != "" &&
config != null && sprint != "") {
// replace the launcher page with the proper sprint folder location,
// but be sure to keep all the query string values – to pass on
// to our OfficeJS page. However, add our one config setting to the end
url = window.location.href.replace("launcher.html", sprint + "/ComposeMessage.html");
url += "&Config=" + config;
// replace the url
location.replace(url);
}
});

});
}
})();

// Helper function to call the web service controller
makeAjaxCall = function (command, params, callback, error) {
var dataToPassToService = {
Command: command,
Params: params
};
$.ajax({
url: ‘api/Default’,
type: ‘POST’,
data: JSON.stringify(dataToPassToService),
contentType: ‘application/json;charset=utf-8’,
headers: { ‘Access-Control-Allow-Origin’: ‘*’ },
crossDomain: true
}).done(function (data) {
callback(data);
}).fail(function (status) {
error(status.statusText);
})
};
[/code]

	/// <reference path="Scripts/jquery-3.1.1.js" />
	(function () {
	'use strict';

	Office.initialize = function (reason) {
	$(document).ready(function (reason) {
	/// Upon load connect to the server and request
	/// the sprints so that we can fill the select
	/// field on the form.
	makeAjaxCall("GetSprints", null, function (response) {
	/** @type {String} */
	var data = response.Message.toString();
	/** @type {String[]} */
	var results = data.split(",");
	$.each(results, function (index, value) {
	$("#selectSprintList").append("<option>" + value + "</option>");
	})
	}, function (error) {
	$(document).append("<br/><p>" + error + "<p>");
	});

	makeAjaxCall("GetConfigs", null, function (response) {
	/** @type {String} */
	var data = response.Message.toString();
	/** @type {String[]} */
	var results = data.split(",");
	$.each(results, function (index, value) {
	$("#selectConfigList").append("<option>" + value + "</option>");
	})
	});

	$("#launchSprintButton").click(function (event) {
	/// The user clicked the submit button. Build the URL
	/// from the selection in the select control and
	/// change the location
	var sprint = $("#selectSprintList").val();
	var config = $("#selectConfigList").val();
	var url = "";
	if (sprint != null && sprint != "" &&
	config != null && sprint != "") {
	// replace the launcher page with the proper sprint folder location,
	// but be sure to keep all the query string values – to pass on
	// to our OfficeJS page. However, add our one config setting to the end
	url = window.location.href.replace("launcher.html", sprint + "/ComposeMessage.html");
	url += "&Config=" + config;
	// replace the url
	location.replace(url);
	}
	});

	});
	}
	})();

	// Helper function to call the web service controller
	makeAjaxCall = function (command, params, callback, error) {
	var dataToPassToService = {
	Command: command,
	Params: params
	};
	$.ajax({
	url: 'api/Default',
	type: 'POST',
	data: JSON.stringify(dataToPassToService),
	contentType: 'application/json;charset=utf-8',
	headers: { 'Access-Control-Allow-Origin': '*' },
	crossDomain: true
	}).done(function (data) {
	callback(data);
	}).fail(function (status) {
	error(status.statusText);
	})
	};

view raw

Launcher.js

hosted with ❤ by GitHub

And here is the code for the controller, now, I cheated and just returned a string value for each call, but this at least help you get the idea of how to build your own list for sprints and configs:

[code lang=”javascript” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace OutlookLauncherDemoWeb.Controllers
{
public class DefaultController : ApiController
{
///
<summary>
/// Service Request – incoming
/// </summary>

public class WebServiceRequest
{
public string Command { get; set; }
public string[] Params { get; set; }
}

///
<summary>
/// Service Response – outgoing
/// </summary>

public class WebServiceResponse
{
public string Status { get; set; }
public string Message { get; set; }
}

[HttpPost()]
public WebServiceResponse Values(WebServiceRequest request)
{
WebServiceResponse response = null;
switch (request.Command)
{
case "GetSprints":
return getSprints();
case "GetConfigs":
return getConfigs();
}

response = new WebServiceResponse();
response.Message = "Unknown command";
return response;
}

private WebServiceResponse getConfigs()
{
WebServiceResponse response = new WebServiceResponse();
response.Message = "Config1,Config2,Config3,Config4,Config5,Config6,Config7,Config8";
return response;
}

private WebServiceResponse getSprints()
{
WebServiceResponse response = new WebServiceResponse();
response.Message = "sprint1,sprint2,sprint3,sprint4";
return response;
}
}
}
[/code]

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Net;
	using System.Net.Http;
	using System.Web.Http;

	namespace OutlookLauncherDemoWeb.Controllers
	{
	public class DefaultController : ApiController
	{
	/// <summary>
	/// Service Request – incoming
	/// </summary>
	public class WebServiceRequest
	{
	public string Command { get; set; }
	public string[] Params { get; set; }
	}

	/// <summary>
	/// Service Response – outgoing
	/// </summary>
	public class WebServiceResponse
	{
	public string Status { get; set; }
	public string Message { get; set; }
	}

	[HttpPost()]
	public WebServiceResponse Values(WebServiceRequest request)
	{
	WebServiceResponse response = null;
	switch (request.Command)
	{
	case "GetSprints":
	return getSprints();
	case "GetConfigs":
	return getConfigs();
	}

	response = new WebServiceResponse();
	response.Message = "Unknown command";
	return response;
	}

	private WebServiceResponse getConfigs()
	{
	WebServiceResponse response = new WebServiceResponse();
	response.Message = "Config1,Config2,Config3,Config4,Config5,Config6,Config7,Config8";
	return response;
	}

	private WebServiceResponse getSprints()
	{
	WebServiceResponse response = new WebServiceResponse();
	response.Message = "sprint1,sprint2,sprint3,sprint4";
	return response;
	}
	}
	}

view raw

DefaultWebController.cs

hosted with ❤ by GitHub

The idea here is that this add-in is a baseline for yours. You can build these components into a debug build of your add-in and it will call into each separate folder/sprint where you have posted previous versions.

I have published the full source up on GitHub: https://github.com/davecra/OutlookLauncherDemo

Automating Excel and Add-ins Don’t load

December 16, 2016July 3, 2017 David E. CraigLeave a comment

There is a common problem I see when automating Excel from an external .NET application:

You will launch Excel, open a workbook, perform work, and then try to close Excel and find that it remains in memory. You might even have code in your solution to try to re-use existing instance of Excel and find that it uses one of these “zombie” instances that do not have any of the add-ins loaded… and maybe an add-in your code relies on.

The issue is that Excel is not able to close because the primary thread of your application is holding on to a COM reference. Even adding Marshal.ComReleaseObject() and GC.Collect() like this probably will not improve the situation either:

	Marshal.ReleaseComObject(excelWorkbook);
	Marshal.ReleaseComObject(excelApp);
	excelApp = null;
	excelWorkbook = null;
	GC.Collect();
	GC.Collect();
	GC.WaitForPendingFinalizers();
	GC.WaitForFullGCComplete();

view raw

COMRelease.cs

hosted with ❤ by GitHub

From my experience, and from what I have gathered is that until the thread is stopped, the COM reference will be locked. Not sure where the issue lies (EXCEL, COM, or .NET), but I have found a workaround to this. What you have to do is terminate your thread. But because you do not want to terminate your application, you need to create a NEW thread. So the following pattern is what I have found works:


	OpenFileDialog openFileDialog1 = new OpenFileDialog();
	openFileDialog1.Filter = "Excel Files\|.xls";
	DialogResult result = openFileDialog1.ShowDialog();
	if (result == DialogResult.OK)
	{
	new Thread(() =>
	{
	Excel.Application excelApp = null;
	Excel.Workbook excelWorkbook = null;

	this.Invoke(new Action(() =>
	{
	try
	{
	excelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
	}
	catch
	{
	excelApp = new Excel.Application();
	}

	string workbookPath = openFileDialog1.FileName;
	excelWorkbook = excelApp.Workbooks.Open(workbookPath);

	excelApp.Visible = true;

	}));

	Marshal.ReleaseComObject(excelWorkbook);
	Marshal.ReleaseComObject(excelApp);
	excelApp = null;
	excelWorkbook = null;
	openFileDialog1 = null;
	GC.Collect();
	GC.Collect();
	GC.WaitForPendingFinalizers();
	GC.WaitForFullGCComplete();
	}).Start();
	}

view raw

ExcelWorkaround.cs

hosted with ❤ by GitHub

Outlook Export Calendar to Word Add-in

December 13, 2016December 19, 2016 David E. CraigLeave a comment

I have been working with a number of customers over the years that come from the world of Lotus Notes. And one of the areas they often complain about with regards to Outlook is the calendar printing options. There are certain things you just cannot do in Outlook from the printing perspective that leaves them wont for more.

So, I have been working over the years on this add-in. This has actually gone through a few iterations – the current version 1.2.0.9 is the most recent and most fully-featured version.

The full source code is on GitHub, here. It is totally open source and free to use, modify, etc. Here is what it can do:

Printing calendars not available in Outlook by default.
The ability to create your own custom calendar
The ability to combine calendars for multiple people at once:
- Displaying only overlapping schedules on the same calendar
- Displaying all meetings including overlapping meeting
The ability to export in daily, weekly, by-weekly, tri-weekly or monthly formats.

The exact details on customization, installation and usage are all covered in the user guide, here.

Developing an On-premises Web Add-in

October 3, 2016December 19, 2016 David E. Craig3 Comments

A customer I was working with wanted help developing a fully on-premises Outlook web add-in. By this, they wanted no part of it to reach out to the Internet (Azure or Office 365). They wanted:

To connect to their internal Exchange server
An internal IIS website
And no references to the Internet (including the Office.js).

This is the topology we are trying to achieve:

topology

If you have developed an Office Web Add-in lately, you find it is inherently biased to the Internet. Even the samples and solutions provided assume Office 365/Exchange and Azure websites. In a default, new Visual Studio solution, the links to the Office.js libraries and stylesheets are all pointing to the web. And, so as you might expect, there are some challenges to getting it to work on-premises only.

This posting covers what you must do to get such a solution to work, including getting past some pitfalls.

First, you have to download the Office.js files locally. And especially for Outlook because the Office.js files that are provided by default in your solution folder (“for offline debugging” as part of VS2015U3 or earlier) are missing some features to work with specific builds of Outlook 2013 and Outlook 2016. You will run into some strange “type” missing and “Office not defined” errors if you forget this step.
Once you have downloaded the Office.JS files, you will delete all the files under the Scripts\Office\1 folder and copy in the contents you downloaded in step 1.
Next, find all your HTML pages where you have the following reference:[code language=”html”]
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>;
[/code]
Comment out that line and add the following two lines:
[code language=”html”]
<!– <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript></script> –>
<script src="../../Scripts/Office/MicrosoftAjax.js" type="text/javascript"></script>;
<script src="../../Scripts/Office/1.1/office.js" type="text/javascript"></script>;
[/code]
Once you have developed your solution, you must setup your IIS server. In general here is what you must do:
- The IIS Server must have ASP.NET installed, it must have .NET4 installed and you must have the Web Application role enabled.
- Open IIS Manager
- Create a site, and figure the folder path
- Convert the site to an Application
- Apply an SSL certificate that is already trusted on all your client computers or that has a root certificate authority that is trusted on all your client computers. If you browse to your site using HTTPS and you get a RED warning about an untrusted site, then the certificate is not trusted or properly setup.
Next, and this was a major issue to troubleshoot, your Exchange Web Services certificate cannot be expired. If it is, any EWS call you make will return “succeeded” but will be blank – missing data. Digging into the logs you might find an error: “ErrorInvalidClientAccessTokenRequest” The Microsoft Exchange Server Auth Certificate that is used for OAuth needs to be updated. To do this you have to be logged into the Exchange server as administrator:
- Run this cmdlet to identify the thumbprint of the certificate being used for OAUTH:Get-AuthConfig | FT currentcertificate*
- Run this cmdlet to identify the thumbprint of certificates used for other Exchange servers (IIS, SMTP, etc.):Get-ExchangeCertificate | Fl *thumb*
- Run these cmdlets to configure Exchange to use the valid certificate (copy/paste the thumbprint from the Get-ExchangeCertificate output):$today = Get-Date
  Set-AuthConfig -NewCertificateThumbprint newthumbprint -NewCertificateEffectiveDate $today -Force
- Run this cmdlet to make sure the changes are published to the environmentSet-AuthConfig -PublishCertificate
- Run this cmdlet to verify the certificate thumbprintGet-AuthConfig | FT currentcertificate*,previouscert*
Now you can deploy your solution to IIS, update your manifest to point to the IIS server (do not forget the HTTPS) and then install it on the Exchange Server Control Panel (ECP) under the Organization / Add-ins option as a mandatory add-in.
Finally, and this last bit is important: IF USING OUTLOOK 2013 or OUTLOOK 2016, YOU MUST BE LOGGED INTO WINDOWS ON THE SAME DOMAIN AS YOUR EMAIL. I know, I know… for some folks this sucks. I have reported this to our product team and they are looking into it. If you are not logged into the same domain controller as your email address is registered, you will not see the advertised add-in. It will load in Outlook Web Access (OWA), but will not appear in Outlook 2013/2016. The exact cause of this problem is unknown, but hopefully it will be addressed in a future version of the product (Exchange or Outlook or both).

Setting up for 100% on-premises is difficult, but it CAN be done. There are a lot of steps, but if you follow the above prescription, you should get it to work. In time, I hope to see this process get easier. But in an online world where Microsoft Office 365 and Azure are main focus, “old fashioned” on-premises solutions are going to require a little more elbow grease.

NOTE: This entry was contributed to by Arthel Bibbens (MSFT) / Exchange PFE. You can follow his posts on this topic here:

Administering Office Add-ins within Exchange 2013 and Exchange 2016

blogs.technet.microsoft.com

I recently worked with a developer to deploy an Office add-in within an Exchange 2013 on-premises environment. This project highlighted a capability of Exchange and Outlook that is a huge shift in the way mail add-ins are developed, deployed, and maintained. Let’s take a look at the key components of Exchange 2013 that support this…

Simple Web Service Controller for Office Add-in

April 28, 2016July 3, 2017 David E. CraigLeave a comment

I have found Web Service Controllers in Office Add-ins to be quite useful. There are a number of reasons you might want to keep functions of your add-in on a server, including obfuscation, complex calculation, data intense, service mashups, and much more. However, every time you want to use your Web Ser4vice from the JavaScript interface, there is a lot of code associated with the AJAX call you have to make. This begs for simplicity and that is what I have done. In my App.js project, I added a simple makeAjaxCall function that takes the command you want to invoke, the parameters you want to pass it (as an array) a callback when the call is complete and a callback if there was an error. Here is the core code for the makeAjaxCall() method:

[code lang=”javascript” collapse=”true” title=”click to expand if the docs.com embedding below is not visible.”]
// Helper function to call the web service controller
app.makeAjaxCall = function (command, params, callback, error) {
var dataToPassToService = {
Command: command,
Params: params
};
$.ajax({
url: ‘../../api/WebService’,
type: ‘POST’,
data: JSON.stringify(dataToPassToService),
contentType: ‘application/json;charset=utf-8’
}).done(function (data) {
callback(data);
}).fail(function (status) {
error(status);
})
};
[/code]

	// Helper function to call the web service controller
	app.makeAjaxCall = function (command, params, callback, error) {
	var dataToPassToService = {
	Command: command,
	Params: params
	};
	$.ajax({
	url: '../../api/WebService',
	type: 'POST',
	data: JSON.stringify(dataToPassToService),
	contentType: 'application/json;charset=utf-8'
	}).done(function (data) {
	callback(data);
	}).fail(function (status) {
	error(status);
	})
	};

view raw

makeAjaxCall.js

hosted with ❤ by GitHub

Here is a sample of my web service controller:

[code lang=”javascript” collapse=”true” title=”click to expand if the docs.com embedding below is not visible.”]
/// <summary>
/// CORE SERVICE FUNCTION
/// This function will take a web request which will contain the command the caller wants
/// to initate and then the paramaters needed for that call. We enter a SELECT statement
/// below to determine the command given and we then call the proper helper function
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
[HttpPost()]
public WebServiceResponse WebService(WebServiceRequest request)
{
WebServiceResponse response = null;
switch(request.Command)
{
case "DoFunctionA":
return functionA(request.Params);
case "DoFunctionB":
return functionB(request.Params);
case "DoFunctionC":
return functionC(request.Params);
case "DoFunctionD":
return functionD(request.Params);
}

response = new WebServiceResponse();
response.Message = "Unknown command";
return response;
}
[/code]

	/// <summary>
	/// CORE SERVICE FUNCTION
	/// This function will take a web request which will contain the command the caller wants
	/// to initate and then the paramaters needed for that call. We enter a SELECT statement
	/// below to determine the command given and we then call the proper helper function
	/// </summary>
	/// <param name="request"></param>
	/// <returns></returns>
	[HttpPost()]
	public WebServiceResponse WebService(WebServiceRequest request)
	{
	WebServiceResponse response = null;
	switch(request.Command)
	{
	case "DoFunctionA":
	return functionA(request.Params);
	case "DoFunctionB":
	return functionB(request.Params);
	case "DoFunctionC":
	return functionC(request.Params);
	case "DoFunctionD":
	return functionD(request.Params);
	}

	response = new WebServiceResponse();
	response.Message = "Unknown command";
	return response;
	}

view raw

OfficeControllerWebService.cs

hosted with ❤ by GitHub

And here is how you call it:

[code language=”javascript”]
app.makeAjaxCall("DoFunctionA", ["Value1", "Value2"], function (data) {
var result = $.parseJSON(data.Message);
// do something with the retuirned result here…
});
[/code]

For more information on how to create a Web Service controller, there is a great blog post from a colleague of mine, Michael Zlatkovsky on how to do this:

Create a web service for an app for Office using the ASP.NET Web API