springboot导入excel表到mysql数据库中

需要导入的依赖

 <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>
        <dependency>
            <groupId>com.yanzhenjie.apache</groupId>
            <artifactId>fileupload</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.4</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.freemarker/freemarker -->
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.23</version>
        </dependency>

Controller层

// 跳转到导入Excel表的页面
  @RequestMapping("excel")
    public String UploadUI() {
        return "uploadExcel";
    }


    /**
     * 导入excel
     */
    @RequestMapping("/import")
    @ResponseBody
    public String excelImport(@RequestParam(value="filename") MultipartFile file){
        int result = 0;
        try {
            result = accService.ExcelAdd(file);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(result > 0){
            return "excel文件数据导入成功！";
        }else{
            return "请查看文件内容是否有误，确认无误后请继续上传！！";
        }

service层

// 根据controller层的错误提示进行代码的编写
int ExcelAdd(MultipartFile file) throws IOException, ParseException;

serviceImpl实现层

```java

  @Override
    public int ExcelAdd(MultipartFile file) throws IOException, ParseException {
        int result = 0;
        //存放excel表中所有账号的信息
        List<Account> accountList = new ArrayList<>();
        /**
         *
         * 判断文件版本
         */
        String fileName = file.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);

        InputStream ins = file.getInputStream();
        Workbook wb = null;
        if (suffix.equals("xlsx")) {
            wb = new XSSFWorkbook(ins);
        } else {
            wb = new HSSFWorkbook(ins);
        }
        /**
         * 获取excel表单
         */
        Sheet sheet = wb.getSheetAt(0);


        /**
         * line = 1:从表的第二行开始获取记录
         *
         */
        if (null != sheet) {

            for (int line = 1; line <= sheet.getLastRowNum(); line++) {

                Account account = new Account();

                Row row = sheet.getRow(line);

                if (null == row) {
                    continue;
                }

                //将从Excel表中读取的内容转成String格式（如果Excel表格中纯数字，不执行一下操作，会报错，建议一下操作全部执行。）
                row.getCell(0).setCellType(CellType.STRING);
                row.getCell(1).setCellType(CellType.STRING);
                row.getCell(2).setCellType(CellType.STRING);
                row.getCell(3).setCellType(CellType.STRING);
                row.getCell(4).setCellType(CellType.STRING);
                row.getCell(5).setCellType(CellType.STRING);
                row.getCell(6).setCellType(CellType.STRING);
                row.getCell(7).setCellType(CellType.STRING);


                /**
                 * 获取第一个单元格的内容
                 */
                String aid = row.getCell(0).getStringCellValue();
                /**
                 * 获取第二个单元格的内容
                 */
                String aname = row.getCell(1).getStringCellValue();
                String bandphone = row.getCell(2).getStringCellValue();
                String bandemail = row.getCell(3).getStringCellValue();


                //Date类型 需要注意，Excel表里的单元格式，我用的是文本格式，其他的我不太晓得方法！

                String receive_date = row.getCell(4).getStringCellValue();
                Date date2;
                //下面的日期格式需要和实体类中的日期格式一样，Excel表的日期也要这样写，否则会报日期格式不对的错误
                DateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
                date2 = format2.parse(receive_date);


                String aplatform = row.getCell(5).getStringCellValue();
                String anature = row.getCell(6).getStringCellValue();
                String username = row.getCell(7).getStringCellValue();

                //Integer类型
                /*Integer i = null;
                String status = row.getCell(6).getStringCellValue();
                if(status.equals("") || status.equals(null)) {
                    i = null;
                }else {
                    i = Integer.parseInt(status);
                }*/



                /*String receive_date = row.getCell(10).getStringCellValue();
                Date date2 = null;
                DateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
                date2 = format2.parse(receive_date);

                String send_date = row.getCell(11).getStringCellValue();
                Date date3 = null;
                DateFormat format3 = new SimpleDateFormat("yyyy-MM-dd");
                date3 = format3.parse(receive_date);*/

                account.setAid(null);
                account.setAname(aname);
                account.setBandphone(bandphone);
                ;
                account.setBandemail(bandemail);
                account.setAdate(date2);
                account.setAplatform(aplatform);
                account.setAnature(anature);
                account.setUsername(username);

                accountList.add(account);

            }
            for (Account account : accountList) {
                result = accMapper.ExcelAdd(account);
            }
        }
        return result;
    }

后面就是mapper层
int ExcelAdd(Account account);

XML文件，实现SQL语句进行对数据的插入

 <insert id="ExcelAdd" parameterType="Account">
        insert into account (aname, bandphone, bandemail, adate, aplatform, anature, username)
        values (#{aname}, #{bandphone}, #{bandemail}, #{adate}, #{aplatform}, #{anature}, #{username});
    </insert>

实体类

```java
@Data
public class Account implements Serializable {
    private Long aid;
    private String aname;
    private String bandphone;
    private String bandemail;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date adate;
    private String aplatform;
    private String anature;
    private String username;
}