一、最屌丝的方法。只是临时导数据用的。方便。最基本的方法,
[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);