第一步:为项目安装Epplus Nuget包。
Install-Package EPPlus
第二步:在data项目中新建Repository,命名为ExcelWriter.cs.
将以下代码粘入,修改项目引用名字。
using System; using OfficeOpenXml; using System.Collections.Generic; using System.ComponentModel; using System.IO; using System.Linq; namespace ****.Data.Repositories { public class ExcelWriter<TEntity> where TEntity :class { public void WriteToFileStream(IEnumerable<TEntity> list, out MemoryStream memoryStream) { var pack = new ExcelPackage(); var ws = pack.Workbook.Worksheets.Add("预约时间表"); var col = 1; var row = 1; foreach (var propertyInfo in typeof(TEntity).GetProperties()) { var displayName = propertyInfo.Name; ws.Cells[row, col].Value = displayName; col++; } row++; foreach (var coachBatchPayoutExcelModel in list) { for (var i = 1; i < col; i++) { var colName = ws.Cells[1, i].Value.ToString(); ws.Cells[row, i].Value = coachBatchPayoutExcelModel.GetType().GetProperty(colName).GetValue(coachBatchPayoutExcelModel, null); } row++; } foreach (var propertyInfo in typeof(TEntity).GetProperties()) { var attribute = propertyInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true).Cast<DisplayNameAttribute>().SingleOrDefault(); if (attribute != null) { ws.Cells.First(item => item.GetValue<string>() == propertyInfo.Name).Value = attribute.DisplayName; } } ws.Column(6).Style.Numberformat.Format = "yyyy年MM月dd日"; ws.Column(1).Width = 20; ws.Column(2).Width = 15; ws.Column(3).Width = 20; ws.Column(4).Width = 25; ws.Column(5).Width = 6; ws.Column(6).Width = 15; memoryStream = new MemoryStream(pack.GetAsByteArray()); } } }
以上为一泛型,如何使用请参考以下使用样例。
#region 下载数据 Mapper.CreateMap<OnlineBookingInfo, ExcelModel>(); var excels = Mapper.Map<List<OnlineBookingInfo>, List<ExcelModel>>(onlineBookingInfos); MemoryStream memoryStream; var excelWriter = new ExcelWriter<ExcelModel>(); excelWriter.WriteToFileStream(excels, out memoryStream); return File(memoryStream, "application/vnd.ms-excel", "郑州科技馆预约参观名单" + DateTime.Now.Date.ToString("yyyy_MM_dd") + ".xls"); #endregion
先将需要导出的数据使用mapper,映射到新的实体类型(只需要导出的实体,此处的可以为新建)
excel的title为新建实体的dispalyname的名字。
使用的时候先
MemoryStream memoryStream;
var excelWriter = new ExcelWriter<ExcelModel>();
然后
excelWriter.WriteToFileStream(excels, out memoryStream);
最后输出
return File(memoryStream, "application/vnd.ms-excel", "郑州科技馆预约参观名单" + DateTime.Now.Date.ToString("yyyy_MM_dd") + ".xls");