Configuring Excel Services Share Point 2010

Developing solutions using Excel Services is pretty straightforward. We’re all familiar with Excel and what it can do, so all we really need to do is save our spreadsheet onto our SharePoint server and our mission is accomplished. (Maybe it’s not quite as simple as that, but the point I’m making is that we’re not facing a near vertical learning curve here.) Where we do need to pay a bit more attention is in the configuration of Excel Services. Left unchecked, users could upload server-crushing calculation mayhem. They could easily create a spreadsheet that dragged in the entire contents of the company customer relationship management (CRM) database and performed string matches on description fields before extracting the top ten customers and the value of their sales during the past two weeks. We could, of course, argue that this is a job for the IT pros and it’s probably fair to say that as a day-to-day task it is, but what we’ll find as we start developing solutions using Excel Services is that practically all the problems we’ll encounter come down to configuration issues.

Service Application Settings

Chapter covered the Service Application Framework and how it’s used by SharePoint to provide services that can be shared among web applications. It will probably come as no surprise to learn that Excel Services is implemented using the Service Application Framework. To configure Excel Services, we use the SharePoint 2010 Central Administration tool. From the Central Administration home page, the Excel Services configuration settings can be reached by clicking Application Management | Manage Service applications | Excel Services. Of course, if more than one instance of Excel Services is running, you’ll be able to configure that using whatever name you assigned when you set it up. When you’re configuring and using Excel Services, keep in mind the notion of trust. System administrators determine what is trusted and what isn’t, and users are allowed to create and use workbooks that make use of trusted resources only. So, for example, a system administrator may decide that data from a particular source—such as a data warehouse—is trusted, whereas data from a second source—such as a CRM application—isn’t trusted. Making this distinction doesn’t mean that the CRM system data is any less accurate than the data warehousing data; of course, the opposite is likely true. What it means is that the system administrator has determined that the schema and data volumes within the CRM system are likely to cause issues if they are used freely within Excel. By choosing not to trust this data source, users will be unable to reference it when creating workbooks. So the notion of trust is actually about trusting the resource to play nicely within Excel Services. The configuration settings for Excel Services involve five sections, as shown next. I’ll quickly run through these, calling out any settings that are relevant to developers.

Service Application Settings
Global Settings

As you might expect, this section covers most of the high-level configuration options such as load balancing and memory utilization. For the most part, these settings are unlikely to cause developers problems and are best left to the IT pros as part of maintaining and configuring the farm overall—with one exception: the external data settings. When a spreadsheet is created that connects to an external data source, you need to consider a few options for authentication. These are covered in more detail later in the section “Using Data Connection Libraries,” but for now you should know that if the authentication type is set to None, connections are made using the unattended service account. Since the unattended service account may be used to connect to many different types of data source, the Secure Store Service is used to map appropriate credentials to the account. This allows non-Windows credentials to be used where appropriate..

Trusted File Locations

The Trusted File Locations section is probably not going to cause you too many problems. In previous versions of SharePoint, this was not configured by default, so no Excel Service workbooks would load properly. Thankfully, with SharePoint 2010, the default setting is to allow all workbooks stored within SharePoint to be accessed using Excel Services. You can use workbooks that are not stored within SharePoint within Excel Services. Trusted file locations can be created for Universal Naming Convention (UNC) paths or other web sites. A few things worthy of mention include the User-Defined Functions setting and the settings in the External Data section. Although User-Defined Functions have a separate configuration heading, they can be disabled or enabled at the trusted file location level. By default, UDFs are allowed. In the External Data section, external data can be disabled at the trusted file location, and the ability to refresh external data when using the REST API is determined by a setting in this section.

Trusted Data Providers

Trusted Data Providers defines the lists of drivers that can be used when connecting to external data sources. If a driver isn’t listed, it can’t be used to create a connection. Having said that, even if a driver is listed, there’s no guarantee that it can be used.

Trusted Data Connection Libraries

Data Connection libraries serve a few functions: They allow a system administrator to create a library with a series of preconfigured connections that can be easily used by business users for various tasks. In addition, data connection information is stored in a central location, and any changes that may be required can be made globally without having to update myriad documents throughout an organization. At the Trusted File Location level, you can restrict data connections to trusted data connection libraries only. Unless this option is selected, users are free to create their own data connections using any of the trusted providers and embed those connection details within a workbook.

UserDefined Function Assemblies

In this section, you can configure any assemblies containing user-defined functions. UDF assemblies can either be loaded from a file location or from the Global Assembly Cache (GAC). Note that the assembly must be available on all machines running Excel Services. For ease of administration, an assembly can be disabled, meaning that it remains configured but can’t be used by Excel Services.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Share Point 2010 Topics