Using SharePoint lists as data sources with Excel Services (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010

If you have ever tried to use Microsoft SharePoint Server 2010 lists as a data source for Excel Services in SharePoint, you know that it does not work. Natively, Excel Services in Microsoft SharePoint Server 2010 is unable to consume SharePoint Server 2010 lists as data sources. However, there are ways to work around this situation.

In this article:

  • Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel 2010

  • User-defined functions (UDFs)

  • Web Services API

  • Java Script Object Model (JSOM)

PowerPivot for Excel

PowerPivot for Excel is an add-in to Microsoft Excel 2010 that provides tools that can help consume SharePoint Server 2010 lists. For more information, see PowerPivot for Excel in SQL Server Books Online.

With PowerPivot for Excel you can use the Export as Data Feed command in a SharePoint Server 2010 list and then save this as a data feed to a new or existing PowerPivot for Excel workbook. You can then publish this PowerPivot for Excel workbook to SharePoint Server 2010 by using Excel Services.

Important

You must install ADO.NET Data Services Update for the .NET Framework 3.5 SP 1. You can download it by using one of the following links:

User-defined functions

User-defined functions extend the Microsoft Excel 2010 and Excel Services functionalities to work with SharePoint Server 2010 lists. For information about how to use user-defined functions together with SharePoint Server lists and Excel Services, see Consuming SharePoint Lists in Excel Services (https://go.microsoft.com/fwlink/p/?LinkId=209143). Note that this blog post was published for Excel Services in Microsoft Office SharePoint Server 2007 but is still relevant for Excel Services in Microsoft SharePoint Server 2010.

For information about how to use user-defined functions, see "Consuming SharePoint Lists" in the MSDN Library article, Extending the Excel Services Programmability Framework (https://go.microsoft.com/fwlink/p/?LinkId=209145). Also, see Understanding Excel Services UDFs (https://go.microsoft.com/fwlink/p/?LinkId=209146 ) for detailed information about how to use user-defined functions with Excel Services.

Web Services API

The Web Services API can be used to push data from a database and then refresh the data in a SharePoint Server list by using Excel Services. In the Excel Web Services API methods, you can use GetRange and SetRange to select specific information from lists that you want to compare or analyze in an Excel 2010 workbook and then use Excel Services to expose it in SharePoint Server 2010. For more information, see the following articles on the Excel Developer Roadmap for Excel Services (https://go.microsoft.com/fwlink/p/?LinkId=209137) site:

JavaScript Object Model

The JavaScript Object Model for Excel Services in Microsoft SharePoint Server 2010 provides many solutions for Excel Services. By using the JavaScript Object Model, you can refresh an Excel workbook that is published as a SharePoint Server list.

Using the JavaScript Object Model, you can access complete lists or ranges and then set and retrieve values from individual cells or ranges. Additionally, the JavaScript Object Model can be used to scroll to a different region and to switch the displayed spreadsheet or named item.

For detailed information about the JavaScript Object Model, see the blog post Introducing the JavaScript Object Model for Excel Services in SharePoint 2010 (https://go.microsoft.com/fwlink/p/?LinkId=209147).