ASP.NET MVC页面批量导出数据

ASP.NET MVC页面批量导出数据

在ASP.NET MVC项目中很多时候我们在查询出数据库中的数据显示在页面时、需要对页面数据进行导出，那么怎么进行页面数据导出呢、下面我们进行了解页面数据导出：
1、 先创建Excel对象、创建Excel对象工作簿
2、 接收页面传过来的点击选择行的数据库关键值
3、 查询出数据库相关表的数据然后对关键值进行筛选
4、 给导出的Excel设置表头
5、 给每行添对应加数据
6、 设置导出的文件的名称
7、 把Excel转化为文件流，输出

对页面传过来的数据的对应值提交导控制器：
视图：

 function ImportStuExcel() {
            var AcademeID = $("#sltAcademe").val();
            var GradeID = $("#sltGrade").val();
            var ClassID = $("#sltClass").val();
            if ((GradeID == "" || GradeID == null)) {
                GradeID = 0;
            }
            if ((ClassID == "" || ClassID == null)) {
                ClassID = 0;
            }
            if (AcademeID == 0) {
                layer.confirm("是否导出全校数据？若不需要请筛选数据", { icon: 3, title: '提示' }, function (index) {
                    layer.close(index);
                    window.open("ExportExamineeByID?AcademeID=" + AcademeID + "&GradeID=" + GradeID + "&ClassID=" + ClassID);
                });
            } else {
                window.open("ExportExamineeByID?AcademeID=" + AcademeID + "&GradeID=" + GradeID + "&ClassID=" + ClassID);
         }
        }

控制器：

public ActionResult ExportExamineeByID(int AcademeID, int GradeID, int ClassID)
        {
            try
            {
                //创建Excel对象
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();//创建Excel对象工作簿


                #region 查询考生信息
                var listStudent = from tbStudent in myModel.PW_Student
                                  join tbUser in myModel.PW_User on tbStudent.UserID equals tbUser.UserID
                                  join tbClass in myModel.SYS_Class on tbStudent.ClassID equals tbClass.ClassID
                                  join tbSpecialty in myModel.SYS_Specialty on tbStudent.SpecialtyID equals tbSpecialty.SpecialtyID
                                  join tbAcademe in myModel.SYS_Academe on tbStudent.AcademeID equals tbAcademe.AcademeID
                                  join tbGrade in myModel.SYS_Grade on tbStudent.GradeID equals tbGrade.GradeID
                                  where tbUser.ToVoidNo == true
                                  select new StudentUserInfor
                                  {
                                      studentID = tbStudent.studentID,//学生ID
                                      StudentNumber = tbStudent.StudentNumber,//学号
                                      StudentName = tbStudent.StudentName,//学生姓名
                                      StudentIDNum = tbStudent.StudentIDNum,//学生身份证
                                      StudentSex = tbStudent.StudentSex,//学生性别
                                      AcademeName = tbAcademe.AcademeName,//学院
                                      SpecialtyName = tbSpecialty.SpecialtyName,//专业
                                      GradeName = tbGrade.GradeName,//年级
                                      ClassName = tbClass.ClassName,//班级
                                      UserNuber = tbUser.UserNuber,//用户账号
                                      AcademeID = tbStudent.AcademeID,//学院ID
                                      GradeID = tbStudent.GradeID,//年级ID
                                      ClassID = tbStudent.ClassID//班级ID
                                  };
                if (AcademeID > 0)
                {
                    listStudent = listStudent.Where(m => m.AcademeID == AcademeID);
                }
                if (GradeID > 0)
                {
                    listStudent = listStudent.Where(m => m.GradeID == GradeID);
                }
                if (ClassID > 0)
                {
                    listStudent = listStudent.Where(m => m.ClassID == ClassID);
                }
                List<StudentUserInfor> listStudents = listStudent.ToList();
                #endregion  
                #region 给导出的Excel设置表头
                NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(0);//给sheet添加第一行的头部标题
                row1.CreateCell(0).SetCellValue("学生ID");
                row1.CreateCell(1).SetCellValue("学号");
                row1.CreateCell(2).SetCellValue("姓名");
                row1.CreateCell(3).SetCellValue("身份证号");
                row1.CreateCell(4).SetCellValue("性别"); 
                row1.CreateCell(5).SetCellValue("学院");
                row1.CreateCell(6).SetCellValue("专业");
                row1.CreateCell(7).SetCellValue("年级");
                row1.CreateCell(8).SetCellValue("班级");
                row1.CreateCell(9).SetCellValue("账号");
                sheet.SetColumnWidth(3, 20 * 256);//设置身份证列的宽度
                sheet.SetColumnWidth(5, 20 * 256);//设置学院列的宽度
                sheet.SetColumnWidth(6, 20 * 256);//设置专业列的宽度
                #endregion
                #region 给sheet的每行添加数据
                for (int i = 0; i < listStudents.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1);//给sheet添加一行
                    row.CreateCell(0).SetCellValue(listStudents[i].studentID.ToString());
                    if (listStudents[i].StudentNumber == null)
                    {
                        row.CreateCell(1).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(1).SetCellValue(listStudents[i].StudentNumber);
                    }
                    if (listStudents[i].StudentName == null)
                    {
                        row.CreateCell(2).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(2).SetCellValue(listStudents[i].StudentName);
                    }
                    if (listStudents[i].StudentIDNum == null)
                    {
                        row.CreateCell(3).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(3).SetCellValue(listStudents[i].StudentIDNum);
                    }
                    if (listStudents[i].StudentSex == null)
                    {
                        row.CreateCell(4).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(4).SetCellValue(listStudents[i].StudentSex);
                    }
                    if (listStudents[i].AcademeName == null)
                    {
                        row.CreateCell(5).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(5).SetCellValue(listStudents[i].AcademeName);
                    }
                    if (listStudents[i].SpecialtyName == null)
                    {
                        row.CreateCell(6).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(6).SetCellValue(listStudents[i].SpecialtyName);
                    }
                    if (listStudents[i].GradeName == null)
                    {
                        row.CreateCell(7).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(7).SetCellValue(listStudents[i].GradeName);
                    }
                    if (listStudents[i].ClassName == null)
                    {
                        row.CreateCell(8).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(8).SetCellValue(listStudents[i].ClassName);
                    }
                    if (listStudents[i].UserNuber == null)
                    {
                        row.CreateCell(9).SetCellValue(0);
                    }
                    else
                    {
                        row.CreateCell(9).SetCellValue(listStudents[i].UserNuber);
                    }
                }
                #endregion
                //输出的文件名称
                string fileName = "考生信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
                //把Excel转化为文件流，输出
                MemoryStream BookStream = new MemoryStream();//定义文件流
                book.Write(BookStream);//将工作薄写入文件流
                BookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek（偏移量，游标位置）方法：获取文件流的长度
                return File(BookStream, "application/vnd.ms-excel", fileName); // 文件类型/文件名称/
            }
            catch (Exception)
            {
                return View("");
            }
        }