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

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

var sb = new StringBuilder();
sb.Append("<table>");
// column labels
sb.Append("<tr>");
foreach (var colKey in pvtTbl.ColumnKeys) {
	sb.AppendFormat("<th>{0}</th>", colKey.ToString() );
}
sb.Append("<th>Totals</th>");
sb.Append("</tr>");
// rows
for (var r=0; r<pvtTbl.RowKeys.Length; r++) { 
	var rowKey = pvtTbl.RowKeys[r];
	sb.Append("<tr>");
	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
	sb.Append("</tr>");
}
// row for column totals
sb.Append("<tr>");
sb.Append("<th>Totals</th>");
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
sb.Append("</tr>");
sb.Append("</table>");
Console.WriteLine(sb.ToString());

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 advanced Toolkit components

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

Writer 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);
pvtHtmlWr.Write(pvtTbl);
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);
pvtCsvWr.Write(pvtTbl);
PivotTableExcelWriter Exports pivot table to Excel format (with help of EPPlus library).
Stream output = new MemoryStream();
var pvtExcelWr = new PivotTableExcelWriter(output, "Worksheet1");
pvtExcelWr.Write(pvtTbl);
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);
pvtJsonWr.Write(pvtTbl);
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);
You may test these writers with online demo and get more usage examples by requesting PivotData Toolkit trial package.