using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Performance.Infrastructure;

public interface IChildren<T>
{
    IEnumerable<T> Children { get; set; }
}
public class EColumn : IChildren<EColumn>
{
    public EColumn(string label, string name, decimal sort = 0m, IEnumerable<EColumn>? children = null, string comment = "")
    {
        Label = label;
        Name = name;
        Sort = sort;
        Comment = comment;
        Children = children ?? new HashSet<EColumn>();
    }

    public string Label { get; set; }
    public string Name { get; set; }
    public string Comment { get; set; }
    public decimal Sort { get; set; }
    public IEnumerable<EColumn> Children { get; set; }
}
public class ExcelDownloadRequest
{
    /// <summary>
    /// 工作表名
    /// </summary>
    public string Name { get; set; }
    /// <summary>
    /// 数据
    /// </summary>
    public List<Dictionary<string, object>> Rows { get; set; }
    /// <summary>
    /// 列头
    /// </summary>
    public IEnumerable<EColumn> Columns { get; set; }
    /// <summary>
    /// 不需要导出的列
    /// </summary>
    public string[] IgnoreColumns { get; set; } = new string[0];
}
public static partial class UtilExtensions
{
    public static DataTable Read(this ExcelPackage package, string name, int startRow = 1, int startCol = 1)
    {

        if (package is null)
            throw new ArgumentNullException(nameof(package));

        if (!package.Workbook.Worksheets.Any(w => w.Name == name))
            return package.Read(0);

        var worksheet = package.Workbook.Worksheets[name];
        return Read(worksheet, startRow, startCol);
    }

    public static DataTable Read(this ExcelPackage package, int index, int startRow = 1, int startCol = 1)
    {

        if (package is null)
            throw new ArgumentNullException(nameof(package));

        if (package.Workbook.Worksheets.Count <= index)
            throw new IndexOutOfRangeException(nameof(index));

        var worksheet = package.Workbook.Worksheets[index];
        return Read(worksheet, startRow, startCol);
    }

    public static bool TryRead(this ExcelWorksheet ws, out DataTable table, int startRow = 1, int startCol = 1)
    {
        table = new DataTable();
        try
        {
            table = Read(ws, startRow, startCol);
            return true;
        }
        catch { }

        return false;
    }

    public static DataTable Read(this ExcelWorksheet ws, int startRow = 1, int startCol = 1)
    {
        var table = new DataTable();
        foreach (var firstRowCell in ws.Cells[startRow, startCol, 1, ws.Dimension.End.Column])
        {
            var name = !string.IsNullOrEmpty(firstRowCell.Text) ? firstRowCell.Text : string.Format("EColumn {0}", firstRowCell.Start.Column);
            if (!string.IsNullOrEmpty(name) && table.Columns.Contains(name))
                throw new Exception("重复列名" + name);

            table.Columns.Add(name);
        }

        for (var rowNum = startRow + 1; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];

            var row = table.NewRow();
            foreach (var cell in wsRow)
            {
                if (table.Columns.Count >= cell.Start.Column)
                    row[cell.Start.Column - 1] = cell.Value?.ToString()?.Trim()?.Clean() ?? "";
            }
            table.Rows.Add(row);
        }
        return table;
    }

    /// <summary>
    /// 修改列头
    /// </summary>
    /// <param name="updateHead">修改列头名称</param>
    /// <returns></returns>
    public static List<string> UpdateHeads(this DataTable dt, Dictionary<string, string> updateHead)
    {
        List<string> heads = new List<string>();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (updateHead.ContainsKey(dt.Columns[i].ColumnName))
                dt.Columns[i].ColumnName = updateHead[dt.Columns[i].ColumnName];
            heads.Add(dt.Columns[i].ColumnName);
        }
        return heads;
    }

    /// <summary> 
    /// 数据表转键值对集合
    /// 把DataTable转成 List集合, 存每一行 
    /// 集合中放的是键值对字典,存每一列 ，键/值相同
    /// </summary> 
    /// <param name="dt">数据表</param> 
    /// <returns>哈希表数组</returns> 
    public static List<Dictionary<string, object>> ToListDictionary(this DataTable dt)
    {
        List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
        foreach (DataRow dr in dt.Rows)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            foreach (DataColumn dc in dt.Columns)
            {
                dic.Add(dc.ColumnName, dr[dc.ColumnName]);
            }
            list.Add(dic);
        }
        return list;
    }
    public static bool Export(string filepath, params ExcelDownloadRequest[] contents)
    {
        return Export(contents.ToList(), filepath);
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="data"></param>
    /// <param name="filepath">路径</param>
    /// <returns></returns>
    public static bool Export(List<ExcelDownloadRequest> data, string filepath)
    {
        FileHelper.CreateFile(filepath);
        using (Stream newStream = new FileStream(filepath, FileMode.Create))
        {
            using (ExcelPackage package = new ExcelPackage(newStream))
            {
                foreach (var item in data)
                {
                    if (package.Workbook.Worksheets.Any(t => t.Name == item.Name))
                        package.Workbook.Worksheets.Delete(item.Name);

                    var worksheet = package.Workbook.Worksheets.Add(item.Name);
                    CreateWorksheet(worksheet, item);
                }
                package.Save();
                return true;
            }
        }
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="worksheet"></param>
    /// <param name="excelDownloadRequest"></param>
    public static void CreateWorksheet(ExcelWorksheet worksheet, ExcelDownloadRequest excelDownloadRequest)
    {
        excelDownloadRequest.Rows = excelDownloadRequest.Rows ?? new List<Dictionary<string, object>> { };
        //获取列头最大行
        List<int> all = new List<int>();
        GetTreeCount(excelDownloadRequest.Columns, all);
        int headRow = all.Max();
        //获取列头
        var headList = GetHeads(excelDownloadRequest.Columns.OrderBy(t => t.Sort), excelDownloadRequest.IgnoreColumns);
        //生成列头
        GenerateHeads(worksheet, excelDownloadRequest.Columns, excelDownloadRequest.IgnoreColumns, headRow);

        for (int col = 0; col < headList.Count; col++)
        {
            for (int row = 0; row < excelDownloadRequest.Rows.Count; row++)
            {
                var temp = excelDownloadRequest.Rows.ElementAt(row);
                if (temp.ContainsKey(headList[col].Name))
                    worksheet.Cells[row + headRow + 1, col + 1].Value = temp[headList[col].Name] ?? "";
            }
        }

        #region 样式设置

        for (int row = worksheet.Dimension.Start.Row; row <= worksheet.Dimension.End.Row; row++)
        {
            worksheet.Row(row).Height = 20;
            for (int col = worksheet.Dimension.Start.Column; col <= worksheet.Dimension.End.Column; col++)
            {
                worksheet.Cells[row, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                worksheet.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            }
        }

        worksheet.View.FreezePanes(headRow + 1, 1);
        worksheet.Cells.AutoFitColumns();
        for (int col = worksheet.Dimension.Start.Column; col <= worksheet.Dimension.End.Column; col++)
        {
            worksheet.Column(col).Width = worksheet.Column(col).Width > 20 ? 20 : worksheet.Column(col).Width;
        }
        #endregion

    }
    /// <summary>
    /// 获得列头
    /// </summary>
    /// <param name="columns"></param>
    /// <param name="ignoreColumns"></param>
    /// <returns></returns>
    private static List<EColumn> GetHeads(IEnumerable<EColumn> columns, string[] ignoreColumns)
    {
        List<EColumn> heads = new List<EColumn>();
        foreach (var item in columns.OrderBy(w => w.Sort))
        {
            if (!ignoreColumns.Contains(item.Name) && !ignoreColumns.Contains(item.Label))
            {
                if (item.Children?.Any() != true)
                    heads.Add(item);
                else
                    heads.AddRange(GetHeads(item.Children, ignoreColumns));
            }
        }
        return heads;
    }
    /// <summary>
    /// 生成excel列头
    /// </summary>
    /// <param name="worksheet"></param>
    /// <param name="columns"></param>
    /// <param name="ignoreColumns"></param>
    /// <param name="headRow"></param>
    /// <param name="row"></param>
    /// <param name="col"></param>
    private static void GenerateHeads(ExcelWorksheet worksheet, IEnumerable<EColumn> columns, string[] ignoreColumns, int headRow, int row = 1, int col = 1)
    {
        int colIndex = col;
        foreach (var item in columns.OrderBy(w => w.Sort))
        {
            if (!ignoreColumns.Contains(item.Label) && !ignoreColumns.Contains(item.Name))
            {
                int children = GetMinChildCount(item.Children, 0);
                worksheet.Cells[row, colIndex].Value = string.IsNullOrEmpty(item.Label) ? item.Name : item.Label;
                if (!string.IsNullOrWhiteSpace(item.Comment))
                    worksheet.Cells[row, colIndex].AddComment(item.Comment, "SYSTEM");

                worksheet.Cells[row, colIndex].Style.Font.Bold = true;
                if (children != 0)
                {
                    worksheet.Cells[row, colIndex, row, colIndex + children - 1].Merge = true;

                    GenerateHeads(worksheet, item.Children, ignoreColumns, headRow, row + 1, colIndex);
                    colIndex += children;
                }
                else
                {
                    worksheet.Cells[row, colIndex, headRow, colIndex].Merge = true;
                    colIndex++;
                }
            }

        }
    }
    /// <summary>
    /// 获取树最大深度
    /// </summary>
    /// <param name="columns"></param>
    /// <param name="all"></param>
    /// <param name="level"></param>
    private static void GetTreeCount(IEnumerable<EColumn> columns, List<int> all, int level = 1)
    {
        foreach (var item in columns)
        {
            if (item.Children?.Any() == true)
            {
                GetTreeCount(item.Children, all, level + 1);
            }
            else
                all.Add(level);
        }
    }
    /// <summary>
    /// 获得最小的子集数量
    /// </summary>
    /// <param name="columns"></param>
    /// <param name="count"></param>
    /// <returns></returns>
    private static int GetMinChildCount(IEnumerable<EColumn> columns, int count)
    {
        foreach (var col in columns)
        {
            if (col.Children?.Any() != true)
                count++;
            else
                count = GetMinChildCount(col.Children, count);
        }
        return count;
    }
}