The full text comes from the explanation of official documents and relevant videos , The author takes notes with ！

Common scenarios ：

1. Export user information as Excel form （ export xls data …）
2. take Excel Enter the information in the table into the website database （ Exercise upload … The company asks the teacher to fill in the question to excel Then upload the website , The website will take advantage of relevant POI Backfill the data to the database , This greatly reduces the amount of website entry .）

In development, we often design excel To deal with , Such as export Excel, Import Excel To database ！
operation Excel What's popular now is Apache POI and Alibaba EasyExcel！

Apache POI

Apache POI Official website ：

Apache POI Official website ：https://poi.apache.org/

  Apache POI yes Apache Software Foundation's open source library , use Java Write free open source cross platform Java API.Apache POI Provide API to Java Program pair Microsoft Office Format file reading and writing functions .POI by “Poor Obfuscation Implementation” An acronym for , Meaning for “ Simple version of fuzzy implementation ”.

Basic function ：

HSSF － Provide reading and writing Microsoft Excel XLS The function of format file .
XSSF － Provide reading and writing Microsoft Excel OOXML XLSX The function of format file .
HWPF － Provide reading and writing Microsoft Word DOC The function of format file .
HSLF － Provide reading and writing Microsoft PowerPoint The function of format file .
HDGF － Offer to read Microsoft Visio The function of format file .
HPBF － Offer to read Microsoft Publisher The function of format file .
HSMF － Offer to read Microsoft Outlook The function of format file .

Excel Version difference ：03 Version and 07 edition

03 edition 2003 edition .xls： The number of lines is only 65535 That's ok , Too much to put .

07 edition 2007 edition .xlsx： unlimited

POI-Excel Write

Create common Maven project ：

Java edition ：1.8

After creating the project , Import dependence pom.xml：

<!-- Import dependence -->
    <dependencies>
      <!-- excel2003 edition  xls -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

      <!-- excel 2007 edition  xlsx -->
        <dependency>-->
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

        <!--  Date formatting tool  -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>

        <!-- test  unit testing  -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

Java Programming , Everything is object ：

excel Each object in the table ： workbook 、 Worksheet 、 That's ok 、 Column 、 Cell

Implementation class , write in

First create the workbook , Create worksheet after , Then there are rows and columns , Finally, the data ：

public class ExcelWriteTest {
     //03 Version implementation class 

String PATH = "/Users/**/Documents/IdeaProjects/guo_poi/src/main/resources/data/" ;// Set fixed file path 

    @Test  // establish 03 edition 
    public void testWrite03() throws Exception{
    
        // 1. Create a workbook 
        Workbook workbook = new HSSFWorkbook();  // There are differences in objects  HSSF
        // 2. Create a worksheet 
        Sheet sheet = workbook.createSheet("guo1");  //sheet  The name is ：guo1
        // 3. Create a line  （1,1)
        Row row11 = sheet.createRow(0);  // first line 
        // 4.  Create a cell 
        Cell cell11 = row11.createCell(0);  // first line , The grid in the first column （0,0)
        cell11.setCellValue(" Number of people today ");  // Set the value 

        //(1,2)
        Row row12 = sheet.createRow(1);
        Cell cell12 = row12.createCell(0);
        cell12.setCellValue(666);

        //  The second line 
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue(" Count the time ");
        // (2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-mm-dd HH:mm:ss");
        cell22.setCellValue(time);

        // Generate a table (IO flow ) 03 edition   Is the use of xls ending ！
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03 Version Statistics .xls");

        // Output 
        workbook.write(fileOutputStream);
        // Closed flow 
        fileOutputStream.close();

        System.out.println("03 Version Statistics .xls  Generation completed ！");
    }
}

public class ExcelWriteTest {
     //07 Version implementation class   and 03 The version is written under the same class 
  // Set fixed file path , And 03 identical 
String PATH = "/Users/**/Documents/IdeaProjects/guo_poi/src/main/resources/data/" ;
    @Test // establish 07 edition 
    public void testWrite07() throws Exception{
    

        // 1. Create a workbook 
        Workbook workbook = new XSSFWorkbook();  // There are differences in objects  XSSF
        // 2. Create a worksheet 
        Sheet sheet = workbook.createSheet("guo2");  //sheet  The name is ：guo1
        // 3. Create a line  （1,1)
        Row row11 = sheet.createRow(0);  // first line 
        // 4.  Create a cell 
        Cell cell11 = row11.createCell(0);  // first line , The grid in the first column （0,0)
        cell11.setCellValue(" Number of people today ");  // Set the value 

        //(1,2)
        Row row12 = sheet.createRow(1);
        Cell cell12 = row12.createCell(0);
        cell12.setCellValue(666);

        //  The second line 
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue(" Count the time ");
        // (2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-mm-dd HH:mm:ss");
        cell22.setCellValue(time);

        // Generate a table (IO flow ) 03 edition   Is the use of xlsx ending ！ // There are differences 
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07 Version Statistics .xlsx");

        // Output 
        workbook.write(fileOutputStream);
        // Closed flow 
        fileOutputStream.close();

        System.out.println("07 Version Statistics .xlsx  Generation completed ！")
    }
}

03 And 07 The only difference is that the file suffix is different ！

Write data in bulk ： Large file write HSSF (03 edition )

shortcoming ： At most, it can only deal with 65536 That's ok , Otherwise, an exception will be thrown

java.lang.11legalArgumentException : Invalid row number (65536) outside allowable range (0..65535)

advantage ： Write cache in process , Do not operate disk , The last one-time write to disk , Fast .

 // Set fixed file path , And 03 identical 
String PATH = "/Users/**/Documents/IdeaProjects/guo_poi/src/main/resources/data/" ;
   
    @Test  //03 Large amount of data 
    public void testWrite03BigData() throws IOException {
    
        // Time difference  begin  and  end  The difference between the 
        long begin = System.currentTimeMillis();

        //  Create Workbook 
        Workbook workbook = new HSSFWorkbook(); //03 Version object  HSSF
        //  Create sheet 
        Sheet sheet = workbook.createSheet();
        //  Write data 
        for( int rowNum = 0; rowNum < 65536; rowNum++){
     //65537 Will report a mistake 
            Row row = sheet.createRow(rowNum);    //  That's ok 
            for (int cellNum = 0; cellNum <10; cellNum++){
      // I've set it up here 10 Column 
                Cell cell = row.createCell(cellNum);   //  Column 
                cell.setCellValue(cellNum);  // loop 65536 That's ok , Every line is 10 Columns of data 
            }
        }
        // Operation completed , Output file stream 
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite03BigDATA.xls" );
        workbook.write(outputStream);  // write in 
        //  Closed flow 
        outputStream.close();
        // Time difference 
        long end = System.currentTimeMillis();
        // Calculate the time difference , Coercive transformation double, In seconds 
        System.out.println((double)(end - begin)/1000);
    }

Large file write HSSF （07 edition ）

shortcoming ： Writing data is very slow , Very memory intensive , Memory overflow may occur , Such as 100 Ten thousand .
advantage , A large amount of data can be written , Such as 20 Ten thousand data .

// Set fixed file path , And 03 identical 
String PATH = "/Users/**/Documents/IdeaProjects/guo_poi/src/main/resources/data/" ;

    @Test //07 Big data reading   Longer time consuming 
    public void testWrite07BigData() throws IOException {
    
        // Time difference  begin  and  end  The difference between the 
        long begin = System.currentTimeMillis();

        //  Create Workbook 
        Workbook workbook = new XSSFWorkbook(); //07 Version object  XSSF
        //  Create sheet 
        Sheet sheet = workbook.createSheet();
        //  Write data 
        for( int rowNum = 0; rowNum < 1000000; rowNum++){
    
            Row row = sheet.createRow(rowNum);    //  That's ok 
            for (int cellNum = 0; cellNum <10; cellNum++){
      // I've set it up here 10 Column 
                Cell cell = row.createCell(cellNum);   //  Column 
                cell.setCellValue(cellNum);  // loop 65536 That's ok , Every line is 10 Columns of data 
            }
        }
        // Operation completed , Output file stream 
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BigDATA.xlsx" );
        workbook.write(outputStream);  // write in 
        //  Closed flow 
        outputStream.close();
        // Time difference 
        long end = System.currentTimeMillis();
        // Calculate the time difference , Coercive transformation double, In seconds 
        System.out.println((double)(end - begin)/1000);
    }

Big files write SXSSF （ Than 07 edition , Faster ）

advantage ： Can write a very large amount of data , Such as 100 Ten thousand or more , Write Data speed is fast , Take up less memory .
Be careful ： Temporary files are generated in the process , Need to clean up temporary files
principle ： Default by 100 strip Records are stored in memory , If you exceed this amount , The first data is written to the temporary file , If you want to customize the amount of data in memory , have access to new SXSSFWorkbook（ Number ）
SXSSFWorkbook Come to the official explanation , Realization “BigGridDemo“ Flow of strategy XSSFWorkbook edition , This allows very large files to be written without running out of memory , Because at any time only the configurable part of the row is saved in memory .
Please note that , Still can consume a lot of memory , This memory is based on the functionality you are using , For example, merge areas , notes , Still can only be stored in memory , So if it's widely used , It may take a lot of memory .

// Set fixed file path , And 03 identical 
String PATH = "/Users/**/Documents/IdeaProjects/guo_poi/src/main/resources/data/" ;

    @Test  //07 Big data reading   Short time 
    public void testWrite07BigDataS() throws IOException{
    
        // Time difference  begin  and  end  The difference between the 
        long begin = System.currentTimeMillis();

        //  Create Workbook 
        Workbook workbook = new SXSSFWorkbook(); //07 Version object  XSSF
        //  Create sheet 
        Sheet sheet = workbook.createSheet();
        //  Write data 
        for( int rowNum = 0; rowNum < 100000; rowNum++){
    
            Row row = sheet.createRow(rowNum);    //  That's ok 
            for (int cellNum = 0; cellNum <10; cellNum++){
      // I've set it up here 10 Column 
                Cell cell = row.createCell(cellNum);   //  Column 
                cell.setCellValue(cellNum);  // loop 65536 That's ok , Every line is 10 Columns of data 
            }
        }
        // Operation completed , Output file stream 
        System.out.println("over");
        FileOutputStream outputStream = new FileOutputStream(PATH + "testWrite07BigDATAS.xlsx" );
        workbook.write(outputStream);  // write in 
        //  Closed flow 
        outputStream.close();
        
        // Clear temporary files ！
        ((SXSSFWorkbook)workbook).dispose();
        
        // Time difference 
        long end = System.currentTimeMillis();
        // Calculate the time difference , Coercive transformation double, In seconds 
        System.out.println((double)(end - begin)/1000);
    }

POI-Excel read

// workbook  Represents a workbook , Use excel Operable functions ,workbook All can be operated 
workbook.createCellStyle();// Create styles 
workbook.createName();// Create name 
workbook.findFont();// Look for content 
workbook.getAllPictures();// Get some pictures 
workbook.isHidden();// hide 
workbook.removeName();// Remove name 


//sheet  Represents the settings in the table   Use excel The functions that the table can operate ,sheet All can be operated 
sheet.createRow();// Create lines 
sheet.autoSizeColumn();// The size of the column 
sheet.getColumnWidth();// The width of the column 
sheet.getDefaultRowHeightInPoints();// Row spacing 

03 edition , Reading data ：

 @Test. //03 Version read data 
    public void testRead03() throws Exception{
    

        // Get file stream 
        FileInputStream inputStream =  new FileInputStream(PATH +"guo_poi03 Version Statistics .xls");

        // 1. Create a workbook   Use excel Operable functions ,workbook All can be operated 
        Workbook workbook = new HSSFWorkbook(inputStream); //  Stream get to workbook 
        // 2. Get the table   The settings in the table  
        Sheet sheet = workbook.getSheetAt(0); // Take it to  get Method 
        // 3. Get the line 
        Row row = sheet.getRow(0);   // Get the first line 
        // 4. Get the column 
        Cell cell  = row.getCell(0); // Get the first column 
        
        // Type judgment is required when reading values !
        //  Get string 
        System.out.println(cell.getStringCellValue());  // String type  
                                                        //cell.getNumericCellValue() Get numbers 
        //  Closed flow 
        inputStream.close();
    }

07 edition , Reading data ：

@Test  //07 Version read data 
    public void testRead07() throws Exception{
    
        // Get file stream 
        FileInputStream inputStream =  new FileInputStream(PATH +"guo_poi07 Version Statistics .xlsx");

        // 1. Create a workbook   Use excel Operable functions ,workbook All can be operated 
        Workbook workbook = new XSSFWorkbook(inputStream); //  Stream get to workbook 
        // 2. Get the table   The settings in the table 
        Sheet sheet = workbook.getSheetAt(0); // Take it to  get Method 
        // 3. Get the line 
        Row row = sheet.getRow(0);   // Get the first line 
        // 4. Get the column 
        Cell cell  = row.getCell(0); // Get the first column 
        // Type judgment is required when reading values !
        //  Get string 
        System.out.println(cell.getNumericCellValue()); // Get numbers 
        //  Closed flow 
        inputStream.close();
    }

Note the type of value obtained ;

Read different data types ：（ Most troublesome ） Pay attention to type conversion

 @Test // Test type 
    public void testCellType() throws Exception{
    
        // Get file stream 
        FileInputStream inputStream =  new FileInputStream(PATH +" Member consumption list .xls");

        //  Create a workbook   Use excel It can be operated on all sides that can be operated 
        Workbook workbook = new HSSFWorkbook(inputStream);  //03 edition 
        // Get the table 
        Sheet sheet = workbook.getSheetAt(0);
        // Get the title content 
        Row rowTitle = sheet.getRow(0); // The top line 
        if(rowTitle != null){
    
            // Be sure to master   Get all the columns 
            int cellCount = rowTitle.getPhysicalNumberOfCells(); //  Get all column data , total   such as ：13 Columns 
            for (int cellNum = 0; cellNum < cellCount;cellNum++){
    
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null){
    
                    CellType cellType = cell.getCellType(); // The type of data to be retrieved 
                    String cellValue = cell.getStringCellValue(); // get data   The data is string  String type 
                    System.out.print(cellValue + "|"); // Print out the data , Output without line breaks 
                }
            }
            System.out.println();
        }

        // Gets the contents of the table 
         int rowCount = sheet.getPhysicalNumberOfRows(); // Get all the lines  getPhysicalNumberOfCells() Get all columns 
        for( int rowNum = 1;rowNum< rowCount;rowNum++){
    
            Row rowData = sheet.getRow(rowNum);
            if(rowData != null){
      // Sentenced to empty 
                // Read column 
                int cellCoumt = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0;cellNum < cellCoumt ; cellNum++){
    
                    System.out.print("["+(rowNum+1)+"-"+(rowNum+1)+"]");
                    Cell cell = rowData.getCell(cellNum);
                    //  Match the data type of the column 
                    if(cell != null){
    
                        CellType cellType = cell.getCellType();
                        String cellValue = "";

                        switch(cellType){
    
                            case STRING: // String type 
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN: // Boolean type 
                                System.out.print("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK: // empty 
                                System.out.print("[BLANK]");
                                break;
                            case _NONE:
                                System.out.print("[NONE]");
                                break;
                            case NUMERIC: // Numbers （ date 、 Common number ）
                                System.out.print("[NUMERIC]");
                                if(DateUtil.isCellInternalDateFormatted(cell)) {
     // date 
                                    System.out.print("[ date ]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else{
    
                                    // It's not a date format , Prevent numbers from being too long ！
                                    System.out.print("[ Convert to string output ]");
                                    cellValue = cell.toString();
                                }
                                break;
                            case ERROR: // error 
                                System.out.print("【 Wrong data type 】");
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }
            }
        }
        inputStream.close(); // Closed flow 
      }

 @Test // Test type   Extract into tool classes 
    public void testCellType(FileInputStream inputStream) throws Exception{
    

        //  Create a workbook   Use excel It can be operated on all sides that can be operated 
        Workbook workbook = new HSSFWorkbook(inputStream);  //03 edition 
        // Get the table 
        Sheet sheet = workbook.getSheetAt(0);
        // Get the title content 
        Row rowTitle = sheet.getRow(0); // The top line 
        if(rowTitle != null){
    
            // Be sure to master   Get all the columns 
            int cellCount = rowTitle.getPhysicalNumberOfCells(); //  Get all column data , total   such as ：13 Columns 
            for (int cellNum = 0; cellNum < cellCount;cellNum++){
    
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null){
    
                    CellType cellType = cell.getCellType(); // The type of data to be retrieved 
                    String cellValue = cell.getStringCellValue(); // get data   The data is string  String type 
                    System.out.print(cellValue + "|"); // Print out the data , Output without line breaks 
                }
            }
            System.out.println();
        }

        // Gets the contents of the table 
        int rowCount = sheet.getPhysicalNumberOfRows(); // Get all the lines  getPhysicalNumberOfCells() Get all columns 
        for( int rowNum = 1;rowNum< rowCount;rowNum++){
    
            Row rowData = sheet.getRow(rowNum);
            if(rowData != null){
      // Sentenced to empty 
                // Read column 
                int cellCoumt = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0;cellNum < cellCoumt ; cellNum++){
    
                    System.out.print("["+(rowNum+1)+"-"+(rowNum+1)+"]");
                    Cell cell = rowData.getCell(cellNum);
                    //  Match the data type of the column 
                    if(cell != null){
    
                        CellType cellType = cell.getCellType();
                        String cellValue = "";

                        switch(cellType){
    
                            case STRING: // String type 
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN: // Boolean type 
                                System.out.print("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK: // empty 
                                System.out.print("[BLANK]");
                                break;
                            case _NONE:
                                System.out.print("[NONE]");
                                break;
                            case NUMERIC: // Numbers （ date 、 Common number ）
                                System.out.print("[NUMERIC]");
                                if(DateUtil.isCellInternalDateFormatted(cell)) {
     // date 
                                    System.out.print("[ date ]");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else{
    
                                    // It's not a date format , Prevent numbers from being too long ！
                                    System.out.print("[ Convert to string output ]");
                                    cellValue = cell.toString();
                                }
                                break;
                            case ERROR: // error 
                                System.out.print("【 Wrong data type 】");
                                break;
                        }
                        System.out.println(cellValue);
                    }

                }
            }
        }
        inputStream.close(); // Closed flow 
    }

Read and calculate the formula ：

Table style ：

 @Test // The formula 
    public void testFormula() throws Exception{
    
        // Get file stream 
        FileInputStream inputStream =  new FileInputStream(PATH +" The formula .xls");
        //  Create a workbook   Use excel It can be operated on all sides that can be operated 
        Workbook workbook = new HSSFWorkbook(inputStream);  //03 edition 
        // Get the table 
        Sheet sheet = workbook.getSheetAt(0);
        // Get the fifth line 
        Row row = sheet.getRow(4);
        // Get the first column 
        Cell cell = row.getCell(0);
        
        // Get the formula  eval function   Use it to calculate 
        FormulaEvaluator formulaEvaluator =  new HSSFFormulaEvaluator((HSSFWorkbook)workbook);
        
        // Output cell content 
        CellType cellType = cell.getCellType();
        switch (cellType){
    
            case FORMULA: // Formula type   You need to match the formula type , If it doesn't match, it won't enter the method 
                String formula = cell.getCellFormula(); 
                System.out.println(formula);
                
                // Calculation 
                CellValue evaluate = formulaEvaluator.evaluate(cell); //  use evaluate(cell) Method to calculate 
                String cellValue = evaluate.formatAsString();// The result of the calculation is , The value of the column , Format into a string to output 
                System.out.println(cellValue); // Output results 
                break;
        }
    }

EasyExcel

EasyExcel： Fast 、 concise 、 Solve the problem of large file memory overflow java Handle Excel Tools

POI Generally, the bottom layer will still use , The disadvantage is that when the amount of data is large, it will report OOM abnormal , Comparison POI It's relatively simple ！

EasyExcel ：
EasyExcel Official address ：https://github.com/alibaba/easyexcel

Official profile ：
Java analysis 、 Generate Excel Well known frameworks are Apache poi、jxl. But they all have a serious problem that is very memory consuming ,poi There is a set SAX Mode API It can solve some memory overflow problems to a certain extent , but POI There are still some flaws , such as 07 edition Excel Decompression and storage after decompression are done in memory , Memory consumption is still high .easyexcel Rewrote poi Yes 07 edition Excel Parsing , One 3M Of excel use POI sax Parsing still needs 100M Left and right memory , change to the use of sth. easyexcel It can be reduced to a few M, And the bigger excel There will be no memory overflow ;03 Version depends on POI Of sax Pattern , In the upper layer, the encapsulation of model transformation is done , Make the user more simple and convenient

GitHub EasyExcel :

Official documents ：

Official documents ：https://www.yuque.com/easyexcel/doc/easyexcel

EasyExcel Alibaba is an open source excel Processing framework , To make it easy to use 、 Save memory storage .
EasyExcel The main reason that can greatly reduce the memory occupation is parsing Excel The data is not loaded into memory at one time , And it reads data line by line from the disk , One by one .
Memory problems ：POI = 100w Load into memory first （ It's a lot of memory ） Write it to the file again , When there is not enough memory, it may report OOM. and EasyExcel In the written file, it is read line by line through the disk .
The picture below is EasyExcel and POI In parsing Excel A comparison of time .

pom.xml rely on

  <!--  Import easyexcel rely on  -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
       

        <!--  Other dependencies that may need to be imported  -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.62</version>
        </dependency>

Entity class ：EasyExcel The table will be automatically generated according to the entity class

package easy;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class DemoData {
    
    @ExcelProperty(" String title ")
    private String string;
    @ExcelProperty(" Date title ")
    private Date date;
    @ExcelProperty(" Digital title ")
    private Double doubleData;
    /** *  Ignore this field  */
    @ExcelIgnore
    private String ignore;
}

Implementation class ： Writing data

package easy;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.junit.Test;

import java.util.Date;
import java.util.List;

public class TestExcel {
    

    String PATH = "/Users/**/Documents/IdeaProjects/guo_poi/src/main/resources/" ;// Set fixed file path 

    // Automatic generation 10 A random number , Then put the random number into list
    private List<DemoData> data() {
       // The entity class is loaded 
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
    
            DemoData data = new DemoData();
            // Set initial value , Back to list in 
            data.setString(" character string " + i);  
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

    // according to list  write in excel
    /** *  The simplest way to write  * <p> * 1.  establish excel Corresponding entity object   reference {@link DemoData} * <p> * 2.  Just write it directly  */
    @Test
    public void simpleWrite() {
    
        //  Be careful  simpleWrite When the amount of data is small, you can use （5000 within , It also depends on the actual situation ）, Large amount of data   Write repeatedly 

        //  The simplest way to write  JDK8+  The rest are written in official documents 
        String fileName = PATH  + "dataEasyTest.xlsx";
        //  here   You need to specify which class To write , Then write the first one sheet, The name is template   Then the file stream will close automatically 
        // write.(fileName, Format class )  The format class will automatically generate the attributes of the table according to the comments 
        // sheet(" Table name ")  Method of generating table 
        //dowrite.( data )  Write data   Data read from the front end or database 
        EasyExcel.write(fileName, DemoData.class) //filename  Data flow ,DemoData.class  Corresponding entity   Title Generation 
                .sheet(" Templates ") // Generate a sheet Table for template 
                .doWrite(data());//dowrite( data )
             //data()  Here is a simple generated random number , The real situation is one that comes back from the front end or is read from the database list, binding mapper Just go 
    }    
                    
}

Running successfully ： You can see that there is dataEasyExcel.xlsx

Generated data

Implementation class ： Reading data

 // Only the method is written , This method is also put in TestExcel in 
    /** *  The simplest reading  * <p> * 1.  establish excel Corresponding entity object   reference {@link DemoData} * <p> * 2.  Because of the default line reading excel, So you need to create excel Line by line callback listener , reference {@link DemoDataListener} * <p> * 3.  Just read it directly  */
    @Test
    public void simpleRead() {
    

        //  There is a very important point  DemoDataListener  Can not be spring management , Every time you read excel Both new, And then it uses spring You can construct methods to pass in 
        //  The simplest way to write  JDK8+  The rest are written in official documents 
        String fileName = PATH  + "dataEasyExcel.xlsx";
        //  here   You need to specify which file to read , Which one to use class Output... In format , adopt DemoDataListener The monitor judges the logic and reads the first sheet  The file stream closes automatically 
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

Persistence layer ：DAO

package easy;

import java.util.List;

/** *  Suppose this is your DAO Storage . Of course, this class also makes spring management , Of course, you don't have to store , You don't need this class either . **/
public class DemoDAO {
    
    public void save(List<DemoData> list) {
    
        // Persistence operation , Save database ！
        //  If it is mybatis, Try not to call directly more than once insert, Write one yourself mapper There's a new method batchInsert, All data is inserted at once 
    }
}

DemoDataListener:

package easy;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;

//  There is a very important point  DemoDataListener  Can not be spring management , Every time you read excel Both new, And then it uses spring You can construct methods to pass in 
@Slf4j
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    

    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);

    /** *  every other 5 Storage database , It can be used in practice 100 strip , Then clean up list , Convenient for memory recycling  */
    private static final int BATCH_COUNT = 5;
    private List<DemoData> list = new ArrayList<DemoData>();

    //sprin  Use when injection is required 
    /** *  Suppose this is a DAO, Of course, there is business logic, which can also be a service. Of course, it's useless if you don't have to store this object . */
    private DemoDAO demoDAO;

    public DemoDataListener() {
    
        //  Here is demo, So whatever new One . If it comes to spring, Please use the following parameter constructor 
        demoDAO = new DemoDAO();
    }

    /** *  If used spring, Please use this construction method . Each creation Listener You need to put spring The management class came in  * * @param demoDAO */
    public DemoDataListener(DemoDAO demoDAO) {
    
        this.demoDAO = demoDAO;
    }


    // When reading data, it will execute  invoke  Method 
    //  Read in 
    // DemoData  type 
    // AnalysisContext  analyzer   Used to analyze context 
    /** *  Every data parsing will call  invoke  Method  * * @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
    
        //fastjson  Import Maven rely on 
        System.out.println(JSON.toJSONString(data));  // Every piece of data read will become  JSON character string 
        list.add(data);
        //  achieve BATCH_COUNT 了 , Need to store the database once , Prevent tens of thousands of data in memory , Easy to OOM
        if (list.size() >= BATCH_COUNT) {
    
            saveData(); // Save database   This is empty 
            //  Storage complete cleaning  list
            list.clear();
        }
    }

    /** *  All data analysis is done   Will call  * * @param context */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    
        //  We also need to save data here , Make sure that the last legacy data is also stored in the database 
        saveData();
        log.info(" All data analysis completed ！");
    }

    /** *  Plus the storage database  */
    private void saveData() {
    
        log.info("{} Data , Start storing the database ！", list.size());
        demoDAO.save(list);
        log.info(" Storage database success ！");
    }
}

Fixed routine ：

1. write in , Write in Fixed class format ！
2. Read , Read according to the rules set by the listener ！
All the codes are in the official documents

Official documents ：
Official documents ：https://www.yuque.com/easyexcel/doc/easyexcel