Springboot series (16): Integrated easyPOI to achieve Excel import and export

“This is the 18th 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

The previous period, I mainly take everybody to learn the concepts of easypoi and depend on the configuration, including the use of some commonly used annotations, this period of time I will bring you to practical exercises, hoped everybody in the way of learning to learn, I also is not big, but I hope I can drive you to write articles, even this you may be one person, I am also willing to help all I can give, I am such a person, let oneself shine, lead more people, you for everyone, everyone will be for you.

2. Introduce dependency configuration

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

Third, implement Excel import

1. Define a receiving entity

/** * 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") private String name; @excel (name = "age ", orderNum = "1") private Integer age; @excel (name = "gender ", orderNum = "2") private String sex; @excel (name = "address ", orderNum = "3") private String address; @excel (name = "user description ", orderNum = "4") private String describes; }Copy the code

The column header must correspond to the @excle name name in our object. See the following for details:

To expand:

The 1.@Data annotation is provided by the Lombok plug-in to provide read and write functionality for classes without having to write get and set methods. 2.@Excel is used for filed, which is a description of the Excel column.Copy the code

2. Controller adds the import method

@getMapping ("/import") @apiOperation (value = "excel batch import user ", Public ResultResponse<Boolean> importUsers(@requestParam ("file") MultipartFile file) {return new  ResultResponse<>(userService.importUsers(file)); }Copy the code

3. Define the import interface

/** * @param file excel file */ Boolean importUsers(MultipartFile file);Copy the code

4. Implement import interface

/** * excel batch import user ** @param file excel file */ @override public Boolean importUsers(MultipartFile file) importParams = new ImportParams(); // Set the header line to 1, default is 0, can not set; Set this parameter based on the actual situation. importParams.setTitleRows(1); Importparams.setheadrows (1); importparams.setheadRows (1); Try {/ / read the excel List < ImportUser > users. = ExcelImportUtil importExcel (file. The getInputStream (), ImportUser. Class, importParams); Boolean isSuccess = this.saveUsers(users); // return isSuccess; } catch (Exception e) { e.printStackTrace(); } return false; }Copy the code

Here you need to pay attention to the table header line set must be correct! Otherwise the collection data cannot be read and an error is reported. It is important to distinguish between a header, which is the column name, and a header, which is the text above the header. See the screenshot below for an example.

The sample screenshot file has a title and takes up one line, so setTitleRows is set to 1. The header also takes up a row, so setHeadRows is set to 1;

The specific code Settings are as follows:

importParams.setTitleRows(1);
importParams.setHeadRows(1);
Copy the code

After the data is retrieved to be inserted into the database, the collection is then inserted into the database using the batch insert method.

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

5, Postman test interface

First step: fill in the URL, then pass in the parameters, select Body–>form-data; Submit as a table of data, then fill in your fixed parameter name, such as mine: File, select File as the parameter type instead of the default text, and select add as the excel import file you just created.

Specific demonstration is as follows:

Step 2: Click Send to request the interface. The interface result is displayed.

Check the console SQL execution print again. The details are as follows: No error is reported.

If the result is true, the data in your Excel is successfully stored in the database. If you are worried, the most direct way is to log in to the mysql database and check whether the Excel data is in the table by yourself. If the amount of data is large, you can also directly execute SQL query.

Step 3: Connect to the database and check whether the data is inserted into the database.

. .

Ok, that’s all for this issue, next time we will learn how to implement Excel export through EasyPOi, stay tuned and see you next time.

Four, the past popular recommendation

Springboot series (16) : Integrated easyPOI implementation of Excel import and export (preparation)
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.