Received a abnormal demand: how to generate Excel + PDF export in Java?

Keywords: Java

Author: slow time

1, Preface

The export and import of Excel and PDF is a problem we often encounter in our work. Just today, the company's business encountered this problem. By the way, take a note to prevent the same problem from being helpless next time.

The company has two needs:

Requirement 1. A form is given to assemble the found data into the form and provide the download function in PDF format.

Demand 2. Find out the data and download it in the form of Excel.

2, Java realizes the functions of PDF generation, dynamic data insertion and export

1. Step 1: PDF template

Because the commonly used pdf software does not support editing, we first use WPS to edit in the form of Word to create the same style as the customer's requirements, and then directly save it as. pdf, as shown in the following figure:

a. Making templates in word

b. Change name to. pdf format

c. At this time, a software called Adobe Acrobat DC is required. The specific operations are as follows:

Open the PDF file whose name we just changed with Adobe Acrobat DC and click the "more tools" button in the lower right corner

Go to the following page and click the "prepare form" button

d. Next, you need to configure your data source in detail

The data source is the corresponding data in the entity class in your code (note that the fields must correspond one by one). After configuration, you can save it and write the following code.

2. Code writing

It is assumed that all our entity classes have been written, the data is imported and obtained through the front end, and the template is located in the root directory of E-disk. The name is: vehicle maintenance approval form.pdf

Import jar package:

<!-- PDF export-->
<!-- -->

Realize PDF generation, data insertion and export

@RegisterToSMP(serviceDisplay = "Preview page PDF download")
@RequestMapping(value = "/DM/gwclwxsq/qygl/exportPDF$m=query.service",method =RequestMethod.POST)
public String exportPdf(@RequestBody GwclwxsqBean gwclwxsqBean , HttpServletResponse response) throws UnsupportedEncodingException {
    // 1. Specify parser
    String filename="Vehicle maintenance approval form.pdf";
    String path="e:/";
    response.setHeader("Content-Disposition", "attachment;fileName="
            + URLEncoder.encode(filename, "UTF-8"));
    OutputStream os = null;
    PdfStamper ps = null;
    PdfReader reader = null;
    try {
        os = response.getOutputStream();
        // 2 read in pdf form
        reader = new PdfReader(path+ "/"+filename);
        // 3 generate a new pdf according to the form
        ps = new PdfStamper(reader, os);
        // 4 get pdf form
        AcroFields form = ps.getAcroFields();
        // 5 add Chinese font to the form. The system font is adopted here. If it is not set, Chinese may not be displayed
        BaseFont bf = BaseFont.createFont("C:/WINDOWS/Fonts/SIMSUN.TTC,1",
                      BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
        // 6 query data================================================
        Map<String, String> data = new HashMap<String, String>();
              data.put("commitTime", gwclwxsqBean.getCommitTime());
              data.put("driver", gwclwxsqBean.getDriver());
              data.put("carId", gwclwxsqBean.getCarId());
              data.put("carType", gwclwxsqBean.getCarType());
              data.put("repairAddress", gwclwxsqBean.getRepairAddress());
              data.put("project", gwclwxsqBean.getProject());
              data.put("fwbzzxfzrYj", gwclwxsqBean.getFwbzzxfzrYj());
              data.put("fgldspYj", gwclwxsqBean.getFgldspYj());
              data.put("remarks", gwclwxsqBean.getRemarks());
         // 7 traverse data to assign values to pdf form tables
        for (String key : data.keySet()) {
        ps.setFormFlattening(true);"*******************PDF Export succeeded***********************");
    } catch (Exception e) {          log.error("*******************PDF Export failed***********************");
    } finally {
        try {
        } catch (Exception e) {
    return null;


3. Testing

3, Java realizes Excel generation, data insertion and export

This is relatively simple. You can directly write the code (assuming that your entity class and query have been written). Note: one entity class is your own data entity class, and the other is the corresponding entity class in the table when you export. Summary of 100 Java intermediate and advanced interview questions

Let's take a real company business as an example (an Excel export function for counting the information of epidemic registration personnel)

a. The header corresponds to the entity class

import lombok.Data;

 * description:
 * @author: zhouhong
 * @version: V1.0.0
 * @date: 2021 January 14, 2013 3:05:54 PM
public class ExportYqfkdj {
     * Serial number
    private Integer xuhao;
     * full name
    private String xingming;
     * Identification Number 
    private String zjhm;
     * contact number
    private String lxdh;
     * Applicant's work unit
    private String sqrGzdw;
     * Have you been exposed to suspected cases
    private String sfjcgysbl;
     * Do you currently live with isolated persons at home
    private String sfyjjglrytz;
     * current state
    private String dqzt;
     * Current health status
    private String dqjkzt;

     * Current body temperature
    private String dqtw;
     * Current address
    private String dqszdz;
     * Current residential address
    private String dqjzdz;
     * Submission time
     * */
    private String tjsj;

b.Service layer

 * export
 * @param yqfkdjBean
 * @author zhouhong
 * @return
 * @throws Exception
@Transactional(rollbackFor = { Exception.class })
public DataResult exporYqfkdj(YqfkdjBean yqfkdjBean) throws Exception {
    DataResult result = new DataResult();
    List<ExportYqfkdj> list = new ArrayList<ExportYqfkdj>();
    try {
        /* Query export information */
        result = getYqfkMhCXQuery(yqfkdjBean);
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddhhmmssSSS");
        for (int i = 0; i < result.getTotalcount(); i++) {
            ExportYqfkdj dmKhfwdcDtjlZxDto = new ExportYqfkdj();
            dmKhfwdcDtjlZxDto = ObjectUtil.parsePojo(result.getResults().get(i), ExportYqfkdj.class);
            dmKhfwdcDtjlZxDto.setXuhao(i + 1);
        String filepath = "D:/Epidemic prevention and control information" + df.format(new Date()) + ".xlsx";
        if (System.getProperty(YqfkdjUtils.Wjdz.NAME).toLowerCase().startsWith(YqfkdjUtils.Wjdz.LI)
                || System.getProperty(YqfkdjUtils.Wjdz.NAME).toLowerCase().startsWith(YqfkdjUtils.Wjdz.LIN)) {
            filepath = "/home/Tomcat/temp/" + df.format(new Date()) + ".xlsx";
        EasyExcel.write(filepath, ExportYqfkdj.class).head(head()).sheet().doWrite(list);
    } catch (Exception e) {
        throw e;
    return result;
 * Export header of epidemic prevention and control information
 * @author zhouhong
 * @return List<List<String>>
private List<List<String>> head() {
    List<List<String>> list = new ArrayList<List<String>>();
    List<String> head0 = new ArrayList<String>();
    head0.add("Serial number");
    List<String> head1 = new ArrayList<String>();
    head1.add("full name");
    List<String> head2 = new ArrayList<String>();
    head2.add("Identification Number ");
    List<String> head3 = new ArrayList<String>();
    head3.add("contact number");
    List<String> head4 = new ArrayList<String>();
    head4.add("Work unit");
    List<String> head5 = new ArrayList<String>();
    head5.add("Contact with suspected cases");
    List<String> head6 = new ArrayList<String>();
    head6.add("Do you live with isolated personnel");
    List<String> head7 = new ArrayList<String>();
    head7.add("current state");
    List<String> head8 = new ArrayList<String>();
    head8.add("Current health status");
    List<String> head9 = new ArrayList<String>();
    List<String> head10 = new ArrayList<String>();
    head10.add("Current address");
    List<String> head11 = new ArrayList<String>();
    head11.add("Current residential address");
    List<String> head12 = new ArrayList<String>();
    head12.add("Submission time");
    return list;

c.Controller layer

@RegisterToSMP(serviceDisplay = "Epidemic prevention and control query export")
@RequestMapping(value = "/DM/yqfkdj/gr/yqfkdjdc$m=export.service", method = RequestMethod.POST)
public void exportKhfxxx(@RequestBody YqfkdjBean yqfkdjBean, HttpServletResponse resp) throws Exception {
    DataResult result = new DataResult();
    try {
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddhhmmssSSS");
        result = yqfkdjService.exporYqfkdj(yqfkdjBean);
        String filepath = result.getMsg().replace("\"", "");
        File file = new File(filepath);
        String filename = "Epidemic prevention and control information" + df.format(new Date()) + ".xlsx";
        InputStream fis = new BufferedInputStream(new FileInputStream(filepath));
        byte[] buffer = new byte[fis.available()];;
                "attachment;filename=" + new String(filename.replaceAll(" ", "").getBytes("gbk")));
        resp.setHeader("Content-Length", "" + file.length());
        OutputStream os = new BufferedOutputStream(resp.getOutputStream());
        // output file
    } catch (Exception e) {
        throw e;


The functions of generating, inserting and exporting PDF and Excel have been completed.

Reference link:

Recent hot article recommendations:

1.1000 + Java interview questions and answers (2021 latest version)

2.Stop playing if/ else on the full screen. Try the strategy mode. It's really fragrant!!

3.what the fuck! What is the new syntax of xx ≠ null in Java?

4.Spring Boot 2.6 was officially released, a wave of new features..

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!

Posted by SoreE yes on Mon, 22 Nov 2021 03:35:42 -0800