SSpringBoot integrates EasyExcel to achieve Excel import

This is the first time to write an article for sharing. If you have any questions in the article, you are welcome to give feedback in the group at the end of the article.

The background,

Why do I use Easyexcel to upload Excel

EasyExcel is often used in daily projects to read data from Excel locally. There is also a front-end page to configure the data to be processed (e.g. The folder where Excel is located, the file name of Excel, the name of the Sheet column, and some parameters needed to process data). Since I read local files every time, I wonder what I should do if I need to upload Excel to me through the front end one day and let me handle it. How can I do this with as little code change as possible (the project has been rewritten 3 times since the company changes requirements frequently)? When I did a lot of research and found that Excel could read Excel using InPutStream, I suddenly understood something.

Alibaba language sparrow team to EasyExcel is introduced like this

Java parsing, Excel generation frameworks are well known Apache POI, JXL. Poi has a SET of SAX mode API, which can solve some problems of memory overflow to a certain extent. However, POI still has some defects. For example, the decompression and storage after decompression of 07 Version Excel are completed in memory, which still consumes a lot of memory. Easyexcel rewrites poI on 07 version of Excel parsing, can originally a 3M Excel with POI SAX still need about 100M memory reduced to a few M, and no matter how large Excel memory overflow, 03 version depends on POI SAX mode. The model transformation is encapsulated in the upper layer to make the user more simple and convenient. Of course, the Rush mode is faster, but the memory footprint is a little over 100M.Copy the code

Two, integrated EasyExcel?

saada

Add EasyExcel dependency to POM.xml

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.3</version>
        </dependency>
Copy the code

2. Create EasyExcel mapping entity class

import com.alibaba.excel.annotation.ExcelProperty;

public class ExcelEntity {
    // The parameters in ExcelProperty should correspond to the title in Excel
    @ExcelProperty("ID")
    private int ID;

    @ExcelProperty("NAME")
    private String name;

    @ExcelProperty("AGE")
    private int age;

    public ExcelEntity(a) {}public ExcelEntity(int ID, String name, int age) {
        this.ID = ID;
        this.name = name;
        this.age = age;
    }

    public int getID(a) {
        return ID;
    }

    public void setID(int ID) {
        this.ID = ID;
    }

    public String getName(a) {
        return name;
    }

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

    public int getAge(a) {
        return age;
    }

    public void setAge(int age) {
        this.age = age; }}Copy the code

3, create custom Easyexcel listener class

Each ExcelEntity object in this listener class represents a row of data
In this listener class, each row of data that is read can be manipulated individually
Here the data is read in the order of each piece of data in Excel

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;

public class UploadExcelListener extends AnalysisEventListener<ExcelEntity> {

    private static final Logger logger = LoggerFactory.getLogger(LoggerItemController.class);
    public static final List<ExcelEntity> list = new ArrayList<>();

    @Override
    public void invoke(ExcelEntity excelEntity, AnalysisContext context) {
        logger.info(String.valueOf(excelEntity.getID()));
        logger.info(excelEntity.getName());
        logger.info(String.valueOf(excelEntity.getAge()));
        list.add(excelEntity);
    }
Copy the code

4. Create controller

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

@RestController
@CrossOrigin
@RequestMapping("/loggerItem")
public class LoggerItemController {


    // The MultipartFile class is used to accept files from the foreground
    @PostMapping("/upload")
    public List<ExcelEntity> upload(@RequestParam(value = "multipartFile") MultipartFile multipartFile){
        if (multipartFile == null) {return null;
        }

        InputStream in = null;
        try {
            // Get the InputStream from multipartFile
            in = multipartFile.getInputStream();

            /* * EasyExcel has several different read methods, This is suitable for a variety of needs * here we call EasyExcel's Read method which reads Excel through InputStream * it returns an ExcelReaderBuilder return value * ExcelReaderBuilder has a doReadAll method that reads all sheets */
            EasyExcel.read(in,ExcelEntity.class,new UploadExcelListener())
                    .sheet("Sheet1")
                    .doRead();

            // The EasyExcel read method closes the stream every time it finishes reading
            in = multipartFile.getInputStream();
            /* * The Sheet method in ExcelReaderBuilder needs to add the read Sheet name * and don't forget to call the doReadAll method later, otherwise the read operation will not be performed */

            EasyExcel.read(in,ExcelEntity.class,new UploadExcelListener()).doReadAll();
        } catch (IOException e) {
            e.printStackTrace();
        }
        returnUploadExcelListener.list; }}Copy the code

5. Application. Yml configuration

server:
  # specify port number
  port: 8080
spring:
  servlet:
    multipart:
      Configure the size of a single uploaded file
      file-size-threshold: 100M
      Configure the total upload size
      max-request-size: 300M
Copy the code

6, test,

Let’s start with a simple Excel to test

The request is then sent via Postman emulation

Select the Post request and enter the request address
Select Body below
Enter the parameters of the request method in controller in the Key box, and select File in the drop-down box
In the VALUE box there is a Select File, click and Select Excel for the test you just created
Finally click Send to Send the request

The return value is as follows:

Since I read it twice and return it in the same List, there are eight objects in the return value.

[{"name": "Black"."age": 25."id": 1
    },
    {
        "name": "White"."age": 22."id": 2
    },
    {
        "name": "Yellow"."age": 22."id": 3
    },
    {
        "name": "Little green"."age": 23."id": 4
    },
    {
        "name": "Black"."age": 25."id": 1
    },
    {
        "name": "White"."age": 22."id": 2
    },
    {
        "name": "Yellow"."age": 22."id": 3
    },
    {
        "name": "Little green"."age": 23."id": 4}]Copy the code

3, EasyExcel Read method summary

/**
     * Build excel the read
     *
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(a) {
        return new ExcelReaderBuilder();
    }

    /**
     * Build excel the read
     *
     * @param file
     *            File to read.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(File file) {
        return read(file, null.null);
    }

    /**
     * Build excel the read
     *
     * @param file
     *            File to read.
     * @param readListener
     *            Read listener.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(File file, ReadListener readListener) {
        return read(file, null, readListener);
    }

    /**
     * Build excel the read
     *
     * @param file
     *            File to read.
     * @param head
     *            Annotate the class for configuration information.
     * @param readListener
     *            Read listener.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(File file, Class head, ReadListener readListener) {
        ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
        excelReaderBuilder.file(file);
        if(head ! =null) {
            excelReaderBuilder.head(head);
        }
        if(readListener ! =null) {
            excelReaderBuilder.registerReadListener(readListener);
        }
        return excelReaderBuilder;
    }

    /**
     * Build excel the read
     *
     * @param pathName
     *            File path to read.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(String pathName) {
        return read(pathName, null.null);
    }

    /**
     * Build excel the read
     *
     * @param pathName
     *            File path to read.
     * @param readListener
     *            Read listener.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(String pathName, ReadListener readListener) {
        return read(pathName, null, readListener);
    }

    /**
     * Build excel the read
     *
     * @param pathName
     *            File path to read.
     * @param head
     *            Annotate the class for configuration information.
     * @param readListener
     *            Read listener.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) {
        ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
        excelReaderBuilder.file(pathName);
        if(head ! =null) {
            excelReaderBuilder.head(head);
        }
        if(readListener ! =null) {
            excelReaderBuilder.registerReadListener(readListener);
        }
        return excelReaderBuilder;
    }

    /**
     * Build excel the read
     *
     * @param inputStream
     *            Input stream to read.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(InputStream inputStream) {
        return read(inputStream, null.null);
    }

    /**
     * Build excel the read
     *
     * @param inputStream
     *            Input stream to read.
     * @param readListener
     *            Read listener.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(InputStream inputStream, ReadListener readListener) {
        return read(inputStream, null, readListener);
    }

    /**
     * Build excel the read
     *
     * @param inputStream
     *            Input stream to read.
     * @param head
     *            Annotate the class for configuration information.
     * @param readListener
     *            Read listener.
     * @return Excel reader builder.
     */
    public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) {
        ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
        excelReaderBuilder.file(inputStream);
        if(head ! =null) {
            excelReaderBuilder.head(head);
        }
        if(readListener ! =null) {
            excelReaderBuilder.registerReadListener(readListener);
        }
        return excelReaderBuilder;
    }
Copy the code

All the methods are there, so if you don’t know which read method to call, you can use the parameters you can get.

Four, extension,

Read local Excel
```
public static void main(String[] args) {
    EasyExcel.read("C:/Users/Lonely Programmer/Desktop/ New Microsoft Excel worksheet.xlsx"
                   ,ExcelEntity.class
                   ,new UploadExcelListener())
        .doReadAll();
}
Copy the code
```
Reading Excel locally is the same as reading it from an InPutStream stream, except that the parameters have changed. Instead of passing an InPutStream stream, you now pass the absolute path to the file. The listener class and the mapping entity class are the same as the upload class. You can also set your own listener class and entity class according to your needs
MultipartFile document

IO /spring-fram…

The translation is done through Google Chrome’s own translation plug-in. You are advised to use Google Chrome to open the translation function

In fact, I also work needs, learn here. Writing this article is also to make a note, if there is this need can also refer to it. Can help is better, the main purpose is also for their own later can review.

SSpringBoot integrates EasyExcel to achieve Excel import

SSpringBoot integrates EasyExcel to achieve Excel import

The background,

Why do I use Easyexcel to upload Excel

Alibaba language sparrow team to EasyExcel is introduced like this

Two, integrated EasyExcel?

saada

Add EasyExcel dependency to POM.xml

2. Create EasyExcel mapping entity class

3, create custom Easyexcel listener class

4. Create controller

5. Application. Yml configuration

6, test,

3, EasyExcel Read method summary

Four, extension,

Related Posts

In order to get to work, I developed “BOSS is Coming” in Python!

Design pattern — Adapter pattern

BTree and B + Tree