Poi introduction

Usually used to process Excel data, you can also process word, ppt readable and writable.

 

Component correspondence in Excel file and poi

1. An Excel file corresponds to a Workbook object
2. A Workbook can have multiple Sheet objects
3. A Sheet object consists of multiple Row objects
4. A Row object consists of multiple Cell objects

Operational steps for Excel

1. Open or create an Excel file object with Workbook
2. Create or retrieve a Sheet object with the Excel object of the previous step
3. Create or retrieve a Row object with a Sheet object
4. Create or retrieve a Cell object with a Row object
5. Read and write to ell object

The use of Poi

Add maven dependencies:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>

Write data to Excel:

		// Create an Excel object
        XSSFWorkbook sheets = new XSSFWorkbook();

        // Creating Form Sheet Objects
        XSSFSheet sheet = sheets.createSheet();

        // Creating Row Objects
        XSSFRow row1 = sheet.createRow(0);
        XSSFRow row2 = sheet.createRow(1);
        XSSFRow row3 = sheet.createRow(2);

        // Create Cell objects and write them

        // first line
        XSSFCell cell = row1.createCell(0);
        cell.setCellValue("Full name");
        XSSFCell cell1 = row1.createCell(1);
        cell1.setCellValue("Age");

        // Second elements
        cell = row2.createCell(0);
        cell.setCellValue("Zhang San");
        cell1 = row2.createCell(1);
        cell1.setCellValue("22");

        // Second elements
        cell = row3.createCell(0);
        cell.setCellValue("Li Si");
        cell1 = row3.createCell(1);
        cell1.setCellValue("20");

        String excelName = "Testing.xlsx";
        response.setContentType("application/octet-stream");
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(excelName.getBytes("UTF-8"), "ISO8859-1"));
            // IOUtils.copy()
            sheets.write(outputStream);
            response.flushBuffer();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

Read the data in Excel:

public static List<List<Object>> readExcelData(InputStream inputStream){
        List<List<Object>> allList = null;
        try {
            XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
            if(null == sheets){
                return null;
            }
            //Traversing all sheet s in Excel
            allList = new ArrayList<>();
            for (Sheet sheet : sheets) {
                //Traverse all rows in the current sheet
                for (Row row: sheet) {
                    //Traverse all columns
                    List<Object> cellList = new ArrayList<>();
                    for (int c=row.getFirstCellNum();c<row.getLastCellNum();c++) {
                        Cell cell = row.getCell(c);
                        if (null != cell){
                            cellList.add(cell.getStringCellValue());
                        }else {
                            cellList.add("");
                        }
                    }
                    allList.add(cellList);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return allList;
    }