briddums briddums - 1 year ago 163 Question

How to set column name on pivot table

Using this answer I was able to create a pivot table with a tabular layout in EPPlus.

However column headings are not displaying properly. When I create the pivot table with Excel I get the column headings "GA Category" and "Container":
Pivot table by Excel

When I create the pivot table through EPPlus I get the column headings "Row Labels" and "Column Labels"
Pivot table by EPPlus

I was wondering how I can create set the column headings via EPPlus.

Answer Source

Setting the column header via EPPLus cannot be done with the current package. In order to do so I needed to modify ExcelPivotTable.cs in the project, adding this code:

public string ColHeaderCaption
        return GetXmlNodeString("@colHeaderCaption");

It is then possible to set the row & column captions via the PivotTable class:

'Sheet containing the data
Dim dataSheet as ExcelWorksheet
dataSheet = package.Workbook.WorkSheets(0)

'Data used on pivot table - default to entire sheet
Dim dataRange as ExcelRangeBase
dataRange = dataSheet.Cells(dataSheet.Dimension.Address)

'New sheet for the pivot table
Dim sheet as ExcelWorkSheet
sheet = package.Workbook.Worksheets.Add("Pivot")
sheet.View.TabSelected = true

'Create the pivot table
Dim pivot as Table.PivotTable.ExcelPivotTable
pivot = sheet.PivotTables.Add(sheet.Cells("A1"), dataRange, "PivotTable")

'Add row field

'Set row caption
pivot.RowHeaderCaption = "My Row Caption"

'Add column field

'Set column caption
pivot.ColumnHeaderCaption = "My Column Caption"

If you cannot or do not want to modify EPPlus, you can still add the header to to your pivot table by modifying the XML after the pivot table is created:

pivot.PivotTableXml.DocumentElement.SetAttribute("colHeaderCaption", "My Column Caption");
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download