第一种方法：EasyExcel和hutool 完成Excel导入导出

1.引入依赖

<!--        hutool工具包-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.1.0</version>
        </dependency>
<!--        EasyExcel依懒-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.5</version>
        </dependency>

2.实现导入

   /**
     * 任务导入    使用hutool工具类导入Excel文件
     * @return
     */
    @PostMapping("/import")
    @ApiOperation("导入")
    public Result fileUpload(
            @RequestParam("file") MultipartFile file
    ) {
        try {
            //使用hutool工具类导入Excel文件
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
            //读取excel中的数据，与User实体类一一对应
            List<User> listData = reader.readAll(User.class);
            //批量存入数据库中 
            //userTaskService.saveImportTask(listData);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new Result<>();
    }

3.实现导出

/**
     * 统计导出 文件下载
     *
     * @return
     */
    @GetMapping(value = "/export")
    @ApiOperation(value = "导出")
    public void statisticsExport(
            @ApiParam(name = "province", value = "省")
            @RequestParam("province") String province,
            @ApiParam(name = "city", value = "市")
            @RequestParam("city") String city,
            @ApiParam(name = "counter", value = "区")
            @RequestParam("counter") String counter,
            @ApiParam(name = "startTime", value = "开始时间")
            @RequestParam("startTime") Long startTime,
            @ApiParam(name = "endTime", value = "结束时间")
            @RequestParam("endTime") Long endTime,
            @ApiParam(name = "orderByType", value = "排序类型 ASC 升序 DESC 倒序 默认倒序")
            @RequestParam("orderByType") String orderByType,
            HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("统计", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            FastDateFormat fastDateFormat = FastDateFormat.getInstance(DatePattern.NORM_DATETIME_PATTERN, TimeZone.getTimeZone("Asia/Shanghai"));
            String startTimeStr = fastDateFormat.format(DateUtil.beginOfDay(new DateTime(startTime, TimeZone.getTimeZone("Asia/Shanghai"))));
            String endTimeStr = fastDateFormat.format(DateUtil.endOfDay(new DateTime(endTime, TimeZone.getTimeZone("Asia/Shanghai"))));
            //  DemoDTO 查询条件入参
            DemoDto dto= new DemoDto();
            dto.setProvince(province);
            dto.setCity(city);
            dto.setCounter(counter);
            dto.setStartTime(startTimeStr);
            dto.setEndTime(endTimeStr);
            dto.setOrderByType(orderByType);
            //根据查询条件查询数据库---把需要导出的数据放到list中
            List<DemoVO> list = task.findStatisByParams(DemoDto);
            // 这里需要设置不关闭流
            String dateTitle = "时间段：" + fastDateFormat.format(new DateTime(startTime,TimeZone.getTimeZone("Asia/Shanghai"))) + "至" + DateUtil.formatDate(new DateTime(endTime, TimeZone.getTimeZone("Asia/Shanghai")));
            String rangeTitle = "范围:" +
                    (StrUtil.isBlank(province) ? "全部" : province) + "/" +
                    (StrUtil.isBlank(city) ? "全部" : city) + "/" +
                    (StrUtil.isBlank(counter) ? "全部" : counter);
            EasyExcel.write(response.getOutputStream(), ClientDetailStatisVO.class)
                    .head(ClientDetailStatisVO.head(dateTitle, rangeTitle))
                    .autoCloseStream(Boolean.FALSE).sheet("统计")
                    //上面从数据库查出来的数据
                    .doWrite(list);
        } catch (Exception e) {
            // 重置response
            response.reset();
            throw new CustomException(Result.Status.INVALID_PARAM);

        }
    }

第二种方法：poi

1. Excel中有两条数据，我们需要通过java利用IO流操作，把这两条数据，导入进我们的mysql数据库。

2. 在pom.xml 中导入POI依赖

<!--excel文件提取 poi框架依赖-->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.9</version>
</dependency>

3.在model中建立实体类，与Excel中的表头一致----------实体类

@Data
@ApiModel("java批量导入")
@Table(name = "excel")
public class ExcelModel {

    @Id
    @Column(name = "id")
    @GeneratedValue(generator="JDBC")
    @ApiModelProperty("主键")
    private Integer id;
    @ApiModelProperty("公司名字")
    private String companyName;
    @ApiModelProperty("职位名称")
    private String jobTitle;
    @ApiModelProperty("年薪")
    private String annualSalary;
    @ApiModelProperty("候选人姓名")
    private String name;

    public void toPo(ExcelModelFormBean formBean) {

        this.id = formBean.getId();
        this.companyName = formBean.getCompanyName();
        this.jobTitle = formBean.getJobTitle();
        this.annualSalary = formBean.getAnnualSalary();
        this.name = formBean.getName();
    }
}

4.通过IO流批量获取Excel中的信息转化成list集合-------工具类

/**
     * 批量获取excel文件中的信息转换成list集合
     *
     * @param file 文件信息
     * @return list集合
     */
    public static List<ExcelDateInfo> getExcelFile(MultipartFile file) throws IOException {

        List<ExcelDateInfo> list = new LinkedList<>();

        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            return Collections.emptyList();
        }
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return Collections.emptyList();
        } else {
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }

            InputStream is = file.getInputStream();
            Workbook workbook;
            if (isExcel2003) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }
            Sheet sheet = workbook.getSheetAt(0);
            if (null == sheet) {
                return Collections.emptyList();
            }
            //r = 1 表示从第二行开始循环 如果你的第三行开始是数据
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                //通过sheet表单对象得到 行对象
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                ExcelDateInfo info = new ExcelDateInfo();
                if (row.getCell(0) != null) {
                    //得到每一行第二个单元格的值
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    info.setColumn1(row.getCell(0).getStringCellValue());
                }
                if (row.getCell(1) != null) {
                    //得到每一行的 第三个单元格的值
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    info.setColumn2(row.getCell(1).getStringCellValue());
                }
                if (row.getCell(2) != null) {
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    info.setColumn3(row.getCell(2).getStringCellValue());
                }
                if (row.getCell(3) != null) {
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    info.setColumn4(row.getCell(3).getStringCellValue());
                }
                // 对象放入集合
                list.add(info);
            }
        }
        return list;
    }

5.调用sql批量添加到数据库中

    /**
     * 模板文件--批量添加
     *
     * @param file 添加信息
     * @return 添加个数
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Result<Integer> saveOrderList(MultipartFile file) {

        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            return new Result<>(Result.Status.INVALID_PARAM);
        }
        if (StringUtils.equals(fileName.substring(fileName.lastIndexOf(".")), "xlsx")) {
            return new Result<>(Result.Status.INVALID_PARAM);
        }

        try {
            //读取Excel文件转化成list
            List<ExcelDateInfo> list = FileUtil.getExcelFile(file);
            if (CollectionUtils.isEmpty(list) || list.size() < 2) {
                return new Result<>(Result.Status.EMPTY_DATA);
            }

            ExcelDateInfo excel = list.get(0);
            // 验证文件头是否正确
            if (!"公司".equals(excel.getColumn1()) || !"职位".equals(excel.getColumn2()) ||
                    !"年薪".equals(excel.getColumn3()) || !"姓名".equals(excel.getColumn4())) {
                return new Result<>(Result.Status.TEMPLATE_ERROR);
            } else {
                list.remove(0);
            }

            List<ServiceHistoryOrder> orderList = new ArrayList<>();
            for (ExcelDateInfo info : list) {
                if (StringUtils.isEmpty(info.getColumn1()) && StringUtils.isEmpty(info.getColumn2()) &&
                        StringUtils.isEmpty(info.getColumn3()) && StringUtils.isEmpty(info.getColumn4())) {
                    continue;
                }

                ServiceHistoryOrder order = new ServiceHistoryOrder();
                // 判断公司列长度
                if (StringUtils.isEmpty(info.getColumn1())) {
                    return new Result<>(Result.Status.EMPTY_DATA);
                } else if (info.getColumn1().length() > 500) {
                    return new Result<>(Result.Status.DATA_TOO_LONG);
                } else {
                    order.setCompanyName(info.getColumn1());
                }
                // 判断职位列长度
                if (StringUtils.isEmpty(info.getColumn2())) {
                    return new Result<>(Result.Status.EMPTY_DATA);
                } else if (info.getColumn2().length() > 500) {
                    return new Result<>(Result.Status.DATA_TOO_LONG);
                } else {
                    order.setJobTitle(info.getColumn2());
                }
                // 判断年薪列长度
                if (StringUtils.isEmpty(info.getColumn3())) {
                    return new Result<>(Result.Status.EMPTY_DATA);
                } else if (info.getColumn3().length() > 500) {
                    return new Result<>(Result.Status.DATA_TOO_LONG);
                } else {
                    order.setAnnualSalary(info.getColumn3());
                }
                // 判断姓名列长度
                if (StringUtils.isEmpty(info.getColumn4())) {
                    return new Result<>(Result.Status.EMPTY_DATA);
                } else if (info.getColumn4().length() > 500) {
                    return new Result<>(Result.Status.DATA_TOO_LONG);
                } else {
                    order.setName(info.getColumn4());
                }
                orderList.add(order);
            }
            // 调用批量插入的sql语句，把excel数据插入到数据库    返回成功条数
            int result = orderMapper.saveHistoryOrderList(orderList);
            if (result == 0) {
                return new Result<>(Result.Status.ERROR);
            }
            return new Result<>(result);
        } catch (Exception e) {
            e.printStackTrace();
            return new Result<>(Result.Status.ERROR);
        }
    }

Best Regards！
Make a little progress every day！