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 The pdf format is 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
Suppose that all our entity classes have been written, the data is obtained through the front end, and the template is located in the root directory of the 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 based on the form
ps = new PdfStamper(reader, os);
//4. Get pdf form
AcroFields form = ps.getAcroFields();
// 5 add Chinese font to the form} here the system font is used. 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) 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. Test
2, 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:
*/
@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
* @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
* @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;
}
}
d. Testing
The functions of generating, inserting and exporting PDF and Excel have been completed.