csharp-pivot

Pivot data with aggregation and output to HTML

Example with one pivot

Say your data is a list of animals

Family	Name	Size	Quantity
Felidae	Cat	Small	1
Felidae	Lion	Tall	1
Felidae	Tiger	Tall	1
Canidae	Dog	Small	1
Canidae	Wolf	Tall	1
Canidae	Racoon	Small	1
Canidae	Fox	Small	1

1. First, represent your data as a List of Row's

   var animals = new List<Row> 
   {
       new Row 
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Cat" },
               { "Family", "Felidae" },
               { "Size", "Small" },
               { "Quantity", 1 },
           }
       },
       new Row
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Lion" },
               { "Family", "Felidae" },
               { "Size", "Tall" },
               { "Quantity", 1 },
           }
       },
       new Row
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Tiger" },
               { "Family", "Felidae" },
               { "Size", "Tall" },
               { "Quantity", 1 },
           }
       },
       new Row
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Dog" },
               { "Family", "Canidae" },
               { "Size", "Small" },
               { "Quantity", 1 },
           }
       },
       new Row
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Wolf" },
               { "Family", "Canidae" },
               { "Size", "Tall" },
               { "Quantity", 1 },
           }
       },
       new Row
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Racoon" },
               { "Family", "Canidae" },
               { "Size", "Small" },
               { "Quantity", 1 },
           }
       },
       new Row
       {
           Fields = new Dictionary<string, object> 
           {
               { "Name", "Fox" },
               { "Family", "Canidae" },
               { "Size", "Small" },
               { "Quantity", 1 },
           }
       },
   };
   

2. Initialize grid.
   In this example we are going to group by "Size"

   Grid grid = new Grid(originalData: animals,
       columnDefinitions: new ColumnDefinition[] { 
           new ColumnDefinition { HeaderName = "Family", Field = "Family", CssClass = "", Width = 100 },
           new ColumnDefinition { HeaderName = "Name", Field = "Name", CssClass = "", Width = 100 },
           new ColumnDefinition { HeaderName = "Size", Field = "Size", CssClass = "", Width = 100 },
           new ColumnDefinition { HeaderName = "Qty", Field = "Quantity", CssClass = "align-right", Width = 40 }
       },
       pivotColumns: new string[] { "Size" },
       aggregatedColumns: new string[] { "Quantity" });
   

3. Get html

   string html = grid.GetHtml();
   

4. Output:

Family	Name	Size	Qty
		Small	4
Felidae	Cat	Small	1
Canidae	Dog	Small	1
Canidae	Racoon	Small	1
Canidae	Fox	Small	1
		Tall	3
Felidae	Lion	Tall	1
Felidae	Tiger	Tall	1
Canidae	Wolf	Tall	1

Example with multiple pivots

With the same source data, this:

   pivotColumns: new string[] { "Family", "Size" },

Will output this:

Family	Name	Size	Qty
Felidae			3
Felidae		Small	1
Felidae	Cat	Small	1
Felidae		Tall	2
Felidae	Lion	Tall	1
Felidae	Tiger	Tall	1
Canidae			4
Canidae		Small	3
Canidae	Dog	Small	1
Canidae	Racoon	Small	1
Canidae	Fox	Small	1
Canidae		Tall	1
Canidae	Wolf	Tall	1

TO-DO:

• Format numbers
• Format dates
• Allow column grouping in header (eg. Column "Birth": Column "Day" + Column "Month" + Column "Year")
• Allow other types of aggregation: average, min, max
• Sorting