/// <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 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); }
/// <summary> /// /// </summary> /// <param name="element"></param> /// <param name="evp"></param> public override void Visit(Fun element, EvaluationParam evp) { if (element.Type == FunctionType.Avg) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Average); } } else if (element.Type == FunctionType.Count) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Rows.Count; } } else if (element.Type == FunctionType.UCount) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.DefaultView.ToTable(true, element.ActionField).Rows.Count; } } else if (element.Type == FunctionType.Min) { if (element.GetSource() != null) { DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Min); } } else if (element.Type == FunctionType.Max) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Max); } } else if (element.Type == FunctionType.Sum) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Sum); } } else if (element.Type == FunctionType.Case) { var c = (Case)element; System.Data.DataTable dt = getRecord(element); result = new Result(); StringBuilder sb = new StringBuilder(); sb.Append("{0}"); foreach (CaseParam cp in c.Cases) { StringBuilder s = new StringBuilder(); s.AppendFormat("IIF([{0}] {1} '{2}','{3}','{4}')", c.FieldName, cp.Condition, cp.Value, cp.AliasValue, "{0}"); string sst = sb.ToString(); sb.Clear(); sb.AppendFormat(sst, s.ToString()); } if (sb.ToString() != "") { string st = sb.ToString(); sb.Clear(); sb.AppendFormat(st, c.elseValue); if (dt.Columns.Contains(c.NewFieldName) == false) { dt.Columns.Add(c.NewFieldName, typeof(string)); } dt.Columns[c.NewFieldName].Expression = sb.ToString(); dt.AcceptChanges(); result.Value = dt; } } else if (element.Type == FunctionType.Order) { var c = (Order)element; System.Data.DataTable dt = getRecord(element); result = new Result(); string s = ""; foreach (ParamFields p in c.Fields) { s = s + "," + p.FieldName + " " + p.Order; } if (s.StartsWith(",")) { s = s.Substring(1); } DataView viewFI = new DataView(dt); viewFI.Sort = s; dt = viewFI.ToTable(); result.Value = dt; } else if (element.Type == FunctionType.Pivot) { var p = (Pivot)element; System.Data.DataTable dt = (DataTable)element.GetSource(); List <string> row = new List <string>(); List <string> datafield = new List <string>(); List <AggregateFunctionE> dataagg = new List <AggregateFunctionE>(); List <string> colfield = new List <string>(); foreach (ParamFields d in p.RowArguments) { row.Add(d.FieldName); } foreach (ParamFields d in p.AggrArguments) { datafield.Add(d.FieldName); dataagg.Add((AggregateFunctionE)d.Aggregate); } foreach (ParamFields d in p.ColumnArguments) { colfield.Add(d.FieldName); } dt = dt.PivotData(datafield, dataagg.ToList(), row.ToArray(), colfield.ToArray(), false, 0, null, null, false); result = new Result(); result.Value = dt; } else if (element.Type == FunctionType.Mean) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Median); } } else if (element.Type == FunctionType.Mode) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Mode); } } else if (element.Type == FunctionType.Range) { } else if (element.Type == FunctionType.Medium) { if (element.GetSource() != null) { System.Data.DataTable dt = getRecord(element); result = new Result(); result.Value = dt.Computing("", element.ActionField, AggregateFunctionE.Median); } } else if (element.Type == FunctionType.Merge) { } else if (element.Type == FunctionType.Join) { } else if (element.Type == FunctionType.Filter) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); var flt = "1=1"; foreach (ParamFields pf in element.Arguments) { flt = flt + " AND " + pf.FieldName + " = " + pf.FieldValue; } dt.DefaultView.RowFilter = flt; dt = dt.DefaultView.ToTable(true); dt.DefaultView.RowFilter = ""; result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.Calculate) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); dt.Calculate(((Calculate)element).ColumnName, ((Calculate)element).formula); result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.Columnduplicate) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); dt = dt.DuplicateColumn(((Duplicate)element).ColumnName, ((Duplicate)element).AliasColumnName); result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.Dateparse) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); dt = dt.DateParse(((DateParse)element).ColumnName, ((DateParse)element).GetParse()); result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.Columnspit) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); dt = dt.Split(((Split)element).SplitColumn, ((Split)element).Spliter, ((Split)element).ColumnPrefix); result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.Replace) { if (element.GetSource() != null) { List <KeyValuePair <string, string> > kp = new List <KeyValuePair <string, string> >(); kp.Add(new KeyValuePair <string, string>(((Replace)element).FindString, ((Replace)element).ReplaceString)); System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); dt = dt.FindReplace(((Replace)element).ColumnName, kp, ((Replace)element).NewColumnName); result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.ChangeCase) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); if ((((TextCase)element).CaseType == TextCaseType.UPPER)) { dt = dt.UpperCase(((TextCase)element).ColumnName); } else if ((((TextCase)element).CaseType == TextCaseType.CAPITAL)) { dt = dt.CapitalCase(((TextCase)element).ColumnName); } else if ((((TextCase)element).CaseType == TextCaseType.LOWER)) { dt = dt.LowerCase(((TextCase)element).ColumnName); } else if ((((TextCase)element).CaseType == TextCaseType.TITLE)) { dt = dt.TitleCase(((TextCase)element).ColumnName); } result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.Trancate) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); dt.Trancate(((Trancate)element).ColumnName, ((Trancate)element).TrancateIndex); result = new Result(); result.Value = dt; } } else if (element.Type == FunctionType.GetValue) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); if (dt.Rows.Count > 0) { var name = ((Fun)element).Arguments[0].FieldName; var s = dt.Rows[0][name.Trim()]; result = new Result(); result.Value = s; } else { result = new Result(); result.Value = ""; } } } else if (element.Type == FunctionType.GetValues) { if (element.GetSource() != null) { System.Data.DataTable dt = (System.Data.DataTable)element.GetSource(); if (dt.Rows.Count > 0) { var name = ((Fun)element).Arguments[0].FieldName.Trim(); Type type = dt.Columns[name].DataType; var selectedColumn = string.Join(",", dt.AsEnumerable().Select(s => s.Field <string>(name)).ToArray()); result = new Result(); result.Value = selectedColumn; } else { result = new Result(); result.Value = ""; } } else { result = new Result(); result.Value = ""; } } else if (element.Type == FunctionType.toString) { if (element.GetSource() != null) { } } }
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); }