This article is participating in the Java Theme Month – Java Debug Notes EventActive link
Recently, we are working on the Excel import function, and the product requires that the imported data should be verified before being put into the database. So a simple encapsulation of a tool, the results of the brothers have agreed to use today to share ideas.
Easyexcel library
We all know that POI is the basic library for Java to manipulate Excel. There is no customization for versatility, and there are some limitations. After some research, we decided to use easyExcel for business development.
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
Copy the codeEasyexcel abstracts the life cycle of reading Excel into several phases, making it easy for us to inject the logic you want to implement in each phase. These phases are contained in the ReadListener interface
public interface ReadListener<T> extends Listener {
/** * When any listener executes an error report, all listeners receive this method. If an exception is thrown here, the entire read terminates. * Here is the * * that handles the exception of reading Excel@param exception
* @param context
* @throws Exception
*/
void onException(Exception exception, AnalysisContext context) throws Exception;
/** * This method is executed when each row of Excel header is read **@param headMap
* @param context
*/
void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context);
/** * this method is invoked when each row is read **@param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
* analysis context
*/
void invoke(T data, AnalysisContext context);
/** * this method is used to process ** if additional cell information is returned@param extra
* extra information
* @param context
* analysis context
*/
void extra(CellExtra extra, AnalysisContext context);
/** * The logic executed after the entire Excel sheet has been parsed. * *@param context
*/
void doAfterAllAnalysed(AnalysisContext context);
/** * controls whether to read the next line of policy **@param context
* @return* /
boolean hasNext(AnalysisContext context);
}
Copy the codeThe abstract implementation AnalysisEventListener
provides a more appropriate abstraction, which I will further implement for Excel import and validation.
Once you understand a framework’s abstract interface, try to see if it has an implementation that meets your needs.
In addition, the AnalysisContext in the interface contains a lot of useful context meta-information, such as the current row, the current configuration policy, the overall Structure of Excel, and so on, that you can call as needed.
JSR303 check
At first, I wrote an abstract verification tool by myself, and finally FOUND that each field had to write its specific verification logic. If the number of fields in Excel exploded, it might be a nightmare for development. This brings me to the existing specification in the industry, the JSR303 Validation specification, which abstractions the data Model and Validation separately, making it very flexible and significantly less workload. We just need to find a place to integrate with the esayExcel lifecycle. We only need to introduce the following dependencies to integrate JSR303 validation into our Spring Boot project:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
Copy the codeSee my other article for a tutorial on JSR303
Train of thought
We can validate each field as it is parsed, corresponding to the ReadListener invoke(T Data, AnalysisContext Context) method, which implements the strategy of stopping Excel parsing when field validation triggers a constraint; The other can perform verification after Excel parsing, corresponding to doAfterAllAnalysed(AnalysisContext Context). So let’s take the second one and implement it.
When we write code, try to have a single responsibility, a class or a method that does one thing, so that our code is clear enough.
Write validation processing classes
Here I parse and verify separate implementation, first write JSR303 check tool. It is assumed that there has been a Validator javax.mail. Validation. The realization of the Validator, later I speak this implementation where injection.
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 check tool **@param <T> the type parameter
* @author felord.cn
* @since14 14:14 2021/4 / * /
@AllArgsConstructor
public class ExcelValidator<T> {
private final Validator validator;
private final Integer beginIndex;
/** * set check **@paramData Sets to be verified *@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;
}
/** * here is the basic method of verification **@paramIndex Indicates the row number *@paramData Indicates the data to be verified *@returnThe system prompts data verification exceptions. If a verification rule is triggered, the system encapsulates the information. * /
private String doValidate(int index, T data) {
// Here uses JSR303's validator, at the same time uses the group validator, Excel as the group identifier
Set<ConstraintViolation<T>> validate = validator.validate(data, Excel.class);
return validate.size()>0 ? "The first" + index +
"Ok, trigger constraint:" + validate.stream()
.map(ConstraintViolation::getMessage)
.collect(Collectors.joining(",")) :""; }}Copy the codeThe above is the logic of the whole checksum. If the checksum passes, no message is displayed. If the checksum fails, the checksum constraint information is encapsulated and returned. Where did the Validator come from? When Spring Boot is integrated with JSR303 there is a Validator implementation that is automatically injected into Spring IoC and we can take advantage of it.
Implement AnalysisEventListener
This is completely easyExcel’s function, we just need to implement AnalysisEventListener, and add the parsed field to the collection, and then verify after the complete parsing. If the verification fails, an exception with verification information will be thrown, and the exception will be returned to the front end after processing.
Remember: The implementation of AnalysisEventListener cannot inject Spring IoC.
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;
/** * This class cannot be hosted by Spring **@param <T> the type parameter
* @author felord.cn
* @since2021/4 14 [/ * /
public class JdbcEventListener<T> extends AnalysisEventListener<T> {
/** * Excel total number threshold */
private static final Integer MAX_SIZE = 10000;
/** ** Check tool */
private final ExcelValidator<T> excelValidator;
/** * If the excel data obtained through consumption parsing */
private final Consumer<Collection<T>> batchConsumer;
/** * temporary storage container for parsing data */
private final List<T> list = new ArrayList<>();
/**
* Instantiates a new Jdbc event listener.
*
* @paramExcel Validator *@paramBatchConsumer Excel analysis results batch consumption tool, can be implemented as writing to the database and other consumption operations */
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) {
// Add parsed Excel fields to the collection if the threshold is not exceeded
if (list.size() >= MAX_SIZE) {
throw new ServiceException("The number of uploads at a time shall not exceed:" + MAX_SIZE);
}
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// After all parses are completed, the collection is validated and consumed
if(! CollectionUtils.isEmpty(this.list)) {
List<String> validated = this.excelValidator.validate(this.list);
if (CollectionUtils.isEmpty(validated)) {
this.batchConsumer.accept(this.list);
} else {
throw newServiceException(JSONUtil.toJsonStr(validated)); }}}}Copy the codeEncapsulate the final tool
Here refer to the esayExcel documentation package as a general-purpose Excel reader
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 reader **@author felord.cn
* @since14 therefore 2021/4 / * /
@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();
}
/** * Parse the required metadata **@param <T> the type parameter
*/
@Data
public static class Meta<T> {
/** * Excel file streams */
private InputStream excelStream;
/** * Excel header line number, refer to easyExcel API and your actual situation */
private Integer headRowNumber;
/** * Corresponding to excel encapsulated data class, need to refer to easyExcel tutorial */
private Class<T> domain;
/** * Parses the result of the consumption function */
privateConsumer<Collection<T>> consumer; }}Copy the codeWe injected this tool into Spring IoC for our convenience.
/** ** Excel reader **@param validator the validator
* @return the excel reader
*/
@Bean
public ExcelReader excelReader(Validator validator) {
return new ExcelReader(validator);
}
Copy the codeWrite the interface
Here Excel data class ExcelData is not described, too simple! Just look at the esayExcel documentation. Writing a Spring MVC interface example is, yes, that simple.
@Autowired
private ExcelReader excelReader;
@Autowired
private DataService dataService;
@PostMapping("/excel/import")
publicRest<? > 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);
// Batch write database logic
excelDataMeta.setConsumer(dataService::saveBatch);
this.excelReader.read(excelDataMeta);
return RestBody.ok();
}
Copy the codeconclusion
Today I demonstrated how to combine easyExcel and JSR303. In fact, the principle is very simple, you just need to find the junction of the two technologies and combine them. Have you learned it? Please like, follow, retweet, and support more: code farmer Xiao Pang learns more useful skills.