Excel Services is installed as part of SharePoint Server 2007 but is not enabled by default. To make use of Excel Services, a few additional steps are required to configure it in an SharePoint Server 2007 installation.
To configure Excel Services, you must first install an instance of SharePoint Server 2007 in either the Complete or Web Server mode. Once it is installed, you need to create a Shared Services Provider (SSP). Excel Services is a shared service that is available only from an SSP. There are two parts to configuring Excel Services. First you need to enable Excel Services and then you need to configure a trusted connection.
|More Info|| |
For details on configuring an SSP, see Chapter 18, "Administrating Shared Services Providers."
Excel Services are not enabled in a default installation of SharePoint Server 2007, so the first step is to enable the service on at least one server in the farm. In a server farm with one Web front-end server and one application server, you can enable the service on either server with the objective of using the server with the least load on it currently. If you have multiple Web front-end servers, best practice is to enable Excel Services on a separate application server that is available to all Web front-end servers. You can enable Excel Services on multiple servers in the farm to enhance redundancy and scalability. For instructions on how to do this, see the section "Scaling Excel Services." To enable Excel Services, follow these steps:
Open SharePoint 3.0 Central Administration.
Click the Operations tab.
Click Services On Server.
If the Status of Excel Calculation Services is Stopped, click the Start link to the right.
Excel Services can only process data in workbooks that are stored in specifically authorized locations, known as Trusted File Locations. A Trusted File Location can be either a Windows SharePoint Services document library, a URL to an Excel file, or a path to a file in a shared folder. Controlling which locations Excel Services will recognize and render data from allows administrators to control who has permission to both publish and view Excel workbooks through Excel Services. For Windows SharePoint Services sites, you must create a new Trusted File Location for each document library by completing the following steps:
In Central Administration, on the left menu bar, under Shared Services Administration, click the link for the Shared Services Provider you are using for your Web application.
Under Excel Services Management, click Manage Trusted File Locations.
Click Add Trusted File Location.
On the Add Trusted File Location page, type the URL of the file location as follows (see Figure 20-1):
For Windows SharePoint Services Type the full URL to a specific document library, for example: http://mossserver1/sites/wsssite/doclib.
For UNC Type the path to an Excel file stored in a shared folder, for example: \\server1\sharedfolder.
For HTTP Type the HTTP address to an Excel file stored on a Web site, for example: http://webserver1/virtualdir.
Selecting the HTTP location type when referring to a document library, or vice versa, will cause the Excel Calculation Services request to fail. When loading a workbook from a Windows SharePoint Services site, permission checks are handled by impersonating the user account making the request, which cannot be done for UNC shares or HTTP Web sites.
If the location you entered contains subfolders, they will not be trusted automatically. To specify that subfolders also be trusted, select the Children Trusted check box.
Under the External Data section, select the Allow External Data option if the spreadsheets you will be publishing have links to External data sources.
External data sources include queries to databases through Office Data Connection (ODC) connections, which are supported by Excel Services only if this option is selected. Unless you select this option, you won't be able to use these data sources.
Click OK to add the Trusted File Location.
Figure 20-1: Add Trusted File Location
Real World Planning Trusted File Locations
When planning your Excel Services architecture, you need to decide how many Trusted File Locations to create within your farm. This is a complex question that can involve several possible approaches. Let's take a look at two possible approaches adopted by fictional companies.
A Small Organization
An organization with 300 users decided that their information needs were fairly specific and only a few users would be editing and published workbooks to the server. They concluded that they would need only one Trusted File Location for their intranet portal and sites. The decision was based partly on their interest in including the address to the Trusted File Location library in their training materials so that everyone who needed the feature would know where to go.
A Large Organization
An organization with several thousand users examined the same problem and came up with a different strategy. They realized that they would have several groups using Excel Services for different purposes and that that one size would not fit all. One department in the organization wanted to be able to put all its workbooks on the server and view any of them through the Web browser. For this group, a document library was configured so that everyone in the department had Edit permissions, but the maximum workbook size that Excel Calculation Services would process was set to 7 MB to reduce the overhead on the server. A different department had a limited set of relatively complex workbooks that generated cost projections for the department quarterly. At present, the department tracks large amounts of data in these workbooks, but it is considering moving the data into a back-end database and performing some of the calculations using User Defined Functions. For this group, a document library was configured with only a few users with Edit permissions, but which allowed workbooks up to 20 MB and allowed connections in trusted data connection libraries and user-defined functions.