c# excel npoi 导入

二话不说直接上代码：

 

  public static class ExcelImport
    {
        /// <summary>
        /// 获取导入excel数据（npoi）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="workbook">excel表</param>
        /// <param name="sheetName">sheet名称，不传默认取第一个，多个用逗号隔开</param>
        /// <param name="headRowNum"></param>
        /// <param name="isTrim">内容是否去空格</param>
        /// <returns></returns>
        public static List<T> ExcelToList<T>(this IWorkbook workbook, string sheetName = null, int headRowNum = 0, bool isTrim = true)
             where T : class, new()
        {
            var resObj = new List<T>();
            //如果有指定工作表名称
            if (!string.IsNullOrWhiteSpace(sheetName))
            {
                foreach (var name in sheetName.Split(','))
                {
                    ISheet sheet = workbook.GetSheet(name);
                    var dataList = ExcelSheetToList<T>(sheet, headRowNum, isTrim);
                    resObj.AddRange(dataList);
                }
            }
            else
            {
                //如果没有指定的sheetName，则尝试获取第一个sheet
                ISheet sheet = workbook.GetSheetAt(0);
                var dataList = ExcelSheetToList<T>(sheet, headRowNum, isTrim);
                resObj.AddRange(dataList);
            }
            return resObj;
        }

        /// <summary>
        /// 获取导入excel数据（npoi）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sheet"></param>
        /// <param name="headRowNum">表头为第几行默认第一行</param>
        /// <param name="isTrim">内容是否去空格</param>
        /// <returns></returns>
        public static List<T> ExcelSheetToList<T>(this ISheet sheet, int headRowNum = 0, bool isTrim = true)
            where T : class, new()
        {
            var resObj = new List<T>();
            if (sheet != null)
            {
                //最后一列的标号
                int rowCount = sheet.LastRowNum;

                //// 处理表头
                IRow headRow = sheet.GetRow(headRowNum);

                //总的列数
                int cellCount = headRow.LastCellNum;

                #region 处理表头
                Dictionary<int, PropertyInfo> dic = new Dictionary<int, PropertyInfo>();

                for (int i = 0; i < cellCount; i++)
                {
                    var propertiesList = typeof(T).GetProperties();
                    ICell headCell = headRow.GetCell(i);
                    if (headCell != null)
                    {
                        string cellValue = headCell.StringCellValue;
                        if (cellValue.IsNotEmpty() && propertiesList.IsNotNullOrEmptyList())
                        {
                            foreach (var propertyInfo in propertiesList)
                            {
                                var displayName = propertyInfo.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
                                if (displayName.IsNotEmpty() && displayName == cellValue)
                                {
                                    dic[i] = propertyInfo;
                                }
                            }
                        }
                    }
                }

                #endregion

                //// 数据读取的开始
                int rowBeginNum = headRowNum + 1;

                for (int i = rowBeginNum; i <= rowCount; i++)
                {
                    T resData = new T();
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null　　

                    foreach (var dicInfo in dic)
                    {
                        var cellNum = dicInfo.Key;
                        var propertyInfo = dicInfo.Value;
                        var sourcevalue = string.Empty;
                        if (isTrim)
                        {
                            sourcevalue = row.GetCell(cellNum)?.ToString().Trim();
                        }
                        else
                        {
                            sourcevalue = row.GetCell(cellNum)?.ToString();
                        }
                        FillProValue(resData, propertyInfo, sourcevalue);
                    }

                    resObj.Add(resData);

                }
            }
            return resObj;
        }


        /// <summary>
        /// 属性赋值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="resObj">返回的对象</param>
        /// <param name="resProperty">被赋值的属性</param>
        /// <param name="sourcevalue">数据源属性值</param>
        private static void FillProValue<T>(T resObj, PropertyInfo resProperty, object sourcevalue)
        {
            if (!resProperty.PropertyType.IsGenericType)
            {
                var newValue = string.IsNullOrEmpty(sourcevalue?.ToString()) ? null : Convert.ChangeType(sourcevalue, resProperty.PropertyType);
                //非泛型
                resProperty.SetValue(resObj, newValue, null);
            }
            else
            {
                //泛型Nullable<>
                Type genericTypeDefinition = resProperty.PropertyType.GetGenericTypeDefinition();
                if (genericTypeDefinition == typeof(Nullable<>))
                {
                    var newValue = string.IsNullOrEmpty(sourcevalue?.ToString()) ? null : Convert.ChangeType(sourcevalue, Nullable.GetUnderlyingType(resProperty.PropertyType));
                    resProperty.SetValue(resObj, newValue, null);
                }
            }



        }
    }

 

 

        /// <summary>
        ///  xxx
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("fileUpload")]
        [CreateBy("Conlin.Lin")]
        [AllowAnonymous]
        public async Task<IActionResult> fileUpload([FromForm]aa input)
        {
            IFormFileCollection formFiles = Request.Form.Files;//获取上传的文件  
            IFormFile file = formFiles[0];
            IWorkbook workbook = null;
            if (file.FileName.IndexOf(".xlsx") > 0)
            {
                using (var stream= file.OpenReadStream())
                {
                    workbook = new XSSFWorkbook(stream);//excel的版本2007
                }
         
            }
            else if (file.FileName.IndexOf(".xls") > 0)
            {
                using (var stream = file.OpenReadStream())
                {
                    workbook = new HSSFWorkbook((Stream)file);//excel的版本2003
                }
           
            }
            var ss = workbook.ExcelToList<bb>();
            return Ok(ss);
        }
        public class aa
        {
            /// <summary>
            /// 附件
            /// </summary>
            public IFormFile file { get; set; }
        }
        public class bb
        {
            [DisplayName("姓名")]
            public string name { get; set; }

            [DisplayName("年龄")]
            public int age { get; set; }
        }