项目添加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.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.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.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.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.demo.entity.User;
import com.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.FileOutputStream;
import java.io.OutputStream;
import java.util.List;
@RestController
public class UserController {
/**
* 注入自定义处理器
*/
@Resource
private UserVerifyHandler userVerifyHandler;
@PostMapping(value = "/excel/importTest")
public List<User> excelImportTest(@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 path = "C:\\Users\\16837\\Desktop\\error.xls";
OutputStream outputStream = new FileOutputStream(path, true);
workbook.write(outputStream);
// 关闭输出流
workbook.close();
outputStream.close();
}
return userList;
}
}
测试结果
页面生成到处错误文件的excel
表格内容如下文章来源地址https://www.toymoban.com/news/detail-850249.html
文章来源:https://www.toymoban.com/news/detail-850249.html
到了这里,关于EasyPoi表格导入添加校验的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!