Creating webpages with Excel Web App mashups

Pages in this article

  1. Preparations
  2. JavaScript
  3. Web controls
  4. Demo with controls
  5. Dynamic demo
  6. Conclusion

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:

<div id="chartDiv" style="width: 500px; height: 500px"></div>

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:

<script language="javascript" src="https://r.office.microsoft.com/r/rlidExcelWLJS?v=1&amp;kip=1" type="text/javascript"></script>

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

<script language="javascript" type="text/javascript"> All Java code listed below is located in this location within the script tag </script>

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

Showing last 8 comments of 24 in total (Show All Comments):

 


Comment by: Karen (25-8-2015 18:04:12) deeplink to this comment

Just reading your info, plus a lot of searching and can't find an answer. My workbooks are all in OneDrive, but I would like to link to a single spreadsheet not the whole workbook and have the link update on the webpage, when the spreadsheet is updated. Is that possible?


Comment by: Jan Karel Pieterse (26-8-2015 11:28:29) deeplink to this comment

Hi Karen,

The last time I looked, links were not supported in the Excel webapp.


Comment by: Drew (11-11-2016 23:40:16) deeplink to this comment

Hi Mr. Pieterse,

I wonder if you think it is possible to store an excel file in google drive and embed it from there. I found that fetching the data from a gsheet in Google Drive is about twice as fast as getting it from and xls in skydrive. Or is there a way to format the xlsx so that the data moves faster?

Things I'll try are: create the skydrive sheet using the web app instead of the PC app. Maybe the real xlsx files that I'm synching to skydrive are bloated and I don't know it? If Google Drive is just faster overall, that would be too bad!

Anyway, thanks for thinking about it if you have a minute!
-Drew


Comment by: Jan Karel Pieterse (14-11-2016 09:15:01) deeplink to this comment

Hi Drew,

I wouldn't know of a way to do this from Google drive, I'm sorry.


Comment by: Ravi Kumar (1-3-2018 11:11:55) deeplink to this comment

Where can I get file token from this below embedded excel code?

&lt;iframe width="402" height="346" frameborder="0" scrolling="no" src="https://onedrive.live.com/embed?resid=1F5226D4D3063070%211089&authkey=%21AMkL-xfmDZpIyqY&em=2&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True&wdInConfigurator=True"&gt;&lt;/iframe&gt;


Comment by: Jan Karel Pieterse (2-3-2018 13:31:36) deeplink to this comment

Hi Ravi,

I see you are using OneDrive for business, I expect that works different from OneDrive personal. So I don't really know I'm afraid.


Comment by: Ravi Kumar (5-3-2018 05:46:52) deeplink to this comment

I have to display and edit excel sheet in web page. After editing it, I have to convert into xml file by adding a buttton. Can u tell me pls, Is there any solution for it? Is there any plugins that can be used to embed sheet and do operations?

Thanks in advance.


Comment by: Jan Karel Pieterse (5-3-2018 10:13:49) deeplink to this comment

Hi Ravi,

That is something that should be possible using Office add-ins programmed using JavaScript. Unfortunately I don't have much experience in that area.


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.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].