项目添加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;/*** 行号*/@ExcelIgnoreprivate Integer rowNum;/*** 错误信息*/@ExcelIgnoreprivate 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> {@Overridepublic 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 {/*** 注入自定义处理器*/@Resourceprivate 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