使用Springboot创建REST API，将数据导出到Excel和PDF

使用Springboot创建REST API，将数据导出到Excel和PDF

介绍

在这篇文章中，我们将学习如何使用springboot创建用于将数据导出到excel和pdf的rest API，并且我们还将使用浏览器测试所有API，用于下载文件（excel和pdf）。

Springboot导出到Excel PDF文件

测试下载pdf和excel文件的API列表

要求

• Maven 3+
• Java 8+
• IDE（Intelij Idea）
• 浏览器（用于测试下载excel和pdf）

概述

• 为导出PDF文件添加OpenPDF依赖项
• 添加用于导出Excel文件的poi-ooxml依赖项
• 创建ReportController、ReportService、ReportRepo、ReportAbstractService
• 在ReportRepo上创建虚拟用户数据以导出文件
• 使用浏览器测试所有API以下载Excel和PDF文件

生成Springboot项目

• 首先，我们在link：https：//start.spring.io/上创建springboot项目，然后像这样在pom.xml上添加dependecies

   <!-- export to excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>



    <!-- export to pdf-->
        <dependency>
            <groupId>com.github.librepdf</groupId>
            <artifactId>openpdf</artifactId>
            <version>1.3.8</version>
        </dependency>

创建控制器、服务、Repo、ReportAbtstractService

• 第1步，创建UserReportController这个类有2个API，如下所示

测试下载pdf和excel文件的API列表

package com.deni.app.module.user.controller;

import com.deni.app.module.user.service.UserReportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@RestController
@RequestMapping("/v1/report/user")
public class UserReportController {

    @Autowired
    UserReportService userReportService;

    @GetMapping("/pdf/all")
    public void exportToPdf(HttpServletResponse response) throws IOException {
        this.userReportService.exportToPdf(response);
    }


    @GetMapping("/excel/all")
    public void exportToExcel(HttpServletResponse response) throws IOException {
        this.userReportService.exportToExcel(response);
    }
}

• 第二步，创建UserReportService这个类用于业务逻辑，这个类也有控制器要调用的函数

package com.deni.app.module.user.service;

import com.deni.app.module.user.dto.UserDTO;
import com.deni.app.module.user.repo.UserReportRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@Service
public class UserReportService {

    @Autowired
    UserExportToPdfService userExportToPdfService;

    @Autowired
    UserExportToExcelService userExportToExcelService;

    @Autowired
    UserReportRepo userReportRepo;


    public void exportToPdf(HttpServletResponse response) throws IOException {
        // get all user
        List<UserDTO> data = userReportRepo.getUserList();

        // export to pdf
        userExportToPdfService.exportToPDF(response, data);
    }


    public void exportToExcel(HttpServletResponse response) throws IOException {
        // get all user
        List<UserDTO> data = userReportRepo.getUserList();

        // export to pdf
        userExportToExcelService.exportToExcel(response, data);

    }


}

• 第3步，创建UserReportRepo这个类有服务调用的函数，有获取所有数据用户的伪函数

package com.deni.app.module.user.repo;

import com.deni.app.module.user.dto.UserDTO;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class UserReportRepo {
    public List<UserDTO> getUserList() {
        List<UserDTO> list = new ArrayList<>();
        list.add(UserDTO.builder().id(1L).username("admin").password("*****").roles("ADMIN,USER").permissions("READ,WRITE").active(1).blocked(0).createdDate(new Date()).createdBy("admin").updatedDate(new Date()).updatedBy("admin").build());

        list.add(UserDTO.builder().id(2L).username("user").password("*****").roles("USER").permissions("READ").active(1).blocked(0).createdDate(new Date()).createdBy("admin").updatedDate(new Date()).updatedBy("admin").build());

        list.add(UserDTO.builder().id(3L).username("deni").password("*****").roles("USER").permissions("READ").active(1).blocked(0).createdDate(new Date()).createdBy("admin").updatedDate(new Date()).updatedBy("admin").build());

        list.add(UserDTO.builder().id(4L).username("setiawan").password("*****").roles("USER").permissions("READ").active(1).blocked(0).createdDate(new Date()).createdBy("admin").updatedDate(new Date()).updatedBy("admin").build());

        list.add(UserDTO.builder().id(5L).username("manager").password("*****").roles("MANAGER").permissions("READ,WRITE,DROP").active(1).blocked(0).createdDate(new Date()).createdBy("admin").updatedDate(new Date()).updatedBy("admin").build());
        return list;
    }

}

• 第4步，创建ReportAbstractService文件此类将由UserExportToExcelService和UserExportToPdfService扩展。

ReportAbstractService.java

ReportAbstractService上有将数据导出到Excel和PDF的通用功能。

package com.deni.app.common.report;

import com.lowagie.text.*;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public abstract class ReportAbstract {


    // ----------------------
    // REPORT PDF
    // ----------------------

    public HttpServletResponse initResponseForExportPdf(HttpServletResponse response, String fileName) {
        response.setContentType("application/pdf");
        DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd:hh:mm:ss");
        String currentDateTime = dateFormatter.format(new Date());

        String headerKey = "Content-Disposition";
        String headerValue = "attachment; filename=pdf_" + fileName + "_" + currentDateTime + ".pdf";
        response.setHeader(headerKey, headerValue);
        return response;
    }


    public void writeTableHeaderPdf(PdfPTable table, String[] headers) {

        // for auto wide by paper  size
        table.setWidthPercentage(100);

        // cell
        PdfPCell cell = new PdfPCell();

        //  headers
        for (int i = 0; i < headers.length; i++) {
            cell.setPhrase(new Phrase(headers[i], getFontContent()));
            table.addCell(cell);
        }

    }


    public Font getFontTitle() {
        Font font = FontFactory.getFont(FontFactory.HELVETICA_BOLD);
        font.setSize(18);
        return font;
    }

    public Font getFontSubtitle() {
        Font font = FontFactory.getFont(FontFactory.HELVETICA);
        font.setSize(12);
        return font;
    }

    public Font getFontContent() {
        Font font = FontFactory.getFont(FontFactory.HELVETICA);
        font.setSize(10);
        return font;
    }

    public void enterSpace(Document document) {
        Paragraph space = new Paragraph(" ", getFontSubtitle());
        space.setAlignment(Paragraph.ALIGN_LEFT);
        document.add(space);
    }


    // ----------------------
    // REPORT EXCEL
    // ----------------------

    public XSSFWorkbook workbook;
    public XSSFSheet sheet;

    public void newReportExcel() {
        workbook = new XSSFWorkbook();
    }

    public HttpServletResponse initResponseForExportExcel(HttpServletResponse response, String fileName) {
        response.setContentType("application/octet-stream");
        DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd:hh:mm:ss");
        String currentDateTime = dateFormatter.format(new Date());

        String headerKey = "Content-Disposition";
        String headerValue = "attachment; filename=" + fileName + "_" + currentDateTime + ".xlsx";
        response.setHeader(headerKey, headerValue);
        return response;
    }

    public void writeTableHeaderExcel(String sheetName, String titleName, String[] headers) {

        // sheet
        sheet = workbook.createSheet(sheetName);
        org.apache.poi.ss.usermodel.Row row = sheet.createRow(0);
        CellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight(20);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);


        // title
        createCell(row, 0, titleName, style);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
        font.setFontHeightInPoints((short) 10);

        // header
        row = sheet.createRow(1);
        font.setBold(true);
        font.setFontHeight(16);
        style.setFont(font);
        for (int i = 0; i < headers.length; i++) {
            createCell(row, i, headers[i], style);
        }
    }

    public void createCell(org.apache.poi.ss.usermodel.Row row, int columnCount, Object value, CellStyle style) {
        sheet.autoSizeColumn(columnCount);
        org.apache.poi.ss.usermodel.Cell cell = row.createCell(columnCount);
        if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else {
            cell.setCellValue((String) value);
        }
        cell.setCellStyle(style);
    }

    public CellStyle getFontContentExcel() {
        CellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontHeight(14);
        style.setFont(font);
        return style;
    }

}

UserExportToExcelService.java

该类扩展到ReportAbstractService以使用通用函数，该类具有exportToExcel（）函数，并将由UserReportService类调用

package com.deni.app.module.user.service;

import com.deni.app.module.user.dto.UserDTO;
import com.deni.app.common.report.ReportAbstract;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@Service
public class UserExportToExcelService extends ReportAbstract {

    public void writeTableData(Object data) {
        // data
        List<UserDTO> list = (List<UserDTO>) data;

        // font style content
        CellStyle style = getFontContentExcel();

        // starting write on row
        int startRow = 2;

        // write content
        for (UserDTO UserDTO : list) {
            Row row = sheet.createRow(startRow++);
            int columnCount = 0;
            createCell(row, columnCount++, UserDTO.getId(), style);
            createCell(row, columnCount++, UserDTO.getUsername(), style);
            createCell(row, columnCount++, UserDTO.getPassword(), style);
            createCell(row, columnCount++, UserDTO.getRoles(), style);
            createCell(row, columnCount++, UserDTO.getPermissions(), style);
            createCell(row, columnCount++, UserDTO.getActive(), style);
            createCell(row, columnCount++, UserDTO.getBlocked(), style);
            createCell(row, columnCount++, UserDTO.getCreatedBy(), style);
            createCell(row, columnCount++, UserDTO.getCreatedDate().toString(), style);
            createCell(row, columnCount++, UserDTO.getUpdatedBy(), style);
            createCell(row, columnCount++, UserDTO.getUpdatedDate().toString(), style);

        }
    }


    public void exportToExcel(HttpServletResponse response, Object data) throws IOException {
        newReportExcel();

        // response  writer to excel
        response = initResponseForExportExcel(response, "UserExcel");
        ServletOutputStream outputStream = response.getOutputStream();


        // write sheet, title & header
        String[] headers = new String[]{"No", "username", "Password", "Roles", "Permission", "Active", "Bloked", "Created By", "Created Date", "Update By", "Update Date"};
        writeTableHeaderExcel("Sheet User", "Report User", headers);

        // write content row
        writeTableData(data);

        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }
}

UserExportToPdfService.java

该类扩展到ReportAbstractService以使用通用函数，该类具有exportToPdf（）函数，并将由UserReportService类调用

package com.deni.app.module.user.service;

import com.deni.app.module.user.dto.UserDTO;
import com.deni.app.common.report.ReportAbstract;
import com.deni.app.common.report.ReportPdfService;
import com.lowagie.text.Document;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfWriter;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@Service
public class UserExportToPdfService extends ReportAbstract implements ReportPdfService {

    public void writeTableData(PdfPTable table, Object data) {
        List<UserDTO> list = (List<UserDTO>) data;

        // for auto wide by paper  size
        table.setWidthPercentage(100);
        // cell
        PdfPCell cell = new PdfPCell();
        int number = 0;
        for (UserDTO item : list) {
            number += 1;
            cell.setPhrase(new Phrase(String.valueOf(number), getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getUsername(), getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getRoles(), getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getPermissions(), getFontContent()));
            table.addCell(cell);

            String active = item.getActive() == 1 ? "Active" : "Non Active";
            cell.setPhrase(new Phrase(active, getFontContent()));
            table.addCell(cell);

            String blocked = item.getBlocked() == 1 ? "Blocked" : "Non Blocked";
            cell.setPhrase(new Phrase(blocked, getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getCreatedBy(), getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getCreatedDate().toString(), getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getUpdatedBy(), getFontContent()));
            table.addCell(cell);

            cell.setPhrase(new Phrase(item.getUpdatedDate().toString(), getFontContent()));
            table.addCell(cell);
        }

    }


    public void exportToPDF(HttpServletResponse response, Object data) throws IOException {


        // init respose
        response = initResponseForExportPdf(response, "USER");

        // define paper size
        Document document = new Document(PageSize.A4);
        PdfWriter.getInstance(document, response.getOutputStream());

        // start document
        document.open();

        // title
        Paragraph title = new Paragraph("Report User", getFontTitle());
        title.setAlignment(Paragraph.ALIGN_CENTER);
        document.add(title);

        // subtitel
        Paragraph subtitel = new Paragraph("Report Date : 09/12/2022", getFontSubtitle());
        subtitel.setAlignment(Paragraph.ALIGN_LEFT);
        document.add(subtitel);

        enterSpace(document);

        // table header
        String[] headers = new String[]{"No", "username", "Roles", "Permission", "Active", "Bloked", "Created By", "Created Date", "Update By", "Update Date"};
        PdfPTable tableHeader = new PdfPTable(10);
        writeTableHeaderPdf(tableHeader, headers);
        document.add(tableHeader);

        // table content
        PdfPTable tableData = new PdfPTable(10);
        writeTableData(tableData, data);
        document.add(tableData);

        document.close();
    }

}

运行Springboot应用程序

• 运行Springboot应用程序，并确保该应用程序正在运行。

应用程序运行

应用程序运行

测试下载PDF文件

• 打开的浏览器
• 点击URL API下载PDF文件
• 文件将显示在浏览器的左下角

使用API成功下载pdf文件

测试下载Excel文件

• 打开的浏览器
• 点击url API下载Excel文件
• 文件将显示在浏览器的左下角

使用API成功下载excel文件

结论

最后，我们已经学习了如何使用springboot创建用于将数据导出到excel和pdf的rest API。

完整的源代码在这里