vb.netpivot-tableepplus

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.


Solution

  • 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
    {
        get
        {
            return GetXmlNodeString("@colHeaderCaption");
        }
        set
        {
            SetXmlNodeString("@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")
    package.Workbook.Worksheets.MoveToStart(sheet.Name)
    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
    pivot.RowFields.Add(pivot.Fields("RowField"))
    
    'Set row caption
    pivot.RowHeaderCaption = "My Row Caption"
    
    'Add column field
    pivot.ColumnFields.Add(pivot.Fields("ColumnField"))
    
    '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");