r/EasyXLS • u/EasyXLS • 7d ago
How to Create a Pivot Table in Excel from C# Using EasyXLS
Pivot tables are one of the most powerful features in Microsoft Excel. They allow you to quickly summarize, analyze, organize, and present large amounts of data by grouping and aggregating information without modifying the original dataset.
Using the EasyXLS Excel Library, you can create Excel workbooks containing pivot tables directly from your C# applications. This enables you to generate professional reports automatically without requiring Microsoft Excel to be installed on the server or client machine.
Why Use Pivot Tables?
Pivot tables are commonly used to:
- Summarize large datasets
- Calculate totals, averages, counts, minimums, and maximums
- Group data by categories
- Analyze sales and financial reports
- Create dynamic business dashboards
- Generate management reports
Instead of manually building reports in Excel, your C# application can generate them automatically.
Benefits of EasyXLS
EasyXLS provides an easy way to create Excel files programmatically without Microsoft Office automation.
Some advantages include:
- No Microsoft Excel installation required
- Supports XLSX, XLSB and XLS formats
- Fast processing of large worksheets
- Easy API for Excel manipulation
- Supports charts, formatting, formulas, images, pivot tables, and many other Excel features
Prerequisites & Setup
Before getting started, you will need the following:
- EasyXLS Library: Download and install the EasyXLS Excel library from the EasyXLS site.
- Development Environment: Visual Studio for .NET projects or another IDE .
- Install EasyXLS library: Make sure the EasyXLS library is installed in your development environment. For .NET, you can download and add it via NuGet or reference the EasyXLS DLL in your project.
- Setup the project and get started: Include EasyXLS into project according to your programming language. Find details about getting started with EasyXLS.
Create a Workbook and Worksheet
Create a workbook object and a sheet.
ExcelDocument workbook = new ExcelDocument();
workbook.easy_addWorksheet("Sheet1");
ExcelWorksheet sheet = (ExcelWorksheet) workbook.easy_getSheet("Sheet1");
Populate the Worksheet with Data
Add sample data that will be used as the pivot table source.
ExcelTable table = sheet.easy_getExcelTable();
//Report header
table.easy_getCell(0,0).setValue("Sale agent");
table.easy_getCell(0,1).setValue("Sale country");
table easy_getCell(0,2).setValue("Month");
table.easy_getCell(0,3).setValue("Year");
table.easy_getCell(0,4).setValue("Sale amount");
// Report values
table.easy_getCell(1,0).setValue("John Down");
table.easy_getCell(1,1).setValue("USA");
table.easy_getCell(1,2).setValue("June");
table.easy_getCell(1,3).setValue("2010");
table.easy_getCell(1,4).setValue("550");
table.easy_getCell(2,0).setValue("Scott Valey");
table.easy_getCell(2,1).setValue("United Kingdom");
table.easy_getCell(2,2).setValue("June");
table.easy_getCell(2,3).setValue("2010");
table.easy_getCell(2,4).setValue("2300");
table.easy_getCell(3,0).setValue("John Down");
table.easy_getCell(3,1).setValue("USA");
table.easy_getCell(3,2).setValue("July");
table.easy_getCell(3,3).setValue("2010");
table.easy_getCell(3,4).setValue("3100");
table.easy_getCell(4,0).setValue("John Down");
table.easy_getCell(4,1).setValue("USA");
table.easy_getCell(4,2).setValue("June");
table.easy_getCell(4,3).setValue("2011");
table.easy_getCell(4,4).setValue("1050");
Create a Pivot Table in C
Create an Excel pivot table containing above sample data.
ExcelPivotTable xlsPivotTable = new ExcelPivotTable();
xlsPivotTable.setName("Sales");
xlsPivotTable.setSourceRange("Sheet1!$A$1:$E$5",workbook);
xlsPivotTable.setLocation("G3:M15");
xlsPivotTable.addFieldToRowLabels("Sale agent");
xlsPivotTable.addFieldToColumnLabels("Year");
xlsPivotTable.addFieldToValues("Sale amount","Sale amount per year", PivotTable.SUBTOTAL_SUM);
xlsPivotTable.addFieldToReportFilter("Sale country");
xlsPivotTable.setOutlineForm();
sheet.easy_addPivotTable(xlsPivotTable);
More about how to create a pivot table in Excel from C#.
Save the Excel File
Finally, save the workbook.
workbook.easy_WriteXLSXFile("C:\\Excel Pivot Table.xlsx");
Conclusion
Pivot tables are one of Excel's most valuable reporting tools, allowing users to summarize and analyze large datasets with just a few clicks. By using the EasyXLS Excel Library, you can generate Excel workbooks containing pivot tables directly from your C# applications without relying on Microsoft Office automation.
Whether you're building financial reports, sales dashboards, inventory summaries, or business analytics solutions, EasyXLS provides a straightforward API for creating pivot tables programmatically, helping you automate report generation while delivering professional Excel documents.
For more information, refer to the EasyXLS documentation, which provides detailed guidance on various features and capabilities of the library.
