Pieter van der Westhuizen

Office UX guidelines for Excel 2013 content apps and Word 2013 task pane apps

Ok, it’s time to get excited about the new Office, new Windows and the new Microsoft applications! If you’ve been following the news you would’ve noticed that for the first time in a quarter of a century Microsoft has a new logo. This is just part of a Microsoft’s overhaul of their corporate image. You can read more about the Microsoft brand family in this article.

This also means that we as Office developers need to get our apps ready to fit into the new look and feel of Office 2013 and Windows 8. Luckily Microsoft does give use a number of UX guidelines on how to deliver a good experience for our users.

My original plan for this article was to give you an overview of the new Office 2013 UX guidelines, but I’ve since decided to do a more hands on guide with some code thrown in for good measure. So strap on your web development helmets folks, it’s going to be a wild ride!

Content App for Excel 2013

The first example of the Office 2013 UX guidelines Microsoft gives us is a Content App for Excel that enables the user to visualize their selected data in the form of a chart as per the following image:

Content App for Excel that enables the user to visualize their selected data in the form of a chart

I’ve created a new Content App for Excel 2013 project using Visual Studio 2012. The wizard adds the necessary files to your project and the two files we’ll be working with are:

  • ExcelContentApp.html in the Pages folder and;
  • ExcelContentApp.js in the Scripts folder.

The structure of the Excel Content App created by the Visual Studio 2012 wizard

Herewith the full code listing for each file:

ExcelContentApp.html

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
    <title>ExcelContentApp</title>
 
    <link rel="stylesheet" type="text/css" href="../Content/Office.css" />
    <link rel="stylesheet" type="text/css" href="../Content/App.css" />
 
    <script src="../Scripts/jquery-1.6.2.js"></script>
    <script src="../Scripts/Office/MicrosoftAjax.js"></script>
    <script src="../Scripts/Office/Office.js"></script>
    <script src="../Scripts/jscharts.js"></script>
 
    <!-- Add your JavaScript to the following file -->
    <script src="../Scripts/ExcelContentApp.js"></script>
</head>
<body>
    <div id="Header" class="heading">
        <img src="../Images/banner.png" />
        <h2>Visualize data in your spreadsheet.</h2>
        <h2>Select values and click the button.</h2>
        <input disabled="disabled" type='button' value='Create Chart' 
			id="createChartButton" style="margin-right: 10px; 
			padding: 0px; width: 100px;" />
    </div>
    <br />
    <div id="Content">
        <div id="chart"></div>
    </div>
    <br />
    <div id="footer" style="visibility: hidden; padding-top: 250px;">
        <input type="button" id="backButton" value='Back' />
    </div>
    <div id="message"></div>
</body>
</html>

ExcelContentApp.js

// Add any initialization logic to this function.
Office.initialize = function (reason) {
	// Checks for the DOM to load.
	$(document).ready(function () {
		Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, function (eventArgs) {
			$('#createChartButton').removeAttr('disabled');
		});
 
		$("#createChartButton").click(function () {
			CreateChart();
		});
 
		$("#backButton").click(function () {
			$('#Header').show('slow');
			$('#footer').css('visibility', 'hidden');
			$('#chart').empty();
		});
	});
}
 
function CreateChart() {
	Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix, function (asyncResult) {
		if (asyncResult.status != Office.AsyncResultStatus.Failed) {
			$('#Header').hide('slow', function () {
				var myChart = new JSChart('chart', 'bar');
				myChart.setDataArray(asyncResult.value);
				myChart.draw();
				$('#footer').css('visibility', 'visible');
			});
		}
	});
}

The content app will remain inactive until the user selects cells in Excel 2013. In my example the “Create Chart” button is disabled until the user selects values in the Excel sheet.

Create Chart button is disabled until the user selects values in the Excel sheet

As soon as the user selects cells in Excel the Create Chart button becomes enabled and the user can then click on it. This is accomplished by creating the button in Html and setting its disabled attribute’s value to disabled e.g.

<input disabled="disabled" type='button' value='Create Chart' id="createChartButton" 
	style="margin-right: 10px; padding: 0px; width: 100px;" />

Once cells are selected in the worksheet and to enable the button, use the Document objects’ SelectionChanged event:

Office.context.document.addHandlerAsync(Office.EventType.DocumentSelectionChanged, function (eventArgs) {
	$('#createChartButton').removeAttr('disabled');
});

Create Chart button is enabled

All that the above code does, is it removes the disabled attribute from the “Create Chart” button using jQuery. Next, we are ready to respond to when the user clicks on the “Create Chart” button, do this by creating an event handler for the .click() event:

$("#createChartButton").click(function () {
	CreateChart();
});

The .click event will call a function to create the chart to visualize the data. I found a really great JavaScript library that is ideal for generating charts from the multi-dimensional arrays returned by the Office JavaScript API, called JS Charts.

The code for the CreateChart function will look like this:

function CreateChart() {
	Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix, function (asyncResult) {
		if (asyncResult.status != Office.AsyncResultStatus.Failed) {
			$('#Header').hide('slow', function () {
				var myChart = new JSChart('chart', 'bar');
				myChart.setDataArray(asyncResult.value);
				myChart.draw();
				$('#footer').css('visibility', 'visible');
			});
		}
	});
}

In essence this code, (1) hides the header containing our branding; (2) creates a new JSChart object and tells it to use the array returned by the getSelectedDataAsync event as the data source for the chart; and (3) shows an html div called footer. It is very important to note that the CoercienType must be set to Matrix when selecting more than one cell in Excel.

The resulting chart in Excel 2013 looks like this:

The resulting chart in Excel 2013

Task Pane App for Word 2013

Next, we’ll take a look at the Office 2013 UX guidelines for Word task pane apps. We are going to create an app that can be used to search for a postal code and then inserting the state and city information into the active Word 2013 document when the user clicks on the item in the task pane. The Microsoft UX guidelines suggested layout for such an app looks like the following:

Microsoft Office 2013 UX guidelines for Word 2013 Task Pane Apps

For this example we need to create a Task Pane App in Visual Studio 2012.

Creating a Task Pane App in Visual Studio 2012

The Visuals Studio “Create App for Office” wizard will automatically create the folder structure for the app as well as add all the necessary files to the project. As with the Excel content app, we’ll focus on two files:

  • WordTaskPaneApp.html in the Pages folder; and
  • WordTaskPaneApp.js in the Scripts folder.

The folder structure of the Task Pane App created by the Create App for Office wizard

The full code listing for each file would be as follows:

WordTaskPaneApp.html

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8" />
	<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
	<title>WordTaskPaneApp</title>
 
	<link rel="stylesheet" type="text/css" href="../Content/Office.css" />
	<link rel="stylesheet" type="text/css" href="../Content/App.css" />
 
	<script src="../Scripts/jquery-1.6.2.js"></script>
	<script src="../Scripts/Office/MicrosoftAjax.js"></script>
	<script src="../Scripts/Office/Office.js"></script>
	<script src="../Scripts/jeoquery.js"></script>
 
	<!-- Add your JavaScript to the following file -->
	<script src="../Scripts/WordTaskPaneApp.js"></script>
</head>
<body>
	<div id="header">
		<img src="../Images/banner.png" />
	</div>
	<div id="Content">
		<div id="searchwrapper">
			<h2>Search</h2>
			<input id="searchValue" type="text" class="searchbox" 
				name="s" value="" />
			<input id="searchButton" type="image" src="../Images/Find.png" 
				class="searchbox_submit" value="" />
		</div>
		<br />
		<div id="featured">
			<h2>Featured</h2>
			<p class="tiles">
				<img src="../Images/tile.png" />
				<a class="pcode" href="#">Addison
					<br />
					Dallas, Texas
					<br />
					75001</a>
			</p>            
			<p class="tiles">
				<img src="../Images/tile.png" />
				<a class="pcode" href="#">Bronx
					<br>
					Bronx, New York
					<br>
					10453</a>
			</p>
			<p class="tiles">
				<img src="../Images/tile.png" />
				<a class="pcode" href="#">Henderson
					<br />
					Clark, Nevada
					<br />
					89044</a>
			</p>
		</div>
		<div id="searchResults" style="visibility: hidden;">
			<h2>Search Results</h2>
			<div id="results"></div>
		</div>
 
	</div>
</body>
</html>

WordTaskPaneApp.js

// Add any initialization logic to this function.
Office.initialize = function (reason) {
 
	// Checks for the DOM to load.
	$(document).ready(function () {
		$('.pcode').click(function () {
			setData(this.innerText);
		});
 
		$('#searchButton').click(function () {
			$('#featured').hide('slow');
			if ($('#searchButton').hasClass('searchbox_submit')) {
				$('#searchResults').css('visibility', 'visible');
				jeoquery.userName = 'demo';
				jeoquery.postalCodeLookup(geoCallback, $('#searchValue').val(), 'US');
 
				$('#searchButton').attr('src', '../Images/Cancel.png');
				$('#searchButton').removeClass('searchbox_submit');
				$('#searchButton').addClass('searchbox_cancel');
			} else {
				$('#featured').show('slow');
				$('#results').empty();
				$('#searchResults').css('visibility', 'hidden');
				$('#searchValue').val('');
				$('#searchButton').attr('src', '../Images/Find.png');
				$('#searchButton').addClass('searchbox_submit');
				$('#searchButton').removeClass('searchbox_cancel');
			}
		});
	});
}
 
// Writes data to current selection.
function setData(dataToInsert) {
	Office.context.document.setSelectedDataAsync(dataToInsert);
}
 
function geoCallback(data) {
	$('#results').empty();
	$.each(data.postalcodes, function (index, data) {        
		$('#results').append('<p class="tiles"><img src="../Images/tile.png" /><a onclick="setData(this.innerText)" class="pcode" href="#">' + data.placeName + '<br/> ' + data.adminName2 + ', ' + data.adminName1 + '<br/>' + data.postalcode + '</a></p>');
	});
}

This Word 2013 app will allow the user to enter a postal code and then return the US city and state information for that postal code. I’ll be using the GeoNames WebServices and the jeoQuery jQuery wrapper to return the postal code information from the GeoNames database.

This Word 2013 app will return the postal code information from the GeoNames database

According to the Microsoft Office 2013 UX guidelines a task pane app should display a search box with a search icon and a list of featured items. When the user enters a value to search for and click the search icon, the icon should change to a cancel icon and the search results should be displayed.

The app displays a search box with a search icon and a list of featured items as per Microsoft UX guidelines

This is accomplished by adding a click event to the search image in the WordTaskPaneApp.js file. The event should be declared inside the jQuery .ready function. This event (1) hides the list of featured items; (2) calls the GeoNames, using jeoQuery, Postal Code lookup web service; and (3) returns a list of postal codes to a call back function called geoCallback.

The geoCallback function simply loops through the JSON array and appends it to a div in our html.

function geoCallback(data) {
	$('#results').empty();
	$.each(data.postalcodes, function (index, data) {        
		$('#results').append('<p class="tiles"><img src="../Images/tile.png" /><a onclick="setData(this.innerText)" class="pcode" href="#">' + data.placeName + '<br/> ' + data.adminName2 + ', ' + data.adminName1 + '<br/>' + data.postalcode + '</a></p>');
	});
}

Next, when the user clicks on an item in either the featured or search result lists, the information gets added to the active document.

The postal code information is added to the Word 2013 document

This is accomplished by adding another click event to all elements with a class name of pcode:

$('.pcode').click(function () {
	setData(this.innerText);
});

The click event in turn calls the setData function which adds the address information to the active document.

That sums up a quick overview of some Office 2013 UX guidelines for the new Apps for Office and gives you some real world examples of what sort of apps can be created for Office using the new web extensibility platform.

You’ll find all the examples mentioned in this article as a download in this article, and as always you are welcome to ask us any question or give us feedback by leaving a comment or engaging with us on twitter.

Thank you for reading. Until next time, keep coding!

Available downloads:

C# sample app for Excel 2013 and Word 2013

You may also be interested in:

3 Comments

  • https://secure.gravatar.com/avatar/42d92c0928b28250ded5b494e6cb6718?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Ravi Ayitha says:

    Hi

    Thanks for providing very informative article.

    Please do needful if any solution feasible for following scenario:

    Using Apps for Office content pane, on button click is it feasible to create bar/pie chart without using plugins:
    Jplot
    Jscharts
    Visualize

    mean to say creating a chart using HTML and javascript API for Apps for office.

    Thanks,
    Ravi

  • https://secure.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi Ravi,

    Thank you for reading and your comment.

    Ok, so how I understand your question is, that you would like to be able to create a chart in MS Excel using the new Apps for Office JavaScript API and HTML without the need for any JavaScript libraries to make the job easier?
    Yes, you can do it without plugins, you would have to write your own JavaScript in order to show the charts using HTML and CSS, you would have to manually read the data in the mult-dimensional array and generate your chart from that. But I would highly recommend using an existing jQuery or JavaScript library to make your job easier.

    If you want to create a built-in Excel chart using the new Apps for Office JavaScript API, I’m afraid your out of luck, as it’s not possible.

    Hope this answered your question.

  • https://secure.gravatar.com/avatar/42d92c0928b28250ded5b494e6cb6718?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Ravi Ayitha says:

    Hi Pieter van der Westhuizen

    Thanks for the immediate response. This helps.

    Thanks,
    Ravi Ayitha

Post a comment

Have any questions? Ask us right now!