Grouping data in a pivot table simplifies data analysis by consolidating similar items into meaningful categories. For example, you can group dates into months or years to see trends over time, or group numbers into ranges like price levels or age groups for easier comparison and analysis. In this article, we will demonstrate how to group data in Excel pivot tables based on dates and numbers in C# using Spire.XLS for .NET.
- Group Pivot Table Data in Excel Based on Dates in C#
- Group Pivot Table Data in Excel Based on Numbers in C#
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Group Pivot Table Data in Excel Based on Dates in C#
The IPivotField.CreateGroup(DateTime start, DateTime end, PivotGroupByTypes[] groupByArray) method in Spire.XLS for .NET allows developers to group data in Excel pivot tables based on date and time. It requires three parameters: a start date time, an end date time, and an array of grouping categories specified by the PivotGroupByTypes enum.
The list below shows the categories that can be used when grouping by date and time:
- Days
- Months
- Quarters
- Years
- Seconds
- Minutes
- Hours
The steps below demonstrate how to group the data in a pivot table by date and time using Spire.XLS for .NET:
- Create an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Access the worksheet that contains the pivot table using the Workbook.Worksheets[] property.
- Get the pivot table using the Worksheet.PivotTables[] property.
- Get the specific pivot field that you want to group using the XlsPivotTable.PivotFields[] property.
- Create two instances of the DateTime class to specify the start date time and end date time.
- Create a PivotGroupByTypes array to specify the grouping categories, such as days and months.
- Group the data of the selected pivot field based on the specified grouping categories using the IPivotField.CreateGroup(DateTime start, DateTime end, PivotGroupByTypes[] groupByArray) method.
- Refresh the pivot table using the XlsPivotTable.IsRefreshOnLoad property.
- Save the result file using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
using System;
namespace GroupDataInPivotTableByDates
{
internal class Program
{
static void Main(string[] args)
{
// Create an instance of the Workbook class
Workbook workbook = new Workbook();
// Load an Excel file
workbook.LoadFromFile("Sample1.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
// Get the first pivot table in the worksheet
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;
// Get the "Date" pivot field
PivotField ptField = pt.PivotFields["Date"] as PivotField;
// Specify the start date time and end date time
DateTime start = new DateTime(2024, 1, 1);
DateTime end = new DateTime(2024, 10, 14);
// Create a PivotGroupByTypes array to specify the grouping categories, such as days and months
PivotGroupByTypes[] groupByTypes = new PivotGroupByTypes[]
{
PivotGroupByTypes.Days,
PivotGroupByTypes.Months
};
// Group the data in the pivot field based on the specified grouping categories
ptField.CreateGroup(start, end, groupByTypes);
// Refresh the pivot table
pt.Cache.IsRefreshOnLoad = true;
// Save the result file
workbook.SaveToFile("GroupPivotTableDataByDates.xlsx", FileFormat.Version2016);
workbook.Dispose();
}
}
}
Group Pivot Table Data in Excel Based on Numbers in C#
In addition to grouping based on date and time, Spire.XLS for .NET also enables developers to group pivot table data based on numeric values using another overload of the CreateGroup() method: CreateGroup(double startValue, double endValue, double intervalValue). The detailed steps are as follows.
- Create an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Access the worksheet that contains the pivot table using the Workbook.Worksheets[] property.
- Get the pivot table using the Worksheet.PivotTables[] property.
- Get the specific pivot field that you want to group using the XlsPivotTable.PivotFields[] property.
- Group the data in the selected pivot field based on numeric values using the IPivotField.CreateGroup(double startValue, double endValue, double intervalValue) method.
- Calculate the pivot table data using the XlsPivotTable.CalculateData() method.
- Refresh the pivot table using the XlsPivotTable.IsRefreshOnLoad property.
- Save the result file using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
namespace GroupDataInPivotTableByNumbers
{
internal class Program
{
static void Main(string[] args)
{
// Create an instance of the Workbook class
Workbook workbook = new Workbook();
// Load an Excel file
workbook.LoadFromFile("Sample2.xlsx");
// Get the first worksheet
Worksheet pivotSheet = workbook.Worksheets[0];
// Get the first pivot table in the worksheet
XlsPivotTable pt = pivotSheet.PivotTables[0] as XlsPivotTable;
// Group data of the "SalesAmount" pivot field based on based on numeric values
PivotField ptField = pt.PivotFields["SalesAmount"] as PivotField;
ptField.CreateGroup(1500, 4500, 200);
// Calculate the pivot table data
pt.CalculateData();
// Refresh the pivot table
pt.Cache.IsRefreshOnLoad = true;
// Save the result file
workbook.SaveToFile("GroupPivotTableDataByNumbers.xlsx", FileFormat.Version2016);
workbook.Dispose();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
