EasyPOI 导入验证功能
来自:
http://www.leftso.com/blog/326.html
https://www.cnblogs.com/bigbigxiao/p/11897871.html
1.加入EasyPOI 的Jar包支持
多说两句,我刚开始测试的时候一直报ValidationException异常,然后加上
hibernate-validator就可以了
<!-- easypoi的支持 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<!--防止报javax.validation.ValidationException: Unable to create a Configuration, because no Bean异常-->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>4.3.0.Final</version>
</dependency>
2.创建实体类
注意观察有校验的注解:
package com.day2;
import cn.afterturn.easypoi.excel.annotation.Excel;
import javax.validation.constraints.Min;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
/**
* @author
* @create 2020-07-07 11:05
*/
public class User
{
@Excel(name = "用户ID",width = 10)
@Min(value =1,message = "用户Id必须为大于0的整数")
private int id;
@Excel(name = "用户名称",width = 10)
@NotNull(message = "用户名不为空")
private String name;
@Excel(name = "用户密码",width = 10)
@Pattern(regexp = "\\d{6}",message = "用户密码是数字6位数")
private String password;
}
3.自定义校验(比如Id的唯一性校验,我们经常要去数据库查找数据进行比对)
package com.day2;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import java.util.ArrayList;
/**
* @author
* @create 2020-07-07 11:27
*/
public class UserVerifyHandler implements IExcelVerifyHandler<User>
{
private static boolean getById(User u){
ArrayList<User> users = new ArrayList<>();
users.add(new User(1,"张娜娜","123456"));
users.add(new User(2,"谢娜","654321"));
users.add(new User(3,"李娜","654123"));
for (User user : users)
{
if (user.getId() == u.getId())
{
return true;
}
}
return false;
}
/**
* 自定义检验实现
* @param obj
* @return
*/
@Override
public ExcelVerifyHandlerResult verifyHandler(User obj)
{
ExcelVerifyHandlerResult Result = new ExcelVerifyHandlerResult();
//判断Id是否重复
boolean b = getById(obj);
if (b){
Result.setSuccess(false);
Result.setMsg("Id重复:["+obj.getId()+"]");
}
return Result;
}
}
4.测试
package com.day2;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.List;
/**
* @author
* @create 2020-07-07 11:18
*/
public class UserTest
{
public static void main(String[] args) throws Exception
{
FileInputStream fileInputStream = new FileInputStream(new File("G:\\user.xlsx"));
ImportParams importParams = new ImportParams();
// 需要验证
importParams.setNeedVerfiy(true);
//数据处理
IExcelVerifyHandler userVerifyHandler = new UserVerifyHandler();
importParams.setVerifyHandler(userVerifyHandler);
ExcelImportResult<User> Result = ExcelImportUtil.importExcelMore(fileInputStream, User.class, importParams);
//成功的数据
List<User> Succeslist = Result.getList();
System.out.println("成功的列表:"+Succeslist);
//失败的数据
List<User> failList = Result.getFailList();
System.out.println("失败的列表"+failList);
//将错误的数据写出
Workbook failWorkbook = Result.getFailWorkbook();
if (null != failWorkbook){
failWorkbook.write(new FileOutputStream(new File("G:\\fail_user2.xlsx")));
}
}
}
控制台打印结果:
看一下源Excel文件 user.xlsx:
查看输出错误的Excel文件 fail_user2.xlsx:
发现EasyPOI是不是很厉害,不仅可以把正确的数据导入并且还可以将错误的数据同样用Excel文件写出,我之前公司有这个需求,当时我自己用原生的POI写了大量的代码,当找到EasyPOI,几行代码就能解决
总结一下:
1. 首选我们看一下源码:ExcelImportUtil这个工具类
我们常用的导入方法无非就是:
方式一:
/**
* Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
*
* @param inputstream
* @param pojoClass
* @param params
* @return
* @throws Exception
*/
public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass,
ImportParams params) throws Exception {
return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, false).getList();
}
和
方式二:
/**
* Excel 导入 数据源IO流 字段类型 Integer,Long,Double,Date,String,Boolean
* 支持校验,支持Key-Value
*
* @param inputstream
* @param pojoClass
* @param params
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream,
Class<?> pojoClass,
ImportParams params) throws Exception {
return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, true);
}
方式一:是不需要校验参数的方法;
方式二:是需要校验参数的方法,且返回校验结果
当然还支持大数据导入,本次不做研究
2.分析一下导入的几个参数
2.1 InputStream inputstream,这个不用多讲,是携带数据的输入流
2.2 Class<?> pojoClass 这个参数是实体类的Class对象
2.3 ImportParams params ,这个参数是导入参数,是EasyPOI这个库里面的类,现在就这个详细说明
字段 | 字段类型 | 默认值 | 说明 |
---|---|---|---|
titleRows | int | 0 | 表格标题行数,默认0,意思就是告诉系统你的标题栏是在第几行,一般标题栏都在首行,默认是0 |
headRows | int | 1 | 表头行数,默认1 |
startSheetIndex | int | 0 | 开始读取的sheet位置,默认为0 ,就是第一个sheet工作表 |
sheetNum | int | 1 | 上传表格需要读取的sheet 数量,默认为1,一般读取的是一个sheet工作表,如果你有多个sheet,你可以定义一次读取多个sheet |
needVerfiy | boolean | false | 是否需要校验上传的Excel,默认为false,如果你设置true就可以对上传的excel中的字段进行校验,一般和verifyHandler同时使用 |
verifyHandler | IExcelVerifyHandler | null | 除了给字段加上注解进行基本的校验之外,用户还可以自定义一些校验规则,比如去数据库查数据校验数据唯一性等,IExcelVerifyHandler是,用户需要自己实现该接口,并重写verifyHandler(T obj)的方法,本案例中已用到 |
以下关于几个参数源码的剖析:
ImportParams导入参数类
package cn.afterturn.easypoi.excel.entity;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
/**
* 导入参数设置
*
* @author JueYue
* 2013-9-24
* @version 1.0
*/
@SuppressWarnings("rawtypes")
public class ImportParams extends ExcelBaseParams {
public static final String SAVE_URL = "/excel/upload/excelUpload";
/**
* 表格标题行数,默认0
*/
private int titleRows = 0;
/**
* 表头行数,默认1
*/
private int headRows = 1;
/**
* 字段真正值和列标题之间的距离 默认0
*/
private int startRows = 0;
/**
* 主键设置,如何这个cell没有值,就跳过 或者认为这个是list的下面的值
* 大家不理解,去掉这个
*/
private Integer keyIndex = null;
/**
* 开始读取的sheet位置,默认为0
*/
private int startSheetIndex = 0;
/**
* 上传表格需要读取的sheet 数量,默认为1
*/
private int sheetNum = 1;
/**
* 是否需要保存上传的Excel,默认为false
*/
private boolean needSave = false;
/**
* 校验组
*/
private Class[] verfiyGroup = null;
/**
* 是否需要校验上传的Excel,默认为false
*/
private boolean needVerfiy = false;
/**
* 校验处理接口
*/
private IExcelVerifyHandler verifyHandler;
/**
* 保存上传的Excel目录,默认是 如 TestEntity这个类保存路径就是
* upload/excelUpload/Test/yyyyMMddHHmss_***** 保存名称上传时间_五位随机数
*/
private String saveUrl = SAVE_URL;
/**
* 最后的无效行数
*/
private int lastOfInvalidRow = 0;
/**
* 手动控制读取的行数
*/
private int readRows = 0;
/**
* 导入时校验数据模板,是不是正确的Excel
*/
private String[] importFields;
/**
* 导入时校验excel的标题列顺序。依赖于importFields的配置顺序
*/
private boolean needCheckOrder = false;
/**
* Key-Value 读取标记,以这个为Key,后面一个Cell 为Value,多个改为ArrayList
*/
private String keyMark = ":";
/**
* 按照Key-Value 规则读取全局扫描Excel,但是跳过List读取范围提升性能
* 仅仅支持titleRows + headRows + startRows 以及 lastOfInvalidRow
*/
private boolean readSingleCell = false;
public int getHeadRows() {
return headRows;
}
public String getSaveUrl() {
return saveUrl;
}
public int getSheetNum() {
return sheetNum;
}
public int getStartRows() {
return startRows;
}
public int getTitleRows() {
return titleRows;
}
public IExcelVerifyHandler getVerifyHandler() {
return verifyHandler;
}
public boolean isNeedSave() {
return needSave;
}
public void setHeadRows(int headRows) {
this.headRows = headRows;
}
@Deprecated
public void setKeyIndex(Integer keyIndex) {
this.keyIndex = keyIndex;
}
public void setNeedSave(boolean needSave) {
this.needSave = needSave;
}
public void setSaveUrl(String saveUrl) {
this.saveUrl = saveUrl;
}
public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}
public void setStartRows(int startRows) {
this.startRows = startRows;
}
public void setTitleRows(int titleRows) {
this.titleRows = titleRows;
}
public void setVerifyHandler(IExcelVerifyHandler verifyHandler) {
this.verifyHandler = verifyHandler;
}
public int getLastOfInvalidRow() {
return lastOfInvalidRow;
}
public void setLastOfInvalidRow(int lastOfInvalidRow) {
this.lastOfInvalidRow = lastOfInvalidRow;
}
public int getStartSheetIndex() {
return startSheetIndex;
}
public void setStartSheetIndex(int startSheetIndex) {
this.startSheetIndex = startSheetIndex;
}
public boolean isNeedVerfiy() {
return needVerfiy;
}
public void setNeedVerfiy(boolean needVerfiy) {
this.needVerfiy = needVerfiy;
}
public String[] getImportFields() {
return importFields;
}
public void setImportFields(String[] importFields) {
this.importFields = importFields;
}
public int getReadRows() {
return readRows;
}
public void setReadRows(int readRows) {
this.readRows = readRows;
}
public Integer getKeyIndex() {
return keyIndex;
}
public String getKeyMark() {
return keyMark;
}
public void setKeyMark(String keyMark) {
this.keyMark = keyMark;
}
public boolean isReadSingleCell() {
return readSingleCell;
}
public void setReadSingleCell(boolean readSingleCell) {
this.readSingleCell = readSingleCell;
}
public Class[] getVerfiyGroup() {
return verfiyGroup;
}
public void setVerfiyGroup(Class[] verfiyGroup) {
this.verfiyGroup = verfiyGroup;
}
public boolean isNeedCheckOrder() {
return needCheckOrder;
}
public void setNeedCheckOrder(boolean needCheckOrder) {
this.needCheckOrder = needCheckOrder;
}
}
ExcelBaseParams基本参数类(导入导出参数都会继承它)
package cn.afterturn.easypoi.excel.entity;
import cn.afterturn.easypoi.handler.inter.IExcelDataHandler;
import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
/**
* 基础参数
* @author JueYue
* 2014年6月20日 下午1:56:52
*/
@SuppressWarnings("rawtypes")
public class ExcelBaseParams {
/**
* 数据处理接口,以此为主,replace,format都在这后面
*/
private IExcelDataHandler dataHandler;
/**
* 字段处理类
*/
private IExcelDictHandler dictHandler;
public IExcelDataHandler getDataHandler() {
return dataHandler;
}
public void setDataHandler(IExcelDataHandler dataHandler) {
this.dataHandler = dataHandler;
}
public IExcelDictHandler getDictHandler() {
return dictHandler;
}
public void setDictHandler(IExcelDictHandler dictHandler) {
this.dictHandler = dictHandler;
}
}
IExcelVerifyHandler接口
package cn.afterturn.easypoi.handler.inter;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
/**
* 导入校验接口
*
* @author JueYue
* 2014年6月23日 下午11:08:21
*/
public interface IExcelVerifyHandler<T> {
/**
* 导入校验方法
*
* @param obj
* 当前对象
* @return
*/
public ExcelVerifyHandlerResult verifyHandler(T obj);
}
ExcelImportUtil导出工具类
package cn.afterturn.easypoi.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.excel.imports.sax.parse.ISaxRowRead;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IExcelReadRowHandler;
/**
* Excel 导入工具
*
* @author JueYue
* 2013-9-24
* @version 1.0
*/
@SuppressWarnings({ "unchecked" })
public class ExcelImportUtil {
private ExcelImportUtil() {
}
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);
/**
* Excel 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
*
* @param file
* @param pojoClass
* @param params
* @return
*/
public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
FileInputStream in = null;
try {
in = new FileInputStream(file);
return new ExcelImportService().importExcelByIs(in, pojoClass, params, false).getList();
} catch (ExcelImportException e) {
throw new ExcelImportException(e.getType(), e);
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
throw new ExcelImportException(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(in);
}
}
/**
* Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
*
* @param inputstream
* @param pojoClass
* @param params
* @return
* @throws Exception
*/
public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass,
ImportParams params) throws Exception {
return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, false).getList();
}
/**
* Excel 导入 数据源IO流 字段类型 Integer,Long,Double,Date,String,Boolean
* 支持校验,支持Key-Value
*
* @param inputstream
* @param pojoClass
* @param params
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream,
Class<?> pojoClass,
ImportParams params) throws Exception {
return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, true);
}
/**
* Excel 导入 数据源本地文件 字段类型 Integer,Long,Double,Date,String,Boolean
* 支持校验,支持Key-Value
* @param file
* @param pojoClass
* @param params
* @return
*/
public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass,
ImportParams params) {
FileInputStream in = null;
try {
in = new FileInputStream(file);
return new ExcelImportService().importExcelByIs(in, pojoClass, params, true);
} catch (ExcelImportException e) {
throw new ExcelImportException(e.getType(), e);
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
throw new ExcelImportException(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(in);
}
}
/**
* Excel 通过SAX解析方法,适合大数据导入,不支持图片
* 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
*
* @param inputstream
* @param pojoClass
* @param params
* @return
*/
public static <T> List<T> importExcelBySax(InputStream inputstream, Class<?> pojoClass,
ImportParams params) {
return new SaxReadExcel().readExcel(inputstream, pojoClass, params, null, null);
}
/**
* Excel 通过SAX解析方法,适合大数据导入,不支持图片
* 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
*
* @param inputstream
* @param pojoClass
* @param params
* @param hanlder
*/
@SuppressWarnings("rawtypes")
public static void importExcelBySax(InputStream inputstream, Class<?> pojoClass,
ImportParams params, IExcelReadRowHandler hanlder) {
new SaxReadExcel().readExcel(inputstream, pojoClass, params, null, hanlder);
}
/**
* Excel 通过SAX解析方法,适合大数据导入,不支持图片
* 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
*
* @param inputstream
* @param rowRead
* @return
*/
public static <T> List<T> importExcelBySax(InputStream inputstream, ISaxRowRead rowRead) {
return new SaxReadExcel().readExcel(inputstream, null, null, rowRead, null);
}
}
本次的分享就到这里,今后将继续更新