This is the first day of my participation in the Gwen Challenge in November. Check out the details: the last Gwen Challenge in 2021
About the cover: On the way back to study hall after dinner
Easyexcel official documentation
Easyexcel | github
preface
I was writing a little exercise recently, and I needed to use this. I’m going to write an integrated Demo for you this time.
I hope you can learn something.
After reading this article, I think you should have no problem using Java and Easyexcel to manipulate Excel.
First, environmental preparation
1.1. Import related dependencies
I use Easyexcel JAR package is in October 2021, say is the latest version, mo question 😁
easyexcel | maven
<! -- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.74</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
Copy the code1.2. Project structure
You know how to build a project, so let me put my own structure here.
Second, readExcel operation readExcel
2.1 preparation
With an XSLX file template ready, I have prepared myself.
We create an entity class that corresponds to the column names in XLSX.
Entity class
@Data
public class DemoModel {
/** * use the name to match the data. Note that if the name is repeated, only one field will be read, so we usually use the following format to confirm. * /
@excelProperty (value = "blogname ", index = 0)
private String name;
@excelProperty (value = "community ", index = 1)
private String communityName;
@excelProperty (value = "home ", index = 2)
private String homePageUrl;
@excelProperty (value = "domain involved ", index = 3)
private String specialty;
@excelProperty (value = "email ", index = 4)
private String email;
/** ** string (); I want to receive year month day format */
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
@excelProperty (value = "first post ", index = 5)
private String startDate;
}
Copy the codeThe listener
/** * There is an important point in the DocumentListener that is not managed by Spring. To read Excel, you need to use new, and then use spring's constructor to pass it in@author crush
*/
public class DemoListener extends AnalysisEventListener<DemoModel> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoListener.class);
/** * Store the database every 5 entries, in actual use can be 3000 entries, then clear the list, convenient memory collection */
private static final int BATCH_COUNT = 10;
List<DemoModel> list = new ArrayList<DemoModel>();
/** * If this is a DAO, of course there is business logic that this can also be a service. Of course this object doesn't work if you don't have to store it. * /
private DemoMapper demoMapper;
public DemoListener(a) {
// This is demo, so just create a new one. If you are in Spring, use the parameterized constructor below
demoMapper = new DemoMapper();
}
/** * If spring is used, use this constructor. Each time you create a Listener, you need to pass in spring-managed classes * *@param demoDAO
*/
public DemoListener(DemoMapper demoMapper) {
this.demoMapper = demoMapper;
}
/ * * * the each data parsing would call for * *@param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(DemoModel data, AnalysisContext context) {
LOGGER.info("Parse to a single data :{}", JSON.toJSONString(data));
list.add(data);
// 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 listlist.clear(); }}/** * when all data is parsed, call **@param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// Save the data here, too, to ensure that the last remaining data is also stored in the database
saveData();
LOGGER.info("All data parsed!"+count);
}
/** * + store database */
private void saveData(a) {
LOGGER.info("{} data, start storing database!", list.size());
// Perform database level operations
demoMapper.save(list);
LOGGER.info("Database saved successfully!"); }}Copy the codeMapper layer: this is just simulation
/ * * *@Author: crush
* @Date: 2021-10-31 11:39
* version 1.0
*/
@Repository
public class DemoMapper {
public void save(List<DemoModel> demoModels){
System.out.println("Mapper:"+demoModels); }}Copy the codeType conversion
2.2. Read a Sheet
/** * demo1 the simplest read ** we first read a single Sheet ** <p>1. Create excel corresponding entity object refer to {@linkQuestionModel} * * <p>2. Create an Excel line-by-line callback listener by default.@linkDocumentListener} * * <p
@Test
public void demo1TestRead(a) {
// 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
// write method 1:
String fileName = "E:\\project_code\\commons-utils\\springboot-excel\\src\\main\\resources\\excel\\demo.xlsx";
// We need to specify which class is used to read the sheet, and then the first read stream is automatically closed
EasyExcel.read(fileName, DemoModel.class, new DemoListener()).sheet().doRead();
}
Copy the codeWe don’t need to specify sheet here because we only have one worksheet and we’re done by default.
Console output:
23:35:52.583[the main] INFO com. Crush. Excel. Listener. DemoListener - resolution to a data: {" communityName ":" nuggets ", "email" : "nzc_wyh @163.com","homePageUrl":"https://juejin.cn/user/2859142558267559", "name" : "NingZaiChun", "specialty" : "Java back-end development", "startDate" :"2021-10-31 12:01:52"}
23:35:52.584[the main] INFO com. Crush. Excel. Listener. DemoListener - resolution to a data: {" communityName ":" CSDN ", "email" : "nzc_wyh @163.com","homePageUrl":"https://blog.csdn.net/weixin_45821811? spm=1000.2115.3001.5343", "name" : "NingZaiChun", "specialty" : "Java back-end development", "startDate" :"2020-05-11 12:01:52"}
23:35:52.585 [main] INFO com.crush.excel.listener.DemoListener - 2Data, start storing database!Mapper:[DemoModel(name= Rather in spring,communityName= the nuggets,homePageUrl=https: / /juejin.cn/user/ 2859142558267559,specialty=JavaBack-end development,email=nzc_wyh@ 163.com.startDate12:01:52 = 2021-10-31),DemoModel(name= Rather in spring,communityName=CSDN.homePageUrl=https: / /blog.csdn.net/weixin_45821811?spm= 1000.2115.3001.5343,specialty=JavaBack-end development,email=nzc_wyh@ 163.com.startDate617 [= 2020-05-11 23:35:52 12:01:52)].main] INFO com.crush.excel.listener.DemoListener- Database storage succeeded! 618 [23:35:52.main] INFO com.crush.excel.listener.DemoListener- All data parsing completed! 2Copy the codeThis is the easiest way to read a single Sheet in Excel, but we usually have multiple worksheets in an Excel.
As follows:
2.3. Read multiple sheets
It’s essentially a single sheet.
Because the data for each Sheet is different, you create multiple models and multiple listener classes.
This side will not post all the model, if you do not understand, you can go to the end of the text to see the source code.
Create multiple listeners as well.
The mapper layer is omitted here.
/** * read multiple or all of the sheets. Note that one sheet cannot be read more than once. Create excel corresponding entity object refer to {@linkQuestionModel} * <p> * 2. The default is to read excel line by line, so we need to create excel line by line callback listener.@linkDocumentListener} * <p> * 3
@Test
public void repeatedRead(a) {
String fileName = "E:\\project_code\\commons-utils\\springboot-excel\\src\\main\\resources\\excel\\Document_Success.xlsx";
// Read the Sheet to be read
ExcelReader excelReader = EasyExcel.read(fileName).build();
// You must use a different Listener
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(DocumentModel.class).registerReadListener(new DocumentListener()).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(TemplateModel.class).registerReadListener(new TemplateListener()).build();
ReadSheet readSheet3 =
EasyExcel.readSheet(2).head(SectionModel.class).registerReadListener(new SectionListener()).build();
ReadSheet readSheet4 =
EasyExcel.readSheet(3).head(QuestionModel.class).registerReadListener(new QuestionListener()).build();
ReadSheet readSheet5 =
EasyExcel.readSheet(4).head(OptionModel.class).registerReadListener(new OptionListener()).build();
ReadSheet readSheet6 =
EasyExcel.readSheet(5).head(ConditionModel.class).registerReadListener(new ConditionListener()).build();
ReadSheet readSheet7 =
EasyExcel.readSheet(6).head(QuestionTooltipModel.class).registerReadListener(new QuestionTooltipListener()).build();
ReadSheet readSheet8 =
EasyExcel.readSheet(7).head(OptionTooltipModel.class).registerReadListener(new OptionTooltipListener()).build();
// Sheet1 sheet2 must be uploaded together. Otherwise, excel 03 will read sheet1 sheet2 many times, wasting performance
excelReader.read(readSheet1, readSheet2, readSheet3, readSheet4, readSheet5, readSheet6, readSheet7, readSheet8);
// Do not forget to close this file, temporary files will be created while reading, and the disk will crash
excelReader.finish();
}
Copy the codeConsole output:
23:42:19.733[the main] INFO com. Crush. Excel. Listener. DocumentListener - resolution to a data: {" customerId ":1,"name":"Document i"}
23:42:19.755[the main] INFO com. Crush. Excel. Listener. TemplateListener - resolution to a data: {" documentType ":1,"fontStyleId":1,"lobId":1,"name":"Template q","userGroupId":1}
23:42:19.785[the main] INFO com. Crush. Excel. Listener. SectionListener - resolution to a data: {" index ":1,"name":"Section 1","order":1}
23:42:19.785[the main] INFO com. Crush. Excel. Listener. SectionListener - resolution to a data: {" index ":2,"name":"Section 2","order":2}
23:42:19.823[the main] INFO com. Crush. Excel. Listener. QuestionListener - resolution to a data: {" allowComments ":" false ", "answerType" :"1","enhancedField":"false","index":"1","order":"1","question":"This is Simple Question","required":"true","sectionIndex":"1"}
23:42:19.824[the main] INFO com. Crush. Excel. Listener. QuestionListener - resolution to a data: {" allowComments ":" false ", "answerType" :"2","enhancedField":"false","index":"2","order":"2","question":"This is Simple Question","required":"false","sectionIndex":"1"}
23:42:19.825[the main] INFO com. Crush. Excel. Listener. QuestionListener - resolution to a data: {" allowComments ":" false ", "answerType" :"10","enhancedField":"false","index":"3","order":"3","question":"This is Simple Question","required":"false","sectionIndex":"1"}
23:42:19.826[the main] INFO com. Crush. Excel. Listener. QuestionListener - resolution to a data: {" allowComments ":" false ", "answerType" :"3","enhancedField":"false","index":"4","order":"1","question":"This is Simple Question","required":"true","sectionIndex":"2"}
23:42:19.827[the main] INFO com. Crush. Excel. Listener. QuestionListener - resolution to a data: {" allowComments ":" false ", "answerType" :"5","enhancedField":"false","index":"5","order":"2","question":"This is Simple Question","required":"false","sectionIndex":"2"}
23:42:19.831[the main] INFO com. Crush. Excel. Listener. QuestionListener - resolution to a data: {" allowComments ":" false ", "answerType" :"12","enhancedField":"false","index":"6","order":"3","question":"This is Simple Question","required":"true","sectionIndex":"2"}
23:42:19.847[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":1,"order":1,"questionIndex":3,"value":"Option 1"}
23:42:19.848[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":2,"order":2,"questionIndex":3,"value":"Option 2"}
23:42:19.849[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":3,"order":3,"questionIndex":3,"value":"Option 3"}
23:42:19.849[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":4,"order":4,"questionIndex":3,"value":"Option 4"}
23:42:19.850[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":5,"order":1,"questionIndex":6,"value":"Option 1"}
23:42:19.850[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":6,"order":2,"questionIndex":6,"value":"Option 2"}
23:42:19.851[the main] INFO com. Crush. Excel. Listener. OptionListener - resolution to a data: {" index ":7,"order":3,"questionIndex":6,"value":"Option 3"}
23:42:19.868[the main] INFO com. Crush. Excel. Listener. ConditionListener - resolution to a data: {" action ":1,"index":1,"questionIndex":1,"selectedAnswer":"Test","triggerQuestionIndex":1,"triggerSectionIndex":1}
23:42:19.869[the main] INFO com. Crush. Excel. Listener. ConditionListener - resolution to a data: {" action ":1,"index":2,"questionIndex":2,"selectedAnswer":"Test","triggerSectionIndex":2}
23:42:19.888[the main] INFO com. Crush. Excel. Listener. QuestionTooltipListener - resolution to a data: {" endIndex ":10,"index":1,"parentIndex":1,"startIndex":1,"text":"Sample Tooltip"}
23:42:19.908[the main] INFO com. Crush. Excel. Listener. OptionTooltipListener - resolution to a data: {" endIndex ":10,"index":1,"parentIndex":1,"startIndex":1,"text":"Sample Tooltip"}
23:42:19.910[the main] INFO com. Crush. Excel. Listener. OptionTooltipListener - resolution to a data: {" endIndex ":10,"index":2,"parentIndex":2,"startIndex":1,"text":"Sample Tooltip"}
Copy the codeWhen we get the data, we can store it in the database. In addition, we often upload Excel for data import, so let’s take a look at excel reading in the Web.
2.4. Read on the Web
@AutowiredDemoMapper demoMapper;/** * file upload * <p> * 1.@linkUploadData * <p> * UploadData * <p> *@linkUploadDataListener} * <p> * 3. Just read */@PostMapping("upload")@ResponseBodypublic String upload(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), DemoModel.class, new DemoListener(demoMapper)).sheet().doRead(); return "success"; }Copy the code
I think this scenario is the one we use most often in Web development.
If you have enough memory and not too much data, it’s perfectly acceptable to write like this.
If it is a big brother operating data of one hundred thousand or hundreds of thousands of data, you can go to the official website to see the optimization plan. 👉 Easyexcel | dead simple
Write Excle operation writeExcel
3.1. Write to a single Sheet
/** ** <p>1. Create excel corresponding entity object reference {@linkDemoModel} * <p>2
@Test
public void simpleWrite(a) {
DemoModel model = new DemoModel();
model.setName("Better in spring");
model.setCommunityName("Zhihu");
model.setHomePageUrl("https://www.zhihu.com/creator/manage/creation/all");
model.setEmail("[email protected]");
model.setSpecialty("SpringBoot");
model.setStartDate("The 2021-10-31 12:00:00");
DemoModel model2 = new DemoModel();
model2.setName("Ningjichun 2");
model2.setCommunityName("Zhihu 2");
model2.setHomePageUrl("2https://www.zhihu.com/creator/manage/creation/all");
model2.setEmail("[email protected]");
model2.setSpecialty("SpringBoot2");
model2.setStartDate("The 2021-10-31 12:00:00");
List<DemoModel> models = new ArrayList<DemoModel>();
models.add(model);
models.add(model2);
/ / write 1
String fileName = "E:\\project_code\\commons-utils\\springboot-excel\\src\\main\\resources\\excel\\demo2.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, DemoModel.class).sheet(0).doWrite(models);
// // 2
// // here you need to specify which class to write to
ExcelWriter excelWriter = EasyExcel.write(fileName, DemoModel.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
excelWriter.write(models, writeSheet);
// Don't forget that Finish helps close the stream
excelWriter.finish();
}
Copy the codeThis is a simple operation to manipulate a single Sheet in an Excel file.
EasyExcel.write(fileName, DemoModel.class).sheet(0).doWrite(models); With regard to the parameter 0 in sheet(), we actually have a sheet, and it is the same whether we fill in the sheet or not. In addition, we can also fill in the sheet name here, which is also the same.
Operation results:
Note:
Note: I noticed that if I write directly to this Excel file, the default is to overwrite it, which means all the previous information will be overwritten.
Testing:
Before writing.
After writing.
Supplement:
If we were to write directly, it would be in overwrite mode, which would certainly not fit some business scenarios. So there’s got to be a solution,
Easyexcel also handles this.
It has a write based on a template, which looks like this:
1. Create an entity object corresponding to Excel by referring to * 2. Use annotations to specify columns to write to *
3. Use withTemplate to write to the template *
4. Just write */
@Test
public void templateWrite(a) {
DemoModel model = new DemoModel();
model.setName("Better in spring");
model.setCommunityName("Zhihu");
model.setHomePageUrl("https://www.zhihu.com/creator/manage/creation/all");
model.setEmail("[email protected]");
model.setSpecialty("SpringBoot");
model.setStartDate("The 2021-10-31 12:00:00");
DemoModel model2 = new DemoModel();
model2.setName("Ningjichun 2");
model2.setCommunityName("Zhihu 2");
model2.setHomePageUrl("2https://www.zhihu.com/creator/manage/creation/all");
model2.setEmail("[email protected]");
model2.setSpecialty("SpringBoot2");
model2.setStartDate("The 2021-10-31 12:00:00");
List<DemoModel> models = new ArrayList<DemoModel>();
models.add(model);
models.add(model2);
String templateFileName = "E:\\project_code\\commons-utils\\springboot-excel\\src\\main\\resources\\excel\\demo2.xlsx";
String fileName = "E:\\project_code\\commons-utils\\springboot-excel\\src\\main\\resources\\excel\\templateWrite.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
EasyExcel.write(fileName, DemoModel.class).withTemplate(templateFileName).sheet().doWrite(models);
}
Copy the codeThe final effect is as follows:
When I wrote the notice section, I also thought about how this might be handled.
What I originally thought was that I would read the file, then put the data together, and then write it in. But I immediately overturned that idea, because once you do that, the memory consumption and everything else is just too big to be appropriate.
And then a quick look at it, it just new a File and load it into memory. (Feeling silly)
Because of this, I went to test a different one, and what would happen if the template was modified differently?
The test result is consistent with my guess, is a copy of the original file, on the basis of the source file write operation, regardless of the format of the original file, will be retained.
3.2. Repeatedly write or write to multiple sheets
Repeat write:
It’s just a loop of a single operation.
// Method 1 if written to the same sheet
String fileName = "E:\\project_code\\commons-utils\\springboot-excel\\src\\main\\resources\\excel\\demo2.xlsx";
// // here you need to specify which class to write to
ExcelWriter excelWriter = EasyExcel.write(fileName, DemoModel.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
// call write, which I called five times, based on the total number of database pages
for (int i = 0; i < 5; i++) {
// Query data on each page of the database
List<DemoModel> data = data();
excelWriter.write(data, writeSheet);
}
// Don't forget that Finish helps close the stream
excelWriter.finish();
Copy the codeData () is a method that generates data.
Write to multiple sheets:
Its essence is the same as above, just call different listener class, different object just, simpler way, to do different processing, has not found a simpler way.
The same way you read multiple sheets, I won’t repeat them here.
3.3. Write in the Web
Since there is writing will also have to read, beginning and end, this is also posted for everyone.
/** * File download (failure will return an Excel with partial data) * * 1. To create the entity object corresponding to Excel, refer to *
* 2. Set the returned parameters *
* 3. Note that the OutputStream will be automatically closed when you finish
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
// Using Swagger can cause problems, please use browser or postman
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// Here URLEncoder. Encode can prevent Chinese garbled of course and easyExcel has no relationship
String fileName = URLEncoder.encode("Test"."UTF-8");
response.setHeader("Content-disposition"."attachment; filename=" + fileName + ".xlsx");
// The argument to the sheet is the name of the generated sheet
EasyExcel.write(response.getOutputStream(), DemoModel.class).sheet("Template").doWrite(data());
}
/** * Returns json when the file is downloaded and fails (default failure returns an Excel with partial data) **@since2.1.1 * /
@GetMapping("downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
// Using Swagger can cause problems, please use browser or postman
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// Here URLEncoder. Encode can prevent Chinese garbled of course and easyExcel has no relationship
String fileName = URLEncoder.encode("Test"."UTF-8");
response.setHeader("Content-disposition"."attachment; filename=" + fileName + ".xlsx");
// Here you need to set not to close the stream
EasyExcel.write(response.getOutputStream(), DemoModel.class).autoCloseStream(Boolean.FALSE).sheet("Template")
.doWrite(data());
} catch (Exception e) {
/ / reset the response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status"."failure");
map.put("message"."Failed to download file"+ e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); }}private List<DemoModel> data(a){
List<DemoModel> demoModels = new ArrayList<>();
DemoModel model = new DemoModel();
model.setName("Better in spring");
model.setCommunityName("Zhihu");
model.setHomePageUrl("https://www.zhihu.com/creator/manage/creation/all");
model.setEmail("[email protected]");
model.setSpecialty("SpringBoot");
model.setStartDate("The 2021-10-31 12:00:00");
DemoModel model2 = new DemoModel();
model2.setName("Ningjichun 2");
model2.setCommunityName("Zhihu 2");
model2.setHomePageUrl("2https://www.zhihu.com/creator/manage/creation/all");
model2.setEmail("[email protected]");
model2.setSpecialty("SpringBoot2");
model2.setStartDate("The 2021-10-31 12:00:00");
demoModels.add(model);
demoModels.add(model2);
return demoModels;
}
Copy the codeResults:
Four, possible problems
-
It is already used in entities
@DataAnnotations, don’t use them anymore@Accessors(chain = true)Otherwise, the read will be empty.This I have personally stepped on the pit (at the beginning of the row this wrong row will 😂)
-
While reading
ListenerIt needs to use Spring@AutowiredCreate a member variable for the Listener and pass it in the constructor. Spring must not manage the Listener; one must be new each time it is read.
These are two pits that I think are very common and relatively easy to step on, and I have listed them for you.
Details of Easexcel FAQ are available at 👉
Five, talk to yourself
Starting from November, I will write projects every day and make records to enrich every day.
The paper come zhongjue shallow, and must know this to practice.Hello everyone, I am ning Zaichun: homepage
A young man who likes literature and art but takes the path of programming.
Hope: by the time we meet on another day, we will have achieved something.
A cute little cat emoji, too cute to resist the temptation.
