#nullable enable
using Furion.FriendlyException;
using Furion;
using Microsoft.AspNetCore.Http;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using Microsoft.Extensions.Configuration;

namespace iWare.Wms.Core.Util
{
    public static class ExcelUtil
    {
        /// <summary>
        /// 在文件中导出标准格式的数据
        /// </summary>
        /// <param name="headers"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public static string ToExcel(List<string> headers, List<List<object>> data, string sheetName, string target, string fileName)
        {
            if (!target.EndsWith("\\"))
            {
                target += "\\";
            }
            target += DateTime.Now.Ticks + "\\";
            if (!Directory.Exists(target))
            {
                Directory.CreateDirectory(target);
            }
            fileName = fileName + "[" + DateTime.Now.ToString("yyyy-MM-dd") + "]" + ".xlsx";
            var path = target + fileName;
            using (var package = new ExcelPackage(new FileInfo(path)))
            {
                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();
            }
            return path;
        }

        /// <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>
        /// 导入标准格式的数据文件
        /// </summary>
        /// <param name="file">上传的文件</param>
        /// <returns></returns>
        public static void FromExcel(IFormFile file, out List<string> headers, out List<List<object?>> data, out string sheetName)
        {
            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);
            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[1, col++].Value?.ToString();

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

                var headersCount = headers.Count;

                if (headersCount == 0) return;

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

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