Detailed explanation of SpringBoot's operation of using Apache POI library to read Excel files

Project background

Suppose we need to develop a function that reads data in an Excel file and processes it. Usually, such requirements will occur in the following scenarios:

Data migration: Import the data from Excel tables into the database.
Data analysis: summarize and statistically analyze the data in Excel.
Batch processing: Read configuration information or parameters from Excel files for batch processing. In this article, we will show how to use Java to read Excel files, get the data in them, and show how to convert this data into business objects for subsequent processing.

Dependency import

First, you need to add the dependencies of Apache POI in your project. The Apache POI version is used here, and you can add the following dependencies:

<dependency>
    <groupId></groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

This dependency package includes support for reading xlsx format. If you need to support older xls formats, you can add poi modules.

Implementation of reading Excel templates

Next, let's look at a simple example code that shows how to read the contents of an Excel file and process the data. We will take an example Excel table as an example, assuming that the content of the table is as follows:

Name	age	gender
Zhang San	25	male
Li Si	30	female
Wang Wu	28	male

Code implementation

import ;
import .slf4j.Slf4j;
import .*;
import ;
import ;

import ;
import ;
import ;
import ;

/**
  * Service class that reads Excel files and performs data processing
  */
@Service
@Slf4j
public class ReadExcelServiceImpl {

    public Boolean readExcel() {
        try {
            String pathStr = "/path/to/your/excel/";
            // Excel file path            FileInputStream fis = new FileInputStream(pathStr);
            // Create a workbook object            Workbook workbook = new XSSFWorkbook(fis);
            // Get the first worksheet            Sheet sheet = (0);
            // Get the total number of rows            int lastRowNum = ();
            
            // Store the data list read by Excel            List&lt;ExcelDemoInfoDTO&gt; demoInfoList = new ArrayList&lt;&gt;();

            // Read data.  Loop through the line, starting from the second line, assuming the first line is the title line            for (int i = 1; i &lt;= lastRowNum; i++) {
                ("Reading row {}", i);

                Row row = (i);
                if (row != null) {
                    try {
                        // Get the value of the cell                        String cell0 = getCellValue((0)); // Name                        String cell1 = getCellValue((1)); // age                        String cell2 = getCellValue((2)); // gender
                        // Create data objects and set fields                        ExcelDemoInfoDTO demoInfoDTO = new ExcelDemoInfoDTO();
                        (cell0);
                        ((cell1));
                        (cell2);

                        // Add data objects to the list                        (demoInfoDTO);

                    } catch (Exception e) {
                        ("Error reading row {}", i, e);
                    }
                }
            }

            // Use Jackson to convert read data into JSON string            ObjectMapper mapper = new ObjectMapper();
            String json = (demoInfoList);
            (json);

            // Close the resource            ();
            ();

        } catch (IOException e) {
            ();
            return false;
        }
        return true;
    }

    /**
      * Get the value of the cell and process different types of cells
      *
      * @param cell object
      * @return String value of the cell
      */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (()) {
            case STRING:
                return ();
            case NUMERIC:
                return ((int) ());
            default:
                return "";
        }
    }
}

Code parsing

Open Excel file Use FileInputStream to open the Excel file with a specified path, and then load it as a workbook (Workbook) through XSSFWorkbook.
Read Excel worksheet Get the first worksheet (Sheet) by (0). You can change the index value in getSheetAt as needed to get other worksheets.
Traversing rows and columns Use (i) to get the data for each row. We start reading from the second line (i=1), because the first line is usually the title line.
Get cell contents Get the content of each column by (i) and use the getCellValue method to get the corresponding value according to the cell type (string, number, etc.).
Encapsulate data Encapsulate each row of data into a business object (ExcelDemoInfoDTO) and add it to a list.
Convert to JSON format Use the Jackson library to convert the read data into JSON format for subsequent processing or transmission.
Resource Close After use, close the workbook and FileInputStream to free up the resource.

ExcelDemoInfoDTO Data Transfer Object

To better encapsulate data, we create a simple DTO (Data Transfer Object) class ExcelDemoInfoDTO:

public class ExcelDemoInfoDTO {
    private String name;
    private int age;
    private String gender;

    // Getters and Setters
    public String getName() {
        return name;
    }

    public void setName(String name) {
         = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
         = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
         = gender;
    }
}

Summarize

In this practice, we introduced how to use itApache POI libraryRead Excel files in Java, get the data in it, and encapsulate the data as business objects. In this way, we can flexibly read Excel data in various formats and perform subsequent business processing. For more complex Excel files, we can further extend the code to handle more types of cells, cross-worksheet readings, etc.

The above is a detailed explanation of the operation of SpringBoot using the Apache POI library to read Excel files. For more information about SpringBoot Apache POI library to read Excel, please follow my other related articles!