Creating a PivotTable Programmatically

  • Article

I received an email from a customer the other day asking how to set up an Excel pivot table programmatically. The Excel OM exposes a number of objects and methods that you can use to create pivot tables, some more sophisticated than others. I wanted the simplest possible way to set up a pivot table from an external data source, and this is what I ended up with...

I used the AdventureWorks SQL database, which you can download from here. In my solution, I first set up a data connection to the database, with a SQL select statement to fetch all the SalesPerson sales records:

string connection =

    @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=AdventureWorks";

string command =

    "SELECT * FROM [Sales].[vSalesPersonSalesByFiscalYears]";

Next, I added a new PivotCache to the PivotCaches collection in the active workbook, and set its data connection and SQL command properties:

Excel.PivotCache pivotCache =

    this.Application.ActiveWorkbook.PivotCaches().Add(

    Excel.XlPivotTableSourceType.xlExternal, missing);

pivotCache.Connection = connection;

pivotCache.MaintainConnection = true;

pivotCache.CommandText = command;

pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;

I can then add a new PivotTable to the worksheet, based on the PivotCache I’ve just configured:

Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveSheet;

Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(missing);

Excel.PivotTable pivotTable = pivotTables.Add(

    pivotCache, this.Application.ActiveCell, "PivotTable1",
missing, missing);

Then, set the PivotTable to use the pivot table stencil outline instead of the default 2x2 cell grid, and format it with grey alternating row shading:

pivotTable.SmallGrid = false;

pivotTable.ShowTableStyleRowStripes = true;

pivotTable.TableStyle2 = "PivotStyleLight1";

Set up the SalesTerritory field as the page field, and FullName as the row field:

Excel.PivotField pageField =
(Excel.PivotField)pivotTable.PivotFields("SalesTerritory");

pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;

Excel.PivotField rowField =
(Excel.PivotField)pivotTable.PivotFields("FullName");

rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;

Add a data field for the sales for 2004:

pivotTable.AddDataField(

    pivotTable.PivotFields("2004"), "Sum of 2004", Excel.XlConsolidationFunction.xlSum);

Done. The end result looks like this: