exceljs

Dialogs in OfficeJS

June 14, 2017June 13, 2017 David E. Craig13 Comments

I have been working on a number of projects for my customers and recently, dialogs have taken front and center stage. The Office.context.ui Dialogs are powerful, albeit a tad confusing and the documentation suffers from a few easily missed points. Here is the documentation:

But in this post, I hope to explain everything I have learned. To start off with here is the code to issue the dialog:

[code lang=”javascript” collapse=”true” title=”click to expand if the embedding below is not visible.”]
Office.context.ui.displayDialogAsync(‘https://localhost:3000/function-file/dialog.html’,
{ height: 20, width: 30, displayInIframe: true },
function (asyncResult) {
dialog = asyncResult.value;
// callbacks from the parent
dialog.addEventHandler(Office.EventType.DialogEventReceived, processMessage);
dialog.addEventHandler(Office.EventType.DialogMessageReceived, processMessage);
});
[/code]

	Office.context.ui.displayDialogAsync('https://localhost:3000/function-file/dialog.html',
	{ height: 20, width: 30, displayInIframe: true },
	function (asyncResult) {
	dialog = asyncResult.value;
	// callbacks from the parent
	dialog.addEventHandler(Office.EventType.DialogEventReceived, processMessage);
	dialog.addEventHandler(Office.EventType.DialogMessageReceived, processMessage);
	});

view raw dialog.js hosted with ❤ by GitHub

What this dialog does is opens as a modal form in a frame over the application (in Office Web Apps). It looks like this:

As you can see the dialog is modal. But what is really important are the two event handlers you need to register to be able to get back to your code:

[code lang=”javascript”]
dialog.addEventHandler(Office.EventType.DialogEventReceived, processMessage);
dialog.addEventHandler(Office.EventType.DialogMessageReceived, processMessage);
[/code]

The first one is an event receiver and really this is the event handler for errors, such as being unable to open the dialog or, most importantly, the user closed the dialog by clicking the (X) in the upper right of the dialog. There are a series errors you can catch, but specifically, the dialog cancel is this:

12006

The dialog box was closed, usually because the user chooses the X button.

The second one is a handler for messages coming from the dialog. These messages can be anything, but is usually a string or a JSON string. You can send a message from the dialog like this:

[code lang=”javascript”]
Office.context.ui.messageParent(‘{message}’);
[/code]

When the dialog issues a message using the code above, the function defined in the event handler is called. For example, if the user clicks an OK button or Submit button, you can pass the stringified values from the form back to the callback function. From there the dialog actually remains open until the caller issues a close, like this:

[code lang=”javascript”]
// close the dialog
dialog.close();
[/code]

In the example above where I make the displayDialogAsync call, you will see I defined the SAME callback function for both dialog events. I did this because the results can be parsed by the same function. Here is what my function look like:

[code lang=”javascript” collapse=”true” title=”click to expand if the embedding below is not visible.”]
function processMessage(arg) {
// close the dialog
dialog.close();
// procress the result
if(arg.error == 12006) {
// user clicked the (X) on the dialog
sendEvent.completed({ allowEvent: false });
} else {
if(arg.message=="Yes") {
// user clicked yes
sendEvent.completed({ allowEvent: true });
} else {
// user clicked no
sendEvent.completed({ allowEvent: false });
}
}
}
[/code]

	function processMessage(arg) {
	// close the dialog
	dialog.close();
	// procress the result
	if(arg.error == 12006) {
	// user clicked the (X) on the dialog
	sendEvent.completed({ allowEvent: false });
	} else {
	if(arg.message=="Yes") {
	// user clicked yes
	sendEvent.completed({ allowEvent: true });
	} else {
	// user clicked no
	sendEvent.completed({ allowEvent: false });
	}
	}
	}

view raw dialogCallback.js hosted with ❤ by GitHub

My previous blog post references all the code for the dialog:

Outlook OnSend and Dialog Sample

Yo Office

June 13, 2017June 14, 2017 David E. Craig5 Comments

Ok, odd title, I know. For some developers “in the know,” they will understand what I am eluding to. So what does it mean? It is short for Yeoman. In their own words, here is what it does:

Yeoman helps you to kickstart new projects, prescribing best practices and tools to help you stay productive. To do so, we provide a generator ecosystem. A generator is basically a plugin that can be run with the `yo` command to scaffold complete projects or useful parts.

So, by “yo office”, I am telling you that Office Web Add-in templates can be delivered through this cross-platform command line tool. This means you do NOT need Visual Studio (or even Windows) to create a new Office Web Add-in project package. More importantly, these packages are setup through NodeJs Package Manager and are configured to use Node.js web server (which comes on MANY different platforms).

Now, if you are like me (a 20+ year VS Office Developer veteran), when I first heard this, it all sounds just like this:

Γράφω στα ελληνικά και αν καταλαβαίνετε αυτό, καλό για σας

FYI: That is Greek.

So, I have a choice here and that is to make this a long blog post, or a short blog post and I dislike making long posts as much as you do reading them, so… In this case, here is the executive summary: The Microsoft Office Developer team is trying to make Office an accessible development platform no matter where you come from (experience wise) or what platform you use.

Now, for “super hip” developers living on the cusp of command-line based free developer tooling, put this in your MacBook:

yo office

For the rest of you…

To get this to work, you need to install Node JS from here. And next you need to install the prerequisites as per these steps. Once installed, you will open the Node JS command prompt (in Windows, from the Start menu, type NodeJS and it should appear as a choice) and perform these steps:

Change directory (using cd) to the folder where you want the project to live.
Type “yo office” and then answer the questions.
In my case, I developed an Outlook add-in, with a manifest, using JavaScript, called “Outlook-Sample-1” and I chose JQuery as the framework. Here is the output:

When it is done, it will create a folder called Outlook-Sample-1 and place a set of files in it that you can edit and the NodeJS configuration to run it from a standalone web server locally. I used Visual Studio Code (or VS Code, as it is known, which is a new multi-platform code editor from Microsoft that runs on Mac, Windows, and Linux), to edit the add-in.

NOTE: If you have configured everything in Visual Studio Code correctly, you can not only get NodeJS to interact from the “Terminal Window” you can also hook to GitHub to push/pull your projects. It is a whole other topic for a whole other blog post, but VS Code is a bare essentials utilitarian cross-platform code editing development tool.

Now, which files you need to edit and such are beyond the scope here, but if you have done some development in this area before, you can probably quickly figure it out. I might post more on this in the future. Stay tuned!

Ok, ok… I know, I know… I need to stop here and address the big huge hulking elephant in the room. YES, these are command line tools. Yes, DOS is still dead. No, it is. Really. Repeat after me, DOS “IS” DEAD. Now, just accept that this is the way it is, because this is the way it is. I know, it seems so… 1994… Neo called from the Matrix to assure you it is, indeed, 2017. For my fellow Visual Studio, 20+ year developer gurus: “deep breaths.” Now, back to our regularly scheduled broadcast..

Here is a view of my project from VS Code, with the Terminal Window open…

Once I was done, I opened a NodeJS command prompt in the Terminal window and launched my project using the following command:

npm start

Once you run this command, you will effectively start NodeJS web server at http://localhost:3000 which will be hosting your WebAdd-in. But you still need to side load your manifest. To do this, you will need to follow these steps (for Windows), here for Mac, here for Outlook. This gets all kinds of technical and deep into other areas that are potentially future blog posts, but the point is that you can then debug your add-in locally no matter which platform you are on.

Ok, Ok… back to the elephant…

So, is this BETTER than Visual Studio Enterprise and the JavaScript debugger there and code assistance I get?

Well, this is more nuanced… I lean towards “no” as most of this stuff is too lightweight, especially given my experience. Especially without the debugging. But, I do see some advantages (besides the command line tools) that have its advantages for use on Windows:

It is available across multiple platforms – including Windows. VS Code, Node JS, and “yo office” are everywhere, anywhere, over there, under that, etc..
Out-of-the-box support for TypeScript. Which I have not blogged on a lot, but Michael Zlatkovsky has covered well in his book. TypeScript makes JSDoc seem like child’s play.
Out-of-the-box support for the Node Package Manager ecosystem. This is a huge repository of reusable code.
VS Code is a light weight editor that hooks into NPM, Node and Git really well. And while it does not debug, recent builds of Office have added the ability to hook to a debugger right from your taskpane. Which is only good if you have a taskpane, but it is a start.
Node provides auto-refresh (browser-sync), auto-compilation, and other goodness.

Again, this might look like a lot of Greek, but if you find your shop turning Greek, you can at least know some starter phrases to begin acquiring the knowledge you need to speak it.

So, why is any of this important to me? Especially if you are telling me:

“I use Visual Studio Enterprise (or Pro) and I am happy with it, why would you introduce me to this Greek language lesson.”

The answer comes in three points you can take away:

Office development is really multi-platform capable now. This correlates to the fact the Office Applications are also multi-platform. Write your add-in once, and it will run (or the future plan is, it will run) on every platform you can think of: Windows, Mac, iOS, Android, Linux (or essentially an browser or any platform with a browser that has HTML5 support).
You know more about the vision, commitments and goals of the core Microsoft Office teams. If you get nothing out of this other than Microsoft is committed to true multi-platform capabilities with Office Web Add-ins, you have come away with a major point. But there is also the knowledge on how Office development can be done no matter the developer background. And hey. You 20+ year VS Office Developer veterans: take this to heart and read into it, because these are the tea leaves; then look over to your mentee that is likely your kids age and <sigh>.
You can move to another platform and still take what you know with you. This means that “cool” MacBook you bought several years back and gave to your kiddo when they went off to college can actually do something more than read email and browse web pages. Uh, I still strongly suggest you stick to Web Add-in development on Windows and Visual Studio Enterprise if you are already there… just saying…

So, with this, I hope I have sufficiently given you a vision of things to come, and maybe even given a few of you some new things to research and delve into. If you want more, the entire how-to with Yeoman, including a cool video, is on the following page:

https://dev.office.com/blogs/creating-office-add-ins-with-any-editor-introducing-yo-office

OfficeJS: Create a (VERY) Basic Add-in for Excel

March 3, 2017July 3, 2017 David E. Craig3 Comments

Recently, I was asked what is needed in order to create the most basic add-in you can. The least amount of files, work and effort. Technically, you only need two files:

An HTML file with all your JavaScript inline and all the Office and JQuery libraries being pulled from a CDN.
An XML Manifest file that defines the add-in.

You publish the HTML file to a web server, get the address to where it is published, update the manifest with that information and then install the XML file as an add-in. The XML file will define the add-in name/description and points to the HTML file for the code. When you launch the add-in, the task pane will open and the HTML page will be loaded in it. From there your JavaScript will execute and your add-in will come alive. It is THAT easy. 2 files.

To demonstrate this, I created a simple Excel add-in. Here is the code for the bare minimum manifest XML:

[code lang=”xml” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
<?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>5226365f-62d0-435f-a4af-cc6a7bd753b2</Id>
<Version>1.0.0.1</Version>
<ProviderName>TheOfficeContext.com</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="BasicAddin" />
<Description DefaultValue="This is a basic addin."/>
<IconUrl DefaultValue="" />
<Hosts>
<Host Name="Workbook" />
</Hosts>
<DefaultSettings>
<SourceLocation DefaultValue="https://basicaddin.azurewebsites.net/home.html" />
</DefaultSettings>
<Permissions>ReadWriteDocument</Permissions>
</OfficeApp>
[/code]

	<?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>5226365f-62d0-435f-a4af-cc6a7bd753b2</Id>
	<Version>1.0.0.1</Version>
	<ProviderName>TheOfficeContext.com</ProviderName>
	<DefaultLocale>en-US</DefaultLocale>
	<DisplayName DefaultValue="BasicAddin" />
	<Description DefaultValue="This is a basic addin."/>
	<IconUrl DefaultValue="" />
	<Hosts>
	<Host Name="Workbook" />
	</Hosts>
	<DefaultSettings>
	<SourceLocation DefaultValue="https://basicaddin.azurewebsites.net/home.html" />
	</DefaultSettings>
	<Permissions>ReadWriteDocument</Permissions>
	</OfficeApp>

view raw

BasicAddin.xml

hosted with ❤ by GitHub

Here is the HTML file that is quite basic, I am not even using the Microsoft Fabric for the controls. This is a simple HTML page, period:

[code lang=”xml” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<title>Excel Basic Add-In</title>
<!–using JQuery CDN so we do not need to include–>
<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
    <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
<script type="text/javascript">
        (function () {
            "use strict";
            // The initialize function must be run each time a new page is loaded.
            Office.initialize = function (reason) {
                $(document).ready(function () {
                    // Add a click event handler for the button.
                    $(‘#simple-button’).click(function () {
                        Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
                        function (result) {
                            if (result.status === Office.AsyncResultStatus.Succeeded) {
                                $("#banner-text").text(‘The selected text is: "’ + result.value + ‘"’);
                                $("#banner").show();
                            } else {
                                $("#banner-text").text(‘Error: ‘ + result.error.message);
                                $("#banner").show();
                            }
                        });
                    });
                    $("#banner-close").click(function () { $("#banner").hide(); });
                    $("#banner").hide();
                });
            }
        })();
    </script>
</head>
<body>
<div id="content-main">
<div class="padding">
<h1>Basic Addin</h1>
<div>Select a cell with text and then…</div>
<button id="simple-button">Click Here!</button></div>
</div>
<div id="banner" style="position:absolute;bottom: 0;">
<div id="banner-text"></div>
<button id="banner-close"> <i>X</i> </button></div>
</body>
</html>
[/code]

	<!DOCTYPE html>
	<html>
	<head>
	<meta charset="UTF-8" />
	<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
	<title>Excel Basic Add-In</title>
	<!–using JQuery CDN so we do not need to include–>
	<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
	<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
	<script type="text/javascript">
	(function () {
	"use strict";

	// The initialize function must be run each time a new page is loaded.
	Office.initialize = function (reason) {
	$(document).ready(function () {
	// Add a click event handler for the button.
	$('#simple-button').click(function () {
	Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
	function (result) {
	if (result.status === Office.AsyncResultStatus.Succeeded) {
	$("#banner-text").text('The selected text is: "' + result.value + '"');
	$("#banner").show();
	} else {
	$("#banner-text").text('Error: ' + result.error.message);
	$("#banner").show();
	}
	});
	});
	$("#banner-close").click(function () { $("#banner").hide(); });
	$("#banner").hide();
	});
	}
	})();
	</script>
	</head>
	<body>
	<div id="content-main">
	<div class="padding">
	<h1>Basic Addin</h1>
	<div>Select a cell with text and then…</div>
	<button id="simple-button">Click Here!</button>
	</div>
	</div>
	<div id="banner" style="position:absolute;bottom: 0;">
	<div id="banner-text"></div>
	<button id="banner-close"> <i>X</i> </button>
	</div>
	</body>
	</html>

view raw

Home.html

hosted with ❤ by GitHub

What this add-in does is very simple. It opens the task pane with a single button. Enter some text in a cell in Excel, select that cell, and then click the button. It gets the text and displays it in a popup at the bottom of the pane. I have published this add-in manifest here:

https://basicaddin.azurewebsites.net/basicaddin.xml

You can run it by using these steps:

Open Excel Online. I first log into https://outlook.office365.com. Then I click on the menu button in the upper left and select Excel.
I select to create a “New blank workbook.”
On the Insert tab, I click Add-ins.
In the upper right, I select “Upload My Add-in”
I click Browse and in the Filename box, I put https://basicaddin.azurewebsites.net/basicaddin.xml and then click Open. This will download the add-in to the cache on your system and you will get a file name like “basicaddin[1].xml.”
Click Upload.
The add-in will load from my Azure site and you will see the button demonstrated above.

Now, to go ahead and answer a question I know are coming. How did I upload this Azure without Visual Studio publishing tools:

I went to my Azure Protal (portal.azure.com).
I clicked the +New button on the left side.
I selected Web + Mobile, then Web App
I filled in the required information: Name and resource group and clicked Create.
After it was done, I followed these steps to get to the site via FTP: https://blogs.msdn.microsoft.com/kaushal/2014/08/01/microsoft-azure-web-site-connect-to-your-site-via-ftp-and-uploaddownload-files/
I uploaded ONLY the HTML file.
I got the path to the location from the Azure portal:
Next, I updated the XML manifest to point to https://basicaddin.azurewebsites.net/home.html
Then I used the steps above to load it into Excel Online.

This is fairly simple from a web developer perspective. Now, if you are not a web developer (but a traditional Office developer), you are probably still on the fence on how/why you would want to go through all of this. Again, the primary thing I am demonstrating here is how you can build an add-in with as few files as possible. But, I want to reiterate how these new add-ins are truly cross-platform. Follow these steps on your iPad and you will know what I mean. It is incredible and this work really is not that difficult to perform. It might just be a tad time consuming in the beginning as you are getting used to the new interfaces.

If you have questions, please feel free to reach out to me.

Testing a Web Add-in with Multiple Manifest

July 13, 2016December 19, 2016 David E. Craig5 Comments

First, let’s discuss the architecture and from an Outlook/Exchange perspective:

First, you load your manifest on the Exchange server. The manifest is simply an XML file that contains pointers to your web site (on the IIS server).
When you load Outlook Web Access and click the add-ins button, the Add-ins pane will appear and each application manifest you have loaded will appear in the list.
When you click on one of the add-is, the task pane will load (in Orange) and your site (located on the IIS Server) will populate in the pane.

The problem arises when you have but ONE Exchange server or ONE developer account for development and test. Developers want to be able to debug the code they are working with which typically loads from the local instance of IIS Express (localhost). But testers need to be able to work with the latest release build to test.

So, how do you do this?

The key is in the Manifest file. If you open the Manifest file, you will see the <Id> field. This is the most important piece, but there are other areas you should/need to update as well. What you will essentially have is two copies of your manifest file:

The first file will use the default ID provided in the project, it should have a name like Developer Release and it will point to your localhost (this is default with the setup of a new Web Add-in).

[code language=”xml”]
<?xml version="1.0" encoding="UTF-8"?>
…
<Id>aaaaaaaa-3c56-4091-951e-18fda9e471d4</Id>
<Version>1.0.0.2</Version>
<ProviderName>David E. Craig</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="Demo Developer Release" />
…
<FormSettings>
<Form xsi:type="ItemEdit">
<DesktopSettings>
<SourceLocation DefaultValue="https://localhost:44303/AppCompose/Home/Home.html"/>
</DesktopSettings>
</Form>
</FormSettings>
…
</OfficeApp>
[/code]

diagram - developer - manifest

The second file will have a different unique ID, a different name (like Tester Release) and it will point to your IIS website.

[code language=”xml”]
<?xml version="1.0" encoding="UTF-8"?>
…
<Id>bbbbbbbb-3c56-4091-951e-18fda9e471d4</Id>
<Version>1.0.0.2</Version>
<ProviderName>David E. Craig</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="Demo Tester Release" />
…
<FormSettings>
<Form xsi:type="ItemEdit">
<DesktopSettings>
<SourceLocation DefaultValue="https://TestServer/AppCompose/Home/Home.html"/>
</DesktopSettings>
</Form>
</FormSettings>
…
</OfficeApp>
[/code]

diagram - tester - manifest

The real key is having different ID’s and publishing them separately. In Visual Studio the developers will have everything set as shown above (essentially leave everything as default). When they are ready to drop a build to the testers:

Right-click on the Website project and click Publish. Follow the steps to publish the site to the Testers server.
Right-click on the Manifest project and click Publish. Click the “Package the add-in” button.
In the resulting dialog, enter the URL to the Testers site, This will ONLY update the URL, but not change the ID.
Open the <manifest>.xml file in Notepad and then change the fields as shown above:
- Modify the ID
- Change the name so that you can identify which one is which
- Verify the URL is correct.

At this point you are ready to go. And you can create MULTIPLE versions of your manifest. If for example you need one for Testers, one for Developer and then another for Pilot and yet another for experimental testing (each pointing to different IIS instances, sites or even to the Cloud (Azure). You can create as many manifests as you need this way, have them all show up in the Add-ins task pane allowing the testers/users to select the one they wish to work with.

Useful Refresh Command

April 29, 2016December 19, 2016 David E. CraigLeave a comment

I recently watched a video by my colleague Michael Zlatkovsky in which he demonstrates the changes to the OfficeJS Libraries. And in this video he proposed a nifty little trick I have been using ever since to refresh the task pane app without having to stop and reload the solution (which saves minutes each time you need to stop debugging). What you do is place a refresh button at the bottom of your task pane HTML, like this:

[code language=”html”]
<id="refresh-button">Refresh</button>
[/code]

Then you wire it up like this:

[code language=”javascript”]
$(‘#refresh-button’).click(function () {
location.reload();
});
[/code]

Simple eh?! Thanks Michael for the nifty tip!

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

[UPDATE] In which Application is my Add-in running?

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

UPDATE: The code outlined in this post will no longer work with Office Online. See this post for more information: http://stackoverflow.com/a/32851938/678505. Specifically, this block:

Update Dec 5, 2016: We will soon be releasing an API to detect the platform info (partially in response to the fact that the _host_info URL paramater, which folks had unofficially relied on, needed to be recently removed for Office Online). We also have a temporary workaround in anticipation of the forthcoming official API. See “In Excel Online, OfficeJS API is not passing the host_Info_ parameter anymore to Excel Add-In” for information on the API and workaround.

Original post:

In case you missed the news, there was an update to the Office API’s. Version 1.2 was announced at //build and released 2 weeks ago. This API enhancement gets us closer to parity with the native object models used in VBA and VSTO. One interesting aspect of the new Office add-ins (formally known as Apps for Office) is how you can use the same codebase across all the applications. However, as the API becomes richer they are also becoming more product specific. Meaning, there is now the Excel.run() and Word.run() commands (PowerPoint is a little lagging in this area, but it is supposedly on the horizon). These allow you to do Excel Workbook (or Word document) context specific commands. And there is a lot of other goodies (like promises). But, I digress…

I was working on a proof of concept for my customer when I found that I needed to use the same codebase for a lot of the same work, but in some cases I needed to do something specific in Word and in another, something specific in Excel. I came up with the following function that I placed in my App.js file:

[code lang=”javascript” collapse=”true” title=”click to expand if the docs.com embedding below is not visible.”]
var current;
app.hostTypes = { Word :"Word", PowerPoint :"PowerPoint", Excel :"Excel"};
app.getHost = function () {
if (current == null) {
if (Office.context.requirements.isSetSupported(‘WordApi’)) {
current = app.hostTypes.Word;
} else if (Office.context.requirements.isSetSupported(‘ExcelApi’)) {
current = app.hostTypes.Excel;
} else {
var host = $.urlParam("_host_Info");
if (host.toLowerCase().indexOf("word",0) >= 0) {
current = app.hostTypes.Word;
} else if (host.toLowerCase().indexOf("excel",0) >= 0) {
current = app.hostTypes.Excel;
} else {
current = app.hostTypes.PowerPoint;
}
}
return current;
}
else {
return current;
}
};
[/code]

	var current;
	app.hostTypes = { Word :"Word", PowerPoint :"PowerPoint", Excel :"Excel"};
	app.getHost = function () {
	if (current == null) {
	if (Office.context.requirements.isSetSupported('WordApi')) {
	current = app.hostTypes.Word;
	} else if (Office.context.requirements.isSetSupported('ExcelApi')) {
	current = app.hostTypes.Excel;
	} else {
	var host = $.urlParam("_host_Info");
	if (host.toLowerCase().indexOf("word",0) >= 0) {
	current = app.hostTypes.Word;
	} else if (host.toLowerCase().indexOf("excel",0) >= 0) {
	current = app.hostTypes.Excel;
	} else {
	current = app.hostTypes.PowerPoint;
	}
	}
	return current;
	}
	else {
	return current;
	}
	};

view raw

getHost.js

hosted with ❤ by GitHub

To use this you would do this for Word:

[code language=”javascript”]
/// WORD
if (app.getHost() == app.hostTypes.Word) {
// Word specific code where
}
[/code]

Or, this for Excel:

[code language=”javascript”]
/// EXCEL
if (app.getHost() == app.hostTypes.Excel) {
// Excel specific code here
}
[/code]

Debugging with Office Online

February 17, 2016December 19, 2016 David E. CraigLeave a comment

Recently I began working on an Office Add-in (formally known as Apps for Office) that interfaces with Excel, PowerPoint and Word online. By default, when you create a new Office Add-in in Visual Studio 2015, it will default to using the installed Office Desktop Client. I have done a lot of work with Mail Apps, but this was the first time I really delved deeply into Office Add-ins with the express need to design against the online versions. After pulling my hair out looking for the settings in the debug tab of the Manifest Project settings, I found this in the Properties page of the Manifest Project file:

When you select the project at the top, you see these properties in the Properties window (F4).

Simply change these as such:

You then specify the URL to your Office 365 subscription. To get this, I logged into my Office 365 developer account from http://office365.com selected my OneDrive folder from the menu and the copied the full path from the address bar and placed it in the field. I got a prompt to log in and then all was good.

The next problem I had was with side loading the application. When I tried to Debug, I got this error in Visual Studio:

Error occurred in deployment step ‘Install app for SharePoint’

According to the documentation and everything I can find, you should not get this error if you are using an Office 365 Developer account. I am, and I am still getting this error. So, I had to go into my account and enable Side loading. I searched and searcha nd then found this blog by Tobias Lekman: https://blog.lekman.com/2012/11/sharepoint-2013-sideloading-of-apps-is.html. He gives two simple enough sounding steps:

Download and install the SharePoint Online Management Shell for PowerShell
Download the script Sideload.ps1 and execute it within the SharePoint Online Management Shell.

Sounds easy. So, I downloaded and installed this: https://www.microsoft.com/en-us/download/details.aspx?id=30359.

Then, I downloaded script and placed it right in my PowerShell folder: http://lekman.codeplex.com/releases/view/98505.

Now, I am the first to admit, I am a developer and not a PowerShell scripter. So I have next to no experience with PowerShell. When I ran the script I got an error:

Essentially there is an execution policy preventing my PowerShell script from running. So, I went to the link provided: https://technet.microsoft.com/library/hh847748.aspx. From there I found I needed to run this command:

After, I did that I was able to run the Sideload.ps file, it asked for my url, username and password and then setup my side to allow the side load for testing.

When I returned to Visual Studio and clicked Run, it ran and installed my app.