Custom Table Styles

From EPPlus version 5.6 you can create your own custom named table via the ExcelPackage.Workbook.Styles property. Custom styles can either be created from scratch or use a build-in or another custom style as a template.

The samples below are from our sample project - Sample 7.4-C# or Sample 7.4-VB

Samples

Custom table styles can be created with the CreateTableStyle or the CreateTableAndPivotTableStyle method. The CreateTableStyle method will return a styling object with elements that only applies to table. The CreateTableAndPivotTableStyle will create a styling object that can be used both for tables and pivot tables.

//Create a named style used for tables only.
var customTableStyle = p.Workbook.Styles.CreateTableStyle("Custom Table Style 1");

The returned object exposes properties to set styles for different elements of a table.

customTableStyle.WholeTable.Style.Font.Color.SetColor(eThemeSchemeColor.Text2);
customTableStyle.HeaderRow.Style.Font.Bold = true;
customTableStyle.HeaderRow.Style.Font.Italic = true;

customTableStyle.HeaderRow.Style.Fill.Style = eDxfFillStyle.GradientFill;
customTableStyle.HeaderRow.Style.Fill.Gradient.Degree = 90;

var c1 = customTableStyle.HeaderRow.Style.Fill.Gradient.Colors.Add(0);
c1.Color.SetColor(Color.LightGreen);

var c3 = customTableStyle.HeaderRow.Style.Fill.Gradient.Colors.Add(100);
c3.Color.SetColor(Color.DarkGreen);

customTableStyle.TotalRow.Style.Font.Italic = true;

Style elements used for stripes also exposes the BandSize property to set the number of stripes per row or column. Here we use a build-in table style as template for our custom style.

var customTableStyle = p.Workbook.Styles.CreateTableStyle(styleName, TableStyles.Dark11);

//Set the stripe size to 2 rows for both the both the first and second row stripes element.
customTableStyle.FirstRowStripe.BandSize = 2;
customTableStyle.FirstRowStripe.Style.Fill.BackgroundColor.SetColor(Color.LightGreen);

customTableStyle.SecondRowStripe.BandSize = 2;
customTableStyle.SecondRowStripe.Style.Fill.BackgroundColor.SetColor(Color.LightSkyBlue);

As described, you can also create a named table style that can be applied to both tables and pivot tables. This style will have all style elements for both tables and pivot tables.

//Create a named style that can be used both for tables and pivot tables. 
//We create this style from one of the build in pivot table styles - Medium13, but table styles can also be used as a parameter for this method
var customTableStyle = p.Workbook.Styles.CreateTableAndPivotTableStyle(customTableStyle3, PivotTableStyles.Medium13);

To apply a custom table style to a table or pivot table you only need to set the StyleName property to the name you choose for you style.

table1.StyleName = "Custom Table Style 1";

Custom Slicer Styles

In the same way as you create custom table style you can create named styles from slicers. A named slicer style is created with the CreateSlicerStyle method...

            //Slicers can also be styled by creating a named style. Here we use the build in Light 5 as a template and changes the font of the slicer.
            //See Sample 27 for more detailed samples.
            var slicer = pivotTable4.Fields["CompanyName"].Slicer;
            var styleName = "CustomSlicerStyle1";
            var style = pck.Workbook.Styles.CreateSlicerStyle(styleName, eSlicerStyle.Light5);
            style.WholeTable.Style.Font.Name = "Stencil";
            slicer.StyleName = styleName;

The slicer styles contains slicer unique elements, like SelectedItemWithData in the code below, that can have different styles.

//Create a named style that applies to slicers with a console feel to the style.
var customSlicerStyle = p.Workbook.Styles.CreateSlicerStyle(styleName);

customSlicerStyle.WholeTable.Style.Font.Name = "Consolas";
customSlicerStyle.WholeTable.Style.Font.Size = 12;
customSlicerStyle.WholeTable.Style.Font.Color.SetColor(Color.WhiteSmoke);
customSlicerStyle.WholeTable.Style.Fill.BackgroundColor.SetColor(Color.Black);

customSlicerStyle.SelectedItemWithData.Style.Fill.BackgroundColor.SetColor(Color.Gray);
customSlicerStyle.SelectedItemWithData.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.DarkGray);

See also

• Sample 7.4-C# or Sample 7.4-VB
• Tables