Excel Services Cheat Sheet

You want (or maybe you need) to learn Excel Services in 3 minutes?

Ok, ok, I know it’s impossible, but anyway, if you have to cheat, here’s some help.

Ready? Go!

· Their name is Excel Calculation Services

· They are exposed by a Service Application, that I strongly suggest you create J

· You need the Enterprise Edition of SPS2010

· The ECS perform computations and rendering on the serve side, starting from an Excel 2007/2010 file that should be “compatible” and uploadedl into a SharePoint library

· What compatible means?

o Long story: http://office.microsoft.com/en-us/excel-help/differences-between-using-a-workbook-in-excel-and-excel-services-HA010021716.aspx?pid=CH101024611033. It’s for Office2007/SP2007, most of these limitations still apply

o Short Story: no VBA macros, no data validation, no external worksheet references, etc…

· You access computation results from a client application using SOAP Web Services or the brand new Rest Services API

· You access the sheet (or part of it) rendered by the server just by clicking the “View in browser” link on the ECB, or maybe the document link itself if the default library settings state that the default open behavior is “Open with Server Side Handler”

· When the document is viewed in the browser, all logics behind it (i.e. all formulas) are flattened and you only get the results of the computation

· If a user has just the ViewOnly permissions, he can just view the document in the browser or download a snapshot of it. Once again, the snapshot contains just data, without formulas

· If a user has, at least, the Read permissions, he can also download a copy of the document, which contains also formulas and logics

· From an administrative perspective, you – the Farm Admin guy – should probably:

o Define “trusted locations” (i.e. libraries where the ECS consider documents trusted and, as such, can manage them as discussed above)

o Define external data connections (upload Data Connection Files, specify how connections are performed, take care about authentication, etc…)

· If you are guessing what a data connection is… well… it’s a connection to some external data repository! More seriously speaking, y can define such connections with the Excel Client (or retrieve them from a central location that is, of course, your SharePoint environment). Data that flow through data connections can be displayed in a sheet, with al sort of cool features that Excel provide (pivot tables and reports, slices, etc…)

Mmmmmhhh… it took more that 3 minutes, probably J