最近在做 MVC 项目的时候遇到项目的导出，下面总结下两种导出到excel 的方法
第一种方法： 将文件写到本地，然后返回这个File 或者返回这个 File 的绝对地址
其中 _hostingEnvironment 可以在全局读取的配置文件的工具类 Globals 中配置
直接上代码：
private IHostingEnvironment _hostingEnvironment; public XlsxController(IHostingEnvironment hostingEnvironment) { _hostingEnvironment = hostingEnvironment; } public IActionResult ExportData(int programmeId) { //获取方案名称 var programmeName = ProgrammeService.GetProgrammeNameById(programmeId); //获取列表内容 var dataFromDb = ProgrammeService.GetProgrammeStatisticEnrolmentExportList(programmeId); var data = dataFromDb.Select(a => new ProgrammeStatisticsEnrolmentDetailsExportViewModel { UserName = a.UserName, Phone = a.Phone, EnrolTime = a.EnrolTimeStr }).ToList(); var sWebRootFolder = _hostingEnvironment.WebRootPath; var sFileName = $"XXX-{programmeName}.xlsx"; var file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); using (var package = new ExcelPackage(file)) { //如果之前有同名的文件先删除然后重新创建 var count = package.Workbook.Worksheets.Count; if (count > 0) { for (var i = 0; i < count; i++) { package.Workbook.Worksheets.Delete(i + 1); } package.File.Delete(); } // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1"); //添加头 worksheet.Cells[1, 1].Value = "姓名"; worksheet.Cells[1, 2].Value = "手机号"; worksheet.Cells[1, 3].Value = "报名时间"; //添加值 for (var i = 0; i < data.Count; i++) { worksheet.Cells[$"A{i + 2}"].Value = data[i].UserName; worksheet.Cells[$"B{i + 2}"].Value = data[i].Phone; worksheet.Cells[$"C{i + 2}"].Value = data[i].EnrolTime; } package.Save(); } var returnFile = File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); returnFile.FileDownloadName = sFileName; return returnFile; }
第二种方法： 用写流文件的方法 将文件写入浏览器
这里首先我们需要一个导出的工具类 如下：
////// 公共工具类 /// public class CommonHelper { //获取列名委托方法 public delegate string GetColumnName(string columnName); #region 导入导出Excel相关 ////// 将泛类型集合List类转换成DataTable /// /// 泛类型集合 ///返回转换后的DataTable public static DataTable ListToDataTable(List entitys) { //生成DataTable的structure var dt = new DataTable(); try { //检查泛型实体是否为空 if (entitys == null || entitys.Count < 1) { return dt; } //取出第一个实体的所有Propertie var entityType = entitys[0].GetType(); var entityProperties = entityType.GetProperties(); for (var i = 0; i < entityProperties.Length; i++) { dt.Columns.Add(entityProperties[i].Name); } //将所有entity添加到DataTable中 foreach (object entity in entitys) { //检查所有的的实体都为同一类型 if (entity.GetType() != entityType) { throw new Exception("要转换的集合元素类型不一致"); } var entityValues = new object[entityProperties.Length]; for (var i = 0; i < entityProperties.Length; i++) { entityValues[i] = entityProperties[i].GetValue(entity, null); } dt.Rows.Add(entityValues); } } catch (Exception e) { Console.WriteLine(e); } return dt; } /// /// 将dataTable转换为Excel字节流 /// /// /// ///public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName) { var xssfworkbook = new XSSFWorkbook(); var sheet = xssfworkbook.CreateSheet("Sheet"); //表头 var row = sheet.CreateRow(0); for (var i = 0; i < dt.Columns.Count; i++) { var cell = row.CreateCell(i); //列名称,数据库中字段 var columnName = dt.Columns[i].ColumnName; var convertColumnName = getColumnName(columnName); cell.SetCellValue(convertColumnName); } //数据 for (var i = 0; i < dt.Rows.Count; i++) { var row1 = sheet.CreateRow(i + 1); for (var j = 0; j < dt.Columns.Count; j++) { var cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //转为字节数组 var stream = new MemoryStream(); xssfworkbook.Write(stream); var buf = stream.ToArray(); return buf; } #endregion }
然后我们就可以导出了
////// 表格列名称 /// private static string GetColumnNameForDetail(string columnName) { switch (columnName) { case "UserName": return CommonConst.Column_Programme_Detail_UserName; case "Phone": return CommonConst.Column_Programme_Detail_Phone; case "EnrolTime": return CommonConst.Column_Programme_Detail_EnrolTime; default: return String.Empty; } } ////// 导出excel /// [HttpGet] public void ExportData(int programmeId) { //获取方案名称 var programmeName = ProgrammeService.GetProgrammeNameById(programmeId); //获取列表内容 var dataFromDb = ProgrammeService.GetProgrammeStatisticEnrolmentExportList(programmeId); var data = dataFromDb.Select(a => new ProgrammeStatisticsEnrolmentDetailsExportViewModel { UserName = a.UserName, Phone = a.Phone, EnrolTime = a.EnrolTimeStr }).ToList(); var sFileName = string.Format(CommonConst.Export_Programme_Detail_Excel_Name, programmeName); var dataTable = CommonHelper.ListToDataTable(data); var result = CommonHelper.GetExcelForXLSX(dataTable, GetColumnNameForDetail); Response.ContentType = ResponseConfigure.ContentTypeExcel; SetResponseHeaderForDetail(sFileName); Response.Body.Write(result); Response.Body.Flush(); Response.Body.Close(); } ////// 设定导出的标头内容 /// /// 导出的文件名 private void SetResponseHeaderForDetail(string fileName) { Response.Headers.Add(ResponseConfigure.ContentDisposition, ResponseConfigure.Attachment + HttpUtility.UrlEncode(fileName)); }