using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Linq;
using System.Text;
using ExcelColumn = Siger.Middlelayer.Utility.ExcelImport.ExcelColumn;
using Siger.Middlelayer.Utility.ExcelImport;
using Siger.Middlelayer.Common.Extensions;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using Siger.Middlelayer.Utility.ImportEntities;

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

        public NPOIExcelHelper()
        {

        }

        public void GenerateExcelByTemplate(IEnumerable<T> list, string templateFilePath, string filePath, bool longTimeFormat = false)
        {
            if (!File.Exists(templateFilePath))
            {
                throw new Exception("template not exists failed.");
            }

            using (FileStream fileStream = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
                workbook.WriteProtectWorkbook("admin", "");
                using (MemoryStream ms = ExportTemplate(list, workbook, "data"))
                {
                    using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                }
            }
        }
        public void Export(IEnumerable<T> list, string strHeaderText, string strFileName)
        {
            using (MemoryStream ms = Export(list, strHeaderText))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, 0, data.Length);
                    fs.Flush();
                }
            }
        }
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="list"></param>
        /// <param name="strHeaderText"></param>
        /// <returns></returns>
        private MemoryStream Export(IEnumerable<T> list, string strHeaderText)
        {
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(T)
                .GetProperties()
                .Where(f => f.CustomAttributes.Any(ColumnOnly)
                && f.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();

            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
            int rowIndex = 0;
            foreach (var entity in list.ToList())
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columns.Count() - 1));
                    }
                    #endregion


                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(1);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        for (int i = 0; i < columns.Count(); i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(columns[i].Column);
                        }
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion


                #region 填充内容
                ICellStyle contentStyle = workbook.CreateCellStyle();
                contentStyle.Alignment = HorizontalAlignment.Left;
                IRow dataRow = sheet.CreateRow(rowIndex);
                for (var j = 0; j < columns.Count; j++)
                {
                    ICell newCell = dataRow.CreateCell(j);
                    newCell.CellStyle = contentStyle;

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

                    var propertyType = columns[j].Property.PropertyType;
                    switch (propertyType.ToStr())
                    {
                        case "System.String"://字符串类型  
                            newCell.SetCellValue(value);
                            break;
                        case "System.DateTime"://日期类型  
                            DateTime dateV;
                            DateTime.TryParse(value, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示  
                            break;
                        case "System.Boolean"://布尔型  
                            bool boolV = false;
                            bool.TryParse(value, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型  
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(value, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型  
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(value, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理  
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }

        }
        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="list">数据源</param>
        /// <param name="workbook">模板文档</param>
        /// <param name="sheetname">数据导出的sheet</param>
        /// <param name="templateSheet">模板所在sheet</param>
        /// <returns></returns>
        private MemoryStream ExportTemplate(IEnumerable<T> list, HSSFWorkbook workbook, string sheetname)
        {
            bool ColumnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
            var columns = typeof(T)
                .GetProperties()
                .Where(f => f.CustomAttributes.Any(ColumnOnly)
                && f.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();
            ISheet sheet = workbook.GetSheet(sheetname);
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < columns.Count(); i++)
            {
                headerRow.CreateCell(i).SetCellValue(columns[i].Column);
            }
            foreach (var entity in list.ToList())
            {
                var row = 1;
                #region 填充内容
                ICellStyle contentStyle = workbook.CreateCellStyle();
                contentStyle.Alignment = HorizontalAlignment.Left;
                IRow dataRow = sheet.GetRow(row);
                for (var j = 0; j < columns.Count; j++)
                {
                    ICell newCell = dataRow.GetCell(j);
                    newCell.CellStyle = contentStyle;

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

                    var propertyType = columns[j].Property.PropertyType;
                    switch (propertyType.ToStr())
                    {
                        case "System.String"://字符串类型  
                            newCell.SetCellValue(value);
                            break;
                        case "System.DateTime"://日期类型  
                            DateTime dateV;
                            DateTime.TryParse(value, out dateV);
                            newCell.SetCellValue(dateV);
                            break;
                        case "System.Boolean"://布尔型  
                            bool boolV = false;
                            bool.TryParse(value, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型  
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(value, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型  
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(value, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理  
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion
                row++;
            }
            workbook.GetSheetAt(0).ForceFormulaRecalculation = true;//强制套用公式
            workbook.GetSheetAt(1).ForceFormulaRecalculation = true;//强制套用公式
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        public void Dispose()
        {
        }
    }
}
