(C) 2009 - 2014 by Mourad Louha · All rights reserved

Embedding an Excel file from SkyDrive on your website and mash it up by using JavaScript

If you are using SkyDrive for storing your Office documents, you have probably already discovered the features for sharing your files with other people. And perhaps you have also read on some blogs how to embed an Excel file to your website. Recently, the Excel Team has created the possibility to interact with an embedded Excel file by using JavaScript.

In this blog post, I will describe how to embed and interact with an Excel file from SkyDrive. For this, I uploaded a web app compatible version of my workbook for my Excel formula translations and then I implemented some JavaScript Code to change values in the embedded file.

The workbook includes five worksheets: “Info” just includes some info’s about me, “Formulas” lists the formulas in the different languages, “Compare” can be used for comparing the formulas in two languages, “Languages” includes the list of available languages and “Resources” allows to select between the three languages German, English and French for the interface of the workbook. My goal was now to allow to the user to select the two languages for comparing the formulas and to switch from one interface language to another.

Learn and explore

Microsoft has recently launched the website “Excel Mashup”, where in addition to learning materials and tutorials you will also find some very interesting code samples. The site also lists the most important links to the MSDN documentation about the JavaScript object model, methods and properties for the Excel Services.

SkyDrive Excel Mashup

You can get started creating your first mash-up by going to the section “Get Started” and follow the three steps. The third step “Mash it up!” explains a sample application connecting Bing Maps to an Excel file. However, because the code in this sample may be a little bit complex for a beginner, I recommend to switch to the “Explore” section where you can reach a page offering three choices.

The first possibility “How to” leads to an article from MSDN explaining the basics how to include the JavaScript code on your webpage. The second possibility “Interactive Code Snippets” lets you explore and interactively modify some sample code snippets.

SkyDrive Excel Mashup

As I needed to change some cell values on my sheets for changing a language, I had a closer look on “Set specific cell to value”, which seemed to be most suitable for my goal.

Developing the sample application

For embedding an Excel file to your webpage after you have uploaded the file to SkyDrive, you can select the menu “Share – Embed” to open the assistant. The dialogue offers two different methods how the file should be embedded and allows to set some interactivity and display options.

SkyDrive Excel Mashup

The most important option for ensuring the programmatic interactivity of the Excel workbook is to select “JavaScript” when creating the Embed code. This code may be copied from the textbox and be taken as the starting point for the further development. The code for my application looks like the in the following.

As you can see, the code includes some HTML Tags and then a JavaScript block. The div-element is marked with an ID and also has the width and height properties set. A JavaScript file is then retrieved from a Microsoft server and finally a JavaScript block loads and initialize the Excel file from SkyDrive.

Each file on SkyDrive has a token, a specific and unique identifier. I recommend to always use the assistant from SkyDrive for getting the appropriate token for your file. After initializing the token, the function loadEwaOnPageLoad() is attached to the browser event handler in order to call this function when the page or DOM element is loaded. Let’s have a closer look on the function loadEwaOnPageLoad().

In a first step, the function initializes some parameters for controlling the behavior of the Excel file. Their purpose can be relatively easily guessed by their names; “showGridlines” is used to setup if the grid lines should be displayed, “allowSorting” and “allowFiltering” determine whether if it is possible to sort and/or filter the data in the Excel file. The last line of the function loads the file from SkyDrive and passes the ID of the div.

The function onEwaLoaded() permits to execute own code after the Excel file was loaded.

After a testing if all things go fine in a separate HTML file to this blog, I started with my own extensions. For changing the display language of the workbook, the cell value of $J$9 from the Resources sheet may be changed to an appropriate value. The workbook includes the three languages German, English and French; changing the cell value to DE, EN or FR switches the language. The other sheets are then calculated by using formulas.

So, for interacting with the Excel sheet outside from the workbook, I had to implement some code for overwriting the value of $J$9. The best moment to change the value seemed to be when the Excel file is loaded and by using an URL argument. For providing a comfortable way to switch the language, I added three links on the left upper corner of the HTML file.

I also needed a function to overwrite a cell value and, as I will also need this function for more cases, I considered the sheet name, cell row and column, the new value and an option for activating the target sheet to the function arguments’ list. So, the declaration is setValue(Sheet, Row, Column, Value, Activate) and the code is:

Firstly, I need a reference to the EWA-Object (please also see the MSDB article link at the end of this post) and also a reference to the range or cell object. At this point, it is important to know that the row and column values for a cell address are starting with zero. So, $J$9 is not (9, 10) but (8, 9); what I personally find somewhat illogical, as Excel usually assumes 1 as starting row and column.

An array with two dimensions may be used for writing to a cell. If you explore the Excel Mashup samples, you will discover, that they used [[32]] for writing the value 32 to a cell. However, 32 is a static value, I need a variable. So, I declared the array “arrValue”. The reason why the array is required is that you can also use the array to write values to a range containing more than one cell at the same time.

The value itself is written to the cell by calling the EWA function setValuesAsync(), which also expects the name of a callback function. The callback function is called after the operation has been accomplished and, for example, allows to check if an error occurred. The last argument “Activate” in my function just activates the sheet, if needed.

Then I needed a function to extract the URL parameter and check its value for validity. This is done by the function getURLParam(Parameter, Default, Validator) which parses the URL parameter list and verifies the specified value by comparing it with the validator list. The argument “Parameter” defines the URL parameter name, “Default” represents its default value and “Validator” contains the list of valid arguments.

The result from getURLParam() is stored in the global variable “globalLanguage”, as I needed this URL parameter on different places.

For setting up the workbook display language when the Excel file is loaded from SkyDrive, I called my function setValue(“Resources”, 8, 9, globalLanguage, 0) in onEwaLoaded(); which is, as mentioned before, automatically called when the file is loaded.

Let us now talk about the languages for comparing the formulas. The worksheet “Compare” allows to do this task by entering the language codes to the cells $H$9 and $I$9. The formulas are the retrieved from the worksheet “Formulas” by using Excel functions. The possible language codes are listed on the worksheet “Languages”. I thought, a good way to let the user choose the language, should be to use two combo boxes on the upper left corner of the excel file. When the user selects a language, the selected language code should be written to the corresponding cell and the worksheet should also be activated. And, when loading the Excel file, these combo boxes should also be initialized with the current language.

I assigned the function setFormulaLanguage(Target, Value) to the onChange event for each combo box; where “Target” represents the combo box and “Value” corresponds to the selected value. Then I could use the already implemented function setValue() with the correct arguments.

For initializing the combo boxes I had added the function initFormulaLanguage() and assigned this function to the load handler event of the HTML file.

Finally an important note: for getting the value changes work, you may allow that cell values can be changed in cells. For this, it is needed that the parameter “allowTypingAndFormulaEntry“ is set to true in the loadEwaOnPageLoad() function. The full code is presented in the following listing:

The last steps then were to create some CSS styles for a better look of the file.

Embedding the Excel file in a WordPress blog

I added this section to this blog post, as I had some difficulties to integrate the JavaScript code in my blog post. WordPress successfully did not allow to use copy and paste from my editor.

If you like to use JavaScript code in a single blog post without using a plugin, WordPress recommends not to include the source code to the post, but outsource the code to a single JavaScript file and include the code by referencing to the outsourced file. Example: <script type=”text/javascript” src=http://www.excel-ticker.de/maninweb/mashups/translations.js> </script>.

While testing my embedded file using the method previously described I had some problems when the Excel file was displayed. The icons on the bottom were misplaced and unusable. This may perhaps be due to my CSS styles, which were probably in conflict with the styles used by the Excel WebApp.

On the other side, I used a separate HTML file for developing the sample. So, I included this file by using an I-Frame, in this case not referring to the Excel file but to my file.

Conclusion

The controls I used outside the Excel WebApp are currently not considering the language the user has chosen for its display language. However, this can be easily be implemented. The strings are included in the Excel file and can, for example, be read by the app.

The interactive embedding features of Excel files from SkyDrive are now offering interesting possibilities for everyone. I’m thinking about evaluations, interactive examples of formulas and functions, tutorials, animations and much more.

 
Comments

Is there any way we can achieve the excel mashup without skydrive?

Hi Ankit,

I think it may be possible on a Sharepoint server, but I am afraid it’s not possible on other webservers.

Regards :-)