Create Excel PivotTable by DataTable PivotData C# Examples

EPPlus library can generate Excel files (xlsx) from C# code. It provides simple API for exporting DataTable to Excel Sheet and configuring PivotTable by this data:

DataTable ordersTable; // facts table with columns "product", "year", "amount"
ExcelWorksheet wsData; // worksheet for dataset
ExcelWorksheet wsPivotTable; // worksheet for pivot table
var rangePivotTable = wsData.Cells["A1"].LoadFromDataTable( ordersTable, true );
var pivotTable = wsPivotTable.PivotTables.Add(ws.Cells[1,1], rangePivotTable, "pvtTable");
pivotTable.DataFields.Add(pivotTable.Fields["amount"]).Function = 

This works well for small datasets, but in many cases facts table cannot be directly exported to Excel worksheet for number of reasons:

  • dataset is too large for exporting to Excel (hundreds of thousands rows or more)
  • facts table contains sensitive data (for example, staff salaries used for calculating total expenses)

The workaround is exporting to Excel only aggregated data and NReco PivotData library can help you with that.

1. Aggregate data for Excel Pivot Table in C#
var ordersPvtData = new PivotData(new[] { "product", "year" },
	new SumAggregatorFactory("amount"));
ordersPvtData.ProcessData( new DataTableReader(ordersTable) );
2. Configure PivotTable model
var pvtTbl = new PivotTable(
	new[] {"country"}, //rows
	new[] { "year"}, // columns
3. Export pivot table model to Excel PivotTable
var pkg = new ExcelPackage();
pkg.Compression = CompressionLevel.Default;
var wsPvt = pkg.Workbook.Worksheets.Add( "Pivot Table" );
var wsData = pkg.Workbook.Worksheets.Add( "Source Data" );

var pvtTbl = new PivotTable(
		new[] {"country"}, //rows
		new[] { "year"}, // columns
var excelPvtTblWr = new ExcelPivotTableWriter(wsPvt, wsData);
using (var excelFs = new FileStream("result.xlsx", FileMode.Create, FileAccess.Write)) { 

This code can export any PivotTable model: see sample Excel file with PivotTable produced in this way. Output may be easily customized by modifying ExcelPivotTableWriter code (add header or footer, format cells, add charts etc)

ExcelPivotTableWriter class is available in the "ExcelPivotTable" example; you can get it by downloading NReco PivotData Free package.