using NPOI.HSSF.UserModel;
using NPOI.SS.Formula;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Performance.DtoModels;
using Performance.Infrastructure;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace Performance.Services.ExtractExcelService
{
    public static class ExcelHelper
    {
        public static IRow GetOrCreate(this ISheet sheet, int index)
        {
            var row = sheet.GetRow(index);
            if (row == null)
                row = sheet.CreateRow(index);

            return row;
        }

        public static ICell GetOrCreate(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            if (cell == null)
                cell = row.CreateCell(index);

            return cell;
        }
        /// <summary>
        /// 删除重建
        /// </summary>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static ICell GetOrRecreate(this IRow row, int index)
        {
            var cell = row.GetCell(index);
            if (cell != null)
                row.RemoveCell(cell);

            return row.CreateCell(index);
        }

        public static void SetRowStyle(this IRow row, ICellStyle cellStyle)
        {
            List<ICell> cells = row.Cells;
            foreach (var cell in cells)
            {
                cell.CellStyle = cellStyle;
            }
        }

        public static void SetCellValue<T>(this ICell cell, object value, T defaultValue = default)
        {
            if (cell == null) return;

            try
            {
                switch (typeof(T).ToString())
                {
                    #region SetValue

                    case "System.String"://字符串类型
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(ConvertHelper.To<string>(value));
                        break;

                    case "System.DateTime"://日期类型
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(ConvertHelper.To<DateTime>(value).ToString("yyyy/M/d"));
                        break;

                    case "System.Boolean"://布尔型
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(ConvertHelper.To<bool>(value));
                        break;

                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(ConvertHelper.To<int>(value));
                        break;

                    case "System.Decimal"://浮点型
                    case "System.Double":
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(ConvertHelper.To<double>(value));
                        break;

                    case "System.DBNull"://空值处理
                    default:
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue("");
                        break;

                        #endregion SetValue
                }
            }
            catch
            {
                cell.SetCellType(CellType.String);
                cell.SetCellValue("");
            }
        }

        public static void SetValue(this ISheet sheet, int row, int column, object value)
        {
            var icell = sheet.GetOrCreate(row).GetOrCreate(column);
            if (value != null && !string.IsNullOrEmpty(value.ToString()))
            {
                switch (value)
                {
                    case string reg when Regex.IsMatch(reg, @"^[+-]?\d*[.]?\d*$"):
                        value = ConvertHelper.To<double>(value);
                        break;

                    case string reg when Regex.IsMatch(reg, @"^[+-]?\d*$"):
                        value = ConvertHelper.To<int>(value);
                        break;
                }
            }

            SetCellOValue(icell, value);
        }

        public static void SetCellOValue(this ICell cell, object value)
        {
            if (cell == null) return;

            if (value == null || string.IsNullOrWhiteSpace(value.ToString()))
            {
                cell.SetCellValue("");
                return;
            }

            try
            {
                string stringV = value.ToString().Trim();
                var type = value.GetType();
                switch (type.ToString())
                {
                    #region SetValue

                    case "System.String"://字符串类型
                        cell.SetCellValue(stringV);
                        break;

                    case "System.DateTime"://日期类型
                        DateTime dateV;
                        DateTime.TryParse(stringV, out dateV);
                        cell.SetCellValue(dateV.ToString("yyyy/M/d"));
                        break;

                    case "System.Boolean"://布尔型
                        bool boolV = false;
                        bool.TryParse(stringV, out boolV);
                        cell.SetCellValue(boolV);
                        break;

                    case "System.Int16"://整型
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(stringV, out intV);
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(intV);
                        break;

                    case "System.Decimal"://浮点型
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(stringV, out doubV);
                        cell.SetCellType(CellType.Numeric);
                        cell.SetCellValue(doubV);
                        break;

                    case "System.DBNull"://空值处理
                        cell.SetCellValue("");
                        break;

                    default:
                        cell.SetCellValue("");
                        break;

                        #endregion SetValue
                }
            }
            catch
            {
                cell.SetCellValue("");
            }
        }

        public static T GetCellValue<T>(this ICell cell)
        {
            try
            {
                cell.SetCellType(CellType.String);
                string value = cell.StringCellValue;
                return ConvertHelper.To<T>(value);
            }
            catch
            {
                return default;
            }
        }

        /// <summary>
        /// 获取去除转义后的字符
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static string GetDecodeEscapes(this ICell cell)
        {
            try
            {
                if (cell == null) return "";

                cell.SetCellType(CellType.String);
                return cell.StringCellValue.NoBlank();
            }
            catch
            {
                return "";
            }
        }

        public static string NoBlank(this string @string)
        {
            if (string.IsNullOrEmpty(@string))
                return "";
            return @string.Replace("\n", "").Replace("\r", "").Replace(" ", "").Trim();
        }

        public static string GetNo(this string @string)
        {
            var match = Regex.Match(@string, "^(1.[0-9]+.[1-9])|(^[1-9]+.[1-9]+)");
            return match.Value;
        }

        public static IWorkbook GetWorkbook(string filePath)
        {
            IWorkbook workbook = null;

            if (string.IsNullOrEmpty(filePath)) return workbook;

            var version = FileHelper.GetExtension(filePath) == ".xlsx" ? ExcelVersion.xlsx : ExcelVersion.xls;

            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
            {
                workbook = (version == ExcelVersion.xlsx)
                    ? (IWorkbook)(new XSSFWorkbook(fs))
                    : (IWorkbook)(new HSSFWorkbook(fs));
            }

            return workbook;
        }

        public static List<string> GetCellValues(this IRow row)
        {
            List<string> list = new List<string>();

            if (row == null || row.Cells == null || !row.Cells.Any()) return list;

            list = row.Cells.Select(t => t.GetDecodeEscapes()).ToList();

            return list;
        }

        public static Dictionary<string, int> GetCellIndex(this IRow row, params string[] list)
        {
            Dictionary<string, int> pairs = new Dictionary<string, int>();

            if (row == null || row.Cells == null || !row.Cells.Any()) return pairs;

            if (list == null || !list.Any()) return pairs;

            var columns = row.Cells.Select(t => t.GetDecodeEscapes()).Distinct().ToList();
            foreach (string key in list)
            {
                if (!pairs.ContainsKey(key) && columns.Contains(key))
                {
                    pairs.Add(key, columns.IndexOf(key));
                }
            }

            return pairs;
        }

        /// <summary>
        /// 实时更新公式的结果
        /// </summary>
        /// <param name="workbook"></param>
        public static void EvaluateAll(this IWorkbook workbook)
        {
            try
            {
                var creation = workbook.GetCreationHelper();
                var formula = creation?.CreateFormulaEvaluator();
                formula?.EvaluateAll();
            }
            catch
            {
                return;
            }
        }

        /// <summary>
        /// 设置打开Excel时将更新公式
        /// </summary>
        /// <param name="workbook"></param>
        public static void ForceFormulaRecalculation(this IWorkbook workbook)
        {
            try
            {
                if (workbook == null) return;

                int index = 0;
                while (index < workbook.NumberOfSheets)
                {
                    var sheet = workbook.GetSheetAt(index);
                    index++;

                    sheet.ForceFormulaRecalculation = true;
                }
            }
            catch
            {
                return;
            }
        }
    }
}
