EasyExcel “Design for Millions of data Reads and writes”
January 10, 2024
by Anay Manda
No Comments
There is the concept of flying in the sky, there must be the realization of landing
The concept ten times is not as good as the code once, my friend, I hope you can type all the code cases in the article once
Like it before you look at it, make it a habit
SpringBoot illustrated tutorial series article directory
SpringBoot图文教程1 “concept + case mind mapping” “basics”
Use “logback” and “log4j” to log.
SpringBoot graphic tutorial 3 – “‘ first love ‘complex” integrated Jsp
4 – SpringBoot implementation file upload and download
Use Aop in SpringBoot
SpringBoot Tutorial 6 – The use of filters in SpringBoot
SpringBoot tutorial 7 – The use of SpringBoot interceptor posture is available here
SpringBoot integration with MBG code Generator
SpringBoot import/export Excel “Apache Poi”
SpringBoot graphic tutorials 10 – template export Excel export | | millions data image export “easypoi”
SpringBoot integration with MybatisPlus
Basic use of SpringData Jpa
SpringBoot+IDEA implementation code hot deployment
EasyExcel
EasyExcel is alibaba open source a Java Excel operation technology, and EasyPoi is the same tool class encapsulation Poi. But the difference is that in EasyExcel, Poi technology has solved the memory consumption problem of reading large quantities of data. Of course, it also encapsulates many common Excel operations
Basic import and export
Image import and export
Import and export of millions of data
The official address: https://alibaba-easyexcel.github.io/quickstart/write.html
Basic import and export
In the most basic import/export, the entity class of the data to be exported is as follows:
publicclassTeacher { / * * * The teacher's primary key * / private Integer teacherId; / * * * name * / private String teacherName; / * * * Address of profile picture * / private String teacherImage; / * * * The teacher's status 0 means normal and 1 means deleted * / private Integer teacherStatus; } Copy the code
Omit the get the set
1. Import dependencies
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>At 2.0.5</version> </dependency> Copy the code
2. Annotate the data entity class to be exported
EasyExcel is also annotated development, commonly annotated as follows
ExcelProperty specifies the column in Excel for which the current field corresponds
ExcelIgnore: All fields match excel by default
DateTimeFormat Date conversion, called when a String is used to receive data in Excel date format. In the value of reference to Java. Text. SimpleDateFormat
NumberFormat number conversion, which is called when String is used to receive excel NumberFormat data. See java.text.decimalFormat for the value inside
String fileName = "/Users/lubingyang/Desktop/hhhh.xlsx"; // You need to specify which class to write to, then write to the first sheet, name it template and the stream will automatically close // If you want to use 03, pass in the excelType argument EasyExcel.write(fileName, Teacher.class).sheet("Template").doWrite(teachers);
} Copy the code
Import the code
The data reading of EasyExcel will be slightly troublesome. The data read directly through the tool class cannot be processed directly, and an intermediate class listener class needs to be used. The general process is shown in the following figure
The code for the listener is shown below, with detailed comments
// There is an important point that DemoDataListener cannot be managed by Spring. To read Excel, you need to use new method, and then use Spring constructor to pass it in publicclassDemoDataListenerextendsAnalysisEventListener<Teacher> { / * * * Store the database every 5 entries, 3000 entries in actual use, and then clean the list to facilitate memory reclamation * / privatestatic final int BATCH_COUNT = 5;
/ * * * This collection is used to receive data from reading Excel files * / List<Teacher> list = new ArrayList<Teacher>();
/ * * * Assuming this is a DAO, there is of course business logic that this could also be a service. Of course this object doesn't work if you don't have to store it. * / private TeacherDao teacherDao;
publicDemoDataListener(a){
}
/ * * * * Do not use autowiring * Pass the DAO as a parameter in the test class * / publicDemoDataListener(TeacherDao teacherDao){ this.teacherDao = teacherDao; }
/ * * * This is called for every data parse * * / @Override publicvoidinvoke(Teacher teacher, AnalysisContext context){
list.add(teacher); // Reach BATCH_COUNT, need to go to store a database, prevent data tens of thousands of data in memory, easy OOM if (list.size() >= BATCH_COUNT) { saveData(); // Store the cleanup list list.clear(); } }
/ * * * This is called when all data parsing is complete * * / @Override publicvoiddoAfterAllAnalysed(AnalysisContext context){ // Save the data here, too, to ensure that the last remaining data is also stored in the database saveData();
}
/ * * * Plus storage database * / privatevoidsaveData(a){ // This is where the DAO can be called and we can print the data directly System.out.println(list); } } Copy the code
Note:
The listener class cannot be managed by Spring and comes out with a separate new each time
The imported code is as follows:
/ * * * Add the DAO used by the database * / @Autowired private TeacherDao teacherDao; / * * * The easiest way to read * / @Test publicvoidsimpleRead(a){ String fileName = "/Users/lubingyang/Desktop/hhhh.xlsx";
// We need to specify which class is used to read the sheet, and then the first read stream is automatically closed / * * * Parameter 1 the file to read * Parameter 2 specifies the entity class object corresponding to the data to be read * Argument 3 Listener objects can be created with the DAO as an argument * / EasyExcel.read(fileName, Teacher.class, new DemoDataListener(teacherDao)).sheet().doRead();
} Copy the code
The result is as follows:
Image export
EasyExcel supports import and export of various images, what does it mean? Under normal circumstances, the picture field in the database table stores the picture path, reading the picture file export is supported, in addition, also supports the network path, stream, byte array and so on. My case is based on reading local image files.
The official documentation address: https://alibaba-easyexcel.github.io/quickstart/write.html#%E5%9B%BE%E7%89%87%E5%AF%BC%E5%87%BA
Entity transformation
The exported code is [no changes required]
Millions of data import and export
The biggest feature of EasyExcel is its ability to avoid memory overflow, so how do you do it?
First of all, why do POIS run the risk of running out of memory
When the Poi reads an Excel file, it first reads all the data into the memory and then processes it. This time if read from file data quantity is large, Java occurs. Lang. OutOfMemoryError: Java heap space error.
So what does EasyPoi do?
Instead of reading the entire file into memory at once, EasyPoi uses the stream and processes it as it goes along
When processing, EasyPoi adopts the method of processing one data as it reads one data to ensure that too much data will not be stored in memory. You can set a limit by yourself, for example, to store database every 500 data.
Next, let’s test the import and export of millions of data in code
Performance tests are available on the website, if you need them, at https://alibaba-easyexcel.github.io
Million data export
Train of thought
Paging data reading
Write each read data to Excel
Discard non-essential data like styles, fonts, etc
// We need to specify which class to write to ExcelWriter excelWriter = EasyExcel.write(fileName, CmfzUser.class).build();
// Create the same sheet only once WriteSheet writeSheet = EasyExcel.writerSheet("Big Data").build();
// Query the number of test pages. 20W data is queried each time for (int i = 1; i <= pageCount; i++) { System.out.println(i); users = userDao.selectPage(new Page<>(i, size), null).getRecords(); // Write out the data excelWriter.write(users, writeSheet);
users.clear(); }
Date end = newDate(a); System.out.println(newDate().getTime() - start.getTime()+"ms");
// Don't forget that Finish helps close the stream excelWriter.finish();
} Copy the code
The total execution time is:
Mega data read
The Excel file with millions of data cannot be read directly through Poi. I have tested it and an exception occurred
So if we’re going to use Poi we’re going to have to do a little bit of processing and then we’re going to look at that with EasyExcel
Reading files using EasyExcel is divided into two steps:
/ * * * Assuming this is a DAO, there is of course business logic that this could also be a service. Of course this object doesn't work if you don't have to store it. * / private CmfzUserDao userDao;
publicUserDataListener(a){
}
/ * * * * Do not use autowiring * Pass the DAO as a parameter in the test class * / publicUserDataListener(CmfzUserDao userDao){ this.userDao = userDao; }
/ * * * This is called for every data parse * * / @Override publicvoidinvoke(CmfzUser user, AnalysisContext context){
list.add(user); // Can set more than 500 pieces of data up, need to go to store a database, prevent data tens of thousands of pieces of data in memory, easy OOM if (list.size() >= 3000) { saveData(); // Store the cleanup list list.clear(); } }
/ * * * This is called when all data parsing is complete * * / @Override publicvoiddoAfterAllAnalysed(AnalysisContext context){ // Save the data here, too, to ensure that the last remaining data is also stored in the database saveData();
}
/ * * * Plus storage database * / privatevoidsaveData(a){ // This is where the DAO can be called and we can print the data directly teacherDao.addList(list); System.out.println("Store data:"+list.size()+"Article"); } } Copy the code
// We need to specify which class is used to read the sheet, and then the first read stream is automatically closed / * * * Parameter 1 the file to read * Parameter 2 specifies the entity class object corresponding to the data to be read * Argument 3 Listener objects can be created with the DAO as an argument * / EasyExcel.read(fileName, CmfzUser.class, new UserDataListener(userDao)).sheet().doRead();
} Copy the code
The code will work perfectly
conclusion
Congratulations on completing this chapter. A round of applause! If this article is helpful to you, please help to like, comment, retweet, this is very important to the author, thank you.
Let’s review the learning objectives of this article again
Master the basic use of EasyExcel in SpringBoot
To learn more about SpringBoot, stay tuned for this series of tutorials.
Ask for attention, ask for likes, ask for retweets
Welcome to pay attention to my public account: Teacher Lu’s Java notes, will update Java technology graphic tutorials and video tutorials in the long term, Java learning experience, Java interview experience and Java actual combat development experience.
