SHJX_cod/SHJX.Service.Common/Utils/Converts.cs

using System;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using SHJX.Service.Model.Control;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Windows.Media;
using LicenseContext = OfficeOpenXml.LicenseContext;

namespace SHJX.Service.Common.Utils
{
    public static class Converts
    {
        #region 颜色
        private static readonly BrushConverter BrushConverter = new();
        public static SolidColorBrush ConvertToBrush(this string colorRgb)
        {
            if (string.IsNullOrWhiteSpace(colorRgb))
            {
                throw new ArgumentNullException(colorRgb);
            }

            return (SolidColorBrush)BrushConverter.ConvertFromString(colorRgb);
        }
        #endregion

        #region ExcelConvert

        public static void ConvertToExcel(this DataTable dt, string filePath)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var package = new ExcelPackage();
           
            var sheet = package.Workbook.Worksheets.Add("SampleTemplate");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.Column(i + 1).Width = 15;
            }
            sheet.Row(1).Style.Font.Bold = true;
            sheet.Row(1).Style.Font.Size = 12;
            sheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            sheet.Row(1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                sheet.SetValue(1, i + 1, dt.Columns[i].ColumnName);
            }
            string[] types = new string[] { "水样", "空白", "标定"};
            var typeVaildationData = sheet.DataValidations.AddListValidation("F2:F49");
            foreach (var item in types)
            {
                typeVaildationData.Formula.Values.Add(item);
            }
            string[] concentrations = new string[] { "高", "低" };
            var concentrationVaildationData = sheet.DataValidations.AddListValidation("G2:G49");
            foreach (var item in concentrations)
            {
                concentrationVaildationData.Formula.Values.Add(item);
            }
            //数据绑定
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    sheet.SetValue(i + 2, j + 1, dt.Rows[i][j].GetType().IsValueType ? Convert.ToDouble(dt.Rows[i][j]) : dt.Rows[i][j].ToString());
                }
            }
            byte[] bin = package.GetAsByteArray();
            File.WriteAllBytes(filePath, bin);
        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="hasHeader"></param>
        /// <returns></returns>
        public static DataTable ReadExcel(this string filePath, bool hasHeader = true)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using ExcelPackage package = new();
            using (FileStream stream = File.OpenRead(filePath))
            {
                package.Load(stream);
            }
            var ws = package.Workbook.Worksheets.First();
            DataTable dt = new();
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                dt.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }
            var startRow = hasHeader ? 2 : 1;
            for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                var row = dt.NewRow();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
                dt.Rows.Add(row);
            }
            return dt;
        }
        #endregion

        #region ListToDataTable
        public static DataTable ToDataTable<T>(this List<T> items)
        {
            DataTable dataTable = new();
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                dataTable.Columns.Add(prop.Name);
            }
            foreach (T obj in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    values[i] = Props[i].GetValue(obj, null);
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }
        #endregion

        #region DataTable -> List<T>

        /// <summary>
        /// 将DataTable转换为List
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> ConvertToList<T>(this DataTable dt) where T : class, new()
        {
            List<T> lists = new(); // 定义集合
            foreach (DataRow dr in dt.Rows) //遍历DataTable中所有的数据行
            {
                T t = new();
                PropertyInfo[] propertys = typeof(T).GetProperties(); // 获得此模型的公共属性
                foreach (PropertyInfo item in propertys) //遍历该对象的所有属性
                {
                    object value = null;
                    object[] attr = item.GetCustomAttributes(typeof(DescriptionAttribute), false);
                    string description = attr.Length.Equals(0) ? item.Name : ((DescriptionAttribute)attr[0]).Description;
                    object[] defaultValues = item.GetCustomAttributes(typeof(DefaultValueAttribute), false);
                    //检查DataTable是否包含此列（列名等于对象的属性的Description属性）
                    if (!dt.Columns.Contains(description))
                    {
                        if (defaultValues is null or { Length: 0 })
                        {
                            continue;
                        }
                        value = ((DefaultValueAttribute)defaultValues[0]).Value;
                    }
                    else
                    {
                        if (!item.CanWrite) continue; //该属性不可写，直接跳出
                        value = dr[description];//取值
                    }
                    if (value.Equals(DBNull.Value))
                    {
                        continue;//如果空，则返回
                    }
                    value = item.PropertyType.FullName switch
                    {
                        "System.String" => value.ToString(),
                        "System.Double" => Convert.ToDouble(value),
                        "System.Boolean" => value.Equals("1"),
                        "System.Int32" => Convert.ToInt32(value),
                        _ => value,
                    };
                    item.SetValue(t, value, null);
                }
                lists.Add(t); //对象添加到泛型集合中
            }
            return lists;
        }

        #endregion
    }
}