Connecting PivotTables in Excel 2007 to Data Stored in SharePoint Lists
Summary: Learn how to use the Open XML SDK 2.0 to create Microsoft Office Excel 2007 PivotTables that are connected to data stored in SharePoint lists. (18 printed pages)
Joel Krist, iSoftStone
March 2010
Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office SharePoint Server 2007, Windows SharePoint Services 3.0, Open XML SDK 2.0 for Microsoft Office
Contents
Introduction to Excel 2007 PivotTables and the Open XML SDK 2.0
Creating the SharePoint List
Exporting the SharePoint List to an Excel Web Query File
Creating an Excel Workbook and Adding a PivotTable
Exploring the PivotTable and Data Connection Code
Conclusion
Additional Resources
Watch the video that accompanies this article.
Download the code sample that accompanies this article.
Introduction to Excel 2007 PivotTables and the Open XML SDK 2.0
Microsoft Office Excel 2007 PivotTables provide a powerful way to slice data that enables you to analyze the data from multiple perspectives with only a few mouse clicks. For an overview of Excel PivotTables, see Overview of PivotTable and PivotChart Reports. PivotTables can work with data that is stored externally to Excel. External data sources can include databases, OLAP cubes, text files, and SharePoint lists.
The Open XML SDK 2.0 for Microsoft Office makes programmatic manipulation of Excel workbooks (and many other tasks) possible. Working with the Open XML SDK requires knowledge of the way Excel stores workbook content, the System.IO.Packaging API, and XML programming.
The typesafe classes that are provided with the Open XML SDK 2.0 simplify programmatically working with Microsoft Office Word 2007, Office Excel 2007, and Microsoft Office PowerPoint 2007 documents. However, creating valid documents still requires an understanding of the different document parts and their relationships. Excel PivotTables use several related document parts. This article uses the DocumentReflector tool that is provided with the Open XML SDK 2.0.
DocumentReflector can open an existing Open XML document and dynamically generate C# source code that uses the Open XML SDK 2.0 typesafe classes to create the document parts. Studying the source code created by DocumentReflector is a great way to become familiar with the Open XML SDK 2.0 classes and the Office document formats.
To show how to use the Open XML SDK 2.0 to create Excel PivotTables that are connected to data that is stored in SharePoint lists, this section describes the following steps:
Creating a SharePoint list that is used as the PivotTable data source.
Exporting the SharePoint list as an Excel Web query file.
Creating an Excel workbook based on the Web query file and adding a PivotTable that uses the data connection to the SharePoint list.
Using Microsoft Visual Studio 2008 to create a Windows console application.
Opening the worksheet that contains the PivotTable with the Open XML SDK 2.0 DocumentReflector.
Copying sections of the source code that is generated by DocumentReflector to the Visual Studio console application solution.
Examining the PivotTable and connection-related code that is generated by DocumentReflector.
Creating the SharePoint List
This article uses a simple custom SharePoint list named Sales as the PivotTable data source. The list contains three custom columns: Country, Sales, and Period. The Sales list is used for deomonstration purposes only. Any list could be used as the PivotTable data source.
Note
The figures provided with the following procedure are for a SharePoint site based on the Team Site template. The menu options available might be different, depending on the site template being used, but the basic list creation process is the same.
To create the SharePoint list
Navigate to the SharePoint site as a user who has permission to create lists.
On the SharePoint site, on the Site Actions menu, click Create.
On the Create page, in the Custom Lists section, click Custom List.
On the New page, name the custom list Sales, and then click Create.
On the All Items page, in the Settings list, select List Settings.
On the Customize Sales page, make the following changes to the columns of the Sales list:
Change the name of the Title column to Country.
Add a new column named Sales of type Currency.
Add a new column named Period of type Single line of text.
Figure 1. Adding columns to the Sales list
.gif "Adding columns to the Sales list")
Add a new item to the Sales list. This enables the rows and columns in the PivoTable to be formatted.
Figure 2. Adding data to the list
.gif "Adding data to the list")
Exporting the SharePoint List to an Excel Web Query File
To export the SharePoint list to an Excel Web query file
Navigate to the SharePoint Sales list that you created previously. On the Actions menu, select Export to Spreadsheet.
Figure 3. Export to Spreadsheet
.gif "Export to Spreadsheet")
In the File Download dialog box, click Save. In the Save As dialog box, save the Excel Web query file in the desired folder; name the file SalesData.
Creating an Excel Workbook and Adding a PivotTable
To create a PivotTable in the Excel worksheet
In Windows Explorer, navigate to the folder that contains the SalesData.iqy query file that you saved previously and double-click the file to start Excel.
If Excel dispays the Security Notice dialog box, click Enable to unblock the data connection in the file.
Excel opens a new workbook with the SharePoint list data inserted as a refreshable query table.
To add a PivotTable that uses the same data connection as the query table, on the ribbon, on the Insert tab, in the Tables group, click PivotTable.
Figure 4. Create a PivotTable
.gif "Create a PivotTable")
In the Create PivotTable dialog box, select Use an external data source, and then click Choose Connection.
In the Existing Connections dialog box, select the SalesData connection from the connections in the workbook, and then click Open.
Figure 5. Select the data connection
.gif "Select the data connection")
In the Create PivotTable dialog box, specify a location in the existing worksheet for the new PivotTable, and then click OK to insert the PivotTable.
Figure 6. Specify the PivotTable location
.gif "Specify the PivotTable location")
Remove the refreshable query table from the worksheet by deleting the rows that contain the table, leaving only the PivotTable in the worksheet.
Select a cell in the PivotTable and follow these steps:
Specify the Country field as a Row Label, the Period field as a Column Label, and the Sales field as a Value field.
Format the Value cell and the Grand Total cell in the table to use the currency format.
Center the column labels.
Change the row label to Countries and the column label to Periods. Change the Values label to Sales by Country and Period.
The result should be a PivotTable that resembles Figure 7.
Figure 7. Formatted PivotTable with data
.gif "Formatted PivotTable with data")
Navigate to the SharePoint Sales list and delete the item that you added previously.
Note
You can skip step 9 and step 10, if the result that you want is to have the data that was present in the list display in the PivotTable by default.
In the Excel worksheet, right-click the PivotTable and select Refresh. Because there are no items in the SharePoint list, the PivotTable is refreshed to contain no values. This is performed so that the DocumentReflector used in the following steps does not generate code to insert values into the PivotTable. This enables an empty table to be created and then initialized by the user, by using the current list data via a manual PivotTable Refresh operation. The result should be a PivotTable that resembles Figure 8.
Figure 8. The final PivotTable
.gif "The final PivotTable")
Save the Excel workbook with the name SalesData.xlsx.
Creating the Visual Studio 2008 Solution
This article uses a Windows console application to show how to work with the Open XML SDK 2.0 to create an Excel PivotTable that is connected to data in a SharePoint list. The decision to use a console application instead of some other application type was made only to simplify the deomonstration of the process. The same approach could be used to create other kinds of applications that work with the Open XML document formats.
The console application shown in Figure 9 uses code that is generated by the DocumentReflector tool that uses the typesafe classes that are provided with the Open XML API. The DocumentReflector tool and the Open XML API assembly are provided with the downloadable Open XML SDK 2.0.
To create the Visual Studio 2008 solution
Open Visual Studio 2008.
Create a C# Windows console application project.
Figure 9. Create the Visual Studio project
.gif "Create the Visual Studio project")
Add a reference to the Open XML API assembly:
In Visual Studio, on the Project menu, select Add Reference.
In the Add Reference dialog box, on the .NET tab, scroll down until the DocumenFormat.OpemXml component is visible, select the component. and then click OK.
Add a reference to the WindowsBase assembly:
In Visual Studio, on the Project menu, select Add Reference.
In the Add Reference dialog box, on the .NET tab, scroll down until the WindowsBase component is visible, select the component, and then click OK.
The next steps involve copying code that is generated by the DocumentReflector tool to the Visual Studio project. The DocumentReflector tool that is provided with the Open XML SDK 2.0 enables users to open a valid Open XML document, select an XML element, part, or the whole package, and have DocumentReflector generate a C# class that can create the selected document parts by using Open XML SDK 2.0 classes. The DocumentReflector tool is located in the Tools folder under the Open XML SDK 2.0 installation folder.
Use Windows Explorer to navigate to the Tools folder and double-click the DocumentReflector.exe file to start the DocumentReflector tool.
Figure 10. Launch DocumentReflector
.gif "Launch DocumentReflector")
In the DocumentReflector tool, on the File menu, click Open. In the Open dialog box, browse to the folder that contains the SalesData.xlsx workbook created previously, select the workbook, and then click Open. DocumentReflector opens the workbook and displays the content of the document. Clicking the top-level Package node displays the generated code that can be used to create the whole package.
Figure 11. DocumentReflector
.gif "DocumentReflector")
At this point, the code that is generated by DocumentReflector can be copied and pasted into the Visual Studio solution for reuse and learning purposes.
Select the using statements from the top of the DocumentReflector code window. Then copy them to the top of the Program.cs file in the Visual Studio project.
using DocumentFormat.OpenXml.Packaging; using ap = DocumentFormat.OpenXml.ExtendedProperties; using vt = DocumentFormat.OpenXml.VariantTypes; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Spreadsheet; using a = DocumentFormat.OpenXml.Drawing;Select the code for all of the methods inside the GeneratedClass class from the DocumentReflector code window, and then copy the code as methods of the Program class in the Program.cs file in the Visual Studio project.
Figure 12. Copy DocumentReflector code
.gif "Copy DocumentReflector code")
Change the copied CreatePackage method to be private static so that it can be called from the static Main method.
private static void CreatePackage(string filePath) { using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { AddParts(package); } }Modify the Main method and add a call to the CreatePackage method.
{ // In the current folder, create an Excel // workbook named PivotTableTest.xlsx. CreatePackage("PivotTableTest.xlsx"); }Build and run the sample. By usingthe code shown previously, the sample application creates an Excel workbook named PivotTableTest.xlsx, located in the Visual Studio project's Debug or Release build folder, depending on the selected build mode. Opening the workbook with Excel displays a workbook that looks exactly like the SalesData.xlsx workbook created previously. Depending on the current Excel security settings for data connections, Excel warns that the embedded data connection is disabled. To allow the PivotTable to be refreshed by using the data connection to the SharePoint list, click Options and enable the data connection. To verify that the data connection and PivotTable are working correctly, add items to the SharePoint list and refresh the PivotTable. The PivotTable displays the new data.
Exploring the PivotTable and Data Connection Code
The DocumentReflector generates lots of code to create the workbook. A large part of it is related to the themes, styles, and properties of the workbook. The code related to the PivotTable and data connection is located primarily in the following methods:
GeneratePivotTableCacheDefinitionPart1
GeneratePivotTableCacheRecordsPart1
GeneratePivotTablePart1
GenerateConnectionsPart1
PivotTables in Open XML consist of two kinds of parts: the PivotTable cache and the PivotTable definition. The cache defines the data being used in the PivotTable and the definition focuses on presenting that data in a table format.
PivotTable Cache Definition
A PivotTable cache definition is made up of two parts: the cache source, which defines how to access the data source that is used by the PivotTable cache, and the cache fields, which define the fields that reference the data in the cache source. The Open XML SDK 2.0 provides the PivotCacheDefinition class to represent PivotTable cache definition parts.The GeneratePivotTableCacheDefinitionPart1 method that is generated by the DocumentReflector tool for the SalesData.xlsx workbook allocates, initializes, and returns a PivotCacheDefiniition object. The code uses the CacheSource class to specify the data source for the cache definition and the CacheField class to define the fields that reference the data in the cache source.
private static PivotCacheDefinition
GeneratePivotTableCacheDefinitionPart1()
{
var element =
new PivotCacheDefinition(
new CacheSource() { Type = SourceValues.External,
ConnectionId = (UInt32Value)1U },
new CacheFields(
new CacheField(
new SharedItems(
new StringItem() { Val = "China",
Unused = true }
) { Count = (UInt32Value)1U }
) { Name = "Country",
NumberFormatId = (UInt32Value)0U },
new CacheField(
new SharedItems() { ContainsSemiMixedTypes = false,
ContainsString = false }
) { Name = "Sales", NumberFormatId = (UInt32Value)0U },
new CacheField(
new SharedItems(
new StringItem() { Val = "Q1-2009",
Unused = true }
) { Count = (UInt32Value)1U }
) { Name = "Period",
NumberFormatId = (UInt32Value)0U },
new CacheField(
new SharedItems() { ContainsSemiMixedTypes = false,
ContainsString = false }
) { Name = "Path",
NumberFormatId = (UInt32Value)0U },
new CacheField(
new SharedItems() { ContainsSemiMixedTypes = false,
ContainsString = false }
) { Name = "Item Type",
NumberFormatId = (UInt32Value)0U }
) { Count = (UInt32Value)5U }
) { Id = "rId1",
RefreshedBy = "Joel",
RefreshedDate = 39889.0758001157D,
CreatedVersion = 3,
RefreshedVersion = 3,
MinRefreshableVersion = 3,
RecordCount = (UInt32Value)0U };
return element;
}
The CacheSource.Type property specifies that the data source is external to the workbook and the CacheSource.ConnectionId property specifies the ID of the connection part that defines the connection.
PivotTable Cache Records
A PivotTable cache records part contains the underlying data to be aggregated by the PivotTable. It is a cache of the source data. The Open XML SDK 2.0 provides the PivotCacheRecords class to represent PivotTable cache records parts. The GeneratePivotTableCacheRecordsPart1 method that is generated by the DocumentReflector tool for the SalesData.xlsx workbook allocates, initializes, and returns a PivotCacheRecords object. Because the PivotTable in the SalesData.xlsx workbook was refreshed when the SharePoint list contained no items, the PivotTable contained no data. As a result, the code that is generated by the DocumentReflector tool for the GeneratePivotTableCacheRecordsPart1 method returns a PivotCacheRecords collection that contains no PivotCacheRecord objects. If the step of deleting the list items and refreshing the PivotTable was skipped, the PivotCacheRecords object contains PivotCacheRecord objects.
private static PivotCacheRecords GeneratePivotTableCacheRecordsPart1()
{
var element =
new PivotCacheRecords() { Count = (UInt32Value)0U };
return element;
}
PivotTable Definition
A PivotTable definition is focused on specifying the presentation of the fields that are defined in the associated PivotTable cache definition. To do this, it defines the PivotTable cache to use, how to display the fields in the PivotTable cache, and where it can put the PivotTable. The Open XML SDK 2.0 provides the pivotTableDefinition class to represent PivotTable definition parts.
The GeneratePivotTablePart1 method that is generated by the DocumentReflector tool for the SalesData.xlsx workbook allocates, initializes, and returns a pivotTableDefinition object.
private static pivotTableDefinition GeneratePivotTablePart1()
{
var element =
new pivotTableDefinition(
new Location() { Reference = "A1:D5",
FirstHeaderRow = (UInt32Value)1U,
FirstDataRow = (UInt32Value)2U,
FirstDataColumn = (UInt32Value)1U },
new PivotFields(
new PivotField(
new Items(
new Item() { Index = (UInt32Value)0U },
new Item() { Index = (UInt32Value)1U },
new Item() { ItemType = ItemValues.Default }
) { Count = (UInt32Value)3U }
) { Axis = PivotTableAxisValues.AxisRow,
ShowAll = false },
new PivotField() { DataField = true,
ShowAll = false },
new PivotField(
new Items(
new Item() { Index = (UInt32Value)0U },
new Item() { Index = (UInt32Value)1U },
new Item() { ItemType = ItemValues.Default }
) { Count = (UInt32Value)3U }
) { Axis = PivotTableAxisValues.AxisColumn,
ShowAll = false },
new PivotField() { ShowAll = false },
new PivotField() { ShowAll = false }
) { Count = (UInt32Value)5U },
new RowFields(
new Field() { Index = 0 }
) { Count = (UInt32Value)1U },
new RowItems(
new RowItem(
new MemberPropertyIndex()),
new RowItem(
new MemberPropertyIndex() { Val = 1 }),
new RowItem(
new MemberPropertyIndex()
) { ItemType = ItemValues.Grand }
) { Count = (UInt32Value)3U },
new ColumnFields(
new Field() { Index = 2 }
) { Count = (UInt32Value)1U },
new ColumnItems(
new RowItem(
new MemberPropertyIndex()),
new RowItem(
new MemberPropertyIndex() { Val = 1 }),
new RowItem(
new MemberPropertyIndex()
) { ItemType = ItemValues.Grand }
) { Count = (UInt32Value)3U },
new DataFields(
new DataField() { Name = "Sales by Country and Period",
Field = (UInt32Value)1U,
BaseField = 0,
BaseItem = (UInt32Value)0U,
NumberFormatId = (UInt32Value)44U }
) { Count = (UInt32Value)1U },
new Formats(
new Format(
new PivotArea() { Outline = false,
CollapsedLevelsAreSubtotals = true,
FieldPosition = (UInt32Value)0U }
) { FormatId = (UInt32Value)1U }
) { Count = (UInt32Value)1U },
new PivotTableStyle() { Name = "PivotStyleLight16",
ShowRowHeaders = true,
ShowColumnHeaders = true,
ShowRowStripes = false,
ShowColumnStripes = false,
ShowLastColumn = true }
) { Name = "PivotTable11",
CacheId = (UInt32Value)57U,
ApplyNumberFormats = false,
ApplyBorderFormats = false,
ApplyFontFormats = false,
ApplyPatternFormats = false,
ApplyAlignmentFormats = false,
ApplyWidthHeightFormats = true,
DataCaption = "Values",
UpdatedVersion = 3,
MinRefreshableVersion = 3,
ShowCalculatedMembers = false,
UseAutoFormatting = true,
ItemPrintTitles = true,
CreatedVersion = 3,
Indent = (UInt32Value)0U,
Outline = true,
OutlineData = true,
MultipleFieldFilters = false,
RowHeaderCaption = "Countries",
ColumnHeaderCaption = "Periods",
FieldListSortAscending = true };
return element;
}
Data Connection
The Connections part specifies the properties of the connections stored in the workbook. The Open XML SDK 2.0 provides the Connections Class class to represent connections parts.
The GenerateConnectionsPart1 method that is generated by the DocumentReflector tool for the SalesData.xlsx workbook allocates, initializes, and returns a Connections object, which contains a Connection object together with its properties set to point to the SharePoint Sales list. The connection named SalesData1 has its ID property set to 1, which matches the ConnectionId property of the CacheSource element of the PivotTable cache definition shown previously.
The connection's Connection property specifies that the Microsoft.Office.List.OLE DB.2.0 data provider should be used and that the Command property provides the details about the list that the data is coming from.
private static Connections GenerateConnectionsPart1()
{
var element =
new Connections(
new Connection(
new DatabaseProperties()
{ Connection =
"Provider=Microsoft.Office.List.OLEDB.2.0;" +
"Data Source=\"\";" +
"ApplicationName=Excel;Version=12.0.0.0",
Command = "<LIST>" +
"<VIEWGUID>" +
"{03198973-7AEB-42CA-B806-6E16E585CDBE}" +
"</VIEWGUID>" +
"<LISTNAME>" +
"{A91A5DD7-600F-4873-ADA4-9F84C6ED1570}" +
"</LISTNAME>" +
"<LISTWEB>" +
"https://sharepoint/_vti_bin" +
"</LISTWEB>" +
"<LISTSUBWEB></LISTSUBWEB>" +
"<ROOTFOLDER>" +
"/Lists/Sales" +
"</ROOTFOLDER>" +
"</LIST>",
CommandType = (UInt32Value)5U }
) { Id = (UInt32Value)1U,
ConnectionFile = "C:\\Tmp\\SalesData.iqy",
KeepAlive = true,
Name = "SalesData1",
Type = (UInt32Value)5U,
RefreshedVersion = 3,
MinRefreshableVersion = 3,
SaveData = true });
return element;
}
Conclusion
It is possible to use the typesafe classes that are provided with the Open XML SDK 2.0 to programmatically create an Excel PivotTable that is connected to data that is stored in a SharePoint list. The Open XML SDK 2.0 provides the PivotCacheDefinition, PivotCacheRecords, and pivotTableDefinition classes to help create the required PivotTable related parts and the Connections Class to help create a connection part.
The Open XML SDK 2.0 typesafe classes simplify programmatically manipulating Open XML documents. However, becoming familiar with the required Open XML document parts and relationships to implement a specific functionality still requires gaining an understanding of the document format. A useful approach that can help speed this learning process is to use the DocumentReflector tool that is provided with the Open XML SDK 2.0. By using this approach, an Office client application, such as Microsoft Office Excel 2007, is used to create the desired functionaility, such as a PivotTable. The ability of DocumentReflector to generate C# code that uses the Open XML SDK 2.0 typesafe classes can then be taken advantage of to generate the code that can build the desired functionality. Ths code can then be used as a starting point for making modifications.
Additional Resources
For more information, see the following resources: