Apps for Office In Excel

I want to be clear that when I say apps for Office in Excel, what I am talking about are the apps that you insert into your worksheet from the Excel ribbon.

Microsoft have stated that they are moving away from the term Apps for Office though and will be using Office add-ins instead.

I’m not sure this makes things any less confusing though, as to me an add-in is something you write in VBA and then install into Excel in a completely different way.

What is an App?

Essentially it is a web page that you can embed into your worksheet. It can be as simple as a few lines of HTML or as complicated as your imagination.

You can embed whole websites, write complicated web based applications and make use of things like JavaScript, PHP and any other technology and programming language a web page can contain.

Requirements to Install and Run Apps for Office

Depending on where you look, you might find slightly different requirements listed to be able to install and use apps for Office. But here is my summary of requirements to use apps on your Windows desktop computer:

• Excel 2013 (or later) or Excel Online. No mention is made of Office 365 but I presume that it will work with it.
• Internet Explorer 9 or later. This doesn’t have to be your default browser, but it must be installed as Excel uses IE to render the app.

If you are using Excel Online then you must be using a browser that supports HTML 5 and JavaScript 5. The minimum browser versions are :

• Internet Explorer 9 with at least MS12-037: Cumulative Security Update for Internet Explorer: June 12, 2012 installed
• Internet Explorer 10 or later (highly recommended)
• Firefox 12
• Safari 5
• Chrome 18

Content Apps v Task Pane Apps

In Excel you can have two types of apps, Content App and Task Pane Apps.

A Content App is inserted into the body of the worksheet (inline) and appears as an object embedded into the sheet, similar to how a chart is embedded.

A Task Pane App appears alongside the worksheet in a separate Task Pane. This type of app can be used to provide additional functionality, for example you could have an app that translates selected text from the sheet.

How do I create an app?

An app consists of, at minimum, just one file. This is an XML file that describes the app for Excel, and points to a website or HTML file that contains all the code that makes your app work.

If you want, and to make any real use of apps you will, create your own HTML file and put in this all the code you need for the app.

If you are already screaming at the thought of XML and HTML, the good news is it is just text and the programming skills required to get started are pretty much zero. If you can edit a text file, you can create an app.

Microsoft does provide a development tool called NAPA and you can also use Visual Studio, but you can just use your favorite text editor and mine is Notepad++.

Anatomy of an App

Before we dive into creating an app, let’s first look at how the things work. As I said the bare minimum you need just one file, an XML file, which is called the app manifest.

This manifest is a series of data telling Excel things about the app like who wrote it, what type of app it is, what it can do and lets you specify things like a name for the app and a description, and where the HTML file is located. A simple one looks like this

NOTE : the color coding and numbering along the left hand side are all done automatically by Notepad++. It really helps when working with this type of file and beats the pants off Notepad

For what we are going to do the only bits you need to change are

• <Id> : a unique identifier for the app. Must be a series of HEX numbers i.e. 0 to 9 and a to f
• <ProviderName> : Your name or your business name
• <DefaultLocale> - change this if you are not using EN-US (English-United States)
• <DisplayName> : The name of the app shown in the list of apps when looking for apps to insert in Excel
• <Description> : The tool tip that pops up when you hover your mouse over the app in the list of available apps.
• <SourceLocation> : where the app’s HTML file is located
• <RequestedWidth> : initial width of the app. This is optional
• <RequestedHeight> : initial height of the app. This is optional

You can see that for the <SourceLocation> I have specified our blog address https://www.myonlinetraininghub.com/blog so all this app does is load our blog into the worksheet. Easy!

Creating Your Own HTML File

If you want to do more than just load a website then you will need to create your own HTML files, or get someone to do this for you. At its most basic the HTML looks like this :

Let’s say we want to play a video from Vimeo in our worksheet. We need to get the embed code for the video from Vimeo and put this into a HTML file which is loaded by the app. Here’s the HTML file with the video embed code added :

NOTE : The embed code from Vimeo is the line starting with <iframe … and then continues off screen. I haven’t shown it all simply because it won’t fit.

All I’ve had to do was add a single line of code (provided by Vimeo) and I now have an app that can embed and play a video in my worksheet. Cool.

Microsoft state that an app can also include a 3rd file which is a CSS file (Cascading Style Sheet) which is used to apply styling to the HTML file. Things like the colour of text or the size of the font. A CSS file isn’t always needed and if it is, it is linked to from within the HTML file anyway, so from my point of view it's best to say that an app contains as many files as the HTML requires. This could be one or more CSS or JavaScript files, and/or other files like CSV, video, images etc.

Setting up apps for installation

In order for the app to be available for you to insert into Excel you need to put the XML and HTML files in certain places. The XML file must be stored in an App Catalog, and the HTML file can be in that same App Catalog or on a web server.

Creating an App Catalog

The purpose of the app catalog is to store all the app manifests (the XML files) in a central location that can be accessed by everyone. Your colleagues can then access and install your apps.

Apps can be published to the Office Store, and Outlook apps can be published to a Microsoft Exchange server, I will cover neither of these scenarios here.

Our Excel app manifest (the XML file) can be stored (published) to either a Microsoft Sharepoint catalog, or to a shared folder on a computer. I am not going to go over setting up Sharepoint, and will instead look at setting up a shared folder as an app catalog.

The shared folder can be on your own Windows computer but if you want to distribute apps to your work colleagues, it would make most sense to be on a server that everyone in your company can access.

Setting Up a Shared Folder

The first thing you need to do is create a folder on the computer hard drive (or use an existing one) and then share it so that everyone has Read access.

Nominating the Trusted App Catalog

In Excel go to :

File -> Options -> Trust Center -> Trust Center Settings -> Trusted App Catalogs

Add the shared folder into the Catalog Url field using the format \\computer_name\share_name then click the Add catalog button.

The location should now appear in the list of Trusted Catalog Addresses. Check the box beside the shared folder name (under the Show in Menu heading) then click OK, and OK again to get back to Excel.

In the example below my computer is named pgt-pc and the shared folder is named Appcatalog so the Url I entered was \\pgt-pc\Appcatalog

Store XML App Manifests in Catalog

Make sure you move all your XML manifest files to the shared folder you are using as your catalog. Then, go into Excel and click on the INSERT tab, then click on My Apps

Click on SHARED FOLDER and you should see the apps you have created, you may need to click Refresh.

Either double click one to insert it, or click on an app and then click on the Insert button at the bottom right of the window. If you haven’t specified an initial width and height for the app, you will probably need to resize it.

File Locations

The XML app manifest files must be stored in an app catalog. According to what I have read from Microsoft they say that you can also store HTML files in the catalog. Whilst this will work for some simple apps, you will probably encounter errors with other apps that need to load files (JavaScript, PHP, CSS etc) from other web based locations. So I’d recommend that you only store XML manifest files in the app catalog.

HTML and any other files the app needs are best stored on a web server. Your web server could be an intranet, a publicly available web server or a Content Delivery Network like Amazon S3.

Examples

I’ve created several example apps, some very simple, some more complicated. Remember that what you are doing is embedding a webpage into a worksheet. Pretty much anything that you can do on a webpage you can do in the app.

The videos below for each example are show in 720p High Definition so you can go full screen if you wish, or just watch them embedded in the page. There is no sound with any of these videos.

Privately Hosted Video

This video is loaded from our content delivery network (CDN) Amazon Cloudfront.

Embedded Video from Vimeo

This video is loaded using the embed code provided by Vimeo.

Graphics

This displays a map of the world created using the D3.js JavaScript library created by Mike Bostock.

A Website

Our blog loaded into your workbook.

Market Data

Data such as stock and commodity prices, foreign exchange rates, and stock charts. Some of this data is continuously updated in your workbook.

Workbook Embedded in a Workbook!

Just for fun I created an app so that I could embed a workbook that we have stored on OneDrive into another workbook. Read our blog on how to create an interactive Excel workbook on OneDrive.

Interactive Animation

An interactive animation using the D3 JavaScript library. Point your mouse at the bubbles and watch them move away. How long will you chase those bubbles?

Bar Chart

A bar chart created using D3 again. This chart shows the frequency of use of the letters of the alphabet.

After inserting an app you can resize it and Excel will remember this next time you open the workbook.

Check out these apps yourself

In order to install my apps yourself you will need access to the XML manifest files. As you won't be able to access the shared folder on my computer, the best way for you to do this is to download the manifest files and set up your own shared app catalog, as described above.

You can then insert the app(s) into your workbook and load the HTML files from our servers. Or if you like you can also download the HTML files, pick them apart to learn how they work, and put them on your own servers.

Downloads

Interacting With the Workbook

Apps aren’t limited to just looking at or reading what’s loaded into them. You can interact with the data in your workbook. But this is where it starts to get complicated and you need to know JavaScript.

Microsoft provide a JavaScript API (application programming interface) to allow you to both transfer data from the sheet to the app, and write data from the app back to the sheet. Want to take that stock price from the app and use it in your worksheet? How about taking those sales figures from your sheet and plotting an interactive chart or a choropleth map in the app?

The possibilities are huge.

Acknowledgements

All D3.js code written by Mike Bostock

Charts and market data from Investing.com and TradingView

Sharing is Caring

If you liked this or know someone who could use it please click the buttons below to share it with your friends and colleagues.