PivotData Web Service Add-In for Excel 2010
Overview
Learn more
Installation
Support
Microsoft Office Marketplace logo

Overview

PivotData Web Service is an add-in for Microsoft Excel 2010 to expose your Excel Pivot table data via WCF web service. This enable Windows, Silverlight and ASP.NET web service clients to directly interact with the Pivot table data. The web service provides methods to set filters (Excel Slicers) on Pivot table data and then retrieve the table data.

Endpoints:

The web service exposes 2 endpoints, "http" and "net.tcp".
  • "http://localhost:8181/excel" endpoint is primarily used to test/try different service operations.
  • "net.tcp://localhost:8282/excel" endpoint is used to call from Windows/Silverlight/ASP.NET clients.

Help URL (http://localhost:8181/excel/help)

Service operations at this endpoint.
  • ExcelTest = http://localhost:8181/excel/ExcelTest?name={NAME}
  • ExcelInfo = http://localhost:8181/excel/ExcelInfo?name={NAME}
  • GetSlicerItems = http://localhost:8181/excel/GetSlicerItems?name={NAME}
  • SetSlicerFilter = http://localhost:8181/excel/SetSlicerFilter?name={NAME}&filter={FILTER}
  • GetPivotTable = http://localhost:8181/excel/GetPivotTable?sheetname={SHEETNAME}&tablename={TABLENAME}&delimiter={DELIMITER}

Service Operation: ExcelTest

This method is used to test if the WCF service is started and responding.

Service Operation: ExcelInfo

This method retrieves Excel workbook information like Pivot table names, Slicer names.

Service Operation: GetSlicerItems

This method retrieves Slicer items (filter items) for the given Slicer name.

Service Operation: SetSlicerFilter

This method sets the Slicer filter, returns true if successful.

Service Operation: GetPivotTable

This method retrieves Pivot table data (Slicer filtered data).

Limitations:

  • PivotData WCF service is a singleton service instance with "ServiceThrottlingBehavior.MaxConcurrentSessions = 1".
  • The service can process only one request at a time. "http" endpoint is only for testing and demonstration purpose.
  • Always use "net.tcp" endpoint for connecting, open channel, set filters, retrieve data, close channel.