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 ValveExcelHelper
    {
        public static string ParseValveExcel(string fileName, out List<HStation.Vmo.AssetsValveMainVmo> list)
        {
            list = new List<Vmo.AssetsValveMainVmo>();
            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_caliber = 1;
            //材料列
            int col_index_materials = 2;
            //系数列
            int col_index_coefficient = 3;
            //备注列
            int col_index_descriptions = 4;

            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_caliber);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double? caliber))
                    break;
                if (caliber < 0)
                    break;
                var material = row_temp.GetCell(col_index_materials);
                if (material == null)
                    break;
                cell = row_temp.GetCell(col_index_coefficient);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double coefficient))
                    break;
                if (coefficient < 0)
                    break;
                list.Add(new Vmo.AssetsValveMainVmo
                {
                    Name = name.StringCellValue,
                    Caliber = caliber,
                    Material = material.StringCellValue,
                    MinorLoss = coefficient,
                    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 ExportValveTemplate(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("口径(mm)");
            rowTile1.CreateCell(2).SetCellValue("材料");
            rowTile1.CreateCell(3).SetCellValue("局阻系数");
            rowTile1.CreateCell(4).SetCellValue("备注");
            theSheet1.ForceFormulaRecalculation = true;

            using FileStream fs = File.OpenWrite(file_path);
            theBook.Write(fs);
        }
    }
}