using ClosedXML.Excel;
using System;
using System.Collections.Generic;
namespace ClosedXML_Examples
{
public class PivotTables : IXLExample
{
private class Pastry
{
public Pastry(string name, int numberOfOrders, double quality, string month, DateTime bakeDate)
{
Name = name;
NumberOfOrders = numberOfOrders;
Quality = quality;
Month = month;
BakeDate = bakeDate;
}
public string Name { get; set; }
public int NumberOfOrders { get; set; }
public double Quality { get; set; }
public string Month { get; set; }
public DateTime BakeDate { get; set; }
}
public void Create(String filePath)
{
var pastries = new List<Pastry>
{
new Pastry("Croissant", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", 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", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};
using (var wb = new XLWorkbook())
{
var sheet = wb.Worksheets.Add("PastrySalesData");
// Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1
var source = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);
sheet.Columns().AdjustToContents();
// Create a range that includes our table, including the header row
var range = source.DataRange;
var header = sheet.Range(1, 1, 1, 3);
var dataRange = sheet.Range(header.FirstCell(), range.LastCell());
IXLWorksheet ptSheet;
IXLPivotTable pt;
#region Pivots
for (int i = 1; i <= 3; i++)
{
// Add a new sheet for our pivot table
ptSheet = wb.Worksheets.Add("pvt" + i);
// Create the pivot table, using the data from the "PastrySalesData" table
pt = ptSheet.PivotTables.AddNew("pvt", ptSheet.Cell(1, 1), dataRange);
// The rows in our pivot table will be the names of the pastries
pt.RowLabels.Add("Name");
if (i == 2) pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
// The columns will be the months
pt.ColumnLabels.Add("Month");
if (i == 3) pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
// The values in our table will come from the "NumberOfOrders" field
// The default calculation setting is a total of each row/column
pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfBearclaw")
.ShowAsPercentageFrom("Name").And("Bearclaw")
.NumberFormat.Format = "0%";
if (i > 1)
{
pt.Values.Add("Quality", "Sum of Quality")
.NumberFormat.SetFormat("#,##0.00");
}
if (i > 2)
{
pt.Values.Add("NumberOfOrders", "Sum of NumberOfOrders");
}
ptSheet.Columns().AdjustToContents();
}
#endregion Pivots
#region Different kind of pivot
ptSheet = wb.Worksheets.Add("pvtNoColumnLabels");
pt = ptSheet.PivotTables.AddNew("pvtNoColumnLabels", ptSheet.Cell(1, 1), dataRange);
pt.RowLabels.Add("Name");
pt.RowLabels.Add("Month");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);
pt.SetRowHeaderCaption("Pastry name");
#endregion Different kind of pivot
#region Pivot table with collapsed fields
ptSheet = wb.Worksheets.Add("pvtCollapsedFields");
pt = ptSheet.PivotTables.AddNew("pvtCollapsedFields", ptSheet.Cell(1, 1), dataRange);
pt.RowLabels.Add("Name").SetCollapsed();
pt.RowLabels.Add("Month").SetCollapsed();
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);
#endregion Pivot table with collapsed fields
#region Pivot table with a field both as a value and as a row/column/filter label
ptSheet = wb.Worksheets.Add("pvtFieldAsValueAndLabel");
pt = ptSheet.PivotTables.AddNew("pvtFieldAsValueAndLabel", ptSheet.Cell(1, 1), dataRange);
pt.RowLabels.Add("Name");
pt.RowLabels.Add("Month");
pt.Values.Add("Name").SetSummaryFormula(XLPivotSummary.Count);//.NumberFormat.Format = "#0.00";
#endregion Pivot table with a field both as a value and as a row/column/filter label
#region Pivot table with subtotals disabled
ptSheet = wb.Worksheets.Add("pvtHideSubTotals");
// Create the pivot table, using the data from the "PastrySalesData" table
pt = ptSheet.PivotTables.AddNew("pvtHidesubTotals", ptSheet.Cell(1, 1), dataRange);
// The rows in our pivot table will be the names of the pastries
pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel);
// The columns will be the months
pt.ColumnLabels.Add("Month");
pt.ColumnLabels.Add("Name");
// The values in our table will come from the "NumberOfOrders" field
// The default calculation setting is a total of each row/column
pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfBearclaw")
.ShowAsPercentageFrom("Name").And("Bearclaw")
.NumberFormat.Format = "0%";
pt.Values.Add("Quality", "Sum of Quality")
.NumberFormat.SetFormat("#,##0.00");
pt.Subtotals = XLPivotSubtotals.DoNotShow;
pt.SetColumnHeaderCaption("Measures");
ptSheet.Columns().AdjustToContents();
#endregion Pivot table with subtotals disabled
#region Pivot Table with filter
ptSheet = wb.Worksheets.Add("pvtFilter");
pt = ptSheet.PivotTables.AddNew("pvtFilter", ptSheet.Cell(1, 1), dataRange);
pt.RowLabels.Add("Month");
pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);
pt.ReportFilters.Add("Name")
.AddSelectedValue("Scone")
.AddSelectedValue("Doughnut");
pt.ReportFilters.Add("Quality")
.AddSelectedValue(5.19);
pt.ReportFilters.Add("BakeDate")
.AddSelectedValue(new DateTime(2017, 05, 03));
#endregion Pivot Table with filter
wb.SaveAs(filePath);
}
}
}
}
