POI profile

Apache POI is the Apache Software Foundation’s open source library. POI provides APIS for Java programs to read and write files in Microsoft Office formats, including Excel, Word, PowerPoint, etc.

Official website: poi.apache.org/

Preknowledge of POI

1. Coordinate

Every cell in Excel has a coordinate. The starting coordinate is (0, 0), which means the start behavior is row 0 and the start column is column 0.

Every Sheet in Excel has subscripts, starting at 0.

2. Data type

There are six cell data types defined in the POI, each of which is represented by a numeric constant:

  • CELL_TYPE_NUMERIC: value 0, numeric type and Date type (int, float, Date…)
  • CELL_TYPE_STRING: Value 1, String type (String)
  • CELL_TYPE_FORMULA: the value is 2 and the calculation formula type
  • CELL_TYPE_BLANK: value is 3, null, meaning there is nothing in the cell (null)
  • CELL_TYPE_BOOLEAN: Value is 4, Boolean type (Boolean)
  • CELL_TYPE_ERROR: The value is 5 and the type is wrong

The most common things stored in Excel are character data and numeric data:

  • Character data is similar to “ABC” and “AB123”. POI is stored as CELL_TYPE_STRING by default, and converted to Java as String through POI.

  • Numeric and date data are similar to “123”, “2021/12/8” (dates are computed as numbers using a specific algorithm), and POI is stored as CELL_TYPE_NUMERIC by default, which is converted to the Java type double through POI.

    If Excel needs to store data like a phone number, the default is the CELL_TYPE_NUMERIC type, but you can change the type to CELL_TYPE_STRING.

  • Boolean data is similar to “TRUE”, “TRUE”, and POI is stored as CELL_TYPE_BOOLEAN by default. The type converted to Java by POI is Boolean.

3. File type

Excel has undergone two big changes, namely Excel 2003 and Excel 2007.

Prior to Excel 2003, only.xls files were supported. After Excel 2007, both.xls and.xlsx files were supported.

Therefore, POI provides two independent read/write interfaces (HSSF and XSSF) for.xls and.xlsx files.

4. Work ahead

Importing dependencies in an XML file:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
Copy the code

Excel Basic Reading and writing

1. HSSF

1.1 write operation

@Test
public void testHSSFWrite(a) throws IOException {
    // Create a file
    Workbook workbook = new HSSFWorkbook();
    / / create a Sheet
    Sheet sheet = workbook.createSheet("sheet1");
    // Create line 0
    Row row = sheet.createRow(0);
    // Create cell 0 on line 0
    Cell cell = row.createCell(0);
    // write at (0,0)
    cell.setCellValue("Hello POI");

    / / write
    FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\hssf-write.xls");
    workbook.write(outputStream);

    // Close the output stream
    outputStream.close();
}
Copy the code

1.2 read operation

@Test
public void testHSSFRead(a) throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\hssf-read.xls");

    Workbook workbook = new HSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    // read the content of the character at (0,0)
    String content = cell.getStringCellValue();
    System.out.println(content);

    inputStream.close();
}
Copy the code

2. XSSF

2.1 write operation

@Test
public void testXSSFWrite(a) throws IOException {
    // Create a workbook
    Workbook workbook = new XSSFWorkbook();
    / / create a Sheet
    Sheet sheet = workbook.createSheet("sheet1");
    // Create line 0
    Row row = sheet.createRow(0);
    // Create cell 0 on line 0
    Cell cell = row.createCell(0);
    // write at (0,0)
    cell.setCellValue("Hello POI");

    / / write
    FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\xssf-write.xlsx");
    workbook.write(outputStream);

    // Close the output stream
    outputStream.close();
}
Copy the code

2.2 read operation

@Test
public void testXSSFRead(a) throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\xssf-read.xlsx");

    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    // read the content of the character at (0,0)
    String content = cell.getStringCellValue();
    System.out.println(content);

    inputStream.close();
}
Copy the code

Excel big data reading and writing

HSSF can read and write a maximum of 65535 rows at a time. If the number of rows exceeds 65535, the data cannot be read or written.

A maximum of 1048,576 rows and 16,384 columns can be read and written using XSSF. Theoretically, large data can be read and written, but the speed is very slow, the time is uncontrollable, and memory overflow may occur. The reason is that data is stored in the memory as long as the read/write is not completed, regardless of whether the data has been persisted. As a result, in big data scenarios, memory overflow will occur.

Therefore, THE POI provides a set of dedicated read/write interfaces for big data. SXSSF supports only.xlsx files.

1. The read operation

@Test
public void testHSSFWrite(a) throws IOException {
    // Create a workbook
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
    / / create a Sheet
    Sheet sheet = sxssfWorkbook.createSheet("sheet1");
    // Create line 0
    Row row = sheet.createRow(0);
    // Create cell 0 on line 0
    Cell cell = row.createCell(0);
    // write at (0,0)
    cell.setCellValue("Hello POI");

    / / write
    FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\sxssf-write.xlsx");
    sxssfWorkbook.write(outputStream);

    // Clear temporary files in memory
    sxssfWorkbook.dispose();

    // Close the output stream
    outputStream.close();
}
Copy the code

2. Write operations

@Test
public void testSXSSFRead(a) throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\sxssf-read.xlsx");

    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
    // The size of the memory window is 1000, which means that the maximum number of newly created data can be seen from the Sheet window
    SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, 1000);

    Sheet sheet = xssfWorkbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);

    // read the content of the character at (0,0)
    String content = cell.getStringCellValue();
    System.out.println(content);

    inputStream.close();
}
Copy the code

POI of actual combat

Read all the data from the Excel file named student-grade. XLSX into the program.

  1. Introduce POI dependencies in XML files

    <! -- POI -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>
Copy the code

  2. Custom unit type exception CellTypeException

  3. Create a unit type exception enumeration object in ResultCode

  4. Configure the unit type exception as a global exception

  5. Create an object that stores the read in data

    @Data
public class Student {

    private String university;

    private String studentId;

    private String name;

    private Integer score;

    private Integer credit;

    private Date gmtCreate;

}
Copy the code

  6. Create POIUtils

    /** * POI tools *@author admin
 */
public class POIUtils {

    /** * gets the cell value *@paramCell *@returnThe value of the cell */
    public static Object getCellValue(XSSFCell cell) {
        Object value = null;

        if(cell ! =null) {
            // Get the cell type
            int type = cell.getCellType();
            / / Numeric types
            if (type == XSSFCell.CELL_TYPE_NUMERIC) {
                // Determine whether it is a date type or a numeric type
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else{ value = cell.getNumericCellValue(); }}/ / type String
            else if (type == XSSFCell.CELL_TYPE_STRING) {
                value = cell.getStringCellValue();
            }
            / / a Boolean type
            else if (type == XSSFCell.CELL_TYPE_BOOLEAN) {
                value = cell.getBooleanCellValue();
            }
            / / the Error types
            else if (type == XSSFCell.CELL_TYPE_ERROR) {
                throw newCellTypeException(ResultCode.CELL_TYPE_ERROR); }}return value;
    }

    /** * CELL_TYPE_STRING -- > String *@paramThe cell unit *@returnData * /
    public static String getStringValue(XSSFCell cell) {
        return (String) getCellValue(cell);
    }

    /** * CELL_TYPE_NUMERIC (numeric, scientific) -- > String (normal) *@paramThe cell unit *@returnData * /
    public static String getDouble2StringValue(XSSFCell cell) {
        // Round double data represented by scientific notation to integer data
        BigDecimal bigDecimal = BigDecimal.valueOf((Double) getCellValue(cell)).setScale(0, BigDecimal.ROUND_DOWN);

        return bigDecimal.toString();
    }

    /** * CELL_TYPE_NUMERIC (number) -- > Integer *@paramThe cell unit *@returnData * /
    public static Integer getDouble2IntegerValue(XSSFCell cell) {
        // Round double data to integer data
        return (int) Math.round((double) getCellValue(cell));
    }

    /** * CELL_TYPE_NUMERIC (Date) -- > Date *@paramThe cell unit *@returnData * /
    public static Date getDateValue(XSSFCell cell) {
        return (Date) getCellValue(cell);
    }

    /** * Populates the Student property *@paramColumnIndex Index of the cell column *@paramThe cell unit *@paramStudent student object */
    public static void setStudentProperty(int columnIndex, XSSFCell cell, Student student) {
        if (columnIndex == 0) {
            student.setUniversity(getStringValue(cell));
        } else if (columnIndex == 1) {
            student.setStudentId(getDouble2StringValue(cell));
        } else if (columnIndex == 2) {
            student.setName(getStringValue(cell));
        } else if (columnIndex == 3) {
            student.setScore(getDouble2IntegerValue(cell));
        } else if (columnIndex == 4) {
            student.setCredit(getDouble2IntegerValue(cell));
        } else{ student.setGmtCreate(getDateValue(cell)); }}}Copy the code

    Except for the first method in the utility class, all methods are fixed depending on the data to be imported.

    For example, if you want to import student-grade. XLSX data, each row of the school and name fields is received using getStringValue, and the student number field is received using getDouble2StringValue. The score and credit field data are received using getDouble2IntegerValue, and the upload date is received using getDateValue. After receiving the data, the setStudentProperty method is used to assemble the data into the PO to persist it into the database.

  7. Create a read operation

    @Test
public void read(a) throws IOException {
    FileInputStream inputStream = new FileInputStream("G:\\file\\student-grade.xlsx");

    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = workbook.getSheetAt(0);

    // Get the number of rows
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int i = 1; i < rowCount; i ++) {
        XSSFRow row = sheet.getRow(i);

        Student student = new Student();
        // Get the number of columns
        int cellCount = row.getPhysicalNumberOfCells();
        for (int j = 0; j < cellCount; j ++) {
            XSSFCell cell = row.getCell(j);
            // Load data
            POIUtils.setStudentProperty(j, cell, student);
        }

   		// Persist student to the database
    }

    inputStream.close();
}
Copy the code