common scenario in many companies, particularly those that create financial or other analytical applications is one where domain experts (for example financial analysts) model calculations they would like the application to perform using Excel. This model forms a proof-of-concept, as well as a sandbox in which domain experts ensure that their calculations work correctly. The model may also become the basis for a functional specification for programmers, who can take the spreadsheet and implement the formulas it contains in a high level language such as C# or Java in the main application.
In many cases this isn't really necessarya dedicated server application to crunch the numbers may not be the best solutionbut distributing and securing a spreadsheet, as well as managing its deployment and versioning causes so much overhead that it is often worth spending time and effort to build a hosted application that performs the same calculation.
However with the advent of SharePoint 2007, which facilitates communication between its users, and with Excel Services, application developers can approach this scenario in a new and different way. Excel Services provides a server-side paradigm for Excel in which spreadsheets published to SharePoint can calculate and render on the server, letting users pull results down to their browsers. In this new scenario, analysts can publish their worksheets to SharePoint, and allow Excel Services to kick in, meaning that anybody who browses to the site containing the spreadsheet can access the full functionality of the sheetwhether or not they have Excel installed. In this scenario, the formulae that analysts enter become the code that everyone will run. You don't need a developer to reimplement the formulae or create a new application.
Creating and Configuring SharePoint for Excel Services
This article assumes that you have SharePoint 2007 server installed. The software is currently in beta, and you can download it from MSDN. When the installation completes, you'll see an icon under "Administrative Tools" called "SharePoint 3.0 Central Administration." Use this to open the SharePoint Administrator. From the Administrator you can use the "Create Site Collection" option on the "Application Management" tab to create a new SharePoint site.
|Figure 1. Creating a New SharePoint Site: From the SharePoint Administrator window, select the "Blank Template" option to create a new blank SharePoint site. Table 1 shows the settings you'll need.|
Give the new site a name, and use the "Blank Site" template to keep things simple. See Figure 1
Table 1. Blank SharePoint Site Settings: The table shows
the settings you'll need to create a blank SharePoint site to host
the sample Excel Services spreadsheet.
||Use this value
||"<User name of local administrator>"
When you've set all the values as shown in Table 1, click "OK" to create your site. If you browse to the blank SharePoint site that you just created it will look like Figure 2
|Figure 2. Your New SharePoint Site: Here's how the new blank SharePoint site looks in a browser.||
|Figure 3. Creating New Items in your SharePoint Site: The Create Page screen provides several categories containing links for creating various item types.||
|Figure 4. Your Document Library in SharePoint: The new "Analytics" document library can contain a set of Excel spreadsheets.||
At the top right of the screen you can see the "Site Actions" dropdown list. Select "Create" from the dropdown and you'll be taken to a screen containing several categories of options for creating various types of items (see Figure 3
Select "Document Library" from this screen to create a new Document Library. On the Document Library screen, give the new document library a name and a Document Template. Choose any name you like (the examples in this article use "Analytics," and select "Microsoft Office Excel Worksheet" as the document template).
You'll now have a SharePoint site containing a document library of Excel spreadsheets (see Figure 4).