Back
Excel

Interacting with Your Website’s Data Using Web Excel Mashups

This post is brought to you by Harrison Gordon, a Program Manager on the Excel Services team.

What exactly is an Excel Mashup? With Web Excel, you can embed workbooks into your website or blog. This alone is really powerful and lets you show a workbook directly in your website (it also lets you easily sort, filter, and interact with the workbook as if you were in Web Excel). For more info on embedding a workbook, check out our previous blog post here. If you embed the workbook as a mashup, you can add JavaScript to your website to set or grab data and “mash” it up with other services or functionality. There are a ton of things you can do, but here are a few to give you just a taste of what’s possible:

  • Grab data from Facebook, Twitter, or any other source and add it to a table in an Excel workbook for easy sorting, filtering, and pivoting.
  • Take data from an Excel workbook and visualize it in ways you can’t in Excel.
  • Use the Excel Button to quickly add sorting, filtering, and charts to HTML table data in your website or blog.

Great! How do I make one?

ExcelMashup.com has all of the resources you need to get coding with Web Excel right away. Want to add the Excel Button? Try it out and then create the Excel Button code using our Excel Button generator. Want to see what’s possible with our JavaScript API? Walk through examples, tutorials, and use the JavaScript code explorer to get started writing code.

Go forth and code!

To show the power of Excel Mashups and how quickly you can build one, here’s a Magic 8 Ball example (let’s call it the “Excel 8 Ball”). I created a workbook in Excel that randomly chooses between the original 8 Ball’s 20 iconic responses:

Magic 8 Ball example
 
I could just embed this workbook onto my webpage, but then how would my visitors be able to “shake” the 8 ball? It’s no fun until you get the answer you want, after all.

This is where the magic of Excel Mashups come in – I can add a button to my website called “Shake!” and use that to tell my workbook to recalc – that will change the value on my Excel 8 ball.

Step 1: Upload the workbook to SkyDrive

Upload the workbook to SkyDrive – you can download this workbook here.

Step 2: Create the Javascript Embed Code

Open the workbook in the Excel Web App. In the Home ribbon, press “Share” -> “Embed”. Your configurator should look like this (note that “Ball” is selected and the Embed code is “JavaScript”):


 
Copy the Embed Code on the bottom.

Step 3: Add the Embed Code to your webpage

This is the tricky part, so I’ll walk you through some tips to make this easier. Let’s take a look at the copied code and break it down:

<div id=”myExcelDiv” style=”width: 457px; height: 442px”></div>

This is the DIV that we will place the workbook in – you should copy/paste it into the appropriate spot in your website.

<script type=”text/javascript” src=”http://r.office.microsoft.com/r/rlidExcelWLJS?v=1&kip=1″></script>
<script type=”text/javascript”>
 /*
  * This code uses the Microsoft Office Excel Javascript object model to programmatically insert the
  * Excel Web App into a div with id=myExcelDiv. The full API is documented at
  *
http://msdn.microsoft.com/en-US/library/hh315812.aspx. There you can find out how to programmatically get
  * values from your Excel file and how to use the rest of the object model.
  */

 // Use this file token to reference Excel 8 Ball.xlsx in Excel’s APIs
 var fileToken = “SD3781C2B519CA486B!114/3999692027159267435/t=0&s=0&v=!ACRPXH3rmtuN16I”;

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

 function loadEwaOnPageLoad() {
  var props = {
   item: “Ball”,
   uiOptions: {
    showDownloadButton: false,
    showGridlines: false,
    showParametersTaskPane: false
   },
   interactivityOptions: {
    allowTypingAndFormulaEntry: false,
    allowParameterModification: false,
    allowSorting: false,
    allowFiltering: false,
    allowPivotTableInteractivity: false
   }
  };

  Ewa.EwaControl.loadEwaAsync(fileToken, “myExcelDiv”, props, onEwaLoaded);
 }

 function onEwaLoaded(result) {
  /*
   * Add code here to interact with the embedded Excel web app.
   * Find out more at
http://msdn.microsoft.com/en-US/library/hh315812.aspx.
   */
 }
</script>

This is the JavaScript that is necessary to load the workbook – you should place this after your BODY tag (this way, the rest of your page will load before the workbook does, it will keep your page running quickly).

Bonus!

I created a JavaScript file that makes working with the JavaScript API a bit easier. Add these Script tags between the two Excel script tags. It should look like this:

<script type=”text/javascript” src=”http://r.office.microsoft.com/r/rlidExcelWLJS?v=1&kip=1″></script>
<script type=”text/javascript” src=”
http://excelpowered.com/excel8ball/js/jquery.js”></script>
<script type=”text/javascript” src=”
http://excelpowered.com/excel8ball/js/xl.js”></script>
<script type=”text/javascript”> …

At this point, you should be able to open the web page and the Workbook will show up!

Step 4: Add a Shake Button:

To add a shake button, we need to do a few things:

  • Add the below button to your HTML page:

<button id=”shake”>Shake!</button>

  • Replace the JavaScript function onEwaLoaded with the following code:

var xl = null;
function onEwaLoaded(result) {
 /*
  * Add code here to interact with the embedded Excel web app.
  * Find out more at
http://msdn.microsoft.com/en-US/library/hh315812.aspx.
  */
  xl = new XL( Ewa.EwaControl.getInstances().getItem(0) );
  $(‘button#shake’).click( onShakeButtonClick );
}

function onShakeButtonClick( event )
{
 xl.set(“D9″,”=Answer”, recalcAnswer );
};

function recalcAnswer()
{
 xl.recalc();
};

What did that just do?

We just added a little bit of extra code that gets the workbook (and sticks it in the variable “xl”), then adds a click event to the button. The button, when clicked, will call “onShakeButtonClick”. Here’s where it gets a little messy – I have to set the cell D9 so when we recalc the workbook it will change the value of D9 appropriately. I then tell the workbook that after you finish setting D9, call recalcAnswer, which recalcs the workbook.

That’s it! All you have to do now is make it more presentable with a little CSS!

The Finished Product:

You can check out the finished product here. I’ve made it look modern and added a “shake” effect when you press the button, but what you do from here on out is up to you!

There are a lot of things that you can do with Web Excel and our JavaScript API; we look forward to seeing what you build!

Show us your website and let us know what you think!

Have a project you want to share with us and the Excel community? Send us your website and we may showcase it on the Excel blog or on ExcelMashup.com. You can also let us know what you think of Excel Mashups!

Harrison Gordon
Program Manager, Excel Services

Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\taxonomy.php on line 3255 Notice: Array to string conversion in D:\home\site\wwwroot\wp-includes\category-template.php on line 1245

Tags

Join the conversation

1 comments

Comments are closed.