#nullable enable
using Furion;
using Furion.FriendlyException;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using Microsoft.Extensions.Configuration;
using Microsoft.VisualBasic;
using OfficeOpenXml;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Information;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using OfficeOpenXml.FormulaParsing.Excel.Functions.RefAndLookup;
using OfficeOpenXml.Style;
using RestSharp;
using SQLitePCL;
using StackExchange.Profiling.Internal;
using System.Collections.Generic;
using System.Data;
using System.Reflection.PortableExecutable;

namespace Admin.NET.Core
{
    public static class ExcelUtil
    {
        /// <summary>
        /// 在response中导出标准格式的数据
        /// </summary>
        /// <param name="headers"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public static void ToExcel(List<string> headers, List<List<object>> data, string sheetName, Stream newStream)
        {
            using var package = new ExcelPackage(newStream);
            var worksheet = package.Workbook.Worksheets.Add(sheetName);

            for (int j = 0; j < headers.Count; j++)
            {
                worksheet.Cells[1, j + 1].Value = headers[j];
                worksheet.Cells[1, j + 1].Style.Font.Bold = true;//加粗
                worksheet.Cells[1, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 
                worksheet.Cells[1, j + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 
                worksheet.Cells[1, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
                worksheet.Cells[1, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
                worksheet.Cells[1, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
                worksheet.Cells[1, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
            }

            for (int i = 0; i < data.Count; i++)
            {
                for (int j = 0; j < data[i].Count; j++)
                {
                    worksheet.Cells[2 + i, j + 1].Value = data[i][j];
                }
            }
            package.Save();
        }



        /// <summary>
        /// 在response中导出标准格式的多表数据
        /// </summary>
        /// <param name="headersList"></param>
        /// <param name="dataList"></param>
        /// <param name="sheetNameList"></param>
        /// <param name="newStream"></param>
        public static void ToExcel(List<List<string>> headersList, List<List<List<object>>> dataList, List<string> sheetNameList, Stream newStream)
        {
            using var package = new ExcelPackage(newStream);
            for (var k = 0; k < sheetNameList.Count; k++)
            {
                var worksheet = package.Workbook.Worksheets.Add(sheetNameList[k]);

                for (int j = 0; j < headersList[k].Count; j++)
                {
                    worksheet.Cells[1, j + 1].Value = headersList[k][j];
                    worksheet.Cells[1, j + 1].Style.Font.Bold = true;//加粗
                    worksheet.Cells[1, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 
                    worksheet.Cells[1, j + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 
                    worksheet.Cells[1, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
                    worksheet.Cells[1, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
                    worksheet.Cells[1, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
                    worksheet.Cells[1, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
                }

                for (int i = 0; i < dataList[k].Count; i++)
                {
                    for (int j = 0; j < dataList[k][i].Count; j++)
                    {
                        worksheet.Cells[2 + i, j + 1].Value = dataList[k][i][j];
                    }
                }


            }

            package.Save();
        }





        /// <summary>
        /// 在response中根据模板导出单表数据
        /// </summary>
        /// <param name="headers"></param>
        /// <param name="templatePath"></param>
        /// <param name="data"></param>
        /// <param name="sheetName"></param>
        /// <param name="dataStartLine"></param>
        /// <param name="newStream"></param>
        /// <returns></returns>
        public static void ToExcel(string templatePath, List<string> headers, List<List<object>> data, string sheetName, int headStartLine, int dataStartLine, Stream newStream)
        {

            var options = new RestClientOptions(App.Configuration["FileUrl"])
            {
                ThrowOnAnyError = true,
                MaxTimeout = 2000
            };

            var client = new RestClient(options);
            var request = new RestRequest($"/file/download", Method.Get);
            request?.AddParameter(Parameter.CreateParameter("path", templatePath, ParameterType.QueryString))
                ?.AddParameter(Parameter.CreateParameter("fileName", "template.xlsx", ParameterType.QueryString));

            var response = client.DownloadData(request ?? null!);

            MemoryStream ms = new(response ?? null!);


            using var package = new ExcelPackage(newStream, ms);
            var worksheet = package.Workbook.Worksheets[0];

            worksheet.Name = sheetName;

            for (int j = 0; j < headers.Count; j++)
            {
                worksheet.Cells[headStartLine, j + 1].Value = headers[j];
            }

            for (int i = 0; i < data.Count; i++)
            {
                for (int j = 0; j < data[i].Count; j++)
                {
                    worksheet.Cells[dataStartLine + i, j + 1].Value = data[i][j];
                }
            }
            package.Save();

        }

        /// <summary>
        /// 在response中根据模板导出多表数据
        /// </summary>
        /// <param name="templatePath"></param>
        /// <param name="headersList"></param>
        /// <param name="dataList"></param>
        /// <param name="sheetNameList"></param>
        /// <param name="headStartLineList"></param>
        /// <param name="dataStartLineList"></param>
        /// <param name="newStream"></param>
        public static void ToExcel(string templatePath, List<List<string>> headersList, List<List<List<object>>> dataList, List<string> sheetNameList, List<int> headStartLineList, List<int> dataStartLineList, Stream newStream)
        {
            var options = new RestClientOptions(App.Configuration["FileUrl"])
            {
                ThrowOnAnyError = true,
                MaxTimeout = 2000
            };

            var client = new RestClient(options);
            var request = new RestRequest($"/file/download", Method.Get);
            request?.AddParameter(Parameter.CreateParameter("path", templatePath, ParameterType.QueryString))
                ?.AddParameter(Parameter.CreateParameter("fileName", "template.xlsx", ParameterType.QueryString));

            var response = client.DownloadData(request ?? null!);

            MemoryStream ms = new(response ?? null!);

            using var package = new ExcelPackage(newStream, ms);
            for (var k = 0; k < sheetNameList.Count; k++)
            {
                var worksheet = package.Workbook.Worksheets.FirstOrDefault(x => x.Name == sheetNameList[k]) ?? null!;

                for (int j = 0; j < headersList[k].Count; j++)
                {
                    worksheet.Cells[headStartLineList[k], j + 1].Value = headersList[k][j];

                }

                for (int i = 0; i < dataList[k].Count; i++)
                {
                    for (int j = 0; j < dataList[k][i].Count; j++)
                    {
                        worksheet.Cells[dataStartLineList[k] + i, j + 1].Value = dataList[k][i][j];
                    }
                }
            }
            package.Save();
        }


        /// <summary>
        /// 导入标准格式的数据文件
        /// </summary>
        /// <param name="file">上传的文件</param>
        /// <returns></returns>
        public static void FromExcel(IFormFile file, int headStartLine, int dataStartLine, out List<string> headers, out List<List<object?>> data, out string sheetName)
        {
            if (file == null) throw Oops.Oh(ErrorCode.D8000);

            //暂时注释，以后再放开 【Editby shaocx,2024-04-15】
            /*
            string key = "UploadFile:Excel";
            var allowContentTypes = App.Configuration.GetSection($"{key}:contentType").Get<IEnumerable<string>>();
            if (!allowContentTypes.Contains(file.ContentType)) throw Oops.Oh(ErrorCode.D8001);
            //*/

            sheetName = "";
            headers = new();
            data = new();

            using var package = new ExcelPackage(file.OpenReadStream());
            var worksheet = package.Workbook.Worksheets.Count > 0 ? package.Workbook.Worksheets[0] : null;

            sheetName = package.Workbook.Worksheets.Count > 0 ? package.Workbook.Worksheets[0].Name : "";
            var cellsCount = worksheet?.Cells?.Count() ?? 0;

            int col = 1;
            string? header = worksheet?.Cells[headStartLine, col++].Value?.ToString();

            while (!string.IsNullOrWhiteSpace(header))
            {
                headers.Add(header);
                header = worksheet?.Cells[headStartLine, col++]?.Value?.ToString();
            }

            var headersCount = headers.Count;

            if (headersCount == 0) throw Oops.Oh("表头数量不能为0");

            var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - dataStartLine + 1;

            for (var i = 0; i < rowsCount; i++)
            {
                List<object?> row = new();
                for (var j = 0; j < headersCount; j++)
                {
                    row.Add(worksheet?.Cells[i + dataStartLine, j + 1]?.Value);
                }
                data.Add(row);
            }
        }


        /// <summary>
        /// 导入多表格式的数据文件
        /// </summary>
        /// <param name="file">上传的文件</param>
        /// <returns></returns>
        public static void FromExcel(IFormFile file, Dictionary<string, int> nameHeadStartLineDict, Dictionary<string, int> nameDataStartLineDict,
            out List<List<string>> headersList, out List<List<List<object?>>> dataList, out List<string> sheetNameList)
        {
            if (file == null) throw Oops.Oh(ErrorCode.D8000);
            string key = "UploadFile:Excel";
            var allowContentTypes = App.Configuration.GetSection($"{key}:contentType").Get<IEnumerable<string>>();
            if (!allowContentTypes.Contains(file.ContentType)) throw Oops.Oh(ErrorCode.D8001);
            sheetNameList = new();
            headersList = new();
            dataList = new();

            using var package = new ExcelPackage(file.OpenReadStream());

            foreach (var worksheet in package.Workbook.Worksheets)
            {
                if (!nameHeadStartLineDict.ContainsKey(worksheet?.Name ?? "")) throw Oops.Oh("非法的Excel文件");

                sheetNameList.Add(worksheet?.Name ?? "");
                var cellsCount = worksheet?.Cells?.Count() ?? 0;
                var headers = new List<string>();
                int col = 1;
                int r = nameHeadStartLineDict[worksheet?.Name ?? ""];
                string? header = worksheet?.Cells[r, col++].Value?.ToString();
                while (!string.IsNullOrWhiteSpace(header))
                {
                    headers.Add(header);
                    header = worksheet?.Cells[r, col++]?.Value?.ToString();
                }
                headersList.Add(headers);
                var headersCount = headers.Count;
                if (headersCount == 0) throw Oops.Oh("表头数量不能为0");
                r = nameDataStartLineDict[worksheet?.Name ?? ""];
                var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - r + 1;
                List<List<object?>> data = new();

                for (var i = 0; i < rowsCount; i++)
                {
                    List<object?> row = new();
                    for (var j = 0; j < headersCount; j++)
                    {
                        row.Add(worksheet?.Cells[i + r, j + 1]?.Value);
                    }
                    data.Add(row);
                }
                dataList.Add(data);

            }

        }



        /// <summary>
        ///   以文件流IFormFile方式导入到DataTable
        /// </summary>
        /// <param name="file"></param>
        /// <param name="headStartLine"></param>
        /// <param name="dataStartLine"></param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public static DataTable ImportExcelToDataTable(IFormFile file, int headStartLine ,int dataStartLine)
        {

            if (file == null) throw Oops.Oh(ErrorCode.D8000);

            //暂时注释，以后再放开 【Editby shaocx,2024-04-15】
            /*
            string key = "UploadFile:Excel";
            var allowContentTypes = App.Configuration.GetSection($"{key}:contentType").Get<IEnumerable<string>>();
            if (!allowContentTypes.Contains(file.ContentType)) throw Oops.Oh(ErrorCode.D8001);
            //*/

            List<string> headers = new List<string>();
            System.Data.DataTable dataTable = null;
            ExcelPackage package = null;
            DataColumn column = null;
            DataRow dataRow = null;
            try
            {

                //读取Excel文件
                using (package = new ExcelPackage(file.OpenReadStream()))
                {
                    var worksheet = package.Workbook.Worksheets.Count > 0 ? package.Workbook.Worksheets[0] : null;

                    dataTable = new System.Data.DataTable();

                    //  var rowsCount = worksheet.Dimension.Rows;//总行数
                    var cellsCount = worksheet?.Cells?.Count() ?? 0;



                    int col = 1;
                    string? header = worksheet?.Cells[headStartLine, col++].Value?.ToString();
                    //构建datatable的列
                    while (!string.IsNullOrWhiteSpace(header))
                    {
                        headers.Add(header);
                        column = new DataColumn(header);
                        dataTable.Columns.Add(column);
                        header = worksheet?.Cells[headStartLine, col++]?.Value?.ToString();
                    }
                    var headersCount = headers.Count;
                    if (headersCount == 0) throw Oops.Oh("表头数量不能为0");
                    var rowsCount = (int)Math.Ceiling(((double)cellsCount) / headersCount) - dataStartLine + 1;

                    //填充行
                    for (var i = 0; i < rowsCount; i++)
                    {
                        dataRow = dataTable.NewRow();

                        for (var j = 0; j < headersCount; j++)
                        {
                            var cell = worksheet?.Cells[i + dataStartLine, j + 1];

                            if (cell == null)
                            {
                                dataRow[j] = "";
                            }
                            else
                            {
                                #region 类型判断
                                //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                //switch (cell.GetType())
                                //{
                                //    case CellType.Blank:
                                //        dataRow[j] = "";
                                //        break;

                                //    case CellType.Numeric:
                                //        short format = cell.CellStyle.DataFormat;
                                //        //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                //        if (format == 14 || format == 31 || format == 57 || format == 58)
                                //        {
                                //            if (DateUtil.IsCellDateFormatted(cell))
                                //            {
                                //                dataRow[j] = cell.DateCellValue.Date.ToString();
                                //            }
                                //        }
                                //        else
                                //        {
                                //            dataRow[j] = cell.NumericCellValue;
                                //        }

                                //        break;

                                //    case CellType.String:
                                //        dataRow[j] = cell.StringCellValue;
                                //        break;
                                //}

                                #endregion
                                dataRow[j] = cell?.Value;
                            }
                        }

                        //去除空白行数据
                        bool rowdataisnull = true;
                        for (int k = 0; k <= headersCount - 1; ++k)
                        {
                            if (!string.IsNullOrEmpty(dataRow[k] as string))
                            {
                                rowdataisnull = false;
                            }
                        }
                        if (!rowdataisnull)
                        {
                            dataTable.Rows.Add(dataRow);
                        }

                    }


                };


            }
            catch (Exception ex)
            {

                throw Oops.Oh("导入异常，请联系管理员");
            }
            finally
            {
                if(package!=null) package.Dispose();

            }
            return dataTable;



        }

       

    }
}