一、添加需要的包依赖
<!--读取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();}}
运行程序之后就可以了
