Exercise 2: Advanced Excel Services ECMAScript Object Model Usage

In the last exercise you learned how to provide basic workbook navigation using the JSOM. A slightly more advanced use of the JSOM is to manipulate the data within a workbook. Additionally, you can use the JSOM to extract data out of a workbook for other uses such as populating controls in other web parts on a web page. In Exercise two, you’ll utilize both of these techniques in order to provide “Reset” functionality and to provide a way to change which machines are selected in the model.

Task 1 – Providing a Reset Feature

1. Ideally, the TCO model would allow you to select a machine and populate the model variables with the default values associated with the machine. After you interact with the variables to perform an ad-hoc “what-if” analysis, you may want to reset the variables to the defaults associated with the machine. For example, on the Machine Inputs worksheet, you could change things like the selling price or the residual value and see the effect on the total cost of ownership. When you click reset, these values revert to their original settings. In this task you will implement this “Reset” functionality.
2. If it’s not already open, open the TCO.js file in Visual Studio 2010

3. Locate the GetEwa() function you added in Exercise 1, Task 2, Step 3 and modify the line // TO DO: xlWebPart.add_activeSelectionChanged(onSelectionChange); as shown below.

   JavaScript

   xlWebPart.add_activeSelectionChanged(onSelectionChange);

4. Within the <script /> node, locate the initial group of comments beginning with the comment // TO DO: Add functionality for resetting workbook.

5. Add the following code underneath the comment // onSelectionChange(). This code is an event handler the Excel Web Access part triggers as you change the selection. If you look on the Machine Inputs worksheet, you see the text “reset this item” above each machine. This event handler looks to see if the selected cell is one of those cells and, if it is, calls the resetModel function that you’ll implement in the next step.

   JavaScript

   // Event handler for cell selection changes. function onSelectionChange(rangeArgs) { selectedAddress = rangeArgs.getRange().getAddressA1(); if(selectedAddress == "'Machine Inputs'!E6") resetModel('1'); else if(selectedAddress == "'Machine Inputs'!F6") resetModel('2'); else if(selectedAddress == "'Machine Inputs'!G6") resetModel('3'); }

6. Add the following code underneath the comment // resetModel(). The primary purpose of this function is to initiate the process of retrieving the data associated with model indicated by the modelIndex argument. On the Data worksheet, there is a named range for each machine (Machine1, Machine2, and Machine3). This function uses the JSOM method getRangeA1Async to initiate the process of retrieving this range. The JSOM will call the method specified (getMachineInputRangeComplete in this example) when it returns. The final argument supplied to getRangeA1Async is a user context argument. You can add your own data in this argument and the JSOM will pass it back to your callback function so that your callback function is aware of the context in which the JSOM calls it. The second part of this function retrieves the list of manufacturers in the model.

   JavaScript

   // Reset values on Machine Inputs page to default settings function resetModel(modelIndex) { xlWebPart.getActiveWorkbook().getRangeA1Async( "'Data'!Machine"+modelIndex, getMachineInputRangeComplete, 'ResetMachine' + modelIndex); var rows = new Array('','6','7','8'); var rowIndex = rows[parseInt(modelIndex)]; xlWebPart.getActiveWorkbook().getRangeA1Async( "'Data'!AA" + rowIndex, getMachineInputRangeComplete, 'MachineManu' + modelIndex); }

7. Add the following code underneath the comment // getMachineValuesComplete(). After retrieving the machine input range, the JSOM calls the callback named getMachineInputRangeComplete that you specified in the previous step. Although you have the desired range at this point, you do not have the values. In order to get the values, you have to call the getValuesAsync() method as demonstrated in getMachineInputRangeComplete. When you call this method, you specify whether you want the formatted value or the raw value, the name of callback, and any user context data to include. In this example, getMachineValuesComplete() is the specified callback. Depending on the user context, this function calls one of several other methods to do the actual work with the values that the JSOM returned.

   JavaScript

   // Call back function used when values need to be retrieved from the // active workbook function getMachineInputRangeComplete(asyncResult) { var format = null; asyncResult.getReturnValue().getValuesAsync( format, getMachineValuesComplete, asyncResult.getUserContext()); } // Call back function for values being retrieved from the workbook function getMachineValuesComplete(asyncResult) { var values = asyncResult.getReturnValue(); if (values.length > 0) { if(asyncResult.getUserContext() == 'AvailableMachines') { fillMachineDropdowns("set",values); } else if(asyncResult.getUserContext() == 'MachineTotalCosts') { getChartImage("set",values); } else if(asyncResult.getUserContext() == 'SelectedMachines') { fillMachineDropdowns(" ",values); } else if(asyncResult.getUserContext().substr(0,12) == 'ResetMachine') { //reset models var resetIndex = parseInt( asyncResult.getUserContext().substr(12,1)); var columnNames = new Array("","E","F","G"); var column = columnNames[resetIndex]; xlWebPart.getActiveWorkbook().getRangeA1Async( "'Machine Inputs'!"+column+"12:"+column+"31", getInputsNamedRangeComplete, values); } else if(asyncResult.getUserContext().substr(0,11) == 'MachineManu') { var resetIndex = parseInt( asyncResult.getUserContext().substr(11,1)); xlWebPart.getActiveWorkbook().getRangeA1Async( "'Machine Inputs'!M"+resetIndex+"Manu", getInputsMachineName, values); } } }

8. Add the following code underneath the comment // getInputsMachineName(). The two functions shown in this block of code are responsible for taking the values obtained in the previous calls to getValuesAsynch() and setting the values of the desired ranges on the Machine Inputs worksheet. This whole process transfers data from the Data worksheet to the Machine Inputs worksheet. You could achieve a similar result using lookup formulas. However, the limitation of using lookup formulas is that as soon as you enter a new value in the cell to perform ad-hoc “what if” analysis, you overwrite the formula with a literal value. Consequently, there is not a way to “Reset” back to the original value.

   JavaScript

   // Call back function for setting the manufacturer names in // the Machine Inputs sheet function getInputsMachineName(asyncResult) { var range = asyncResult.getReturnValue(); var values = asyncResult.getUserContext(); var valueArray = new Array(1); valueArray[0] = new Array(1); valueArray[0][0] = values[0][0]; range.setValuesAsync(valueArray); } // Call back function for setting the default values for each // machine in the Machine Inputs sheet function getInputsNamedRangeComplete(asyncResult) { var range = asyncResult.getReturnValue(); var values = asyncResult.getUserContext(); var valueArray = new Array(20); for(var i = 0; i < 20; i++) valueArray[i] = new Array(1); valueArray[0][0] = values[0][2]; valueArray[1][0] = values[0][3]; valueArray[2][0] = values[0][10]; valueArray[3][0] = values[0][15]; valueArray[4][0] = values[0][16]; valueArray[7][0] = values[0][4]; valueArray[8][0] = values[0][5]; valueArray[9][0] = values[0][6]; valueArray[12][0] = values[0][7]; valueArray[13][0] = values[0][8]; valueArray[16][0] = values[0][11] * 100; valueArray[17][0] = values[0][12] * 100; valueArray[18][0] = values[0][13] * 100; valueArray[19][0] = values[0][14] * 100; range.setValuesAsync(valueArray); // Now that the values have been set, update the chart to // reflect the new information // TO DO: getChartImage("get"); }

Task 2 – Adding a Machine Selection Feature

1. In order to be truly useful, a TCO model would allow you to select from a wide range of competing machines rather than be hard-coded to the same three machines. On the Excel client, you might use VBA or VSTO to provide the user interface and related functionality to implement this feature. In the browser, you can recreate this functionality using standard HTML controls and JavaScript along with the Excel Services JSOM.

2. One of the <div /> nodes at the top of the TCO.js file contains three drop-down lists. One of the first things to do is add code to populate each of these drop-down lists with the list of machines available in the model. Locate the GetEwa() function you added in Exercise 1, Task 2, Step 3 and modify the line // TO IMPLEMENT: fillMachineDropdowns(“get”) as shown below. You will implement this function shortly.

   JavaScript

   fillMachineDropdowns("get");

3. Add the following code underneath the comment // dropdown_onchange(). This is the event handler specified in the drop-down controls for the change event. Much of the process for selecting a machine follows the pattern for resetting a machine. That is, locate the data on the Data worksheet, obtain the values, and then transfer the values to the appropriate cells on the Machine Inputs worksheet. The process begins by getting the range on the Data worksheet associated with the machine selected in the drop-down.

   JavaScript

   //Event handler for changes with the machine selection drop down function dropdown_onchange(event) { if(event == null || event.selectedIndex < 0) return; var machineIndex = event.options[event.selectedIndex].value; // first item in the list. not an actual machine if(machineIndex == "null") return; var machineName = event.options[event.selectedIndex].text; xlWebPart.getActiveWorkbook().getRangeA1Async( machineIndex, getMachineInputRange, machineName); }

4. Add the following code underneath the comment // setMachineInput(). The dropdown_onchange event handler in Step 2 calls getRangeA1Asynch. When getRangeA1Asynch returns, it calls getMachineInputRange. getMachineInputRange observes the value associated with the range and sets a value on the Data worksheet that indicates what the current machine is. The Data worksheet uses VLOOKUP functions to lookup the correct information from the range of data on the worksheet containing all of the available machines. In order to set values on a worksheet, the JSOM provides a function named setValuesAsync. setValuesAsync accepts three arguments: an array containing the values to set, the name of a callback function, and a user context argument. In the code below, the function setMachineInput is the callback function specified in the call to setValuesAsync. setMachineInput determines which machine was changed and then leverages the functionality provided by the “Reset” functionality from the prior task to update the model with the correct information.

   JavaScript

   // Call back function for changing the model key in the Data sheet // to reflect changes within the machine selection drop down function getMachineInputRange(asyncResult) { var range = asyncResult.getReturnValue(); var machineName = asyncResult.getUserContext(); machineModel = machineName.split(" "); var valueArray = new Array(1); valueArray[0] = new Array(1); valueArray[0][0] = machineModel[0] + '\n' + machineModel[1]; range.setValuesAsync(valueArray, setMachineInput, range.getAddressA1()); } // Call back function to handle updating the Machine Inputs when a // new machine has been selected in the machine drop down function setMachineInput(asyncResult) { rangeAddress = asyncResult.getUserContext(); if(rangeAddress == "'Data'!B6") resetModel(1); else if(rangeAddress == "'Data'!B7") resetModel(2); else if(rangeAddress == "'Data'!B8") resetModel(3); }

5. Add the following code underneath the comment // fillMachineDropdowns(). This function handles a couple of chores associated with populating the Machine drop-down controls and setting them to default values.

   JavaScript

   // Gets the information needed for the machine selection drop downs, and // creates the drop down list of available machines function fillMachineDropdowns(call, values) { if(call == "get") xlWebPart.getActiveWorkbook().getRangeA1Async( "'Data'!AvailableMachines", getMachineInputRangeComplete, 'AvailableMachines'); else if(call == "set") { for(var i = 0; i < values.length; i++) { // parse out new line escape character from model key for IE var machineName = values[i][0]; if(machineName.split("\n").length > 1) { var tempName = machineName.split("\n"); machineName = tempName[0] + ' ' + tempName[1]; } // add the model item to each of the three lists addItemToDropdown( machineName,"'Data'!B6",document.getElementById('machine1dd')); addItemToDropdown( machineName,"'Data'!B7",document.getElementById('machine2dd')); addItemToDropdown( machineName,"'Data'!B8",document.getElementById('machine3dd')); } xlWebPart.getActiveWorkbook().getRangeA1Async( "'Data'!B6:B8", getMachineInputRangeComplete,'SelectedMachines'); } else { // Handles setting the drop down to the machine that is displayed // when the workbook is initially loaded for(i = 0; i < values.length; i++) { var machineName = values[i][0]; if(machineName.split("\n").length > 1) { var tempName = machineName.split("\n"); machineName = tempName[0] + ' ' + tempName[1]; } var ddIndex = i+1; var list = document.getElementById('machine'+ddIndex+'dd'); setSelectedDDItem(list, machineName); } } }

6. Add the following code underneath the comment // setSelectedDDItem(). These are just a couple of helper functions to add an item to a drop-down list and to set the selected index of a drop-down list.

   JavaScript

   // Add item to a drop down object function addItemToDropdown(text, value, list) { var opt = document.createElement("option"); opt.text = text; opt.value = value; list.options.add(opt); } // Sets the selected index for a drop down object function setSelectedDDItem(list, value) { if(list == null) return; for(var i = 0; i < list.options.length; i++) { if(list.options[i].text == value) { list.selectedIndex = i; break; } } }

Exercise 2 Verification

To check your work thus far, perform the following steps.

1. In Visual Studio 2010, press CTRL + S to save your work.
2. Open Internet Explorer and navigate to https://intranet.contoso.com/SiteAssets/
3. Click Add document
4. Click Browse and select the TCO.JS file in C:\Student\Excel Services
5. Click Open
6. Click OK to upload the document (make sure Overwrite existing files is checked)
7. Navigate to https://intranet.contoso.com/SitePages/TCO Model.aspx
8. Click on Machine Inputs under the Model Navigation section
9. Double-click on the selling price associated with machine 1.
10. Edit the value so that it is $500,000. Excel Services recalculates the workbook with the new value.
11. Click on a different cell.
12. Click on the text “reset this item” above Machine 1.
13. Observe that the value reverts to its original value.
14. Click on the link named Show Machine Selection under the Other heading. Observe that the machine drop-down controls appear.
15. Change the machine selected for Machine 1. Observe that the model updates the Machine Inputs worksheet to reflect your selection.