SharePoint 2007: Excel Services - A Quick Introduction

Posted on 3/15/2007 @ 11:53 PM in #SharePoint by | Feedback | 44470 views

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.

Sound off but keep it civil:

Older comments..


On 8/20/2007 5:55:47 PM Vik said ..
Hi Sahil,

Thanks for the nice article.

In your step #2, on my installation I can't seem to find a section names "Excel Services"?


How do I turn this on?


On 10/19/2007 1:21:21 AM sukhbir said ..
plz send disadvantages of excel sheet .i want to make a project on vb....


On 12/7/2007 3:55:58 AM kuldeep Singh said ..
Nice article...

Please explain something about how a excel chart will be automatically updated with it's corresponding sharepoint List.

Thanks in advance.

On 12/24/2007 2:02:11 PM Pedro said ..
Cant find any posts anywhere about this kind of funcionallity you talked, do you know any?

This would very interessing but i need a startup article of some sort...

"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."


On 1/17/2008 4:26:27 PM Mark said ..
Hi Sahil


Great / funny write-up.


I have noticed tho, that when I try to make mini-charts with >8 point text, then the chart rendering in Excel Web display gets grainy and sometimes just unpleasant. Do you know of some Excel services control settings which allow me to crankup the pixel quality and get a sharper image? - can you hack it?


On 7/11/2008 9:24:29 AM Andy Dale said ..
Very good article using all those Excel Services annoyances.

I am just going to see if Dundas can do any better.

Andy Dale


Information Specialist


Officetalk


Website : http://www.office-talk.com


Blog : http://andydalesharepoint.blogspot.com/


On 11/21/2008 9:58:24 AM Ariel M said ..
It's annoying, but 3D graphics won't work on your sharepoint or browser, as the very Microsoft states here:


http://office.microsoft.com/en-us/sharepointserver/HA101054621033.aspx#Microsoft%20Office%2012%203-D%20graphic%20effects%20not%20supported


On 4/10/2009 3:34:02 PM Jax said ..
okay, i hear the buzz of Excel Services but if the Excel Services is mailnly what's mentioned above then it's worthless except you can show it in a Web Part and this is worthless because:

1) Excel sheet is stored in a Doc Library and I can distribute user the link to it. And they can open it based on their permission on it (view/contribute/admin etc..)

2) I can configure the Doc Lib to open the doc in browser any ways w/o Excel Services

3) I can use the Page Viewer Web part to display the Excel Spreadsheet info on a page

4) I can have few contributer to update the Excel sheet to keep it updated so rest of us see the lates info.

So if i can do all these without Excel services why do I need Excel Service????

So what's the benefits of using Excel Services anyways????

Regards,


Jax---


On 5/6/2009 3:12:52 AM san said ..
Absolute Funny


On 8/29/2009 6:53:17 PM Ron said ..
Hey, nice Introduction. I have a question, if the end-users (audience) have Excel 2003 installed on their machine and the developer (that's Me) is using Excel 2007 and our corporate SharePoint Site is using MOSS 2007 with add-in web components (I think), will the end-user be able to view the uploaded Excel 2007 spreadsheet on their browser? Just wanted to be sure if they can view the spreadsheet or if they need to install any additional components in order to view. Thanks.


On 9/9/2009 12:33:59 PM Karen said ..
Hi, I got a BIG question. I need to know if there is any way I can read an Excel file that is stored on a SharePoint server directly without down loading to a local disk. The Excel file will be stored on a SharePoint documentation site, and I'd like to know if I can use its URL as an input to either a InfoPath form or ASP.NET web site. I want to read the Excel file to convert it to a script file. If that does not work, will Excel Service on SharePoint work and how can I make it possible? Thank you in advance


On 9/21/2009 1:46:15 PM Vaibhav said ..
Great article


Is it possible to have auto refresh on excel web access accessing data from sharepoint lists?


On 10/5/2009 6:34:52 AM Joy said ..
Hi, can the publishing to a webpart work for spreadsheets where automatic refresh is enabled? My Excel data comes from a CRM database, and am looking for a way to have the data published to a webpage updated realtime. Many thanks!


On 10/6/2009 12:10:29 PM Satyen said ..
You are coool. Excel services made simple...that should be the title.


On 11/25/2009 12:38:43 AM Karthik Raja.P said ..
Thanks,

All the step i followed as per your settings. all are well, but finally excel service explore view displayed error message as "Request Registry access is not allowed", pls help any settings created before the excel service started!. pls help

Thanks,


Karthik


On 4/1/2010 8:34:21 AM lee said ..
its god,I LIKE IT BECAUSE ITS VERY USEFUL FOR ME


On 4/7/2010 3:26:02 PM sammy said ..
I have a custom list having bunch of drop down columns like stats(pending/active/close) etc. I want to show this list in consolidated view (like sum of selected columns like a dash view). Does this excel services will work in this case? please advice!


On 6/11/2010 1:09:30 PM CG said ..
Funny why the author never seems to reply to any questions, or maybe he himself has given up on Excel's services!


On 8/9/2010 7:33:34 AM mahbod said ..
hi all


I want to open the excel file in web browser but i see this message:


Excel Web Access

An error has occurred.

Please contact your system administrator if this problem persists.


On 8/23/2010 4:17:11 PM Joy Monyhan said ..
Hello,

I have a SharePoint site that is collecting information from info path forms. I would like to take the information that is in a library list and make a excel web part in the form of pie charts etc. Could you give me what you think to be the best way to do this

Thanks,


Joy


On 8/29/2010 10:42:30 PM JC said ..
i follow the above steps, when I tried to browse the workbook from my document libriary folder, I got the Excel Web Access: The trial period for Office Share Point Server has expired. I tried to search on the internet but I couldn't found the solution. Would you please help? Many thanks


On 5/16/2011 10:31:48 AM Hirwani Jain said ..
hi all

I want to open the excel file in web browser but i see this message:

Excel Web Access

An error has occurred.

Please contact your system administrator if this problem persists.


On 5/18/2011 6:15:07 AM Mukesh said ..
Hi Sahil, Excel services are available in Enterprise edition.


On 2/17/2012 3:36:29 PM Malli said ..
Hi Hirwani Jain,


I bumbed into a similar issue. It is becuase you/I dont have excel services. IMO it is not possible to view Excel sheet on a web browser without having excel services. Someone mentioned it is possible. But its just me and my experience with it.


Cheers!


Malli


On 5/3/2012 3:11:35 AM Tripathy said ..
I am unable to follow step 2. i am interest in creating this but failed to do.


Is there any thing that i need to install?


On 12/11/2012 2:58:40 PM Gerson said ..
Hi,


Can i have a Desktop Excel file and when i save that file it would be saved in a library from sharepoint server?


thx