Styling Excel Pivot Tables in .NET Core

June 05 2020

The pivot table is one of the powerful features of Excel. It helps to organize and summarize data from large datasets to create meaningful reports. When organizing the data for analysis purposes, there may be a need to highlight/style specific data in a different way to make it more significant. Pivot tables can be styled either by using one of the built-in table styles or by defining a custom table style. The table style defines styling for different elements of the pivot table namely HeaderRow, GrandTotalColumn, GrandTotalRow, WholeTable, FirstRowSubheading and more.

GrapeCity Documents for Excel provides support for creating, styling and exporting the pivot tables to Excel and PDF. In this article, we'll apply style to a pivot table and export to a PDF using GcExcel.

The data that the Pivot table represents will be the sales data of products across different countries. The countries have been grouped to create a categorization based on the region (North, South, East and West). The sales data has been summarized using the GrandTotalColumn and GrandTotalRow. The styling has been applied to highlight the product names, the different regions, the summarized values and differentiate the data rows from the header rows.

Create a Pivot Table:

Populate the workbook sheet with the dataset that would be used to create a pivot table. Here, we are loading a sample excel file in the workbook to accomplish the same.
Create a pivot cache by specifying a cell range that consists of the data.
Add the pivot table to the sheet by assigning the pivot cache to the pivot table.

Code:

public IPivotTable createPivotTable()
        {
            // Initialize workbook and load sample data
            workbook = new Workbook();
            workbook.Open(@"data\pivotdata.xlsx");

            //Fetch default worksheet 
            IWorksheet dataworksheet = workbook.Worksheets[1];
            IWorksheet pivotworksheet = workbook.Worksheets[0];
            pivotworksheet.Range["A:B"].ColumnWidth = 17.5;
            pivotworksheet.Range["C:H"].ColumnWidth = 12;
            pivotworksheet.Range["B:H"].HorizontalAlignment = HorizontalAlignment.Center;

            //Create the pivot cache
            var pivotcache = workbook.PivotCaches.Create(dataworksheet.Range["A1:G100"]);
            //Add the pivot table
            var pivottable = pivotworksheet.PivotTables.Add(pivotcache, pivotworksheet.Range["A5"], "pivottable1");

            //Add pivot fields and set number format code            
            var field_region = pivottable.PivotFields[1];
            field_region.Orientation = PivotFieldOrientation.RowField;
            field_region.Name = "Region";
            field_region.Position = 0;

            var field_city = pivottable.PivotFields[2];
            field_city.Orientation = PivotFieldOrientation.RowField;
            field_city.Name = "City";
            field_city.Position = 1;

            var field_Category = pivottable.PivotFields[3];
            field_Category.Orientation = PivotFieldOrientation.ColumnField;
            field_Category.Name = "Category";

            var field_Price = pivottable.PivotFields[6];
            field_Price.Orientation = PivotFieldOrientation.DataField;
            field_Price.NumberFormat = "#,##0"; //set number format code                                  
            return pivottable;
        }

The pivot table with default style looks like this:

Let's alter this default style by applying a built-in table style.

Apply a Built-in Table Style to Your Pivot Table:

GcExcel provides a collection of built-in table styles which can be accessed through TableStyles enum. You can simply set the Styles property of the PivotTable class to a built-in table style defined in the TableStyles enum to apply the built-in table style to the pivot table.

Code:

public void BuiltInStylePivot()
        {
            //Create pivot table and apply built-in style to it
            IPivotTable pivotTable = createPivotTable();
            pivotTable.Style = workbook.TableStyles["PivotStyleMedium20"];

            //Save workbook to Excel and PDF
            SavePivot("builtInStyle.xlsx", "builtInStyle.pdf");
        }

Applying the PivotStyleMedium20 style to the pivot table gives it the following appearance:

Styling of the pivot tables is not just confined to using built-in table style. You can even define a custom style and assign it to the Styles property of the pivot table.

Apply Custom Table Style to Pivot Table:

The ITableStyle interface let’s you define a new custom table style. The table style defines the style for different table elements such as HeaderRow, GrandTotalRow, GrandTotalColumn etc. You can define the Border, Interior, Font and StripSize style settings for each table element.

Here is a sample code snippet for defining a custom style which sets the interior color for the HeaderRow, GrandTotalRow, GrandTotalColumn,FirstRowSubheading, WholeTable and font color for HeaderRow and GrandTotalRow:

Code:

public void CustomStylePivot()
        {
            //Create pivot table
            IPivotTable pivotTable = createPivotTable();

            //Create PivotTable custom style 
            ITableStyle style = workbook.TableStyles.Add("pivotStyle1");

            // Configure the table style
            style.ShowAsAvailablePivotStyle = true;
            style.TableStyleElements[TableStyleElementType.HeaderRow].Interior.Color = Color.BlueViolet;
            style.TableStyleElements[TableStyleElementType.HeaderRow].Font.Color = Color.White;
            style.TableStyleElements[TableStyleElementType.GrandTotalRow].Interior.Color = Color.BlueViolet;
            style.TableStyleElements[TableStyleElementType.GrandTotalRow].Font.Color = Color.White;
            style.TableStyleElements[TableStyleElementType.GrandTotalColumn].Interior.Color = Color.LightCyan;
            style.TableStyleElements[TableStyleElementType.FirstRowSubheading].Interior.Color = Color.MediumPurple;
            style.TableStyleElements[TableStyleElementType.WholeTable].Interior.Color = Color.LightCyan;

            //Apply the custom style to pivot table
            pivotTable.Style = style;

            //Restrict applying custom table style to GrandTotalColumn                   
            pivotTable.ShowTableStyleLastColumn = false;

            //Save workbook to Excel and PDF
            SavePivot("customStyle.xlsx", "customStyle.pdf");
        }

And this is how the pivot table would appear after applying the custom style:

At times, you may want to apply a table style only to a few table elements and not others. The ShowTableStyleLastColumn, ShowTableStyleColumnHeaders, ShowTableStyleRowHeaders, ShowTableStyleColumnStripes, ShowTableStyleRowStripes can help you accomplish the same. Setting any of these properties to false restricts the table style from being applied to the table element being referred by the property.

Now let’s see how these styles are exported to Excel and PDF.

Export styled Pivot Table to Excel/PDF:

The Save method of the Workbook class let’s you export the workbook with styled pivot tables to both Excel and PDF formats.

Code:

public void SavePivot(string excelfilename, string pdffilename)  
        {  
            //Saving workbook to Excel  
            workbook.Save(excelfilename, SaveFileFormat.Xlsx);

            // Saving workbook to pdf                  
            workbook.Save(pdffilename, SaveFileFormat.Pdf);  
        }

Excel Output: