Working with custom named table or slicer styles
From EPPlus version 5.6 you can create your own custom named table, pivot table or slicer styles. Custom named styles are created 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
Tables and Pivot tables can share the same styles or be created to apply to tables or pivot table only.
//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;Elements setting style for stripes also exposes the BandSize property to set the number of stripes used.
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);Pivot table styles will expose other parts than a table style.
//Create a named style that applies only to pivot tables.
var customPivotTableStyle = p.Workbook.Styles.CreatePivotTableStyle(styleName);
customPivotTableStyle.WholeTable.Style.Font.Color.SetColor(ExcelIndexedColor.Indexed22);
customPivotTableStyle.PageFieldLabels.Style.Font.Color.SetColor(Color.Red);
customPivotTableStyle.PageFieldValues.Style.Font.Color.SetColor(eThemeSchemeColor.Accent4);
customPivotTableStyle.HeaderRow.Style.Font.Color.SetColor(Color.DarkGray);
customPivotTableStyle.HeaderRow.Style.Fill.Style = eDxfFillStyle.GradientFill;
customPivotTableStyle.HeaderRow.Style.Fill.Gradient.Degree = 180;
var c1 = customPivotTableStyle.HeaderRow.Style.Fill.Gradient.Colors.Add(0);
c1.Color.SetColor(Color.LightBlue);
var c3 = customPivotTableStyle.HeaderRow.Style.Fill.Gradient.Colors.Add(100);
c3.Color.SetColor(Color.DarkCyan);Custom styles can also be created using another style as a template:
//Create a new custom table style with the build in style Dark11 as template.
var customTableStyle = p.Workbook.Styles.CreateTableStyle(styleName, TableStyles.Dark11);
//Then set the header and total row font to italic.
customTableStyle.HeaderRow.Style.Font.Italic = true;
customTableStyle.TotalRow.Style.Font.Italic = true;You can also create a named table style that can be applied to both tables and pivot tables. This style will have all 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";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);