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