Introduction to Excel Services

Microsoft and other software vendors offer a dizzying array of BI, business productivity, and data management tools from powerful relational databases on up. Yet research (and personal experience) has shown that a large number of corporations still maintain a significant quantity of business logic in plain-old Excel spreadsheets . Logic stored in spreadsheets can lead to a proliferation of cut-and-paste in an attempt at reuse that can become a maintenance nightmare. Responding to the needs of its customers, Microsoft introduced Excel Services for SharePoint Server 2007.

Basically, Excel Services is a server-side version of the Excel calculation engine. Some common usage scenarios to Excel Services are:

1. A set of SharePoint web parts. This allows anyone with access to a SharePoint site to interact with Excel Data stored on a server without necessarily having full control to modify the data or change how it is presented. A Business Intelligence Dashboard showing KPIs (Key Performance Indicators) would be a common application.
2. An interactive spreadsheet viewer. Even if a customer has Excel installed on their workstation, this allows the user to view up-to-date Excel data without having to leave their browser when accessing their corporate SharePoint site
3. Maintaining “one version of the truth” of data (such as quarterly sales, etc.) on a server without having to worry about changes to multiple copies floating around individual users desktops.
4. A set of APIs for developers to leverage the calculation abilities of Excel from within their own applications.

Architecturally, Excel Services is also divided into three components:

1. Excel Calculation Services (ECS) – handles the spreadsheet refresh and recalculation
2. Excel Web Access (EWA) – handles the rendering of the spreadsheet to the client, e.g. SharePoint site
3. Excel Web Services (EWS) – allows access to the Excel calculation engine via web service calls.

Alas, Excel Services does not provide you with 100% of the full power of Excel to your SharePoint site; limitations include lack of support for VBA Macros, references to other Excel workbooks, ActiveX controls, and most graphical elements.

Excel Services may not be the most glamorous technology, and on the surface might seem like a “hack” – using a lowly spreadsheet to manage your important business data, and to provide the calculation engine for custom applications that might be better served by interfacing with a commercial or homegrown software library. However, in the real world of legacy systems and shrinking budgets, Excel Services has its place. In future blog entries, I will be diving in and exploring the different components of Excel Services in greater detail.