using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;

namespace HMI.Util
{
    public class ExcelHelper
    {
        public static void ExportExcel<T>(string targetPath, IList<T> datas)
        {
            Type type = typeof(T);
            var properties = PropertyHelper.GetAtterProperty<T, ExcelColumnAttribute>(out var attrs);
            if (properties == null || properties.Count <= 0)
            {
                throw new Exception("No Attribute");
            }
            IWorkbook workbook = null;
            if (targetPath.EndsWith(".xlsx"))
            {
                workbook = new XSSFWorkbook();
            }
            else if (targetPath.EndsWith(".xls"))
            {
                workbook = new HSSFWorkbook();
            }
            workbook.CreateSheet("sheet1");////新建一个工作表
            ISheet SheetOne = workbook.GetSheet("Sheet1");
            IRow row1 = SheetOne.CreateRow(0);
            for (int i = 0; i < properties.Count; i++)
            {
                row1.CreateCell(i).SetCellValue((attrs[i]).Name);
            }

            for (int i = 0; i < datas.Count(); i++)
            {
                IRow rowTemp = SheetOne.CreateRow(i + 1);
                for (int j = 0; j < properties.Count; j++)
                {
                    var value = properties[j].GetValue(datas[i]);
                    rowTemp.CreateCell(j).SetCellValue(value.ToString());
                }
            }

            using (FileStream fs = new FileStream(targetPath, FileMode.OpenOrCreate))
            {
                workbook.Write(fs);
            }
        }


        public static List<T> ReadFromExcel<T>(string sourcePath)
        {
            Type type = typeof(T);
            var properties = PropertyHelper.GetAtterProperty<T, ExcelColumnAttribute>(out var attrs);
            if (properties == null || properties.Count <= 0)
            {
                throw new Exception("No Attribute");
            }
            Dictionary<string, PropertyInfo> dict = new Dictionary<string, PropertyInfo>();
            for (int i = 0; i < attrs.Count; i++)
            {
                dict.Add(attrs[i].Name, properties[i]);
            }
            using (FileStream fileStream1 = new FileStream(sourcePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null;
                if (sourcePath.EndsWith(".xlsx"))
                {
                    workbook = new XSSFWorkbook(fileStream1);
                }
                else if (sourcePath.EndsWith(".xls"))
                {
                    workbook = new HSSFWorkbook(fileStream1);
                }

                ISheet sheet = workbook.GetSheetAt(0);
                IRow titleRow = sheet.GetRow(0);

                List<T> result = new List<T>();
                IRow row;
                for (int i = 1; i < (sheet.LastRowNum + 1); i++)
                {
                    row = sheet.GetRow(i);
                    if (row != null)
                    {
                        T obj = (T)Activator.CreateInstance(type);
                        bool isEmpty = true;
                        for (int j = 0; j < row.Count(); j++)
                        {
                            string title = titleRow.GetCell(j).StringCellValue;
                            if (dict.ContainsKey(title))
                            {
                                CellType ct = row.GetCell(j).CellType;
                                object value = null;
                                if (ct == CellType.String)
                                    value = row.GetCell(j).StringCellValue;
                                else if (ct == CellType.Numeric)
                                    value = row.GetCell(j).NumericCellValue;
                                else if (ct == CellType.Boolean)
                                    value = row.GetCell(j).BooleanCellValue;

                                var prop = dict[title];
                                if (prop.PropertyType is string)
                                {
                                    value = Convert.ToString(value);
                                }
                                else if (prop.PropertyType is double || prop.PropertyType is double?)
                                {
                                    value = Convert.ToDouble(value);
                                }
                                else if (prop.PropertyType is int || prop.PropertyType is int?)
                                {
                                    value = Convert.ToInt16(value);
                                }
                                else if (prop.PropertyType is bool || prop.PropertyType is bool?)
                                {
                                    value = Convert.ToBoolean(value);
                                }

                                prop.SetValue(obj, value);
                                isEmpty = false;
                            }
                        }
                        if (!isEmpty) result.Add(obj);
                    }
                }
                workbook.Close();
                return result;
            }
        }
    }
}