.net mvc将数据库数据导出到Excel表

时间:2022-02-15 09:19:51

第一步:NuGet下载EPPlus

.net mvc将数据库数据导出到Excel表

 

第二步:控制器代码,在名为CorrectHomeWork的控制器创建方法ExcelExport,头部不需要[HttpPost]

  //将该课程中的该作业命题的全部学生作业导入Excel表
        public ActionResult ExcelExport()
        {
            string Task_Title = Session["Task_Title"].ToString();
            string L_Name = Session["L_Name"].ToString();
            string C_Name = Session["C_Name"].ToString();
            //指定项目存放的路径
            string sWebRootFolder = AppDomain.CurrentDomain.BaseDirectory + "/DownLoadExcelexport";
            //指定项目名字
            //string sFileName = $"{Guid.NewGuid()}.xlsx";
            string sFileName = C_Name+ L_Name+ Task_Title+"作业成绩表"+ $"{Guid.NewGuid()}.xlsx";
            //把项目名加到指定存放的路径
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (ExcelPackage package = new ExcelPackage(file))
            {
                //添加worksheet的名字
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("作业成绩单");
                //添加表头名字
                worksheet.Cells[1, 1].Value = "学号";
                worksheet.Cells[1, 2].Value = "班级";
                worksheet.Cells[1, 3].Value = "作业命题";
                worksheet.Cells[1, 4].Value = "分数";
                worksheet.Cells[1, 5].Value = "首次提交日期";
                worksheet.Cells[1, 6].Value = "最后修改日期";
                worksheet.Cells[1, 7].Value = "教师评价";
                //添加值
                var rowNum = 2; // rowNum 1 is head
                var Homework = homeWorkService.Query(s => s.Task_Title == Task_Title && s.L_Name == L_Name);
                //var Homework = from s in con.Homework select s;//循环数据库输出数据
                Homework = Homework.OrderBy(s => s.S_Number);
                foreach (var message in Homework)
                {
                    worksheet.Cells["A" + rowNum].Value = message.S_Number;
                    worksheet.Cells["B" + rowNum].Value = message.C_Name;
                    worksheet.Cells["C" + rowNum].Value = message.Task_Title;
                    worksheet.Cells["D" + rowNum].Value = message.HW_Grade;
                    worksheet.Cells["E" + rowNum].Value = message.HW_SubmitDate.ToString();
                    worksheet.Cells["F" + rowNum].Value = message.HW_UpdataDate.ToString();
                    worksheet.Cells["G" + rowNum].Value = message.HW_Comment;
                    rowNum++;
                }
                package.Save();
            }
            //获取到存在项目中文件的路径
            var fileUrl = Server.MapPath("~/DownLoadExcelexport/" + sFileName);
            //application/ms-excel为内部固定的,
            return File(fileUrl, "application/ms-excel", sFileName);
        }

 

第三步:在视图<a>标签调用控制器方法:

<span style="font-size:18px;color:blue"><a href="/CorrectHomeWork/ExcelExport">Excel表导出作业</a></span>