Springboot series (16): EasyPOI excel template export PDF files

“This is the 26th 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 previous few sessions, we mainly talked about how to integrate EasyPOI to achieve excel import and export, Word export function, right? I don’t know how you have mastered it, if you still have questions about any of the following, please ask more questions, ha ha ha, although I am not a big man, but I will try my best to teach each other, learn together, to fill in the gaps, can grow fast.

How does Springboot integrate with EasyPOI
Integrate EasyPOI to realize Excel import function
Integrated EasyPOI to achieve Excel export function
Integrated easyPOI to realize the function of exporting multiple pages from single Word template
Integrated easyPOI to achieve Excel template image export
Integrated EasyPOI to achieve excel multi-sheet import and export

In fact, in addition to the above documents, there is another scenario is to export documents in PDF format. I don’t know whether you have encountered it in your daily development. Excel template is directly exported to PDF document format, although it can be manually converted to PDF, but it is required that the development can achieve excel template into PDF.

In fact, the implementation process is quite simple, after all, I have taught you how to achieve excel export through EasyPOi, we just need to excel export on the basis of, and then write the stream to the PDF format document. That’s the basic idea.

But don’t worry, I’m just here to take you from scratch. I hope you can prepare the lesson in advance. If you really meet this need, you will definitely be the most beautiful kid in the room.

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

We will rely on EasyPOi to implement excel template export. 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

In addition, we need to reference a package specifically for the Excel to PDF tool. So we’re going to import that as well. However, this package is currently charged, remote Maven image can not be pulled locally, so we need to local guide this package.

Here is an import method that I provide that references local JARS without automatically pulling them from maven sources. I don’t know the details. You can just follow the following format.

<! --> <dependency> <groupId> e-iceBlue </groupId> <artifactId> Spire.xls. Free </artifactId> < version > 2.2.0 < / version > < scope > system < / scope > < systemPath > ${project. The basedir} / libs/spire. XLS. Free - 2.2.0. Jar < systemPath >  </dependency>Copy the code

Import jar directly into the local directory, please see the screenshot below:

As for the JAR package download, you can download it by yourself. Originally, I added the cloud disk address, but it did not show that it was shielded, so we need to download it by ourselves, and provide you with a download address download link.

Iii. Import of Multiple sheets and Excel

1. Define an Excel template

Since we need to read the Excel template, we must define the Excel template and then render the template, so we will do the same internal loop of assignment through FE.

Please see the screenshot for details:

2. Controller adds excel import method

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

@getMapping ("/export-for-pdf") @apiOperation (value = "export PDF file ", The notes = "export PDF file") public void exportPdfUsers (HttpServletResponse response) {userService. ExportPdfUsers (response); }Copy the code

3. Define import PDF interface

/** * export PDF file */ void exportPdfUsers(HttpServletResponse Response);Copy the code

4. Implement import method (core)

ImportExcel () : easyPOI ExcelExportUtil Workbook class importExcel() : easyPOI ExcelExportUtil Workbook class Finally, use the newly imported spire.xls. Free to write the PDF and export it to the specified directory for saving. This is the general idea, please see the implementation code below, if you have a better way to implement, please remember que ME oh.

The specific implementation code is as follows:

*/ @override public void exportPdfUsers(HttpServletResponse Response) { Object> mapList = new HashMap<>(); List<Map<String, Object>> listUsers = new ArrayList<>(); Try {// Specify excel template; TemplateExportParams params = new TemplateExportParams = new TemplateExportParams("./template/ Export excel template.xlsx "); List<UserEntity> users = this.list(); AtomicInteger AtomicInteger = new AtomicInteger(1); users.forEach(user -> { Map<String, Object> map = new HashMap<>(); map.put("id", atomicInteger.getAndIncrement()); map.put("name", user.getName()); map.put("age", user.getAge()); map.put("sex", user.getSex()); map.put("address", user.getAddress()); map.put("describes", user.getDescribes()); // To add a map to the collection, a line is listusers.add (map); }); mapList.put("users", listUsers); / / call exportExcel () Workbook Workbook. = ExcelExportUtil exportExcel (params, mapList); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); // write the excel file to the new outputStream workbook.write(outputStream); ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.tobyteArray ()); Workbook com.spire.xls.Workbook wb = new com.spire.xls.Workbook(); wb.loadFromStream(inputStream); // Set the field to display full WB.getConverterSetting ().setSheetFitTopage (true); Wb. saveToFile("D:/ PDF/student basic information table.pdf"); } catch (Exception e) { e.printStackTrace(); }}Copy the code

Note:

Point 1: I am exporting the PDF to specify the directory as localD:/ PDF/Student Basic information.pdfIf there is no PDF folder, one will be created automatically. But if you’re a requirements developer, you’ll have to get the directory for your actual development environment, so I’m just demonstrating here. In fact, you can also open a hole in the page, by the user to manually select the location, and then the directory address selected by the user as an input parameter, so that the user can not worry about where to save.
Second: In line with the Excel template export method, this subsequent export method can be wrapped into a call.
Thirdly, the following is a screenshot of the directory structure of my demo project to store excel export templates, so I directly wrote the relative location of the template.

Now, all that’s left for us is testing.

5. Browser testing

We go to the browser, go to the address bar, type in the address of the interface that we just exposed in the Controller,

Like me: http://localhost:8080/user/export-for-pdf. You just type in your interface address and press Enter to access it.

The browser access screenshot is as follows:

After entering the address press enter, check whether there is an error on the console. Let’s check whether the specified folder already contains the XXX.pdf file. The actual result can be seen that XXX. PDF file has been saved in the PDF folder, which proves that the PDF export method has no problem, or it is very successful. The remaining thing is to check whether the PDF document data and typeset format are the same as scheduled.

When I opened the PDF document, I did not expect that the fields on one page would not be fully displayed, but should be displayed in pages. This is not very friendly, so I must think of a way to solve it. Set.setsheetfitTopage () to true to display all columns on a single page.

The following is a screenshot of an exported PDF without setting this attribute:

Let’s add this property to it and set it to true, let’s restart the project export and see what happens.

// Set the field to display full WB.getConverterSetting ().setSheetFitTopage (true);Copy the code

After adding this property, see the screenshot below. Compared to the above, it is obvious that all fields are displayed on one page instead of one page.

. .

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

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