Jerry Nixon on Windows: 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;
_ListObject.SetDataBinding(_Source);

// create pivot

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

PivotCache _PivotCache;
_PivotCache = _PivotCaches.Create(
XlPivotTableSourceType.xlDatabase,
"ListObject1",
XlPivotTableVersionList.xlPivotTableVersion12);

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

PivotTable _PivotTable;
_PivotTable = _PivotCache.CreatePivotTable(
_DestinationRange,
"PivotTable1",
missing,
XlPivotTableVersionList.xlPivotTableVersion12);
_PivotTable.DisplayFieldCaptions = false;
_PivotTable.TableStyle2 = "PivotStyleMedium2";

Globals.Sheet2.Name = "PIVOT_DATA";
Globals.Sheet3.Name = "PIVOT_TABLE";
Globals.Sheet3.Activate();

// 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.AddDataField(
(_PivotTable.PivotFields(5) as PivotField),
"Sum Orders",
XlConsolidationFunction.xlSum).NumberFormat = "$#,##0";