Java implements pdf and Excel generation and dynamic data insertion and export

First, the preamble

Excel, PDF export, import is a problem we often encounter in our work, just today the company's business encountered this problem, take a note in case the next time encounter the same problem and helpless.

The company has two needs:

Requirement i, give a form, let the data found into the form and provide pdf download function.

Demand 2, the data to find out in the form of Excel table download down.

Second, Java to achieve PDF generation and data dynamic insertion, export functions

1, the first step: PDF production template

Because PDFs are commonly used software that doesn't allow editing to be supported, we first use WPS to edit as Word to create the same style as the customer's needs, and then save directly as .pdf as shown in the following image:

a.Word makes templates

b. Change the name to .pdf form

c. A piece of software called Adobe Acrobat DC is required, as follows:

Open the PDF file we just renamed with Adobe Acrobat DC and click the "More Tools" button in the lower right corner

Go to this page below and click on 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), the configuration is ready to save for the following code writing work.

2, the writing of code

Suppose that our entity class or something has been written, that the data is passed in and out through the front end, and that the template location is named under the E root directory: vehicle repair approval form .pdf

Import the jar package:

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

Implement build PDFs, data insertion, and export

@RegisterToSMP(serviceDisplay = "预览页面PDF下载")      
@RequestMapping(value = "/DM/gwclwxsq/qygl/exportPDF$m=query.service",method =RequestMethod.POST) 
public String exportPdf(@RequestBody GwclwxsqBean gwclwxsqBean , HttpServletResponse response) throws UnsupportedEncodingException {            
    // 1.指定解析器
    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();
        // 2 读入pdf表单
        reader = new PdfReader(path+ "/"+filename);
        // 3 根据表单生成一个新的pdf
        ps = new PdfStamper(reader, os);
        // 4 获取pdf表单
        AcroFields form = ps.getAcroFields();
        // 5给表单添加中文字体 这里采用系统字体。不设置的话，中文可能无法显示
        BaseFont bf = BaseFont.createFont("C:/WINDOWS/Fonts/SIMSUN.TTC,1",
                      BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
        form.addSubstitutionFont(bf);
        // 6查询数据================================================
        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遍历data 给pdf表单表格赋值
        for (String key : data.keySet()) {
            form.setField(key,data.get(key).toString());
        }
        ps.setFormFlattening(true);       
        log.info("*******************PDF导出成功***********************");
    } catch (Exception e) {          log.error("*******************PDF导出失败***********************");
        e.printStackTrace();
    } finally {
        try {
            ps.close();
            reader.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return null;
}

3. Test

Second, Java to implement Excel generation and data insertion, export

This is relatively simple, go straight to the code (assuming that your entity class, query, or anything has been written) Note that the entity class is one of your own data entity classes and one is the entity class that corresponds to the table when you export. A summary of 100 Java mid-to-high-level interview questions

Let's take a real company business as an example (an Excel export feature that counts information about outbreak registrants)

a. The header corresponds to the entity class ExportYqfkdj .java

import lombok.Data;

/**
 * description: 
 * @author: zhouhong
 * @version: V1.0.0
 * @date: 2021年1月14日 下午3:05:54
 */
@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

/**
 * 导出
 * @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 {
        /* 查询导出信息 */
        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:/疫情防控信息" + 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;
}
/**
 * 疫情防控信息导出表头
 * @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("序号");
    List<String> head1 = new ArrayList<String>();
    head1.add("姓名");
    List<String> head2 = new ArrayList<String>();
    head2.add("证件号码");
    List<String> head3 = new ArrayList<String>();
    head3.add("联系电话");
    List<String> head4 = new ArrayList<String>();
    head4.add("工作所在单位");
    List<String> head5 = new ArrayList<String>();
    head5.add("是否接触疑似病例");
    List<String> head6 = new ArrayList<String>();
    head6.add("是否与隔离人员同住");
    List<String> head7 = new ArrayList<String>();
    head7.add("当前状态");
    List<String> head8 = new ArrayList<String>();
    head8.add("当前健康状态");
    List<String> head9 = new ArrayList<String>();
    head9.add("体温(°C)");
    List<String> head10 = new ArrayList<String>();
    head10.add("当前所在地址");
    List<String> head11 = new ArrayList<String>();
    head11.add("当前居住地址");
    List<String> head12 = new ArrayList<String>();
    head12.add("提交时间");
    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 = "疫情防控查询导出")
@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

The build, insert, and export capabilities for PDFs and Excels have been completed.