SharePoint 2007: Managing Excel Services

SharePoint has always been a great intranet and portal Web solution. With the
new MOSS 2007, you also get a completely new feature called Excel
Services. 

This feature allows you to manage Excel based data through the SharePoint
portal and centrally manage the data that is input as well as output.

Let's take a sample scenario to see how Excel services can be of great
help. Consider a large organization that is spread across many different
locations. Assume that each location has a set of accountants that take care of
the salaries of the local employees of that region. These salaries are
calculated based on many different criteria like grade, years, base salary,
optional and mandatory additions or deductions, etc. All of this is done by the
accountants in Excel spreadsheets that contain cells for input of all this data
as well as complex formulae that calculate the final result.

Now, imagine that the company decides to grant a New Year bonus to all
employees. For the sake of brevity, let's assume that this is 10% of the base
salary of each employee (no difference in percentage based on grade, etc.). So
what does the company do? Typically, the CFO would send an email out to his
juniors with this information and ask them to make appropriate changes. Now
there are many implications of this. For instance, an accountant on leave may
not see this email and might calculate the salaries for at least some of the
employees in his region without the bonus. Other accounts might maliciously
ignore giving the bonus to people they may not like, etc.

So the solution to this issue is to use a centralized mechanism for this. And
this is where Excel Services comes into play.

To setup Excel Services, there are a few things that you need to do first.
First, you need to setup a site with a document library and having appropriate
permissions for your users. For instance, in the above case, you might create an
“Accounting” site with a document library called “Employee Data”.

Next, browse over to the SharePoint Central Administration and select the
server that you wish to add Excel Services to. Note that you can add the Excel
Services feature to a server if you haven't already done so. Once added and
showing up in the list, you can click the “Start” link to fire up the Excel
Services feature on that server.

Now comes the part where you need to configure the service. At the bare
minimum, you need to point Excel Services to the document libraries that Excel
Services can use and monitor. For instance, you will need to add the document
library “Employee Data” that you added earlier to this list to enable Excel
Services on it. Note that you can add UNC and other HTTP addresses as well. To
do this, go to the Application Management > Shared Services > Create or
Configure this farm's shared services > SharedServices1 (Default) >
Excel Services.

In this location, select the “Trusted Location” and click the “Add”
button. Enter the URL name of the document library you wish to add here. Since
you are adding a SharePoint Document Library, make sure that Windows SharePoint
Services is selected as the type. Check the “Enable user defined functions”
at the bottom of the page as well. This step makes Excel services available for
that library.

In SharePoint 2007, add Excel Services to a site and point it to a document library in this site. Publish your spreadsheets to this location and manage them centrally

Now comes the interesting part. Open Excel 2007 and create a new workbook
that contains all the input and output elements. For a simple case, let's
assume that the workbook has 2 inputs and 2 outputs — that is, Base Salary,
HRA as inputs and Bonus and Total Salary as outputs. Create a table that
contains the labels for this and the cells for holding this information.

Now, go to the Formulas Ribbon and click on Define name for each of these
cells. Give a unique name for each of them.

Go to the File > Publish > Excel Services menu option and put the full
URL of the Excel Services enabled document library in it. Now, before you hit
save, click the Excel Service Options button and you can start configuring it.

You can configure whether the entire workbook is accessible or only certain
sheets within it.

You can also set which are the input cells (called workbook parameters in the
dialog box) and select the worksheet from the names you created above, in our
case Basic Salary and HRA. This means that a user who opens this Excel file from
the document library will be able to see only the first sheet and have access to
input only in the two named ranges defined. Once an input occurs, Excel on the
client contacts Excel on the server and calculates the formulas and fills in the
information back on the client.

One great part of this is that if the formula needs to be changed, it can be
done simply once on the server “copy” and all the clients using that file
will automatically start using the new formula without any user intervention.

Excel Server also performs two other very important functionalities:

1. It enables the Excel file in view mode within a browser. That is, a user
   can view and browse the contents of the file without requiring any version
   of Excel client on his system.
2. It enables the formulas within the Excel file to be accessible through a
   Web service. Which means that Excel files containing complex formulas can be
   accessed from say a WinForm or even a Java application using a web service
   call.

This is beyond the scope of this article and we may revisit this feature in
greater detail in the coming months sometime.

As you can see, the Excel service of Microsoft Office SharePoint 2007 is a
very powerful and interesting new feature.

By allowing data to not just be shared, but shared with policies and
centralized management in place, enables large organizations to ensure data
consistency.