页面如下;

 

当我点击”导出当前数据到Excel”时需要将数据导出;

按钮: <button class="layui-btn layui-btn-sm" onclick="exportExcel()">导出当前数据到Excel</button>

当我点击时触发点击事件

 //导出考生成绩信息;

        function exportExcel() {

            var AcadeneId = $("#sltAcademe").val();

            //获取查询条件;

            var formSearch = $("#formSearch").serialize();

            if (AcadeneId == 0) {

                layer.confirm("是否导出全校数据?<br>若不需要请选择筛选条件!", { icon: 3, title: "提示" }, function (index) {

                    layer.close(index);

                    window.open("@Url.Content("/SetExaimine/AchievementFill/ExportStudentAchievement")");

                })

            } else {

                window.open("@Url.Content("/SetExaimine/AchievementFill/ExportStudentAchievement")?"+formSearch)

            }

        }

当我点击通过window.open方法将会跳转到控制器中的ExportStudentAchievement中;

 public ActionResult ExportStudentAchievement(int? AcademeID,int? GradeID, int? ClassID)

        {

            //语句描述：Linq使用group by分组按UserID用户ID查询出用户最高的成绩信息

            //group by分组语法：group tbAchievement by tbAchievement.UserID into tbStudent

            //表示对tbAchievement按UserID字段进行分组，其结果命名为tbStudent

            //一旦重新命名，tbAchievement的作用域就结束了，所以，最后select时，只能select tbStudent

            var linqAchievement = from tbAchievement in myModel.PW_Achievement

                                  group tbAchievement by tbAchievement.UserID into tbStuAchievement

                                  select new

                                  {

                                      userId = tbStuAchievement.Key,

                                      Achievement = tbStuAchievement.OrderByDescending(m => m.Achievement).FirstOrDefault().Achievement,

                                      ExamNumber = tbStuAchievement.Max(m => m.ExamNumber),

                                      EligibleTypeID = tbStuAchievement.OrderByDescending(m => m.Achievement).FirstOrDefault().EligibleTypeID,

                                      AchievementId = tbStuAchievement.OrderByDescending(m => m.Achievement).FirstOrDefault().AchievementID

                                  };

            //联表查询

            var linqResult = from tbAchievement in linqAchievement

                             join tbEligibleType in myModel.SYS_EligibleType on tbAchievement.EligibleTypeID equals tbEligibleType.EligibleTypeID

                             join tbStudent in myModel.PW_Student on tbAchievement.userId equals tbStudent.UserID

                             join tbAcademe in myModel.SYS_Academe on tbStudent.AcademeID equals tbAcademe.AcademeID

                             join tbGrade in myModel.SYS_Grade on tbStudent.GradeID equals tbGrade.GradeID

                             join tbClass in myModel.SYS_Class on tbStudent.ClassID equals tbClass.ClassID

                             select new AchievementVo

                             {

                                 StudentID = tbStudent.studentID,//学生ID

                                 StudentNumber = tbStudent.StudentNumber,//学号

                                 StudentName = tbStudent.StudentName,//姓名

                                 StudentSex = tbStudent.StudentSex,//性别

                                 ClassName = tbClass.ClassName,//班级

                                 ExamNumber = tbAchievement.ExamNumber,//考试次数

                                 Achievement = tbAchievement.Achievement,//最优成绩

                                 EligibleType = tbEligibleType.EligibleTypeName,//合格类型

                                 AcademeID = tbAcademe.AcademeID,//学院ID

                                 GradeID = tbGrade.GradeID,//年级ID

                                 ClassID = tbClass.ClassID//班级ID

                             };

            if (AcademeID != null && AcademeID > 0)

            {

                linqResult = linqResult.Where(m => m.AcademeID == AcademeID);

            }

            if (GradeID != null && GradeID > 0)

            {

                linqResult = linqResult.Where(m => m.GradeID == GradeID);

            }

            if (ClassID != null && ClassID > 0)

            {

                linqResult = linqResult.Where(m => m.ClassID == ClassID);

            }

            //查询出最优成绩

             List<AchievementVo> listAchievement=linqResult.OrderByDescending(m=>m.Achievement).ToList();

           

}

从页面传递过来三个int类型的参数 int? AcademeID,int? GradeID, int? ClassID;

通过这三个参数可以从数据库中查询出需要导出的表格信息;

 #region  工作蒲;

            IWorkbook workbook = new HSSFWorkbook();

                //创建工作表;

                ISheet sheet = workbook.CreateSheet("学生成绩表");

            #region 标题行

            //3、标题行

            //3.1、创建标题行 CreateRow(行的索引值)

            IRow rowTitle = sheet.CreateRow(0);

            //3.2、创建单元格

            ICell cell0 = rowTitle.CreateCell(0);

            //3.3、设置单元格内容

            cell0.SetCellValue("考生成绩表");

            //3.4、设置标题行跨列显示（合并指定单元格）

            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 6));

            //3.5、设置标题行样式

            ICellStyle cellStyle_Title = workbook.CreateCellStyle();

            cellStyle_Title.Alignment = HorizontalAlignment.CENTER;

            cellStyle_Title.VerticalAlignment = VerticalAlignment.CENTER;

            IFont font_Title = workbook.CreateFont();

            font_Title.Color = NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index;

            font_Title.Boldweight = (short)FontBoldWeight.BOLD;//加粗

            cellStyle_Title.SetFont(font_Title);

            cellStyle_Title.BorderLeft = CellBorderType.THIN;

            cellStyle_Title.BorderTop = CellBorderType.THIN;

            cellStyle_Title.BorderRight = CellBorderType.THIN;

            cellStyle_Title.BorderBottom = CellBorderType.THIN;

            cell0.CellStyle = cellStyle_Title;

            #endregion

          再通过HSSFWorkbook方法创建工作蒲与标题行

          #region 表头行

            //4、表头行

            //4、创建表头行并设置字段

            IRow rowHeader = sheet.CreateRow(1);

            rowHeader.CreateCell(0).SetCellValue("序号");

            rowHeader.CreateCell(1).SetCellValue("学号");

            rowHeader.CreateCell(2).SetCellValue("姓名");

            rowHeader.CreateCell(3).SetCellValue("性别");

            rowHeader.CreateCell(4).SetCellValue("班级");

            rowHeader.CreateCell(5).SetCellValue("成绩");

            rowHeader.CreateCell(6).SetCellValue("合格类型");

            //4.2、设置表头行样式

            ICellStyle cellStyle_header = workbook.CreateCellStyle();//声明样式

            cellStyle_header.Alignment = HorizontalAlignment.CENTER;//水平居中

            cellStyle_header.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中                                                                                            

            cellStyle_header.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置背景填充效果

            cellStyle_header.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.AQUA.index;//设置背景填充颜色

            //设置边框线为实线

            cellStyle_header.BorderLeft = CellBorderType.THIN;

            cellStyle_header.BorderTop = CellBorderType.THIN;

            cellStyle_header.BorderRight = CellBorderType.THIN;

            cellStyle_header.BorderBottom = CellBorderType.THIN;

            //设置字体

            IFont font_header = workbook.CreateFont();//声明字体

            font_Title.Boldweight = (short)FontBoldWeight.BOLD;//加粗

            font_header.FontHeightInPoints = 10;//字体大小

            cellStyle_header.SetFont(font_header);//加入单元格

            // 给 rowHeader 单元格设置样式 循环

            for (int i = 0; i < rowHeader.Cells.Count; i++)

            {

                rowHeader.GetCell(i).CellStyle = cellStyle_header;

            }

            #endregion

            #region 数据行

            //5、遍历数据

            //5.1、创建数据单元格的样式

            ICellStyle cellStyle_value = workbook.CreateCellStyle();

            cellStyle_value.Alignment = HorizontalAlignment.CENTER;//水平居中

            cellStyle_value.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中

            //四周边框线

            cellStyle_value.BorderLeft = CellBorderType.THIN;

            cellStyle_value.BorderTop = CellBorderType.THIN;

            cellStyle_value.BorderRight = CellBorderType.THIN;

            cellStyle_value.BorderBottom = CellBorderType.THIN;

            //5.2、遍历数据

            for (int i = 0; i < listAchievement.Count; i++)

            {

                //创建行

                IRow row = sheet.CreateRow(2 + i);//标题和表头已经占了两行

                row.HeightInPoints = 22;//设置行高

                //创建列，并设置值

                row.CreateCell(0).SetCellValue(i + 1);//序号

                row.CreateCell(1).SetCellValue(listAchievement[i].StudentNumber);

                row.CreateCell(2).SetCellValue(listAchievement[i].StudentName);

                row.CreateCell(3).SetCellValue(listAchievement[i].StudentSex);

                row.CreateCell(4).SetCellValue(listAchievement[i].ClassName);

                row.CreateCell(5).SetCellValue((double)listAchievement[i].Achievement);

                row.CreateCell(6).SetCellValue(listAchievement[i].EligibleType);

                //给每个单元格添加样式

                for (int j = 0; j < row.Cells.Count; j++)

                {

                    row.GetCell(j).CellStyle = cellStyle_value;

                }

            }

            #endregion

            for(int i = 0; i < sheet.GetRow(1).Cells.Count; i++)

            {

                sheet.AutoSizeColumn(i);//这列自动适应最长数据单元格的宽度

                sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) * 12 / 10);//设置列的宽度为自适应宽度之后的1.2倍

            }

            //7、把创建好的Excel输出到浏览器

            string fileName = "考生成绩" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";

            //把Excel转化为流输出

            MemoryStream BookStream = new MemoryStream();//定义流

            workbook.Write(BookStream);//将工作薄写入流

            BookStream.Seek(0, SeekOrigin.Begin);//输出之前调用Seek（偏移量，游标位置）

            return File(BookStream, "application/vnd.ms-excel", fileName); // 文件，文件类型，文件名称

            #endregion

        }