Ten Tips for Using SharePoint Server 2007 with Excel Services
Summary: Learn ten tips to improve your use of SharePoint Server 2007 with Excel Services from the SharePoint - Excel Services forum. (15 printed pages)
Joel Krist, Akona Consulting
May 2008
Applies to: Microsoft Office SharePoint Server 2007, Microsoft Office Excel 2007, Excel Services
Contents
Overview
Making an Excel Workbook Compatible with Excel Services
Configuring External Data Connections
Configuring Excel Services to Work with Kerberos
Saving an Excel Workbook to a SharePoint Document Library Programmatically
Avoiding Exceptions in a UDF When Accessing the Caller's Identity
Handling Dimension Issues for UDFs That Return Arrays
Calling 32-Bit Native DLLs from UDFs on 64-Bit Operating Systems
Quick Tips for Common Excel Services Issues
Conclusion
Additional Resources
Overview
This article gleans community-contributed information from the SharePoint - Excel Services forum on the Microsoft Developer Network (MSDN). It provides tips for and answers to frequently asked questions for using Microsoft Office SharePoint Server 2007 with Microsoft Office Excel 2007 and Excel Services.
Making an Excel Workbook Compatible with Excel Services
Excel Services is a secure, scalable, server-side calculation service that has the ability to load, calculate, and render Excel worksheets. Excel Services is more than the Excel client running on the server.
Note
Excel Services is developed to support some, but not all, of the very large feature set that is available with the Excel client. Certain features, such as Microsoft Visual Basic for Applications (VBA), are not safe to run on the server. In addition, some features, such as Shared Workbooks, are not designed to be run on the server
An Excel workbook that contains features that are incompatible with Excel Services can fail to work as expected. Symptoms can range from a failure to load, to loading with reduced functionality. For a good review of unsupported features, see Unsupported Features in Excel Services.
So how can you tell if you are using unsupported features in your workbook? One obvious way is that the workbook fails to load in Excel Services. Figure 1 shows the error message that Excel Services displays when you try to load an Excel workbook that contains a link to another workbook (an external reference).
Figure 1. Unable to Load Workbook error
.jpg "Unable to Load Workbook error")
To prevent users of your workbook from receiving error messages and then contacting you, you can use the Excel Services Compatibility Checker Addin - Beta. This add-in, available for download from the blog of Shahar Prish, lets you check your workbook for compatibility before you upload it to your SharePoint site.
Configuring External Data Connections
Excel workbooks can reference data from external sources. Some examples of external data sources are Microsoft SQL Server and Microsoft SQL Server Analysis Services. Before Excel can work with an external data source, it needs to know how to connect to it. This connection information can be stored in the workbook as an embedded connection and in an external data connection file as a linked connection. Linked connection files can be centrally stored, managed, and reused.
Excel Services must be configured correctly to enable Excel workbooks that use external data to load and refresh successfully. If Excel Services encounters a problem when trying to connect to an external data source to perform a refresh, it displays an error message such as the one shown in Figure 2.
Figure 2. Data Refresh Failed error
.jpg "Data Refresh Failed error")
For more information about configuring external data connections with Excel Services, see Plan External Data Connections for Excel Services (written by John Campbell, a program manager for Excel Services).
One way to create a data connection file is to use the Excel Data Connection Wizard. The wizard lets you specify the type of authentication to use when workbooks that are using the connection are opened in Excel Services. Figure 3 shows the Excel Services Authentication Settings dialog box that is displayed by the Excel Data Connection Wizard.
Figure 3. Excel Services Authentication Settings dialog box
.jpg "Excel Services Authentication Settings dialog box")
From the figure you can see that your authentication choices are Windows Authentication, SSO (Single Sign On), and None. Following are some general tips for using these options to configure Excel Services, and for troubleshooting external data refresh problems.
Windows Authentication Option
In Windows authentication mode, Excel Services attempts to use the Windows identity of the user who is logged on when authenticating with the external data source. Be aware of the following when choosing this mode:
Ensure Excel Services is configured to use the Delegation access model rather than the Trusted Subsystem model. You must set this configuration even if Excel Services and the back-end data source are running on the same server. To configure Excel Services to use the Delegation access model, use the Stsadm command-line tool. Type the following commands at a command prompt on the server, replacing sspname with the name of your Shared Services Provider (SSP):
stsadm -o set-ecssecurity -accessmodel delegation -sspsspnamestsadm -o execsvcadmjobsiisresetIf the external data source and Excel Services are running on separate servers, set up Kerberos delegation. You must do this to enable Excel Services to delegate the identity of the user who is viewing the workbook through the server tiers. For information about how to do this, see Configuring Excel Services to Work with Kerberos later in this article.
None Option
Selecting None for the authentication mode in the Excel Services Authentication Settings dialog box causes Excel Services to assume that the connection string contains the user name and password needed to authenticate with the data source. If the connection string requires the use of integrated security, the Excel Services Unattended Account is used for authentication. Ensure the following:
You have configured the Unattended Account. Excel Services impersonates the Unattended Account when it tries to authenticate with a data source by using either no authentication setting (None), or single sign-on (SSO) when the SSO application is not storing Windows account credentials.
You can log on locally with the Excel Services Unattended Account on the application servers.
SSO Option
The SSO service uses a centralized database to store authentication credentials. Excel Services uses the SSO application ID that is specified for the data connection to request the credentials when authenticating with the data source. When you are using SSO, and want to determine the cause of data refresh failures, you can try the following to troubleshoot:
Open the event log on the Excel Services application server and search for events with a source of Office SharePoint Server and a category of SSO. The table of single sign-on error codes provided in the ...Me Unplugged... blog post contains descriptions of some of the SSO error codes.
If Excel Services is configured to use the Trusted Subsystem access model and the error code is -2140995579, then verify that the SSP Application Pool identity which the Excel Services application server is running under is configured as an SSO Administrator.
If the SSO Application (identified by the SSO ID) stores database credentials, ensure you configured the Excel Services Unattended Account (see above).
Verify that the SSO service is configured through SharePoint Central Administration.
Verify that the SSO Windows service is started on the SharePoint front-end Web server and application servers.
Verify that the SSO application exists in the SSO database.
Verify that the user who is refreshing the connection has access to the SSO credentials.
For a walkthrough of how to configure SSO on SharePoint Server, see Configure Single Sign-On (Office SharePoint Server) on Microsoft TechNet.
Configuring Excel Services to Work with Kerberos
In a typical enterprise environment, Excel Services is installed as part of a SharePoint farm and runs on a server separate from the SharePoint front-end Web server. All data sources will run on different servers. You must correctly configure Kerberos authentication to allow the trusted and secure delegation of user identities in this type of a configuration.
The following resources provide useful information on how to configure Kerberos for SharePoint Server and Excel Services, and on how to troubleshoot Kerberos problems:
Configuring Kerberos for SharePoint 2007: Part 1 - Base Configuration for SharePoint, a two-part article on Martin Kearn's blog, presents step-by-step guidance on configuring SharePoint Server and Excel Services to work with Kerberos.
Essential Tips On Kerberos for SharePoint Deployers, an entry on The James World blog, provides information about configuring Kerberos in a SharePoint environment.
Troubleshooting Kerberos Errorsa white paper on Microsoft TechNet, provides information that can help you troubleshoot Kerberos authentication problems.
Saving an Excel Workbook to a SharePoint Document Library Programmatically
The Excel Web Access Web Part and Excel Web Services do not provide a direct way to create, modify, or save an Excel workbook to Office SharePoint Server. However, you can learn ways to programmatically upload a workbook to a SharePoint document library.
The following example code shows a simple console application that uses the HTTP PUT method to upload an Excel workbook to a SharePoint document library. The code opens a file stream on the local file, and then writes the contents of the local file into the request stream.
using System;
using System.Net;
using System.IO;
namespace UploadFile
{
class Program
{
static void Main(string[] args)
{
try
{
// LocalFile represents the path to the local file to upload.
// Replace LocalFile with your own value, for example,
// c:\workbook.xlsx.
string localFile = LocalFile;
// SharePointFile represents the path and file name to upload the local file to.
// Replace SharePointFile with your own value, for example,
// http://moss.litwareinc.com/Documents/Workbook.xlsx.
string sharepointFile = SharePointFile;
// Create a PUT Web request to upload the file.
WebRequest request =
WebRequest.Create(sharepointFile);
request.Credentials =
CredentialCache.DefaultCredentials;
request.Method = "PUT";
// Allocate a 1K buffer to transfer the file contents.
byte[] buffer = new byte[1024];
// Write the contents of the local file to the request
// stream.
using (Stream stream = request.GetRequestStream())
using (FileStream fsWorkbook = File.Open(localFile,
FileMode.Open, FileAccess.Read))
for (int i =
fsWorkbook.Read(buffer, 0, buffer.Length);
i > 0;
i = fsWorkbook.Read(buffer, 0, buffer.Length))
stream.Write(buffer, 0, i);
// Make the PUT request.
WebResponse response = request.GetResponse();
response.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception: {0}", ex.Message);
}
}
}
}
Avoiding Exceptions in a UDF When Accessing the Caller's Identity
Setting the ReturnsPersonalInformation property of the UdfMethod method attribute to true tells Excel Services to make the identity of the user who is calling a user-defined function (UDF) available via the System.Threading.Thread.CurrentPrincipal.Identity interface. If Excel Services is configured to use the Trusted Subsystem model and the UDF attempts to access the calling user's identity, an exception similar to the following is thrown: Unable to cast object of type 'Microsoft.Office.Excel.Server.CalculationServer.WorkOnBehalfIdentity' to type 'System.Security.Principal.WindowsIdentity'.
The problem here is that to have a Windows identity, Excel Services must be configured to use the Delegation access model.
To configure Excel Services to use the Delegation access model, use the Stsadm command-line tool. Type the following commands at a command prompt on the server, replacing sspname with the name of your SSP:
stsadm -o set-ecssecurity -accessmodel delegation -ssp sspname
stsadm -o execsvcadmjobs
iisreset
In addition, if SharePoint Server and Excel Services are running on different servers, you must use Kerberos authentication. For pointers on how to do this, see Configuring Excel Services to Work with Kerberos earlier in this article.
Handling Dimension Issues for UDFs That Return Arrays
Array functions in Excel must use a predetermined size, requiring users to specify the maximum possible size for the array so that all rows returned by the UDF will be visible. However, it's not always possible to know what the maximum size should be. This problem is compounded when the number of rows returned by the UDF is less than the height of the range; in this case, Excel displays #N/A for the cells in those rows.
One possible solution to the problem is to create a pseudo query table (pseudo because real query tables are not supported in this version of Excel Services). The pseudo query table can reference the array formula indirectly to display the results returned by the UDF, and can convert the #N/A seen in empty array formula cells to empty strings. For sample workbooks that use this technique, see Extending the Excel Services Programmability Framework, and Luis Bitencourt-Emilio's (LuisBE) blog entry Using UDFs and Excel Formulas to Get a Query Table on Excel Services.
Another solution is to implement paging support in the UDF. With this approach, the UDF is passed a page size and page number. The UDF returns an array that contains the requested number of rows, with empty rows padded with blanks so Excel does not display #N/A. The following code is a modified version of the getSharepointView UDF provided with the SharePoint Server 2007 Sample: Extending Excel Services Programmability Framework Samples. This version accepts page number and page size parameters, sizes the returned array appropriately, and pads empty rows with blanks.
/// <summary>
/// Using the SharePoint API, read data from a
/// SharePoint list and return it as an object array.
/// </summary>
/// <param name="serverName">Server name.</param>
/// <param name="siteName">SharePoint site name.</param>
/// <param name="listName">SharePoint list name.</param>
/// <param name="viewName">SharePoint list view name.</param>
/// <param name="getTitles">Include column titles.</param>
/// <param name="pageNumber">The page index.</param>
/// <param name="pageSize">The number of rows per page.</param>
/// <returns>Two-dimensional object array of list values.</returns>
[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object[,] getSharepointViewWithPaging(string serverName,
string siteName, string listName, string viewName, bool getTitles,
int pageNumber, int pageSize)
{
object[,] toExcel = null;
// Impersonate the current user viewing Excel Web Access.
using (WindowsImpersonationContext wiContext = impersonateUser())
{
SPSite site = null;
SPList list = null;
SPView view = null;
SPListItemCollection values = null;
SPField field = null;
try
{
// Get SharePoint objects.
site = new SPSite(serverName);
list = site.AllWebs[siteName].Lists[listName];
view = list.Views[viewName];
// Get collection of SharePoint list values.
values = list.GetItems(new SPQuery(view));
// Add a row for the titles, if requested.
int titleRow = getTitles ? 1 : 0;
int totalRows = pageSize + titleRow;
// Avoid two-dimensional arrays with only one dimension
// of data by adding an empty row, if needed.
if (totalRows == 1)
totalRows++;
// Create an object array to return to Excel.
toExcel = new object[totalRows, view.ViewFields.Count];
// Add column titles to array, if needed.
if (getTitles)
{
for (int i = 0; i < view.ViewFields.Count; i++)
{
toExcel[0, i] = list.Fields.GetFieldByInternalName(
view.ViewFields[i]).Title;
}
}
int j = titleRow;
// Decrement the provided 1-based page number to make it
// 0-based to work with the 0-based items list
// that is returned from SharePoint Server.
pageNumber--;
// Calculate the starting and ending item indexes based on
// the requested page number and page size.
int startItemIndex = pageNumber * pageSize;
int endItemIndex = startItemIndex + pageSize;
for (int itemIndex = startItemIndex;
itemIndex < endItemIndex; itemIndex++)
{
// Add every field value of the current item to the array.
for (int i = 0; i < view.ViewFields.Count; i++)
{
// If we've used up all of the returned list
//items...
if (j > values.Count)
{
// ...then pad the remaining array elements
// with empty strings.
toExcel[j, i] = string.Empty;
}
else
{
// Get the current field.
field = values[itemIndex].Fields.
GetFieldByInternalName(view.ViewFields[i]);
// Get the field value as text string.
toExcel[j, i] = field.GetFieldValueAsText(
values[itemIndex][field.Id]);
}
}
j++;
}
}
catch (System.Exception ex)
{
object[,] error = new object[1, 1];
error[0, 0] = "An error has occurred.";
toExcel = error;
}
finally
{
// Dispose of SharePoint objects instead of relying
// on the common language runtime.
if (site != null)
site.Dispose();
}
} // End impersonation.
return toExcel;
}
Calling 32-Bit Native DLLs from UDFs on 64-Bit Operating Systems
Let's consider a scenario in which you have created a managed code UDF assembly that acts as a wrapper for some existing, unmanaged 32-bit DLLs. In this scenario, the UDF works well on the 32-bit version of Excel Services, but fails when you try to use it on the 64-bit version.
Essentially, it's not possible to load the 32-bit native DLLs into the 64-bit Excel Services process. For a good description of what's happening and why, take a look at the UDFs in the World of 64bit Excel Services Installations blog entry by Shahar Prish.
You can work around this issue in three ways:
If you have the source code for the native DLLs and the required expertise, you can rebuild them as 64-bit native DLLs.
If you have the source code and expertise, you can rewrite the native DLLs by using managed code.
You can create a solution whereby you create a 32-bit surrogate process that loads the 32-bit native DLLs. The managed code UDF assembly would load the 32-bit native process and use it to communicate with the 32-bit native DLLs.
Quick Tips for Common Excel Services Issues
The following three tips provide quick answers to some common Excel Services issues and questions.
Remember to enable UDFs on trusted file locations. The option to do this is located at the very bottom of the Excel Services Edit Trusted File Location page and is easy to miss. You can tell if you might have this problem if Excel Web Access displays #NAME in cells that reference the UDF as opposed to #VALUE, which is what is displayed if the UDF is being called but is not working.
Figure 4. Allow UDFs from trusted location
.jpg "Allow UDFs from trusted location")
Check the Excel Services Trusted File Locations and make sure the location the file is being opened from is trusted.
Attempting to use the Excel Web Services API to open a workbook from a location that is not specified as a trusted file location causes a SOAP exception and generates the following message: You do not have permissions to open this file on Excel Services.
You can use the Excel Services API to perform calculations and work with Excel workbooks without having to install the Excel client.
Conclusion
This article provides answers to some of the frequently asked questions posted in the SharePoint - Excel Services forum on MSDN. It also provides tips and guidance to help solve problems related to using and configuring Excel Services, and points to other resources for more information.
Additional Resources
For more information about using SharePoint Server 2007 with Excel Services, see the following resources: