java实现excel数据导入数据库

一、pom文件引入：

<dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>

二、Controller层

 @ApiOperation(value = "Excel表格中车辆信息导入数据库cars表中", notes = "Excel表格中车辆信息导入数据库cars表中")
    @PostMapping(value = "/importExcelCars",headers="content-type=multipart/form-data")
    public ResultData importExcelCars(@ApiParam(value = "Excel表格",required = false) MultipartFile file) throws IOException {
        logger.info("------Excel表格中车辆信息导入数据库cars表中   importExcelCars:{} start {}");
                String fileName = file.getOriginalFilename();
       try {
           String result =importExcelService.batchImport(fileName, file);
           if (!result.equals(ResultMessage.SUCCESS)) {
              logger.info("------Excel表格中车辆信息导入数据库cars表中  !result.equals(ResultMessage.SUCCESS)  importExcelCars:{} {} end", "业务异常");
               return ResultData.bizError(result);
            }
       } catch (Exception e) {
           e.printStackTrace();
        }
    }

三、Service层

    public String batchImport(String fileName, MultipartFile file) throws Exception {
        List<Cars> carsList = new ArrayList<Cars>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            return ResultMessage.NOT_EXCEL_ERROR;
        }
        boolean isExcel2003 = true;
       if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            Cars cars = new Cars();
            String carNum = row.getCell(0).getStringCellValue();
            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            String devNum = row.getCell(1).getStringCellValue();
            cars.setCarNum(carNum);//车牌号
            cars.setDevNum(devNum);//设备号
            cars.setUnitId(0L);
            cars.setCreateTime(new Date());
            cars.setStatus(false);
            carsList.add(cars);
        }
        carsDao.insertExcelCarsList(carsList);//数据库操作
        wb.close();
        return ResultMessage.SUCCESS;
    }

Excel表格内容：

 

 

 

 

                            ---------------长按二维码关注程序媛小姐姐公众号有更多彩蛋哦---------------