java小程序：读取excel数据存入mysql

1.添加依赖

<!--excel工具依赖-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.5.1</version>
</dependency>

<!--连接数据库工具依赖-->

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

<!--数据库操作工具依赖-->

<dependency>
    <groupId>org.nutz</groupId>
    <artifactId>nutz</artifactId>
    <version>1.r.68.v20190220</version>
</dependency>

2.连接数据库

SimpleDataSource dataSource = new SimpleDataSource();
dataSource.setJdbcUrl("jdbc:mysql://192.168.0.210:3306/renren_fast?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true");
dataSource.setUsername("账号");
dataSource.setPassword("密码");
Dao dao = new NutDao(dataSource);

3.读取excel并存入数据库

String path = "D:\\lixiao\\excel\\"+eName;
        Workbook wb = getWorkBook(path);
        //开始解析
        Sheet sheet = wb.getSheetAt(count);     //读取sheet 0
        int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读
        int lastRowIndex = sheet.getLastRowNum();//最后一行
        List<WxContactTask> list = new ArrayList<>();
        for(int rIndex = 1; rIndex <= lastRowIndex; rIndex++) {   //遍历行
            Row row = sheet.getRow(rIndex);
            if (row != null) {
                int firstCellIndex = row.getFirstCellNum();
                int lastCellIndex = row.getLastCellNum();
                Cell cell = row.getCell(1);
                String category = cell.toString();
                System.out.println(category);
                WxContactTask wxContactTask = new WxContactTask();
                wxContactTask.setPublicAccountName(category);
                wxContactTask.setInsertTime(new Date());
                list.add(wxContactTask);
//                List<TAll> allList = dao.query(TAll.class, Cnd.where("cate", "=", last));
            }
        }
        list = list.stream().distinct().collect(Collectors.toList());//去重
        dao.fastInsert(list);

public static Workbook getWorkBook(String path){
    //创建工作簿并载入excel文件流
    File excel = new File(path);
    try {
        if (excel.isFile() && excel.exists()) {   //判断文件是否存在
            String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
            Workbook wb;
            //根据文件后缀（xls/xlsx）进行判断
            if ("xls".equals(split[1])) {
                FileInputStream fis = new FileInputStream(excel);   //文件流对象
                return new HSSFWorkbook(fis);
            } else if ("xlsx".equals(split[1])) {
                return new XSSFWorkbook(excel);
            } else {
                System.out.println("文件类型错误!");
            }
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    }

    return null;
}