Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

Creating PivotTable and PivotChart using VSTO

Extending the previous discussion on PivotTables, in this blog post I will show how you can create a PivotTable and PivotChart using Visual Studio Tools for Office (VSTO). After populating data into excel worksheet, generating PivotTables is quite a common task for VSTO developers. This can be easily broken down into below steps:

a) Create Table Range where Pivot Table would be created
b) Create Chart Range where Pivot Chart would be placed
c) Determine Data Range that would act as input for Pivot (generally you might want to create many pivot tables out of this range)
d) Create a PivotCache object using Data Range as Input
e) Determine Data Range that would act as input for a pivot table
f) Generate Pivot Table and set the orientation of Pivot fields
g) Create chart with using Pivot Table as input

Let’s have a quick look at the C# source code below:

//Step A, B
Worksheet worksheet = Globals.Factory.GetVstoObject(

var chartRange = worksheet.Range["G3", "J15"];
var pivotTableRange = worksheet.Range["G20", "J30"];
//Step C
var listObject = FindListObject("demoDataList");
var end = listObject.DataBodyRange.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).Row;
var dataRangeForPivot = worksheet.Range["A1", "W" + end.ToString()];
//Step D
Excel.PivotCache pivotCache = this.Application.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, dataRangeForPivot);
//Step E
Microsoft.Office.Interop.Excel.Range dataRangeForPivotTable = worksheet.Range["H35", "H" + end.ToString()];
//Step F
Microsoft.Office.Interop.Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(dataRangeForPivotTable, @"DTable", dataRange, Type.Missing);
Microsoft.Office.Interop.Excel.PivotField demoField = ((Microsoft.Office.Interop.Excel.PivotField)pivotTable.PivotFields(8));
demoField.Orientation =
demoField.Orientation =
demoField.Function = Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount;
Chart chart = worksheet.Controls.AddChart(chartRange, "DChart");
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
chart.ChartTitle.Text = "Demo Analysis";

Confusing? I hope not🙂

One response to “Creating PivotTable and PivotChart using VSTO

  1. Pingback: Link Resource # 59 : Jun 06 – Jun 22 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: