using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Net;

namespace Mall.Common.Plugin
{
    /// <summary>
    /// Excel模板导出帮助类
    /// </summary>
    public class ExcelTempLateHelper
    {

        /// <summary>
        /// 模板导出
        /// </summary>
        /// <param name="list">模板数据</param>
        /// <param name="isTempLate">是否模板导出</param>
        /// <param name="tempLatePath">模板文件路劲</param>
        public static byte[] ToExcel(List<ExcelDataSource> list, bool isTempLate = false, string tempLatePath = "")
        {
            HSSFWorkbook workbook = null;
            HSSFSheet sheet;
            if (isTempLate && !string.IsNullOrEmpty(tempLatePath.Trim()))
            {
                using (FileStream file = new FileStream(tempLatePath, FileMode.Open, FileAccess.Read))
                {
                    //将文件流中模板加载到工作簿对象中
                    workbook = new HSSFWorkbook(file);
                }
                sheet = workbook.GetSheetAt(0) as HSSFSheet;
            }
            else
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet() as HSSFSheet;
            }
            int rowIndex = 0;
            //循环添加行
            foreach (var item in list)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                if (item.ColumnHight > 0)
                {
                    dataRow.Height = (short)(item.ColumnHight * 20);//行高
                }
                else
                {
                    dataRow.Height = 20 * 20;//行高
                }
                int columnsIndex = 0;

                //循环添加列
                foreach (var subItem in item.ExcelRows)
                {
                    //单元格样式
                    HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                    var cell = dataRow.CreateCell(columnsIndex);
                    fCellStyle.WrapText = true;
                    //设置单元宽度
                    if (subItem.CellWidth > 0)
                    {
                        sheet.SetColumnWidth(columnsIndex, subItem.CellWidth * 256);
                    }

                    //设置单元格背景颜色
                    switch (subItem.BgColorEnum)
                    {
                        case ColorEnum.BrightGreen:
                            fCellStyle.FillForegroundColor = HSSFColor.BrightGreen.Index;
                            fCellStyle.FillPattern = FillPattern.SolidForeground;
                            break;
                        case ColorEnum.DarkBlue:
                            fCellStyle.FillForegroundColor = HSSFColor.DarkBlue.Index;
                            fCellStyle.FillPattern = FillPattern.SolidForeground;
                            break;
                        case ColorEnum.Red:
                            fCellStyle.FillForegroundColor = HSSFColor.Red.Index;
                            fCellStyle.FillPattern = FillPattern.SolidForeground;
                            break;
                        case ColorEnum.Yellow:
                            fCellStyle.FillForegroundColor = HSSFColor.Yellow.Index;
                            fCellStyle.FillPattern = FillPattern.SolidForeground;
                            break;
                        case ColorEnum.Default:
                            //fCellStyle.FillForegroundColor = HSSFColor.White.Index;
                            //fCellStyle.FillPattern = FillPattern.SolidForeground;
                            break;
                    }
                    HSSFFont ffont = (HSSFFont)workbook.CreateFont();
                    if (subItem.FontHeight > 0)
                    {
                        ffont.FontHeight = subItem.FontHeight * 20;
                    }
                    else
                    {
                        ffont.FontHeight = 11 * 20;
                    }
                    if (!string.IsNullOrWhiteSpace(subItem.FontName))
                    {
                        ffont.FontName = subItem.FontName;
                    }
                    else
                    {
                        ffont.FontName = "宋体";
                    }
                    //设置字体颜色
                    switch (subItem.FontColorEnum)
                    {
                        case ColorEnum.Red:
                            ffont.Color = HSSFColor.Red.Index;
                            break;
                        case ColorEnum.Green:
                            ffont.Color = HSSFColor.Green.Index;
                            break;
                        case ColorEnum.Yellow:
                            ffont.Color = HSSFColor.Yellow.Index;
                            break;
                        case ColorEnum.Default:
                            break;
                        case ColorEnum.BrightGreen:
                            break;
                        case ColorEnum.DarkBlue:
                            break;
                        default:
                            ffont.Color = HSSFColor.Black.Index;
                            break;
                    }

                    //字体加粗
                    if (subItem.IsBold)
                    {
                        ffont.IsBold = subItem.IsBold;
                    }
                    fCellStyle.SetFont(ffont);

                    //水平对齐方式
                    fCellStyle.Alignment = subItem.HAlignmentEnum switch
                    {
                        HAlignmentEnum.CENTER => HorizontalAlignment.Center,
                        HAlignmentEnum.LEFT => HorizontalAlignment.Left,
                        HAlignmentEnum.RIGHT => HorizontalAlignment.Right,
                        _ => HorizontalAlignment.Center,
                    };

                    //垂直对齐方式
                    fCellStyle.VerticalAlignment = subItem.VAlignmentEnum switch
                    {
                        VAlignmentEnum.CENTER => VerticalAlignment.Center,
                        VAlignmentEnum.TOP => VerticalAlignment.Top,
                        VAlignmentEnum.BOTTOM => VerticalAlignment.Bottom,
                        _ => VerticalAlignment.Center,
                    };
                    if (!subItem.IsSetBorder)
                    {
                        fCellStyle.BorderBottom = BorderStyle.Thin;
                        fCellStyle.BorderLeft = BorderStyle.Thin;
                        fCellStyle.BorderRight = BorderStyle.Thin;
                        fCellStyle.BorderTop = BorderStyle.Thin;
                    }
                    else
                    {
                        fCellStyle.BorderBottom = BorderStyle.None;
                        fCellStyle.BorderLeft = BorderStyle.None;
                        fCellStyle.BorderRight = BorderStyle.None;
                        fCellStyle.BorderTop = BorderStyle.None;
                    }
                    //合并单元格
                    if (subItem.Colspan > 1 || subItem.Rowspan > 1)
                    {
                        var region = new CellRangeAddress(rowIndex, rowIndex + (subItem.Rowspan - 1), columnsIndex, columnsIndex + (subItem.Colspan - 1));
                        sheet.AddMergedRegion(region);
                        for (int i = region.FirstRow; i <= region.LastRow; i++)
                        {
                            IRow row = CellUtil.GetRow(i, sheet);
                            for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                            {
                                ICell singleCell = CellUtil.GetCell(row, (short)j);
                                singleCell.CellStyle = fCellStyle;
                            }
                        }
                        columnsIndex += subItem.Colspan;
                    }
                    else
                    {
                        cell.CellStyle = fCellStyle;
                        columnsIndex++;
                    }
                    cell.SetCellValue(subItem.Value ?? "");
                }
                rowIndex++;
            }

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

        /// <summary>
        /// 模板导出
        /// </summary>
        /// <param name="list">模板数据</param>
        /// <param name="isTempLate">是否模板导出</param>
        /// <param name="tempLatePath">模板文件路劲</param>
        public static byte[] ToExcelExtend(List<ExcelDataSource> list, bool isTempLate = false, string tempLatePath = "")
        {
            HSSFWorkbook workbook = null;
            HSSFSheet sheet;
            if (isTempLate && !string.IsNullOrEmpty(tempLatePath.Trim()))
            {
                using (FileStream file = new FileStream(tempLatePath, FileMode.Open, FileAccess.Read))
                {
                    //将文件流中模板加载到工作簿对象中
                    workbook = new HSSFWorkbook(file);
                }
                sheet = workbook.GetSheetAt(0) as HSSFSheet;
            }
            else
            {
                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet() as HSSFSheet;
            }


            int rowIndex = 0;
            HSSFFont ffont = (HSSFFont)workbook.CreateFont();

            //单元格样式
            HSSFCellStyle fCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();

            //循环添加行
            foreach (var item in list)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                if (item.ColumnHight > 0)
                {
                    dataRow.Height = (short)(item.ColumnHight * 10);//行高
                }
                else
                {
                    dataRow.Height = 50 * 10;//行高
                }
                int columnsIndex = 0;

                //循环添加列
                foreach (var subItem in item.ExcelRows)
                {
                    if (subItem.Pic == 1 && !string.IsNullOrWhiteSpace(subItem.Value))
                    {
                        HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

                        try
                        {
                            Uri uri = new Uri(subItem.Value); //imgPath :网络图片地址
                            WebRequest webRequest = WebRequest.Create(uri);
                            byte[] bytes;
                            using (WebResponse webResponse = webRequest.GetResponse())
                            {
                                Bitmap bitmap = new Bitmap(webResponse.GetResponseStream());
                                using MemoryStream ms = new MemoryStream();
                                bitmap.Save(ms, ImageFormat.Jpeg);
                                bytes = ms.ToArray();
                            }
                            int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);

                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnsIndex, rowIndex, columnsIndex + 1, rowIndex + 1);
                            //把图片插到相应的位置
                            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);


                        }
                        catch
                        {

                        }
                        columnsIndex++;
                    }
                    else
                    {
                        var cell = dataRow.CreateCell(columnsIndex);
                        fCellStyle.WrapText = true;
                        //设置单元宽度
                        if (subItem.CellWidth > 0)
                        {
                            sheet.SetColumnWidth(columnsIndex, subItem.CellWidth * 256);
                        }

                        //设置单元格背景颜色
                        switch (subItem.BgColorEnum)
                        {
                            case ColorEnum.BrightGreen:
                                fCellStyle.FillForegroundColor = HSSFColor.BrightGreen.Index;
                                fCellStyle.FillPattern = FillPattern.SolidForeground;
                                break;
                            case ColorEnum.DarkBlue:
                                fCellStyle.FillForegroundColor = HSSFColor.DarkBlue.Index;
                                fCellStyle.FillPattern = FillPattern.SolidForeground;
                                break;
                            case ColorEnum.Red:
                                fCellStyle.FillForegroundColor = HSSFColor.Red.Index;
                                fCellStyle.FillPattern = FillPattern.SolidForeground;
                                break;
                        }
                        if (subItem.FontHeight > 0)
                        {
                            ffont.FontHeight = subItem.FontHeight * 20;
                        }
                        else
                        {
                            ffont.FontHeight = 11 * 20;
                        }
                        if (!string.IsNullOrWhiteSpace(subItem.FontName))
                        {
                            ffont.FontName = subItem.FontName;
                        }
                        else
                        {
                            ffont.FontName = "宋体";
                        }
                        if (subItem.FontSize > 0)
                        {
                            ffont.FontHeightInPoints = subItem.FontSize;
                        }

                        //设置字体颜色
                        switch (subItem.FontColorEnum)
                        {
                            case ColorEnum.Red:
                                ffont.Color = HSSFColor.Red.Index;
                                break;
                            case ColorEnum.Green:
                                ffont.Color = HSSFColor.Green.Index;
                                break;
                        }

                        //字体加粗
                        if (subItem.IsBold)
                        {
                            ffont.IsBold = subItem.IsBold;
                        }
                        fCellStyle.SetFont(ffont);

                        //水平对齐方式
                        fCellStyle.Alignment = subItem.HAlignmentEnum switch
                        {
                            HAlignmentEnum.CENTER => HorizontalAlignment.Center,
                            HAlignmentEnum.LEFT => HorizontalAlignment.Left,
                            HAlignmentEnum.RIGHT => HorizontalAlignment.Right,
                            _ => HorizontalAlignment.Center,
                        };

                        //垂直对齐方式
                        fCellStyle.VerticalAlignment = subItem.VAlignmentEnum switch
                        {
                            VAlignmentEnum.CENTER => VerticalAlignment.Center,
                            VAlignmentEnum.TOP => VerticalAlignment.Top,
                            VAlignmentEnum.BOTTOM => VerticalAlignment.Bottom,
                            _ => VerticalAlignment.Center,
                        };
                        if (!subItem.IsSetBorder)
                        {
                            fCellStyle.BorderBottom = BorderStyle.Thin;
                            fCellStyle.BorderLeft = BorderStyle.Thin;
                            fCellStyle.BorderRight = BorderStyle.Thin;
                            fCellStyle.BorderTop = BorderStyle.Thin;
                        }
                        else
                        {
                            fCellStyle.BorderBottom = BorderStyle.None;
                            fCellStyle.BorderLeft = BorderStyle.None;
                            fCellStyle.BorderRight = BorderStyle.None;
                            fCellStyle.BorderTop = BorderStyle.None;
                        }
                        //合并单元格
                        if (subItem.Colspan > 1 || subItem.Rowspan > 1)
                        {
                            var region = new CellRangeAddress(rowIndex, rowIndex + (subItem.Rowspan - 1), columnsIndex, columnsIndex + (subItem.Colspan - 1));
                            sheet.AddMergedRegion(region);

                            for (int i = region.FirstRow; i <= region.LastRow; i++)
                            {
                                IRow row = CellUtil.GetRow(i, sheet);
                                for (int j = region.FirstColumn; j <= region.LastColumn; j++)
                                {
                                    ICell singleCell = CellUtil.GetCell(row, (short)j);
                                    singleCell.CellStyle = fCellStyle;
                                }
                            }
                            columnsIndex += subItem.Colspan;
                        }
                        else
                        {
                            cell.CellStyle = fCellStyle;
                            columnsIndex++;
                        }
                        cell.SetCellValue(subItem.Value ?? "");
                    }

                }
                rowIndex++;
            }

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


    /// <summary>
    /// Excel数据源
    /// </summary>
    public class ExcelDataSource
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelDataSource()
        {

        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="columnHight">高度（1=0.6px）</param>
        public ExcelDataSource(int columnHight)
        {
            this.ColumnHight = columnHight;
        }

        /// <summary>
        /// 行高
        /// </summary>
        public int ColumnHight { get; set; }

        /// <summary>
        /// Excel行
        /// </summary>
        public List<ExcelColumn> ExcelRows { get; set; }
    }

    /// <summary>
    /// Excel列
    /// </summary>
    public class ExcelColumn
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="value">值</param>
        /// <param name="colspan">跨列(默认一列，不跨列)</param>
        /// <param name="rowspan">跨行(默认一行，不跨行)</param>
        /// <param name="selfBgColorIndex">自定义单元格背景颜色(索引)</param>
        /// <param name="isPic">0-不是图片，1-是图片</param>
        public ExcelColumn(string value = "", int colspan = 1, int rowspan = 1, short selfBgColorIndex = 0, int isPic = 0)
        {
            this.Value = value;
            this.Colspan = colspan;
            this.Rowspan = rowspan;
            this.Pic = isPic;
            this.SelfBgColorIndex = selfBgColorIndex;
        }

        /// <summary>
        /// 自定义单元格背景颜色(16进制)
        /// </summary>
        public short SelfBgColorIndex { get; set; }

        /// <summary>
        /// 值
        /// </summary>
        public string Value { get; set; }

        /// <summary>
        /// 跨列
        /// </summary>
        public int Colspan { get; set; }

        /// <summary>
        /// 跨行
        /// </summary>
        public int Rowspan { get; set; }

        /// <summary>
        /// 单元格宽度(1=7px)
        /// </summary>
        public int CellWidth { get; set; }

        /// <summary>
        /// 背景颜色
        /// </summary>
        public ColorEnum BgColorEnum { get; set; }

        /// <summary>
        /// 字体颜色
        /// </summary>
        public ColorEnum FontColorEnum { get; set; }

        /// <summary>
        /// 是否加粗
        /// </summary>
        public bool IsBold { get; set; }

        /// <summary>
        /// 水平对齐
        /// </summary>
        public HAlignmentEnum HAlignmentEnum { get; set; }

        /// <summary>
        /// 垂直对齐
        /// </summary>
        public VAlignmentEnum VAlignmentEnum { get; set; }

        /// <summary>
        /// 是否设置边框
        /// </summary>
        public bool IsSetBorder { get; set; }
        /// <summary>
        /// 字体
        /// </summary>
        public string FontName { get; set; }
        /// <summary>
        /// 字体高度
        /// </summary>
        public int FontHeight { get; set; }

        /// <summary>
        /// 字体大小
        /// </summary>
        public short FontSize { get; set; }

        /// <summary>
        /// 1-是图片
        /// </summary>
        public int Pic { get; set; }
    }

    /// <summary>
    /// 字体颜色
    /// </summary>
    public enum ColorEnum
    {
        /// <summary>
        /// 默认
        /// </summary>
        Default = 0,

        /// <summary>
        /// 红色
        /// </summary>
        Red = 1,

        /// <summary>
        /// BrightGreen
        /// </summary>
        BrightGreen = 2,

        /// <summary>
        /// Green
        /// </summary>
        Green = 3,

        /// <summary>
        /// DarkBlue
        /// </summary>
        DarkBlue = 4,

        /// <summary>
        /// Yellow
        /// </summary>
        Yellow = 5,
    }

    /// <summary>
    /// 水平对齐方式
    /// </summary>
    public enum HAlignmentEnum
    {
        /// <summary>
        /// 居左对齐
        /// </summary>
        LEFT = 1,
        /// <summary>
        /// 右对齐
        /// </summary>
        RIGHT = 2,
        /// <summary>
        /// 居中
        /// </summary>
        CENTER = 3

    }

    /// <summary>
    /// 垂直对齐方式
    /// </summary>
    public enum VAlignmentEnum
    {
        /// <summary>
        /// 居上
        /// </summary>
        TOP = 1,

        /// <summary>
        /// 垂直居中
        /// </summary>
        CENTER = 2,

        /// <summary>
        /// 居下
        /// </summary>
        BOTTOM = 3,
    }
}
