SharePoint 2007: Excel Services - A Quick Introduction

First of all i found a great video tutorial from the microsoft site.Please have a look it'll show you how to enable the excel services in sharepoint.



Excel Services is a new cool thing that came out of MSFT as something that comes bundled with MOSS 2007. Excel is arguably one of the top used tools for the information worker (whoaa! Information worker, now I'm talking like MSFT marketing). I utilize Excel very very frequently. If it wasn't for excel, I'd be dead. Let me put it this way, given a choice between excel and gmail, it's like choosing between pizza and hamburgers - please god, may I never see a day when I have to choose between either. Funnily enough, it's not just me, a lot of others use Excel too. And suddenly, these excel sheets that contain valuable information collect on user's desktops and start acting as mini-databases with very valuable calcuations in them. Microsoft realized this as a potential for improvement, so they bundled up Excel Services in SharePoint 2007. This basically gives you a server side version of Excel, and a tad bit more, but a tad bit less also. First of all, Excel Services is NOT like Google Spread. Not at all. In fact, you don't have a web based UI to start editing your excel sheets, and all user's concurrency issues are taken care of - none of that happens. You get some interactivity, in the shape of some standard excel features transporting over such as grouping, pivot tables, filtering etc. and the ability to parameterize your excel sheets. You can even write .NET UDFs to extend the functionality of an Excel sheet. In short, it is a great BI & data presentation tool, but it is not a great data entry tool for the web. Excel Services can be broadly split into two halves - a) What you see and use through a browser, this looks and feels a bit like using Excel, but through a browser. b) The web service API for Excel services. The advantages of Excel Services versus Desktop Excel are obvious Sheets are automatically versioned, a "god" copy is maintained - no more versions in emails and hard drives. Backups of all this important data are easy to do. You gain the capability of securing workbooks, so certain users with limited rights can see the data, but not edit the data. Also, you can establish rules such as "Show only this worksheet to anonymous users" etc. It is possible to create snapshots of the workbook and take them with you for that trip you had been waiting for. You get a major portion of the capability of Excel 2007 interactivity on a web based application. Thus, if you had colors, pie charts, filtering, you name it - a huge portion of that richness can work on a web front end. You can extend the excel sheet through .NET. Whoaaa!! You could do that through VSTO anyway right? Well, you could, but deploying those binaries and deploying updated versions to everyone's desktop is a royal bitch in any reasonable sized organization. The formulae in an excel sheet can now be subjected to the power of a huge and powerful server, running compute cluster, and thus this calculation load can be shared/distributed. And don't forget, these excel sheets are stored in a document library, so you can have workflows, rss feeds, alerts and everything else that comes out of a sharepoint document library. The disadvantages are: Not everything that works on a desktop excel sheet, may work on the web version. For instance, stuff with ActiveX controls, or stuff with add-ins will not work properly in excel services. Excel services are bundled with MOSS, so you gotta buy the nice shiny version of SharePoint to use this stuff. Stuff that you are used to in the desktop version, from a UI perspective, such as split/frozen panes, zooming, etc. won't work in excel services. Some features work in a "lite/diet" fashion. For instance, PivotChart/PivotTable reports functionality is limited to non-interactive, and limited interactivity respectively. You cannot seriously modify the workbook once it is on excel services. For instance, if you want to add a chart, you can't. You need to do that in desktop excel and republish. So, in this blogpost, I am going to create a simple excel sheet - put it on excel services, and demo how this stuff works. Here is the problem statement I am trying to solve - I am going to assume that I went to the MVP summit (though this year I didn't (too much work to do)), and over there as we go out and eat and drink, other MVPs are borrowing money from me, or I am borrowing money from them. I wish to track all this information in an excel sheet, since I tend to get drunk easily, and the tightwad that I am - I don't wanna loose track of this info. Now I'd like to use all the nice features of excel to get an idea of how much I owe, how much everyone owe's me, and use color coding/charts - all that shputz! But I want this information online, say using Excel services. Okay so Step #1 is, create an excel sheet. My Excel sheet with some sample data looks like this - As you can see, I have a simple workbook with 1 worksheet. I have a table with 3 columns, Person/Date/Amount. I am tracking who owes who what, and on what date was the $ loaned. Also, I have done a sum, and a funky 3D chart, and I am using conditional formatting and nice looking icons that appear automatically to make my data visually more meaningful and appealing. Step #2, is to go under SharedServices for the site you wish to put this excel sheet on, and under Excel Services settings, click on "Trusted File Locations". Go ahead and add the location you will put this excel sheet on. I put it on http://moss2007:10000 and I trusted all children. Step #3, is to go to the site, http://moss2007:10000 in my case, and create a document library. Go to the document library settings, advanced settings, and under "Open browser-enabled documents", choose "Display as web page". Step #4, Is to make the above excel sheet available via Excel services. If you are using a version of office that can publish to excel services, such as Office Ultimate 2007, go ahead and use OFFICEBUTTON->Publish->Excel Services, and publish to the document library created in step #3. Otherwise, just save it in that doc. lib :-). Publishing via Excel services unlocks the possibility of making available named cells as parameters to the excel sheet on the web. Named cells can be exposed as parameters by clicking on the "Excel Services Options" box in Office Excel Ultimate, during the publish process. Cells can be named using the DefineName thing on the Formula ribbon/bar. Also, if you wish to limit the publishing to specific sheets, or part of a sheet - you need to use a version of Office that has the ability to publish to Excel services. Step #5, is to go ahead and click on the excel sheet in SharePoint. What do you see? After a circular green progress circle thingie, the excel sheet is rendered as shown below - Note that I am running this in a browser :-). Even neater, check this out, the same worksheet in a WebPart - w00t!! Now, what do you see? A large portion of the functionality carried over, though Excel Services decided to turn my 3D chart into 2D (chee thanks! Lesson learnt, don't promise anything based on excel services to client, unless you've tried it yourself first). Plus, if you start playing with the above, you can clearly tell that this isn't exactly "Excel" like "Google Spread", in fact - that would be much like comparing apples to oranges. Google spread is a web based version of excel lite/diet, wheras excel services gives you the ability to put the various excel computations that business users code in an excel sheet, on a server quality machine. Then you can use stuff like Web Services to query, and UDFs to enhance. Plus, another thing you see is, Bill Ryan seems to owe me $95. WTF! Other cool things to do with Excel Services - - Play around a bit and see what stuff carries over to the web based version. Things get interesting with PivotTable and PivotChart, I recommend trying those out. - You could query the functionality embedded in excel sheets using a web service. Imagine, going up to your business user and telling them - hey man, why don't you code your complex calculations in Excel, let me know when you're done, and I'll give a web based version of it. Quite a punch IMO. - Extend this functionality using UDFs. - Use Excel to display stuff in either SQL Server BI or a SharePoint list, and use the data connection library + excel services to render this data in the browser.