using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.SS.UserModel;

namespace NPOIDemo
{
    public class Excel
    {
        public HSSFWorkbook hssfWorkbook
        {
            get {
                FileStream file = new FileStream(@"D:\Source\NPOIDemo\NPOIDemo\Files\temp.xls", FileMode.Open, FileAccess.Read);

                return new HSSFWorkbook(file);
            }
        }

        /// <summary>
        /// 获取Sheet中所有数据有效性
        /// </summary>
        /// <returns></returns>
        public List<IDataValidation> GetDataValidations()
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            return ws.GetDataValidations();
        }

        /// <summary>
        /// 获取单元格数据有效性
        /// </summary>
        /// <param name="range">单元格范围 F71:F93或E5等</param>
        /// <returns></returns>
        public string[] GetExplicitListValuesByRange(string range)
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            var dataValidations = ws.GetDataValidations();
            foreach (var item in dataValidations)
            {
                foreach (var rangeAddresses in item.Regions.CellRangeAddresses)
                {
                    if (rangeAddresses.FormatAsString() == range)
                    {
                        return item.ValidationConstraint.ExplicitListValues;
                    }
                }
            }

            return new string[0];
        }

        /// <summary>
        /// 获取批注
        /// </summary>
        /// <returns></returns>
        public string GetCommont()
        {
            ISheet ws = hssfWorkbook.GetSheet("1");
            IComment commont=ws.GetCellComment(88, 55);

            return commont.String.String;
        }

    }
}