学生能力提升平台绑定学生信息

2021SC@SDUSC

学生能力提升平台绑定学生信息

excel的生成

    /**
     * @api {GET} /bind/allStudentsBindExcel 绑定信息下载
     * @apiVersion 1.0.0
     * @apiGroup BindController
     * @apiName allStudentsBindExcel
     * @apiDescription 获取所有学生绑定信息的excel(数据量大接口可能会慢)
     * @apiPermission ADMIN
     * @apiSuccess (响应结果) {Number} code
     * @apiSuccess (响应结果) {String} message
     * @apiSuccess (响应结果) {Object} data
     * @apiSuccessExample 响应结果示例
     * data.xlsx
     */
    @GetMapping("allStudentsBindExcel")
    @PreAuthorize("hasRole('ROLE_ADMIN')")
    public ResultEntity allStudentsBindExcel(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("data", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), StudentBind.class).sheet("sheet1")
                .doWrite(bindService.allStudentsBindNoPager());
        return ResultEntity.success();
    }

通过EasyExcel生成的数据，新建一个Excel表格，因为需要下载所以设置Header中的Content，确定下载文件的内容

csv的格式写入

@RestController
@RequestMapping("csv")
public class CsvController {
    @Autowired
    private SpeechService speechService;
    @Autowired
    private TokenService tokenService;

    @RequestMapping("speech")
    String speech(@RequestHeader("Token") String token) throws IOException {
        Integer userId = tokenService.getUserId(token);
        if (userId == null) {
            return "";
        }
        List<Speech> speechList = speechService.userCanSeeSpeech(userId);
        JsonNode jsonTree = new ObjectMapper().readTree(JSONObject.toJSONString(speechList));
        CsvSchema.Builder csvSchemaBuilder = CsvSchema.builder();
        JsonNode firstObject = jsonTree.elements().next();
        firstObject.fieldNames().forEachRemaining(csvSchemaBuilder::addColumn);
        CsvSchema csvSchema = csvSchemaBuilder.build().withHeader();
        CsvMapper csvMapper = new CsvMapper();
        Writer writer = new StringWriter();
        csvMapper.writerFor(JsonNode.class)
                .with(csvSchema)
                .writeValue(writer, jsonTree);
        return writer.toString();
    }
}

这里可以采用一种更为简便的方式，我写在下面

    /**
     * @api {POST} /bind/uploadStudentExcel 上传学生信息
     * @apiVersion 1.0.0
     * @apiGroup BindController
     * @apiName uploadStudentExcel
     * @apiDescription 上传学生信息excel
     * @apiPermission ADMIN
     * @apiParam (请求参数) {MultipartFile} file
     * @apiParamExample 请求参数示例
     * file=file.xlsx
     * @apiSuccess (响应结果) {Stream} data.xlsx
     * @apiSuccessExample {Stream} 响应结果示例
     * data.xlsx
     */
    @PostMapping("uploadStudentExcel")
    @PreAuthorize("hasRole('ROLE_ADMIN')")
    public ResultEntity uploadStudentExcel(HttpServletResponse response, MultipartFile file) throws IOException {
        if (file == null || file.isEmpty()) {
            response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
            return ResultEntity.error(StatusCode.PARAM_NOT_VALID);
        }
        List<StudentBind> list = EasyExcel.read(file.getInputStream()).head(StudentBind.class).sheet().doReadSync();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("data", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), StudentBind.class).sheet("sheet1")
                .doWrite(bindService.generateBindCode(list));
        return ResultEntity.success();
    }

通过写入service，来实现数据excel到数据库的转化

    public List<StudentBind> allStudentsBindNoPager() {
        return bindMapper.selectStudentBindNoPage();
    }
    public List<StudentBind> searchStudentBind(String name, String stuNum) {
        return bindMapper.searchByNameAndStuNum("%" + name + "%", "%" + stuNum + "%");
    }

    public StudentBind selectStudentBindByStuNum(String stuNum) {
        return bindMapper.selectStudentBindByStuNum(stuNum);
    }

通过自动字符串#{%%}拼接，防止可能的sql注入的风险