Creating webpages with Excel Web App mashups

Explanation of the Javascript and html items needed

Whilst you can use the default html code (the code that starts with "<iframe>"), there is also the option to use JavaScript to embed the Excel file. And there is more: you can use JavaScript to:

Respond to events in the Excel file (yes, really!), like the Change event
Change a value in a cell in your embedded file
Update form controls on your web page using values from cells in the embedded Excel file
Whatever else you can come up with!

What is needed?

Basically, the JavaScript embedding needs the following items on your web page:

A <div> tag that will act as a placeholder for the to-be-embedded Excel file
An Excel file in one of your public skydrive folders
JavaScript code to show the file at the div tag and to hook up events.

You can also find excellent examples on this page: Excel Mashups

The webpage itself must have the right document type declaration and be loaded from a web server

The Javascript will not work if you store your webpage locally, it must be stored on a web server. Also the page must have the right document type declaration set.

For example, this doc type declaration should work:

<!DOCTYPE html>

html: Add a place holder for your embedded Excel object

At the position where you want the embedded Excel file to appear, add a div tag like this one:

Adjust the dimensions of the tag according to your needs. You may also want to set other positional properties of the div so it behaves according to the other items on the page. I named it chartDiv because that is what I am planning to show, a chart.

Point to the Microsoft JavaScript library which knows about the Excel web app

Javascript needs to know how to get this Excel web app stuff into action. This requires a reference to the proper location, which is achieved by this html code:

The remainder of the JavaScript code is wrapped in the usual tags:

File token

The next thing you need is the file token which uniquely identifies the Skydrive file. This filetoken is embedded in the Javascript code that you can copy from the embed file dialog on skydrive which I discussed on the previous page of this article.

The file token of the file this article will show later on is :

SDB4A3FCAC9C7848C9!285/-5430218907388983095/t=0&s=0&v=!AK3ybHnbJCLh5H0

Not something you'd want to retype every time...

Between the JavaScript tags these lines define a variable and set its value to the filetoken:

// Use this file token to reference the skydrive file in the Excel APIs
var fileToken = "SDB4A3FCAC9C7848C9!285/-5430218907388983095/t=0&s=0&v=!AJHqJpkd-Q5axR8";

We also need a variable that will hold a reference to the Excel Web API (in short: ewa):

var ewaCht = null;

Load the Excel Web Application after loading the page

Next you need an event that will fire once your page is loaded. That is easy enough. Again, this is code that goes between the JavaScript tags, just after the two var declarations I showed you earlier:

// Run the Excel load handler on page load.
if (window.attachEvent)
{
window.attachEvent("onload", loadEwaOnPageLoad);
} else
{
window.addEventListener("DOMContentLoaded", loadEwaOnPageLoad, false);
}

All this says is "when the page is loaded, run the loadEwaOnPageLoad routine".

Loading the Excel Web Application

Once the web page is loaded, the function below is triggered:

function loadEwaOnPageLoad()
            {
            var props = {item: "Chart 2",
            uiOptions:
               {
               showDownloadButton: false,
               showGridlines: false,
               showRowColumnHeaders: false,
               showParametersTaskPane: false
               },
            interactivityOptions:
               {
               allowTypingAndFormulaEntry: true,
               allowParameterModification: true,
               allowSorting: false,
               allowFiltering: false,
               allowPivotTableInteractivity: false
               }
            };
// Embed workbook using loadEwaAsync
Ewa.EwaControl.loadEwaAsync(fileToken, "chartDiv", props, OnChartLoaded);
}

The first thing the code does is define the options for the EWA object, such as whether or not a download link should be provided and whether to show gridlines or not.

The next thing it does is to tell the Ewa to load the Excel file and where to place the object ("chartDiv"). It does so asynchronously, so it sets a function (a call-back) which will be called once the Ewa object is done loading the file. This is the OnChartLoaded function (which you -of course- can change to your own function name).

Setting an object reference to the loaded Ewa

Once the Ewa is done loading your file, it will fire the call-back function:

     function OnChartLoaded(asyncResult)
        {
        if (asyncResult.getSucceeded())
            {
            ewaCht = asyncResult.getEwaControl();
            }
        }

In the example above, a reference to the resulting Ewa object is stored in the ewaCht variable, for later use in your code.

The next page describes how to get the Excel Web app to communicate with controls on your web page.

Comments

All comments about this page:

Comment by: Jonah Feld (12/21/2011 11:58:37 AM)

Great article! I've been tinkering with this technology for a while, and it's pretty amazing. But I'm quite hung up getting it to work in IE.

I can embed a worksheet and manipulate its contents with html forms, which is totally amazing! But a workbook won't load at all via js in IE (tested v7 and 8). I made a super simple test book, and a page which loads it both via iFrame and JS. The embed codes are taken verbatim from SkyDrive. In FF and Chrome, both the iFrame and JS versions load. In IE, only the iFrame. Here's the test page. http://www.jonahfeld.com/excelfeaturetesting.html The killer functionality is being able to manipulate the worksheets contents with form controls, which requires embedding with JS. Any idea why this isn't working in IE?

Might it be my host? What could possibly cause that? Your example in embeddedexcel04.asp loads for me in IE.

Thanks again for the great article and in advance for the help.

Comment by: Jan Karel Pieterse (12/21/2011 11:16:52 PM)

Hi Jonah,

If I copy the code of your page into a straight html page on my desktop it does not work indeed. Even if I replace your fileToken with mine.

But if I put the page on my website AND change the extension from .htm to .asp, it works as expected.

See: www.jkp-ads.com/tempJonah.asp

Conclusion: in order for the JavaScript to work, you need to have a webpage which has the proper extension (serverscript enabled).

Comment by: Jonah Feld (12/22/2011 10:39:55 AM)

Hi Jan,

Thanks so much for the quick response. I recognize that troubleshooting is not why you wrote this article, and I am very appreciative of the help.

I believe that when viewing from the desktop, JS embedding will not work, no matter the browser. This is what you first saw. For JS embedding to work the page must be on a web server.

When viewing the tempJonah.asp page you provided (in IE 8 and 9 on two different machines), I still only see the iFrame embedded workbook, not the JS one below. Here's a screengrab of what I see on your temp page: http://jonahfeld.com/jpk_test_ie8_screengrab.png The page should show the workbook twice: once in an iFrame on top, once embedded by JS below. At the risk of embarrassing myself, can you double check that you see both the iFrame and the JS loaded versions in IE on the temp page you made?

Again, when visiting the page you made with FF or Chrome, I see both.

If you do in fact see the workbook twice in IE and I still only see one, then I must be cursed and am pretty much out of ideas. However, if you see only the iFrame version, then I'm back at square one.

I tested changing the file to .php and .asp on my host (I have an Apache host, yours is IIS). Again, no luck in IE for the JS embedding, but JS works in FF and Chrome with both asp and php file extensions on my host.

Again, thanks for the help. Really hoping to get to the bottom of this.

Comment by: Jonah Feld (12/22/2011 12:01:12 PM)

Hi Jan,

I just got the answer from the thread here: http://blogs.office.com/b/microsoft-excel/archive/2011/12/15/announcing-excelmashup-com.aspx It had to do with a missing DOCTYPE declaration on the page.

Also, IE8 or higher is required.

Thanks again for the article and for the help. Much appreciated.

Comment by: Jim Maiolo (MSFT) (12/22/2011 3:56:25 PM)

Hi Jonah and Jan,

As I discussed in the link below, the problems you are experiencing in IE are actually because the html page you are using does not have a valid DOCTYPE declaration.
http://blogs.office.com/b/microsoft-excel/archive/2011/12/15/announcing-excelmashup-com.aspx

The reason that including the HTML as a server page works is presumably that ASP is adding the doctype info for you. Using static htm and html pages is completely supported as long as you are serving the pages from a webserver and not from a file system.

Please let me know if you have static html pages that are not working for you.

-Jim
Developer, Microsoft Excel Web App

Comment by: Jan Karel Pieterse (12/23/2011 6:40:00 AM)

Hi Jonah,

I could have sworn I got both Excel screens on the asp page, but when I tried just now indeed only the top one appears.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.