Springboot series (16): Integrated easyPOI to achieve excel multi-sheet export

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

In the previous few sessions we have learned the above content, such as how to achieve excel single sheet import and export, Excel multi-sheet import and so on, talking about multi-sheet group import, that will certainly think of how to achieve multi-sheet group export? Hahaha, there must be a way to export multiple sheets. In the last issue, we focused on how to integrate EasyPOI for the import of excel sheets with multiple sheets, so this issue echoes with the last issue, and bug bacteria will tell you how to realize the Excel export of multiple sheets through easyPOI.

Don’t worry, I’m just going to take you from scratch today. I hope you can prepare the lesson in advance. If you really meet this requirement in the project, you will definitely be the most beautiful kid in the audience.

“This requirement is very simple. I’ll take care of it in a few minutes.”

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

3. Export of multiple sheets in Excel

As follows, we first define two Excel export VO, in order to let everyone see clearly that different sheets store different data. At that time, it will also need to be used later, after all, corresponding to different VO export.

First, define two import entity classes.

ExportExcelLog.java

@Data public class ExportExcelLog 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 = "url", width = 20.0) private String url; @excel (name = "IP ", width = 20.0) private String IP; databaseFormat@excel (name = "request time ", format =" YYYY-MM-DD ", width = 20.0) private Date operationTime; @excel (name = "@excel ", width = 20.0) private int code; }Copy the code

ExportExcelUser.java

@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, this needs 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; @excel (name = "avatar ", type = 2, width = 30.0, height = 30.0, imageType = 1) private String image; @excel (name = "user description ", width = 20.0) private String describes; }Copy the code

I’ve defined the user and logInfo classes separately, but you can define either of them, it doesn’t have to be the same.

2. Controller adds excel import method

Next, we define an Excel export method that provides a port to test code integrity through browser access.

The specific code is as follows:

@getMapping ("/export-for-sheets") @apiOperation (value = "Excel export ", Notes = "Excel SheetUsers ") public void exportSheetUsers(HttpServletResponse Response) { userService.exportSheetUsers(response); }Copy the code

3. Define the import interface

/** * excel sheetUsers */ void exportSheetUsers(HttpServletResponse Response);Copy the code

4. Implement import method (core)

This is the core of this article, mainly to understand how to follow the steps of data encapsulation, and then use the Excel export method, otherwise you export Excel sheet is the same.

The specific implementation code is as follows:

UserServiceImpl.java

*/ @override public void exportSheetUsers(HttpServletResponse Response) { Alter table table_name Workbook Workbook = null; ExportParams userExportParams = new ExportParams(); / / set the sheet have to name userExportParams setSheetName (" user list "); / / set the sheet header name userExportParams. SetTitle (" user list "); Map<String, Object> userExportMap = new HashMap<>(); // sheetName userExportMap.put("title", userExportParams) is only set in ExportParams; Userexportmap. put("entity", exportExcelUser.class); List<ExportExcelUser> users = this.changeType(this.list()); Userexportmap. put("data", users); ExportParams logInfoExportParams = new ExportParams(); // Create a parameter object (used to set excel sheet2 contents and other information) ExportParams logInfoExportParams = new ExportParams(); LogInfoExportParams. SetTitle (" log list "); LogInfoExportParams. SetSheetName (" log tables "); // Create sheet2 map map <String, Object> logInfoExportMap = new HashMap<>(); logInfoExportMap.put("title", logInfoExportParams); logInfoExportMap.put("entity", ExportExcelLog.class); List<LogInfo> logInfoEntitys = logInfoMapper. SelectList (new QueryWrapper<>())); List<ExportExcelLog> logInfos = this.ChangeInfoType (logInfoEntitys); // To fill in the sheet2 data logInfoExPortMap. put("data", logInfos); List< map <String, Object>> sheetsList = new ArrayList<>(); // Add the sheet group; sheetsList.add(userExportMap); sheetsList.add(logInfoExportMap); / / execution method workBook = ExcelExportUtil. ExportExcel (sheetsList, ExcelType HSSF); / / set the coding format response. SetCharacterEncoding (StandardCharsets. UTF_8. Name ()); // Set the content type response.setContentType("application/octet-stream"); // Set the header and file name. response.setHeader("Content-Disposition", "attachment; Filename =" + URLEncoder. Encode (" user and operation log export.xls ", standardCharsets.utf_8.name ())); Workbook.write (response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); } finally { if (workBook ! = null) {try {// Close the stream workbook.close (); } catch (IOException e) { e.printStackTrace(); }}}}Copy the code

So let’s do a breakpoint here, just to see if the data that we assigned is being added.

In the screenshot below, it is clear that the data has been put in. Data is stored in data. There are two sheets, all arrays size = 2.

Above, I want to explain a few points in particular:

To viewexportExcel(List<Map<String, Object>> list, ExcelType type)

Put (), where the key must be “title”,”entity”, and “data”. It is already viewable in the source method, so parameter types, etc., must be set according to the three key_name put assignment.

There are sheets and there are sheetsMap<String, Object>, and then add the map toList<Map<String, Object>>In the can.

5. Browser test interface

Next, is ready, only owe the east wind, we only need to visit the interface of a try then know, whether your interface code has a problem, at present is to oneself also is not very convince yourself would have no problem, so for export interface, the best way is derived directly from the browser to call, in your browser’s address bar, type: http://localhost:8080/user/export-for-sheets, you can according to your definition of access interface address for a visit.

Obviously, it can be exported normally, a look at the size of 458KB, you can imagine that there must be data content, the key is whether there is a data sheet points? This has to hit a question, wait for a moment, download so that you can open it.

Export actual Excel screenshots:

Here are the actual sheet1 export data, and it’s clear that they are exactly the same as our own export VO Settings. Sheet1 exports data without problems.

The next thing to do is switch to Sheet2 to see if the data, fields and titles are all set.

See above screenshot, obviously, in an excel, success will be different export data in two of the same excel sheet table, this is thankfully, ha ha ha ha, after all, I’m afraid sent strong export effect, perpetuated this misunderstanding, also good, to obtain the basic to the current focus on the realization of the teaching content, so, If for you in front of the screen, see this, what are waiting for, quickly give bug bacteria a thumbs up, it is really too strong, ha ha ha, calculate these several episodes of content, more difficult knowledge points.

. .

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 (16) : integrated easyPOI to achieve excel multi-sheet import
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.