Java读取Excel文件并将之写入数据库操作

一、添加需要的包依赖

<!--读取excel文件所需要的包-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

二、读取相应的Excel文件

public static void way() throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(new File("D:\\company_info.xls"));
    //获取一张表
    Sheet sheet = workbook.getSheetAt(0);
    for (int i=1;i<=sheet.getLastRowNum();i++) {//跳过第一行，取得其他行数据
        Row row=sheet.getRow(i);//取得第i行数据
        for (int j=0;j<row.getLastCellNum();j++) {
            Cell cell=row.getCell(j);//取得第j列数据
            cell.setCellType(CellType.STRING);
            String value = cell.getStringCellValue();
            System.out.print(i +" "+j+" " +value + " ");

        }
        System.out.println();
    }

}

三、涉及到数据库的话，将所有读取到的一行信息保存在对象中，然后将对象放入集合中

1.Excel表数据存储方式

2.改写之后的程序

public static void way(List<CUserDto> list) throws IOException, InvalidFormatException {//传入集合
    Workbook workbook = WorkbookFactory.create(new File("D:\\company_info.xls"));
    System.out.println("sheets" + workbook.getNumberOfSheets());
    //获取一张表
    Sheet sheet = workbook.getSheetAt(0);
    for (int i=1;i<=sheet.getLastRowNum();i++) {//跳过第一行
        Row row=sheet.getRow(i);//取得第i行数据
        CUserDto userDto=new CUserDto();
        String []str=new String[row.getLastCellNum()];
        for (int j=0;j<row.getLastCellNum();j++) {
            Cell cell=row.getCell(j);//取得第j列数据
            cell.setCellType(CellType.STRING);
            str[j]=cell.getStringCellValue().trim();
            System.out.print(str[j]+" ");
        }
        //System.out.println();
        //封装对象信息
        userDto.setRoleId(2);
        userDto.setUsername(str[0]);
        userDto.setPassword(str[1]);
        userDto.setCompany_name(str[2]);
        userDto.setCompany_code(str[3]);
        userDto.setRegion_code(Integer.parseInt(str[4]));
        userDto.setFirst_cp_code(Integer.parseInt(str[5]));
        userDto.setSecond_cp_code(Integer.parseInt(str[6]));
        userDto.setFirst_industry_code(Integer.parseInt(str[7]));
        userDto.setContact_name(str[8]);
        userDto.setContact_phone(str[9]);
        userDto.setContact_address(str[10]);
        list.add(userDto); //加入到集合中
    }

}

3.将集合list中对象写入数据库

public void saveUser() {
    List<CUserDto> list=new ArrayList<>();
    try {
        ExcelFileIOUtil.way(list); //读取文件到集合中去
        System.out.println("添加数据大小为："+list.size());
        for (CUserDto cUserDtoo:list
             ) {
            User db_user = userService.getUser(cUserDtoo.getUsername());
            if(db_user!=null){
             System.out.println(db_user.getId()+" "+cUserDtoo.getUsername()+" "+cUserDtoo.toString()+" 已存在此用户！");
                continue;
            }
            userService.saveUser(cUserDtoo);

        }
    } catch (IOException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    }
}

运行程序之后就可以了