using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
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;  //开始的行
                    int startRowFrom = 1;  //开始的行
                                           //是否显示行编号
                    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);

                    for (int columnIndex = 1; columnIndex <= dataTable.Columns.Count; columnIndex++)
                    {
                        // autofit width of cells with small content 
                        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();
                        }
                        // 设置NG单元格样式
                        for (int rowIndex = 1; rowIndex <= dataTable.Rows.Count; rowIndex++)
                        {
                            if (columnCells[rowIndex, columnIndex].Text == "NG")
                            {
                                workSheet.Cells[rowIndex, columnIndex].Style.Font.Bold = true;//字体为粗体
                                workSheet.Cells[rowIndex, columnIndex].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                workSheet.Cells[rowIndex, columnIndex].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#E33E33"));// 设置填充样式
                            }
                        }
                    }
                    // 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);
                    }
                    //将sheet写入表中
                    //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>
        /// <param name="excelExportDto"></param>
        /// <param name="fileName">要写入的文件名(全路径名)</param>
        /// <param name="showSrNo"></param>
        /// <returns></returns>
        public static byte[] CreateOrAppendExcel(ExcelExportSet excelExportDto, string fileName, bool showSrNo = false)
        {
            byte[] result = null;
            bool isExist = File.Exists(fileName);
            ExcelExportSet newExcelSet = new ExcelExportSet();

            if (isExist)
            {
                var oldWorksheetDataTable = WorksheetToTable(fileName, excelExportDto.Worksheets);
                newExcelSet.Worksheets = excelExportDto.Worksheets;
                newExcelSet.WorksheetColumns = excelExportDto.WorksheetColumns;

                foreach (var sheet in excelExportDto.Worksheets)
                {
                    var oldTable = oldWorksheetDataTable[sheet];
                    newExcelSet.WorksheetDataTable[sheet] = oldTable;
                    if (excelExportDto.WorksheetDataTable[sheet].Rows.Count > 0)
                    {
                        //合并两个 datatable
                        newExcelSet.WorksheetDataTable[sheet].Merge(excelExportDto.WorksheetDataTable[sheet]);
                    }
                }
            }
            else
            {
                newExcelSet = excelExportDto;
            }

            result = ExportExcel(newExcelSet, showSrNo);
            return result;
        }

        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的指定sheet）
        /// </summary>
        /// <param name="fullFielPath">文件的绝对路径</param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> WorksheetToTable(string fullFielPath, List<string> worksheets)
        {
            try
            {
                var resultWorksheetDataTable = new Dictionary<string, DataTable>();
                FileInfo existingFile = new FileInfo(fullFielPath);
                ExcelPackage package = new ExcelPackage(existingFile);
                foreach (var sheet in worksheets)
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[sheet];//选定 指定页
                    var dataTable = WorksheetToTable(worksheet);
                    resultWorksheetDataTable[sheet] = dataTable;
                }
                return resultWorksheetDataTable;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    //默认将第一行设置为datatable的标题
                    if (i == 1)
                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                    //剩下的写入datatable
                    else
                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                }
            }
            return dt;
        }
        private static string GetString(object obj)
        {
            try
            {
                return (obj ?? "").ToString();
            }
            catch (Exception)
            {
                return "";
            }
        }

    }
}