using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Siger.Middlelayer.Utility.ExcelImport;
using ExcelColumn = Siger.Middlelayer.Utility.ExcelImport.ExcelColumn;

namespace Siger.Middlelayer.Utility.Helpers
{
    public class EpPlusExcelHelper<T> : IDisposable where T : ImportBase, new()
    {
        private ExcelWorksheet Sheet { get; }
        private readonly int _language = 0;

        public EpPlusExcelHelper(int language = 0)
        {
            _language = language;
        }

        public EpPlusExcelHelper(string filePath, int language = 0)
        {
            _language = language;
            if (File.Exists(filePath))
            {
                var file = new FileInfo(filePath);
                var excelPackage = new ExcelPackage(file);
                if (excelPackage.Workbook != null && excelPackage.Workbook.Worksheets.Count > 0)
                {
                    Sheet = excelPackage.Workbook.Worksheets.First();
                }
                else
                {
                    throw new Exception("read excel failed.");
                }
            }
        }

        /// <summary>
        /// create excel file by list and filepath
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="longTimeFormat"></param>
        /// <param name="category"></param>
        public void GenerateExcel(IEnumerable<T> list, string filePath, bool longTimeFormat = false, EchartCategory category = EchartCategory.None)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            try
            {
                ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add("sheet1");
                ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("sheet2");

                if (category != EchartCategory.None)
                {
                    AddData(worksheet2, list, longTimeFormat, category == EchartCategory.SlicePercentColumnStacked);

                    CreateEChart(worksheet1, worksheet2, category, list.Count());
                }
                else
                {
                    AddData(worksheet1, list, longTimeFormat);
                }
                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }
        /// <summary>
        /// 模板文件导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="templateFilePath">模板文件路径</param>
        /// <param name="filePath">目标输出路径</param>
        /// <param name="longTimeFormat"></param>
        public void GenerateExcelByTemplate(IEnumerable<T> list, string templateFilePath, string filePath, bool longTimeFormat = false)
        {
            ExcelWorksheet templateSheet;
            ExcelWorksheet dataSheet;
            if (!File.Exists(templateFilePath))
            {
                throw new Exception("template not exists failed.");
            }
            File.Copy(templateFilePath, filePath, true);
            var package = new ExcelPackage(new FileInfo(filePath));
            try
            {
                if (package.Workbook != null && package.Workbook.Worksheets.Count > 1)
                {
                    templateSheet = package.Workbook.Worksheets.First();
                    var sheetNames = package.Workbook.Worksheets.AsQueryable().Select(s => s.Name);
                    if (!sheetNames.Contains("data"))
                    {
                        throw new Exception("read template failed: no data sheet.");
                    }
                    dataSheet = package.Workbook.Worksheets["data"];
                }
                else
                {
                    throw new Exception("read template failed.");
                }
                AddData(dataSheet, list, longTimeFormat);
                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }

        private void AddData(ExcelWorksheet worksheet, IEnumerable<T> list, bool longTimeFormat, bool rowTransfer = false)
        {
            //add head
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(T)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(ColumnOnly)
                            && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetExportAttribute)))
                .Select(p => new
                {
                    Property = p,
                    Column = (_language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                        ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                        : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                }).ToList();

            for (var i = 0; i < columns.Count; i++)
            {
                worksheet.Column(i + 1).Width = 20;
            }
            worksheet.Column(1).Width = 16;
            worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells.Style.ShrinkToFit = true;

            //add content
            var entities = list.ToList();
            if (rowTransfer) //行列转换
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[i + 1, 1].Value = columns[i].Column;
                }

                for (var j = 0; j < columns.Count; j++)
                {
                    for (var i = 0; i < entities.Count; i++)
                    {
                        var entity = entities[i];
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        worksheet.Cells[j + 1, i + 2].Value = value;
                    }
                }
            }
            else
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columns[i].Column;
                }
                for (var i = 0; i < entities.Count; i++)
                {
                    var entity = entities[i];
                    for (var j = 0; j < columns.Count; j++)
                    {
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        var propertyType = columns[j].Property.PropertyType;
                        if (propertyType.ToString().Contains("System.DateTime"))
                        {
                            if (value != null)
                            {
                                var date = DateTime.Parse(value.ToString());
                                if (date != DateTime.MinValue)
                                {
                                    var result = date.ToString((longTimeFormat) ? DateFormatHelper.DateTimeFormat : DateFormatHelper.DateFormat);
                                    worksheet.Cells[i + 2, j + 1].Value = result;
                                }
                            }
                        }
                        //other type
                        else
                        {
                            worksheet.Cells[i + 2, j + 1].Value = value;
                        }
                    }
                    worksheet.Cells[i + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }
        }

        private void CreateEChart(ExcelWorksheet worksheet1, ExcelWorksheet worksheet2, EchartCategory category, int rowCount)
        {
            var helper = new EpPlusEchartHelper(worksheet1, worksheet2, rowCount);
            switch (category)
            {
                case EchartCategory.Oee:
                    helper.CreateOeeChart();
                    break;
                case EchartCategory.OeeDetail:
                    helper.CreateOeeChart();
                    break;
                case EchartCategory.SlicePercentColumnStacked:
                    helper.CreateSliceChart();
                    break;
                case EchartCategory.EfficiencyAnalysis:
                    helper.CreateEfficiencyAnalysis();
                    break;
                default:
                    break;
            }
        }

        /// <summary>
        /// 导出合并单元格
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="MergeInfo">需要合并行信息</param>
        /// <param name="ColInfo">指定合并的列</param>
        /// <param name="longTimeFormat"></param>
        public void GenerateExcel(IEnumerable<T> list, string filePath, List<MergeRow> MergeInfo, List<int> ColInfo, bool longTimeFormat = false)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            var worksheet = package.Workbook.Worksheets.Add("sheet1");

            try
            {
                //add head
                bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
                var columns = typeof(T)
                    .GetProperties()
                    .Where(x => x.CustomAttributes.Any(ColumnOnly)
                                && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetExportAttribute)))
                    .Select(p => new
                    {
                        Property = p,
                        Column = (_language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                            ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                            : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                    }).ToList();

                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columns[i].Column;
                    worksheet.Column(i + 1).Width = 18;
                }

                worksheet.Column(1).Width = 10;
                worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells.Style.ShrinkToFit = true;

                //add content
                var rows = 0;
                var col = 0;
                var entities = list.ToList();
                for (var i = 0; i < entities.Count; i++)
                {
                    if (i == 0)
                        rows = i + 2;
                    var entity = entities[i];
                    for (var j = 0; j < columns.Count; j++)
                    {

                        col = j + 1;
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        var propertyType = columns[j].Property.PropertyType;
                        if (propertyType.ToString().Contains("System.DateTime"))
                        {
                            if (value != null)
                            {
                                var date = DateTime.Parse(value.ToString());
                                if (date != DateTime.MinValue)
                                {
                                    var result = date.ToString((longTimeFormat) ? DateFormatHelper.DateTimeFormat : DateFormatHelper.DateFormat);
                                    worksheet.Cells[i + 2, j + 1].Value = result;
                                }
                            }
                        }
                        //other type
                        else
                        {
                            worksheet.Cells[i + 2, j + 1].Value = value;
                        }
                        //mergeCells
                        #region  mergeCells
                        if (MergeInfo.Any())
                        {
                            if (ColInfo.Exists(f => f.Equals(col)))
                            {
                                if (!MergeInfo[i].Show)
                                    worksheet.Cells[rows, col, i + 2, col].Merge = true;
                                else
                                    rows = i + 2;
                            }

                        }
                        #endregion


                    }
                    worksheet.Cells[i + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }

                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }
        private int GetColumnByName(string columnName)
        {
            if (Sheet == null)
            {
                throw new ArgumentNullException(nameof(Sheet));
            }

            var column = Sheet.Cells["1:1"].FirstOrDefault(c => c.Value.ToString() == columnName);
            if (column == null)
            {
                return 0;
            }
            return Sheet.Cells["1:1"].First(c => c.Value.ToString() == columnName).Start.Column;
        }

        /// <summary>
        /// convert excel sheet1 to list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IEnumerable<T> ConvertSheetToList()
        {
            if (Sheet == null)
            {
                throw new ArgumentNullException(nameof(Sheet));
            }
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(T)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(ColumnOnly) && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetValidateAttribute) &&
                                                                                              m.AttributeType != typeof(DonnetCreateAttribute)))
                .Select(p => new
                {
                    Property = p,
                    Column = (_language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                        ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                        : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                }).ToList();

            var endRow = 2;
            for (var row = 2; row <= Sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columns.Count; i++)
                {
                    var val = Sheet.Cells[row, i];
                    if (val != null)
                    {
                        columnValues.Add(val.Text);
                    }
                }

                if (columnValues.All(string.IsNullOrWhiteSpace))
                {
                    endRow = row - 1;
                    break;
                }

                endRow = row;
            }

            IList<int> rows = new List<int>();
            for (var i = 2; i <= endRow; i++)
            {
                rows.Add(i);
            }
            var collection = rows
                .Select(row =>
                {
                    var tnew = new T();
                    columns.ForEach(col =>
                    {
                        var val = Sheet.Cells[row, GetColumnByName(col.Column)];
                        if (val.Value == null)
                        {
                            if (col.Property.PropertyType == typeof(int))
                                col.Property.SetValue(tnew, 0);
                            if (col.Property.PropertyType == typeof(double))
                                col.Property.SetValue(tnew, 0d);
                            if (col.Property.PropertyType == typeof(decimal))
                                col.Property.SetValue(tnew, 0);
                            if (col.Property.PropertyType == typeof(DateTime))
                                col.Property.SetValue(tnew, DateTime.Now);
                            if (col.Property.PropertyType == typeof(string))
                                col.Property.SetValue(tnew, string.Empty);

                            return;
                        }

                        if (col.Property.PropertyType == typeof(int))
                        {
                            col.Property.SetValue(tnew, val.GetValue<int>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(decimal))
                        {
                            col.Property.SetValue(tnew, val.GetValue<decimal>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(double))
                        {
                            col.Property.SetValue(tnew, val.GetValue<double>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(DateTime?))
                        {
                            col.Property.SetValue(tnew, val.GetValue<DateTime?>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(DateTime))
                        {
                            col.Property.SetValue(tnew, val.GetValue<DateTime>());
                            return;
                        }

                        if (col.Property.PropertyType == typeof(bool))
                        {
                            col.Property.SetValue(tnew, val.GetValue<bool>());
                            return;
                        }

                        col.Property.SetValue(tnew,
                            string.IsNullOrWhiteSpace(val.GetValue<string>()) ? "" : val.GetValue<string>());
                    });

                    return tnew;
                });
            return collection;
        }

        public List<string> CheckExcel()
        {
            if (Sheet == null)
            {
                throw new ArgumentNullException(nameof(Sheet));
            }

            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var messages = new List<string>();
            var columns = typeof(T)
                 .GetProperties()
                 .Where(x => x.CustomAttributes.Any(ColumnOnly) && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetValidateAttribute) &&
                                                                                               m.AttributeType != typeof(DonnetCreateAttribute)))
                 .Select(p => new
                 {
                     Property = p,
                     Column = (_language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                         ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                         : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                 }).ToList();

            var rows = Sheet.Cells.Select(cell => cell.Start.Row).Distinct().OrderBy(x => x).ToList().Skip(1);
            if (!rows.Any())
            {
                messages.Add($"{(int)ImportEnum.NoRecordForImport}");
                return messages;
            }

            var endRow = 2;
            for (var row = 2; row <= Sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columns.Count; i++)
                {
                    var val = Sheet.Cells[row, i];
                    if (val != null)
                    {
                        columnValues.Add(val.Text);
                    }
                }

                if (!columnValues.All(string.IsNullOrWhiteSpace))
                {
                    continue;
                }
                endRow = row - 1;
                break;
            }

            for (var row = 2; row <= endRow; row++)
            {
                foreach (var col in columns)
                {
                    var column = GetColumnByName(col.Column);
                    if (column == 0)
                    {
                        messages.Add($"{row},{(int)ImportEnum.ColumnNameNotFound}");
                        continue;
                    }
                    var val = Sheet.Cells[row, column];
                    if (val == null)
                    {
                        continue;
                    }
                    //非空验证
                    var emptyValidate = col.Property.CustomAttributes.FirstOrDefault(m => m.AttributeType == typeof(EmptyValidate));
                    if (emptyValidate != null)
                    {
                        if (string.IsNullOrWhiteSpace(val.Text))
                        {
                            var errorMessage = emptyValidate.ConstructorArguments[0].Value;
                            var errorKey = (int)Enum.Parse(typeof(ImportEnum), errorMessage.ToString());
                            messages.Add($"{row},{errorKey}");
                        }
                    }
                    //正则验证
                    var expressionValidate = col.Property.CustomAttributes.FirstOrDefault(m => m.AttributeType == typeof(RegularExpressionValidate));
                    if (expressionValidate != null)
                    {
                        if (!string.IsNullOrWhiteSpace(val.Text))
                        {
                            var expression = expressionValidate.ConstructorArguments[0].Value.ToString();
                            var errorMessage = expressionValidate.ConstructorArguments[1].Value.ToString();
                            var regex = new Regex(expression);
                            if (!regex.IsMatch(val.Text))
                            {
                                var errorKey = (int)Enum.Parse(typeof(ImportEnum), errorMessage);
                                messages.Add($"{row},{errorKey}");
                            }
                        }
                    }

                    if (string.IsNullOrWhiteSpace(val.Text))
                    {
                        continue;
                    }

                    bool result;
                    if (col.Property.PropertyType == typeof(int))
                    {
                        result = int.TryParse(val.Text, out var _);
                        if (!result)
                        {
                            messages.Add($"{row},{(int)ImportEnum.NumberIsValid}");
                        }
                    }

                    if (col.Property.PropertyType == typeof(DateTime))
                    {
                        result = DateTime.TryParse(val.Text, out DateTime _);
                        if (!result)
                        {
                            messages.Add($"{row},{(int)ImportEnum.DateTimeIsInValid}");
                        }
                    }
                }
            }

            return messages;
        }

        public void GenerateExcel<T1, T2, T3>(IEnumerable<T1> list1, IEnumerable<T2> list2, IEnumerable<T3> list3, string filePath, bool longTimeFormat = false)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            try
            {
                ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add("sheet1");
                ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("sheet2");
                ExcelWorksheet worksheet3 = package.Workbook.Worksheets.Add("sheet3");
                AddData(worksheet1, list1, longTimeFormat);
                AddData(worksheet2, list2, longTimeFormat);
                AddData(worksheet3, list3, longTimeFormat);
                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }
        public void GenerateExcel<T1, T2>(IEnumerable<T1> list1, IEnumerable<T2> list2, string filePath, bool longTimeFormat = false)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            try
            {
                ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add("sheet1");
                ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("sheet2");
                AddData(worksheet1, list1, longTimeFormat);
                AddData(worksheet2, list2, longTimeFormat);
                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }
        private void AddData<T1>(ExcelWorksheet worksheet, IEnumerable<T1> list, bool longTimeFormat, bool rowTransfer = false)
        {
            //add head
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(T1)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(ColumnOnly)
                            && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetExportAttribute)))
                .Select(p => new
                {
                    Property = p,
                    Column = (_language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                        ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                        : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                }).ToList();

            for (var i = 0; i < columns.Count; i++)
            {
                worksheet.Column(i + 1).Width = 20;
            }
            worksheet.Column(1).Width = 16;
            worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells.Style.ShrinkToFit = true;

            //add content
            var entities = list.ToList();
            if (rowTransfer) //行列转换
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[i + 1, 1].Value = columns[i].Column;
                }

                for (var j = 0; j < columns.Count; j++)
                {
                    for (var i = 0; i < entities.Count; i++)
                    {
                        var entity = entities[i];
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        worksheet.Cells[j + 1, i + 2].Value = value;
                    }
                }
            }
            else
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columns[i].Column;
                }
                for (var i = 0; i < entities.Count; i++)
                {
                    var entity = entities[i];
                    for (var j = 0; j < columns.Count; j++)
                    {
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        var propertyType = columns[j].Property.PropertyType;
                        if (propertyType.ToString().Contains("System.DateTime"))
                        {
                            if (value != null)
                            {
                                var date = DateTime.Parse(value.ToString());
                                if (date != DateTime.MinValue)
                                {
                                    var result = date.ToString((longTimeFormat) ? DateFormatHelper.DateTimeFormat : DateFormatHelper.DateFormat);
                                    worksheet.Cells[i + 2, j + 1].Value = result;
                                }
                            }
                        }
                        //other type
                        else
                        {
                            worksheet.Cells[i + 2, j + 1].Value = value;
                        }
                    }
                    worksheet.Cells[i + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }
        }
        public void GenerateExcels(IEnumerable<T> list, string filePath, int count, bool longTimeFormat = false, EchartCategory category = EchartCategory.None)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            try
            {
                ExcelWorksheet worksheet1 = package.Workbook.Worksheets.Add("sheet1");
                ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("sheet2");

                if (category != EchartCategory.None)
                {
                    AddDatas(worksheet2, list, count, longTimeFormat, category == EchartCategory.SlicePercentColumnStacked);

                    CreateEChart(worksheet1, worksheet2, category, list.Count());
                }
                else
                {
                    AddDatas(worksheet1, list, count, longTimeFormat );
                }
                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }
        private void AddDatas(ExcelWorksheet worksheet, IEnumerable<T> list, int count, bool longTimeFormat, bool rowTransfer = false)
        {
            //add head
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var col = typeof(T)
                .GetProperties()
                .Where(x => x.CustomAttributes.Any(ColumnOnly)
                            && x.CustomAttributes.All(m => m.AttributeType != typeof(DonnetExportAttribute)))
                .Select(p => new
                {
                    Property = p,
                    Column = (_language == 0 || !EpPlusLanguageHelper.LanguageDictionary.ContainsKey(p.GetCustomAttributes<ExcelColumn>().First().ColumnName))
                        ? p.GetCustomAttributes<ExcelColumn>().First().ColumnName
                        : EpPlusLanguageHelper.LanguageDictionary[p.GetCustomAttributes<ExcelColumn>().First().ColumnName]
                }).ToList();


            var columns = col.Where(f => f.Property.GetCustomAttributes<IsShow>().FirstOrDefault() == null || (f.Property.GetCustomAttributes<IsShow>().FirstOrDefault() != null && f.Property.GetCustomAttributes<IsShow>().First().No <= count)).ToList();
            //columns.Where(f =>(f.Property.GetCustomAttributes<IsShow>().FirstOrDefault() != null && f.Property.GetCustomAttributes<IsShow>().First().No >= count));




            for (var i = 0; i < columns.Count; i++)
            {
                worksheet.Column(i + 1).Width = 20;
            }
            worksheet.Column(1).Width = 16;
            worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells.Style.ShrinkToFit = true;

            //add content
            var entities = list.ToList();
            if (rowTransfer) //行列转换
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[i + 1, 1].Value = columns[i].Column;
                }

                for (var j = 0; j < columns.Count; j++)
                {
                    for (var i = 0; i < entities.Count; i++)
                    {
                        var entity = entities[i];
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        worksheet.Cells[j + 1, i + 2].Value = value;
                    }
                }
            }
            else
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columns[i].Column;
                }
                for (var i = 0; i < entities.Count; i++)
                {
                    var entity = entities[i];
                    for (var j = 0; j < columns.Count; j++)
                    {
                        var value = entity.GetType().InvokeMember(columns[j].Property.Name,
                            BindingFlags.GetProperty, null, entity, null);

                        var propertyType = columns[j].Property.PropertyType;
                        if (propertyType.ToString().Contains("System.DateTime"))
                        {
                            if (value != null)
                            {
                                var date = DateTime.Parse(value.ToString());
                                if (date != DateTime.MinValue)
                                {
                                    var result = date.ToString((longTimeFormat) ? DateFormatHelper.DateTimeFormat : DateFormatHelper.DateFormat);
                                    worksheet.Cells[i + 2, j + 1].Value = result;
                                }
                            }
                        }
                        //other type
                        else
                        {
                            worksheet.Cells[i + 2, j + 1].Value = value;
                        }
                    }
                    worksheet.Cells[i + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }
        }

        public void Dispose()
        {
            Sheet?.Dispose();
        }
    }
}
