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:
- Save capabilities - save back to the file that is currently opened in Excel Services.
- Save As capabilities - save the workbook into a new location (document library in SharePoint)
- 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.
- 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:
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.
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:
- In the server, the companion detects what EWA it is connected to - this is done everytime the control is created.
- 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.
- The companion web-part then calls the Excel Web Services API's GetWorkbook function, getting the stream of bytes that represents the workbook.
- 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:
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.
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:
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:
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:
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.