Download the Ewa Companion

Over the past year or so, some requests have been voiced by the community again and again. Some of these related to the EWA, asking for extra functionality that is not there by default. To that extent, I decided to write the EWA companion which contains some of the functionality that has been requested by users.

Important note: I am not an HTML/JS programmer. At best, I stumble around, trying to make things work. At worse.. Well.. Lets not talk about that. My HTML stuff tends to look like crap. On top of that, this code is intended to be sample code. I only have rudimentary error checking and some code-paths have not been tested very well.

The EWA companion introduces the following functionality:

  1. Save capabilities - save back to the file that is currently opened in Excel Services.
  2. Save As capabilities - save the workbook into a new location (document library in SharePoint)
  3. Open/Download capabilities - these allow the user to download the currently opened workbook to the client. The extra functionality here is that the user does not have to open Excel to do that. On top of that, this will allow Excel 2003 (and prior) to open the workbook. Furthermore, if you have the Excel Viewer installed, the workbook can be opened automatically there as well.
  4. Periodic Refresh/Recalc - When workbooks contain volatile formulas (=NOW for example, or some volatile UDF), it is sometimes desirable to allow them to periodically recalculate and refresh the HTML to show the new results. Furthermore, it is sometimes useful to be able to periodically refresh pivot-tables that are not connected to external data sources, but rather, to data on an Excel sheet - something EWA cannot do today.

Today's post concentrates on setting up the EWA Companion itself and enabling the "Save" functionality.

Once set up, you can simply add the companion to the page just like you could any other web-part:

Adding the companion

Once added, the companion needs to be connected to an EWA. Currently, the options you have is to place an EWA above or below the companion. You can then control what the companion connects to by modifying the "Controlled EWA" property in the Companion's properties.

In the following image you can see a web-part page in design mode with 4 points of interest:

1 - This is the EWA companion. As you can see, it is "selected" and so the properties pane at the right shows its properties.

2 - This is the EWA itself - it is currently not set up to show any workbook.

3 - Properties Category for the companion - This category contains all the properties that the companion supports.

4 - The Controlled EWA property - this is the one that governs which EWA is controlled. Currently, the value is "Next" which means that the EWA below the companion will be controlled. Note that if the companion was to be below the EWA, this property should have shown "Previous".

image

Next, I will show you how a set-up page looks. This time, there will be a book selected inside the EWA and the companion would have been set up to only show the "Save" menu option:

image

Clicking on the "Save" menu item shows up a warning. Once "OK" is clicked, the workbook as it is in EWA will be saved back to SharePoint, overwriting the workbook that is currently there. Since there's no locking taking place, you may end up overwriting somebody else's changes.

How does it work?

(Note that I am not going into complete details here, just showing the jist of the functionality - the full source code is supplied with the binary)

The save functionality goes through the following steps:

  1. In the server, the companion detects what EWA it is connected to - this is done everytime the control is created.
  2. On the client, when "Save" is clicked, script runs that takes the session-id of the EWA and posts it back to the companion web-part.
  3. The companion web-part then calls the Excel Web Services API's GetWorkbook function, getting the stream of bytes that represents the workbook.
  4. Next, the companion takes the stream and the name of the workbook the EWA is pointing to and uses SharePoint APIs to write the stream back to the document library.

Connecting to the EWA

This is done on the server, in the web-part code:

private void ConnectToEwa()

{

       ExcelWebRenderer result = null;

       if (ConnectMode == ConnectMode.Next || ConnectMode == ConnectMode.Previous)

       {

              int delta = (ConnectMode == ConnectMode.Next) ? 1 : -1;

              int index = ZoneIndex + delta;

              object webPart = Utils.WebPartFromZone(Zone.WebParts, index);

              result = webPart as ExcelWebRenderer;

              if (webPart == null)

              {

                     throw new InvalidConnectionException("Could not find a control to affect. Please check the 'Controlled EWA' property in the companion properties");

              }

              else if (result == null)

              {

                     throw new InvalidConnectionException("The control that is set up to be connected to the companion is not an EWA. Please check the 'Controlled EWA' property.");

              }

       }

       else

       {

              throw new InvalidConnectionException("This connection is not currently supported. Choose either 'Next' or 'Previous' in the 'Controlled EWA' property.");

       }

       m_connectedEwa = result;

}

What this code does, is use the Utils class method WebPartFromZone (shown below) to find the next or previous web-part. The Zone property allows for easy access to the Web-Part page zone the EWA companion is currently located in.

static class Utils

{

       public static WebPart WebPartFromZone(WebPartCollection collection, int zone)

       {

              WebPart result = null;

              foreach (WebPart webPart in collection)

              {

                     if (webPart.ZoneIndex == zone)

                     {

                           result = webPart;

                           break;

                     }

              }

              return result;

       }

}

As you can see, the way this method works is by using the ZoneIndex to look for the relevant Web-Part. ZoneIndex is not necesserily aligned with the actual index of the element in the WebParts collection of the Zone object.

Adding the Save button

Now that we have a reference to EWA (inside the m_connnectedEwa field which is accessible through the ConnectedEwa property), we can use it to build the script that will run when we click the "Save" button:

private void AddSaveButton()

{

       System.Web.UI.WebControls.MenuItem item = new System.Web.UI.WebControls.MenuItem("Save");

       m_menu.Items.Add(item);

       string postBack= Page.ClientScript.GetPostBackEventReference(this, SaveCommand);

       string scriptlet = String.Format(

              "javascript:if (confirm('{0}')) {{ document.getElementById('{1}').value = EwaGetSessionId('{2}');{3}; }};",

              "Saving this file will overwrite any changes that have been made since you last opened it. Are you sure you want to proceed?",

              m_sessionIdTextBox.ClientID,

              ConnectedEwa.ClientID,

              postBack);

 

       item.NavigateUrl = scriptlet;

}

As you can see, the Save button (actually, as ASP.NET menu item) gets a javascript block attached to it. What the Javascript does, in essence, is confirm with the user that they indeed want to save (that's the call to the confirm() method). It then assigns a value to a hidden text-field we have on the web-part (that's the m_sessionIdTextBox - it is created as  part of the web-part). And lastly, it issues a post-back to the web-part (the post-back was generated by a call to GetPostBackEventRefernece()).

Saving the workbook to SharePoint

What this does, in a nut-shell, is to take the session id that the EWA is currently using and post it back to the server, with the "Save" command. This is where the save command is processed:

public void  RaisePostBackEvent(string eventArgument)

{

       if (eventArgument == SaveCommand)

       {

              SaveButton_Click();

       }

}

void SaveButton_Click()

{

       try

       {

              byte[] bits = GetWorkbookBits(WorkbookType.FullWorkbook);

              using (SPSite site = new SPSite(ConnectedEwa.WorkbookUri))

              using (SPWeb web = site.OpenWeb())

              {

                     SPFile file = web.GetFile(ConnectedEwa.WorkbookUri);

                     file.SaveBinary(bits);

                     SetMessage("Save succeeded!");

              }

       }

       catch (UserException u)

       {

              SetMessage("Save failed: " + u.ToString());

       }

       catch (Exception ex)

       {

              SetMessage("Unknown save failure:" + ex.ToString());

       }

}

The SaveButton_Click() method calls the GetWorkbookBits() method, asking for the full workbook (since that's what we are interested in when we save) and then makes calls to the SharePoint APIs to save the workbook back to the document library.

Getting the workbook bits from Excel Services

This is what GetWorkbookBits() looks like:

private byte[] GetWorkbookBits(WorkbookType type)

{

       try

       {

              ExcelService s = new ExcelService();

              Status[] status;

              byte[] bits = s.GetWorkbook(m_sessionIdTextBox.Text, type, out status);

              return bits;

       }

       catch (SoapException soapEx)

       {

              string code = soapEx.SubCode.Code.Name;

              if ("InvalidOrTimedOutSession".Equals(code, StringComparison.OrdinalIgnoreCase))

              {

                     throw new UserException("The session has timed out.");

              }

              else

              {

                     throw new UserException("Could not save the workbook (probably due to permissions).");

              }

       }

}

In here, we make a call to the Excel Web Services APIs to get the binary representation of the workbook (a byte array) which we then simply return to the caller.

That's about it for Save. In the next post I will show how the Save-As functionality works and how it was coded.