Today I am going to walk you through the process of configuring Excel Services in MOSS 2007.  This will walk you through the steps from configuring your server all the way through publishing and displaying an Excel workbook in the Excel Web Access web part.

Background:

Excel services is a Microsoft Office SharePoint Server 2007 (MOSS) feature that can be very effective for a company who is looking to share, secure, and manage Excel 2007 workbooks. 

What is Excel Services composed of? 

Taking straight from the Office 2007 online Beta site excel services is composed of three basic components: 

1. Excel Calculation Services (ECS) is the "engine" of Excel Services that loads and calculates the workbook, refreshes external data, and maintains sessions.

2. Excel Web Access (EWA) is a Web Part that displays the Microsoft Office Excel workbook in a browser by using Dynamic Hierarchical Tag Markup Language (DHTML), and can be connected to other Web Parts on dashboards and other Web Part Pages.

3. Excel Web Services (EWS) is a Web service hosted in Microsoft Office SharePoint Services that provides several methods that a developer can use as an application programming interface (API) to build custom applications based on the Excel workbook.

What is Office Excel Web Access?

Excel Web Access is a Web Part that displays data and charts from an Excel Workbook, has a similar "look and feel" to Microsoft Office Excel, and provides a number of ways to customize the Web Part.

1. The top section contains the toolbar which has several menus, commands, and drop-down lists, and navigation buttons for scrolling by page.

2. The main window displays one or more worksheets in Normal view, an item, such as a chart or an Excel table in Item view, and optionally an outline area.

3. The bottom section contains a status bar that displays alerts and prompts, such as refresh data messages.

4. The Parameter Task Pane has parameter labels, text boxes for data entry, and optional tool tips that provide more information about each parameter.

The steps below assumes that you have already installed and configured MOSS 2007 including the setup of your SSP (shared service provider).  My suggestion is to check out Bill English’s step by step on installing MOSS 2007.  Once you have completed these steps you are ready to begin configuring Excel Services.

1.      Now that you have configured your MOSS 2007 installation your first step is to start the required services.  In our case the service that you must start is the Excel Calculations Services.  You enable this by going to Central Admin >>>Operations>>>Services on Server (as you can also follow along with the nice breadcrumb navigation from the screen shots below YAY breadcrumb!!).

2.       Now that we have successfully started the Excel Service on the server we must make sure that the Excel Services farm feature has been activated.  Under Operations >>>  Global Configuration  >> Manage Farm Features check to see that the Excel Services Farm Feature is activated (illustration shown below).

 

 3.  Once that we confirm that Excel Services feature has been activated we are going to have a look at some of the Excel Services settings within the SSP admin page shown below.  To get to this page under the Excel Services Settings heading click on Edit Excel Services settings. 

 

4.  Below are some of the options that you can adjust.  As you can see there is much flexibility as far as performance and scalability is concerned.  I am not going to go into detail of what each of these options are and what they do being that would be outside of the scope of this article.

 

 

5.   Next step is to Add Trusted File Location.  The page shown below can be found via:  SSP Admin Page >> Excel Settings >>  Add Trusted File Location.  As you can see you can specify a workbook outside a document library such as HTTP, and UNC (File share).  By specifying a trusted location you are giving the excel services server access to those workbooks.

6.  In the Address section you can specify a top-level site collection and check off Children trusted this will trust all child libraries and directories.  **It is interesting to note that you must add the root URL for the address.  So for instance if you are trying to enable an entire site collection and you put in http://mymoss/Pages/Default.aspx this will not work.  You must type in http://mymoss in the trusted location.   Below are the additional options that you will find for that trusted location such as workbook size and calculation behavior.  Again I am not going to go into detail on these options (that is for a whole other article).

 

Here is a quick screen shot of what my Trusted File Location page looks like after I have configured it.

7.  Now that we have added the site collection (and all child libraries/directories) we must make sure that the feature is activated within the Site Collection.  The feature you must activate (if not already) is the Office SharePoint Server Enterprise.  Go to Top-Level Settings >> Site Collection Administration>> Site Collection Features.

 

Since we activated it on the site collection level it should be activated on the site level as well.  You may want to check through going to Site Settings on the site you want to activate>> Site Features.  In turn this is a good method if you don’t want to enable the entire site collection and just individually enable on a per site basis.

8.  Once we have enabled the features we can now provision the Excel Web Access web part to display our workbook, worksheet, chart etc..  Open up the page in Edit mode and click on Add a Web Part.  Scroll down the list until you see the Excel Web Access web part and check it off and click ADD.

 

9.  Now that the Excel Web Access web part  has been added to the page it is now time to publish an Excel 2007 workbook  to a document library within my trusted site collection.
**Note:  Only Excel 2007 workbooks can be published to a document library.  You CANNOT publish an Excel 2003 workbook or use the Excel 2003 client to publish a workbook to a document library. 

10.  When publishing you have the capability to control what you would like to display in the EWA web part.  You can publish entire workbooks, worksheets within a workbook, or Items in the Workbook (these have to be named ranges).  You also have the ability to publish a workbook and define parameters (named ranges).  This gives you the ability to change the values (temporarily) of cells by entering these parameters and therefore updating the results of a formula and do a simple what if type of analysis.

** I want to make it fairly clear that you CANNOT edit the cells in the Excel workbook directly from Excel Services but you can interact with the data in many ways.  Here is a few ways you can interact with the data from the Office 2007 Beta site.

·         View the latest formula results by recalculating data in the workbooks

·         Refresh live data from an external data source, such as another workbook, a database, or an Online Analytical Processing (OLAP) cube.

·         Navigate to different worksheets, parts of worksheets, or selected items (named ranges) in workbook, such as a chart or an Excel table.

·         Sort and Filter data.

·         Expand or collapse levels of data in a PivotTable report

·         Obtain different results or views by selecting data from another connected web part, such as a Filter Web part or List View Web part, on a dashboard or other web part page

 

***Quick suggestion would be to always select “Open this workbook in my browser after I save” because this will spit out any errors that you may run into before you have to go through the process of displaying in EWA.

 

 

11.  Finally we are ready to display for all to see!  In EWA click the hyperlink “Open the tool pane” this will bring you to EWA’s web part properties window.  Under  Workbook to display: you are going to add the URL to the workbook that you just published.  In the properties pane you will find a bunch of nice options that contour to this special little web part. 

 

**When attempting to display workbooks 20MB or higher with many calculations I found it very helpful (performance wise) to use the default values  for the Rows and Columns to display.  This is all depending on your hardware of course and if you have and Excel Calculations server just for these calculations scaled out.  Warning Excel calculations is VERY processor intensive and commands a lot of the processing power.  If you do end up scaling out your Excel Calculations to a standalone server to just do this job QUAD processors would be my recommendation for that machine. 

 

 

And that’s all FOLKS!!  Excel Services IMHO is great improvement over the Office Web Components (OWC) and from what I have heard OWC is not going to be continued welcome to a new era, boy was that easy!!   Some great stuff here I am open to any and all comments because I might have missed something (hopefully not! I mean I am only human right J)

 

Cheers!

Kick me if your think this post is good and worth sharing! :)