Setting Up and Understanding Excel Services
Before you can display workbooks in the browser, you must configure the ability to do so. The first step is to ensure that the Excel Calculation Services has been started. To check this setting, log in to Central Administration, click on the Operations tab, and then select Services on Server. To start the Excel Calculation Services, you need to be a SharePoint administrator. Because Excel Services is only included with the Enterprise version of MOSS, both the Office SharePoint Server Enterprise Site Collection features and Office SharePoint Server Enterprise Site features must be activated on the site collection and the site on which the user wants to display the workbook. Activating features is described in Chapter 2, “Creating Corporate Portal Sites.” In addition to activating the site features, it’s important to understand the architecture behind Excel Services and the options available to be configured in the SSP.
Excel Services Architecture
Three main components make up the Excel Services architecture.
Excel Calculation Services (ECS). ECS is the worker that loads, calculates, maintains the sessions, and refreshes the external data contained in an Excel workbook.
Excel Web Access (EWA). The EWA Web Part is the way in which the users will see the Excel workbooks in the browser and interact with them. This Web Part (available in the Web Parts Gallery) creates the HTML to render the workbook in the browser. Configuring this Web Part will be discussed in greater detail later in the chapter.
Excel Web Services (EWS). EWS is a Web service that allows developers to create custom applications based on an Excel workbook uploaded into Excel Services.
Settings in the Shared Services Provider (SSP)
The following options shown in Figure 12.3 are configured in the SSP. This may not be a location you have access to administer; however, for Excel Services to function properly, several configurations need to be made here.
Edit Excel Services Settings
This settings page contains the settings available for security, load balancing, session management, memory utilization, workbook cache, and external data. The SharePoint administrator uses this page to ensure Excel Services is secure and performs at an optimal level.
Trusted File Locations
A trusted file location is a location where Excel workbooks can be stored and are approved to be rendered in the browser. This allows the administrators to control who has permission to publish workbooks that will be rendered in the browser. Because the workbooks will be loaded by the server rather than on an individual PC, it is important to prevent workbooks that contain malicious code from being uploaded. If an Excel workbook is uploaded to a location that isn’t in the Trusted File locations list, it won’t render in the browser, and an error message will appear instead. When defining trusted file locations, you have three choices for location types: Windows SharePoint Services, Universal Naming Convention (UNC), and HTTP. In the common scenario, where the workbook will be stored in a document library, you select Windows SharePoint Services for the location type. Several options are available when defining trusted file locations, however, three are of particular interest to an information worker. The first is whether or not you want to trust children; this allows you to scope the location to a site and trust all of the libraries within the site. Secondly, you can determine whether you want to allow external data.
When you allow data connections, you can select either trusted data connection libraries only or trusted data connection libraries and embedded data connections. The final option to note is whether you want to allow user-defined functions (UDF). UDFs will be described later in the chapter.
Exercise: Configuring a Trusted File Location
|
For this example, and all of the examples throughout this chapter, you will be creating a dashboard on the home page of a sales team site. If you want to follow along with the examples in this chapter exactly, create a subsite titled “Sales” using the team site template. The URL should be http://portal/sales. Completing this exercise will be essential for other exercises to work correctly.
|
Trusted Data Connection Libraries
This list allows you to define the data connection libraries within SharePoint that you want to trust. For an Excel workbook to use a data connection within a Data Connection Library (DCL), it must be trusted. To add a DCL, on the Trusted Data Connection Libraries page, click Add Trusted Data Connection Library. The only information requested is the URL to the DCL and a description.
Trusted Data Providers
Trusted data providers are external data sources that can be used in Excel workbooks that are being rendered in the browser on this server. Additional data providers may be added, and the out-of-the-box (OOB) data providers may be deleted or edited. To add an additional trusted data provider, on the Trusted Data Provider page, click Add Trusted Data Provider. The provider ID, provider type, and description are requested. If you have SQL Server Analysis Services 2008, the data provider needed, MSOLAP.4, is not included and must be added. Figure 12.5 lists the OOB data providers.
User-Defined Function Assemblies
UDFs allow developers to create custom functions that are not already built into Excel, such as custom mathematical functions, receive data from unsupported data sources, or add additional functionality to a default functions included in Excel. If you want to use UDF assemblies in a workbook rendered through the browser, you need to add the assembly here in the SSP. Since UDFs allow you to also receive data from data sources that are unsupported, you might use a UDF to get data from a SharePoint list and display it in an Excel workbook in the browser.