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

namespace CommonUtil.Excel
{
    /// <summary>
    /// Excel工具类
    /// </summary>
    public class ExcelUtil
    {
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entitys">数据源</param>
        /// <param name="title">导出的列名</param>
        /// <param name="listcolum">导出的列</param>
        /// <returns></returns>
        public static byte[] Output<T>(List<T> entitys, string[] title, List<string> listcolum)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("sheet");
            ISheet sheet1 = workbook.CreateSheet("sheet1");
            ISheet sheet2 = workbook.CreateSheet("sheet2");
            IRow Title = null;
            IRow rows = null;
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            try
            {
                for (int i = 0; i <= entitys.Count; i++)
                {
                    if (i == 0)
                    {
                        Title = sheet.CreateRow(0);
                        for (int k = 1; k < title.Length + 1; k++)
                        {
                            Title.CreateCell(0).SetCellValue("序号");
                            Title.CreateCell(k).SetCellValue(title[k - 1]);
                        }

                        continue;
                    }
                    else
                    {
                        rows = sheet.CreateRow(i);
                        object entity = entitys[i - 1];
                        for (int c = 0; c <= listcolum.Count - 1; c++)
                        {
                            for (int j = 1; j <= entityProperties.Length; j++)
                            {
                                if (entityProperties[j - 1].Name == listcolum[c])
                                {
                                    var dem = entityProperties[j - 1].GetValue(entity);
                                    rows.CreateCell(0).SetCellValue(i);

                                    rows.CreateCell(c + 1).SetCellValue(dem.ToString());
                                }
                            }

                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }

            byte[] buffer = new byte[1024 * 2];
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.GetBuffer();
            }
            return buffer;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ms">数据流</param>
        /// <returns></returns>
        public static List<T> InputExcel<T>(Stream ms) where T : new()
        {
            List<T> list = new List<T> { };
            ms.Seek(0, SeekOrigin.Begin);
            IWorkbook workbook = null;
            try
            {
                //xlsx
                workbook = new XSSFWorkbook(ms);
            }
            catch (Exception)
            {
                //xls
                workbook = new HSSFWorkbook(ms);
            }



            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(0);
            var propertys = typeof(T).GetProperties();
            string value = null;
            int num = cellNum.LastCellNum;

            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                var obj = new T();
                for (int j = 0; j < num; j++)
                {
                    //当输出类型属性个数小于文件列数时，跳出
                    if (propertys.Length - 1 < j)
                    {
                        break;
                    }
                    if (row.GetCell(j) == null)
                    {
                        continue;
                    }
                    value = row.GetCell(j).ToString();
                    string str = (propertys[j].PropertyType).FullName;
                    if (str == "System.String")
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                        propertys[j].SetValue(obj, pdt, null);
                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[j].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[j].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[j].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else
                    {
                        propertys[j].SetValue(obj, null, null);
                    }
                }

                list.Add(obj);
            }

            return list;
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ms"></param>
        /// <param name="hasHeader"></param>
        /// <returns></returns>
        public static List<T> InputExcel<T>(Stream ms, bool hasHeader = false) where T : new()
        {
            List<T> list = new List<T> { };
            ms.Seek(0, SeekOrigin.Begin);
            IWorkbook workbook = null;
            try
            {
                //xlsx
                workbook = new XSSFWorkbook(ms);
            }
            catch (Exception)
            {
                //xls
                workbook = new HSSFWorkbook(ms);
            }



            ISheet sheet = workbook.GetSheetAt(0);
            IRow cellNum = sheet.GetRow(0);
            var propertys = typeof(T).GetProperties();
            string value = null;
            int num = cellNum.LastCellNum;

            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                if (hasHeader && i == 0)
                {
                    continue;
                }

                IRow row = sheet.GetRow(i);
                if (row == null || row.Cells.Count != num)
                {
                    continue;
                }
                var obj = new T();
                for (int j = 0; j < num; j++)
                {
                    //当输出类型属性个数小于文件列数时，跳出
                    if (propertys.Length - 1 < j)
                    {
                        break;
                    }

                    value = row.GetCell(j).ToString();
                    string str = (propertys[j].PropertyType).FullName;
                    if (str == "System.String")
                    {
                        propertys[j].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        try
                        {
                            propertys[j].SetValue(obj, row.GetCell(j).DateCellValue, null);
                        }
                        catch (Exception e)
                        {
                            throw e;
                        }

                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[j].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[j].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[j].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[j].SetValue(obj, pb, null);
                    }
                    else
                    {
                        propertys[j].SetValue(obj, null, null);
                    }
                }

                list.Add(obj);
            }

            return list;
        }

        /// <summary>
        /// 导入csv
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ms">数据流</param>
        /// <returns></returns>
        public static List<T> InputCSV<T>(Stream ms) where T : new()
        {
            List<T> list = new List<T> { };
            ms.Seek(0, SeekOrigin.Begin);

            //Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            StreamReader sr = new StreamReader(ms, Encoding.GetEncoding("GB2312"));
            var propertys = typeof(T).GetProperties();
            while (!sr.EndOfStream)
            {
                string[] values = sr.ReadLine().Split(',');
                var obj = new T();
                for (int i = 0; i < values.Length; i++)
                {
                    //当输出类型属性个数小于文件列数时，跳出
                    if (propertys.Length - 1 < i)
                    {
                        break;
                    }
                    string value = values[i].ToString();
                    string str = (propertys[i].PropertyType).FullName;
                    if (str == "System.String")
                    {
                        propertys[i].SetValue(obj, value, null);
                    }
                    else if (str == "System.DateTime")
                    {
                        DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                        propertys[i].SetValue(obj, pdt, null);
                    }
                    else if (str == "System.Boolean")
                    {
                        bool pb = Convert.ToBoolean(value);
                        propertys[i].SetValue(obj, pb, null);
                    }
                    else if (str == "System.Int16")
                    {
                        short pi16 = Convert.ToInt16(value);
                        propertys[i].SetValue(obj, pi16, null);
                    }
                    else if (str == "System.Int32")
                    {
                        int pi32 = Convert.ToInt32(value);
                        propertys[i].SetValue(obj, pi32, null);
                    }
                    else if (str == "System.Int64")
                    {
                        long pi64 = Convert.ToInt64(value);
                        propertys[i].SetValue(obj, pi64, null);
                    }
                    else if (str == "System.Byte")
                    {
                        byte pb = Convert.ToByte(value);
                        propertys[i].SetValue(obj, pb, null);
                    }
                    else
                    {
                        propertys[i].SetValue(obj, null, null);
                    }
                }
                list.Add(obj);
            }
            return list;
        }
    }
}