一、添加需要的包依賴

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

二、讀取相應(yīng)的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++) {//跳過第一行，取得其他行數(shù)據(jù)Row row=sheet.getRow(i);//取得第i行數(shù)據(jù)for (int j=0;j<row.getLastCellNum();j++) {Cell cell=row.getCell(j);//取得第j列數(shù)據(jù)cell.setCellType(CellType.STRING);String value = cell.getStringCellValue();System.out.print(i +" "+j+" " +value + " ");}System.out.println();}}

三、涉及到數(shù)據(jù)庫的話，將所有讀取到的一行信息保存在對象中，然后將對象放入集合中

1.Excel表數(shù)據(jù)存儲方式

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行數(shù)據(jù)CUserDto userDto=new CUserDto();String []str=new String[row.getLastCellNum()];for (int j=0;j<row.getLastCellNum();j++) {Cell cell=row.getCell(j);//取得第j列數(shù)據(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中對象寫入數(shù)據(jù)庫

public void saveUser() {List<CUserDto> list=new ArrayList<>();try {ExcelFileIOUtil.way(list); //讀取文件到集合中去System.out.println("添加數(shù)據(jù)大小為："+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();} }

運行程序之后就可以了

總結(jié)

以上是生活随笔為你收集整理的Java读取Excel文件并将之写入数据库操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。