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.RowFields.Add(pivotTable.Fields["product"]);
pivotTable.ColumnFields.Add(pivotTable.Fields["year"]);
pivotTable.DataFields.Add(pivotTable.Fields["amount"]).Function = 
	OfficeOpenXml.Table.PivotTable.DataFieldFunctions.Sum;

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
	ordersPvtData
);

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
		ordersPvtData
	);
var excelPvtTblWr = new ExcelPivotTableWriter(wsPvt, wsData);
excelPvtTblWr.Write(pvtTbl);
using (var excelFs = new FileStream("result.xlsx", FileMode.Create, FileAccess.Write)) { 
	pkg.SaveAs(excelFs);
}

Complete example code is here: NReco.PivotData github repository

This sample can be used for exporting 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)