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());