Don't Expect it to Make Sense, Just Accept It
Creating PivotTable
s 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, PivotCache
s 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;
pvt.ErrorString = "";
pvt.DisplayErrorString = true;
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);
pvt.CompactLayoutColumnHeader = "Months";
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:
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:
var fld = ((PivotField)pvt.PivotFields("Description"));
fld.AutoSort(2, "Total Purchases");
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;
int currentRowBeingExamined = FIRST_DESCRIPTION_ROW;
while (currentRowBeingExamined < rowsUsed)
{
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:
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;
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;
}
}