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
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.
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.
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 [] 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
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.
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.