Demystifying Pivot Tables
Written by Janet Swift   
Friday, 20 August 2010
Article Index
Demystifying Pivot Tables
Pivot tables
Using the PivotGrid control
Running the program


The Excel spreadsheet Book1 contains the data in Sheet1 shown below:



You can find this spreadsheet as part of the download of this project. We will also assume that the spreadsheet is stored in the C# project directory in the Debug sub-directory so that it can be read in more easily.

Start a new C# project and drag and drop a XamPivotGrid control onto the page. As soon as the control has initialised you can see that it looks a bit like a vestigial 2D spreadsheet with placeholder in the row and column fields. The headings Drop Column Fields here and Drop Row Fields here are used to define the two categories that are used to create the 2D cross tabulation.



Although everything can be done using XAML and although it is more usual to allow the user to setup the PivotGrid using the associated PivotDataControl we will set up everything from code. Once you know how the code relates to the objects and properties involved using the PivotDataControl and converting to XAML is simpler.

Our first task is to read in the Excel spreadsheet. You don't have to worry about having Excel installed on the client system as there is an Excel connector that understands the file format. So at this point make sure that the spreadsheet that you are going to work with has been saved in the project's Debug directory - if not change the file names and paths appropriately.

To created the pivot table we first have to use a DataSource object to provide the data cube to the PivotGrid. In this case the DataSource has to take the Excel data, convert it into a flat data table add then convert this into a data cube.

First we need to create a stream to read the Excel spreadsheet:

 Stream stream = new FileStream(

Specifying FileShare.ReadWrite allows you to keep the spreadsheet open in Excel while the program is running.

Next we need to create and use a FlatDataSource object. The first thing to do is specify the connection to the Excel spreadsheet - which stream to read and the name of the sheet:

FlatDataSource flatDataSource = 
new FlatDataSource();
excelDataConnectionSettings = 
new ExcelDataConnectionSettings()
      FileStream = stream,
      GeneratedTypeName = "ExcelData",
      WorksheetName = "Sheet1"
flatDataSource.ConnectionSettings =

At this point we have the FlatDataSource set up so that it knows where to get the data from to make up the cube. 

We haven't supplied the information to tell it what constitutes the data that is to be used to cross tabulate and the data that is the value to be cross tabulated. All of the data is identified by the column headings i.e. the text in the Row 0 that makes up the heading for each column. Notice that the heading has to be in row zero.

The column that contains the data to be cross tabulated is specified by the Measures property i.e. it is what you have measured. The two columns used to cross tabulate are specified by the Rows and Columns properties and for the moment we will simply treat these as the rows and columns of a 2D crosstab:

flatDataSource.Measures = 
flatDataSource.Rows =
flatDataSource.Columns =

You can see that we have defined the measure to be the Profit column and the rows and columns of the crosstab correspond to the Store and Type column.






Last Updated ( Friday, 20 August 2010 )

RSS feed of all content
I Programmer - full contents
Copyright © 2014 All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.