Pivot Table Styles
Pivot tables are a separate part of a workbook and their style isn't connected to the cells in the workbook. The style displayed in the worksheet is derived from pivot table theme and its style. If you want to change the style of pivot table, you have to specify style for the pivot table itself.
Style of the pivot table cells in the worksheet pivot is determined by a pivot table theme and by differential styles for subset of pivot table (pivot area).
Pivot tables and styles especially are in a pre-alpha stage and expect a lot of errors. ClosedXML is mostly able to create a workbook with a styled pivot table.
OOXML has several default themes for a pivot table. Each theme defines style for elements of the pivot table. The theme of a pivot table can be changed by setting the property IXLPivotTable.Theme. OOXML contains several predefined themes that are represented by XLPivotTableTheme enum. The XLPivotTableTheme.None only specifies a default Calibri font and size 11.
IXLPivotTable pt = sheet.PivotTables.Add("pivot", sheet.Cell(1, 1), table.AsRange());
pt.Theme = XLPivotTableTheme.PivotStyleLight16;
Pivot table defines a map of pivot areas and for each area defines a differential style. The area can be very specific.
Each area has a criteria that are used to determine which cells of the table satisfy the criteria and thus have applied differential style.
The most basic criteria to determine an area is a type, as seen at the image above. There are many others, e.g. only cells that are on a row with a specific pivot field and many others. Full details of all possible criteria can be found in chapter 18.3.1.68 of ECMA-376.
Each cell can be a member of multiple areas. Each area contributes a piece of a style and the final style for the cell is determined by combining the area styles. E.g. If there were two column fields and there were two areas with a specified style: header area of column field 1 should be bold + header area of column field 2 should be red, the combined style would be bold red, because header for each column field references the same cell.
It's possible to select an are in Excel by moving around a table and looking for a change into a small arrow. Once the arrow appears, click once to select whole area and Excel can now apply the style to the selected area.
The header is an area common for all row/column fields. The final style is determined by combined differential styles of all fields for the axis that define style for its header. Use the IXLPivotTable.RowLabels or IXLPivotTable.ColumnLabels to access the pivot field and then use the IXLPivotField.StyleFormat.Header to modify the style of a header of the field for specific axis.
In the example, the both row fields specify the header style and the resulting style is a combination of both styles. Column dimension has only one field and its style modifies the column header, not the row header.
Setup code
private record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
pt.Theme = XLPivotTableTheme.None;
pt.SetRowHeaderCaption("Pastry sales");IXLPivotField nameRowField = pt.RowLabels.Add("Name");
IXLPivotField monthRowField = pt.RowLabels.Add("Month");
IXLPivotField countryColumnField = pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
nameRowField.StyleFormats.Header.Style.Fill.BackgroundColor = XLColor.LightBlue;
monthRowField.StyleFormats.Header.Style.Font.SetBold().Font.SetFontSize(20);
countryColumnField.StyleFormats.Header.Style.Fill.SetBackgroundColor(XLColor.Orange).Font.SetItalic().Font.SetFontSize(12);
wb.SaveAs("example-style-pivot-table-headers.xlsx");Field labels are an area with unique values of the field. Each label area can be separately styled. Styles for different field labels don't combine themselves. Although not apparently visible, each field has a separate area for its labels (see images).
In the example, both row and column fields have a different style for their field labels.
| Example in a compact layout | Example in a tabular layout |
|---|---|
 |
 |
Setup code
public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
pt.SetRowHeaderCaption("Pastry sales");pt.Theme = XLPivotTableTheme.None;
IXLPivotField nameRowField = pt.RowLabels.Add("Name");
IXLPivotField monthRowField = pt.RowLabels.Add("Month");
IXLPivotField countryColumnField = pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
nameRowField .StyleFormats.Label.Style.Font.SetBold().Font.SetFontSize(12);
monthRowField.StyleFormats.Label.Style.Fill.SetBackgroundColor(XLColor.LightBlue);
countryColumnField.StyleFormats.Label.Style.Fill.SetBackgroundColor(XLColor.Orange);
wb.SaveAs("example-style-pivot-table-labels.xlsx");Basic use of data area styling follows an established pattern. It is however the different in a first practical demonstration of dynamic styling criteria from the pivot styling options. You can set style for data values of a data values fields by first getting the pivot field (IXLPivotTable.RowLabels or IXLPivotTable.ColumnLabels) and then by setting the IXLPivotField.StyleFormats.DataValuesFormat.Style property for desired style.
Just like other places, when a cell is a part of multiple possible areas, the final style is created by combining all the eligible styles.
Specifying the data value area style doesn't affect styling for field labels or headers.
To be clear, by specifying a style for a row field data area, we mean that criteria to apply the style are all cells that are on a same row as the field label and are in a data area. Calculated number in grand total area are considered a data area. Same thing for the column fields.
That is the reason why in the image without any collapsed rows there aren't any bold data values. No cell with a number is on a row with name row field label. Once the rows for all months of Danish and Doughnut are collapsed, the number are automatically summed up to the respective labels and styling is reapplied. The cells for Danish (B7:E7) and Doughnut (B12:E12) are no longer on a row with label for any month, so there isn't a light blue background. On the other hand, they are not on a row with a label of a Name field and thus have applied style bold. This is a practical demonstration of how the styles of pivot table are applied dynamically (even with different layout form).
| Pivot table without any collapsed rows | Pivot table with collapses rows |
|---|---|
 |
 |
| Pivot table without collapsed rows in tabular form | Pivot table with collapsed rows in tabular form |
 |
 |
Setup code
public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
pt.SetRowHeaderCaption("Pastry sales");pt.Theme = XLPivotTableTheme.None;
IXLPivotField nameRowField = pt.RowLabels.Add("Name");
IXLPivotField monthRowField = pt.RowLabels.Add("Month");
pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
nameRowField.StyleFormats.DataValuesFormat.Style.Font.Bold = true;
monthRowField.StyleFormats.DataValuesFormat.Style.Fill.BackgroundColor = XLColor.LightBlue;
wb.SaveAs("example-style-pivot-table-data-values-rows.xlsx");Subtotals at the top of the group is a function that calculates something from individual rows of the group and displays it next to the field that was used to define the group.
In order to use subtotals, pivot table need at least two levels of fields on the chosen axis. Without two levels, there is nothing to group. To enable subtotal, call IXLPivotField.AddSubtotal(XLSubtotalFunction) method with a selected function from the XLSubtotalFunction enum.
Note that styling the top subtotal is same as styling the data values of a row/column field. Styling bottom subtotal requires different API bottom subtotal adds a new cells to the pivot table.
If the field with a top subtotal is collapsed, the calculated subtotal data from the group row are replaced by a sum.
| Data values are from subtotal function | Data values for collapsed rows is a sum |
|---|---|
 |
 |
Setup code
public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
pt.SetRowHeaderCaption("Pastry sales");pt.Theme = XLPivotTableTheme.None;
pt.SetSubtotals(XLPivotSubtotals.AtTop);
IXLPivotField nameRowField = pt.RowLabels.Add("Name");
pt.RowLabels.Add("Month");
pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
nameRowField
.SetSubtotalsAtTop(true) // By default, subtotal is always the top
.AddSubtotal(XLSubtotalFunction.Average);
nameRowField.StyleFormats.DataValuesFormat.Style.Fill.SetBackgroundColor(XLColor.LightBlue);
wb.SaveAs("example-style-pivot-table-subtotal-top.xlsx");Subtotals require at least two fields in one axis. Once there are multiple fields, you can take any non-last and consider all rows within the field a group. The values of group rows can then summed though a subtotal function and displayed in a subtotal row/column.
To style the subtotal row/column, it is necessary to
- Enable subtotals for pivot table (default value is
XLPivotSubtotals.DoNotShow) by settingIXLPivottable.SetSubtotals(XLPivotSubtotals.AtBottom) - For each non-last row/column field in an axis call
IXLPivotField.SetSubtotalsAtTop(false) - Style the field with subtotal from previous step by modifying the
IXLPivotField.StyleFormats.Subtotal.Style
| Styled subtotal rows without collapsed rows | Styled subtotal with collapsed rows |
|---|---|
 |
 |
Setup code
public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
ptSheet.SetTabActive();
pt.SetRowHeaderCaption("Pastry sales");pt.Theme = XLPivotTableTheme.None;
IXLPivotField nameRowField = pt.RowLabels.Add("Name");
pt.RowLabels.Add("Month");
pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
pt.SetSubtotals(XLPivotSubtotals.AtBottom); // For bottom subtotal has to be set to AtBottom value
nameRowField
.SetSubtotalCaption("Sum of orders")
.SetSubtotalsAtTop(false) // For bottom subtotal has to be set to false value
// .AddSubtotal(XLSubtotalFunction.Average) Bug - subtotal styling works only for default subtotal function (sum), fix per 18.10.2.1
;
nameRowField.StyleFormats.Subtotal.Style.Fill.SetBackgroundColor(XLColor.LightBlue);
wb.SaveAs("example-style-pivot-table-subtotal-bottom.xlsx");Pivot table styling allows to select a combination of some columns and rows. ClosedXML has an API for that. Take an axis field from IXLPivotTable.Rowlabels/IXLPivotTable.ColumnLabels and on the IXLPivotStyleFormats property of IXLPivotField.StyleFormats.DataValuesFormat call a method AndWith(IXLPivotField, Predicate<object>). The predicate is there to select only certain cells of the field. The value received by the predicate is a value of a cell in the column/row of the field. The method AndWith is not limited to other axis, see further examples.
In the example, we select only cells that are in
- a row for field Month that has a value Jun or May and
- a column of a field Country with a value Botswana
Setup code
public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "Botswana", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "Botswana", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "Botswana", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
ptSheet.SetTabActive();
pt.SetRowHeaderCaption("Pastry sales");pt.Theme = XLPivotTableTheme.None;
IXLPivotField nameRowField = pt.RowLabels.Add("Name");
IXLPivotField monthColumnField = pt.RowLabels.Add("Month");
IXLPivotField countryColumnField = pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
nameRowField.StyleFormats.DataValuesFormat
.AndWith(countryColumnField, cellValue => cellValue == "Botswana")
.AndWith(monthColumnField, cellValue => cellValue == "May" || cellValue == "Jun")
.Style.Fill.SetBackgroundColor(XLColor.LightBlue).Font.SetBold(true);
wb.SaveAs("example-style-pivot-table-data-value-combination-of-row-and-column.xlsx");Pivot table can have more than one value at the intersection of a row and a column. To style each value field differently, use the IXLPivotField.StyleFormats.DataValuesFormat.ForValueField method. This method says that only cells laying on the axis field that also belong to the value field should be styled. Conditions for axis field can be further refined by using AndWith method and its predicate.
In the example, the pivot table has two values: average quality and a total number of orders. we want to highlight cells with a value of quality at the end of quarter, but keep rest unstylized. We do that by combining row field Month with a value of Jun and further restrict it to only value field Quality.
Setup code
public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", "Botswana", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", "Botswana", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", "Botswana", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
ptSheet.SetTabActive();
pt.SetRowHeaderCaption("Pastry sales");pt.Theme = XLPivotTableTheme.None;
pt.RowLabels.Add("Name");
IXLPivotField monthColumnField = pt.RowLabels.Add("Month");
pt.ColumnLabels.Add("Country");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
var qualityValueField = pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Average);
monthColumnField.StyleFormats.DataValuesFormat
.AndWith(monthColumnField, cellValue => cellValue == "Jun")
.ForValueField(qualityValueField)
.Style.Fill.SetBackgroundColor(XLColor.Orange);
wb.SaveAs("example-style-pivot-table-combination-axis-field-and-value-field.xlsx");ClosedXML can modify the style of a row grand total area of a table. Use the XLPivotTable.StyleFormats.RowGrandTotalFormats.ForElement API for that. It's necessary to specify what part of the row grand total should the style be applied. The following example applies
- large font size to the whole row
- red color to the row grand total label area
- green color to the row grand total data area
Setup code
private record Pastry(string Name, int? Code, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate);
var pastries = new[]
{
new Pastry("Croissant", 101, 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", 101, 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", 101, 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", 102, 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", 102, 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", 102, 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", 103, 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", 103, 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", 103, 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", 104, 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", 104, 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", 104, 221, 24.76, "Jun", new DateTime(2016, 06, 21)),
// Deliberately add different casings of same string to ensure pivot table doesn't duplicate it.
new Pastry("Scone", 105, 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", 105, 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", 105, 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
// For ContainsBlank and integer rows/columns test
new Pastry("Scone", null, 255, 18.4, null, null),
};using var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("PastrySalesData");
var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
IXLWorksheet ptSheet = wb.Worksheets.Add("pivot");
IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange());
pt.Theme = XLPivotTableTheme.None;
pt.SetRowHeaderCaption("Pastry sales");
pt.RowLabels.Add("Name");
pt.ColumnLabels.Add("Month");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
pt.StyleFormats.RowGrandTotalFormats.ForElement(XLPivotStyleFormatElement.All).Style.Font.FontSize = 25;
pt.StyleFormats.RowGrandTotalFormats.ForElement(XLPivotStyleFormatElement.Label).Style.Fill.BackgroundColor = XLColor.Red;
pt.StyleFormats.RowGrandTotalFormats.ForElement(XLPivotStyleFormatElement.Data).Style.Fill.BackgroundColor = XLColor.Green;
wb.SaveAs("example-style-pivot-table-grand-total.xlsx");Setup code