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

Keywords: Java

Author: slow time
Source: cnblogs.com/Tom-shushu/p/14279357.html

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-->
<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.13</version>
</dependency>

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
    System.setProperty("javax.xml.parsers.DocumentBuilderFactory",
            "com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl");
    String filename="Vehicle maintenance approval form.pdf";
    String path="e:/";
    response.setContentType("application/pdf");
    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);
        form.addSubstitutionFont(bf);
        // 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("repairCost",gwclwxsqBean.getRepairCost());
              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()) {
            form.setField(key,data.get(key).toString());
        }
        ps.setFormFlattening(true);
        log.info("*******************PDF Export succeeded***********************");
    } catch (Exception e) {　　　　　　　　　 log.error("*******************PDF Export failed***********************");
        e.printStackTrace();
    } finally {
        try {
            ps.close();
            reader.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    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 ExportYqfkdj.java

import lombok.Data;

/**
 * description:
 * @author: zhouhong
 * @version: V1.0.0
 * @date: 2021 January 14, 2013 3:05:54 PM
 */
@Data
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);
            list.add(dmKhfwdcDtjlZxDto);
        }
        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);
        result.setResults(list);
        result.setSuccess(true);
        result.setMsg(filepath);
    } catch (Exception e) {
        result.setSuccess(false);
        result.setMsg(YqfkdjUtils.Cytx.DCSB);
        e.printStackTrace();
        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>();
    head9.add("temperature(°C)");
    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");
    list.add(head0);
    list.add(head1);
    list.add(head2);
    list.add(head3);
    list.add(head4);
    list.add(head5);
    list.add(head6);
    list.add(head7);
    list.add(head8);
    list.add(head9);
    list.add(head10);
    list.add(head11);
    list.add(head12);
    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()];
        fis.read(buffer);
        fis.close();
        resp.reset();
        resp.setHeader("Content-Disposition",
                "attachment;filename=" + new String(filename.replaceAll(" ", "").getBytes("gbk")));
        resp.setHeader("Content-Length", "" + file.length());
        OutputStream os = new BufferedOutputStream(resp.getOutputStream());
        resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // output file
        os.write(buffer);
        os.flush();
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
        log.info(YqfkdjUtils.Cytx.DCSB);
        throw e;
    }
}