easyExcel导⼊导出功能，并校验
**
⼀、导⼊**
1. 导⼊插件
<dependency>
<groupId>javax.el</groupId>
<artifactId>javax.el-api</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>org.glassfish</groupId>
<artifactId>javax.el</artifactId>
<version>3.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2.创建实体
@Data
@HeadRowHeight(0)
public class TestModel {
@ExcelProperty(value = "⽇期", index = 0)
@NotNull(message = "⽇期不能为空")
private Date disDate;
@ExcelProperty(value = "违章类型", index = 1)
@NotBlank(message = "违章类型不能为空")
private String type;
@ExcelProperty(value = "单位类型", index = 2)
@NotBlank(message = "单位类型不能为空")
private String deptType;
}
注：1) @HeadRowHeight(0)表⽰⾏⾼，@NotNull和@NotBlank⽤的是hibernate的validation验证框架，⼤家可以百度下2个注解的区别
3.创建监听类
public class TestListener extends AnalysisEventListener<TestModel > {
private final static Logger LOGGER = Logger(TestListener .class);
private ArchivesService archivesService;
private DeptService deptService;
private CustomerService userService;
private DictionaryService dictionaryService;
private String uid;
private static final int BATCH_COUNT = 1000;
List<TestModel > successList = new ArrayList<>();
//有参构造，便于servcie层参⼊，⽤于数据库操作
public TrafficViolationListener(DictionaryService dictionaryService, ArchivesService archivesService, CustomerService userService, DeptService deptSer vice, String uid) {
this.dictionaryService = dictionaryService;
this.archivesService = archivesService;
this.userService = userService;
this.deptService = deptService;
this.uid = uid;
}
@Override
public void invoke(TestModel testModel , AnalysisContext analysisContext) {
/*
⼤家如果不想批量写⼊，可以直接对testModel 操作。invoke⽅法是读取excel的每⾏数据
*/
String errMsg;
try {
//根据excel数据实体中的javax.validation + 正则表达式来校验excel数据(格式校验：字符长度，类型，格式，侏儒时间，⼿机号，⾝份证都可进⾏校验) errMsg = EasyExcelValiHelper.validateEntity(testModel);
} catch (NoSuchFieldException e) {
errMsg = "解析数据出错";
LOGGER.info("解析数据出错" + e.getMessage());
}
//将解析完成后数据放⼊list
if (!StringUtils.isEmpty(errMsg)) {
successList.add(testModel);
}
//批量写⼊数据库
if(successList>BATCH_COUNT){
saveData(successList);
//清空list防⽌oom
successList.clear();
}
public void saveData(List<testModel> testModelList) {
具体的业务操作（如写⼊数据库）
}
/**
* excel解析完成后，会调⽤
*
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//防⽌⼩于BATCH_COUNT的数据遗漏
saveData(successList);
successList.clear();
LOGGER.info("所有数据解析完成！");
}
}
4.数据验证类
/**
* 数据格式验证类
* @author zhuobin
* @date 2020/12/10 15:18
*/
public class EasyExcelValiHelper {
private EasyExcelValiHelper(){}
private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
public static <T> String validateEntity(T obj) throws NoSuchFieldException {
StringBuilder result = new StringBuilder();
Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class);
if (set != null && !set.isEmpty()) {
for (ConstraintViolation<T> cv : set) {
Field declaredField = Class().PropertyPath().toString()); ExcelProperty annotation = Annotation(ExcelProperty.class);
//拼接错误信息，包含当前出错数据的标题名字+错误信息
result.append(annotation.value()[0]+cv.getMessage()).append(";");
}
}
String();
}
}
5…测试调⽤
public static void main(String[] args) {
//创建⼯作簿对象
ExcelReader excelReader = null;
//创建输⼊流
InputStream in = null;
//这⾥根据⾃⼰项⽬实际情况获取要导⼊的⽂件地址
String newFileName="⽂件地址";
try {
in = new FileInputStream(newFileName);
excelReader = ad(newFileName).build();
//读取excel sheet1数据
ReadSheet readSheet1 = adSheet(0).head(testModel.class)
.registerReadListener(new TestListener (dictionaryService,archivesService, userService, deptService, uid)).headRowNumber(1).build(); ad(readSheet1);
//获取sheet页总⾏数
Integer totaoRow = excelReader.analysisContext().readSheetHolder().getApproximateTotalRowNumber();
//判断sheet页数据是否为空；getCurrentRowAnalysisResult为null，如果有数据会返回TrafficViolationModel
if (totaoRow <= 2 && excelReader.analysisContext().readRowHolder().getCurrentRowAnalysisResult() == null) {
throw new VException(Constants.TRAFFIC_VIOLATION_EXCEL_IS_NULL, ErrorCode.TRAFFIC_VIOLATION_IMPORT_IS_NULL_ERROR );
}
} catch (IOException e) {
<(e.getMessage());
throw new VException(Constants.TRAFFIC_VIOLATION_EXCEL_PARSE_NULL, ErrorCode.TRAFFIC_VIOLATION_IMPORT_IS_ERROR);
} finally {
try {
if (in != null) {
in.close();
}
// 这⾥⼀定别忘记关闭，读的时候会创建临时⽂件，到时磁盘会崩
if (excelReader != null) {
excelReader.finish();
}
} catch (IOException e) {
<(e.getMessage());
throw e;
}
}
}
⼆、导出
getsavefilename1.创建实体类模板：
同导⼊
2.创建导出公共⽅法
/**
* excel导出公有类
*
* @author zhuobin
* @date 2020/11/26
*/
public class ExcelUtil {
private final static Logger LOGGER = Logger(ExcelUtil.class);
/**
* 使⽤模型来写⼊Excel 单sheet
*
* @param outputStream Excel的输出流
* @param dataList 要写⼊的数据
* @param clazz 模型的类
*/
public static void writeExcelWithModel(OutputStream outputStream, String sheetName, List<? extends Object> dataList, Class<? extends Object> clazz) throws IOException {
throws IOException {
try {
//这⾥指定需要表头，因为model通常包含表头信息
ExcelWriter writer = EasyExcel.write(outputStream, clazz).build();
//创建writeSheet，设置基本信息
WriteSheet writeSheet = new WriteSheet();
writeSheet.setSheetName(sheetName);
writer.write(dataList, writeSheet);
writer.finish();
} catch (Exception e) {
<(e.getMessage());
} finally {
try {
// 关闭流
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
<(e.getMessage());
}
}
}
/**
* 多sheet页导出
* @param writer 写⼊类
* @param dataList 要导出的数据
* @param clazz 实体类
* @param sheetNo sheet页
* @param sheetName sheet名字
*/
public static void writeUnitExcel(ExcelWriter writer, List<? extends Object> dataList, Class<? extends Object> clazz,Integer sheetNo, String sheetName){ try {
WriteSheet writeSheet=EasyExcel.writerSheet(sheetNo,sheetName).head(clazz).build();
writer.write(dataList, writeSheet);
} catch (Exception e) {
<(e.getMessage());
}
}
/**
* 导出结束关闭流
*
* @param out
* @param excelWriter
* @throws IOException
*/
public static void finishWriter(OutputStream out, ExcelWriter excelWriter) throws IOException {
out.flush();
excelWriter.finish();
out.close();
}
/**
* 创建导出⽂件地址
*
* @return
*/
public static String getFileName(String fileName, String path) {
String exportDir = VcloudApplication.DATA_DIR + path;
File exportDirFile = new File(exportDir);
exportDirFile.mkdirs();
String newExportPath = exportDir + fileName;
return newExportPath;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论