This post shows how to code the sample application shown on this blog. This continues part 1 of this coding series.

Before we go into the code of the sample app, I will explain the flow of the web part page. The previous post showed the building of the page. The two really interesting parts in there are the content web-part that hosts the panel at the left and the Query String filter which tells EWA which workbook to load. The flow of the application can be separated into two distinct functionalities – what happens when the “I found something interesting” is clicked and what happens when the page loads (that’s when the page decides if it needs to show “something interesting” or go into the default state.

This part of the series will show what happens when the “I found something interesting” button is clicked – it is the more elaborate piece of the code.

Stage 1: The HTML

First off, here’s the HTML for the panel where the user types what they found:

image

Here’s the HTML:

<div>

    <a href="/Lists/InterestingWorkbookLinks">See what others found.</a>

    <br />

    or...

    <br />

    Describe what you found:<br />

    <input id="title" type="text" value="Check this out!"/><br />

    Notes:<br />

    <textarea id="notes" rows="6" cols="30"></textarea>

    <button id="saveButton" onclick="foundSomething()" type="button">

        I found something interesting!!</button>

</div>

<div id="savingElement" style="display: none">

    Saving...

</div>

<div id="saveDoneElement" style="display: none">

    Done saving!!

</div>

<div>

<a style="display:none" id="sharedLink">Link to what you found</a>

</div>

 

 

 

The only part that’s really interesting is the button – that’s the guy who initiates the operation of saving the data (it runs the method foundSomething()). Above that piece are the text boxes used to enter the relevant information (title + notes) and below it are a bunch of divs I use to notify the user about what’s happening as well as a link (that’s initially hidden) to the link we just created.

Stage 2: The onclick method

(Note that the full script contains the JSOM skeleton OM described in my EWA JavaScript OM posts)

When clicking on the button, the following code runs:

function foundSomething()

{

    var d = new Date();

    var name = "InterestingWB" + d.getYear().toString() + d.getMonth().toString() + d.getDay().toString() + d.getHours().toString() + d.getMinutes().toString() + d.getSeconds().toString() + d.getMilliseconds().toString() + ".xlsx";

   

    var sid = ewa.getActiveWorkbook().getSessionId();

    var url = "http://tsdlt2/InterestingWorkbooks/" + name;

    var client = new ExcelServices(url);

    client.saveWorkbookCopy(sid, url, saveComplete, name);

    showElement("savingElement", true);

    showElement("saveDoneElement", false);

    enableElement("saveButton", false);

}

 

 

First, we create a “unique” name for the file (local variable name). We then get the sessionId the EWA is currently working with from the control. The session id is the opaque id on the back-end that is used to identify the instance of the workbook the user has opened. It is used widely when using Excel Web Services. The method for getting the session id (getSessionId()) is the replacement for the old ewaGetSessionId() method we had in SharePoint 2007. Once we have the session id inside the sid local variable, we also create a new proxy for the SOAP interface exposed by Excel Services. I am using a modified version of the Excel Web Services AJAX library I published on the blog a few years ago – to this version, I added the new saveWorkbookCopy() method introduced in Excel Services in SharePoint 2010.

With the proxy created, we make a call to the saveWorkbookCopy() method – passing in the callback saveComplete() which will, as the name implies, be called when save is complete. The Excel Web Services saveWorkbookCopy() function will save the workbook into the appropriate URL (the unique name in a document library created especially for this application called “InterestingWorkbooks.xlsx”).

Finally, the function shuffles around some of the elements so that the user gets an indication that the workbook is being published.

Stage 3: Adding the proper link to the SharePoint list of links we use as the repository

When the call to SaveWorkbookCopy() is done, we are ready to create the link that will be saved into the SharePoint link library.

This method is divided into three parts – the first part figures out the URL to the workbook we just saved, the second builds the XML we will send SharePoint to add a link to the list and the last does the actual sending of the link:

function saveComplete(call)

{

    // Part 1 - Create the link.

    var sel = ewa.getActiveWorkbook().getActiveSelection();

    var range = sel.getAddressA1();

    var url = call.excelServices.userState;

    var link = "/Docs/SomethingInteresting.aspx?url=" + encodeURIComponent(url) + "#" + range + "|" + encodeURIComponent(document.getElementById("title").value);

 

    // Part 2 - Build the XML we will send ListData.svc

    var data = document.getElementById("restInsertRequest").xml;

 

 

    data = data.replace("{title}", xmlEncode(document.getElementById("title").value));

    data = data.replace("{link}", xmlEncode(link));

    data = data.replace("{notes}", xmlEncode(document.getElementById("notes").value));

   

    // Add to the list of links.

    var req = new XMLHttpRequest();

    req.onreadystatechange = listAddDone;

    req.open("POST", "/_vti_bin/ListData.svc/InterestingWorkbookLinks");

    req.setRequestHeader("Content-type", "application/atom+xml");

    req.setRequestHeader("Content-length", data.length);

    data.replace("{title}", document.getElementById("title").value);

    req.send(data);

    document.getElementById("sharedLink").href = link;

}

 

Part 1 – we get the current selection from the EWA by using the getActiveSelection()method on the workbook. We then get the address of the selection (i.e. Sheet1!A1:Z26) and the URL to the saved workbook (we set it as the user-state when we made the saveWorkbookCopy() call). The link takes the following form:

/Docs/SomethingInteresting.aspx?url=[url to saved workbook]#[range]|[title]

In the previous post, I showed how we have a QueryString filter web part associated with the “url” parameter – this is how we make the EWA show the correct workbook when opening from the list of links (the url parameter on the link we create). We then have two items after the hash of the link – these will be used to figure out what to do and I will show that code in the next post.

Part 2 – Here we take the XML from an XML part that’s on the page – this XML is a template we will use to do a REST call into SharePoint and tell it to add a link to the list. Here’s the XML:

<xml id="restInsertRequest">

  <entry xml:base="http://tsdlt2/_vti_bin/ListData.svc/"

  xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"

  xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"

  xmlns="http://www.w3.org/2005/Atom">

    <id />

    <title type="text"></title>

    <updated>2009-12-04T09:06:53-08:00</updated>

    <author>

      <name />

    </author>

    <category term="Microsoft.SharePoint.DataService.InterestingWorkbookLinksItem" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />

    <content type="application/xml">

      <m:properties>

        <d:URL>{link}, {title}</d:URL>

        <d:Notes>{notes}</d:Notes>

      </m:properties>

    </content>

  </entry>

</xml>

 

Most of it is goo needed to make SharePoint’s ListData.svc work, the interesting part is at the bottom where the properties element is – we have two elements there – the link (<URL>) and the notes (<Notes>).

Once we take the XML content, we replace the various tokens (link, title and notes) with their respective values.

Part 3 – Finally we make the call to SharePoint to add the link. We use an XMLHttpRequest() object to post the data to the appropriate link (/_vti_bin/ListData.svc/InterestingWorkbookLinks in this case – the REST entrypoint for our list of interesting links). We pass in the function listAddDone() function as a callback.

At the end, we also change the sharedLink element to point to the link we just created – that way users can copy it and send via email.

Stage 4 (Last): Update the UI to show the user that the save is done.

Finally, we just do some UI clean up, showing the relevant elements to the user so that they know we are done saving.

function listAddDone()

{

 

    showElement("savingElement", false);

    showElement("saveDoneElement", true);

    enableElement("saveButton", true);

    showElement("sharedLink", true);

} 

 

And that’s it pretty much – once this sequence has occurred, the following steps have taken place:

1. We save a copy of the currently used workbook to a special document library (so that any changes the user made are retained).

2. We create a link back to our page with a url query string parameter pointing to the new workbook and with some more info tacked at the end saying what the title is and what the range selected is.

3. We add the link to a SharePoint link list so that it can be found by other users.