POI校验并导入EXCEL

1. 编写Controller接收文件代码

@PostMapping("/importPatenHandler")
@ResponseBody
public ResponseEntity<?> importPatenHandler(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
    if (file.isEmpty()) {
        return ResponseEntity.badRequest().body("文件为空");
    }

    try {
        // 1.读取文件中的数据
        List<Map<String, String>> list = readExcelFile(file.getInputStream());

        // 2.筛选不符合条件的数据
        List<String> errorList = this.processData(list);

        // 3.批量导入数据
        this.importData(list);

        return ResponseEntity.ok("导入完成，成功导入" + list.size() + "条数据，失败" + errorList.size() + "条数据，" + errorList);
    } catch (IOException e) {
        e.printStackTrace();
        return ResponseEntity.status(500).body("文件读取失败: " + e.getMessage());
    } catch (Exception e) {
        e.printStackTrace();
        return ResponseEntity.status(500).body("文件读取失败: " + e.getMessage());
    }
}

2. 读取文件中的数据

private List<Map<String, String>> readExcelFile(InputStream inputStream) throws IOException {
    List<Map<String, String>> data = new ArrayList<>();
    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet datatypeSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = datatypeSheet.iterator();

    // Skip the first row as it is the header
    iterator.next();

    while (iterator.hasNext()) {
        Row currentRow = iterator.next();
        Map<String, String> map = new HashMap<>();

        // Assuming the data is in the first four columns
        Cell cell;

        cell = currentRow.getCell(0);
        if (cell != null) {
            map.put("序号", cell.toString());
        }

        cell = currentRow.getCell(1);
        if (cell != null) {
            map.put("编码", cell.toString());
        }

        cell = currentRow.getCell(2);
        if (cell != null) {
            map.put("名称", cell.toString());
        }

        data.add(map);
    }
    workbook.close();
    inputStream.close();

    return data;
}

3. 筛选不符合条件的数据

private List<String> processData(List<Map<String, String>> list) {

    //不符合条件的数据
    List<String> errorList = new ArrayList<>();

    //用于查询的参数
    Map<String, Object> args = new HashMap<>();

    // 使用迭代器来安全地移除列表中的元素
    Iterator<Map<String, String>> iterator = list.iterator();
    while (iterator.hasNext()) {
        Map<String, String> data = iterator.next();
        String index = data.get("序号");
        String patentNo = data.get("编码");
        String account = data.get("名称");

        // 校验数据是否填写
        if (StringUtils.isEmpty(patentNo) || StringUtils.isEmpty(account)) {
            errorList.add("序号" + index + "：内容为空");
            iterator.remove(); // 移除当前元素
            continue;
        }

        // 查询用户是否存在
        User user = userInfoService.getUserByAccount(account);
        if (user == null) {
            errorList.add("序号" + index + "：内容为空！");
            iterator.remove(); // 移除当前元素
            continue;
        } else {
            if (StringUtils.isNotEmpty(user.getName())) {
                data.put("内容为空");
            }
        }

        // 查询数据是否存在
        args.put("sqhzlh", patentNo);
        List<Map<String, Object>> patentList = namedParameterJdbcTemplate.queryForList("SELECT id,sqhzlh,zlmc FROM xm_onl_zlflfj_91353841 WHERE sqhzlh=:sqhzlh", args);
        if (patentList.isEmpty()) {
            errorList.add("序号" + index + "：数据不存在！");
            iterator.remove(); // 移除当前元素
        }
    }
    return errorList;
}

4. 导入数据

/**
 * 导入数据
 */
private void importData(List<Map<String, String>> list) {
    list.forEach(item -> {
        String updateSql = "UPDATE aaa SET handler=? WHERE sqhzlh =?";
        template.update(updateSql, item.get("编码"), item.get("名称"));
    });
}