Advanced Excel Services REST API capabilities – passing parameters to a spreadsheet

So far in the posts discussing Excel Services REST, I showed how easy it was to gain direct access to elements within workbooks. One immediately sees the potential of really releasing the data and the visualization that’s locked within Excel and making it easily available on the web. The really advanced stuff though is a little more subtle! With the Excel Services REST APIs, you can also pass in parameters into the spreadsheet, allowing you not only to leverage the data and the visualization, but also to leverage the logic that’s behind them. These things are always explained best with an example. Take a workbook I used in a talk at the SharePoint Conference 2009 – a workbook that calculates mortality chances per age group, adjusted to life-style. So, for example, if you smoke 5 packets a day, guess what – you have more chances of dying at 50 than if you didn’t.

Here’s part of the workbook:

image 

The table contains a bunch of parameters (each a named cell) which when changed affect the curve and peak of the chart. The chart shows on the Y axis the chances of death and on the X axis  the age for said chance. In this case, when all the parameters are empty (zero), you can see that the peak for the chances is around age 70 with a 30% chance to kick the barrel there. So how do we leverage this? Lets start simple – the URL for the chart is the following (as discussed in the first post I made about REST):

https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/MortalityChances.xlsx/Model/Charts('ChancesChart')

And when navigated to, simply produces the chart that’s in the workbook:

image

Now, lets tell Excel Services to put a value in one of the parameters and recalculate before it returns the actual result – this is done by using the “Ranges” URL parameter:

https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/MortalityChances.xlsx/Model/Charts('ChancesChart')?Ranges(‘Packets’)=10

 

When used in this manner, the following steps will take place:

1. Excel Services will find the cell named “Packets” and place the value 10 in it.
2. It will make sure a recalc takes place so that the workbook will represent the actual changes requested.
3. The chart image will be sent back to the user.

Here’s the result:

image

As you can see, the peak shifted and now the estimate peaks at 30% still, but with the age being about 60! You can of course add more parameters – lets say the person also has a history of heart conditions. We can simply pass another parameter:

https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/MortalityChances.xlsx/Model/Charts('ChancesChart')?Ranges(‘Packets’)=10 &Ranges(‘FamHeart’)=1

And the effect on the chart:

image

You can easily see how this mechanism can be leveraged to get much more out of your workbook! Not only can you get all the data and all the visual capabilities of the workbook, but REST also lets the logic shine through and be leveraged by the user/developer. And this of course works with all the other REST requests that are supported by Excel Services – it is not limited to charts.

In the next post, I will show how the Gadget can be used in conjunction to other REST links (including REST links with parameters) to allow for even better reuse of Excel workbook data and logic.