MES / MES-BaseMes10.0 · Files

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Xml.Serialization;
using System.Text.RegularExpressions;
using System.Linq.Expressions;

namespace Hh.Mes.Common.DataTableTo
{
    /// <summary>
    /// DataTableHelp 的摘要说明
    /// </summary>
    public static class DataTableHelp
    {

        #region 获取某一列的所有值(字符串拼接手动 string.Join(",", x))
        /// <summary>
        /// 获取某一列的所有值 (字符串拼接手动 string.Join(",", x))
        /// </summary>
        /// <typeparam name="T">列数据类型</typeparam>
        /// <param name="dtSource">数据表</param>
        /// <param name="filedName">列名</param>
        /// <returns></returns>
        public static List<T> GetColumnValues<T>(this DataTable dtSource, string filedName)
        {
            return (from r in dtSource.AsEnumerable() select r.Field<T>(filedName)).ToList();
        }
        #endregion


        #region  去重复数据方法
        /// <summary>
        /// 去重复数据方法
        /// </summary>
        public static DataTable Distinct(this DataTable dtSource, params string[] filedNames)
        {
          return  dtSource.DefaultView.ToTable(true, filedNames); 
        }
        #endregion


        #region dt判断是否为null 或者 行数是否为0,true是为空，false存在数据
        /// <summary>
        /// dt判断是否为null 或者 行数是否为0,true是为空，false存在数据
        /// </summary>
        public static bool IsEmpty(this DataTable dt)
        {
            return dt == null || dt.Rows.Count <= 0;
        }
        #endregion


        #region string 空返回0，
        /// <summary>
        /// string 空返回0，
        /// </summary>
        public static string IsEmpty(this object val, string retVal = "")
        {
            if (val == null) return "";
            var temp = val.ToString();
            return string.IsNullOrEmpty(temp) ? retVal : temp;
        }
        #endregion


        #region  list转datatable  DataTableHelp.ListToDt(list)
        /// <summary>
        /// list转datatable  DataTableHelp.ListToDt(list)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <returns></returns>
        public static DataTable ListToDt<T>(IEnumerable<T> collection)
        {
            var props = typeof(T).GetProperties().Where(t => t.GetCustomAttribute(typeof(EditableAttribute)) == null);
            var dt = new DataTable();
            foreach (var item in props)
            {
                DataColumn dataColumn;
                Type colType = item.PropertyType;
                if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                var attr = (DescriptionAttribute)item.GetCustomAttribute(typeof(DescriptionAttribute));
                if (attr != null)
                {
                    dataColumn = new DataColumn(attr.Description, colType);
                }
                else
                {
                    dataColumn = new DataColumn(item.Name, colType);
                }
                dt.Columns.Add(dataColumn);
            }
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }
        #endregion


        #region  DataTable转List

        #region 构建自定义特性（用于获取属性的对应的表格列名）
        /// <summary>
        /// 构建自定义特性（用于获取属性的对应的表格列名）
        /// </summary>
        [AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
        public class ExcelColumnAttribute : Attribute
        {
            public string Name { get; }

            public ExcelColumnAttribute(string name)
            {
                Name = name;
            }
        }
        #endregion

        private static readonly Dictionary<Type, object> _mappingCache = new Dictionary<Type, object>();
        private static readonly Regex _lineBreakRegex = new Regex(@"[\r\n]+", RegexOptions.Compiled);

        /// <summary>
        /// DataTable转为List<Model>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(this DataTable dataTable) where T : new()
        {
            //先重构表
            DataTable newTable = RefactoringTables(dataTable);
            var mappings = GetMappings<T>(newTable);
            var result = new List<T>(newTable.Rows.Count);
            var lst_dtColumnNames = newTable.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
            foreach (DataRow row in newTable.Rows)
            {
                var obj = new T();
                foreach (var mapping in mappings)
                {
                    //object value = GetRowValue(row, mapping, newTable);
                    object value = GetRowValue(row, mapping, lst_dtColumnNames);
                    mapping.Setter(obj, value);
                }
                result.Add(obj);
            }
            return result;
        }


        #region 重构表格(大数据的情况下效率不高)
        /// <summary>
        /// 重构表格
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static DataTable RefactoringTables(DataTable dt)
        {
            DataTable newTable = dt.Copy();
            foreach (DataColumn col in newTable.Columns)
            {
                col.ColumnName = _lineBreakRegex.Replace(col.ColumnName, "");
            }
            return newTable;
        }
        #endregion


        #region 获取表格数值
        /// <summary>
        /// 获取值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="row"></param>
        /// <param name="mapping"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        //private static object GetRowValue<T>(DataRow row, PropertyMapping<T> mapping, DataTable dt)
        private static object GetRowValue<T>(DataRow row, PropertyMapping<T> mapping, List<string> lst_dtColumnNames)
        {
            //检查列名是否存在
            //bool columnExists = dt.Columns.Contains(mapping.ColumnName);
            bool columnExists = lst_dtColumnNames.Contains(mapping.ColumnName);
            object value = columnExists ? row[mapping.ColumnName] : null;

            //处理DBNull和未找到列的情况
            if (value == DBNull.Value || !columnExists)
            {
                return mapping.IsStringType ? "" : mapping.DefaultValue;
            }

            try
            {
                return Convert.ChangeType(value, mapping.PropertyType);
            }
            catch
            {
                return mapping.DefaultValue;
            }
        }
        #endregion


        #region  转成对应Model的Mapping
        /// <summary>
        /// 转成对应Model的Mapping
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private static PropertyMapping<T>[] GetMappings<T>(DataTable dataTable) where T : new()
        {
            var type = typeof(T);
            if (_mappingCache.TryGetValue(type, out var cachedMappings))
                return (PropertyMapping<T>[])cachedMappings;

            var properties = type.GetProperties()
               .Where(p => p.CanWrite)
               .Select(p => new PropertyMapping<T>
               {
                   Property = p,
                   ColumnName = GetColumnName(p, dataTable),
                   PropertyType = p.PropertyType,
                   IsStringType = p.PropertyType == typeof(string),
                   DefaultValue = p.PropertyType.IsValueType ?
                       Activator.CreateInstance(p.PropertyType) : null,
                   Setter = CreateSetter<T>(p)
               })
               .ToArray();

            _mappingCache[type] = properties;
            return properties;
        }
        #endregion


        #region  obj类型转换
        /// <summary>
        /// obj类型转换
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="property"></param>
        /// <returns></returns>
        private static Action<T, object> CreateSetter<T>(PropertyInfo property)
        {
            var instance = Expression.Parameter(typeof(T), "obj");
            var value = Expression.Parameter(typeof(object), "value");

            // 转换object到实际类型
            var convertExpr = Expression.Convert(
                Expression.Convert(value, property.PropertyType),
                property.PropertyType);

            var setterCall = Expression.Call(
                instance,
                property.GetSetMethod(),
                convertExpr);

            return Expression.Lambda<Action<T, object>>(setterCall, instance, value)
                .Compile();
        }
        #endregion


        #region 获取匹配列名（待新增读取配置文件方法）
        /// <summary>
        /// 获取匹配列名（待新增读取配置文件方法）
        /// </summary>
        /// <param name="property"></param>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private static string GetColumnName(PropertyInfo property, DataTable dataTable)
        {
            //如新增读取配置文件方法可以增加标志位或者设置优先级
            //优先读取ColumnAttribute
            var excelColumnAttr = property.GetCustomAttribute<ExcelColumnAttribute>();
            if (!string.IsNullOrEmpty(excelColumnAttr?.Name))
            {
                return excelColumnAttr.Name;
            }

            //匹配列名（不区分大小写）
            var columnNames = dataTable.Columns
                .Cast<DataColumn>()
                .Select(c => c.ColumnName)
                .ToList();

            return columnNames.FirstOrDefault(c =>
                c.Equals(property.Name, StringComparison.OrdinalIgnoreCase)) ?? property.Name;
        }
        #endregion


        #region  构建Mapping
        /// <summary>
        /// 构建Mapping
        /// </summary>
        /// <typeparam name="T"></typeparam>
        private class PropertyMapping<T>
        {
            public PropertyInfo Property { get; set; }
            public string ColumnName { get; set; }
            public Type PropertyType { get; set; }
            public bool IsStringType { get; set; }
            public object DefaultValue { get; set; }
            public Action<T, object> Setter { get; set; }
        }
        #endregion


        #endregion

    }
}