List,DataTable實現行轉列的通用方案

lee_lgw發表於2021-09-09


　　最近在做報表統計方面的需求，涉及到行轉列報表。根據以往經驗使用SQL可以比較容易完成，這次決定挑戰一下直接透過程式碼方式完成行轉列。期間遇到幾個問題和用到的新知識這裡整理記錄一下。

閱讀目錄

  • 問題介紹

  • 動態Linq

  • System.Linq.Dynamic其它用法

  • DataTable行轉列

  • 總結

回到頂部

問題介紹

　　以家庭月度費用為例，可以在[Name,Area,Month]三個維度上隨意組合進行分組，三個維度中選擇一個做為列顯示。

圖片描述

    ///     /// 家庭費用情況    ///     public class House    {        ///         /// 戶主姓名        ///         public string Name { get; set; }        ///         /// 所屬行政區域        ///         public string Area { get; set; }        ///         /// 月份        ///         public string Month { get; set; }        ///         /// 電費金額        ///         public double DfMoney { get; set; }        ///         /// 水費金額        ///         public double SfMoney { get; set; }        ///         /// 燃氣金額        ///         public double RqfMoney { get; set; }    }

圖片描述

戶主-月明細報表
戶主姓名 2016-01 2016-02
電費 水費 燃氣費 電費 水費 燃氣費
張三 240.9 30 25 167 24.5 17.9
李四 56.7 24.7 13.2 65.2 18.9 14.9
區域-月明細報表
區域 2016-01 2016-02
電費 水費 燃氣費 電費 水費 燃氣費
江夏區 2240.9 330 425 5167 264.5 177.9
洪山區 576.7 264.7 173.2 665.2 108.9 184.9
區域月份-戶明細報表
區域 月份 張三 李四
燃氣費 電費 水費 燃氣費 電費 水費
江夏區 2016-01 2240.9 330 425 5167 264.5 177.9
洪山區 2016-01 576.7 264.7 173.2 665.2 108.9 184.9
江夏區 2016-02 3240.9 430 525 6167 364.5 277.9
洪山區 2016-02 676.7 364.7 273.2 765.2 208.9 284.9

    現在後臺查出來的資料是List型別，前臺傳過來分組維度和動態列欄位。  第1個表格前臺傳給後臺引數

{DimensionList:['Name'],DynamicColumn:'Month'}

 第2個表格前臺傳給後臺引數

{DimensionList:['Area'],DynamicColumn:'Month'}

第3個表格前臺傳給後臺引數

{DimensionList:['Area','Month'],DynamicColumn:'Name'}

問題描述清楚後，仔細分析後你就會發現這裡的難題在於動態分組，也就是怎麼根據前臺傳過來的多個維度對List進行分組。回到頂部 

動態Linq

   下面使用System.Linq.Dynamic完成行轉列功能，Nuget上搜尋System.Linq.Dynamic即可下載該包。

   程式碼進行了封裝，實現了通用的List行轉列功能。

圖片描述

        ///         /// 動態Linq方式實現行轉列        ///         /// 資料        /// 維度列        /// 動態列        /// 行轉列後資料        private static List DynamicLinq(List list, List DimensionList, string DynamicColumn, out List AllDynamicColumn) where T : class        {            //獲取所有動態列            var columnGroup = list.GroupBy(DynamicColumn, "new(it as Vm)") as IEnumerable>;            List AllColumnList = new List();            foreach (var item in columnGroup)            {                if (!string.IsNullOrEmpty(item.Key))                {                    AllColumnList.Add(item.Key);                }            }            AllDynamicColumn = AllColumnList;            var dictFunc = new Dictionary>();            foreach (var column in AllColumnList)            {                var func = DynamicExpression.ParseLambda(string.Format("{0}=="{1}"", DynamicColumn, column)).Compile();                dictFunc[column] = func;            }            //獲取實體所有屬性            Dictionary PropertyInfoDict = new Dictionary();            Type type = typeof(T);            var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);            //數值列            List AllNumberField = new List();            foreach (var item in propertyInfos)            {                PropertyInfoDict[item.Name] = item;                if (item.PropertyType == typeof(int) || item.PropertyType == typeof(double) || item.PropertyType == typeof(float))                {                    AllNumberField.Add(item.Name);                }            }            //分組            var dataGroup = list.GroupBy(string.Format("new ({0})", string.Join(",", DimensionList)), "new(it as Vm)") as IEnumerable>;            List listResult = new List();            IDictionary itemObj = null;            T vm2 = default(T);            foreach (var group in dataGroup)            {                itemObj = new ExpandoObject();                var listVm = group.Select(e => e.Vm as T).ToList();                //維度列賦值                vm2 = listVm.FirstOrDefault();                foreach (var key in DimensionList)                {                    itemObj[key] = PropertyInfoDict[key].GetValue(vm2);                }                foreach (var column in AllColumnList)                {                    vm2 = listVm.FirstOrDefault(dictFunc[column]);                    if (vm2 != null)                    {                        foreach (string name in AllNumberField)                        {                            itemObj[name + column] = PropertyInfoDict[name].GetValue(vm2);                        }                    }                }                listResult.Add(itemObj);            }            return listResult;        }

圖片描述

   圖片描述

   標紅部分使用了System.Linq.Dynamic動態分組功能，傳入字串即可分組。使用了dynamic型別，關於dynamic介紹可以參考其它文章介紹哦。

回到頂部

System.Linq.Dynamic其它用法

    上面行轉列程式碼見識了System.Linq.Dynamic的強大，下面再介紹一下會在開發中用到的方法。

    Where過濾

list.Where("Name=@0", "張三")

 

圖片描述

上面用到了引數化查詢，實現了查詢姓名是張三的資料，透過這段程式碼你或許感受不到它的好處。但是和EntityFramework結合起來就可以實現動態拼接SQL的功能了。圖片描述

        ///         /// EF實體查詢封裝         ///         /// 實體型別        /// IQueryable物件        /// 過濾條件        /// 查詢結果        public static EFPaginationResult PageQuery(this IQueryable Query, QueryCondition gridParam)        {            //查詢條件            EFFilter filter = GetParameterSQL(gridParam);            var query = Query.Where(filter.Filter, filter.ListArgs.ToArray());            //查詢結果            EFPaginationResult result = new EFPaginationResult();            if (gridParam.IsPagination)            {                int PageSize = gridParam.PageSize;                int PageIndex = gridParam.PageIndex ();                if (gridParam.IsCalcTotal)                {                    result.Total = query.Count();                    result.TotalPage = Convert.ToInt32(Math.Ceiling(result.Total * 1.0 / PageSize));                }                else                {                    result.Total = result.Data.Count();                }            }            else            {                result.Data = query.ToList();                result.Total = result.Data.Count();            }            return result;        }

圖片描述圖片描述

///         /// 透過查詢條件,獲取引數化查詢SQL        ///         /// 過濾條件        /// 過濾條件字元        private static EFFilter GetParameterSQL(QueryCondition gridParam)        {            EFFilter result = new EFFilter();            //引數值集合            List listArgs = new List();            string filter = "1=1";            #region "處理動態過濾條件"            if (gridParam.FilterList != null && gridParam.FilterList.Count > 0)            {                StringBuilder sb = new StringBuilder();                int paramCount = 0;                DateTime dateTime;                //運算子                string strOperator = string.Empty;                foreach (var item in gridParam.FilterList)                {                    //欄位名稱為空則跳過                    if (string.IsNullOrEmpty(item.FieldName))                    {                        continue;                    }                    //匹配列舉，防止SQL隱碼攻擊                    Operator operatorEnum = (Operator)Enum.Parse(typeof(Operator), item.Operator, true);                    //跳過欄位值為空的                    if (operatorEnum != Operator.Null && operatorEnum != Operator.NotNull && string.IsNullOrEmpty(item.FieldValue))                    {                        continue;                    }                    strOperator = operatorEnum.GetDescription();                    if (item.IgnoreCase && !item.IsDateTime)                    {                        //2016-07-19新增查詢時忽略大小寫比較                        item.FieldValue = item.FieldValue.ToLower();                        item.FieldName = string.Format("{0}.ToLower()", item.FieldName);                    }                    switch (operatorEnum)                    {                        //等於,不等於，小於，大於，小於等於，大於等於                        case Operator.EQ:                        case Operator.NE:                        case Operator.GT:                        case Operator.GE:                        case Operator.LT:                        case Operator.LE:                            if (item.IsDateTime)                            {                                if (DateTime.TryParse(item.FieldValue, out dateTime))                                {                                    if (!item.FieldValue.Contains("00:00:00") && dateTime.ToString("HH:mm:ss") == "00:00:00")                                    {                                        if (operatorEnum == Operator.LE)                                        {                                            listArgs.Add(DateTime.Parse(dateTime.ToString("yyyy-MM-dd") + " 23:59:59"));                                        }                                        else                                        {                                            listArgs.Add(dateTime);                                        }                                    }                                    else                                    {                                        listArgs.Add(dateTime);                                    }                                    sb.AppendFormat(" AND {0} {1} @{2}", item.FieldName, strOperator, paramCount);                                }                            }                            else                            {                                listArgs.Add(ConvertToType(item.FieldValue, GetPropType(item.FieldName)));                                sb.AppendFormat(" AND {0} {1} @{2}", item.FieldName, strOperator, paramCount);                            }                            paramCount++;                            break;                        case Operator.Like:                        case Operator.NotLike:                        case Operator.LLike:                        case Operator.RLike:                            listArgs.Add(item.FieldValue);                            if (operatorEnum == Operator.Like)                            {                                sb.AppendFormat(" AND {0}.Contains(@{1})", item.FieldName, paramCount);                            }                            else if (operatorEnum == Operator.NotLike)                            {                                sb.AppendFormat(" AND !{0}.Contains(@{1})", item.FieldName, paramCount);                            }                            else if (operatorEnum == Operator.LLike)                            {                                sb.AppendFormat(" AND {0}.EndsWith(@{1})", item.FieldName, paramCount);                            }                            else if (operatorEnum == Operator.RLike)                            {                                sb.AppendFormat(" AND {0}.StartsWith(@{1})", item.FieldName, paramCount);                            }                            paramCount++;                            break;                        case Operator.Null:                            listArgs.Add(item.FieldValue);                            sb.AppendFormat(" AND {0}=null", item.FieldName);                            paramCount++;                            break;                        case Operator.NotNull:                            listArgs.Add(item.FieldValue);                            sb.AppendFormat(" AND {0}!=null", item.FieldName);                            paramCount++;                            break;                        case Operator.In:                            sb.AppendFormat(" AND (");                            foreach (var schar in item.FieldValue.Split(','))                            {                                listArgs.Add(schar);                                sb.AppendFormat("{0}=@{1} or ", item.FieldName, paramCount);                                paramCount++;                            }                            sb.Remove(sb.Length - 3, 3);                            sb.AppendFormat(" )");                            break;                        case Operator.NotIn:                            sb.AppendFormat(" AND (");                            foreach (var schar in item.FieldValue.Split(','))                            {                                listArgs.Add(schar);                                sb.AppendFormat("{0}!=@{1} and ", item.FieldName, paramCount);                                paramCount++;                            }                            sb.Remove(sb.Length - 3, 3);                            sb.AppendFormat(" )");                            break;                    }                    if (sb.ToString().Length > 0)                    {                        filter = sb.ToString().Substring(4, sb.Length - 4);                    }                }                #endregion            }            result.Filter = filter;            result.ListArgs = listArgs;            return result;        }

圖片描述 回到頂部

DataTable行轉列

　　該部分是根據網友反饋後期再補充上的內容，意在完善行轉列。下面給出實現程式碼

圖片描述

using Newtonsoft.Json;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;namespace DataTable_RowToColumn{    class Program    {        static void Main(string[] args)        {            DataTable dt = InitTable();            List DimensionList = new List() { "Area", "Month" };            string DynamicColumn = "Name";            List AllDynamicColumn = null;            DataTable dtResult = RowToColumn(dt, DimensionList, DynamicColumn, out AllDynamicColumn);            Console.WriteLine(JsonConvert.SerializeObject(dtResult, Formatting.Indented));            Console.Read();        }        ///         /// 動態Linq方式實現行轉列        ///         /// 資料        /// 維度列        /// 動態列        /// 行轉列後資料        private static DataTable RowToColumn(DataTable dt, List DimensionList, string DynamicColumn, out List AllDynamicColumn)        {            //獲取所有動態列            AllDynamicColumn = new List();            foreach (DataRow dr in dt.DefaultView.ToTable(true, DynamicColumn).Rows)            {                if (dr[DynamicColumn] != null && !string.IsNullOrEmpty(dr[DynamicColumn].ToString()))                {                    AllDynamicColumn.Add(dr[DynamicColumn].ToString());                }            }            //數值列            Dictionary AllNumberColumn = new Dictionary();            foreach (DataColumn item in dt.Columns)            {                if (item.DataType == typeof(int) || item.DataType == typeof(double) || item.DataType == typeof(float))                {                    AllNumberColumn.Add(item.ColumnName, item.DataType);                }            }            //結果DataTable建立            DataTable dtResult = new DataTable();            foreach (var item in DimensionList)            {                dtResult.Columns.Add(item, typeof(string));            }            //動態列            foreach (var dynamicValue in AllDynamicColumn)            {                foreach (var item in AllNumberColumn.Keys)                {                    dtResult.Columns.Add(item + dynamicValue, AllNumberColumn[item]);                }            }            //分組            var dtGroup = dt.DefaultView.ToTable(true, DimensionList.ToArray());            foreach (DataRow dr in dtGroup.Rows)            {                DataRow drReult = dtResult.NewRow();                string filter = "";                foreach (var key in DimensionList)                {                    drReult[key] = dr[key];                    filter += key + "='" + dr[key] + "' AND ";                }                string dynamicFilter = "";                foreach (var dynamicValue in AllDynamicColumn)                {                    dynamicFilter = DynamicColumn + "='" + dynamicValue + "'";                    foreach (var numColumn in AllNumberColumn.Keys)                    {                        drReult[numColumn + dynamicValue] = dt.Compute("sum(" + numColumn + ")", filter + dynamicFilter);                    }                }                dtResult.Rows.Add(drReult);            }            return dtResult;        }        private static DataTable InitTable()        {            DataTable dt = new DataTable();            dt.Columns.Add("Name", typeof(string));            dt.Columns.Add("Area", typeof(string));            dt.Columns.Add("Month", typeof(string));            dt.Columns.Add("DfMoney", typeof(double));            dt.Columns.Add("SfMoney", typeof(double));            dt.Columns.Add("RqfMoney", typeof(double));            DataRow row = dt.NewRow();            row["Name"] = "張三";            row["Month"] = "2016-01";            row["Area"] = "江夏區";            row["DfMoney"] = 240.9;            row["SfMoney"] = 30;            row["RqfMoney"] = 25;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "張三";            row["Month"] = "2016-02";            row["Area"] = "江夏區";            row["DfMoney"] = 167;            row["SfMoney"] = 24.5;            row["RqfMoney"] = 17.9;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "小燕子";            row["Month"] = "2016-01";            row["Area"] = "江夏區";            row["DfMoney"] = 340.9;            row["SfMoney"] = 20;            row["RqfMoney"] = 55;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "小燕子";            row["Month"] = "2016-02";            row["Area"] = "江夏區";            row["DfMoney"] = 67;            row["SfMoney"] = 64.5;            row["RqfMoney"] = 77.9;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "李四";            row["Month"] = "2016-01";            row["Area"] = "洪山區";            row["DfMoney"] = 56.7;            row["SfMoney"] = 24.7;            row["RqfMoney"] = 13.2;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "李四";            row["Month"] = "2016-02";            row["Area"] = "洪山區";            row["DfMoney"] = 65.2;            row["SfMoney"] = 18.9;            row["RqfMoney"] = 14.9;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "爾康";            row["Month"] = "2016-01";            row["Area"] = "洪山區";            row["DfMoney"] = 156.7;            row["SfMoney"] = 124.7;            row["RqfMoney"] = 33.2;            dt.Rows.Add(row);            row = dt.NewRow();            row["Name"] = "爾康";            row["Month"] = "2016-02";            row["Area"] = "洪山區";            row["DfMoney"] = 35.2;            row["SfMoney"] = 28.9;            row["RqfMoney"] = 44.9;            dt.Rows.Add(row);            return dt;        }    }}

圖片描述

回到頂部

總結

      本篇透過行轉列引出了System.Linq.Dynamic，並且介紹了過濾功能，其實它的用處還有很多，等待大家發掘。下面給出本文示例程式碼：DynamicLinq

 　  我的部落格即將搬運同步至騰訊雲+社群，邀請大家一同入駐：https://cloud.tencent.com/developer/support-plan


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/1020/viewspace-2812170/，如需轉載，請註明出處，否則將追究法律責任。

相關文章