17 07 2013
Excel Services REST & JavaScript APIs
There is a relatively unknown yet very powerful and extremely cool API sitting in SharePoint Server since 2010. In my 4 years of using SharePoint 2010, even I only came across this just a couple of weeks ago – Excel Services has a REST API endpoint and a JavaScript API!
What’s this mean? It means you can pull regions out of an Excel workbook that’s stored in a Document Library somewhere, using nothing but a specifically formed URL, or some JavaScript running on your page.
REST is trendy and quickly becoming the go-to technology for modern web development, and this little gem has been hiding in the background.
To use it, simply create a workbook with some charts or tables (and name them appropriately, otherwise use the defaults of Table1 or Chart1, etc), and upload it to a Trusted Location somewhere in your site.
Then, access a URL of a similar format:
http://myserver/sites/somesitecollection/_vti_bin/ExcelRest.aspx/Shared Documents/Documents/someWorkBook.xlsx/model/Charts(‘Chart1’)
All things going well, you will get a PNG of the chart!
And this works great for other resources, such as tables and ranges of worksheets. The supported data formats are HTML, ATOM and PNG (for images), as well as the workbook itself (coming down as the original file, essentially). All you need to do is build the URL properly and specify the format.
This opens up a world of possibilities for you to be able to access data and charts within workbooks from other applications or pages in your enterprise environment.
Read some more info about these pretty neat API sets on MSDN: Excel Services REST API Overvew and Using the Excel Services JavaScript API to Work with Embedded Excel Workbooks.
If you want to have a mess with these APIs and see what’s possible, and you don’t yet have an environment available to try them on, have a look at Excel Mashup which has a decent array of examples and resources to show you what’s possible.
AppFabric Caching and Custom Solutions in SharePoint 2013 Routing Engine: EvaluateCondition threw an exception.
Of course the really cool thing is that this data is then pulled as “live” data .. so everytime the Excel spreadsheet is updated the PNG file will be automatically updated too.
This also works for data-connected workbooks (where it will update the chart-images when the data connection is refreshed) .. if you are using PowerView then this refresh process can be automated as an overnight / batch job.