Exercise 3: Interacting with the REST API and Excel Web Services

Thus far, the techniques demonstrated have used the JSOM exclusively. In order to round out solutions, you may find it necessary to utilize functionality provided by either the Excel Services REST API and/or the Excel Services Web Services. In Exercise 3, you will learn how to integrate both of these options with the use of the JSOM to round out a solution.

Task 1 – Implementing a File Save Feature

1. A primary expectation that people have when working with documents is the ability to save their changes. The Excel Web Services provides this functionality via either the SaveWorkbook method or the SaveWorkbookCopy method. You can call this method using JavaScript to implement this functionality.

2. Add the following code underneath the comment // createXmlDocument(). These are a couple utility functions that assist in the creation and transmission of a SOAP request.

   JavaScript

   //Creates a XmlHttp object for the SOAP request function createXmlHttp() { var xmlHttp = null; if (window.XMLHttpRequest) { xmlHttp = new XMLHttpRequest(); } else { xmlHttp = new ActiveXObject("Microsoft.XMLHTTP"); } return xmlHttp; } //Creates a XmlDocument object for the SOAP envelope function createXmlDocument() { var doc = null; if (document.implementation && document.implementation.createDocument) { doc = document.implementation.createDocument("", "", null); } else { doc = new ActiveXObject("MSXML2.DOMDocument"); } return doc; }

3. Add the following code underneath the comment // clearSaveResults(). The function saveWorkbookCopyAsync does all the heavy lifting when it comes to implementing the save functionality. This function makes a call into Excel Web Services to access the SaveWorkbookCopy method. The important thing here is that you use the session ID associated with the Excel Web Access web part so that when the call to SaveWorkbookCopy is made, Excel Services knows that is should save a copy with the transient values used in the session associated with the Excel Web Access web part. Note that this bit of code assumes you are saving to a document library at https://intranet.contoso.com/Shared%20Documents. Update the URL specified in the SOAP message as required for your environment.

   JavaScript

   // Saves a copy of the workbook through the use of Excel Services and SOAP function saveWorkbookCopyAsync(action) { if(action == 'cancel') { document.getElementById('savediv').style.display = 'none'; return; } var fileName = document.getElementById('fileName').value; var doc = createXmlDocument(); var xmlHttp = createXmlHttp(); var sessionId = om.getActiveWorkbook().getSessionId(); // SOAP envelope for SaveWorkbookCopy request var xml = '<?xml version="1.0" encoding="utf-8"?>' + '<soap:Envelope xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" ' + 'xmlns:xsd="https://www.w3.org/2001/XMLSchema" ' + 'xmlns:soap="https://schemas.xmlsoap.org/soap/envelope/">' + '<soap:Body>' + '<SaveWorkbookCopy ' + 'xmlns="https://schemas.microsoft.com/office/excel/server/webservices">' + '<sessionId>' + sessionId + '</sessionId>' + '<workbookPath>' + 'https://intranet.contoso.com/Shared%20Documents/' + fileName + '.xlsx</workbookPath>' + '<workbookType>FullWorkbook</workbookType>' + '<saveOptions>AllowOverwrite</saveOptions>' + '</SaveWorkbookCopy>' + '</soap:Body>' + '</soap:Envelope>'; doc.loadXML(xml); xmlHttp.open("POST", "/_vti_bin/ExcelService.asmx", true); xmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8"); xmlHttp.setRequestHeader("Content-Length", doc.length); xmlHttp.setRequestHeader( "SOAPAction", "\"https://schemas.microsoft.com/office/excel/server/webservices/SaveWorkbookCopy\""); xmlHttp.onreadystatechange = function() { // If the workbook is saved successfully, display the result if(xmlHttp.readyState == 4 && xmlHttp.status == 200) { document.getElementById('savediv').style.display = 'none'; document.getElementById('saveresult').innerHTML = '<b style="color:#018FC3;">' + fileName + '.xlsm saved successfully.</b>'; //Clear the result setTimeout(clearSaveResults,5000); } } xmlHttp.send(doc.xml); } // Clears the result of the SaveWorkbookCopy request function clearSaveResults() { document.getElementById('saveresult').innerHTML = ''; }

Task 2 – Enhancing the UI with a Chart

1. Excel support for creating charts and tables of data is one of the traits that make it such a wonderful data analysis tool. Using the Excel Services REST API, you can extract this type of information from a workbook for reuse in other applications. In the TCO model for example, the desired product of the calculations is a chart that compares the total cost of ownership for the three selected machines. Rather than having to navigate between the inputs and the outputs as you make changes, a more convenient option is to display the output at all times so that you can see the results of your changes as you make them.

2. Add the following code underneath the comment // getChartImage().

   JavaScript

   // Gets the chart information located within the current session and uses the // REST API to retrieve a chart with those input values from the stored workbook. function getChartImage(call, values) { if(call == "get") xlWebPart.getActiveWorkbook().getRangeA1Async( "'TCO Detailed Results'!D9:F17", getMachineInputRangeComplete,"MachineTotalCosts"); else if(call == "set") { var chartUrlTemp = chartUrl; // Create URL for REST API chartUrlTemp += 'Ranges%28%27%27TCO%20Detailed%20Results%27!D17%27%29=' + values[8][0]; chartUrlTemp += '&Ranges%28%27%27TCO%20Detailed%20Results%27!E17%27%29=' + values[8][1]; chartUrlTemp += '&Ranges%28%27%27TCO%20Detailed%20Results%27!F17%27%29=' + values[8][2]; chartUrlTemp += '&Ranges%28%27%27Machine%20Inputs%27!E12%27%29=' + values[0][0]; chartUrlTemp += '&Ranges%28%27%27Machine%20Inputs%27!F12%27%29=' + values[0][1]; chartUrlTemp += '&Ranges%28%27%27Machine%20Inputs%27!G12%27%29=' + values[0][2]; document.getElementById('chartimg').innerHTML = '<img src=\'' + chartUrlTemp + '\' width="300px" height="300px" />'; } }

3. Locate the GetEwa() function you added in Exercise 1, Task 2, Step 3 and modify the line // TO IMPLEMENT: getChartImage(“get”); as shown below.

   JavaScript

   getChartImage("get");

4. Add a new script level variable underneath the var xlWebPart script level variable as shown below.

   JavaScript

   // SCRIPT LEVEL VARIABLES

   FakePre-bcfa074d894d403786607594abe1d90b-f3343c382c2c4d608f1784783a994cb4var chartUrl = 'https://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/' + 'Contoso%20TCO.xlsx/Model/Charts%28%27Chart%203%27%29?';

Exercise 3 Verification

To check your work, 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. Verify that a chart from the workbook now appears in the upper-left portion of the browser window.

   Figure 4

   Model Navigation – TCO Calculator

9. Click on Save Workbook Copy. Verify that an additional bit of UI appears in the Content Editor Web part.

   Figure 6

   Save Workbook Copy

10. Click Save
11. Navigate to the Shared Documents library (refresh the page if it was already open).
12. Verify that a copy of the workbook is in the library.