Use POI and export Excel reports
First, what is a POI?
1. Basic Concepts
Apache POI is the Apache Software Foundation’s open source functions library that provides apis for Java programs to read and write files in Microsoft Office format.
Ii. Basic structure
HSSF – Provides the function of reading and writing Microsoft Excel files.
XSSF – Provides the ability to read and write Microsoft Excel OOXML files.
HWPF – Provides the function of reading and writing Microsoft Word files.
HSLF – Allows you to read and write Microsoft PowerPoint files.
HDGF – Provides the ability to read and write Microsoft Visio files.
Concept first understand so much, specific can Google
At the beginning, copy the code of the company’s project, use some tool classes ExcelHelper, ExportUtil to create Excel, and export it, part of the code is as follows:
// ...
ExcelHelper.writeExcel(fileName, excelVOList, ProblemLiveVO.class, null, titles);
ExportUtil.exportToClient(response, ContentType.MULTIPART_FORM_DATA.toString(), ParamsUtil.EXCEL, fileName, true);
Copy the code
Later, I found that it was not appropriate to use this method. Generally, if we wanted to make a report, the product manager would first give us a report template. We could make the report template more beautiful (set the color of the table header, etc.), then read the template with POI, add and modify it, and finally export it directly to the client. It’s also more efficient. In fact, the use of poi is relatively simple, can refer to the following code :(detailed comments)
@Transactional(propagation = Propagation.REQUIRED, readOnly = true)
public ResponseEntity<byte[]> downloadExcel() {
// 1. Encapsulate data to List (encapsulate data according to actual situation)
List<ReportFinancial> list = reportFinancialDao.findAll();
try {
// 2. Read the report template
URL resource = this.getClass().getClassLoader()
.getResource("excel/NiceReportForm.xlsx");
Assert.notNull(resource, "Cannot read financial report template");
String path = resource.getPath();
FileInputStream fis = new FileInputStream(path);
String fileName = "Financial statements" + new SimpleDateFormat("yyyyMMddHHmmss")
.format(new Date()) + ".xlsx";
/ / create a Workbook
XSSFWorkbook workbook = new XSSFWorkbook(fis);
/ / read sheet1
XSSFSheet sheet = workbook.getSheetAt(0);
// Center the table
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 3. Add data to Excel
int rowLine = 2; // 2 is the third line, starting from 0 and set according to the actual situation
for (ReportFinancial report : list) {
XSSFRow row = sheet.createRow(rowLine++);
row.createCell(0).setCellValue(report.getReportFinancialId());
row.createCell(1).setCellValue(report.getNickName());
row.createCell(2).setCellValue(report.getMoney());
row.createCell(3).setCellValue(report.getCreateTime());
/ /... Add it based on actual data
// Center the table data added above
for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { row.getCell(i).setCellStyle(cellStyle); }}// 4. Write the workbook data to the output stream
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
baos.close();
// set the request header to return a ResponseEntity
HttpHeaders headers = new HttpHeaders();
headers.set(HttpHeaders.CONTENT_ENCODING, "UTF-8");
headers.setContentDispositionFormData("attachment", fileName);
headers.setPragma(fileName);
headers.setCacheControl("No-cache");
headers.setDate("Expires".0);
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
return new ResponseEntity<>(baos.toByteArray(), headers, HttpStatus.CREATED);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
Copy the code
- ResponseEntity
[]>
; ResponseEntity
[]>
; ResponseEntity
- The downloadExcel method can be used as a reference. The changes that need to be made are:
- The first step is to encapsulate the data according to the actual situation
- The second step is to place the template report under Resources/Excel
- Third, add the actual data to a row or column of the table. Row.createcell (0).setcellValue ()
There is, of course, another way to write data back to the client, to the output stream of HttpServletResponse Response, which is optional.
Finally, this article is just to achieve a simple Excel table, need more detailed Settings, you can see the API
huanglp
Guangzhou Reed Technology Java development team
Reed Technology – Guangzhou professional Internet software service company
Seize every detail, create every beauty
Follow our official account to learn more
Want to fight with us? Lagou search for “Reed Technology” or join us by sending your resume to [email protected]
Follow us, your comments and likes support us the most