Writing in the front
Java backend programmers should encounter the need to read Excel information into DB, etc. Apache POI may suddenly come to mind as a technical solution, but when the volume of Excel data is very large, you may find that, POI is to read the entire Excel content into the memory, so the memory consumption is very serious. If the Excel read operation contains a large amount of data at the same time, it is easy to cause memory overflow problem
But the emergence of EasyExcel is a good solution to POI related problems. Originally, a 3M Excel needs about 100M memory with POI, while EasyExcel can reduce it to a few M, and no matter how big the Excel is, there will be no memory overflow. Because you’re reading Excel line by line. (As usual, don’t worry about the image below, just keep it in mind. It’s easy to go back to it after the use cases below.)
In addition, EasyExcel does the encapsulation of model transformation in the upper layer, which does not need cell and other related operations, making the user more simple and convenient, and see
Easy to read
Suppose we have the following in Excel:
We need to create a new User entity and add member variables to it
@Data
public class User {
/** * name */
@ExcelProperty(index = 0)
private String name;
/** * age */
@ExcelProperty(index = 1)
private Integer age;
}
Copy the codeYou may have noticed that the @excelProperty annotation, which uses the index attribute (0 for the first column, and so on), also supports matching by “column name” name, for example:
@ExcelProperty("Name")
private String name;
Copy the codeFollow the github documentation:
It is not recommended to use index and name at the same time. Either an object uses index or an object uses name for matching
- If the Excel template information column to be read is fixed, it is recommended to use index as the form, because if the name is used to match, the name will be repeated, resulting in only one field to read data, so index is a safer way
- If the Column index of the Excel template changes frequently, it is better to use the name method instead of constantly changing the index value of the entity’s annotation
So you can choose according to your own situation
Write test cases
EasyExcel class overload many read methods, here is not a list of instructions, please check yourself; The sheet method can also specify sheetNo, which is the information for the first sheet by default
The new UserExcelListener() of the code above stands out, which is the key to EasyExcel reading Excel line by line, custom UserExcelListener inheritance AnalysisEventListener
@Slf4j
public class UserExcelListener extends AnalysisEventListener<User> {
/** * Batch processing threshold */
private static final int BATCH_COUNT = 2;
List<User> list = new ArrayList<User>(BATCH_COUNT);
@Override
public void invoke(User user, AnalysisContext analysisContext) {
log.info("Parse to a single data :{}", JSON.toJSONString(user));
list.add(user);
if(list.size() >= BATCH_COUNT) { saveData(); list.clear(); }}@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("All data parsed!");
}
private void saveData(a){
log.info("{} data, start storing database!", list.size());
log.info("Database saved successfully!"); }}Copy the codeRefer back to the EasyExcel schematic at the beginning of this article. The Invoke method reads data line by line, corresponding to subscriber 1; The doAfterAllAnalysed method corresponds to subscriber 2, do you understand?
Print result:
It can be seen from here that although the data is parsed line by line, we can customize the threshold value to complete the batch processing operation of the data, which shows the flexibility of EasyExcel operation
Custom converter
This is basic data reading and writing, and our business data is often not that simple, and sometimes even needs to be converted into programmatically readable data
Gender information transfer
For example, the new “Gender” column in Excel is male/female. We need to convert the gender information in Excel into the program information: “1: male; 2: female”.
First add the member variable gender to the User entity:
@ExcelProperty(index = 2)
private Integer gender;
Copy the codeWith EasyExcel, we can customize The Converter to convert excel content into information needed by our program. GenderConverter is created here to convert gender information
public class GenderConverter implements Converter<Integer> {
public static final String MALE = "Male";
public static final String FEMALE = "Female";
@Override
public Class supportJavaTypeKey(a) {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey(a) {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String stringValue = cellData.getStringValue();
if (MALE.equals(stringValue)){
return 1;
}else {
return 2; }}@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return null; }}Copy the codeThe generics of the Converter interface in the above program refer to the Java data type to be converted, which is the same as the return value type in the supportJavaTypeKey method
Open the @excelProperty annotation to see that the annotation supports custom Converter, so we add the gender member variable to the User entity and specify Converter
/** * Gender 1: male; 2: the female * /
@ExcelProperty(index = 2, converter = GenderConverter.class)
private Integer gender;
Copy the codeTo see the results:
The data was transformed as we expected, and as you can see here, Converter can define convenience everywhere at once
Date information conversion
Date information is also our common conversion data. For example, when the “date of birth” column is added in Excel, we need to parse it into YYYY-MM-DD format, and we need to format it. EasyExcel uses @dateTimeFormat annotation to format it
Add the birth member variable to the User entity and apply the @dateTimeFormat annotation to format it as required
/** * Date of birth */
@ExcelProperty(index = 3)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private String birth;
Copy the codeTo see the results:
If you specify Date as the birth type here, what do you get?
As a Java Web developer, especially under the current mainstream Spring Boot architecture, how to achieve the Web way to read Excel information?
The web to read
Simple Web
Simply move the key code for the test case to the Controller, create a new UserController, and add the Upload method to it
@RestController
@RequestMapping("/users")
@Slf4j
public class UserController {
@PostMapping("/upload")
public String upload(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new UserExcelListener()).sheet().doRead();
return "success"; }}Copy the codeWhen writing test cases, you may have noticed that the listener is passed as a new parameter to the EasyExcel. Read method, which is not in accordance with Spring IoC rules. After reading Excel data, we usually write some business logic for the read data, and the business logic is usually written in the Service layer. How do we call our Service code in the listener?
** Don’t look down just yet, what do you have in mind? 支那
Anonymous inner class mode
The anonymous inner class is the simplest way, we need to create the Service layer information first:
public interface IUser {
public boolean saveData(List<User> users);
}
Copy the codeCreate a new IUser interface implementation class UserServiceImpl:
@Service
@Slf4j
public class UserServiceImpl implements IUser {
@Override
public boolean saveData(List<User> users) {
log.info("UserService {} data, start storing database!", users.size());
log.info(JSON.toJSONString(users));
log.info("UserService saved database successfully!");
return true; }}Copy the codeNext, inject IUser into Controller:
@Autowired
private IUser iUser;
Copy the codeModify the Upload method to override the Listener method as an anonymous inner class:
@PostMapping("/uploadWithAnonyInnerClass")
public String uploadWithAnonyInnerClass(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new AnalysisEventListener<User>(){
/** * Batch processing threshold */
private static final int BATCH_COUNT = 2;
List<User> list = new ArrayList<User>();
@Override
public void invoke(User user, AnalysisContext analysisContext) {
log.info("Parse to a single data :{}", JSON.toJSONString(user));
list.add(user);
if(list.size() >= BATCH_COUNT) { saveData(); list.clear(); }}@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("All data parsed!");
}
private void saveData(a){
iUser.saveData(list);
}
}).sheet().doRead();
return "success";
}
Copy the codeView the results:
This implementation, in fact, is just rewriting everything in the listener and presenting it in the controller. Isn’t it very uncomfortable when you look at such a bloated controller? Obviously this is not our best coding implementation
The constructor passes parameters
In the previous analysis of the source code returned by Spring boot, I do not know if you have noticed that most of the Spring source code is passed as a constructor, so we can add a parameter constructor to the listener. Pass the dependency injected IUser from the Controller as a constructor to the listener:
@Slf4j
public class UserExcelListener extends AnalysisEventListener<User> {
private IUser iUser;
public UserExcelListener(IUser iUser){
this.iUser = iUser;
}
// omit the corresponding code...
private void saveData(a){
iUser.saveData(list); // Call saveData in userService
}
Copy the codeChange the Controller method:
@PostMapping("/uploadWithConstructor")
public String uploadWithConstructor(MultipartFile file) throws IOException {
EasyExcel.read(file.getInputStream(), User.class, new UserExcelListener(iUser)).sheet().doRead();
return "success";
}
Copy the codeOperation result: same as above
The Controller code looks pretty clean with this change, but should we keep adding parameter constructors if there are other services to inject later in the business? Obviously, this approach is also not very flexible.
In fact, when using anonymous inner classes, you might think that we could solve this problem with Java8 lambda
Lambda mass participation
In order to solve the pain point of constructor parameter passing and to make the listener more universal, it is not necessary to create a new listener for each Excel business because the listener reads Excel data line by line. All we need to do is pass in our business logic code to the Listener, so we need to use Consumer
as an argument to construct the Listener.
Create a new tool class ExcelDemoUtils to construct a listener:
As we can see, the getListener method takes a Consumer
> argument, so that our business logic will be executed when the following code is called:
consumer.accept(linkedList);
Copy the codeContinue modifying the Controller method:
Operation result: same as above
At this point, we just need to customize the business logic in the batchInsert method:
- Meet the brevity of Controller RESTful apis
- The Listener is more general and flexible. It acts more like an abstract class, leaving the concrete logic to the implementation of abstract methods
- The business logic is also more scalable and clearer
conclusion
Here, on how to use EasyExcel to read the basic use of Excel information has been introduced, there are a lot of details are not said, we can consult EasyExcel Github document to find more content. Flexible use of Java 8’s functional interfaces makes it easier to increase your code’s reusability while looking more concise and canonical
In addition to reading Excel read, and Excel write, if you need to write it to the specified location, with HuTool tool class FileWriter is very convenient, for the use of EasyExcel, if you have any questions, also welcome to the blog below discussion
For the complete code, please reply to “Demo” on the official account, click the link, and check the contents of “easy-Excel-Demo” folder. In addition, due to special reasons, the homepage of my blog is temporarily closed, other directories are all normal, more articles can be viewed from the entrance of Dayarch. top/archives
Thank you
Many thanks to the authors of EasyExcel 🌹🌹 for making Excel easier to read and write
Soul asking
- In addition to Consumer, if you need business logic that returns values, which functional interface do you use?
- How to deal with complex table headers?
- How to write DB data to Excel and download it?
- What have you learned from the design of EasyExcel
Efficiency tools
Recommended reading
- Don’t miss this step into the world of concurrency
- A thorough understanding of these three cores is the key to learning concurrent programming
- There are three sources of concurrency bugs, so keep your eyes open for them
- Visibility order, happens-before
- Solve the atomic problem? The first thing you need is a macro understanding
- What should we talk about in an interview with volatile keywords?
Welcome to continue to pay attention to the public account: “One soldier of The Japanese Arch”
- Cutting-edge Java technology dry goods sharing
- Efficient tool summary | back “tool”
- Interview question analysis and solution
- Technical data to receive reply | “data”
To read detective novel thinking easy fun learning Java technology stack related knowledge, in line with the simplification of complex problems, abstract problems concrete and graphical principles of gradual decomposition of technical problems, technology continues to update, please continue to pay attention to……
2019.10.24 Additional remarks
I saw some confusion about Lambda in the comments section, so I added a separate explanation here
To be perfectly honest, I couldn’t write this way at first either, thanks to my friend’s on-demand video, thank you in advance.
The birth of Lambda makes it so much easier for us to deal with collections, and we deal with collections every day, that it’s easy to forget that Lambda functional interfaces are designed to solve the problem of code bloat in anonymous inner classes, () -> system.out.println (” Hello world”);
public void testRunnable(Runnable runnable){
runnable.run();
}
@Test
public void callTestRunnable1(a){
testRunnable(new Runnable() {
@Override
public void run(a) {
System.out.println("hello Runnable"); }}); }@Test
public void callTestRunnable(a){
testRunnable(() -> System.out.println("hello Runnable"));
}
Copy the codeA friend here might say how do I convert the AnalysisEventListener anonymous inner class to Lambda mode? The answer is no, this violates the definition of a functional interface (there is only one abstract method, all the others are default). Looking back at the code above, I just put the anonymous inner class approach into the utility class ExcelDemoUtils. This approach is an improvement over the traditional approach, we use Java generics. We can handle any entity except the User entity, look at the code:
public static <T> AnalysisEventListener<T> getListener(a) {
return new AnalysisEventListener<T>() {
private static final int BATCH_COUNT = 2;
List<T> list = new ArrayList<T>();
@Override
public void invoke(T user, AnalysisContext analysisContext) {
log.info("Parse to a single data :{}", JSON.toJSONString(user));
list.add(user);
if(list.size() >= BATCH_COUNT) { saveData(); list.clear(); }}@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("All data parsed!");
}
private void saveData(a){
log.info("{} data, start storing database!", list.size());
log.info("Database saved successfully!"); }}; }Copy the codeThe utility class written here does not have the possibility of business landing, usually we need to read the Excel content (list) to execute the specific business logic and finally persist to DB, this sentence according to Lambda translation is (list) -> specific business logic execution
This matches the accept(T T) method of the Consumer interface, so we’ll just add a Consumer parameter to the getListener method above, To call the getListener method, we need to pass the argument of type Consumer. When the consumer.accept method is executed, the business logic in the batchInsert method we wrote is executed
So, when the basic execution logic is fixed, and only parts of it need to be handled by a particular business, we can use functional interfaces to pass parameters, just remember this mindset
Welcome to pay attention to the above two-dimensional code public number, will be technical landing, interesting talk about Coding those things, finally 1024 happy holidays