C# 使用 NPOI 读取 Excel 转为 Entity

感谢这篇文章，[Npoi]Npoi导入Excel, 转为Entity
原文章使用了CustomAttribute，本文改为直接用Propertie，写着方便-_-||

项目场景：

C# 使用 NPOI 读取 Excel 转为 Entity

用DataTable存储读取到的Excel数据，再走反射那套操作

---

解决方案：

        public class Archive
        {
            public string ValueID { get; set; } = string.Empty;
            public string ValueName { get; set; } = string.Empty;
        }

        static void Main(string[] args)
        {
			var Archives = new ExcelReader(Path.Combine(path, "Archive.xlsx")).ToList<Archive>();
			Console.WriteLine(Archives.First().ValueID);
        }

    public class ExcelReader
    {
        public string FilePath { get; set; } = string.Empty;
        public int SheetIndex { get; set; } = 0;
        public string FileExt { get { return Path.GetExtension(FilePath).ToLower(); } }

        private DataTable _dataTable;

        /// <summary>
        /// 初始化文件路径和Sheet序号
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetIndex"></param>
        public ExcelReader(string filePath, int sheetIndex = 0)
        {
            FilePath = filePath;
            SheetIndex = sheetIndex;
        }


        /// <summary>
        /// 转为指定类型
        /// </summary>
        /// <typeparam name="T"> </typeparam>
        /// <returns> </returns>
        public List<T> ToList<T>()
        {
            if (_dataTable is null) ToDataTable();

            var list = new List<T>();
            var type = typeof(T);
            var properties = type.GetProperties();
            foreach (DataRow row in _dataTable.Rows)
            {
                var t = Activator.CreateInstance<T>();
                foreach (var property in properties)
                {
                    var prop_name = property.Name;
                    var value = row[prop_name];
                    if (value != DBNull.Value)
                    {
                        var d = Convert.ChangeType(value, property.PropertyType);
                        property.SetValue(t, d);
                    }
                }
                list.Add(t);
            }
            return list;
        }


        /// <summary>
        /// Excel转为DataTable
        /// </summary>
        /// <returns> </returns>
        private ExcelReader ToDataTable()
        {
            _dataTable = new DataTable();
            using (var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook? workbook;

                if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (FileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

                if (workbook == null) { throw new Exception("Wrong File Type. Use .xlsx or .xls"); }

                var sheet = workbook.GetSheetAt(SheetIndex);
                if (sheet == null)
                {
                    throw new Exception("Excel sheet not found");
                }

                var headerRow = sheet.GetRow(0);
                var headerRowCount = headerRow.LastCellNum;
                for (var i = headerRow.FirstCellNum; i < headerRowCount; i++)
                {
                    var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    _dataTable.Columns.Add(column);
                }

                for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    var dataRow = _dataTable.NewRow();
                    for (var j = row.FirstCellNum; j < headerRowCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    _dataTable.Rows.Add(dataRow);
                }
            }

            return this;
        }
    }