SHJX_GM/SHJX.Service.Common/Utils/Converter.cs

using System;
using System.IO;
using System.Linq;
using System.Data;
using OfficeOpenXml;
using System.Reflection;
using OfficeOpenXml.Style;
using System.Windows.Media;
using System.ComponentModel;
using SHJX.Service.Model.Enums;
using System.Collections.Generic;
using OfficeOpenXml.DataValidation.Contracts;
using LicenseContext = OfficeOpenXml.LicenseContext;
using SHJX.Service.Common.Event;

namespace SHJX.Service.Common.Utils
{
    public static class Converter
    {
        #region String ConvertTo Brush
        private static BrushConverter _brushConverter;
        private static BrushConverter BrushConverter
        {
            get
            {
                if (_brushConverter is null)
                {
                    _brushConverter = new();
                }
                return _brushConverter;
            }
        }
        public static SolidColorBrush ConvertToBrush(this string colorRgb)
        {
            return string.IsNullOrWhiteSpace(colorRgb)
                ? throw new ArgumentNullException(colorRgb)
                : (SolidColorBrush)BrushConverter.ConvertFromString(colorRgb);
        }
        #endregion

        #region ExcelConvert
        public static void ConvertToExcel(this DataTable dt, string filePath)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using ExcelPackage package = new();
            ExcelWorksheet 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 (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.SetValue(1, i + 1, dt.Columns[i].ColumnName);
            }
            string[] types = new string[] { "样品", "空白" };
            IExcelDataValidationList vaildationData = sheet.DataValidations.AddListValidation("E2:E56");
            foreach (string item in types)
            {
                vaildationData.Formula.Values.Add(item);
            }
            string[] acidTypes = new string[] { "Acid", "Alkali" };
            IExcelDataValidationList acidData = sheet.DataValidations.AddListValidation("F2:F56");
            foreach (string item in acidTypes)
            {
                acidData.Formula.Values.Add(item);
            }
            //数据绑定
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int 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[] bytes = package.GetAsByteArray();
            File.WriteAllBytes(filePath, bytes);
        }

        /// <summary>
        /// 导出结果
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        public static void ExportResult(this DataTable dt, string filePath)
        {
            string savePath = $"{filePath}SampleResult_By_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using ExcelPackage package = new();
            ExcelWorksheet sheet = package.Workbook.Worksheets.Add("SampleResult");
            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 (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.SetValue(1, i + 1, dt.Columns[i].ColumnName);
            }
            //数据绑定
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sheet.Row(i + 2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                sheet.Row(i + 2).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                for (int 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[] bytes = package.GetAsByteArray();
            File.WriteAllBytes(savePath, bytes);
        }

        /// <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);
            }
            ExcelWorksheet ws = package.Workbook.Worksheets.First();
            DataTable dt = new();
            foreach (ExcelRangeBase firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                dt.Columns.Add(hasHeader ? firstRowCell.Text : $"Column {firstRowCell.Start.Column}");
            }
            int startRow = hasHeader ? 2 : 1;
            for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                ExcelRange wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                DataRow row = dt.NewRow();
                foreach (ExcelRangeBase 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)
            {
                object[] 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;
                    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 not { Length: 8 })
                        {
                            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.Boolean" => value.Equals("1"),
                        "System.Int32" => Convert.ToInt32(value),
                        "System.Double" => Convert.ToDouble(value),
                        "SHJX.Service.Model.Enums.AcidBase" => (AcidBase)Enum.Parse(typeof(AcidBase), value.ToString()!),
                        "SHJX.Service.Model.Enums.DetailState" => (DetailState)Enum.Parse(typeof(DetailState), value.ToString()!),
                        _ => value,
                    };
                    item.SetValue(t, value, null);
                }
                lists.Add(t); //对象添加到泛型集合中
            }
            return lists;
        }

        #endregion
    }
}