Recently in doing Excel import function, the product requires the imported data to be verified first and then into the library. So a simple package of tools, we think it’s not bad. Today, we will share the ideas.
easyexcel library
We all know that POI is the base library for Java manipulation of Excel. It is not customized for generality and has some limitations. After some research it was decided to use the secondary wrapper library easyexcel for business development.
1
2
3
4
5
|
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
|
easyexcel abstracts the lifecycle of reading Excel into several phases, making it easy to inject the logic you want to implement in each phase. These stages are contained in the ReadListener interface.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
public interface ReadListener<T> extends Listener {
/**
* 当任何一个侦听器执行错误报告时，所有侦听器都将接收此方法。 如果在此处引发异常，则整个读取将终止。
* 这里是处理读取excel异常的
*
* @param exception
* @param context
* @throws Exception
*/
void onException(Exception exception, AnalysisContext context) throws Exception;
/**
* 读取每行excel表头时会执行此方法
*
* @param headMap
* @param context
*/
void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context);
/**
* 读取每行数据的时候回执行此方法
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
* analysis context
*/
void invoke(T data, AnalysisContext context);
/**
* 如果有额外的单元格信息返回就用此方法处理
*
* @param extra
* extra information
* @param context
* analysis context
*/
void extra(CellExtra extra, AnalysisContext context);
/**
* 在整个excel sheet解析完毕后执行的逻辑。
*
* @param context
*/
void doAfterAllAnalysed(AnalysisContext context);
/**
* 用来控制是否读取下一行的策略
*
* @param context
* @return
*/
boolean hasNext(AnalysisContext context);
}
|
Its abstract implementation AnalysisEventListener<T> provides more abstraction to meet the needs, I will further implement this abstraction to implement the Excel import and verification.
After you understand the abstract interface of a framework, try to see if it has an implementation that meets your needs.
Another point to make here, the interface AnalysisContext contains a lot of useful contextual meta-information, such as the current row, the current configuration policy, the overall structure of excel and other information, you can call these information when needed.
JSR303 Validation
After initially writing an abstract validation tool myself, I eventually realized that each field had to be written with its own specific validation logic, which could be a nightmare for development if the amount of fields in an Excel exploded. This led me to an existing specification in the industry - the JSR303 validation specification, which abstracts the data model (Model) and the validation (Validation) separately, making it very flexible and significantly less work. All we need to do is to find a place to integrate with the esayexcel lifecycle. We just need to introduce the following dependencies to integrate JSR303 validation in our Spring Boot project.
1
2
3
4
|
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
|
Ideas
We can validate each field as it is parsed, which corresponds to the invoke(T data, AnalysisContext context) method of the ReadListener. This way we can implement the strategy of stopping excel parsing when the field validation triggers a constraint; the other way is to perform the validation after Excel is parsed, which corresponds to the doAfterAllAnalysed(AnalysisContext context). The other way is to perform the validation after Excel is parsed, which corresponds to doAfterAllAnalysed(AnalysisContext context). Let’s take the second one as an example and implement it.
When we write the code, try to have a single responsibility, a class or a method try to do only one thing, so that their code is clear enough.
Write a checksum processing class
Here I implement parsing and validation separately, and write the JSR303 validation tool first. It is assumed here that there is already an implementation of the validator javax.validation.Validator, I will talk about where to inject this implementation later .
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
|
import cn.felord.validate.Excel;
import lombok.AllArgsConstructor;
import org.springframework.util.StringUtils;
import javax.validation.ConstraintViolation;
import javax.validation.Validator;
import java.util.*;
import java.util.stream.Collectors;
/**
* excel 校验工具
*
* @param <T> the type parameter
* @author felord.cn
* @since 2021 /4/14 14:14
*/
@AllArgsConstructor
public class ExcelValidator<T> {
private final Validator validator;
private final Integer beginIndex;
/**
* 集合校验
*
* @param data 待校验的集合
* @return list
*/
public List<String> validate(Collection<T> data) {
int index = beginIndex + 1;
List<String> messages = new ArrayList<>();
for (T datum : data) {
String validated = this.doValidate(index, datum);
if (StringUtils.hasText(validated)) {
messages.add(validated);
}
index++;
}
return messages;
}
/**
* 这里是校验的根本方法
*
* @param index 本条数据所在的行号
* @param data 待校验的某条数据
* @return 对数据的校验异常进行提示，如果有触发校验规则的会封装提示信息。
*/
private String doValidate(int index, T data) {
// 这里使用了JSR303的的校验器，同时使用了分组校验，Excel为分组标识
Set<ConstraintViolation<T>> validate = validator.validate(data, Excel.class);
return validate.size()>0 ? "第" + index +
"行，触发约束：" + validate.stream()
.map(ConstraintViolation::getMessage)
.collect(Collectors.joining(",")): "";
}
}
|
The above is the logic of the whole validation, if the validation passes no message is prompted, if the validation does not pass the constraint information of the validation is wrapped and returned. Where does the Validator come from here? When Spring Boot is integrated with JSR303 there is a Validator implementation that is automatically injected into Spring IoC and we can use it.
Implementing AnalysisEventListener
This is entirely an easyexcel function, we just need to implement AnalysisEventListener and add the parsed fields to the collection, and then check it when the parsing is complete. Here, if the validation does not pass, an exception will be thrown with the validation information, and the exception will be processed and returned to the front-end prompt.
Remember: the AnalysisEventListener implementation cannot be injected into Spring IoC.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import cn.felord.exception.ServiceException;
import org.springframework.util.CollectionUtils;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.function.Consumer;
/**
* 该类不可被Spring托管
*
* @param <T> the type parameter
* @author felord.cn
* @since 2021 /4/14 14:19
*/
public class JdbcEventListener<T> extends AnalysisEventListener<T> {
/**
* Excel总条数阈值
*/
private static final Integer MAX_SIZE = 10000;
/**
* 校验工具
*/
private final ExcelValidator<T> excelValidator;
/**
* 如果校验通过消费解析得到的excel数据
*/
private final Consumer<Collection<T>> batchConsumer;
/**
* 解析数据的临时存储容器
*/
private final List<T> list = new ArrayList<>();
/**
* Instantiates a new Jdbc event listener.
*
* @param excelValidator Excel校验工具
* @param batchConsumer Excel解析结果批量消费工具，可实现为写入数据库等消费操作
*/
public JdbcEventListener(ExcelValidator<T> excelValidator, Consumer<Collection<T>> batchConsumer) {
this.excelValidator = excelValidator;
this.batchConsumer = batchConsumer;
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
list.clear();
throw exception;
}
@Override
public void invoke(T data, AnalysisContext context) {
// 如果没有超过阈值就把解析的excel字段加入集合
if (list.size() >= MAX_SIZE) {
throw new ServiceException("单次上传条数不得超过：" + MAX_SIZE);
}
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//全部解析完毕后 对集合进行校验并消费
if (!CollectionUtils.isEmpty(this.list)) {
List<String> validated = this.excelValidator.validate(this.list);
if (CollectionUtils.isEmpty(validated)) {
this.batchConsumer.accept(this.list);
} else {
throw new ServiceException(JSONUtil.toJsonStr(validated));
}
}
}
}
|
The documentation for esayexcel is referenced here to package it into a generic Excel reading tool.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
import com.alibaba.excel.EasyExcel;
import lombok.AllArgsConstructor;
import lombok.Data;
import javax.validation.Validator;
import java.io.InputStream;
import java.util.Collection;
import java.util.function.Consumer;
/**
* excel读取工具
*
* @author felord.cn
* @since 2021 /4/14 15:10
*/
@AllArgsConstructor
public class ExcelReader {
private final Validator validator;
/**
* Read Excel.
*
* @param <T> the type parameter
* @param meta the meta
*/
public <T> void read(Meta<T> meta) {
ExcelValidator<T> excelValidator = new ExcelValidator<>(validator, meta.headRowNumber);
JdbcEventListener<T> readListener = new JdbcEventListener<>(excelValidator, meta.consumer);
EasyExcel.read(meta.excelStream, meta.domain, readListener)
.headRowNumber(meta.headRowNumber)
.sheet()
.doRead();
}
/**
* 解析需要的元数据
*
* @param <T> the type parameter
*/
@Data
public static class Meta<T> {
/**
* excel 文件流
*/
private InputStream excelStream;
/**
* excel头的行号，参考easyexcel的api和你的实际情况
*/
private Integer headRowNumber;
/**
* 对应excel封装的数据类，需要参考easyexcel教程
*/
private Class<T> domain;
/**
* 解析结果的消费函数
*/
private Consumer<Collection<T>> consumer;
}
}
|
We inject this tool into Spring IoC to facilitate our use.
1
2
3
4
5
6
7
8
9
10
|
/**
* Excel 读取工具
*
* @param validator the validator
* @return the excel reader
*/
@Bean
public ExcelReader excelReader(Validator validator) {
return new ExcelReader(validator);
}
|
Writing interfaces
Here the Excel data class ExcelData will not be repeated, too simple! Just go to the esayexcel documentation. Write a sample Spring MVC interface, yes it is that simple.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
@Autowired
private ExcelReader excelReader;
@Autowired
private DataService dataService;
@PostMapping("/excel/import")
public Rest<?> importManufacturerInfo(@RequestPart MultipartFile file) throws IOException {
InputStream inputStream = file.getInputStream();
ExcelReader.Meta<ExcelData> excelDataMeta = new ExcelReader.Meta<>();
excelDataMeta.setExcelStream(inputStream);
excelDataMeta.setDomain(ExcelData.class);
excelDataMeta.setHeadRowNumber(2);
// 批量写入数据库的逻辑
excelDataMeta.setConsumer(dataService::saveBatch);
this.excelReader.read(excelDataMeta);
return RestBody.ok();
}
|
Summary
Today we demonstrated how to combine easyexcel and JSR303, in fact the principle is very simple, you just need to find the combination of the two technologies and combine them, have you learned?