core6 EPPlus 导入下载
EPPlus是一个使用Open Office XML(xlsx)文件格式，能读写Excel 2007/2010 文件的开源组件，在导出Excel的时候不需要电脑上安装office。
对需要导出报表的数据进行如下操作：
1.引入EPPlus包，在程序包管理控制台中执行命令安装依赖包：
PM> Install-Package EPPlus.Core -Version 1.5.4
2.创建一个EPPlusHelper类,包括两个方法,导入和读取数据
using OfficeOpenXml; using OfficeOpenXml.Style; using System; using System.Collections.Generic; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.IO; namespace SW163.Infrastructure { public class EPPlusHelpe { private static int i; /// <summary> /// 导入数据到Excel中 /// </summary> /// <param name="fileName"></param> /// <param name="ds"></param> public static bool ImportExcel(string fileName, DataSet ds) { if (ds == null || ds.Tables.Count == 0) { return false; } FileInfo file = new FileInfo(fileName); if (file.Exists) { file.Delete(); file = new FileInfo(fileName); } //在using语句里面我们可以创建多个worksheet，ExcelPackage后面可以传入路径参数 //命名空间是using OfficeOpenXml using (ExcelPackage package = new ExcelPackage(file)) { foreach (DataTable dt in ds.Tables) { //创建工作表worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName); //给单元格赋值有两种方式 //worksheet.Cells[1, 1].Value = "单元格的值";直接指定行列数进行赋值 //worksheet.Cells["A1"].Value = "单元格的值";直接指定单元格进行赋值 worksheet.Cells.Style.Font.Name = "微软雅黑"; worksheet.Cells.Style.Font.Size = 12; worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { worksheet.Cells[i + 1, j + 1].Value = dt.Rows[i][j].ToString(); } } using (var cell = worksheet.Cells[1, 1, 1, dt.Columns.Count]) { //设置样式:首行居中加粗背景色 cell.Style.Font.Bold = true; //加粗 cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中 cell.Style.Font.Size = 14; cell.Style.Fill.PatternType = ExcelFillStyle.Solid; //背景颜色 cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色 } } //保存 package.Save(); } return true; } /// <summary> /// 读取Excel数据 /// </summary> /// <param name="fileName"></param> public static string ReadExcel(string fileName) { StringBuilder sb = new StringBuilder(); FileInfo file = new FileInfo(fileName); try { using (ExcelPackage package = new ExcelPackage(file)) { var count = package.Workbook.Worksheets.Count; for (int k = 1; k <= count; k++) //worksheet是从1开始的 { var workSheet = package.Workbook.Worksheets[k]; sb.Append(workSheet.Name); sb.Append(Environment.NewLine); int row = workSheet.Dimension.Rows; int col = workSheet.Dimension.Columns; for (int i = 1; i <= row; i++) { for (int j = 1; j <= col; j++) { sb.Append(workSheet.Cells[i, j].Value.ToString() + "\t"); } sb.Append(Environment.NewLine); } sb.Append(Environment.NewLine); sb.Append(Environment.NewLine); } } } catch (Exception ex) { return "An error had Happen"; } return sb.ToString(); } } }
3.在需要使用的地方调用
public ExportController(ISignRepository signRepository ,IUserRepository userRepository ,Microsoft.AspNetCore.Hosting.IHostingEnvironment hostingEnvironment ) { SignRepository = signRepository; UserRepository = userRepository; HostingEnvironment = hostingEnvironment; } public ISignRepository SignRepository { get; } public IUserRepository UserRepository { get; } public Microsoft.AspNetCore.Hosting.IHostingEnvironment HostingEnvironment { get; } public IActionResult Index() { return View(); } public async Task<IActionResult> QueryMethod() { if (!string.IsNullOrEmpty(Request.Form["startingTime"]) && !string.IsNullOrEmpty(Request.Form["EndTime"])) { var starTime = Request.Form["startingTime"].ToString(); var EndTime = Request.Form["EndTime"].ToString(); var EndTimes = EndTime + " " + "23:59:59"; var model = await SignRepository.GetListEntitiesAsync(it => it.Workstatus == 1 && it.Searchtime >= Convert.ToDateTime(starTime) &&it.Searchtime<= Convert.ToDateTime(EndTimes)); if (model.Count > 0) { //创建数据列表 DataTable dataTable = new DataTable(); dataTable.Columns.Add("会员名称", typeof(string)); dataTable.Columns.Add("开始时间", typeof(string)); dataTable.Columns.Add("结束时间", typeof(string)); dataTable.Columns.Add("打卡次数", typeof(string)); DataRow row1 = dataTable.NewRow(); row1["会员名称"] = "会员名称"; row1["开始时间"] = "开始时间"; row1["结束时间"] = "结束时间"; row1["打卡次数"] = "打卡次数"; dataTable.Rows.Add(row1); var usermodel = await UserRepository.GetListEntitiesAsync(it => true); if (usermodel.Count > 0) { foreach (var item in usermodel) { int Count = 0; foreach (var items in model) { if (items.Searchuser == item.Id) { Count += 1; } } DataRow row = dataTable.NewRow(); row["会员名称"] = item.UserName; row["开始时间"] = starTime; row["结束时间"] = EndTime; row["打卡次数"] = Count; dataTable.Rows.Add(row); } } // 创建 DataSet 对象 DataSet dataSet = new DataSet("MyDataSet"); dataSet.Tables.Add(dataTable); string folder = HostingEnvironment.WebRootPath; string year = Convert.ToDateTime(starTime).ToString("yyyy"); string moon = Convert.ToDateTime(starTime).ToString("MM"); string xlsname = year + "年" + moon + "月打卡记录生成日" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string fileName = Path.Combine(folder, "Excel", xlsname); bool result = EPPlusHelpe.ImportExcel(fileName, dataSet); if (result) { return Content("<script >parent.layer.msg('生成成功！');window.location.href ='/Export/Index';</script >", "text/html"); } } } return Content("<script >parent.layer.msg('操作失败!');history.go(-1);</script >", "text/html"); }
4.用列表来展示生成的报表
//列表 public IActionResult GetList() { try { System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(HostingEnvironment.WebRootPath + "/Excel/"); DataTable dt = new DataTable(); dt.Columns.Add("Id", typeof(string)); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("Stime", typeof(System.DateTime)); dt.Columns.Add("NameSize", typeof(string)); DataRow dr = dt.NewRow(); if (Directory.Exists(HostingEnvironment.WebRootPath + "/Excel/")) { foreach (System.IO.FileInfo fi in dir.GetFiles()) { if (fi.Extension.ToLower() == ".xlsx") { dr = dt.NewRow(); dr[0] = fi.Name; dr[1] = fi; dr[2] = fi.CreationTime; dr[3] = (fi.Length / 1024).ToString() + "KB"; dt.Rows.Add(dr); } } DataView dv = dt.DefaultView; dv.Sort = "Stime DESC"; dt = dv.ToTable(); } List<xlsModel> list = new List<xlsModel>(); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { xlsModel model = new xlsModel(); model.Id = dt.Rows[i]["Id"].ToString(); model.Name = dt.Rows[i]["Name"].ToString(); model.NameSize = dt.Rows[i]["NameSize"].ToString(); model.Stime = Convert.ToDateTime(dt.Rows[i]["Stime"].ToString()); list.Add(model); } } return Json(new { code = 0, msg = "成功", count = dt.Rows.Count, data = list }); } catch (System.Exception ex) { return Json(new { code = 200, msg = "获取数据失败！", count = 0, data = ex }); } } //删除 public IActionResult Del(string Ids) { bool ret = false; if (!string.IsNullOrEmpty(Ids)) { string[] msg = Ids.Split(','); dynamic[] id = null; if (msg.Length > 0) { id = new dynamic[msg.Length]; for (int i = 0; i < msg.Length; i++) { id[i] = HostingEnvironment.WebRootPath + "/Excel/" + msg[i]; System.IO.FileInfo file = new System.IO.FileInfo(id[i]); if (file.Exists) { file.Delete(); ret = true; } } } } if (ret) { return Json(new { Msg = "操作成功！" }); } return Json(new { Msg = "操作失败！" }); }
xlsModel类
public class xlsModel { public string Id { get; set; } public string Name { get; set; } public string NameSize { get; set; } public DateTime Stime { get; set; } }
五.效果展示
参考：https://www.cnblogs.com/cxt618/p/10451602.html
