Creating a PivotTable Programmatically
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: