Some time ago in Github found ali’s EasyExcel project, feel pretty good, wrote a simple method of encapsulation, do only a function to complete the import of Excel or guide. Just some time ago updated to fix some bugs, I will share this package out, please give me more advice

Attached source: github.com/HowieYuan/e…

EasyExcel

EasyExcel github: github.com/alibaba/eas… EasyExcel official Introduction:

You can see the biggest feature of EasyExcel is the use of less memory, of course, now its function is relatively simple, can face the complex scene is less, but the basic read and write can meet.

A.

The first is to add the dependencies for the project, currently in version 1.1.2-beta4

< the dependency > < groupId > com. Alibaba < / groupId > < artifactId > easyexcel < / artifactId > < version > 1.1.2 - beta4 < / version > </dependency>Copy the code

Two. Required classes

1. ExcelUtil

Tool class, you can directly call the method of the tool class to complete Excel read or write

2. ExcelListener

The listener class can handle the data it gets as it wishes, but I’m simply adding the data to a List.

Public Class ExcelListener extends AnalysisEventListener {// Custom for temporary storage of data. Private List<Object> datas = new ArrayList<>(); /** * Retrieve the current sheet from the AnalysisContext object. */ @override public void invoke(Object Object, AnalysisContext Context) {// Store data to the list for batch processing, or your own business logic processing. datas.add(object); // According to their own businessdoSomething(object);
    }

    private void doSomething(Object object) {
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) { /* datas.clear(); */} public List<Object>getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) { this.datas = datas; }}Copy the code

3. ExcelWriterFactroy

Use Excel to export multiple sheets. Write multiple sheets by calling the write method multiple times

4. ExcelException

Capture related Exception

3. Read Excel

To readExcel, just call the excelutil.readexcel () method

@RequestMapping(value = "readExcel", method = RequestMethod.POST)
public Object readExcel(MultipartFile excel) {
    return ExcelUtil.readExcel(excel, new ImportInfo());
}
Copy the code

New ImportInfo() is the entity object mapped by this Excel class. It inherits from BaseRowModel. For example:

public class ImportInfo extends BaseRowModel { @ExcelProperty(index = 0) private String name; @ExcelProperty(index = 1) private String age; @ExcelProperty(index = 2) private String email; /* As a model mapping for Excel, we need a setter method */ public StringgetName() {
        return name;
    }

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

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) { this.email = email; }}Copy the code

As a mapping entity class, the @ExcelProperty annotation and the index variable can annotate the column mapped by the member variable, and the setter method is necessary

Export Excel

1. The exported Excel file has only one sheet

Just call ExcelUtil. WriteExcelWithSheets () method:

@RequestMapping(value = "writeExcel". method = RequestMethod.GET) public void writeExcel(HttpServletResponse response) throws IOException { List<ExportInfo> list = getList(); String fileName ="An Excel file";
    String sheetName = "The first sheet";

    ExcelUtil.writeExcel(response, list, fileName, sheetName, new ExportInfo());
    }
Copy the code

FileName, sheetName indicates the fileName and sheet name of the exported file, new ExportInfo() indicates the mapping entity object of the exported data, and list indicates the exported data.

For the mapping entity class, you can customize the header as needed via the @ExcelProperty annotation. Of course, you also need to inherit from the BaseRowModel class, such as:

public class ExportInfo extends BaseRowModel {
    @ExcelProperty(value = "Name" ,index = 0)
    private String name;

    @ExcelProperty(value = "Age",index = 1)
    private String age;

    @ExcelProperty(value = "Email",index = 2)
    private String email;

    @ExcelProperty(value = "Address",index = 3)
    private String address;
}
Copy the code

Value indicates the column name, and index indicates the column ordinal number

If you need more complexity, you can achieve something like this:

The corresponding entity class is written as follows:

public class MultiLineHeadExcelModel extends BaseRowModel {

    @ExcelProperty(value = {"Header 1"."Header 1"."Header 31"},index = 0)
    private String p1;

    @ExcelProperty(value = {"Header 1"."Header 1"."Header 32"},index = 1)
    private String p2;

    @ExcelProperty(value = {"Header 3"."Header 3"."Header 3"},index = 2)
    private int p3;

    @ExcelProperty(value = {"Header 4"."Header 4"."Header 4"},index = 3)
    private long p4;

    @ExcelProperty(value = {"Header 5"."51" header."Header 52"},index = 4)
    private String p5;

    @ExcelProperty(value = {"Header 6"."Header 61"."Header 611"},index = 5)
    private String p6;

    @ExcelProperty(value = {"Header 6"."Header 61"."Header 612"},index = 6)
    private String p7;

    @ExcelProperty(value = {"Header 6"."Header 62"."Header 621"},index = 7)
    private String p8;

    @ExcelProperty(value = {"Header 6"."Header 62"."Header 622"},index = 8)
    private String p9;
}
Copy the code

2. The exported Excel file has multiple sheets

Call ExcelUtil. WriteExcelWithSheets () the first sheet, called after the write () method in sheet after processing, the final use finish end () method

public void writeExcelWithSheets(HttpServletResponse response) throws IOException {
    List<ExportInfo> list = getList();
    String fileName = "An Excel file";
    String sheetName1 = "The first sheet";
    String sheetName2 = "The second sheet";
    String sheetName3 = "The third sheet";

    ExcelUtil.writeExcelWithSheets(response, list, fileName, sheetName1, new ExportInfo())
                .write(list, sheetName2, new ExportInfo())
                .write(list, sheetName3, new ExportInfo())
                .finish();
}
Copy the code

The write method takes the list data of the current sheet, the name of the current sheet, and the corresponding mapping class