javaweb项目上传excel并将表中内容存入数据库

ecxel表格内容

数据库学生表字段（结果图）

1.控制层代码

@RestController
@RequestMapping("/tcvl/student")
@Api(value = "学生表", tags = "学生表接口")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/upload")
    @ApiOperation(notes = "上传学生表",value = "传入Excel")
    public Re upload(@RequestParam(value="file",required = false) MultipartFile file){
        try{
            studentService.readExcel(file);
        }catch (Exception e){
            return Re.error().message("上传失败").data("status",false);
        }
        return Re.ok().message("新增成功").data("status",true);
    }

}

 

2.服务层代码

public interface StudentService extends IService<Student> {

    /**
     * 批量上传学生信息
     * @param file
     */
    void readExcel(MultipartFile file);

}

3.业务层代码

@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Override
    public void readExcel(MultipartFile file) {
        //创建处理EXCEL的类
        ReadExcelUtil readExcelUtil = new ReadExcelUtil();
        //解析excel，获取上传的事件单
        List<Student> studentList = readExcelUtil.getExcelStudentInfo(file);
        for (Student student : studentList){
            Student student1 = new Student();
            student1.setStudentName(student.getStudentName());
            student1.setStudentNumber(student.getStudentNumber());
            student1.setClassnum(student.getClassnum());
            student1.setGrade(student.getGrade());
            student1.setState(student.getState());
            student1.setProfession(student.getProfession());
            student1.setAcademy(student.getAcademy());
            student1.setCreateTime(new Date());
            student1.setIsDeleted(0);
            try{
                studentMapper.insert(student1);
            }catch (Exception e){
                throw new UbwException("添加失败",500);
            }
        }
    }
}

 4.上传文件的工具类代码

/**
 * 将excel数据进行解析
 */
public class ReadExcelUtil {

    // 总行数
    private int totalRows = 0;
    // 总列数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;

    public ReadExcelUtil(){
    }

    public int getTotalRows() {
        return totalRows;
    }

    public int getTotalCells() {
        return totalCells;
    }

    public String getErrorMsg() {
        return errorMsg;
    }

    /**
     * 获取学生信息
     * @param mFile
     * @return
     */
    public List<Student> getExcelStudentInfo(MultipartFile mFile){
        List<Student> studentList = null;
        // 获取文件名
        String fileName = mFile.getOriginalFilename();
        try {
            // 验证文件名是否合格
            if (!validateExcel(fileName)) {
                return null;
            }
            // 根据文件名判断文件是2003版本还是2007版本
            boolean isExcel2003 = true;
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            studentList = createExcel(mFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return studentList;
    }

    /**
     * 根据excel里面的内容读取信息
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     */
    public List<Student> createExcel(InputStream is, boolean isExcel2003){
        List<Student> studentList = null;
        try {
            Workbook wb = null;
            // 当excel是2003时,创建excel2003
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                // 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            // 读取Excel里面客户的信息
            studentList = readExcelValue(wb);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return studentList;
    }

    /**
     * 读取Excel里面的信息
     * @param wb
     * @return
     */
    private List<Student> readExcelValue(Workbook wb){
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Student> studentList = new ArrayList<Student>();
        // 循环Excel行数
        for(int r = 1;r < totalRows;r++){
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            Student student = new Student();
            // 循环Excel的列
            for(int c = 0;c < this.totalCells;c++){
                Cell cell = row.getCell(c);
                if (null != cell){
                    // 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
                    switch (c){
                        case 0:
                            student.setStudentName(cell.getStringCellValue());
                            break;
                        case 1:
                            if(cell.getCellType() == CellType.NUMERIC){
                                String StudentNumber = String.valueOf(cell.getNumericCellValue());
                                student.setStudentNumber(StudentNumber.substring(0,StudentNumber.length()-2 > 0 ? StudentNumber.length()-2 : 1));
                            }
                            break;
                        case 2:
                            if(cell.getCellType() == CellType.NUMERIC){
                                String Classnum = String.valueOf(cell.getNumericCellValue());
                                student.setClassnum(Integer.valueOf(Classnum.substring(0,Classnum.length()-2 > 0 ? Classnum.length()-2 : 1)));
                            }
                            break;
                        case 3:
                            if(cell.getCellType() == CellType.NUMERIC){
                                String Grade = String.valueOf(cell.getNumericCellValue());
                                student.setGrade(Integer.valueOf(Grade.substring(0,Grade.length()-2 > 0 ? Grade.length()-2 : 1)));
                            }
                            break;
                        case 4:
                            if(cell.getCellType() == CellType.NUMERIC){
                                String State = String.valueOf(cell.getNumericCellValue());
                                student.setState(Integer.valueOf(State.substring(0,State.length()-2 > 0 ? State.length()-2 : 1)));
                            }
                            break;
                        case 5:
                            student.setProfession(cell.getStringCellValue());
                            break;
                        case 6:
                            student.setAcademy(cell.getStringCellValue());
                            break;
                    }
                }
            }
            // 添加到list
            studentList.add(student);
        }
        return studentList;
    }

    /**
     * 验证EXCEL文件
     * @param fileName
     * @return
     */
    public boolean validateExcel(String fileName){
        if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

}