Chia sẻ qua

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

  • Bài viết

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.

Comments

  • Anonymous
    March 25, 2010
    I love the rest api but.. there seems to one feature missing from passing parameters.  I am using the excel services with a cube and can change the filter as a named range if I convert the filter to a formula but I can't change the filter in the default pivot table format.  I lose needed features when I change the pivot table to a formula. Is there a way to change a cube filter on a pivot table via the rest api without converting to formulas?

  • Anonymous
    March 26, 2010
    Hi John, We considered adding this to the 2010 version, but ended up on putting it in. We definetly know this is something that people would want, but we did not have the proper resources to put it in on time. Thanks for the feedback though - it's good to have somebody corroborate that this is a needed feature.

  • Anonymous
    June 01, 2010
    i would like to know how to change graph on the same page that i have set using rest URL where i am picking graph from excel sheet on same page. problem: i am not able to refresh graph out side excel sheet when i filter data into excel sheet. what could be the javascript to do so?? thanks in advance

  • Anonymous
    March 02, 2011
    Hi, I have my excel file in this location http://abcintbmvqsp1/Shared%20Documents/ALLW.xlsx when i try to access this link http://abcintbmvqsp1/_vti_bin/ExcelRest.aspx/Shared%20Documents/ALLW.xlsx - it given a bad request error. Please help. Regards, Vinodh.

  • Anonymous
    March 06, 2011
    I had the above problem fixed. But facing a new issue now.. I have an excel with the file name Test.xlsx in my sharepoint server. Inside the excel I the below table in which I have defined names for 3 cells - SMark, MMark, TotMark which contains the values 50, 60 & 100 (50+60). And I am trying to access the TotMark cell by passing the value as 100 tot SMark - which ideally should return 160 as the TotMark. But I am getting http 400 bad request error. I tried accessing the http://abcintbm/_vti_bin/ExcelRest.aspx/MyFolder/Test.xlsx/Model/Ranges('SMark')

  • which instantly displays 50 without any errors. But when I try to the same cell by explicitly by passing a value with this link the http://abcintbm/_vti_bin/ExcelRest.aspx/MyFolder/Test.xlsx/Model/Ranges('SMark')=100
  • i am ending up getting http 400 error. I tried giving the value in single/double quotes and nothing worked out for me. Any suggestions or clue would of great help. Thanks in advance. Parameter Value Science 50 Maths 60 Total 110

  • Anonymous
    October 17, 2011
    How do you do this in Sharepoint 2010?

  • Anonymous
    September 01, 2013
    According to the REST specifications, a PUT should not have any side effects. To change the value of a cell using a PUT would require a recalc, and recalcs would typically produce side effects. Hence, strictly by the REST specification, this is not RESTful. This is not really a criticism. It is the key characteristic of an Excel application to do a recalc. If a spreadsheet didn't do this, it wouldn't be a spreadsheet. But this begs the question: how can a software designer create a practice RESTful api for a spreadsheet, and support recalc? Seems like this is a bit of a problem.

  • Anonymous
    November 07, 2013
    I was able to genrate chart based on your post. However when I am passing more then 30 parameter I see blank screen. Do you know if there is any limit on on of parameters