EasyPoi表格导入添加校验
- 项目添加maven依赖
- 实体类
- 自定义校验
- controller
- 测试结果
- 代码地址
项目添加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
