using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace Bro.M071.DBManager
{

    public class ExcelExportSet
    {
        public List<string> Worksheets { get; set; }

        /// <summary>
        /// Key： Worksheet的名称 Value:Worksheet对应的列名集合(key 为要导出的列名 value 为导出后显示的列名)
        /// </summary>
        public Dictionary<string, Dictionary<string, string>> WorksheetColumns { get; set; }
        public Dictionary<string, DataTable> WorksheetDataTable { get; set; }

        public ExcelExportSet()
        {
            Worksheets = new List<string>();
            WorksheetColumns = new Dictionary<string, Dictionary<string, string>>();
            WorksheetDataTable = new Dictionary<string, DataTable>();
        }

    }

    /// <summary>
    /// Excel导出帮助类
    /// </summary>
    public class ExcelExportHelper
    {
        public static string ExcelContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> data, Dictionary<string, string> worksheetColumns)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable dataTable = new DataTable();
            Dictionary<string, string> tempColumns = new Dictionary<string, string>();
            foreach (var column in worksheetColumns)
            {
                PropertyDescriptor property = properties.Find(column.Key, true);
                if (property != null)
                {
                    dataTable.Columns.Add(column.Value, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
                    tempColumns[column.Key] = column.Value;
                }
            }
            object[] values = new object[tempColumns.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < tempColumns.Count; i++)
                {
                    PropertyDescriptor property = properties.Find(tempColumns.ElementAt(i).Key, true);
                    values[i] = property.GetValue(item);
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="worksheet">工作簿Worksheet</param>
        /// <param name="showSrNo">//是否显示行编号</param>
        /// <param name="columnsToTake">要导出的列</param>
        /// <returns></returns>
        public static byte[] ExportExcel(ExcelExportSet excelExportDto, bool showSrNo = false)
        {
            //ExcelPackage.LicenseContext = LicenseContext.Commercial; 5.0以上版本 需要授权
            byte[] result = null;
            using (ExcelPackage package = new ExcelPackage())
            {
                foreach (var worksheet in excelExportDto.Worksheets)
                {
                    var dataTable = excelExportDto.WorksheetDataTable[worksheet];
                    ExcelWorksheet workSheet = package.Workbook.Worksheets.Add($"{worksheet}");
                    int startRowFrom = string.IsNullOrEmpty(worksheet) ? 1 : 3;  //开始的行
                                                                                 //是否显示行编号
                    if (showSrNo)
                    {
                        DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
                        dataColumn.SetOrdinal(0);
                        int index = 1;
                        foreach (DataRow item in dataTable.Rows)
                        {
                            item[0] = index;
                            index++;
                        }
                    }
                    //Add Content Into the Excel File
                    workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
                    // autofit width of cells with small content 
                    int columnIndex = 1;
                    foreach (DataColumn item in dataTable.Columns)
                    {
                        ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                        int maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
                        if (maxLength < 150)
                        {
                            workSheet.Column(columnIndex).AutoFit();
                        }
                        columnIndex++;
                    }
                    // format header - bold, yellow on black 
                    using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
                    {
                        r.Style.Font.Color.SetColor(System.Drawing.Color.White);
                        r.Style.Font.Bold = true;
                        r.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
                    }
                    // format cells - add borders 
                    using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
                    {
                        r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                        r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                        r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                        r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                        r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                    }
                    if (!string.IsNullOrEmpty(worksheet))
                    {
                        workSheet.Cells["A1"].Value = worksheet;
                        workSheet.Cells["A1"].Style.Font.Size = 20;
                        workSheet.InsertColumn(1, 1);
                        workSheet.InsertRow(1, 1);
                        workSheet.Column(1).Width = 5;
                    }
                }
                result = package.GetAsByteArray();
            }
            return result;
        }

        ///// <summary>
        ///// 导出Excel
        ///// </summary>
        ///// <typeparam name="T"></typeparam>
        ///// <param name="data"></param>
        ///// <param name="heading"></param>
        ///// <param name="isShowSlNo"></param>
        ///// <param name="columnsToTake"></param>
        ///// <returns></returns>
        //public static byte[] ExportExcel<T>(List<T> data, string heading = "", bool isShowSlNo = false, params string[] columnsToTake)
        //{
        //    ExcelExportSet excelExport = new ExcelExportSet();
        //    excelExport.
        //    return ExportExcel(ListToDataTable(data), heading, isShowSlNo, columnsToTake);
        //}
    }
}