JAVA實現(xiàn)PDF和EXCEL生成和數(shù)據(jù)動態(tài)插入以及導出

一、序言

Excel、PDF的導出、導入是我們工作中經(jīng)常遇到的一個問題，剛好今天公司業(yè)務遇到了這個問題，順便記個筆記以防下次遇到相同的問題而束手無策。

公司有這么兩個需求：

需求一、給了一個表單，讓把查出來的數(shù)據(jù)組裝到表單中并且提供以PDF格式的下載功能。

需求二、將數(shù)據(jù)查出來以Excel表格的形式下載下來。

二、Java實現(xiàn)PDF的生成和數(shù)據(jù)動態(tài)插入、導出功能

1、第一步：PDF制作模板

因為PDF常用的軟件不讓支持編輯，我們就先使用WPS以Word的形式進行編輯制作出與客戶需求一樣的樣式，然后直接另存為 .pdf 的形式如下圖所示：

a.Word里面制作模板

b.更改名字為 .pdf形式

c.這時需要用到一個叫：Adobe Acrobat DC的軟件（可以白嫖7天^_^），具體操作如下：

用Adobe Acrobat DC打開我們剛才改過名字的PDF文件，點擊右下角的“更多工具”按鈕

到下面這個頁面再點擊“準備表單”按鈕

d.接下來就需要詳細的配置你的數(shù)據(jù)源了

數(shù)據(jù)源即：你代碼中實體類中對應的數(shù)據(jù)（注意字段一定要一一對應），配置完畢就可以保存進行下面的代碼編寫工作了。

2、代碼的編寫（假定我們實體類什么的都已經(jīng)編寫完成、數(shù)據(jù)通過前端傳入獲取、模板位置在E盤根目錄下名字為：車輛維修審批單.pdf）

導入jar包：

????com.itextpdf
????itextpdf
????5.5.13

實現(xiàn)生成PDF、數(shù)據(jù)插入、導出

????????@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?根據(jù)表單生成一個新的pdf
????????????????ps?=?new?PdfStamper(reader,?os);
????????????????//?4?獲取pdf表單
????????????????AcroFields?form?=?ps.getAcroFields();
????????????????// 5給表單添加中文字體?這里采用系統(tǒng)字體。不設置的話，中文可能無法顯示
????????????????BaseFont?bf?=?BaseFont.createFont("C:/WINDOWS/Fonts/SIMSUN.TTC,1",
??????????????????????????????BaseFont.IDENTITY_H,?BaseFont.EMBEDDED);
????????????????form.addSubstitutionFont(bf);
????????????????//?6查詢數(shù)據(jù)================================================
????????????????Map?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());???????????
?????????????????//?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.測試

二、Java實現(xiàn)Excel生成和數(shù)據(jù)插入、導出

這個比較簡單，直接上代碼（假定你的實體類、查詢什么的都已經(jīng)寫好）注意：實體類一個是你自己的數(shù)據(jù)實體類還有一個是你導出時表格中對應的實體類

我們以一個真實的公司業(yè)務來舉個例子（一個統(tǒng)計疫情登記人員信息的Excel導出功能）

1.表頭對應實體類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;
????/**
?????*?聯(lián)系電話
?????*/
????private?String?lxdh;????
????/**
?????*?申請人工作單位
?????*/
????private?String?sqrGzdw;????
????/**
?????*?是否接觸過疑似病例
?????*/
????private?String?sfjcgysbl;
????/**
?????*?當前是否與居家隔離人員同住
?????*/
????private?String?sfyjjglrytz;????
????/**
?????*?當前狀態(tài)
?????*/
????private?String?dqzt;
????/**
?????*?當前健康狀態(tài)
?????*/
????private?String?dqjkzt;

????/**
?????*?當前體溫
?????*/
????private?String?dqtw;
????/**
?????*?當前所在地址
?????*/
????private?String?dqszdz;
????/**
?????*?當前居住地址
?????*/
????private?String?dqjzdz;
????/**
?????*?提交時間
?????*?*/
????private?String?tjsj;
}

b.Service層

????/**
?????*?導出
?????*?@param?yqfkdjBean
?????*?@author?zhouhong
?????*?@return?
?????*?@throws?Exception
?????*/
????@Transactional(rollbackFor?=?{?Exception.class?})
????public?DataResult?exporYqfkdj(YqfkdjBean?yqfkdjBean)?throws?Exception?{
????????DataResult?result?=?new?DataResult();
????????List?list?=?new?ArrayList();
????????try?{
????????????/*?查詢導出信息?*/
????????????result?=?getYqfkMhCXQuery(yqfkdjBean);
????????????SimpleDateFormat?df?=?new?SimpleDateFormat("yyyyMMddhhmmssSSS");
????????????for?(int?i?=?0;?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>
?????*/
????private?List>?head()?{
????????List>?list?=?new?ArrayList>();
????????List?head0?=?new?ArrayList();
????????head0.add("序號");
????????List?head1?=?new?ArrayList();
????????head1.add("姓名");
????????List?head2?=?new?ArrayList();
????????head2.add("證件號碼");
????????List?head3?=?new?ArrayList();
????????head3.add("聯(lián)系電話");
????????List?head4?=?new?ArrayList();
????????head4.add("工作所在單位");
????????List?head5?=?new?ArrayList();
????????head5.add("是否接觸疑似病例");
????????List?head6?=?new?ArrayList();
????????head6.add("是否與隔離人員同住");
????????List?head7?=?new?ArrayList();
????????head7.add("當前狀態(tài)");
????????List?head8?=?new?ArrayList();
????????head8.add("當前健康狀態(tài)");
????????List?head9?=?new?ArrayList();
????????head9.add("體溫(°C)");
????????List?head10?=?new?ArrayList();
????????head10.add("當前所在地址");
????????List?head11?=?new?ArrayList();
????????head11.add("當前居住地址");
????????List?head12?=?new?ArrayList();
????????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層

????@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.測試

已經(jīng)全部完成PDF和Excel的生成、插入、導出功能。

Spring 中的 Controller 和Service是線程安全的嗎？
SpringBoot+ Dubbo + Mybatis + Nacos +Seata整合來實現(xiàn)Dubbo分布式事務
一個 SpringBoot 項目該包含哪些？
發(fā)現(xiàn)一款牛逼的IDEA插件：檢測代碼漏洞，一鍵修復！
springboot+redis+Interceptor+自定義annotation實現(xiàn)接口自動冪等
100W個微信紅包封面，速度領?。。。?/span>
API接口的安全設計驗證：ticket，簽名，時間戳
牛逼，一份基于SSM框架實現(xiàn)的支付寶支付功能，附完整源代碼...
Redis、Kafka或RabbitMQ，哪個更和微服務更般配？
點擊閱讀全文前往微服務電商教程