Springboot series (16): Integrated easyPOI implementation of Excel multi-sheet import

“This is the 24th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

👨🎓 Author: Bug Bacteria

✏️ blog: CSDN, Nuggets, etc

💌 public account: Magic House of the Circle of the Apes

🚫 special statement: original is not easy, reprint please attach the original source link and this article statement, thank you for your cooperation.

🙏 Copyright notice: part of the text or pictures in the article may come from the Internet or Baidu Encyclopedia, if there is infringement, please contact bug bacteria processing.

Hi, family. I’m the bug. Here I go again. Today we are going to talk about something, OK, and we will continue the Series of articles on SpringBoot. Hope to help more beginners quickly start!

In the process of reviewing articles, if you think the articles are helpful to you at all, please don’t be too mean with your likes and bravely light up the articles 👍. Your likes (collect ⭐️+ pay attention to 👨 port + message board) are the best encouragement and support for bugs on my creation path. Time does not abandon 🏃🏻♀️, creation stopped 💕, refueling 🏻

One, foreword

In the last few sessions, we mainly talked about how to integrate EasyPOI to achieve import and export function of Excel, import and export of Word, right? I don’t know how you have mastered it. If you still have questions about any of the following articles, please ask more questions. Ha ha ha, although I am not a big guy, I will try my best to teach each other and learn to fill in the gaps together.

How does Springboot integrate with EasyPOI
Integrate EasyPOI to realize Excel import function
Integrated EasyPOI to achieve Excel export function
Integrated EasyPOI to export multi-page data from single Word template
Integrate EasyPOI to realize word template traversal display all data
Integrated easyPOI to realize word template image export
Integrated EasyPOI to achieve Excel image export
Integrated EasyPOI to achieve Excel multi-sheet export

So, today we are going to play some advanced. According to the content described in the previous several episodes, Excel always carries out data import or export in a single sheet, but the real environment will certainly be more than one sheet. If multiple sheets are involved in a single Excel, have you ever played the import and export function of Excel? Assuming that the current requirement is to read the data in an Excel sheet for data import and export data according to different sheets, you are required to realize these two requirements as soon as possible! Ha-ha-ha-ha-ha, finished, strong working mode, yes, yes, this is the battlefield, this is the actual combat.

Don’t worry, I will take you from zero today, I hope you can prepare the lesson in advance, if you really meet the need, then you must be the most beautiful boy in the audience.

Next I will start, the students may have to listen to oh ~ I will take you step by step to achieve it, as for how to achieve, then look down.

Two, environment configuration

To realize the import and export function of Excel multi-sheet worksheet, we still have to rely on EasyPOi. So all you need to do is add the following easyPOi starter dependency package to your pom.xml dependency, and you don’t have to import it again.

<! - easypoi dependence, Afterturn </groupId> <artifactId>easypoi-spring-boot-starter</artifactId> The < version > 4.3.0 < / version > < / dependency >Copy the code

Iii. Import of Multiple sheets and Excel

1. Define an import entity class.

/** * excel ImportUser parameter ** @author luoYong * @version 1.0 * @date 2022/2/15 14:34 */ @data public class ImportUser implements Serializable { private static final long serialVersionUID = 1L; /** * @excel is used for a field, the description of the column * @param name column name * @param orderNum subscript, starting from 0. */ @excel (name = "name ", orderNum = "0",width = 10.0) private String name; @excel (name = "age ", orderNum = "1",width = 10.0) private Integer age; @excel (name = "gender ", orderNum = "2",width = 5.0) private String sex; @excel (name = "address ", orderNum = "3",width = 30.0) private String address; @excel (name = "user description ", orderNum = "4",width = 20.0) private String describes; }Copy the code

2. Controller adds excel import method

Let’s start by defining an Excel import method that provides a port for you to test through browser access.

@getMapping ("/import-for-sheets") @apiOperation (value = "excel import ", Notes = "Excel Sheet Import ") public void importForSheetUsers(@requestParam ("file") MultipartFile file) throws IOException { userService.importForSheetUsers(file); }Copy the code

3. Define the import interface

/** * excel multisheet import */ void importForSheetUsers(MultipartFile file) throws IOException;Copy the code

4. Implement import method (core)

ImportExcel () provides a startSheetIndex property to specify the sheet. So we can specify the sheet in the Excel import method. By default, the sheet starts with subscript 0.

That means we can encapsulate an Excel import method that works for both single sheets and multiple sheets.

Please refer to the details I wrote:

EasyPoiUtils.java

package com.example.demo.util; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ImportParams; import java.io.InputStream; import java.util.List; import java.util.NoSuchElementException; /** * @author luoYong * @version 1.0 * @date 2022/2/21 10:40 */ public class EasyPoiUtils {/** * Import multiple sheets based on the received Excel file, return a collection based on the index * * @param inputStream Excel inputStream * @param sheetIndex import sheet index * @param titleRows Public static <T> List<T> importExcel(InputStream inputStream, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) {// Obtain the Workbook from the file. / / which one sheet page table params. SetStartSheetIndex (sheetIndex); // set table titleRows params.settitlerows (titleRows); // Set the number of rows in the table header params.setHeadRows(headerRows); List<T> list = null; Try {/ / read the excel data collection list = ExcelImportUtil. ImportExcel (inputStream, pojoClass, params); } catch (NoSuchElementException e) {throw new RuntimeException(" template cannot be empty "); } catch (Exception e) { e.printStackTrace(); } return list; }}Copy the code

The above is the encapsulation of the implementation of reading multiple sheets from EXCE, and the rest is easy to handle. Our actual business has several sheets, we can call several times in a row, and then insert the read data into the table.

Since I only used the same ImportUser to illustrate the diagram, I did not specify several different import classes. In general, different sheets usually correspond to different data contents, so the function of importing from multiple sheets is needed, which saves the time of making excel sheets each time. Improve the expansion and efficiency of Excel import, otherwise every import of a kind of Excel need to develop an interface, which is certainly not allowed, you think about it is not?

UserServiceImpl.java

/ / @override public void importForSheetUsers(MultipartFile file) throws IOException { List<ImportUser> sheetOneUsers = easyPoiutils.importExcel (file.getinputStream (), sheetOneUsers = easyPoiutils.importExcel (file.getinputStream ()) 0, 1, 1, ImportUser.class); List<ImportUser> sheetTwoUsers = easyPoiutils.importExcel (file.getinputStream (), 1, 1, 1) ImportUser.class); // Insert this.saveUsers(sheetOneUsers); this.saveUsers(sheetTwoUsers); }Copy the code

The following is the method of user data insertion, I also paste here, I am integrated with mybatis- Plus component, so it is very good for single table operation, need friends can turn over my previous several issues of integration of Mybatis – Plus component, hand to hand teaching, one time will be.

*/ private Boolean saveUsers(List<ImportUser> users) {// Store userList <UserEntity> userList = new ArrayList<>(); For (ImportUser user: users) {// void if (user! = null) { UserEntity userEntity = new UserEntity(user); userList.add(userEntity); Return this.saveBatch(userList); }Copy the code

5, Postman test interface

Postman = Postman; Postman = Postman; Postman = Postman; Postman = Postman; Postman = Postman; Postman = Postman; Postman = Postman Then you just need to use it as I did in the screenshot below. Fill in the corresponding interface address and select the request mode according to the interface you specify, then select the file class parameter file, pass in the Excel template file you created, and click Send.

The actual screenshots are as follows: For your reference.

After the call, because there is no return value and no 500 error code, check the console to see if there is an error, the last is to verify whether the database data have been inserted, it is also the most direct the most critical validation step, whether this is the success, although obviously console SQL to print the statement execution, actually had the insert data is verified, But the best way to teach is to be transparent when I show you the code, to be validated, don’t you think?

As shown in the screenshot of the database table above, we checked the database user table specified by the project. It was obvious that the user data in the two sheets in Excel were inserted into the database, which verified one thing. We conducted the function point of importing multiple sheets into Excel, and all the codes were tested correctly. You can rest assured to take to study or directly CV use.

Note: First, debug the breakpoint to see what the size(total) of the excel data actually read is, and then how much the valid data (sj) is, subtract (total-sj = n), which means there are n empty data. So if you have n empty lines underneath the valid data in Excel, maybe you filled it in and even though it looks empty to the naked eye, you can still read it when you read it. The physical method is that you only need to select the n rows below the valid data, delete and save the rows, and then you try to import, and there will be no empty rows read, or logical filtering, for the empty data directly skipped. Pro test effective, welcome to adopt!!

Appendix to Excel template

The sample Excel template I imported is attached below:

Sheet1 screenshots:

Sheet2 screenshots:

. .

Well, that’s all for this episode, and if you want to learn more, you can check out my top tips on how to accumulate a little weird knowledge every day, and over time, you can become a person you respect. Well, I’ll see you next time

Five, the past popular recommendation

Springboot series (16) : Integrated easyPOI implementation of Excel import and export (preparation)
Springboot series (16) : Integrated easyPOI to achieve Excel import
Springboot series (16) : Integrated easyPOI to achieve Excel export
Springboot series (16) : integrated easyPOI to achieve a single Word template export page
Springboot series (16) : Integrated easyPOI to realize word template circulation export multiple data
Springboot series (16) : Integrated easyPOI word template image export
Springboot series (16) : integrated easyPOI to achieve excel multi-sheet export
Springboot series (15) : AOP implements custom annotations for business logging! Have you ever played?
Springboot series (14) : Redis Zero-based teaching, you deserve it!
Springboot Series (thirteen) : How to project integrated Swagger online interface documentation, will you?
Springboot series (12) : How to code to send email reminders, have you written?
. .

If you want to learn more, you can pay attention to the bug bug column “SpringBoot Zero-based Introduction”, from scratch, from zero to one! Hope I can help you.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

☘️ Be who you want to be, there is no time limit, you can start whenever you want,

🍀 You can change from now on, you can also stay the same, this thing, there are no rules to speak of, you can live the most wonderful yourself.