How to Generate and Export PDFs and Excel Files in Java with iText and EasyExcel

1. Introduction

Exporting data to Excel and PDF is a common requirement in many business applications. This article documents a solution for generating PDF and Excel files dynamically in a Java backend.

2. Requirements

Provide a form that assembles queried data and offers a PDF download.

Export queried data as an Excel spreadsheet.

3. PDF Generation and Data Insertion

Step 1: Create PDF Template

Design the PDF layout using WPS (or Word) and save it as a .pdf file.

Rename the file to .pdf.

Open the PDF with Adobe Acrobat DC, click "More Tools" to prepare the form.

Configure the data source to map fields from your Java entity to the PDF form.

Step 2: Write the Code (Assuming the entity class and template are ready)

Import Dependencies

<!-- PDF export -->
<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.13</version>
</dependency>

Implement 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 {
        System.setProperty("javax.xml.parsers.DocumentBuilderFactory", "com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl");
        String filename = "车辆维修审批单.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();
            // 1. Read PDF template
            reader = new PdfReader(path + "/" + filename);
            // 2. Create a new PDF based on the template
            ps = new PdfStamper(reader, os);
            // 3. Get the form fields
            AcroFields form = ps.getAcroFields();
            // 4. Set Chinese font to avoid garbled characters
            BaseFont bf = BaseFont.createFont("C:/WINDOWS/Fonts/SIMSUN.TTC,1", BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
            form.addSubstitutionFont(bf);
            // 5. Prepare data map
            Map<String, String> data = new HashMap<>();
            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());
            // 6. Fill the form fields
            for (String key : data.keySet()) {
                form.setField(key, data.get(key).toString());
            }
            ps.setFormFlattening(true);
            log.info("*******************PDF export successful***********************");
        } 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. Test

4. Excel Generation and Data Insertion

Below is a straightforward implementation using EasyExcel. Assume the entity classes and query logic are already in place.

1. Entity Class for Export (ExportYqfkdj.java)

import lombok.Data;
/**
 * description:
 * @author: zhouhong
 * @version: V1.0.0
 * @date: 2021-01-14
 */
@Data
public class ExportYqfkdj {
    private Integer xuhao;
    private String xingming;
    private String zjhm;
    private String lxdh;
    private String sqrGzdw;
    private String sfjcgysbl;
    private String sfyjjglrytz;
    private String dqzt;
    private String dqjkzt;
    private String dqtw;
    private String dqszdz;
    private String dqjzdz;
    private String tjsj;
}

b. Service Layer

@Transactional(rollbackFor = { Exception.class })
public DataResult exporYqfkdj(YqfkdjBean yqfkdjBean) throws Exception {
    DataResult result = new DataResult();
    List<ExportYqfkdj> list = new ArrayList<>();
    try {
        // Query export data
        result = getYqfkMhCXQuery(yqfkdjBean);
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddhhmmssSSS");
        for (int i = 0; i < result.getTotalcount(); i++) {
            ExportYqfkdj dto = new ExportYqfkdj();
            dto = ObjectUtil.parsePojo(result.getResults().get(i), ExportYqfkdj.class);
            dto.setXuhao(i + 1);
            list.add(dto);
        }
        String filepath = "D:/疫情防控信息" + 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;
}

private List<List<String>> head() {
    List<List<String>> list = new ArrayList<>();
    List<String> head0 = new ArrayList<>();
    head0.add("序号");
    List<String> head1 = new ArrayList<>();
    head1.add("姓名");
    List<String> head2 = new ArrayList<>();
    head2.add("证件号码");
    List<String> head3 = new ArrayList<>();
    head3.add("联系电话");
    List<String> head4 = new ArrayList<>();
    head4.add("工作所在单位");
    List<String> head5 = new ArrayList<>();
    head5.add("是否接触疑似病例");
    List<String> head6 = new ArrayList<>();
    head6.add("是否与隔离人员同住");
    List<String> head7 = new ArrayList<>();
    head7.add("当前状态");
    List<String> head8 = new ArrayList<>();
    head8.add("当前健康状态");
    List<String> head9 = new ArrayList<>();
    head9.add("体温(°C)");
    List<String> head10 = new ArrayList<>();
    head10.add("当前所在地址");
    List<String> head11 = new ArrayList<>();
    head11.add("当前居住地址");
    List<String> head12 = new ArrayList<>();
    head12.add("提交时间");
    Collections.addAll(list, head0, head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12);
    return list;
}

c. Controller Layer

@RegisterToSMP(serviceDisplay = "疫情防控查询导出")
@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 = "疫情防控信息" + 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");
        os.write(buffer);
        os.flush();
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
        log.info(YqfkdjUtils.Cytx.DCSB);
        throw e;
    }
}

d. Test

All PDF and Excel generation, data insertion, and export functionalities are now complete.