Hutool的ExcelUtil常见使用

从表格头开始读，自动封装实体，但只能读到第一个sheet

@PostMapping("/importExcel")
public void importExcel(MultipartFile file) throws IOException {
    InputStream inputStream = file.getInputStream();
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);
    excelReader.addHeaderAlias("用户名", "username");
    excelReader.addHeaderAlias("密码", "password");
    excelReader.addHeaderAlias("昵称", "nickname");
    excelReader.addHeaderAlias("手机号", "phone");
    excelReader.addHeaderAlias("邮箱", "email");
    excelReader.addHeaderAlias("地址", "address");
    List<User> userList = excelReader.readAll(User.class);
    System.out.println(userList);
    userService.saveBatch(userList);
}

指定从第二行开始读，跳过表头

@PostMapping("/importExcel")
public void importExcel(MultipartFile file) throws IOException {
    InputStream inputStream = file.getInputStream();
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);

    // 指定从第几行开始读，但就只能手动塞入数据了，但也是只能读到第一个sheet
    List<List<Object>> readAll = excelReader.read(1);
    readAll.forEach(list -> {
        User user = new User();
        user.setUsername(list.get(0).toString());
        user.setPassword(list.get(1).toString());
        user.setNickname(list.get(2).toString());
        user.setPhone(list.get(3).toString());
        user.setEmail(list.get(4).toString());
        user.setAddress(list.get(5).toString());
    });

    System.out.println(readAll);
    userService.saveBatch(userList);
}

一次性读取所有sheet页中的数据

@PostMapping("/importExcel")
public void importExcel(MultipartFile file) throws IOException {
    InputStream inputStream = file.getInputStream();
    ExcelReader excelReader = ExcelUtil.getReader(inputStream);

    List<User> userList = new ArrayList<>();
    // 获取sheet页数
    int sheetCount = excelReader.getSheetCount();
    // 循环读取sheet页，一次性读取所有sheet页中的数据
    for (int i = 0; i < sheetCount; i++) {
        ExcelReader sheet = excelReader;
        List<List<Object>> read = excelReader.setSheet(i).read();
        read.forEach(list -> {
            User user = new User();
            user.setUsername(list.get(0).toString());
            user.setPassword(list.get(1).toString());
            user.setNickname(list.get(2).toString());
            user.setPhone(list.get(3).toString());
            userList.add(user);
        });
    }
}