poi5.0.0版本简单导入列子

1.导入依赖

<!--        SpringBoot操作导入导出Excel的依赖支持-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>

2.创建User对象

public class User {
    /**
     * 姓名
     */
    private String name;
    /**
     * 年龄
     */
    private Integer age;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

3.poi业务层代码

public List<User> importTest(MultipartFile file){
        List<User> users = new ArrayList<>();
        if (ObjectUtils.isNotEmpty(file)){
            String filename = file.getOriginalFilename();
            Workbook workbook=null;
            try {
                InputStream is = file.getInputStream();
                if (filename.endsWith(EXCEL2007)){
                    workbook=new XSSFWorkbook(is);
                }
                if (filename.endsWith(EXCEL2003)){
                    workbook=new HSSFWorkbook(is);
                }
                if (workbook!=null){
                    Sheet sheet1 = workbook. getSheet( "Sheet1");
                    int lastRowNum = sheet1.getLastRowNum();
                    int b=2;
                    for (int i = 1; i <= lastRowNum; i++){
                        User user = new User();
                        for (int j = 0; j <= b; j++) {
                            DataFormatter formatter = new DataFormatter();
                            String formatCellValue = formatter.formatCellValue(sheet1.getRow(i).getCell(j));

                            if (j == 0) {

                                if (StringUtils.isEmpty(formatCellValue)){
                                    //如果值为空则直接返回或提示
                                }

                                if (formatCellValue.length()>64){
                                    //如果值长度超长，直接返回字段提醒字段超长
                                }
                                user.setName(formatCellValue);
                            } else if (j == 1){
                                //检验值是否为整数
                                Pattern pattern = Pattern.compile("[0-9]*");
                                boolean matches = pattern.matcher(formatCellValue).matches();
                                if (matches){
                                    //第"+i+"行姓名字段数据不正确，年龄应该为整数
                                }
                                if (StringUtils.isEmpty(formatCellValue)){
                                  //"第"+i+"行说明字段没有值"
                                }
                                if (formatCellValue.length()>3){
                                  // "第"+i+"行说明字段长度不能超过3位"
                                }
                                user.setAge(Integer.parseInt(formatCellValue));
                            }
                        }
                        users.add(user);
                    }
                }
            } catch (Exception e){
                e.printStackTrace();
            }
        }
        return users;
    }

4.控制层代码

 /**
     * excel导入用户信息
     *
     * @return
     */
    @ApiLog(value = "excel导入用户信息")
    @ApiOperation(value = "excel导入用户信息", notes = "excel导入用户信息")
    @PostMapping(value = "/importUserFile")
    @ResponseBody
    public List<User> importUserFile(@RequestParam(value ="file",required = false) MultipartFile file) {
        return gcConcealedWorksService.importUserFile(file);
    }

5.excel用户模板

注意：excel格式为.xls

6.用postman测试

现在做的是一个简单的demo，后面有时间完善其他的功能。