Pivoting DataTable in asp.net

Displaying data in tabular form is an essential part of any application nowadays. But sometimes you need to display a huge amount of data in terms of number of rows. It becomes very difficult to analyse if the number of rows is huge. In such cases, you may wish to summarize your data in the other formats like charts, graphs, groups, pivots, etc. This articles presents a simplified way to pivot your data with an appropriate aggregate function so that you can enhance your reports easily.

Pivot.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

// Written by Anurag Gandhi.
// Url: http://www.gandhisoft.com
// Contact me at: soft.gandhi@gmail.com

/// <summary>
/// Pivots the data
/// </summary>
public class Pivot
{
private DataTable _SourceTable = new DataTable();
public Pivot(DataTable SourceTable)
{
_SourceTable = SourceTable;
}

/// <summary>
/// Pivots the DataTable based on provided RowField, DataField, Aggregate Function and ColumnFields.//
/// </summary>
/// <param name="RowField">The column name of the Source Table which you want to spread into rows</param>
/// <param name="DataField">The column name of the Source Table which you want to spread into Data Part</param>
/// <param name="Aggregate">The Aggregate function which you want to apply in case matching data found more than once</param>
/// <param name="ColumnFields">The List of column names which you want to spread as columns</param>
/// <returns>A DataTable containing the Pivoted Data</returns>
public DataTable PivotData(string RowField, string DataField, AggregateFunction Aggregate, params string[] ColumnFields)
{
DataTable dt = new DataTable();
string Separator = ".";
var RowList = (from x in _SourceTable.AsEnumerable() select new { Name = x.Field<object>(RowField) }).Distinct();
// Gets the list of columns .(dot) separated.
var ColList = (from x in _SourceTable.AsEnumerable()
select new { Name = ColumnFields.Select(n => x.Field<object>(n))
.Aggregate((a, b) => a += Separator + b.ToString()) })
.Distinct()
.OrderBy(m => m.Name);

dt.Columns.Add(RowField);
foreach (var col in ColList)
{
dt.Columns.Add(col.Name.ToString()); // Cretes the result columns.//
}

foreach (var RowName in RowList)
{
DataRow row = dt.NewRow();
row[RowField] = RowName.Name.ToString();
foreach (var col in ColList)
{
string strFilter = RowField + " = '" + RowName.Name + "'";
string[] strColValues = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None);
for (int i = 0; i < ColumnFields.Length; i++)
strFilter += " and " + ColumnFields[i] + " = '" + strColValues[i] + "'";
row[col.Name.ToString()] = GetData(strFilter, DataField, Aggregate);
}
dt.Rows.Add(row);
}
return dt;
}
/// <summary>
/// Retrives the data for matching RowField value and ColumnFields values with Aggregate function applied on them.
/// </summary>
/// <param name="Filter">DataTable Filter condition as a string</param>
/// <param name="DataField">The column name which needs to spread out in Data Part of the Pivoted table</param>
/// <param name="Aggregate">Enumeration to determine which function to apply to aggregate the data</param>
/// <returns></returns>
private object GetData(string Filter, string DataField, AggregateFunction Aggregate)
{
try
{
DataRow[] FilteredRows = _SourceTable.Select(Filter);
object[] objList = FilteredRows.Select(x => x.Field<object>(DataField)).ToArray();

switch (Aggregate)
{
case AggregateFunction.Average:
return GetAverage(objList);
case AggregateFunction.Count:
return objList.Count();
case AggregateFunction.Exists:
return (objList.Count() == 0) ? "False" : "True";
case AggregateFunction.First:
return GetFirst(objList);
case AggregateFunction.Last:
return GetLast(objList);
case AggregateFunction.Max:
return GetMax(objList);
case AggregateFunction.Min:
return GetMin(objList);
case AggregateFunction.Sum:
return GetSum(objList);
default:
return null;
}
}
catch (Exception ex)
{
return "#Error";
}
return null;
}

private object GetAverage(object[] objList)
{
return objList.Count() == 0 ? null : (object)(Convert.ToDecimal(GetSum(objList)) / objList.Count());
}
private object GetSum(object[] objList)
{
return objList.Count() == 0 ? null : (object)(objList.Aggregate(new decimal(), (x, y) => x += Convert.ToDecimal(y)));
}
private object GetFirst(object[] objList)
{
return (objList.Count() == 0) ? null : objList.First();
}
private object GetLast(object[] objList)
{
return (objList.Count() == 0) ? null : objList.Last();
}
private object GetMax(object[] objList)
{
return (objList.Count() == 0) ? null : objList.Max();
}
private object GetMin(object[] objList)
{
return (objList.Count() == 0) ? null : objList.Min();
}
}

public enum AggregateFunction
{
Count = 1,
Sum = 2,
First = 3,
Last = 4,
Average = 5,
Max = 6,
Min = 7,
Exists = 8
}

ExcelLayer.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.OleDb;

// Written by Anurag Gandhi.

// Url: http://www.gandhisoft.com

// Contact me at: soft.gandhi@gmail.com

/// <summary>

/// Acts as a DataBase Layer

/// </summary>

public class ExcelLayer

{

public ExcelLayer()

{

// TODO: Add constructor logic here

}

/// <summary>

/// Retireves the data from Excel Sheet to a DataTable.

/// </summary>

/// <param name="FileName">File Name along with path from the root folder.</param>

/// <param name="TableName">Name of the Table of the Excel Sheet. Sheet1$ if no table.</param>

/// <returns></returns>

public static DataTable GetDataTable(string FileName, string TableName)

{

try

{

string strPath = HttpContext.Current.Request.PhysicalApplicationPath + FileName;

DataSet ds = new DataSet();

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; " + "Data Source=" + strPath + "; " + "Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + TableName + "] where IsActive = 1", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

objAdapter1.Fill(ds);

objConn.Close();

return ds.Tables[0];

}

catch (Exception ex)

{

//Log your exception here.//

return (DataTable)null;

}

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Pivot Data Samples</title>

body

{

font-family:Verdana, Tahoma;

font-size:9pt;

}

{

font-family:Verdana, Tahoma;

font-size:14pt;

}

{

color:#667755;

font-family:Verdana, Tahoma;

font-size:12pt;

}

</style>

</head>

<body>

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

<h1>Summary Report: Employee Salary (Annual)</h1>

<cc1:TabContainer ID="TabContainer1" runat="server">

<cc1:TabPanel ID="Panel1" runat="server" HeaderText="Raw Data">

<asp:GridView ID="grdRawData" runat="server" BackColor="White"

BorderColor="#cEcFcE" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"

ForeColor="Black">

</asp:GridView>

</ContentTemplate>

</cc1:TabPanel>

<cc1:TabPanel ID="TabPanel1" runat="server" HeaderText="First Pivot">

<h2>No. of Employees: Comapany vs Year</h2>

<asp:GridView ID="grdCompanyYear" runat="server" BackColor="White"

BorderColor="#cEcFcE" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"

ForeColor="Black">

</asp:GridView>

<br />

<h2>Least Salary: Comapany vs Year</h2>

<asp:GridView ID="grdLeastCompanyYear" runat="server" BackColor="White"

BorderColor="#cEcFcE" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"

ForeColor="Black">

</asp:GridView>

</ContentTemplate>

</cc1:TabPanel>

<cc1:TabPanel ID="TabPanel2" runat="server" HeaderText="Second Pivot">

<h2>Highest Salary: Designation vs Comapany and Year</h2>

<asp:GridView ID="grdDesignationCompanyYear" runat="server" BackColor="White"

BorderColor="#cEcFcE" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"

ForeColor="Black" onrowcreated="grdPivot2_RowCreated">

</asp:GridView>

<br />

<h2>Average Salary: Designation vs Comapany and Year</h2>

<asp:GridView ID="grdDesignationCompanyYearAvg" runat="server" BackColor="White"

BorderColor="#cEcFcE" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"

ForeColor="Black" onrowcreated="grdPivot2_RowCreated">

</asp:GridView>

</ContentTemplate>

</cc1:TabPanel>

<cc1:TabPanel ID="Panel2" runat="server" HeaderText="Third Pivot">

<h2>Average Salary: Designation vs Comapany, Department and Year</h2>

<asp:GridView ID="grdPivot" runat="server" BackColor="White"

BorderColor="#cEcFcE" BorderStyle="Solid" BorderWidth="1px" CellPadding="4"

ForeColor="Black" onrowcreated="grdPivot3_RowCreated">

</asp:GridView>

</ContentTemplate>

</cc1:TabPanel>

</cc1:TabContainer>

</form>

</body>

</html>

Default.aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

// Written by Anurag Gandhi.

// Url: http://www.gandhisoft.com

// Contact me at: soft.gandhi@gmail.com

public partial class _Default : System.Web.UI.Page

{

string _Separator = ".";

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridView();

}

//Binds all the GridView used in the page.//

private void BindGridView()

{

// Retrieve the data table from Excel Data Source.

DataTable dt = ExcelLayer.GetDataTable("_Data\\DataForPivot.xls", "Sheet1$");

Pivot pvt = new Pivot(dt);

grdRawData.DataSource = dt;

grdRawData.DataBind();

grdCompanyYear.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Count, "Year");

grdCompanyYear.DataBind();

grdLeastCompanyYear.DataSource = pvt.PivotData("Company", "CTC", AggregateFunction.Min, "Year");

grdLeastCompanyYear.DataBind();

grdDesignationCompanyYear.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Max, "Company", "Year");

grdDesignationCompanyYear.DataBind();

grdDesignationCompanyYearAvg.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Average, "Company", "Year");

grdDesignationCompanyYearAvg.DataBind();

grdPivot.DataSource = pvt.PivotData("Designation", "CTC", AggregateFunction.Max, "Company", "Department", "Year");

grdPivot.DataBind();

}

protected void grdPivot2_RowCreated(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.Header)

MergeHeader((GridView)sender, e.Row, 2);

}

protected void grdPivot3_RowCreated(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.Header)

MergeHeader((GridView)sender, e.Row, 3);

}

/// <summary>

/// Function used to Create and Merge the Header Cells based on the Pivot conditions.

/// </summary>

/// <param name="gv">GridView</param>

/// <param name="row">Header Row of the GridView</param>

/// <param name="PivotLevel">The no. of ColumnFields used to Pivot the data</param>

private void MergeHeader(GridView gv, GridViewRow row, int PivotLevel)

{

for (int iCount = 1; iCount <= PivotLevel; iCount++)

{

GridViewRow oGridViewRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);

var Header = (row.Cells.Cast<TableCell>()

.Select(x => GetHeaderText(x.Text, iCount, PivotLevel)))

.GroupBy(x => x);

foreach (var v in Header)

{

TableHeaderCell cell = new TableHeaderCell();

cell.Text = v.Key.Substring(v.Key.LastIndexOf(_Separator) + 1);

cell.ColumnSpan = v.Count();

oGridViewRow.Cells.Add(cell);

}

gv.Controls[0].Controls.AddAt(row.RowIndex, oGridViewRow);

}

row.Visible = false;

}

private string GetHeaderText(string s, int i, int PivotLevel)

{

if (!s.Contains(_Separator) && i != PivotLevel)

return string.Empty;

else

{

int Index = NthIndexOf(s, _Separator, i);

if (Index == -1)

return s;

return s.Substring(0, Index);

}

/// <summary>

/// Returns the nth occurance of the SubString from string str

/// </summary>

/// <param name="str">source string</param>

/// <param name="SubString">SubString whose nth occurance to be found</param>

/// <param name="n">n</param>

/// <returns>Index of nth occurance of SubString if found else -1</returns>

private int NthIndexOf(string str, string SubString, int n)

{

int x = -1;

for (int i = 0; i < n; i++)

{

x = str.IndexOf(SubString, x + 1);

if (x == -1)

return x;

}

return x;

}

web.confg

<?xml version="1.0"?>

<!--

Note: As an alternative to hand editing this file you can use the

web admin tool to configure settings for your application. Use

the Website->Asp.Net Configuration option in Visual Studio.

A full list of settings and comments can be found in

machine.config.comments usually located in

\Windows\Microsoft.Net\Framework\v2.x\Config

-->

</sectionGroup>

</configSections>

<system.web>

<!--

Set compilation debug="true" to insert debugging

symbols into the compiled page. Because this

affects performance, set this value to true only

during development.

-->

</compilation>

<!--

The <authentication> section enables configuration

of the security authentication mode used by

ASP.NET to identify an incoming user.

-->

<!--

The <customErrors> section enables configuration

of what to do if/when an unhandled error occurs

during the execution of a request. Specifically,

it enables developers to configure html error pages

to be displayed in place of a error stack trace.

</customErrors>

-->

<pages>

</controls>

</pages>

</httpHandlers>

</httpModules>

</system.web>

<system.codedom>

</compiler>

</compiler>

</compilers>

</system.codedom>

<!--

The system.webServer section is required for running ASP.NET AJAX under Internet

Information Services 7.0. It is not necessary for previous version of IIS.

-->

<system.webServer>

</modules>

</handlers>

</system.webServer>

</dependentAssembly>

</dependentAssembly>

</assemblyBinding>

</runtime>

</configuration>

Monday, December 5, 2011

Pivoting DataTable in asp.net

1 comment :