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 might be a need to highlight or style some specific data in a different way to make it more significant. The 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.

GcExcel provides support for creating, styling and exporting the pivot tables to Excel and PDF. This blog would help you understand how you can apply style to a Pivot table and export the same to PDF using GcExcel. We are considering a scenario where the Pivot table represents the sales data of different products in different countries.

The countries have been grouped to create a categorization based on the region i.e. 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:

  1. 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.
  2. Create a pivot cache by specifying a cell range that consists of the data.
  3. 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:

image 1

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:

images 2

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:

Thanks for following along! If you have any questions about the new features, please leave them in the comments below.

Happy Coding!

Try GcExcel .NET free for 30 days

Download the latest version of GrapeCity Documents for Excel .NET

Download Now!

Try GcExcel Java free for 30 days

Download the latest version of GrapeCity Documents for Excel, Java Edition

Download Now!