Create HTML Pivot Table PivotData C# Examples

Pivot table data model

Once you have in-memory data cube represented by IPivotData instance (see data cube basics), you can create pivot table (crosstab) 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 );

Pivot table values may be accessed with the the following API:

• PivotTable.RowKeys: array of ValueKey objects that represent table row keys.
• PivotTable.ColumnKeys: array of ValueKey objects that represent table column keys.
• PivotTable[int? row, int? col] or PivotTable.GetValue: get pivot table value (IAggregator) at the specified row x column intersection. You can use null for row and/or column to get sub-totals and grand total values.

Render pivot table to HTML with custom C# code

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).

Complete example code is here: NReco.PivotData github repository

Pivot table writers Toolkit components

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

var outputWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);
pvtHtmlWr.Write(pvtTbl);
var pvtTblHtml = outputWr.ToString();

var outputWr = new StringWriter();
var pvtCsvWr = new PivotTableCsvWriter(outputWr);
pvtCsvWr.Write(pvtTbl);

Stream output = new MemoryStream();
var pvtExcelWr = new PivotTableExcelWriter(output, "Worksheet1");
pvtExcelWr.Write(pvtTbl);

var outputWr = new StringWriter();
var pvtJsonWr = new PivotTableJsonWriter(outputWr);
pvtJsonWr.Write(pvtTbl);

var pvtDataTblWr = new PivotTableDataTableWriter();
DataTable dataTbl = pvtDataTblWr.Write(pvtTbl);