java批量读取Excel文件中数据导入进mysql数据库中---（通过java方式）

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);
        }
    }

6.有些Model和Mapper和Controller调用就不写了

Best Regards！

Make a little progress every day！