using iWareCommon.Common.EnumType;
using iWareCommon.Utils;
using iWareExcel.EXCEL.Entity;
using iWareExcel.Properties;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace iWareExcel.Utils
{
   public class ExcelHelper
    {


        /// <summary>
        /// 许艺潇
        /// 将数据导出到excel(通过EPPlus插件)
        /// </summary>
        /// <param name="workBook">导出的样式</param>
        /// <param name="datas">以工作页名为键，工作页数据为值的字典</param>
        /// <param name="msg">异常错误信息</param>
        /// <returns>导出excel的地址</returns>
        public static string ToExcelByEPPlus(WorkBookEntity workBook, Dictionary<string, List<object>> datas, out string msg)
        {

            if (workBook.NeedTemplate == (int)EYesOrNo.是)
            {
                if (!File.Exists(workBook.TemplatePath))
                {
                    msg = string.Format("没有找到模板文件：{0}", workBook.TemplatePath);
                    return "";
                }
            }
            var folder = string.IsNullOrEmpty(workBook.ServerPath) ? @"c:\excel\" : workBook.ServerPath;
            if (!folder.EndsWith(@"\"))
            {
                folder += @"\";
            }

            var now = DateTime.Now;

            folder += string.Format(@"{0}\{1}\{2}\{3}\", now.Year, now.Month, now.Day, (long)(now - Convert.ToDateTime("1970-01-01 08:00:00")).TotalMilliseconds);

            if (!Directory.Exists(folder))
            {
                Directory.CreateDirectory(folder);
            }
            var fileName = string.IsNullOrEmpty(workBook.ServerFileName) ? "default.xlsx" : workBook.ServerFileName;


            //拼装服务器上的路径    
            var path = folder + fileName;
            try
            {
                if (workBook.NeedTemplate == (int)EYesOrNo.是)
                {
                    File.Copy(workBook.TemplatePath, path, true);
                }

                if ((File.Exists(path) && workBook.NeedTemplate == (int)EYesOrNo.是) || workBook.NeedTemplate == (int)EYesOrNo.否)
                {
                    using (var package = new ExcelPackage(new FileInfo(path)))
                    {
                        LogTextHelper.WriteLog(Resources.LogDir, "ExcelHelper", "开始装数据", path);
                        for (int i = 0; i < workBook.WorkSheets.Count; i++)
                        {
                            var worksheet = workBook.NeedTemplate == (int)EYesOrNo.否 ? (ExcelWorksheet)package.Workbook.Worksheets.Add(workBook.WorkSheets[i].Name) : (ExcelWorksheet)package.Workbook.Worksheets[workBook.WorkSheets[i].Name];

                            var workSheet = workBook.WorkSheets[i];

                            worksheet.Name = workBook.WorkSheets[i].Name;

                            var headStartLine = workBook.NeedTemplate == (int)EYesOrNo.是 ? workSheet.HeadStartLine : 1;


                            for (int j = 0; j < workSheet.WorkCells.Count; j++)
                            {
                                var workCell = workSheet.WorkCells[j];
                                worksheet.Cells[headStartLine, j + 1].Value = workCell.DisplayName;
                                worksheet.Cells[headStartLine, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 
                                worksheet.Cells[headStartLine, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                worksheet.Cells[headStartLine, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                worksheet.Cells[headStartLine, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                worksheet.Cells[headStartLine, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                //worksheet.Cells[headStartLine, j + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//边框常规粗细
                            }

                            if (datas.ContainsKey(workSheet.Name))
                            {
                                Type type = Type.GetType(workSheet.ClassName);

                                var tProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();

                                var propNames = new List<string>();

                                workSheet.WorkCells.ForEach(x => propNames.Add(x.PropName));

                                var tPropDic = new Dictionary<string, PropertyInfo>();


                                //遍历S的所有属性,将符合修改的存入字典中 
                                tProps.ForEach(p => { if (propNames.Contains(p.Name)) { tPropDic.Add(p.Name, p); } });


                                var ds = datas[workSheet.Name];

                                var dataStartLine = workBook.NeedTemplate == (int)EYesOrNo.是 ? workSheet.DataStartLine : 2;

                                for (int j = 0; j < ds.Count; j++)
                                {
                                    var data = ds[j];

                                    for (int k = 0; k < workSheet.WorkCells.Count; k++)
                                    {
                                        var workCell = workSheet.WorkCells[k];

                                        var value = tPropDic[workCell.PropName].GetValue(data, null);

                                        worksheet.Cells[j + dataStartLine, k + 1].Value = workCell.IsEnum == (int)EYesOrNo.是 ? Enum.Parse(Type.GetType(workCell.EnumClass), value.ToString().Trim()).ToString() : (value == null ? "" : value.ToString().Trim());
                                        worksheet.Cells[j + dataStartLine, k + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 
                                        worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                        worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                        worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                        worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
                                    }
                                }
                            }
                        }
                        package.Save();

                    }
                }
                msg = "";
                return path;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                LogTextHelper.WriteLog(Resources.LogDir, "ExcelHelper", "ToExcelByEPPlus", msg);
                return path;
            }
        }


        /// <summary>
        /// 许艺潇
        /// 将excel的表转为数据集列表(通过EPPlus插件)
        /// </summary>
        /// <param name="path">导入的excel文件路径</param>
        /// <param name="workBook">excel的表</param>
        /// <param name="msg">异常错误信息</param>
        /// <returns>转换后的数据集列表</returns>
        public static List<List<object>> FromExcelByEPPlus(string path, WorkBookEntity workBook, out string msg)
        {
            msg = "";

            //文件不存在
            if (!File.Exists(path))
            {
                msg = "需要的excel文件不存在";
                return null;
            }

            //文件后缀不是xls或xlsx
            var fileSuffix = Path.GetExtension(path).ToUpper();

            if (!".XLS".Equals(fileSuffix) && !".XLSX".Equals(fileSuffix))
            {
                msg = "该文件不是excel文件";
                return null;
            }

            try
            {
                var result = new List<List<object>>();
                ExcelPackage package = new ExcelPackage(new FileInfo(path));

                for (int i = 0; i < workBook.WorkSheets.Count; i++)
                {
                    var workSheet = workBook.WorkSheets[i];
                    var worksheet = package.Workbook.Worksheets[workSheet.Name];
                    //(Worksheet)workbook.Worksheets[i + 1];

                    if (!workSheet.Name.Equals(worksheet.Name))
                    {
                        msg = "导入的文件不正确";
                        return null;
                    }

                    Type type = Type.GetType(workSheet.ClassName);
                    var tProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
                    var propNames = new List<string>();
                    workSheet.WorkCells.ForEach(x => propNames.Add(x.PropName));
                    var tPropDic = new Dictionary<string, PropertyInfo>();


                    //遍历S的所有属性,将符合修改的存入字典中 
                    tProps.ForEach(p => { if (propNames.Contains(p.Name)) { tPropDic.Add(p.Name, p); } });

                    var datas = new List<object>();

                    var dataStartLine = workBook.NeedTemplate == (int)EYesOrNo.是 ? workSheet.DataStartLine : 2;

                    //是否到最后一行
                    bool endOfLine = false;

                    //当前行数
                    int lineIndex = 0;

                    while (!endOfLine)
                    {
                        //判断是否为空数据
                        bool nullFlag = true;

                        var data = type.Assembly.CreateInstance(type.ToString());

                        for (int j = 0; j < workSheet.WorkCells.Count; j++)
                        {
                            var workCell = workSheet.WorkCells[j];

                            object value = ((ExcelRangeBase)worksheet.Cells[dataStartLine + lineIndex, j + 1]).Value;

                            if (value != null && !string.IsNullOrEmpty(value.ToString()))
                            {
                                nullFlag = false;
                                value = workCell.IsEnum == (int)EYesOrNo.是 ? ((int)Enum.Parse(Type.GetType(workCell.EnumClass), value.ToString())).ToString() : value.ToString();
                                switch (workCell.DataType)
                                {
                                    case (int)EDataType.字符串类型:
                                        value = value.ToString().Trim();
                                        if (value.ToString().EndsWith("\r\n"))
                                        {
                                            value = value.ToString().Substring(0, value.ToString().Length - 2);
                                        }
                                        break;
                                    case (int)EDataType.整数类型:
                                        value = Convert.ToInt32(value.ToString().Trim());
                                        break;
                                    case (int)EDataType.小数类型:
                                        value = Convert.ToDecimal(value.ToString().Trim());
                                        break;
                                    case (int)EDataType.布尔类型:
                                        value = Convert.ToBoolean(value.ToString().Trim());
                                        break;
                                    case (int)EDataType.日期类型:

                                        value = Convert.ToDateTime(value.ToString().Trim());
                                        break;
                                    default:
                                        break;
                                }
                                tPropDic[workCell.PropName].SetValue(data, value, null);
                            }
                            else
                            {
                                switch (workCell.DataType)
                                {
                                    case (int)EDataType.字符串类型:
                                        value = "";
                                        break;
                                    case (int)EDataType.整数类型:
                                        value = 0;
                                        break;
                                    case (int)EDataType.小数类型:
                                        value = 0.0M;
                                        break;
                                    case (int)EDataType.布尔类型:
                                        value = false;
                                        break;
                                    case (int)EDataType.日期类型:

                                        value = DateTime.Now;
                                        break;
                                    default:
                                        break;
                                }
                                tPropDic[workCell.PropName].SetValue(data, value, null);
                            }
                        }


                        if (!nullFlag)
                        {
                            datas.Add(data);
                            lineIndex++;
                            continue;
                        }
                        endOfLine = true;
                    }

                    result.Add(datas);
                }
                return result;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                LogTextHelper.WriteLog(Resources.LogDir, "ExcelHelper", "FromExcelByEPPlus", ex.Message);
                return null;
            }
        }

    }
}