mvc npoi将List<实体>导出excel的最简单方法

 

一、最屌丝的方法。只是临时导数据用的。方便。最基本的方法，  

　　　　　[HttpGet]
        [Route("ExportEnterprise")]
        public BaseResponse ExportEnterprise()
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("onesheet");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("顺序号");
            row0.CreateCell(1).SetCellValue("企业名");
            row0.CreateCell(2).SetCellValue("行业门类");
            row0.CreateCell(3).SetCellValue("行业大类");
            row0.CreateCell(4).SetCellValue("经营属地");
            row0.CreateCell(5).SetCellValue("法人");
            row0.CreateCell(6).SetCellValue("法人手机");
            row0.CreateCell(7).SetCellValue("法人固话");

            var enterprises = _enterpriseService.GetEnterprisesOfTest().ToList();
            var lineNo = 1;
            foreach (var enterprise in enterprises)
            {
                IRow row = sheet.CreateRow(lineNo);
                row.CreateCell(0).SetCellValue(lineNo);
                row.CreateCell(1).SetCellValue(enterprise.EnterpriseName);
                var doorDescr = _industryCategoryService.GetDescriptionBy(enterprise.IndustryCategoryCode);
                row.CreateCell(2).SetCellValue(doorDescr);
                var industryGeneraDescr = _industryCategoryService.GetDescriptionBy(enterprise.IndustryGeneraCode);
                row.CreateCell(3).SetCellValue(industryGeneraDescr);
                row.CreateCell(4).SetCellValue(_administrativeDivisionService.GetDescriptionBy(enterprise.BusinessAddressDivisonCode));
                row.CreateCell(5).SetCellValue(enterprise.LegalPersonName);
                row.CreateCell(6).SetCellValue(enterprise.LegalPersonPhone);
                row.CreateCell(7).SetCellValue(enterprise.LegalPersonFixedPhone);
                         lineNo++;
            }

            //创建流对象并设置存储Excel文件的路径
            using (FileStream url = new FileStream(HttpContext.Current.Server.MapPath("/App_Data/test.xls"), FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                //导出Excel文件
                workbook.Write(url);
            };
            return Success(new BaseResponse());
        }

二、高大上的通用方法

在baseController里写个通用方法，利用反射原理，获取对象的每一个属性的DisplayName作表头

/// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fileBaseName">不带后缀</param>
        /// <param name="datas"></param>
        /// <returns></returns>
        public ActionResult ExportToExcel<T>(string fileBaseName, List<T> datas) where T: ExcelModel
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("导出数据");
            IRow headerRow = sheet.CreateRow(0);

            int rowIndex = 1, piIndex = 0;
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties();
            int pisLen = pis.Length;
            PropertyInfo pi = null;
            string displayName = string.Empty;
            while (piIndex < pisLen)
            {
                pi = pis[piIndex];
                var pName = pi.GetCustomAttribute<DisplayNameAttribute>();
                displayName = pName?.DisplayName??string.Empty;
                if (!displayName.Equals(string.Empty))
                {//如果该属性指定了DisplayName，则输出
                    try
                    {
                        headerRow.CreateCell(piIndex).SetCellValue(displayName);
                    }
                    catch (Exception)
                    {
                        headerRow.CreateCell(piIndex).SetCellValue("");
                    }
                }
                piIndex++;
            }
            foreach (T data in datas)
            {
                piIndex = 0;
                IRow dataRow = sheet.CreateRow(rowIndex);
                while (piIndex < pisLen)
                {
                    pi = pis[piIndex];
                    try
                    {
                        dataRow.CreateCell(piIndex).SetCellValue(pi.GetValue(data, null).ToString());
                    }
                    catch (Exception)
                    {
                        dataRow.CreateCell(piIndex).SetCellValue("");
                    }
                    piIndex++;
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", $"{fileBaseName}.xls");
        }

对象值 需要加displayName注解

  public class ActivityGradeExcelModel: ExcelModel
    {
        [DisplayName("月份")]
        [DisplayFormat(DataFormatString = "yyyy-MM")]
        public DateTime ConductDate { get; set; }
        [DisplayName("活动")]
        public string ActivityName { get; set; }
        [DisplayName("姓名")]
        public string StudentName { get; set; }
        [DisplayName("分数")]
        public decimal Score { get; set; }
        [DisplayName("班级")]
        public string SchoolClassName { get; set; }
        [DisplayName("学号")]
        public string StudyNo { get; set; }
        [DisplayName("专业")]
        public string CollegeMajor { get; set; }
        [DisplayName("学期")]
        public int ConductYear { get; set; }
 
    }

Action中代码

  var gradeModels = query.OrderByDescending(m => m.ConductDate).ThenBy(m => m.ActivityName)
                    .ThenBy(m => m.SchoolClassName)
                    .Select(m => new ActivityGradeExcelModel()
                    {
                        ConductDate = m.ConductDate,
                        ActivityName = m.ActivityName,
                        StudentName = m.StudentName,
                        Score = m.Score,
                        SchoolClassName = m.SchoolClassName,
                        StudyNo = m.StudyNo,
                        CollegeMajor = m.CollegeMajor,
                        ConductYear = m.ConductYear
                    }).ToList();
                var fileBaseName = $"活动成绩表{DateTime.Now.ToString("yyyyMMdd")}";
                return ExportToExcel(fileBaseName, gradeModels);