基于springboot的学生成绩管理系统源码

package sut.edu.zyp.dormitory.manage.controller; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.TypeReference; import jxl.Sheet; import jxl.Workbook; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.read.biff.BiffException; import jxl.write.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import sut.edu.zyp.dormitory.manage.dto.AbstractExcelExportRequest; import sut.edu.zyp.dormitory.manage.dto.ExcelImportResponse; import sut.edu.zyp.dormitory.manage.dto.RestDataExcelExportRequest; import sut.edu.zyp.dormitory.manage.entity.*; import sut.edu.zyp.dormitory.manage.enums.ResponseCodeEnum; import sut.edu.zyp.dormitory.manage.repository.*; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.*; /** * Excel批处理导入和导出操作 * * @author zyp * @version 0.0.1 * @since 0.0.1 */ @Controller public class ExcelIOController { private static final Logger logger = LoggerFactory.getLogger(ExcelIOController.class); @Autowired private AdminRepository adminRepository; @Autowired private BuildingRepository buildingRepository; @Autowired private DormitoryManagerRepository dormitoryManagerRepository; @Autowired private DormitoryRepository dormitoryRepository; @Autowired private LiveRepository liveRepository; @Autowired private StudentRepository studentRepository; /** * 通过http的get请求，返回指定table对应的数据 */ @RequestMapping(method = RequestMethod.GET, path = "/excel/export") public void restDataExcelExport(@RequestParam(name = "table", required = true) String table, HttpServletResponse response) { //获取数据 List<Map<String, String>> data = getDataByUrl("/" + table); RestDataExcelExportRequest restDataExcelExportRequest = GenByUrl("/" + table); //数据写入Excel writeData2Excel(restDataExcelExportRequest, response, data); } /** * 将数据写入excel，并通过httpResponse下载到本地 * * @param request 写入excel的请求，包含对应元数据等 * @param response http返回，需要将excel文件数据流，回写到response，返回给前端 * @param data 需要写入excel的数据，List为每行数据，Map为每列数据 */ private void writeData2Excel(AbstractExcelExportRequest request, HttpServletResponse response, List<Map<String, String>> data) { try { // 获取输出流 OutputStream os = response.getOutputStream(); // 清空输出流 response.reset(); // 设置输出字符集 response.setCharacterEncoding("UTF-8"); // 设定输出文件头 response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(request.getExcelName() + ".xls", "UTF-8")); // 定义输出类型 response.setContentType("application/msexcel"); // 创建Excel WritableWorkbook workbook = Workbook.createWorkbook(os); WritableSheet sheet = workbook.createSheet(request.getSheetName(), 0); //标题样式 WritableFont fontTitle = new WritableFont(WritableFont.createFont("微软雅黑"), 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat formatTitle = new WritableCellFormat(fontTitle); formatTitle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); //标题数据，写入各列（各字段）的数据 for (int i = 0; i < request.getColumnNames().size(); i++) { //初始化列宽25 sheet.setColumnView(i, 25); String eachTitleStr = String.valueOf(request.getColumnNames().get(i)); Label eachTitle = new Label(i, 0, eachTitleStr, formatTitle); sheet.addCell(eachTitle); //logger.info("{}", eachTitleStr); } //数据样式 WritableFont fontData = new WritableFont(WritableFont.createFont("微软雅黑"), 12, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat formatData = new WritableCellFormat(fontData); formatData.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //将数据写入Excel for (int row = 0; row < data.size(); row++) { for (int column = 0; column < request.getColumns().size(); column++) { try { String eachDataStr = String.valueOf(data.get(row).get(request.getColumns().get(column))); if ("createTime".equals(request.getColumns().get(column)) || "updateTime".equals(request.getColumns().get(column))) { long timestamp = Long.parseLong(eachDataStr); eachDataStr = simpleDateFormat.format(new Date(timestamp)); } Label eachData = new Label(column, row + 1, eachDataStr, formatData); sheet.addCell(eachData); //logger.info("{}", eachDataStr); } catch (Exception e) { logger.error("", e); continue; } } } workbook.write(); workbook.close(); os.close(); } catch (IOException e) { logger.error("", e); } catch (WriteException e) { logger.error("", e); } } /** * 根据rest方式，获取数据，这种是需要查询指定表对应的数据库数据 * * @param url 请求的url * @return 所需数据 */ private List<Map<String, String>> getDataByUrl(String url) { List<Map<String, String>> data = new ArrayList<>(); if ("/admin".equals(url)) { Iterable<AdminEntity> adminEntities = adminRepository.findAll(); data = addDataToResult(adminEntities.iterator(), data); } else if ("/building".equals(url)) { Iterable<BuildingEntity> buildingEntities = buildingRepository.findAll(); data = addDataToResult(buildingEntities.iterator(), data); } else if ("/dormitoryManager".equals(url)) { Iterable<DormitoryManagerEntity> dormitoryManagerEntities = dormitoryManagerRepository.findAll(); data = addDataToResult(dormitoryManagerEntities.iterator(), data); } else if ("/dormitory".equals(url)) { Iterable<DormitoryEntity> dormitoryEntities = dormitoryRepository.findAll(); data = addDataToResult(dormitoryEntities.iterator(), data); } else if ("/live".equals(url)) { Iterable<LiveEntity> liveEntities = liveRepository.findAll(); data = addDataToResult(liveEntities.iterator(), data); } else if ("/student".equals(url)) { Iterable<StudentEntity> studentEntities = studentRepository.findAll(); data = addDataToResult(studentEntities.iterator(), data); } else { Map<String, String> defaultRow