Advertisment

SharePoint 2007: Managing Excel Services

author-image
PCQ Bureau
New Update

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. 

Advertisment

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.

Advertisment
Direct Hit!
Applies To:

IT Managers



USP: Centrally manage all Excel data in your organization


Primary Link: microsoft.com


Google Keywords: MS Office SharePoint Server 2007

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

Advertisment

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.

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

Advertisment

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.

Advertisment

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

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.

Advertisment