using DevExpress.CodeParser;
using HStation.Assets;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;

namespace HStation.WinFrmUI
{
    public class PumpExcelHelper
    {
        public static string ParsePumpExcel(string fileName, out List<HStation.Vmo.AssetsPumpMainVmo> list)
        {
            list = new List<Vmo.AssetsPumpMainVmo>();
            if (!File.Exists(fileName))
                return "文件不存在";
            int line = 0;

            IWorkbook workbook;
            using (FileStream file = new(fileName, FileMode.Open, FileAccess.Read))
            {
                if (Path.GetExtension(fileName).ToLower() == ".xls")
                {
                    workbook = new HSSFWorkbook(file);
                }
                else if (Path.GetExtension(fileName).ToLower() == ".xlsx")
                {
                    workbook = new XSSFWorkbook(file);
                }
                else
                {
                    return "不支持的文件格式";
                }
            }

            ISheet sheet_ql = workbook.GetSheet("sheet1");
            if (sheet_ql == null)
            {
                return "表格不符合格式";
            }
            //标题行
            int title_line_index = 0;
            //名称列
            int col_index_name = 0;
            //流量列
            int col_index_flow = 1;
            //扬程列
            int col_index_head = 2;
            //效率列
            int col_index_efficiency = 3;
            //功率列
            int col_index_power = 4;
            //转速列
            int col_index_speed = 5;
            //叶轮外径列
            int col_index_d2 = 6;
            //气蚀列
            int col_index_erosion = 7;
            //说明列
            int col_index_descriptions = 8;
            var row_title = sheet_ql.GetRow(title_line_index);
            if (row_title == null)
            {
                return ("第一行第一列不能空");
            }

            //开始读取的行
            int start_line = title_line_index + 1;
            var cell_0 = row_title.GetCell(0);
            if (cell_0 == null)
            {
                return ("无法读取表头文件");
            }

            NPOI.SS.UserModel.IRow row_temp = null;
            NPOI.SS.UserModel.ICell cell;

            for (line = start_line; line < 1000; line++)
            {
                row_temp = sheet_ql.GetRow(line);
                if (row_temp == null)
                    break;
                var name = row_temp.GetCell(col_index_name);
                if (name == null)
                    break;

                cell = row_temp.GetCell(col_index_flow);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? flow))
                    break;
                if (flow < 0)
                    break;
                cell = row_temp.GetCell(col_index_head);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? head))
                    break;
                if (head < 0)
                    break;

                cell = row_temp.GetCell(col_index_efficiency);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? efficiency))
                    break;
                if (efficiency < 0)
                    break;

                cell = row_temp.GetCell(col_index_power);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? power))
                    break;
                if (power < 0)
                    break;

                cell = row_temp.GetCell(col_index_speed);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? speed))
                    break;
                if (speed < 0)
                    break;

                cell = row_temp.GetCell(col_index_d2);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? d2))
                    break;
                if (d2 < 0)
                    break;

                /*   cell = row_temp.GetCell(col_index_erosion);
                   if (cell == null)
                       break;
                   if (!ParseCellValue(cell, out double? erosion))
                       break;
                   if (erosion < 0)
                       break;*/

                list.Add(new Vmo.AssetsPumpMainVmo
                {
                    Name = name.StringCellValue,
                    RatedFlow = flow ?? 0, // 如果 flow 为 null，则使用默认值 0
                    RatedHead = head ?? 0, // 如果 head 为 null，则使用默认值 0
                    RatedEfficiency = efficiency,
                    RatedPower = power ?? 0, // 如果 power 为 null，则使用默认值 0
                    RatedSpeed = speed ?? 0, // 如果 speed 为 null，则使用默认值 0
                    D2 = d2,
                    //   Erosion = erosion,
                    //     Description = row_temp.GetCell(col_index_descriptions)?.StringCellValue,
                });
            }

            if (list == null || !list.Any())
            {
                return ("表格不符合格式");
            }
            return "";
        }

        private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double cell_value)
        {
            cell_value = 0;
            if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
            {
                cell_value = cell.NumericCellValue;
                return true;
            }

            if (cell.CellType == NPOI.SS.UserModel.CellType.String)
            {
                if (double.TryParse(cell.StringCellValue, out double value))
                {
                    cell_value = value;
                    return true;
                }
            }

            return false;
        }

        //转换可以为空
        private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double? cell_value)
        {
            cell_value = null;
            if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
            {
                cell_value = cell.NumericCellValue;
                return true;
            }

            if (cell.CellType == NPOI.SS.UserModel.CellType.String)
            {
                if (double.TryParse(cell.StringCellValue, out double value))
                {
                    cell_value = value;
                    return true;
                }
                else if (cell.StringCellValue == null)
                {
                    return true;
                }
            }
            if (cell.CellType == NPOI.SS.UserModel.CellType.Blank)
            {
                return true;
            }

            return false;
        }

        public static void ExportPumpTemplate(string file_path)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            var theSheet1 = theBook.CreateSheet("Sheet1");
            NPOI.SS.UserModel.IRow rowTile1 = theSheet1.CreateRow(0);
            rowTile1.CreateCell(0).SetCellValue("型号");
            rowTile1.CreateCell(1).SetCellValue("额定流量(m³/h)");
            rowTile1.CreateCell(2).SetCellValue("扬程(m)");
            rowTile1.CreateCell(3).SetCellValue("额定效率");
            rowTile1.CreateCell(4).SetCellValue("额定功率");
            rowTile1.CreateCell(5).SetCellValue("额定转速");
            rowTile1.CreateCell(6).SetCellValue("叶轮外径");
            rowTile1.CreateCell(7).SetCellValue("气蚀");
            rowTile1.CreateCell(8).SetCellValue("说明");
            theSheet1.ForceFormulaRecalculation = true;
            using FileStream fs = File.OpenWrite(file_path);
            theBook.Write(fs);
        }
    }
}