Welcome to the new Excel Services!

  • Article

Now that Microsoft SharePoint Conference 2009 is done, I have some more time to start posting about all the nice things we have done with SharePoint 2010 – especially with Excel Services. In the coming months, I will post about Excel Services, concentrating on some of the new capabilities we enabled via our new extensibility mechanisms.

Since the new REST capabilities we built into Excel Services are close to my heart, I will start discussing them first.

Now, if you know what REST is, you will probably aching to comment that what I am going to discuss is not really REST. There are quite a few parts missing and some things that may not be exactly up to the “standard” (as much as it is one, which is not much). If it’s easier for you, you can also call it “URL facilitated access to spreadsheet component elements”. I find REST quicker to say.

The new API allows you to access parts of the workbook directly through a URL. The first part of the URL is a representation of the workbook location. The second part is the path to the requested element inside the workbook. In practice, there’s also a “marker” which is our entrypoint (an aspx page).

To start off with a simple example, say there’s a workbook called bugs.xlsx which lives in a document library called “Shared Documents”. Say that workbook contains a chart called “PersonalBugChart”. You can get direct access to the chart via a simple URL:

https://server/_vti_bin/ExcelRest.aspx/Shared Documents/Bugs.xlsx/Model/Charts(‘PersonalBugChart’)

Each Excel Services REST URL is built out of three parts:

Marker path: That’s the “https://server/_vti_bin/ExcelRest.aspx”. To access REST you always need to preface it with this.

File path: That’s the “/Shared Documents/Bugs.xlsx” part. This is the file that contains the element you are interested in.

Element path: That’s “/Model/Charts(‘PersonalBugChart’)” part. This is the path inside the workbook to the element you request.

That’s it. The result of this URL is a PNG stream which would be the chart that is named PersonalBugChart. When the workbook updates, the REST path will return the new chart – this is a completely live URL. Placing this URL inside an img tag on a page will place the image on the page:

<img src=”https://server/_vti_bin/ExcelRest.aspx/Shared Documents/Bugs.xlsx/Model/Charts(‘PersonalBugChart’)”>

If you have SharePoint 2010 Enterprise, try it with your own Excel workbooks! It’s as easy as that! Change the chart type, the contents, anything, and the next time the chart is requested, you will see the new content.

In the next post, we will discuss how you can access ranges both as data (via XML) and as HTML fragments.