How to Add a Pivot Table to a Spreadsheet Using Excel Interop and C#

The Pivot Move

Using Pivot Tables in Excel can be pivotal; they allow you to view data sideways, inside-out, and backwards. I don't know what that means, exactly, but knowing how to create pivot tables comes in handy at times, especially when your employer or client asks for one. Hence this tip.

The following tip assumes you already have the code that creates the Excel spreadsheet and are placing data on it. All this tip shows is how to use existing data on the sheet. So without further ado, here's what you need to do:

Add some declarations (this assumes you have this using clause: "using Excel = Microsoft.Office.Interop.Excel;")

static readonly object useDefault = Type.Missing;
PivotTable pivotTable;
Range pivotData;
Range pivotDestination;
PivotField descriptionPivotField;
PivotField shortnamePivotField;
PivotField itemcodePivotField;
PivotField pricePivotField;
string pivotTableName = @"SupercalifornialisticXPalbeauxdacious";

The PivotFields will differ based on your data - not only their names (it's not likely you want to name yours "shortnamePivotField" and "itemcodePivotField", etc.) but also the number of PivotFields you want will probably differ.

The Gory Details

Now write the code necessary to create the Pivot Table based on existing data on the sheet:

private void AddPivotTable()
{
    pivotData = _xlSheet.Range["A1:K1600"];
    pivotDestination = _xlSheet.Range["A1605", useDefault];
    _xlBook.PivotTableWizard(
            XlPivotTableSourceType.xlDatabase,
            pivotData,
            pivotDestination,
            pivotTableName,
            true,
            true,
            true,
            true,
            useDefault,
            useDefault,
            false,
            false,
            XlOrder.xlDownThenOver,
            0,
            useDefault,
            useDefault
    );

    // Set variables used to manipulate the Pivot Table.
    pivotTable = (PivotTable)_xlSheet.PivotTables(pivotTableName);
    
    shortnamePivotField = (PivotField)pivotTable.PivotFields(2);
    itemcodePivotField = (PivotField)pivotTable.PivotFields(3);
    descriptionPivotField = (PivotField)pivotTable.PivotFields(4);
    pricePivotField = (PivotField)pivotTable.PivotFields(7);

    // Format the Pivot Table.
    pivotTable.Format(XlPivotFormatType.xlReport2);
    pivotTable.InGridDropZones = false;
    pivotTable.SmallGrid = false;
    pivotTable.ShowTableStyleRowStripes = true;
    pivotTable.TableStyle2 = "PivotStyleLight1";

    // Page Field
    shortnamePivotField.Orientation = XlPivotFieldOrientation.xlPageField;
    shortnamePivotField.Position = 1;
    shortnamePivotField.CurrentPage = "(All)";
    
    // Row Fields
    descriptionPivotField.Orientation = XlPivotFieldOrientation.xlRowField;
    descriptionPivotField.Position = 1;
    itemcodePivotField.Orientation = XlPivotFieldOrientation.xlRowField;
    itemcodePivotField.Position = 2;

    // Data Field
    pricePivotField.Orientation = XlPivotFieldOrientation.xlDataField;
    pricePivotField.Function = XlConsolidationFunction.xlSum;
}

Of course, the sheet has to be saved, but the assumption is that this is already being done elsewhere.

As you can see, the code above references a hard-coded range of data for the pivotData value ("A1:K1600"); you will need a different range, and probably want to avoid hard-coding the values, since the amount of data might vary if based on volatile data. The same is true for the pivotDestination (shown above with a row value of "A1605"), which leaves one empty row between the data range and the pivot table. Again, if your main data range is volatile, you will not want to hard-code this with a constant value.

Of course, you will need to call AddPivotTable() from your code after the data on which it relies has been written to the sheet.

Los Resultados

For the code shown above, this is the result - by no means is it perfect, but it's a solid starting point:

...and here it is with the Page filed (Short Name) dropped down, ready for filtering:

Dump the Grumpy Chump

Finally, null out the objects you used:

shortnamePivotField = null;
itemcodePivotField = null;
descriptionPivotField = null;
pivotDestination = null;
pivotData = null;
pivotTable = null;

A Note on XlPivotField"Orientation" Values

One of the problems, to my way of thinking, with the way the Excel Interop PivotTable code works is in the nomenclature for the various field types, which seems misleading or at best non-optimal to me.

The possible values for Excel.XlPivotFieldOrientation (in alphabetical order) are:

• xlColumnField
• xlDataField
• xlHiddenField
• xlPageField
• xlRowField

What in Tarheelnation do these do/how do they differ, each from its brethren? It's not always obvious from the Christian names they were given. I would propose these replacements as a way to better understand their raison d'etre:

• xlColumnField = xlDisplayField (in my tests, I see no difference between this and xlRowField)
• xlDataField = xlCalculatedField (it is indeed a data field, but aren't they all?)
• xlHiddenField = xlInitiallyHidden (it is available in the checkbox list, and checking it will visiblize it)
• xlPageField = xlFilterField
• xlRowField = xlDisplayField (in my tests, I see no difference between this and xlColumnField)

As Vonnegut said, "And So it Goes"

This tip should be enough to generate a Pivot Table; from there, you can tweak and twist and tork it to meet your particular needs.

Note: This code was adapted from the article "Creating PivotTables in Excel 2007", which can be found here.