using EnumType;
using NPOI.SS.UserModel;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace IWareDataAccess.Helper
{
   public static  class ExcelHelper
    {

       public static bool UploadFiles(HttpPostedFile file,  string hostpath, out string msg)//
       {
           try
           {
               msg = "";
               if (file == null)
               {
                   msg="上传的文件为空，请重新上传";
                   return false;
               }
               string filename = Path.GetFileName(file.FileName);
               string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
               string NoFileName = Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
               string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
               if (!FileType.Contains(fileEx))
               {
                   msg="无效的文件类型，只支持.xls和.xlsx文件";
                   return false;
               }
       
               //源数据
               MemoryStream msSource = new MemoryStream();
               var fstream = file.InputStream;
               byte[] b = new byte[fstream.Length];
               fstream.Read(b, 0, b.Length);
               msSource.Write(b, 0, b.Length);
               msSource.Seek(0, SeekOrigin.Begin);
               DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true);

               //模板数据
               string dataDir = hostpath;//获得当前服务器程序的运行目录  
               dataDir = Path.Combine(dataDir, "ExcelTemplate");
               var path = dataDir + "//冲压计划.xlsx";
               MemoryStream msModel = new MemoryStream();
               FileStream stream = new FileStream(path, FileMode.Open);
               stream.CopyTo(msModel);
               msModel.Seek(0, SeekOrigin.Begin);
               DataTable templateExcel = ReadStreamToDataTable(stream, "", true);
               //验证是否同模板相同 
               string columnName = templateExcel.Columns[0].ColumnName;
               if (columnName != sourceExcel.Columns[0].ColumnName)
               {
                   msg="上传的模板文件不正确";
                   return false;
               }
               // 处理后台逻辑 执行 插入操作

     
               return true;
           }
           catch (Exception ex)
           {
                msg="上传的模板文件不正确";
                return false;
           }
       }
    

       public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
       {
           //定义要返回的datatable对象
           DataTable data = new DataTable();
           //excel工作表
           ISheet sheet = null;
           //数据开始行(排除标题行)
           int startRow = 0;
           try
           {
               //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
               IWorkbook workbook = WorkbookFactory.Create(fileStream);
               //如果有指定工作表名称
               if (!string.IsNullOrEmpty(sheetName))
               {
                   sheet = workbook.GetSheet(sheetName);
                   //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                   if (sheet == null)
                   {
                       sheet = workbook.GetSheetAt(0);
                   }
               }
               else
               {
                   //如果没有指定的sheetName，则尝试获取第一个sheet
                   sheet = workbook.GetSheetAt(0);
               }
               if (sheet != null)
               {
                   IRow firstRow = sheet.GetRow(0);
                   //一行最后一个cell的编号 即总的列数
                   int cellCount = firstRow.LastCellNum;
                   //如果第一行是标题列名
                   if (isFirstRowColumn)
                   {
                       for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                       {
                           ICell cell = firstRow.GetCell(i);
                           if (cell != null)
                           {
                               string cellValue = cell.StringCellValue;
                               if (cellValue != null)
                               {
                                   DataColumn column = new DataColumn(cellValue);
                                   data.Columns.Add(column);
                               }
                           }
                       }
                       startRow = sheet.FirstRowNum + 1;
                   }
                   else
                   {
                       startRow = sheet.FirstRowNum;
                   }
                   //最后一列的标号
                   int rowCount = sheet.LastRowNum;
                   for (int i = startRow; i <= rowCount; ++i)
                   {
                       IRow row = sheet.GetRow(i);
                       if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null　　　　　　　

                       DataRow dataRow = data.NewRow();
                       for (int j = row.FirstCellNum; j < cellCount; ++j)
                       {
                           //同理，没有数据的单元格都默认是null
                           ICell cell = row.GetCell(j);
                           if (cell != null)
                           {
                               if (cell.CellType == CellType.Numeric)
                               {
                                   //判断是否日期类型
                                   if (DateUtil.IsCellDateFormatted(cell))
                                   {
                                       dataRow[j] = row.GetCell(j).DateCellValue;
                                   }
                                   else
                                   {
                                       dataRow[j] = row.GetCell(j).ToString().Trim();
                                   }
                               }
                               else
                               {
                                   dataRow[j] = row.GetCell(j).ToString().Trim();
                               }
                           }
                       }
                       data.Rows.Add(dataRow);
                   }
               }
               return data;
           }
           catch (Exception ex)
           {
               throw ex;
           }

       }


       public static List<object> FromExcelByEPPlus<T>(string path, string sheetname,T  t,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>>();
               var package = new ExcelPackage(new FileInfo(path));

                var worksheet = package.Workbook.Worksheets[sheetname];

                if (worksheet == null || !sheetname.Equals(worksheet.Name))
                   {
                       msg = string.Format("未找到Excel工作页{0}", sheetname);
                       return null;
                   }

                Type type = t.GetType();
                var tProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
                var propNames = new List<string>();
                var tPropDic = new Dictionary<string, PropertyInfo>();


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

                   var datas = new List<object>();

                   var 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 < propNames.Count; j++)
                       {
                           var workCell = tPropDic[propNames[j]];

                           object value = ((ExcelRangeBase)worksheet.Cells[dataStartLine + lineIndex, j + 1]).Value;
                           var pty = workCell.PropertyType.FullName;//获得属性的类型
                           if (value != null && !string.IsNullOrEmpty(value.ToString()))
                           {
                               nullFlag = false;
                               if (pty == typeof(int).FullName)
                               {
                                   value = Convert.ToInt32(value.ToString().Trim());
                               }
                               else if (pty == typeof(double).FullName)
                               {
                                   value = Convert.ToDouble(value.ToString().Trim());
                               }
                               else if (pty == typeof(DateTime).FullName)
                               {
                                   value = Convert.ToDateTime(value.ToString().Trim());
                               }
                               else if (pty == typeof(string).FullName)
                               {
                                   value = value.ToString().Trim();
                                   if (value.ToString().EndsWith("\r\n"))
                                   {
                                       value = value.ToString().Substring(0, value.ToString().Length - 2);
                                   }
                               }
                               tPropDic[propNames[j]].SetValue(data, value, null);
                           }
                           else
                           {
                               if (pty == typeof(int).FullName)
                               {
                                   value = 0;
                    
                               }
                               else if (pty == typeof(double).FullName)
                               {
                                   value = 0.0;
                               }
                               else if (pty == typeof(DateTime).FullName)
                               {
                                   value = DateTime.Now; ;
                               }
                               else if (pty == typeof(string).FullName)
                               {
                                   value = "";
                               }

                               tPropDic[propNames[j]].SetValue(data, value, null);
                           }
                       }
                       if (!nullFlag)
                       {
                           datas.Add(data);
                           lineIndex++;
                           continue;
                       }
                       endOfLine = true;
                   }


               return datas;
           }
           catch (Exception ex)
           {
               msg = ex.Message;
               return null;
           }
       }

    }
}