EasyPoi表格导入添加校验

项目添加maven依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>org.example</groupId>
    <artifactId>easypoi-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--easy poi 依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.5.0</version>
        </dependency>
        <!--hibernate校验-->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>5.4.1.Final</version>
        </dependency>
        <dependency>
            <groupId>javax.el</groupId>
            <artifactId>javax.el-api</artifactId>
            <version>3.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.glassfish.web</groupId>
            <artifactId>javax.el</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.20</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>2.0.48</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>3.1.2</version>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

实体类

package com.easy.poi.demo.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;


@Data
public class User implements IExcelDataModel, IExcelModel {

    @Excel(name = "序号", width = 20)
    private String serialNum;

    @Excel(name = "姓名", width = 20)
    private String name;

    @Excel(name = "性别", width = 20)
    private String gender;

    /**
     * 行号
     */
    @ExcelIgnore
    private Integer rowNum;

    /**
     * 错误信息
     */
    @ExcelIgnore
    private String errorMsg;
}

自定义校验

package com.easy.poi.demo.util;

import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import cn.hutool.core.util.ObjectUtil;
import com.easy.poi.demo.entity.User;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 自定义校验
 */
@Component
public class UserVerifyHandler implements IExcelVerifyHandler<User> {
    @Override
    public ExcelVerifyHandlerResult verifyHandler(User user) {
        String[] genders = {"男", "女"};
        if (ObjectUtil.isEmpty(user.getName()) && ObjectUtil.isEmpty(user.getGender())) {
            return new ExcelVerifyHandlerResult(true);
        }
        List<String> genderList = new ArrayList<>(Arrays.asList(genders));
        if (!genderList.contains(user.getGender())) {
            return new ExcelVerifyHandlerResult(false, "性别不合法");
        }
        return new ExcelVerifyHandlerResult(true);
    }
}

controller

package com.easy.poi.demo.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.easy.poi.demo.entity.User;
import com.easy.poi.demo.util.UserVerifyHandler;
import jakarta.annotation.Resource;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

@RestController
public class UserController {

    /**
     * 注入自定义处理器
     */
    @Resource
    private UserVerifyHandler userVerifyHandler;

    @PostMapping(value = "/excel/import")
    public List<User> excelImport(@RequestParam("file") MultipartFile file) throws Exception {
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        // 代表导入这里是需要验证的
        params.setNeedVerify(true);
        // 校验处理接口
        params.setVerifyHandler(userVerifyHandler);
        ExcelImportResult<User> importResult = ExcelImportUtil.importExcelMore(file.getInputStream(), User.class, params);
        List<User> userList = importResult.getList();

        // isVerifyFail:是否存在校验失败,true为校验失败，false为校验成功
        if (importResult.isVerifyFail() || userList.isEmpty()) {
            // 校验失败或者导入的数据为空，输出错误记录的表格
            Workbook workbook = importResult.getFailWorkbook();
            // 当前项目目录
            String currentWorkingDir = System.getProperty("user.dir");
            String projectDir = "easypoi-demo";
            String dirPath = currentWorkingDir + File.separator + projectDir + File.separator + "excel";
            File dir = new File(dirPath);
            if (!dir.exists()) {
                // 目录不存在，创建目录
                dir.mkdir();
            }
            String path = dirPath + File.separator + "error.xlsx";
            OutputStream outputStream = new FileOutputStream(path, true);
            workbook.write(outputStream);

            // 关闭输出流
            workbook.close();
            outputStream.close();
        }
        return userList;
    }
}

测试结果

生成错误表格内容如下

代码地址

https://gitee.com/BAIXUEQIAN/java-study/tree/develop/easypoi-demo