How to Create a PivotTable with Filterable Rows and Columns using Excel Interop

Don't Expect it to Make Sense, Just Accept It

Creating PivotTables with Excel Interop is hard, and the hoops you have to jump through don't often seem to make a whole lot of sense; sometimes it's like going over Niagara Falls in a barrel full of feral monkeys in order to climb into a wading pool. For those who can withstand the zaniness and/or simply need to create a PivotTable using Excel Interop, here is a real-world example of how I finally managed to do it.

First, Grok the Concept

A "Pivot Table" is a way, AFAICT, to slice and dice data both horizontally (by filtering out specified rows) and vertically (by filtering out specified columns). When it works, it's pretty sweet, as it updates totals for you, allows you to calculate values, etc., but getting it to work at all, and then as you want it to, is the rub.

Anyway, you need to provide source data to the PivotTable. Probably the easiest way to do this is to write the raw data to a dedicated sheet and then reference it when creating the pivot table. Here's the "git-go" code for revving up the PivotTable (writing the source/raw data is an exercise left to you - it's no different than any other writing-stuff-to-a-sheet in Excel, so if you're considering adding a Pivot Table, you probably already know how to do that):

int pivotDataRowsUsed = _xlBook.Worksheets["PivotData"].UsedRange.Rows.Count;
int pivotDataColsUsed = _xlBook.Worksheets["PivotData"].UsedRange.Columns.Count;
string lastColWrittenAsAlpha = ReportRunnerConstsAndUtils.GetExcelColumnName(pivotDataColsUsed);
string endRange = string.Format("{0}{1}", lastColWrittenAsAlpha, pivotDataRowsUsed);

var pch = _xlBook.PivotCaches();
Range sourceData = _xlBook.Worksheets["PivotData"].Range[string.Format("A1:{0}", endRange)];
PivotCache pc = pch.Create(XlPivotTableSourceType.xlDatabase, sourceData);
PivotTable pvt = pc.CreatePivotTable(_xlPivotTableSheet.Range["A6"], "PivotTable");

So after setting up the Range for the source data, PivotCaches are created, then the source data is defined. A PivotCache is created using that source data, then a PivotTable is created on its own sheet, using the source data, and placed at a particular spot ("A6" in my case, because the first five rows are used for informative messages and pretty pictures and such); of course, you can put the PivotTable somewhere else, and probably will.

Now for the Legal Hallucinogens

This is the point where the code gets, IMO, really weird. First, I had to add this code to get the contents of the table to appear as I wanted it to:

pvt.MergeLabels = true; // The only thing I noticed this doing was centering the heading labels
// Although somewhat confusing, these "error" settings actually prevent the "#DIV/0!" from displaying
pvt.ErrorString = "";
pvt.DisplayErrorString = true;
// This one converts what would otherwise be blank into "0" for ints and "$0" for decimal vals
pvt.NullString = "-";

The comments pretty much tell the story; it's a small amount of code, but it took me aeons to find the right mix of settings to get things to work right. I guess that's why there's no song that goes, "There's no business like code business" - because there is at least one other business like it: Monkey business!

Anyway, as per the comments, you may or may not need the MergeLabels assignment (YMMV). I was getting "#DIV/0!" for some of my values, but the assignments to "ErrorString" and "DisplayErrorString" as shown above solved that; also, setting NullString as above gave me specific "0" values intead of blanks.

Now for the code that really causes your PivotTable to jump and shout:

var descField = pvt.PivotFields("Description");
descField.Orientation = XlPivotFieldOrientation.xlRowField;

var monthField = pvt.PivotFields("MonthYr");
monthField.Orientation = XlPivotFieldOrientation.xlColumnField;
monthField.NumberFormat = "MMM yy";
monthField.DataRange.Interior.Color = ColorTranslator.ToOle(Color.LightBlue);

// This provides the ability to filter out Month columns
pvt.CompactLayoutColumnHeader = "Months";
// This provides the ability to filter out Description rows
pvt.CompactLayoutRowHeader = "Description";

The reference to "Description" means that's the value in the source data that is being used; making it an "xlRowField" sets it up to make the rows filterable (or pivotable, if you prefer).

To make columns filterable/pivotable/hideable, the "MonthYr" value from the source data is made into an xlColumnField. The "MMM yy" NumberFormat sees to it that values stored as "201509" in the source data appear as "Sep 15" on the PivotTable column heading. The assignments to CompactLayoutColumnHeader for Months and CompactLayoutRowHeader for "Description" are also necessary for this functionality. YMMV, but these assignments would never have been guessable, and are barely even grokkable (if that).

Now you can add the other values that you want to display within the PivotTable as DataFields like so:

pvt.AddDataField(pvt.PivotFields("TotalQty"), "Total Packages", 
XlConsolidationFunction.xlSum).NumberFormat = "###,##0";
pvt.AddDataField(pvt.PivotFields("TotalPrice"), "Total Purchases", 
XlConsolidationFunction.xlSum).NumberFormat = 
    "$#,##0.00";
PivotField avg = pvt.CalculatedFields().Add("Average Price", "=TotalPrice/TotalQty", true);
avg.Orientation = XlPivotFieldOrientation.xlDataField;
avg.NumberFormat = "$###0.00";

Note that you can calculate values, too, such as the last one above. Here's a calculated value that is even trickier:

// This looks wrong, but the value is calculated below 
pvt.CalculatedFields()._Add("PercentOfTotal", "=TotalPrice");
pvt.AddDataField(pvt.PivotFields("PercentOfTotal"), 
"Percentage of Total", Type.Missing).NumberFormat = "###.##";

As the comment comments, the calculation looks wrong, because it's just being assigned the value from TotalPrice. That's true, it is - for now. But the user will never see that, because that value gets further calculated later:

int pivotDataSheetRowsUsed = _xlBook.Worksheets["PivotTable"].UsedRange.Rows.Count;
int pivotDataSheetColsUsed = _grandTotalsColumnPivotTable;
int FIRST_PERCENTAGE_ROW = 12;
int FIRST_PERCENTAGE_COL = 2;
int ROWS_BETWEEN_PERCENTAGES = 5;
int currentPercentageRow = FIRST_PERCENTAGE_ROW;

while (currentPercentageRow < pivotDataSheetRowsUsed)
{
    for (int columnLoop = FIRST_PERCENTAGE_COL; columnLoop <= pivotDataSheetColsUsed; columnLoop++)
    {
        var prcntgCell = (Range)_xlPivotTableSheet.Cells[currentPercentageRow, columnLoop];
        prcntgCell.NumberFormat = "##.#0%";
        prcntgCell.PivotField.Calculation = XlPivotFieldCalculation.xlPercentOfColumn;
    }
    currentPercentageRow = currentPercentageRow + ROWS_BETWEEN_PERCENTAGES;
}

There's a bit of other code that comes between those last two snippets shown, though; this is among the wonkiest of all:

// These two lines don't seem that they would do so, but they do result in the items 
// being sorted by (grand) total purchases descending. Does the "2" signify "descending" or...???
var fld = ((PivotField)pvt.PivotFields("Description"));
fld.AutoSort(2, "Total Purchases");

// This gets the Pivot Table to what it should be, appearance-wise...
pvt.DataPivotField.Orientation = XlPivotFieldOrientation.xlRowField;

Again, the comments give away my thoughts on the matter - why these combinations of code work, I don't know, but they do, so I just grin and bear it.

Except for the formatting and prettifying, that's pretty much it. To prove that this odd-looking pile of seeming nonsequiteriousness actually works, here is a portion of the resulting PivotTable:

Rows can be filtered by the user when they click the "Description" down arrow, and columns can be filtered by them via clicking the "Months" down arrow.

Beware! Warning! Do not Mess with Excel Interop Pivot Table Any More than You Have to If You Value Your Sanity

It may be too late for me, but consider some less mind-punishing pastime than delving into the labyrinthine catacombs of Excel Interop Pivot Tables if you are easily - or even not-so-easily - driven to distraction.

Having said that, here are two bonus features that are not as cranium-corrosive as some of the code above (because they are not PivotTable-centric, but rather just the normal amount of wackiness.

Bonus 1: Color Ranges That Match a Particular Criteria

Here's an example of passing in a list of string that should be colored:

private void ColorizeContractItemBlocks(List<string> contractItemDescs)
{
    int FIRST_DESCRIPTION_ROW = 8;
    int DESCRIPTION_COL = 1;
    int ROWS_BETWEEN_DESCRIPTIONS = 5;
    int rowsUsed = _xlBook.Worksheets["PivotTable"].UsedRange.Rows.Count;
    int colsUsed = _xlBook.Worksheets["PivotTable"].UsedRange.Columns.Count;
    // Loop through PivotTable data, colorizing matching descriptions
    int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
    while (currentRowBeingExamined < rowsUsed) // TODO: May have to add a "-" to the condition
    {
        Range descriptionCell = _xlPivotTableSheet.Cells[currentRowBeingExamined, DESCRIPTION_COL];
        String desc = descriptionCell.Value2.ToString();
        if (contractItemDescs.Contains(desc))
        {
            Range rangeToColorize = _xlPivotTableSheet.Range[
                _xlPivotTableSheet.Cells[currentRowBeingExamined, 1],
                _xlPivotTableSheet.Cells[currentRowBeingExamined + 4, colsUsed]];
            rangeToColorize.Interior.Color = ColorTranslator.ToOle(CONTRACT_ITEM_COLOR);
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_DESCRIPTIONS;
    }
}

Bonus 2: Hide Ranges that Match a Particular Criteria

Adapting code like the following, you can hide rows that match a particular criteria:

// Rows with totals below a certain threshold are hidden
private void HideItemsWithFewerThan1PercentOfSales()
{
    int FIRST_TOTAL_PURCHASES_ROW = 10;
    int ROWS_BETWEEN_TOTALS = 5;
    double totalTotalPurchases;
    double onePercentOfTotalPurchases;
    int rowsUsed = _xlBook.Worksheets["PivotTable"].UsedRange.Rows.Count;
    int lastTotalPurchaseRow = rowsUsed - 6;
    Range totalTotalPurchasesCell = _xlPivotTableSheet.Cells[rowsUsed - 2, _grandTotalsColumnPivotTable];
    totalTotalPurchases = Convert.ToDouble(totalTotalPurchasesCell.Value2);
    onePercentOfTotalPurchases = totalTotalPurchases/100;
    int currentRowBeingExamined = FIRST_TOTAL_PURCHASES_ROW;
            
    // Loop through PivotTable sheet, hiding rows where totalPurchases is < 1% of totalTotalPurchases
    while (currentRowBeingExamined < lastTotalPurchaseRow)
    {
        Range totalPurchasesCell = _xlPivotTableSheet.Cells[currentRowBeingExamined, 
                                   _grandTotalsColumnPivotTable];
        double totalPurchases = Convert.ToDouble(totalPurchasesCell.Value2);
        if (totalPurchases < onePercentOfTotalPurchases)
        {
            Range rangeToHide = _xlPivotTableSheet.Range[
                _xlPivotTableSheet.Cells[currentRowBeingExamined - 2, 1],
                _xlPivotTableSheet.Cells[currentRowBeingExamined + 2, _grandTotalsColumnPivotTable]];
            rangeToHide.EntireRow.Hidden = true;
        }
        currentRowBeingExamined = currentRowBeingExamined + ROWS_BETWEEN_TOTALS;
    }
}