Using SharePoint lists as data sources with Excel Services (SharePoint Server 2010)
Published: January 20, 2011
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.
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 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 (http://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 (http://go.microsoft.com/fwlink/p/?LinkId=209145). Also, see Understanding Excel Services UDFs (http://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
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 (http://go.microsoft.com/fwlink/p/?LinkId=209137) site:
How to: Refresh Data (http://go.microsoft.com/fwlink/p/?LinkId=209139)
Walkthrough: Developing a Custom Application Using Excel Web Services (http://go.microsoft.com/fwlink/p/?LinkId=209140)
Accessing the SOAP API (http://go.microsoft.com/fwlink/p/?LinkId=209142)