Microsoft SharePoint 2007 Technologies

164 8.1 Excel Services

8.1 Excel Services

Excel Services is a server technology that ships in SharePoint Server 2007 and

enables the sharing, controlling, and reusing of Excel 2007 workbooks on

centrally managed servers. Server-based workbooks are accessible via brows-

ers and therefore consumers of these workbooks do not even need to have

Excel 2007 installed locally. Note that the authoring of workbooks does

require the Excel 2007 client, but the subsequent reuse of them does not.

Workbooks can be stored in UNC file shares, HTTP servers, or Share-

Point libraries, and in the last case they benefit from all the inbuilt goodness

of SharePoint, such as security, auditing, workflow, versioning, and so on.

Once workbooks are stored on the server, the data within them can be

exposed through multiple vehicles; for example, a single cell from within a

workbook can be used as the data to measure a key performance indicator

against, or a chart can be displayed within a dashboard page on a SharePoint

site. This is where the real power of Excel Services comes into play: unlocking

and unleashing business logic and business data so that others can use it to

make informed business decisions in a controlled and safe environment.

Sharing Excel workbooks today usually involves sending out multiple

copies attached to e-mail or placing a copy on a central file server. Each inter-

ested party then takes a private copy of the workbook and uses their local

computing power to analyze the data—but in isolation from the other folks.

Not only do you multiply the actual computing power required, you lose

control over who has what and end up with multiple versions of the “truth.”

With Excel Services, a single calculation engine on the server computes all

the functions and prepares the workbook for consumption. Each user then

has his or her own view into the workbook for the purpose at hand. This is a

much better way of ensuring that you have a single version of the truth, and

makes it far more efficient use of computing power by having long-running

calculations run on the server, rather than on each individual’s PC.

There are three core parts to Excel Services: a calculation engine that

runs in tier 2 of a SharePoint architecture, Excel Web Access, which enables

the browser based access to workbooks, and Excel Web Services which allows

other applications to consume workbooks. The last of these is very powerful,

as you can control your business logic using Excel but present the data in any

shape or form you like. Consider a simple mortgage calculation engine in an

Excel workbook. If the interest rate changes, all you need do is to change it in

the source workbook on the server, and every application using that work-

book will reflect the change. No longer do you need to code the business

logic into each and every application.

Additionally, there are some great changes in Excel 2007 itself for those

who author workbooks in the first place. For example, much larger work-

books can be created, with up to one million rows and sixteen thousand col-