【spring boot】java读取excel数据并存入数据库

一、添加包依赖。

<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.16</version>
		</dependency>

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.16</version>
		</dependency>

二、编写controller层。即上传文件并读取里面内容存入数据库层。

/**
     * 上传excel文件处理
     * @param file
     * @return
     * @throws Exception
     */
@RequestMapping("/uploadExcel")
@ResponseBody
public R upload(@RequestParam("file") MultipartFile file) {
    List<CheckpointEntity> errorList = new ArrayList<CheckpointEntity>();
    String fileName = file.getOriginalFilename();
    if (fileName.endsWith(".xls")) {
         File dest = new File(excelFilePath + fileName);
         HSSFWorkbook hssfWorkbook = null;
         try {
                file.transferTo(dest); //存入本地服务器
                //excel模板路径
                InputStream in = new FileInputStream(dest); // 读取文件流
                //读取excel模板
                hssfWorkbook = new HSSFWorkbook(in);

            } catch (IOException e) {
                e.printStackTrace();
            }
            HSSFSheet hssfSheet = null; // 读取的一张表
            try{
                SimpleDateFormat format=new SimpleDateFormat("yyyy-MM"); //存日期转换
                int numberOfSheets = hssfWorkbook.getNumberOfSheets(); // 因为要读取每一
                //张表 所以获取表数量
                for (int k=0;k<numberOfSheets;k++) {
                    hssfSheet = hssfWorkbook.getSheetAt(k);
                    CheckpointEntity checkpointEntity;
                    for (Row row:hssfSheet) {
                        if (row.getRowNum() > 2) { // 从第三行开始读取
                            checkpointEntity = new CheckpointEntity();
                            String date = String.valueOf(row.getCell(8).getNumericCellValue()); //下面是我自己的业务逻辑 不用去看
                            // System.out.println(date);
                            String newDate = date.replace(".","-");
                            Date date1 = new Date(format.parse(newDate).getTime());
                            // System.out.println(row.getCell(2).getStringCellValue());
                            checkpointEntity.setAddress("福建省福州市福清市" + row.getCell(1).getStringCellValue() + row.getCell(4).getStringCellValue());
                            checkpointEntity.setTbbm(row.getCell(2).getStringCellValue());
                            checkpointEntity.setWfzt(row.getCell(3).getStringCellValue());
                            checkpointEntity.setZdmj(row.getCell(5).getNumericCellValue());
                            checkpointEntity.setJzmj(row.getCell(6).getNumericCellValue());
                            checkpointEntity.setWjxz(row.getCell(7).getStringCellValue());
                            checkpointEntity.setTime(date1);
                            checkpointEntity.setZrr(row.getCell(9).getStringCellValue());
                            checkpointEntity.setCcqkjsm(row.getCell(10).getStringCellValue());
                            checkpointEntity.setClassify(row.getCell(11).getStringCellValue());
                            canUploadOrNot canUpload = houseService.canUpload(checkpointEntity.getTbbm(), date);

                            if (canUpload != null) {
                                Integer integer=canUpload.getObjectid();
                                Integer state = canUpload.getState();
                                checkpointEntity.setObjectid(integer);
                                if (state == 0) {
                                    // System.out.println("保存");
                                    checkpointService.save(checkpointEntity);
                                    Integer integer1 = houseService.updateState(integer);
                                }
                                else {
                                    // System.out.println("更新");
                                    checkpointService.updateByObjectId(checkpointEntity);
                                }
                                // list.clear();
                            }
                            else {
                                // System.out.println(checkpointEntity);
                                errorList.add(checkpointEntity);
                                // list.clear();
                            }
                        }
                    }
                }
                return R.ok().put("errorList",errorList);
            }
            catch (Exception e){
                e.printStackTrace();
            }
        }
        return R.error("上传格式错误");
    }