1.需要使用 multipartFile 包 package org.springframework.web.multipart;

2.数据校验

public String exportVehicleViol(MultipartFile multipartFile) { try { //对前端传递的文件进行校验 if (multipartFile == null && multipartFile.getSize() == 0) { return "文件上传错误，重新上传"; } //获取文件名称 判断文件是否为 Execl String filename = multipartFile.getOriginalFilename(); if (!(filename.endsWith(".xls") || filename.endsWith(".xlsx"))) { return "文件上传格式有误，请重新上传"; } List<EhicleViolation> ehicleViolations = null; InputStream inputStream = multipartFile.getInputStream(); //根据文件格式 对应不同的api解析 if (filename.endsWith(".xlsx")) { ehicleViolations = readXlsx(inputStream); } else { ehicleViolations = readXls(inputStream); } //数据保存 saveBatch(ehicleViolations); } catch (IOException e) { e.printStackTrace(); } return JsonResult.Success("导入成功"); }

3.主要解析的业务逻辑

①解析xls

//解析xls private List<EhicleViolation> readXls(InputStream inputStream) throws IOException { HSSFWorkbook sheets = new HSSFWorkbook(inputStream); //读取第一张sheet HSSFSheet sheetAt = sheets.getSheetAt(0); List<EhicleViolation> ehicleViolatsion = new ArrayList<>(); //rowNum = 3 从第三行开始获取值 for (int rowNum = 3; rowNum < sheetAt.getLastRowNum(); rowNum++) { EhicleViolation ehicleViolation = new EhicleViolation(); HSSFRow row = sheetAt.getRow(rowNum); if (row != null) { //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。 //所以先使用setCellType()方法先将该单元格的类型设置为STRING //然后poi会根据字符串读取它 row.getCell(0).setCellType(CellType.STRING); row.getCell(1).setCellType(CellType.STRING); row.getCell(2).setCellType(CellType.STRING); row.getCell(3).setCellType(CellType.STRING); row.getCell(4).setCellType(CellType.STRING); row.getCell(5).setCellType(CellType.STRING); row.getCell(6).setCellType(CellType.STRING); row.getCell(7).setCellType(CellType.STRING); row.getCell(8).setCellType(CellType.STRING); String stringCellValue0 = row.getCell(0).getStringCellValue(); String stringCellValue1 = row.getCell(1).getStringCellValue(); if (StringUtils.isNotBlank(stringCellValue1)) { ehicleViolation.setUserDept(stringCellValue1); } //根据自己需要 获取表格中的数据 String stringCellValue2 = row.getCell(2).getStringCellValue(); if (StringUtils.isNotBlank(stringCellValue2)) { ehicleViolation.setVehicleNumber(stringCellValue2); } else { continue; } } EehicleViolations.add(EehicleViolation); } return EehicleViolations; }

②解析xlsx

//解析xlsx private List<VmsVehicleViolation> readXlsx(InputStream inputStream) throws IOException { XSSFWorkbook sheets1 = new XSSFWorkbook(inputStream); XSSFSheet sheetAt1 = sheets1.getSheetAt(0); List<EhicleViolation> ehicleViolatsion = new ArrayList<>(); //rowNum = 3 从第三行开始获取值 for (int rowNum = 3; rowNum < sheetAt.getLastRowNum(); rowNum++) { EhicleViolation ehicleViolation = new EhicleViolation(); HSSFRow row = sheetAt.getRow(rowNum); if (row != null) { //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。 //所以先使用setCellType()方法先将该单元格的类型设置为STRING //然后poi会根据字符串读取它 row.getCell(0).setCellType(CellType.STRING); row.getCell(1).setCellType(CellType.STRING); row.getCell(2).setCellType(CellType.STRING); row.getCell(3).setCellType(CellType.STRING); row.getCell(4).setCellType(CellType.STRING); row.getCell(5).setCellType(CellType.STRING); row.getCell(6).setCellType(CellType.STRING); row.getCell(7).setCellType(CellType.STRING); row.getCell(8).setCellType(CellType.STRING); String stringCellValue0 = row.getCell(0).getStringCellValue(); String stringCellValue1 = row.getCell(1).getStringCellValue(); if (StringUtils.isNotBlank(stringCellValue1)) { ehicleViolation.setUserDept(stringCellValue1); } //根据自己需要 获取表格中的数据 String stringCellValue2 = row.getCell(2).getStringCellValue(); if (StringUtils.isNotBlank(stringCellValue2)) { ehicleViolation.setVehicleNumber(stringCellValue2); } else { continue; } } EehicleViolations.add(EehicleViolation); } return EehicleViolations; }

注意：对于不同的Execl Java提供了不同的解析对象 

xls使用HSSFWorkbook 对象进行解析 

xlsx使用XSSWorkbook 对象进行解析