项目实训记录（5-7周）

前言

因为之前对springboot和vue不熟悉，所以先从比较简单的功能开始开发。

---

一、为教师展现自己都教哪些课

这里前端是写了一个teacher_view下的classes.vue文件，后端涉及到配套的springboot的三层。

二、查看某一个课程的选课名单

三、教师创建一个课的时候导入选课名单

这里的关键是要处理excel文件成一条一条的数据并存进数据库。

package project.ourcourseassistant.util;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import project.ourcourseassistant.entity.take;
import project.ourcourseassistant.entity.testscore;
import javax.servlet.http.HttpServletResponse;
import java.awt.desktop.SystemEventListener;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class ExcelUtil {


    /**
     * 下载带有表头的空白Excel模板文件
     *
     * @param clazz
     * @param fileName
     * @param response
     * @throws IOException
     */
    public static void exportExcel(Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams();
        exportParams.setCreateHeadRows(true);
        defaultExport(new ArrayList<>(), clazz, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> clazz, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, clazz, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, clazz, fileName, response, new ExportParams());
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> clazz, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, clazz, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> clazz, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, list);
        if (workbook != null) downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> clazz) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(new File(filePath), clazz, params);
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> clazz) throws Exception {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
    }

    /**
     * 将excel文件内容转换成User集合
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static List<take> getUserList(MultipartFile file,String cid) throws IOException {
        String classid=cid;
        String fileName = file.getOriginalFilename();
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
        take take;
        List<take> list = new LinkedList<>();
        int sheetNumber = wb.getNumberOfSheets();//这个应该是有多少条
        for (int i = 0; i < sheetNumber; i++) {
            Sheet sheet = wb.getSheetAt(i);
            int rowNumber = sheet.getLastRowNum();
            for (int r = 0; r < rowNumber; r++) {
                Row row = sheet.getRow(r);
                if (row != null) {


                    String sid = getCellValue(row.getCell(0));
                    //Integer age = (int)row.getCell(2).getNumericCellValue();
                    String rname = getCellValue(row.getCell(1));
                    String studentclass = getCellValue(row.getCell(2));
                    take = new take(classid, sid, rname, studentclass);
                    System.out.println("55555555555555555");
                    System.out.println(classid);
                    System.out.println(sid);
                    System.out.println(rname);
                    System.out.println(studentclass);
                    System.out.println("第几个" + r);
                    System.out.println("55555555555555555");


                    list.add(take);
                }
            }
        }
        return list;
    }

    /**
     * 将excel文件内容转换成User集合
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static List<testscore> setScore(MultipartFile file,String cid) throws IOException {
        String classid=cid;
        String fileName = file.getOriginalFilename();
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
        testscore testscore;
        List<testscore> list = new LinkedList<>();
        int sheetNumber = wb.getNumberOfSheets();//这个应该是有多少条
        for (int i = 0; i < sheetNumber; i++) {
            Sheet sheet = wb.getSheetAt(i);
            int rowNumber = sheet.getLastRowNum();
            for (int r = 0; r < rowNumber; r++) {
                System.out.println("%%%%%%%%%%%%%%%%%%555");
                Row row = sheet.getRow(r);
                if (row != null) {


                    String sid = getCellValue(row.getCell(0));
                    //Integer age = (int)row.getCell(2).getNumericCellValue();
                    String getScoreTemp = getCellValue(row.getCell(1));
                    //下面是将字符串转成float
                    float score = Float.parseFloat(getScoreTemp);

                    testscore = new testscore(cid, sid, score);
                    System.out.println("@@@@@@@2222");
                    System.out.println(classid);
                    System.out.println(sid);
                    System.out.println(score);

                    System.out.println("第几个" + r);
                    System.out.println("@@@@@@@2222");


                    list.add(testscore);
                }
            }
        }
        return list;
    }



    private static String getCellValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    }

}