开始准备
搭建一个SpringBoot+MyBatisPlus 的项目工程。

1.SpringBoot
2.MyBatisPlus
3.Swagger2(接口测试)

引入相关的依赖包

pom.xml
这里我只说一下关键的依赖

        <!--Mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>2.3</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>2.3</version>
        </dependency>

 <!--excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
   <!--Swagger2-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.5.0</version>
        </dependency>
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>swagger-bootstrap-ui</artifactId>
            <version>1.9.3</version>
        </dependency>

1、Entity层

这是里使用了lombok，省略了getset方法。

public class Profession {

    @TableId(value = "profession_id",type = IdType.AUTO)
    private Integer professionId;

    private String professionName;

    private String apartment;

    private String school;

    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
    private Date createTime;

    @Override
    public String toString() {
        return "Profession{" +
            "professionId=" + professionId +
            ", professionName='" + professionName + '\'' +
            ", apartment='" + apartment + '\'' +
            ", school='" + school + '\'' +
            ", createTime=" + createTime +
            '}';
    }
}

2、Dao层

@Mapper
public interface ProfessionMapper extends BaseMapper<Profession> {
}

3、Service层（稍微偷了点懒，嘘~）

@Service
public class ProfessionService {
  @Resource
  private ProfessionMapper professionMapper;

/**
   * 批量导入专业信息
   *
   * @param file
   */
  public Map<String, Object> importProfession(MultipartFile file) {
    Map<String, Object> map = new HashMap<>();
//      获取文件的名称
    String fileName = file.getOriginalFilename();
    System.out.println(fileName);
//      获取文件的后缀名
    String pattern = fileName.substring(fileName.lastIndexOf(".") + 1);
    System.out.println(pattern);
    List<List<String>> listContent = new ArrayList<>();
    String message = "导入成功";

    try {
      if (file != null) {
        //文件类型判断
        if (!ExcelUtil.isEXCEL(file)) {
          message = "该文件不是excel文件";
        } else {
          listContent = ExcelUtil.readExcelContents(file, pattern);
          //文件内容判断
          if (listContent.isEmpty()) {
            message = "表格内容为空";
          } else {
            //    循环遍历
            for (int i = 0; i < listContent.size(); i++) {
              Profession profession = new Profession();
              //      读取excel表格中的数据
              String professionName = listContent.get(i).get(0);
              String apartment = listContent.get(i).get(1);
              String school = listContent.get(i).get(2);
              //赋值
              profession.setProfessionName(professionName);
              profession.setApartment(apartment);
              profession.setSchool(school);
              //      插入数据
              professionMapper.insertSelective(profession);
            }
          }
        }
      } else {
        message = "未选择文件";
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    map.put("code", 200);
    map.put("msg", message);
    map.put("data", fileName);

    return map;
  }

}

4、Controller层

@Api(tags = "上传接口")
@Controller
@RequestMapping("uploadApi")
public class UploadController {

  @Resource
  private ProfessionService professionService;
  
  @ApiOperation(value = "读取excel")
  @PostMapping("/uploadExcel")
  @ResponseBody
  public Map<String, Object> uploadExcel(@RequestParam("file") MultipartFile file) {
      return professionService.importProfession(file);
  }

}

5、ExcelUtil工具类

    /**
     * 根据文档格式返回相应的文档对象
     * @param file
     * @param pattern 文档格式: xls, xlsx
     * @return
     */
    public static Workbook readExcel(MultipartFile file, String pattern) {
        //文档对象
        Workbook workbook = null;
        if (file != null) {
            try {
                //获取输入流
                InputStream is = file.getInputStream();
                if ("xls".equals(pattern)) {
                    //2003版格式-xls
                    return workbook = new HSSFWorkbook(is);
                } else if ("xlsx".equals(pattern)) {
                    //2007及以上版本格式-xlsx
                    return workbook = new XSSFWorkbook(is);
                } else {
                    return null;
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    /**
     * 判断上传的文件是否是EXCEL文件
     * @param file
     * @return
     */
    public static Boolean isEXCEL(MultipartFile file) {
        if (file != null) {
            //文件名
            String fileName = file.getOriginalFilename();
            //文件后缀
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
            //转小写
            suffix = suffix.toLowerCase();
            if ("xls".equals(suffix) || "xlsx".equals(suffix)) {
                return true;
            }
        }
        return false;
    }

    public static List<List<String>> readExcelContents(MultipartFile file, String pattern) {
        List<List<String>> listRow = new ArrayList<>();;
        //文档对象
        Workbook workbook = null;
        //表格对象
        Sheet sheet = null;
        //非空和文件格式判断
        if (isEXCEL(file)) {
            workbook = readExcel(file, pattern);
        }
        if (workbook != null) {
            //获取文档首个表格
            sheet = workbook.getSheetAt(0);
            //获取最大行数
            int rowNum = sheet.getPhysicalNumberOfRows();
            //行对象
            Row row = null;
            //单元格数据
            String cellData = null;
            //跳过第一行标题栏
            for (int i = 1; i < rowNum; i++) {
                row = sheet.getRow(i);
                List<String> listCell = new ArrayList<>();
                if (StringUtils.isBlank(row.getCell(0).toString())){
                    break;
                }
//              ------------------------这边需要修改------------------------
                //遍历列    --- ---   3列数据
                for (int j = 0; j < 3; j++){
                    cellData = row.getCell(j).toString();
                    listCell.add(cellData);
                }
//               --------------------------------------------------------
                listRow.add(listCell);
            }
        }
        return listRow;
    }

6、测试截图

6.1 Excel文件内容

Excel文件内容.png

6.2 测试

测试图片.png

6.3 数据库表截图

数据库表截图.png