Jerry Nixon @Work: Use VSTO 3 to create a PivotTable in Excel 2007

Jerry Nixon on Windows

Thursday, November 13, 2008

Use VSTO 3 to create a PivotTable in Excel 2007

I'm using LINQ to SQL to access Northwind here - you could use ADO.Net or whatever you want. The quick of it is this - I get the data, put the data in a table (ListObject) on Sheet2, then I create a PivotTable on Sheet3 using the ListObject on Sheet2 as the source data. Sheet1 is left empty.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;

// get data

EntityContext.NorthwindDataContext _db;
_db = new EntityContext.NorthwindDataContext();
var _Source = from _Order in _db.Orders
join _SalesPerson in _db.SalesPersons
on _Order.EmployeeID equals _SalesPerson.EmployeeID
select new
SalesPerson = _SalesPerson.FirstName
+ ' ' + _SalesPerson.LastName,
OrderId = _Order.OrderID,
OrderYear = _Order.OrderDate.Value.Year,
OrderCountry = _Order.ShipCountry,
OrderAmount = new Random().Next(500)

// create list

Microsoft.Office.Tools.Excel.ListObject _ListObject;
_ListObject = this.Controls.AddListObject(
Globals.Sheet2.Range["B2", missing], "ListObject1");
_ListObject.AutoSetDataBoundColumnHeaders = true;

// create pivot

PivotCaches _PivotCaches;
_PivotCaches = Globals.ThisWorkbook.PivotCaches();

PivotCache _PivotCache;
_PivotCache = _PivotCaches.Create(

Range _DestinationRange;
_DestinationRange = Globals.Sheet3.Range["B2", missing];

PivotTable _PivotTable;
_PivotTable = _PivotCache.CreatePivotTable(
_PivotTable.DisplayFieldCaptions = false;
_PivotTable.TableStyle2 = "PivotStyleMedium2";

Globals.Sheet2.Name = "PIVOT_DATA";
Globals.Sheet3.Name = "PIVOT_TABLE";

// setup columns/rows

(_PivotTable.PivotFields(4) as PivotField).Orientation
= XlPivotFieldOrientation.xlRowField;
(_PivotTable.PivotFields(1) as PivotField).Orientation
= XlPivotFieldOrientation.xlRowField;
(_PivotTable.PivotFields(3) as PivotField).Orientation
= XlPivotFieldOrientation.xlColumnField;

// measures

(_PivotTable.PivotFields(5) as PivotField),
"Sum Orders",
XlConsolidationFunction.xlSum).NumberFormat = "$#,##0";