Springboot series (16): integrated easypoi to achieve excel image export

“This is the 23rd 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

Now that word exports are diversified, what about data with images and Excel exports?

For example, to export the basic information of all users, but in the user table, there is a self-portrait of each user, such as a one-inch photo, like this situation, how should excel export be implemented? Is it the same as before?

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.

Let’s take a look at the final result of this article. As shown below:

So, if you have any questions about this, I’m going to walk you through it from zero to one, and I’m going to post the basic example code, just to help you better check and have a basic comparison for those of you who don’t get it.

2. Introduce POM dependencies

To implement Excel image export, we still rely on EasyPOi to do it. 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

Third, to achieve Excel image export

1. Start by defining an exported entity class.

I’m going to show you how to use annotations to export excel, and I’ll show you how to export excel templates later. Ok. Mainly based on the @excel annotation to achieve.

The specific implementation code we see below, midway some key, I will expand the code below the explanation, this you can rest assured.

ExportExcelUser.java

/** * excel import user ** @author luoYong * @version 1.0 * @date 2022/2/15 14:34 */ @data public class ExportExcelUser 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 ", width = 10.0) private String name; @excel (name = "age ", width = 10.0) private Integer age; DatabaseFormat @excel (name = "Date of birth ", format =" YYYY-MM-DD ", width = 20.0) private Date bornDate; // If the database is of type string, you need to set the database time format format: @Excel(name = "yyyyMMdd", databaseFormat = "yyyyMMdd", format = "YYYY-MM-DD ", Width = 20.0) private String enterSchoolTime; //replace: drop down box with _0 in the drop-down order Male -> male @excel (name = "male ", width = 10.0, replace = {" male _0"," female _1"}, suffix = "male ", addressList = true) private String sex; @excel (name = "address ", width = 30.0) private String address; //imageType Export type; 1: reads from file. 2: reads data from the database. The default value is a file. @excel (name = "avatar ", type = 2, width = 30.0, height = 30.0, imageType = 1) @excel (name =" avatar ", type = 2, width = 30.0, height = 30.0, imageType = 1) @excel (name = "user description ", width = 20.0) private String describes; }Copy the code

Development:

@data: This annotation is provided by Lombok to omit the manual addition of get set methods.
@excel (databaseFormat=” XXXX “) : If the database field is string, you need to add this attribute and the time format, for example: databaseFormat=” yyyyMMdd”.
@excel (format=” XXXX “): The format attribute indicates the Excel display format.
@excel (replace={“xxx_0”, “xxX_1 “,” xxX_2 “}, addressList = true): indicates the drop-down list of cells. _0 and _1 indicate the order of drop-down values from 0 to back. The addressList attribute is required to implement the field dropdown.
@excel (suffix=” XXX “): automatically add the XXX to your field text, for example, “98”–>98%.
@excel (imageType=” XXX “): indicates the export type, imageType=1: reads from file; ImageType =2: read from the database; The default is a file, and the same applies to imports.

The rest of the notes will directly skip, want to know can go to see my previous several issues of the article, especially the first issue of preparation “Springboot integration Easypoi preparation”.

2. Controller adds excel export method

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

@getMapping ("/export") @apiOperation (value = "excel export", Public void exportUsersToExcel(HttpServletResponse Response) { userService.exportUsersToExcel(response); }Copy the code

3. Define the import interface

*/ void exportUsersToExcel(HttpServletResponse Response);Copy the code

4. Implement the export method (core)

The following export implementation class is very key, we still directly use the easyPOi provided by exportExcel() method, please refer to my writing for detailed use:

The specific code Settings are as follows:

*/ @overridePublic void exportUsersToExcel(HttpServletResponse Response) {try List<UserEntity> users = this.list(); Responsetheader (" content-type ", "application/vnd.ms-excel"); Responsetheader (" Content-disposition ", "attachment; Filename =" + URLEncoder. Encode (" student info table.xls ", standardCharsets.utf_8.name ())); ServletOutputStream out = Response.getOutputStream (); // Set the excel parameter ExportParams params = new ExportParams(); // Set the sheet name name params.setSheetName(" Student List "); // Set the title params.setTitle(" student info table "); // Convert to the corresponding type; List<ExportExcelUser> exportUsers = this.changeType(users); / / import excel Workbook Workbook. = ExcelExportUtil exportExcel (params, ExportExcelUser. Class, exportUsers); / / write workbook. Write (out); } catch (Exception e) { e.printStackTrace(); }}Copy the code

I used mybatis-plus to specify the entity type, so in order to make the entity type consistent with the exported VO class, I will simply write a re-assignment to the exported VO class.

The specific code Settings are as follows:

Vo ** @param users */ private List<ExportExcelUser> changeType(List<UserEntity> users) { List<ExportExcelUser> res = new ArrayList<>(); for (UserEntity user : users) { ExportExcelUser exportUser = new ExportExcelUser(user); res.add(exportUser); } return res; }Copy the code

I’m curious how my image is assigned. So if you see the ExportExcelUser class, I must have constructed it, right, otherwise it would be a little ugly to write it in an entity class.

public ExportExcelUser(UserEntity user) { this.name = user.getName(); this.age = user.getAge(); this.address = user.getAddress(); this.sex = user.getSex(); this.describes = user.getDescribes(); This.borndate = new Date(); This. enterSchoolTime = "20220210"; This.image = user.img (); // Set an image address; Image = "./template/image/ image.jpg "; }Copy the code

Note:

I created one directly in the projecttemplate/imageFolder, and then write a picture, so the next story is that each user data corresponds to this picture.
Keep an eye on the contrastbornDate 与 enterSchoolTimeThese two fields, one for the Date() class and the other for String.

5. Browser test interface

We open the browser, enter the address of the interface we just exposed in Controller in the address bar:

Like me: http://localhost:8080/user/export you for a visit at your interface address.

You can see that a file is being downloaded. The rest is to see if everything is set, especially the focus of this episode, image export.

As shown in the figure above, the requirements are basically met, such as sheet name and document title. Then, for the gender field, drop-down box screening is also displayed, and the gender field is automatically added with text suffixes. What is special is that the column of our avatar field, the pictures are exported, which is worth happy.

The date of birth and the date of school, although the data type is different, but both output “YYYY-MM-DD” specified time format, right?

@excel (name = "yyyy ", format = "YYYY-MM-DD ", width = 20.0) private Date bornDate; @excel (name = "yyyyMMdd", databaseFormat = "yyyyMMdd", format = "YYYY-MM-DD ", width = 20.0) private String enterSchoolTime;Copy the code

. .

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

Four, 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 (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.