By: Neil Barkhina
When the first version of Excel Services was released in 2007 with SharePoint Server, customers were excited and eager at the possibilities this would bring. From a BI Perspective, this meant people could finally bring the rich charting and visualization capabilities of Excel to the web. However, in my many years of working with this technology, Excel Services is probably one of the most misunderstood and underutilized features of SharePoint 2007. I believe there are several reasons for this.
For one, the interactivity capabilities were minimal at best. You could send filter data to Excel Services in the form of Named Parameters, but few people used this feature (or even knew about it). Excel Services could also connect to SQL Data Sources. If you were to configure the SQL Connection inside the Workbook and only give people “View” permissions to that file, you could display LOB data in Excel without revealing login information or the IP behind the formulas driving the Workbook. I have seen a few examples of customers using this functionality but by and large, short of just adding an Excel Web Access Web Part to a page in order to display a spreadsheet, few people took advantage it. To me, some of the most compelling value adds of this technology was the rich programmability and platform capabilities. Excel Services exposes a vast API from both .NET and Web Services.
Fast forward to 2010, and Microsoft has now built out this fledgling technology into a much richer platform. Everyone knows that with the 2010 release of SharePoint we now have Office Web Apps. With this, you can edit most of your favorite Office formats right in the web browser such as Word, PowerPoint, OneNote and of course Excel (with Excel in particular you could do multi-user authoring right on the web!) But what they’ve also done is enhance the programmability in a huge way with improvements to the web services layer, and the introduction of a new REST API and Javascript Object Model.
I won’t be covering the Jacascript OM in this post, but the cool thing about REST is that it’s very easy to use. You can display parts of your workbook and interact with it simply by formatting a URL, which means no programming knowledge required. This is in line with Microsoft’s promise of so called “BI for the Masses”. The data is also real-time, which means you can always have a Live and up to date view of your Workbook Data (even external data). And because it’s real time, you can embed it into things like Word Docs, PowerPoint or even anonline BLOG.
Let’s take a look at a spreadsheet I used to track how many were coming to my Birthday Party last year. What I did was use a sorting and filtering table to track who I believed was coming, and then I’d mark their probability as High, Medium, Low, and Don’t know. Granted, not the most scientific of methods, but you get the picture:
In order to access the REST API through a browser simply insert “_vti_bin/ExcelRest.aspx” to the site url of where your spreadsheet resides and then add “/Model” to the end of it. Here is what it looks like in my case:
http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/birthday.xlsx/Model/
Doing this in internet explorer will reveal the following UI:
As you can see, there are API’s for accessing Ranges, Charts, Tables and even PivotTables. The formatting of how these options get returned can also be specified; in the case of Charts you get an Image back. With Tables or Ranges you can specify an ATOM feed, or even HTML. This makes for some pretty interesting scenarios. With ATOM, you can get access to your workbook data in a structured and programmatic way,and because it’s XML you can subscribe to changes in your workbook and even be alerted subsequently. In the following example, I am requesting just a range A1-C6 from Sheet1 (named ranges are also supported). I can email this link to somebody if I want to highlight just a specific portion of a sheet. This way my colleagues don’t need to dig through an entire spreadsheet just to find what I wanted to show:
http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/birthday.xlsx/Model/Ranges('Sheet1!A1|C6')?$format=HTML
In the following example I am specifying just the summary area where I calculate how many people are coming based on various probabilities and their current responses. If they told me they ARE coming, I am assuming an 80% chance of showing up, if they said maybe, I give it 50%. Then I calculate the attendance based on Highs and Lows:
http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/birthday.xlsx/Model/Ranges('Sheet1!G10%7CN14')?$format=HTML
The nice thing about the REST API is you can do “what-if” scenarios by actually changing cell values. The key thing here is that you are not actually saving your changes back to the workbook; it is simply displaying the data in the context of your change. In this example I am changing my Low percentage from 10 to 30. As you can see, at those odds I now need to plan for 6 additional people coming from the Low end:
http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/birthday.xlsx/Model/Ranges('Sheet1!G10%7CN14')?$format=HTML&Ranges('Sheet1!I11')=0.30
And finally, one of the coolest parts of the API is the ability to return charts as images. You simply pass in the name of the image to the function (if you don’t know the name you can check your workbook, or even browse for it in IE using the REST Model UI by clicking on the Charts Link). The images come back in PNG format, and are always up to date:
http://intranet.contoso.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/birthday.xlsx/Model/Charts('Chart%204')?$format=image
The interesting thing about this is you can actually embed these images into a Word Document by going to Insert -> Quick Parts -> Field
In the dialog box, you then pick “IncludePicture” and paste the URL of the Image request using the REST API:
Now when I go to view my doc in Word or the Web Apps, I have that link to my chart so I can always get the latest version of the data.
Well I hope this provided a good overview of the power of the REST API and the new capabilities of Excel Services 2010. Check back soon for more to come!
-Neil Barkhina