NET Excel轉換為集合對象

1.僅適用於規則Excel：表頭和數據一一對應

2.涉及到Excel轉換為集合對象的部分代碼，完整npoi幫助類點擊查看

        /// <summary>
        /// 預設把excel第一個sheet中的數據轉換為對象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="filePath">文件路徑</param>
        /// <param name="sheetIndex">數據所在sheet索引：預設第一個sheet</param>
        /// <param name="originIndex">數據開始行：表頭行索引</param>
        /// <returns></returns>
        public static List<T> ConvertExcelToList<T>(T entity, string filePath, int sheetIndex = 0, int originIndex = 0)
            where T : class, new()
        {
            var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            using (stream)
            {
                return ConvertExcelToList(entity, filePath, stream, sheetIndex, originIndex);
            }

        }

        /// <summary>
        /// 把excel轉換為對象集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="filePath">文件路徑</param>
        /// <param name="stream">文件流</param>
        /// <param name="sheetIndex">數據所在sheet索引：預設第一個sheet</param>
        /// <param name="originIndex">數據開始行：表頭行索引</param>
        /// <returns></returns>
        public static List<T> ConvertExcelToList<T>(T entity, string filePath, Stream stream, int sheetIndex = 0, int originIndex = 0)
            where T : class, new()
        {
            //  結果集合
            var list = new List<T>();

            //  獲取特性和屬性的關係字典
            Dictionary<string, string> propertyDictionary = GetPropertyDictionary(entity);

            var isExcel2007 = filePath.IsExcel2007();
            var workBook = stream.GetWorkbook(isExcel2007);
            //  獲得數據所在sheet對象
            var sheet = workBook.GetSheetAt(sheetIndex);
            //  獲取表頭和所在索引的關係字典
            Dictionary<string, int> headerDictionary = GetHeaderDictionary(originIndex, propertyDictionary, sheet);

            //  兩個字典對象，只有一個為空，則return
            if (!propertyDictionary.Any() || !headerDictionary.Any())
            {
                return list;
            }

            //  生成結果集合
            BuilderResultList(originIndex, list, propertyDictionary, sheet, headerDictionary);
            return list;
        }

        /// <summary>
        /// 生成結果集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="originIndex">數據開始行：表頭行索引</param>
        /// <param name="list">結果集合</param>
        /// <param name="propertyDictionary">特性和屬性的關係字典:屬性字典</param>
        /// <param name="sheet">數據所在sheet對象</param>
        /// <param name="headerDictionary">表頭和所在索引的關係字典:表頭字典</param>
        private static void BuilderResultList<T>(int originIndex, List<T> list, Dictionary<string, string> propertyDictionary, ISheet sheet, Dictionary<string, int> headerDictionary) where T : class, new()
        {
            #region 通過反射，綁定參數

            //  從表頭行下一行開始迴圈，直到最後一行
            for (int rowIndex = originIndex + 1; rowIndex <= sheet.LastRowNum; rowIndex++)
            {
                T newEntity = new T();
                var newEntityType = newEntity.GetType();
                var itemRow = sheet.GetRow(rowIndex);

                //  迴圈表頭字典
                foreach (var itemKey in headerDictionary.Keys)
                {
                    //  得到先對應的表頭列所在列索引
                    var columnIndex = headerDictionary[itemKey];
                    //  把格式轉換為utf-8
                    var itemCellValue = itemRow.GetValue(columnIndex).FormatUtf8String();

                    //  根據表頭值，從 屬性字典 中獲得 屬性值 名
                    var propertyName = propertyDictionary[itemKey];
                    newEntityType.GetProperty(propertyName).SetValue(newEntity, itemCellValue);
                }
                list.Add(newEntity);
            }

            #endregion
        }

        /// <summary>
        /// 獲取表頭和所在索引的關係字典
        /// </summary>
        /// <param name="originIndex">數據開始行：表頭行索引</param>
        /// <param name="propertyDictionary">特性和屬性的關係字典:屬性字典</param>
        /// <param name="sheet">數據所在sheet對象</param>
        /// <returns></returns>
        private static Dictionary<string, int> GetHeaderDictionary(int originIndex, Dictionary<string, string> propertyDictionary, ISheet sheet)
        {
            var headerDictionary = new Dictionary<string, int>();

            #region 獲取表頭和所在索引的關係字典

            //  獲得表頭所在row對象
           var itemRow = sheet.GetRow(originIndex);
            //  記錄表頭行，表頭和所在索引的關係，存入字典，暫不考慮表頭相同情況
            headerDictionary = new Dictionary<string, int>();
            //  可能會存在無限列情況，設置最大列為200
            var cellTotal = itemRow.Cells.Count() > 200 ? 200 : itemRow.Cells.Count();
            for (int columnIndex = 0; columnIndex < cellTotal; columnIndex++)
            {
                //  把格式轉換為utf-8
                var itemCellValue = itemRow.GetValue(columnIndex).FormatUtf8String();
                //  itemCellValue補等於空 且 不在headerDictionary中 且 在propertyDictionary中
                if (!itemCellValue.IsNullOrWhiteSpace() && !headerDictionary.ContainsKey(itemCellValue) && propertyDictionary.ContainsKey(itemCellValue))
                {
                    headerDictionary.Add(itemCellValue, columnIndex);
                }
            }

            #endregion

            return headerDictionary;
        }

        /// <summary>
        /// 獲取特性和屬性的關係字典
        /// </summary>
        /// <param name="PropertyArr"></param>
        /// <returns></returns>
        private static Dictionary<string, string> GetPropertyDictionary<T>(T entity)
        {
            //  獲取type
            var userType = typeof(T);
            //  獲取類中所有公共屬性集合
            var propertyArr = userType.GetProperties();

            #region 獲取特性和屬性的關係字典

            //  屬性字典，保存別名和屬性的對應關係
            //  key：別名，特性中的值
            //  value：屬性名，類中的屬性
            var propertyDictionary = new Dictionary<string, string>();
            foreach (var itemProperty in propertyArr)
            {
                //  獲取屬性上存在AliasAttribute的數組
                var customAttributesArr = itemProperty.GetCustomAttributes(typeof(AliasAttribute), true);
                //  存在該特性
                if (customAttributesArr.Any())
                {
                    var first = (AliasAttribute)customAttributesArr.FirstOrDefault();
                    if (!propertyDictionary.ContainsKey(first.Name))
                    {
                        propertyDictionary.Add(first.Name, itemProperty.Name);
                    }
                }
            }

            #endregion
            return propertyDictionary;
        }

3.調用測試

            var path = @"C:\導入文件.xlsx";
            var result = NpoiHelper.ConvertExcelToList(new UserDto(), path);

            Assert.IsTrue(result.Any());