学生信息管理系统（springboot框架.zip

package com.mzl.studentmanagesystem.controller; import com.mzl.studentmanagesystem.entity.Score; import com.mzl.studentmanagesystem.entity.ScoreStats; import com.mzl.studentmanagesystem.entity.Student; import com.mzl.studentmanagesystem.service.CourseService; import com.mzl.studentmanagesystem.service.ScoreService; import com.mzl.studentmanagesystem.service.StudentService; import com.mzl.studentmanagesystem.util.AjaxResult; import com.mzl.studentmanagesystem.util.Const; import com.mzl.studentmanagesystem.util.PageBean; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.annotations.CacheNamespace; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import javax.swing.plaf.multi.MultiFileChooserUI; import java.io.InputStream; import java.net.URL; import java.net.URLEncoder; import java.nio.channels.MulticastChannel; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @ClassName : ScoreController * @Description: 成绩控制器 * @Author: mzl * @CreateDate: 2020/8/3 12:58 * @Version: 1.0 */ @Slf4j @Controller @RequestMapping("/score") public class ScoreController { //注入依赖 @Autowired private ScoreService scoreService; @Autowired private StudentService studentService; @Autowired private CourseService courseService; /** * 跳转到成绩列表 * @return */ @GetMapping("/score_list") public String scoreList(){ return "score/scoreList"; } /** * 异步加载成绩列表 * @param page * @param rows * @param studentid * @param courseid * @param from * @param session * @return */ @PostMapping("/getScoreList") @ResponseBody public Object getScoreList(@RequestParam(value = "page", defaultValue = "1")Integer page, @RequestParam(value = "rows", defaultValue = "100")Integer rows, @RequestParam(value = "studentid", defaultValue = "0")String studentid, @RequestParam(value = "courseid", defaultValue = "0")String courseid, String from, HttpSession session){ //封装参数 Map<String, Object> paramMap = new HashMap<>(); paramMap.put("pageno", page); paramMap.put("pagesize", rows); if (!studentid.equals("0")){ paramMap.put("studentid", studentid); } if (!courseid.equals("0")){ paramMap.put("courseid", courseid); } //判断是学生还是老师权限 Student student = (Student) session.getAttribute(Const.STUDENT); if(!StringUtils.isEmpty(student)){ //学生权限只能查询自己的信息 paramMap.put("studentid", student.getId()); } //查询学生成绩列表 PageBean<Score> pageBean = scoreService.queryPage(paramMap); if (!StringUtils.isEmpty(from)){ return pageBean.getDatas(); }else { Map<String, Object> result = new HashMap<>(); result.put("total", pageBean.getTotalsize()); result.put("rows", pageBean.getDatas()); return result; } } /** * 导入Excel表，并把数据存入数据库 * @param importScore * @param response */ @PostMapping("/importScore") @ResponseBody public void importScore(@RequestParam("importScore")MultipartFile importScore, HttpServletResponse response){ response.setCharacterEncoding("UTF-8"); try { InputStream inputStream = importScore.getInputStream(); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream); XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0); //用来标记添加进去数据库的记录数 int count = 0; String errorMsg = ""; for (int rowNum = 0; rowNum <= sheetAt.getLastRowNum(); rowNum++) { System.out.println("oooo"); XSSFRow row = sheetAt.getRow(rowNum);//获取第rowNum行 //第0列为学生姓名 System.out.println("iii"); XSSFCell cell = row.getCell(0); //获取第rowNum行的第0列。即坐标（rowNum, 0） //设置cell类型，每一次获取cellValues都要另外重新设置一次cell类型 cell.setCellType(Cell.CELL_TYPE_STRING); System.out.println(cell.getStringCellValue()); System.out.println("ppp"); if (cell == null){ errorMsg += "第" + rowNum + "行学生缺失！\n"; continue; } //第1列为课程名 cell = row.getCell(1); //设置cell类型，每一次获取cellValues都要另外重新设置一次cell类型 cell.setCellType(Cell.CELL_TYPE_STRING); System.out.println(cell.getStringCellValue()); if (cell == null){ errorMsg += "第" + rowNum + "行课程缺失！\n"; continue; } System.out.println("kkkk"); //第2列为分数 cell = row.getCell(2); if (cell == null){ errorMsg += "第" + rowNum + "行成绩缺失！\n"; continue; } cell.setCellType(Cell.CELL_TYPE_NUMERIC); double scoreValue = cell.getNumericCellValue(); System.out.println(scoreValue); //第3列为remark cell = row.getCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); String remark = null; if (cell != null){ remark = cell.getStringCellValue(); } System.out.println(remark); //将学生课程转换为id，存入数据库 //1.首先获取对应的id cell = row.getCell(0); cell.setCellType(Cell.CELL_TYPE_STRING); int studentId = studentService.findByName(cell.getStringCellValue()); cell = row.getCell(1); cell.setCellType(Cell.CELL_TYPE_STRING); int courseId = courseService.findByName(cell.getStringCellValue()); //2.判断是否已存在数据库中 Score score = new Score(); score.setCourseId(courseId); score.setStudentId(studentId); score.setScore(scoreValue); score.setRemark(remark); if (!scoreService.isScore(score)){ //3.为空，则可以添加进去 int num = scoreService.addScore(score); if(num > 0){ count++; } }else { errorMsg += "第" + rowNum + "行已录入，不重复录入！\n"; } } errorMsg += "成功录入" + count + "条成绩信息！"; response.getWriter().write("<div id = 'message'>" + errorMsg + "</div>"); } catch (Exception e) { e.printStackTrace(); try { response.getWriter().write("<div id = 'message'>上传错误</div>"); } catch (Exception ex) { ex.printStackTrace(); } } } /** * 导出Excel表格