XYY.Core/1.Command/XYY.Core.Standard/ExcelHelper/OPNPOIRow.cs

using CsvHelper;
using CsvHelper.Configuration;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;

namespace XYY.Core.Standard.ExcelHelper
{
    public class OPNPOIRow
    {
        public OPNPOIRow(IRow row)
        {
            Row = row;
        }

        public IRow Row
        {
            get;
        }
        public OPNPOIRow Cell(string value)
        {
            ICell cell = Row.CreateCell(Row.Cells.Count);
            LastCell = cell;
            cell.SetCellValue(value);
            return this;
        }
        public ICell LastCell { get; set; }



        public byte[] WBToByte(IWorkbook wb)
        {
            NpoiMemoryStream ms = new NpoiMemoryStream
            {
                AllowClose = false
            };
            wb.Write(ms);
            byte[] data = new byte[ms.Length];
            ms.Seek(0, SeekOrigin.Begin);
            ms.Read(data, 0, data.Length);
            ms.AllowClose = true;
            return data;
        }

    }

    /// <summary>
    /// NPOI Row与对象间的转换处理
    /// </summary>
    /// <typeparam name="T">处理对象</typeparam>
    public class OPNPOIRow<T> : OPNPOIRow
        where T : class, new()
    {
        public OPNPOIRow(T model, IRow row) : base(row)
        {
            Model = model;
            CellExpressions = new List<LambdaExpression>();
        }


        public T Model
        {
            get;
        }


        public List<LambdaExpression> CellExpressions
        {
            get; set;
        }

        /// <summary>
        /// NPOIExcel列反序列化对象
        /// </summary>
        public void Deserialize()

        {
            int emptyCellCount = 0;
            IRow topRow = Row.Sheet.GetRow(0);
            for (int i = 0; i < topRow.LastCellNum; i++)
            {
                ICell topCell = topRow.GetCell(i);
                if (topCell != null && topCell.CellType == CellType.String)
                {
                    string name = topCell.StringCellValue;
                    ICell cell = Row.GetCell(i);
                    if (cell == null || cell.CellType == CellType.Blank)
                        emptyCellCount++;
                    if (emptyCellCount == topRow.LastCellNum)
                    {
                        //全部空记录时直接返回
                        break;
                    }
                    //过滤空行
                    if (cell != null && cell.CellType != CellType.Blank)
                    {
                        if (topCell.CellComment == null)
                        {
                            SetPropertyVal(cell, name, Model);
                        }
                        else
                        {
                            //根据注解的序号生成List并进行实体与属性填充
                            string[] propertyConfig = topCell.CellComment.String.String.Split('-');
                            string pName = propertyConfig[0];
                            int index = int.Parse(propertyConfig[1]);
                            PropertyInfo p = typeof(T).GetProperty(pName);
                            Type type = typeof(List<>);
                            //目前仅解析List<T>数组
                            if (p.PropertyType.GetGenericTypeDefinition() == type)
                            {
                                object list = null;
                                object pModel = null;
                                int count = 0;
                                if (p.GetValue(Model, null) == null)
                                {
                                    //实例化所有组员
                                    list = Activator.CreateInstance(p.PropertyType);
                                    p.SetValue(Model, list);
                                }
                                else
                                {
                                    list = p.GetValue(Model, null);
                                }
                                count = (int)(list.GetType().GetProperty("Count").GetValue(list));

                                if (count < index)
                                {
                                    MethodInfo method = p.PropertyType.GetMethod("Add");
                                    pModel = Activator.CreateInstance(p.PropertyType.GenericTypeArguments[0]);
                                    method.Invoke(list, new object[] { pModel });
                                }
                                else
                                {
                                    MethodInfo method = p.PropertyType.GetMethod("get_Item");
                                    pModel = method.Invoke(list, new object[] { index - 1 });
                                }

                                Type pModelType = pModel.GetType();
                                string pModelPropertyName = name;
                                SetPropertyVal(cell, name.Split('-')[0], pModel);
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// NPOI 数据转换为C#类上的属性
        /// </summary>
        /// <param name="cell">数据</param>
        /// <param name="name">需赋值的列的DisplayName</param>
        /// <param name="model">需赋值的实体</param>
        private void SetPropertyVal(ICell cell, string name, object model)
        {
            //根据DisplayName查找相关的属性
            PropertyInfo p = model.GetType().GetProperties().Where(x =>
            {
                DisplayNameAttribute attr = x.GetCustomAttribute(typeof(DisplayNameAttribute)) as DisplayNameAttribute;
                if (attr != null)
                {
                    return (attr.DisplayName == name);
                }
                else
                {
                    return false;
                }
            }).FirstOrDefault();

            //类型转换
            if (p != null)
            {
                if (p.PropertyType == typeof(string))
                {
                    if (cell.CellType == CellType.Numeric)
                    {
                        p.SetValue(model, cell.NumericCellValue.ToString(), null);
                    }
                    else
                    {
                        if (cell.StringCellValue.Contains("("))
                        {
                            p.SetValue(model, cell.StringCellValue.TrimEnd(')').Split('(')[1], null);
                        }
                        else
                        {
                            p.SetValue(model, cell.StringCellValue, null);
                        }
                    }
                }
                else if (p.PropertyType == typeof(int) || p.PropertyType == typeof(decimal) || p.PropertyType == typeof(float) || p.PropertyType == typeof(Enum))
                {
                    if (cell.CellType == CellType.String)
                    {
                        if (cell.StringCellValue.Split('-').Length > 1)
                        {
                            p.SetValue(model, int.Parse(cell.StringCellValue.Split('-')[1]), null);
                        }
                    }
                    else
                    {
                        if (p.PropertyType == typeof(int))
                        {
                            p.SetValue(model, (int)cell.NumericCellValue, null);
                        }
                        else if (p.PropertyType == typeof(decimal))
                        {
                            p.SetValue(model, (decimal)cell.NumericCellValue, null);
                        }
                    }
                }
                else if (p.PropertyType == typeof(DateTime))
                {
                    p.SetValue(model, cell.DateCellValue, null);
                }
                else if (p.PropertyType == typeof(Nullable<DateTime>))
                {
                    p.SetValue(model, cell.DateCellValue, null);
                }
                else if (p.PropertyType == typeof(bool))
                {
                    p.SetValue(model, cell.BooleanCellValue, null);
                }
                else
                {
                    p.SetValue(model, cell.StringCellValue, null);
                }
            }
            else
            {
                return;
            }
        }

        public void Serialize()
        {
            Serialize(Model, Row);
        }


        List<T> refData = new List<T>();

        public void MSSerialize(T t, IRow row)
        {
            refData.Add(t);
        }

        public void CSVSerialize(T t, IRow row)
        {
            refData.Add(t);
        }

        public byte[] ToMSExcel()
        {
            var customerMap = new MSExcelDefaultMapping<T>();
            foreach (LambdaExpression expression in CellExpressions)
            {
                MemberExpression memberExpression = expression.Body as MemberExpression;
                PropertyInfo propertyInfo = memberExpression.Member as PropertyInfo;
                IList<CustomAttributeTypedArgument> displayNameAttr =
                    propertyInfo.CustomAttributes.Where(x => x.AttributeType == typeof(DisplayNameAttribute)).Select(x => x.ConstructorArguments).FirstOrDefault();

                var m = customerMap.Map(memberExpression.Member);
                if (displayNameAttr != null)
                {
                    m.Name(displayNameAttr[0].Value as string);
                }
                else
                {
                    m.Name(propertyInfo.Name);
                }
            }

            if (!System.IO.Directory.Exists(tempPath))
                System.IO.Directory.CreateDirectory(tempPath);
            var msHelper = new MSExcelHelper.MSExcelHelper();
            return msHelper.OutDataAsByte(refData, customerMap);
        }

        readonly string tempPath = AppContext.BaseDirectory + "\\temp";
        public byte[] ToCsv()
        {
            var customerMap = new DefaultClassMap<T>();
            foreach (LambdaExpression expression in CellExpressions)
            {
                MemberExpression memberExpression = expression.Body as MemberExpression;
                PropertyInfo propertyInfo = memberExpression.Member as PropertyInfo;
                IList<CustomAttributeTypedArgument> displayNameAttr =
                    propertyInfo.CustomAttributes.Where(x => x.AttributeType == typeof(DisplayNameAttribute)).Select(x => x.ConstructorArguments).FirstOrDefault();

                var m = customerMap.Map(typeof(T), memberExpression.Member);
                if (displayNameAttr != null)
                {
                    m.Name(displayNameAttr[0].Value as string);
                }
                else
                {
                    m.Name(propertyInfo.Name);
                }
            }

            if (!System.IO.Directory.Exists(tempPath))
                System.IO.Directory.CreateDirectory(tempPath);
            string filePath = tempPath + "\\" + Guid.NewGuid() + ".csv";
            using (var writer = new StreamWriter(filePath, false, Encoding.UTF8))
            {
                using (var csv = new CsvWriter(writer, System.Globalization.CultureInfo.InvariantCulture))
                {
                    csv.WriteRecords(refData);
                }
            }

            var data = File.ReadAllBytes(filePath);
            File.Delete(filePath);
            return data;
        }



        public void Serialize(T t, IRow row)
        {
            foreach (LambdaExpression expression in CellExpressions)
            {
                object value = expression.Compile().DynamicInvoke(t);
                int cellNum = row.LastCellNum == -1 ? 0 : row.LastCellNum;
                if (value != null)
                {
                    if (value.GetType() == typeof(string))
                    {
                        ICell cell = row.CreateCell(cellNum);
                        cell.SetCellValue(value as string);
                    }
                    else if (
                        value.GetType() == typeof(int) ||
                        value.GetType() == typeof(long) ||
                        value.GetType() == typeof(uint) ||
                        value.GetType() == typeof(ulong) ||
                        value.GetType() == typeof(short) ||
                        value.GetType() == typeof(sbyte) ||
                        value.GetType() == typeof(byte) ||
                        value.GetType() == typeof(ushort) ||
                        value.GetType() == typeof(decimal) ||
                        value.GetType() == typeof(float) ||
                        value.GetType() == typeof(double)
                        )
                    {
                        ICell cell = row.CreateCell(cellNum);
                        cell.SetCellValue(Convert.ToDouble(value));
                    }
                    else if (value.GetType() == typeof(bool))
                    {
                        ICell cell = row.CreateCell(cellNum);
                        cell.SetCellValue(Convert.ToBoolean(value));
                    }
                    else if (value.GetType() == typeof(DateTime))
                    {
                        ICell cell = row.CreateCell(cellNum);
                        cell.SetCellValue(Convert.ToDateTime(value));
                    }
                    else
                    {
                        ICell cell = row.CreateCell(cellNum);
                        cell.SetCellValue(value as string);
                    }
                }
                else
                {
                    ICell cell = row.CreateCell(cellNum, CellType.Blank);
                }
            }
        }


        /// <summary>
        /// 获取相关注解区域的最大值(已停用）
        /// </summary>
        /// <param name="topRow"></param>
        /// <returns></returns>
        private int GetDeserializeListCount(IRow topRow)
        {
            Regex numberRegex = new Regex("\\d+");
            int qty = 0;
            for (int i = 0; i < topRow.LastCellNum; i++)
            {
                ICell cell = topRow.GetCell(i);
                if (cell.CellComment != null)
                {
                    Match match = numberRegex.Match(cell.CellComment.String.String);
                    if (match.Success)
                    {
                        int index = int.Parse(match.Value);
                        if (index > qty)
                        {
                            qty = index;
                        }
                    }
                }
            }
            return qty;
        }
    }


    /// <summary>
    /// NOPIRow 扩展类
    /// </summary>
    public static class OPNPOIRowExpress
    {

        /// <summary>
        /// 根据表达式生成列头标签（读取相关的DisplayName）
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <typeparam name="TProperty"></typeparam>
        /// <param name="Row"></param>
        /// <param name="expression"></param>
        /// <param name="suffix"></param>
        /// <returns></returns>
        public static OPNPOIRow<TModel> Cell<TModel, TProperty>(this OPNPOIRow<TModel> Row, Expression<Func<TModel, TProperty>> expression, string suffix = null) where TModel : class, new()
        {
            if (expression.Body.NodeType == ExpressionType.MemberAccess)
            {
                MemberExpression memberExpression = expression.Body as MemberExpression;
                PropertyInfo propertyInfo = memberExpression.Member as PropertyInfo;
                IList<CustomAttributeTypedArgument> displayNameAttr = propertyInfo.CustomAttributes.Where(x => x.AttributeType == typeof(DisplayNameAttribute)).Select(x => x.ConstructorArguments).FirstOrDefault();
                Row.CellExpressions.Add(expression);
                if (displayNameAttr != null)
                {
                    string name = displayNameAttr[0].Value as string;
                    Row.Cell(name + suffix);
                }
                else
                {
                    Row.Cell(propertyInfo.Name);
                }
                return Row;
            }
            return null;
        }
        public static OPNPOIRow<TModel> CellWithName<TModel, TProperty>(this OPNPOIRow<TModel> Row, Expression<Func<TModel, TProperty>> expression, string colName) where TModel : class, new()
        {
            if (expression.Body.NodeType == ExpressionType.MemberAccess)
            {
                MemberExpression memberExpression = expression.Body as MemberExpression;
                PropertyInfo propertyInfo = memberExpression.Member as PropertyInfo;
                Row.CellExpressions.Add(expression);
                if (!string.IsNullOrEmpty(colName))
                {
                    Row.Cell(colName);
                }
                else
                {
                    Row.Cell(propertyInfo.Name);
                }
                return Row;
            }
            return null;
        }
    }
    public static class WBWriteExpress
    {
        public static byte[] WriteBytes(this IWorkbook wb)
        {
            NpoiMemoryStream ms = new NpoiMemoryStream
            {
                AllowClose = false
            };
            wb.Write(ms);
            byte[] data = new byte[ms.Length];
            ms.Seek(0, SeekOrigin.Begin);
            ms.Read(data, 0, data.Length);
            ms.AllowClose = true;
            return data;
        }
    }
    /// <summary>
    /// NOPI流的特殊处理
    /// </summary>
    public class NpoiMemoryStream : MemoryStream
    {
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
            {
                base.Close();
            }
        }

        public NpoiMemoryStream(Stream stream) : this()
        {
            byte[] data = new byte[stream.Length];
            stream.Read(data, 0, data.Length);
            Write(data, 0, data.Length);
            stream.Close();
            Seek(0, SeekOrigin.Begin);
        }
    }
}