Excel Services is a component of MOSS (Microsoft Office SharePoint Server 2007) that brings the capabilities of Excel to the server side. In the past, customers and programmers that wished to add Excel calculation logic to the server needed to use ActiveX and COM to instantiate Excel’s Application object. This unfortunately was not an ideal model for a scalable server-side enterprise application and required a few tricks to make it perform and scale well. With Excel Services, much of the functionality of Excel’s client application is brought to the server and can run in the context of MOSS which is highly scalable. Some Excel features like macros and VBA code are not supported on Excel Services (see http://msdn.microsoft.com/en-us/library/ms496823.aspx). But many of these limitations can be overcome using Excel Services User-Defined Functions which can be written in VB.NET or C#.
My company, ThirdM.com, was enlisted to build a demonstration of the power and flexibility of Excel Services by Elliott Ichimura and Bob Daniels of Microsoft. In this scenario, Litware, a professional services company, needs a project planning application built on Excel to work with their customers to decide on those three important factors in every project: duration, deliverables, and fees (also known as schedule, scope, and resources). Litware, like many companies use Excel to do many business calculations and ad-hoc analysis. This is Litware’s what-if scenario for project planning:
To see the full spreadsheet click here. Litware has all of the project planning logic in Excel and does not want to reproduce this logic in C# or some other programming language, since it may change. Also, the people who need to change the business logic are not programmers.
In order to be more agile, Litware has chosen to leverage Excel Services to host the business logic. Programmers were brought in to build a flashy UI on top of the Excel logic using Silverlight 2.0 and Web Services. The Excel spreadsheet is exposed though Excel Web Services which allows the business users to update their logic without the need to change the UI.
Click here to view the full Silverlight and Excel Services demo. Click on the deliverables bar to change the deliverables. Drag on the Duration bar to change the duration of the project (make it longer and shorter). Drag the fee bar to the left to change the fee scaling which will deselect deliverables based on an prioritization logic expressing in Excel. Toggle between Extranet and Intranet view to see what Litware will see for profit and use the Rates button to offer a discounted rate which will reduce the fees but also reduce the profit.
This Silverlight demo uses Excel Services as a backend to recalculate the logic. A copy of the spreadsheet is kept alive in a session for each user. When the user clicks "Save" the session copy saves back to the SharePoint document library that hosts the business logic.