/// <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 static DataTable PivotData(this DataTable SourceTable, string rowField, string dataField, AggregateFunctionE aggregate, params string[] columnFields) { IEnumerable <DataRow> _Source = new List <DataRow>(); _Source = SourceTable.Rows.Cast <DataRow>(); DataTable dt = new DataTable(); string Separator = "."; List <string> rowList = _Source.Select(x => x[rowField].ToString()).Distinct().ToList(); // Gets the list of columns .(dot) separated. var colList = _Source.Select(x => (columnFields.Select(n => x[n]).Aggregate((a, b) => a += Separator + b.ToString())).ToString()).Distinct().OrderBy(m => m); dt.Columns.Add(rowField); foreach (var colName in colList) { dt.Columns.Add(colName); // Cretes the result columns.// } foreach (string rowName in rowList) { DataRow row = dt.NewRow(); row[rowField] = rowName; foreach (string colName in colList) { string strFilter = rowField + " = '" + rowName + "'"; string[] strColValues = colName.Split(Separator.ToCharArray(), StringSplitOptions.None); for (int i = 0; i < columnFields.Length; i++) { strFilter += " and " + columnFields[i] + " = '" + strColValues[i] + "'"; } row[colName] = SourceTable.Computing(strFilter, dataField, aggregate); } dt.Rows.Add(row); } return(dt); }
public static DataTable PivotData(this DataTable _SourceTable, string DataField, AggregateFunctionE Aggregate, string[] RowFields, string[] ColumnFields, Boolean isFindSummary = false) { DataTable dt = new DataTable(); string Separator = "."; var RowList = _SourceTable.DefaultView.ToTable(true, RowFields).AsEnumerable().ToList(); for (int index = RowFields.Count() - 1; index >= 0; index--) { RowList = RowList.OrderBy(x => x.Field <object>(RowFields[index])).ToList(); } // Gets the list of columns .(dot) separated. //dt.Columns.Add(RowFields); foreach (string s in RowFields) { //Get dataType of the column if (_SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("datetime") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("date") >= 0) { dt.Columns.Add(s, typeof(System.DateTime)); } else if (_SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("int") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("double") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("float") >= 0 || _SourceTable.Columns[s].DataType.Name.ToLower().IndexOf("decimal") >= 0) { dt.Columns.Add(s, typeof(double)); } else { dt.Columns.Add(s); } } if (ColumnFields != null) { 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); foreach (var col in ColList) { if (col.Name != null) { dt.Columns.Add(col.Name.ToString(), typeof(double)); // Cretes the result columns.// } } foreach (var RowName in RowList) { DataRow row = dt.NewRow(); string strFilter = string.Empty; foreach (string Field in RowFields) { row[Field] = RowName[Field]; strFilter += " and " + Field + " = '" + RowName[Field].ToString() + "'"; } strFilter = strFilter.Substring(5); foreach (var col in ColList) { string filter = strFilter; if (col.Name != null) { string[] strColValues = col.Name.ToString().Split(Separator.ToCharArray(), StringSplitOptions.None); for (int i = 0; i < ColumnFields.Length; i++) { if (strColValues[i] != null) { filter += " and " + ColumnFields[i] + " = '" + strColValues[i] + "'"; } } object val = _SourceTable.Computing(filter, DataField, Aggregate); if (val == null) { val = 0; } row[col.Name.ToString()] = val; } } dt.Rows.Add(row); } } else { dt.Columns.Add(DataField, typeof(double)); foreach (var RowName in RowList) { DataRow row = dt.NewRow(); string strFilter = string.Empty; foreach (string Field in RowFields) { row[Field] = RowName[Field]; strFilter += " and " + Field + " = '" + RowName[Field].ToString() + "'"; } strFilter = strFilter.Substring(5); row[DataField] = _SourceTable.Computing(strFilter, DataField, Aggregate); dt.Rows.Add(row); } if (isFindSummary) { for (int index = 0; index < RowFields.Count() - 1; index++) { DataTable _temp = new DataTable(); _temp = dt.Clone(); List <string> rFields = new List <string>(); DataTable _dt = new DataTable(); List <string> _disRow = new List <string>(); for (int rix = 0; rix < RowFields.Count() - 1; rix++) { if (index >= rix) { { rFields.Add(RowFields[rix]); } } } foreach (string i in rFields) { _disRow.Add(i); } _dt = _SourceTable.DefaultView.ToTable(true, _disRow.ToArray()); foreach (DataRow _dr in _dt.Rows) { DataRow dr = _temp.NewRow(); string strFilter = ""; foreach (string fn in _disRow) { dr[fn] = _dr[fn]; strFilter += " and " + fn + " = '" + _dr[fn].ToString() + "'"; } strFilter = strFilter.Substring(5); dr[DataField] = dt.Computing(strFilter, DataField, Aggregate); _temp.Rows.Add(dr); } dt.Merge(_temp); } } } for (int i = 0; i < dt.Columns.Count; i++) { dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Replace(" ", "_"); } 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> public static object Computing(this DataTable dt, string Filter, string DataField, AggregateFunctionE Aggregate) { try { // var rows = dt.AsEnumerable().ToList().AsQueryable().Where(Filter); DataRow[] FilteredRows = dt.Select(Filter); object[] objList = FilteredRows.Select(x => x.Field <object>(DataField)).ToArray(); switch (Aggregate) { case AggregateFunctionE.Average: return(GetAverage(objList)); case AggregateFunctionE.Count: return(objList.Count()); case AggregateFunctionE.Exists: return((objList.Count() == 0) ? "False" : "True"); case AggregateFunctionE.First: return(GetFirst(objList)); case AggregateFunctionE.Last: return(GetLast(objList)); case AggregateFunctionE.Max: return(GetMax(objList)); case AggregateFunctionE.Min: return(GetMin(objList)); case AggregateFunctionE.Sum: return(GetSum(objList)); case AggregateFunctionE.NONE: return((objList.Count() == 0) ? null : objList[0]); default: return(null); } } catch (Exception ex) { return(0); } }
public static DataTable PivotData(this DataTable SourceTable, string rowField, string dataField, AggregateFunctionE aggregate, bool showSubTotal, params string[] columnFields) { IEnumerable <DataRow> _Source = new List <DataRow>(); _Source = SourceTable.Rows.Cast <DataRow>(); DataTable dt = new DataTable(); string Separator = "."; List <string> rowList = _Source.Select(x => x[rowField].ToString()).Distinct().ToList(); // Gets the list of columns .(dot) separated. List <string> colList = _Source.Select(x => columnFields.Aggregate((a, b) => x[a].ToString() + Separator + x[b].ToString())).Distinct().OrderBy(m => m).ToList(); if (showSubTotal && columnFields.Length > 1) { string totalField = string.Empty; for (int i = 0; i < columnFields.Length - 1; i++) { totalField += columnFields[i] + "(Total)" + Separator; } List <string> totalList = _Source.Select(x => totalField + x[columnFields.Last()].ToString()).Distinct().OrderBy(m => m).ToList(); colList.InsertRange(0, totalList); } dt.Columns.Add(rowField); colList.ForEach(x => dt.Columns.Add(x)); foreach (string rowName in rowList) { DataRow row = dt.NewRow(); row[rowField] = rowName; foreach (string colName in colList) { string filter = rowField + " = '" + rowName + "'"; string[] colValues = colName.Split(Separator.ToCharArray(), StringSplitOptions.None); for (int i = 0; i < columnFields.Length; i++) { if (!colValues[i].Contains("(Total)")) { filter += " and " + columnFields[i] + " = '" + colValues[i] + "'"; } } row[colName] = SourceTable.Computing(filter, dataField, colName.Contains("(Total)") ? AggregateFunctionE.Sum : aggregate); } dt.Rows.Add(row); } return(dt); }