using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace HotelPms.Share.Windows.Util
{
    public class NPOIExcel : IDisposable
    {
        #region  ★★★★★　Declartions　★★★★★

        private bool m_Disposed = false;
        private bool m_IsEditting = false;

        #endregion

        #region  ★★★★★　Property　★★★★★

        /// <summary>
        /// 絶対パス
        /// </summary>
        public string FileName { get; set; } = string.Empty;

        /// <summary>
        /// 現在のWorkbook
        /// </summary>
        public IWorkbook Workbook { get; set; } = null;

        /// <summary>
        /// 選択されたSheet
        /// </summary>
        public ISheet Sheet { get; set; } = null;

        /// <summary>
        /// Excel2003前？
        /// </summary>
        public bool XlsMode { get; set; } = false;

        #endregion

        #region  ★★★★★　Class Event　★★★★★

        public NPOIExcel(string fileName)
        {
            FileName = fileName;
            FileInfo fileInfo = new FileInfo(FileName);
            XlsMode = (fileInfo.Extension == ".xls");

            if (System.IO.File.Exists(FileName))
            {
                using (FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read))
                {
                    Workbook = XlsMode ? new HSSFWorkbook(file) : new XSSFWorkbook(file);
                }
                Sheet = Workbook.GetSheetAt(0);
                Sheet.ForceFormulaRecalculation = true;
            }
            else
            {
                Workbook = XlsMode ? new HSSFWorkbook() : new XSSFWorkbook();
                Sheet = Workbook.CreateSheet("Sheet1"); //シートの作成
            }
        }

        ~NPOIExcel()
        {
            Dispose(false);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!m_Disposed)   //一回だけ
            {
                if (disposing)
                {
                    if (m_IsEditting) { Save(); }
                    //Managed Resources
                }

                //Unmanaged resources
                m_Disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        #endregion

        #region  ★★★★★　Private Function　★★★★★

        private string GetCellFormat(ICell cell)
        {
            IDataFormat df = XlsMode ?  new HSSFDataFormat((Workbook as HSSFWorkbook).Workbook) : new XSSFDataFormat((Workbook as XSSFWorkbook).GetStylesSource());
            return df.GetFormat(cell.CellStyle.DataFormat);
        }


        #endregion

        #region  ★★★★★　Public  Function　★★★★★

        /// <summary>
        /// シート変換
        /// </summary>
        /// <param name="sheetIdx"></param>
        public void SetCurrentSheet(int sheetIdx)
        {
            Sheet = Workbook.GetSheetAt(sheetIdx);
            Sheet.ForceFormulaRecalculation = true;
        }

        /// <summary>
        /// 存在しない時、null
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <returns></returns>
        public ICell? GetCell(string colRowValue)
        {
            return GetCell(colRowValue, false);
        }

        public object? GetCellValue(string colRowValue)
        {
            try
            {
                ICell cell = GetCell(colRowValue);
                if (cell == null) { return string.Empty; }
                System.Diagnostics.Debug.WriteLine($"{colRowValue}.CellType={cell.CellType}");
                if (cell.CellType == CellType.Numeric) { return cell.NumericCellValue; }
                else if (cell.CellType == CellType.Boolean) { return cell.BooleanCellValue; }
                else { return cell.StringCellValue; }
            }
            catch
            {
                return string.Empty;
            }
        }

        public string? GetCellForceString(string colRowValue)
        {
            try
            {
                object ret = GetCellValue(colRowValue);
                return ret == null ? string.Empty : ret.ToString();
            }
            catch
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// セルの値を取得する
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <returns></returns>
        public string GetCellString(string colRowValue)
        {
            try
            {
                ICell cell = GetCell(colRowValue);
                if (cell == null) { return string.Empty; }
                System.Diagnostics.Debug.WriteLine($"{colRowValue}={cell.StringCellValue}");
                return cell.StringCellValue;
            }
            catch
            {
                return string.Empty;
            }
        }

        public int GetCellInt(string colRowValue)
        {
            try
            {
                ICell cell = GetCell(colRowValue);
                if (cell == null) { return 0; }
                return (int)cell.NumericCellValue;
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 存在しない時、新規
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <returns></returns>
        public ICell? GetCell(string colRowValue, bool newCell)
        {
            System.Drawing.Point p = GetCellPoint(colRowValue);
            IRow row = Sheet.GetRow(p.Y);
            if (row == null) 
            {
                if (newCell) { row = Sheet.CreateRow(p.Y); } else { return null; }
            }

            ICell cell = row.GetCell(p.X);
            if (cell == null) 
            {
                if (newCell) { cell = row.CreateCell(p.X); } else { return null; }  
            }
            return cell;
        }

        /// <summary>
        /// フォントの作成
        /// </summary>
        /// <returns></returns>
        public IFont CreateFont()
        {
            IFont font = Workbook.CreateFont();
            return font;
        }

        /// <summary>
        /// 計算式セット
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <param name="formula"></param>
        public void SetCellFormula(string colRowValue, string formula)
        {
            m_IsEditting = true;
            ICell cell = GetCell(colRowValue, true);
            cell.SetCellFormula(formula);

        }

        /// <summary>
        /// セルの値セット：xls専用
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <param name="richTextString"></param>
        public void SetCell(string colRowValue, HSSFRichTextString richTextString)
        {
            m_IsEditting = true;
            ICell cell = GetCell(colRowValue, true);
            cell.SetCellValue(richTextString);
        }

        /// <summary>
        /// セルの値セット：数字専用
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <param name="value"></param>
        public void SetCell(string colRowValue, double value)
        {
            m_IsEditting = true;
            ICell cell = GetCell(colRowValue, true);
            cell.SetCellValue(value);
        }

        /// <summary>
        /// セルの値セット：日付専用
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <param name="value"></param>
        public void SetCell(string colRowValue, DateTime value)
        {
            m_IsEditting = true;
            ICell cell = GetCell(colRowValue, true);
            cell.SetCellValue(value);
        }

        /// <summary>
        /// セルの値セット：Boolean
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <param name="value"></param>
        public void SetCell(string colRowValue, bool value)
        {
            m_IsEditting = true;
            ICell cell = GetCell(colRowValue, true);
            cell.SetCellValue(value);
        }

        /// <summary>
        /// 数字型変換対応
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <param name="value"></param>
        public void SetCell(string colRowValue, string value)
        {
            m_IsEditting = true;
            System.Drawing.Point p = GetCellPoint(colRowValue);
            IRow row = Sheet.GetRow(p.Y);
            if (row == null) { row = Sheet.CreateRow(p.Y); }

            ICell cell = row.GetCell(p.X);
            if (cell == null) { cell = row.CreateCell(p.X); }

            string format = GetCellFormat(cell);
            if ((format.Contains("#") && format.Contains("0")) || format.Contains("0_"))
            {
                double val = 0;
                if (double.TryParse(value, out val))
                {
                    cell.SetCellValue(val);
                }
                else
                {
                    cell.SetCellValue(value);
                }
            }
            else
            {
                cell.SetCellValue(value);
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        public void Save()
        {
            //Write the stream data of workbook to the root directory
            using (FileStream file = new FileStream(FileName, FileMode.Create))
            {
                Workbook.Write(file, true);
                file.Close();
            }
        }

        /// <summary> ExcelX位置→EXCEL列文字  (1→A,2→B,3→C,27→AA)
        /// </summary>  
        /// <param name="ColNum">索引号</param>  
        /// <returns>string</returns>  
        public static string ToColName(int ColNum)
        {
            int ll_mod;
            string ColName = "";

            while (ColNum > 0)
            {
                ll_mod = ColNum % 26;
                if (ll_mod == 0)
                {
                    ColName = "Z" + ColName;
                    ll_mod = 1;
                }
                else
                {
                    ColName = (Convert.ToChar(ll_mod + 64)).ToString() + ColName;
                }
                ColNum = (ColNum - ll_mod) / 26;
            }

            return ColName;
        }

        /// <summary> Excel列文字→ExcelX位置  ("A"→1、"AA"→27)
        /// </summary>  
        /// <param name="ColName">列名</param>  
        /// <returns>int</returns>  
        public static int ToIndex(string ColName)
        {
            int ColIndex = 0;
            for (int i = ColName.Length - 1; i >= 0; i--)
            {
                ColIndex += Convert.ToInt32(((int)ColName[i] - 64) * Math.Pow(26, (ColName.Length - 1) - i));
            }

            return ColIndex;
        }


        /// <summary> Excel文字→Excel位置("A1"→(0,0)、"B2"→(1,1))(0基準)
        /// </summary>
        /// <param name="colRowValue"></param>
        /// <returns></returns>
        public static System.Drawing.Point GetCellPoint(string colRowValue)
        {
            System.Drawing.Point point = new System.Drawing.Point(0, 0);
            string colstr = string.Empty;
            string rowStr = string.Empty;
            for (int i = 0; i < colRowValue.Length; i++)
            {
                if (Char.IsNumber(colRowValue[i]))
                {
                    rowStr += colRowValue[i].ToString();
                }
                else
                {
                    colstr += colRowValue[i].ToString();
                }
            }

            point.Y = Convert.ToInt32(rowStr) - 1;  //行
            point.X = ToIndex(colstr) - 1;
            return point;
        }

        #endregion
    }
}