Working with Excel Data in SharePoint 2010
Integration between Excel spreadsheets and SharePoint has been a hot topic for years, and Microsoft has provided a number
of methods to integrate the two products. This section reviews some
different basic methods of working with Excel data in SharePoint to
prepare for additional discussions around Excel Services.
To begin with, a
SharePoint list has many similarities with an Excel spreadsheet, so many
organizations simply move the content from the spreadsheet to
SharePoint by creating a list; replicating the column headings, order,
and type of content; and then cutting and pasting from the worksheet
into the SharePoint list. This is a fairly painstaking process, so many
users instead use the Import Spreadsheet template. After the user
specifies the spreadsheet to import and then selects the range of cells,
SharePoint creates a list and does its best to choose column types to
match the columns in the spreadsheet. Although not always perfect (and
the administrator or power user should verify the column settings to
make sure they do in fact match the type of content in the columns),
this is a quick way to pull Excel content into SharePoint and then allow
users to collaborate on the data.
In this situation, there is no
connectivity between the SharePoint list and the source spreadsheet. And
the SharePoint list doesn’t provide all the “bells and whistles” that
Excel provides, so the SharePoint list users might find themselves
exporting content back out to Excel using the Export to Excel tool in
the list. This process is a bit more “sticky” as a connection will be
established between the SharePoint list and Excel 2010, but it is a
one-way connection. The content in the spreadsheet can be updated by
clicking Refresh All from the Design Tab, and any changes in the
SharePoint list will be synced to the local copy of the spreadsheet.
These are certainly valid
processes and are used frequently, and Excel Services offers an
alternative that provides a different set of tools and features, which
are discussed in the following sections.
Getting to Know the Excel Services Service Application
From a high level, Excel
Calculation Services (Excel Services) is a shared service in SharePoint
2010 Enterprise that allows users to publish Excel workbooks to a
document library. Excel Services can open workbooks from SharePoint
libraries as well as from UNC paths and HTTP websites. For initial
testing purposes, it is generally recommended to start with SharePoint
library-based workbooks and then extend to other sources.
This is not meant to replace
the other means of making Excel data available in SharePoint 2010 as
outlined in the previous section, but is meant as a means of managing
and securing the workbooks and publishing content through the SharePoint
interface. For example, if Company ABC wants to make their Product
Sales Spreadsheet available to all users in the organization so that
they can input their personal sales information, Excel Services would
not be the best way to do this. Rather, the manager, who wants to
leverage SharePoint technologies, would ask users to update a SharePoint
list (such as Product Sales List) and then review the information to
ensure it was accurate. The manager would then export this content to
create a spreadsheet, add graphs for ease of analysis, and then publish
it using Excel Services.
Before the process of
publishing using Excel Services is reviewed, the Excel Services service
application will be reviewed from a high level because it is important
for the farm administrator to understand the different tools available
for configuring and managing the Excel Services service application
before opening it up to users for testing purposes.
Excel Services was
introduced in SharePoint 2007, and there are a number of new features in
the product, including the following:
Excel Services is now
a service application, and as such is more manageable and customizable
than it was in SharePoint 2007, where it was part of the shared services
provider.
Excel Services now leverages PowerShell for management instead of the stsadm tool.
REST
API: The REST API is a client/server software architecture/protocol
that uses hyperlinks and lets the user access entities (such as ranges
and charts) in workbooks using Excel Services through HTTP and also
provides a method for users to set values in these ranges, including
single cells.
JSOM
or ECMAScript (JScript or JavaScript object model): ECMAScript enables
syndication, mash-ups, automation of Excel Services, and the extension
of Excel Services by third parties. It also provides a subset of
Microsoft Excel Web Access functionality that lets an administrator or
developer insert JavaScript code on a web page to affect range
navigation, cell values, and other grid operations.
Managing the Excel Service Application
Most SharePoint
Server 2010 Enterprise installations will contain an Excel Services
service application. If a new service application is needed, it can be
created from the Manage Service Applications page on the Central
Administration site. The details of each configuration will vary based
on the needs of the organization, and the settings for the service
application are discussed here from a high level.
A number of tools allow farm administrators to manage the instance of Excel Services, as shown in Figure 1.
Each of these tools will be important for more complex Excel Services
configurations, whereas the farm administrator can most likely leave
them at their default settings for simpler implementations where the
workbooks are stored in SharePoint libraries and don’t contain
connections to external content.
The Global Settings tool
provides access to a number of settings that will be essential to have
configured properly if content not stored in SharePoint libraries is
being accessed. The configuration details will vary based on a number of
factors, such as whether there is a dedicated Excel Services front-end
server (in which case, more resources can be dedicated to the Excel
Services service application) or if Excel Services is sharing a
front-end server with numerous other services applications. Also the
location of the data being connected to will affect these settings. For
reference purposes, here are the items that can be configured:
Security settings—
File Access Method (Impersonation or Process Account), Connection
Encryption (Not Required or Required), Allow Cross Domain Access
(Yes/No). File Access Method settings have no effect when users try to
access content stored in SharePoint 2010 libraries, only when the
workbooks are stored in UNC or HTTP locations. Connection encryption
supports Secure Sockets Layer (SSL) and IPsec.
Caution
In most SharePoint 2010
deployments where front-end servers and Excel Calculation Services
application servers run on different computers, impersonation will
require Kerberos delegation.
Load-balancing options— Workbook URL, Round Robin, with Health Check or Local.
Session management— Maximum Sessions per User.
Memory utilization— Maximum Private Bytes, Memory Cache Threshold, and Maximum Unused Object Age.
Workbook cache— Location, Maximum Size of Workbook Cache, and Caching of Unused Files.
External data settings— Connection Lifetime, and ability to specify an application ID for the Unattended Service Account.
Note
The Unattended Service Account
option allows the farm administrator to specify the Application ID of a
Target Application ID that needs to be configured in the Secure Store
service application. The Target Application ID is provided with
credentials and a password, administrators are configured, and a key is
generated for it. This Target Application ID is then used as a “get
data” type of account when a workbook is loaded that contains a data
connection for the unattended account, and it is required when a
workbook connection specifies None for authentication.
Additional tools available from the Manage Excel Services Application page include the following:
Trusted File Locations—
These are the file locations that are considered “trustworthy,” and
Excel workbooks can be published to these locations. By default, the
address http:// is considered trusted along with children sites, but
this can be changed (for example, to only include the Accounting
Department’s site or other site or site collection).
Trusted Data Providers—
A number of data providers that can be used for external data sources
in Excel workbooks are already provided, and new ones can be defined
using the data provider types OLE DB, ODBC, or ODBC DSN.
Trusted Data Connection Libraries—
By default, there won’t be any trusted data connection libraries, so a
farm administrator will need to add them. First use the Data Connection
Library template to create the library, and then click Add Trusted Data
Connection Library, and then enter the URL of the library.
User Defined Function Assemblies—
The Microsoft.Office.Server.WebAnalytics.UI user-defined function
assembly (UDF) is here by default, and is used to get Web Analytics
report data.
Caution
Stsadm commands (such as
Add-ecsfiletrustedlocation, Add-ecssafedataprovider) are no longer
supported against Excel Services in SharePoint 2010. Fortunately, an
error will be displayed in the command prompt if an administrator tries
to run one of these commands. The error states: “Error. This stsadm
command is no longer supported. Use PowerShell to modify Excel Services
Application settings from the command line.” The cmdlets are listed in
full on TechNet: http://technet.microsoft.com/en-us/library/ee906545.aspx.
