Create Pivot Table PivotData C# Examples

PivotTable model

Once you have in-memory data cube represented by PivotData class instance (see data cube basics), you can create pivot table model with PivotTable class:

PivotData pvtData; // lets assume data cube has dimensions "a", "b", "c"
var pvtTbl = new PivotTable(
		new[] { "a", "b" },  // dimension(s) for table rows. List may be empty.
		new[] { "c" }, // dimension(s) for table columns
		pvtData );

PivotTable provides the following API for accessing pivot table values:

Render PivotTable to HTML table with custom C# code

The following code snippet illustrates how to generate simple HTML table by PivotTable model:

var sb = new StringBuilder();
// column labels
foreach (var colKey in pvtTbl.ColumnKeys) {
	sb.AppendFormat("<th>{0}</th>", colKey.ToString() );
// rows
for (var r=0; r<pvtTbl.RowKeys.Length; r++) { 
	var rowKey = pvtTbl.RowKeys[r];
	sb.AppendFormat("<th>{0}</th>", rowKey.ToString() ); // row label
	for (var c=0; c<pvtTbl.ColumnKeys.Length; c++) {
		sb.AppendFormat("<td>{0}</td>", pvtTbl[r,c].Value );
	sb.AppendFormat("<td>{0}</td>", pvtTbl[r,null].Value ); // row total
// row for column totals
for (var c = 0; c < pvtTbl.ColumnKeys.Length; c++) {
	sb.AppendFormat("<td>{0}</td>", pvtTbl[null, c].Value );
sb.AppendFormat("<td>{0}</td>", pvtTbl[null,null].Value); // grand total

This code doesn't perform labels grouping (several dimensions for rows and/or columns), handling of composite aggregators (several measures) and other advanced pivot table features. You may enhance it to match your needs, or use existing PivotData Toolkit writers (see below).

Pivot table writers Toolkit components

Instead of writing custom code for rendering pivot table by PivotTable model you can use existing components from PivotData Toolkit (NReco.PivotData.Extensions assembly):

Class Description Example
PivotTableHtmlWriter Renders to HTML table. Supports labels grouping, composite aggregators (multiple measures), sub-totals, formatting options.
var outputWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);
var pvtTblHtml = outputWr.ToString();
PivotTableCsvWriter Exports pivot table to CSV format (with help of CSVHelper library).
var outputWr = new StringWriter();
var pvtCsvWr = new PivotTableCsvWriter(outputWr);
PivotTableExcelWriter Exports pivot table to Excel format (with help of EPPlus library).
Stream output = new MemoryStream();
var pvtExcelWr = new PivotTableExcelWriter(output, "Worksheet1");
PivotTableJsonWriter Exports pivot table to JSON structure. Useful for getting data for rendering pivot charts with js libraries.
var outputWr = new StringWriter();
var pvtJsonWr = new PivotTableJsonWriter(outputWr);
PivotTableDataTableWriter Exports pivot table to System.Data.DataTable. Useful for binding pivot table to 3-rd party grid view controls.
var pvtDataTblWr = new PivotTableDataTableWriter();
DataTable dataTbl = pvtDataTblWr.Write(pvtTbl);
There writers are used in the online demo.
You can get more usage examples by downloading PivotData Examples pack.