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:
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:
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".
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:
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.
(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:
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.");
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;
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.
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");
string postBack= Page.ClientScript.GetPostBackEventReference(this, SaveCommand);
string scriptlet = String.Format(
"Saving this file will overwrite any changes that have been made since you last opened it. Are you sure you want to proceed?",
item.NavigateUrl = scriptlet;
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)
byte bits = GetWorkbookBits(WorkbookType.FullWorkbook);
using (SPSite site = new SPSite(ConnectedEwa.WorkbookUri))
using (SPWeb web = site.OpenWeb())
SPFile file = web.GetFile(ConnectedEwa.WorkbookUri);
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.
This is what GetWorkbookBits() looks like:
private byte GetWorkbookBits(WorkbookType type)
ExcelService s = new ExcelService();
byte bits = s.GetWorkbook(m_sessionIdTextBox.Text, type, out status);
catch (SoapException soapEx)
string code = soapEx.SubCode.Code.Name;
if ("InvalidOrTimedOutSession".Equals(code, StringComparison.OrdinalIgnoreCase))
throw new UserException("The session has timed out.");
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.