EasyExcel Learning Notes

Keywords: Java Javascript alibaba easyexcel

EasyExcel experience

Video tutorial: 2 hours to quickly master EasyExcel report import and export - black horse programmer Hangzhou school district

Reference Blog: Alibaba-EasyExcel-Simple and efficient completion of Excel report import and export)

Note: The reference blog is based on the materials that the course instructor has done well in advance; this article is based on the content and cases of the teacher's class, and on this basis, it increases its own understanding.

Students who have experience in importing and exporting reports should have heard or used POI, and they love and hate it. What they love is that they help you to import and export Office documents. This article will take you to import and export Excel reports with ease, efficiency and high performance. During this period, Lombok will be combined with elegant programming to make your needs of Party A father easier and elegant.

First, First Identity EasyExcel

1. Apache POI

First of all, POI, students who have experience in report import and export should listen to or use it.

Apache POI is an open source library of the Apache Software Foundation that provides a cross-platform Java API for reading and writing files in Microsoft Office format. However, there are several issues:

1.1 Higher cost of learning and using

You know that there is SAX mode (Dom parsing mode) in the original POI only after you have a thorough understanding of POI. However, SAX mode is relatively complex, excel has version 03 and 07, the two versions have different data storage methods, and the Sax parsing methods are also different.

It takes two days to write a code test before you want to understand both parsing. Plus, it takes a lot of code to convert to your business model, even after parsing. Overall, it takes at least three days, and subsequent maintenance costs are enormous due to the complexity of the code.

The SAX mode API of POI can solve some memory overflow problems to some extent, but POI still has some drawbacks, such as the decompression of version 07 Excel and the storage after decompression are completed in memory, memory consumption is still large, a 3M Excel parsed by POI SAX still needs around 100M memory.

1.2 POI consumes more memory

Most of the POI uses his userModel mode. The advantage of userModel is that it's easy to use at first, just run through a few copies of code, and all that's left is to write business transformations. Although the transformations also require hundreds of lines of code, which is relatively understandable. However, the biggest problem with userModel mode is that it consumes a lot of memory, and hundreds of megabytes of files can be parsed.Memory. Now many applications use this mode, the reason why running normally must not have much concurrency, and when concurrent, OOM or frequent full gc will occur.

Overall, simple writing is heavily memory-dependent and complex writing is costly to learn.

Characteristic

Powerful
Code writing is redundant and complex
Reading and writing large files is memory intensive and easy to OOM

2. EasyExcel

2.1 Overrides POI's parsing of version 07 Excel

EasyExcel overrides POI's parsing of version 07 Excel, reducing memory consumption from around 100M to less than 10M, and no memory overflow occurs for any larger Excel. Version 03 still relies on POI's SAX mode.
Below is an Excel that reads 75M(46W rows, 25 columns) in one minute from 64M memory (although faster in fast mode, but takes up a little more than 100M of memory)

Model transformation is encapsulated in the upper layer to make the user easier and more convenient

Characteristic

Encapsulated at data model level, easy to use
Rewrite parsing code of version 07 Excel, reduce memory consumption, effectively avoid OOM
Only Excel can be manipulated
Unable to read pictures
Using programs to read and write excel, data flows between the two carriers: excel file, program <entity class, and Map>

Elegant programming lombok

2. Quick Start

0. Import Dependent Coordinates

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>
<!-- lombok Elegant programming -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>
<!-- junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

When importing easyexcel-2.1.6 coordinates, import POI of poi-3.17 by passing.

1. Easiest reading

1.1. Requirements, Preparations

Preparing Excel tables

1.2. Entities that write export data

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private String name;
    private String gender;
    private Date birthday;
    private String id;
}

1.3. Read Excel files

Test class StudentReadDemo for Excel files read by API calling EasyExcel

/**
 * Test EasyExcel for simple reading and writing
 */
public class Test01_StudentRead {
    public static void main(String[] args) {
        
        String fileanme="briup Student List.xlsx";
        
        // Read the file and close it automatically after reading
        // Encapsulate Workbook Objects
        //Parameter description: pathName file path, Class of the entity corresponding to each row of head data, readListener read listener, invoke method of the listener will be called once for each read
        ExcelReaderBuilder workBook = EasyExcel.read(fileanme, Student.class,new StudentReadListener());

        // Encapsulate worksheet
        //Sheet method parameter: the order number of the sheet (corresponding to the Sheet in Excel) or the name of the sheet, which does not pass by default 0
        ExcelReaderSheetBuilder sheet1 = workBook.sheet();
        // read
        sheet1.doRead();
    }
}

Create a listener that reads Excel to handle the data generated by the read

public class StudentReadListener extends AnalysisEventListener<Student> {

    // The invoke method is called once per line read
    @Override
    public void invoke(Student data, AnalysisContext context) {
        System.out.println(data.toString());
    }

    //Read header contents
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("Head:"+headMap);
    }

    // When all is read, the method is called
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("All data has been read!!!");
    }
}

2. The simplest writing

2.1. Requirements, Preparations

2.2. Entities that write export data

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private String name;
    private String gender;
    private Date birthday;
    private String id;
}

2.3, Prepare data and write to file

public class Test02_StudentWrite {
    public static void main(String[] args) {
        //Preparing data
        List<Student> students = initData();
        //Path where String pathName writes to file, object type where Class head er writes to file
        ExcelWriterBuilder workBook = EasyExcel.write("briup Student List.xlsx", Student.class);
        // Sheet method parameter: the order number of the sheet (starting at 0) or the name of the sheet
        ExcelWriterSheetBuilder sheet = workBook.sheet();
        //Writing data to a shell
        sheet.doWrite(students);
    }

    private static List<Student> initData() {
        ArrayList<Student> arrayList = new ArrayList<Student>();
        for (int i = 0; i < 10; i++) {
            Student student = new Student();
            student.setName("Student"+i);
            student.setGender("male");
            student.setBirthday(new Date());
            arrayList.add(student);
        }
        return arrayList;
    }
}

2.4. Simple use of some notes

Custom header, Custom column width, Custom row height, Modify column insertion order, Date format, Ignore a column@ExcelIgnore

If index If the settings are not contiguous, the Excel Vacant in index Create an empty column

Modify Entity Class

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
//@ContentRowHeight(20)//Set the height of each row for the entire table
//@HeadRowHeight(20)//Set the header row height for this table
public class Student {

    @ExcelProperty(value = "Student Name",index = 1)
    private String name;

    @ExcelProperty(value = "Student Gender",index = 2)
    private String gender;

    @ExcelProperty(value = "Date of birth",index = 3)
    @ColumnWidth(20) //Set the line width, which can be used on classes, attributes
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    //index starts at 0, which means the first column in Excel
    @ExcelProperty(value = "Student ID",index = 0)
    private String id;
}

Split and merge cells

Case 1:

    @ExcelProperty(value = {"Student Gender","Student Information Sheet"},index = 2)
    private String gender;

Case 2:

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
//@ContentRowHeight(20)//Set the height of each row for the entire table
//@HeadRowHeight(20)//Set the header row height for this table
public class Student {

    @ExcelProperty(value = {"Student Information Sheet","Student Name"},index = 1)
    private String name;

    @ExcelProperty(value = {"Student Information Sheet","Student Gender"},index = 2)
    private String gender;

    @ExcelProperty(value = {"Student Information Sheet","Date of birth"},index = 3)
    @ColumnWidth(20) //Set the line width, which can be used on classes, attributes
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    //index starts at 0, which means the first column in Excel
    @ExcelProperty(value = {"Student Information Sheet","Student ID"},index = 0)
    private String id;
}

3. Upload and download of documents

Above, we have completed a simple import and export, but read and write in a single environment. The actual production environment is generally in a web environment, so we will upload and download files in this article in conjunction with Spring MVC. Please make sure you are familiar with Spring MVC.

1. Preparations

File upload and download based on Spring MVC

0.Import Dependency

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.0.5</version>
</dependency>
<!-- lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
    <scope>provided</scope>
</dependency>
<!-- junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-nop</artifactId>
    <version>1.7.2</version>
</dependency>
<!-- SpringMVC(Spring) -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>
<!-- Servlet -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>4.0.1</version>
    <scope>provided</scope>
</dependency>
<!-- File Upload -->
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.4</version>
</dependency>

2. File upload

Write entity classes for each row in excel

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private String name;
    private String gender;
    private Date birthday;
    private String id;
}

Write callback listener StudentReadListener

@Component
@Scope("prototype") // Scope: Multiple instances, using a new Listener for each read as required by the author
public class StudentReadListener extends AnalysisEventListener<Student> {

    @Autowired
    private StudentService studentService;

    private final int BATCH_SAVE_NUM = 5;
    ArrayList<Student> students = new ArrayList<>();

    private int count = 0;

    // The invoke method is called once for every read
    @Override
    public void invoke(Student data, AnalysisContext context) {
        students.add(data);
        if (++count % BATCH_SAVE_NUM == 0) {
            studentService.save(students);
            students.clear();
        }
    }

    // When all is read, the method is called
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // TODO......
    }
}

Business Code Interface StudentService and Implementation Class StudentServiceImpl

public interface StudentService {
    void save(ArrayList<Student> students);
}

@Service
public class StudentServiceImpl implements StudentService {
    @Override
    public void save(ArrayList<Student> students) {
        System.out.println("students in service = " + students);
    }
}

Spring Profile

<!-- Component Scan-->
<context:component-scan base-package="net.sunxiaowei"/>

SpringMVC Profile

<!-- Component Scan-->
<context:component-scan base-package="net.sunxiaowei.demo"/>

<!-- MVC File Upload Multipart Parser -->
<bean class="org.springframework.web.multipart.commons.CommonsMultipartResolver"
      id="multipartResolver"/>

Encode Read Uploaded Excel File

@Controller
public class WebUploadAndDownload {
    /**
     * File Upload
     * 1. Write entity classes for each row in excel
     * 2. Since excel is read asynchronously by default, the callback listener needs to be read line by line
     * 3. Start reading Excel
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        ExcelReaderBuilder workBook = EasyExcel.read(file.getInputStream(), Student.class, studentReadListener);
        workBook.sheet().doRead();
        return "success";
    }

3. File Download

Write entity classes and create objects to write tables

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {

    @ExcelIgnore
    private String id;

    //@ExcelProperty({"Student Information Table", "Student Name"})
    @ExcelProperty("Student Name")
    private String name;

    //@ExcelProperty({Student Information Table,'Student Gender'})
    @ExcelProperty("Student Gender")
    private String gender;

    //@ExcelProperty({Student Information Table,'Student Birth Date'})
    @ExcelProperty("Student's date of birth")
    private Date birthday;
}

// Loop 10 Student Objects
private static List<Student> initData() {
    ArrayList<Student> students = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        Student data = new Student();
        data.setName("Analog number 0" + i);
        data.setBirthday(new Date());
        data.setGender("male");
        students.add(data);
    }
    return students;
}

Encoding writes data to response volume for download

public class WebUploadAndDownload {

    /**
     * File Download
     * 1. Write entity classes and create objects to write tables
     * 2. Set response parameters: ContentType of file and file name, and set encoding to avoid scrambling
     * 3. Direct write, finish method is called internally to automatically close OutputStream
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // Prevent Chinese random code 
        String fileName = URLEncoder.encode("test", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        ExcelWriterBuilder workBook = EasyExcel.write(response.getOutputStream(), Student.class);

        ExcelWriterSheetBuilder sheet = workBook.sheet("Template");

        sheet.doWrite(initData());
    }
}

4. Template Filling: Customize Cell Styles

EasyExcel supports adjusting row height, column width, background color, font size, and so on, but the control is as cumbersome as using native POI and is not recommended.

However, you can use template filling to write data directly to a preset-style table, keeping the original style when writing data.

Native POI control operations Excel are redundant and cumbersome, and EasyExcel can't be vulgar either; but if you don't set a style, Excel's style is too plain. So the most common way to work is to use a template, where all the styles are already set, just fill the template with the data you need.

1. Fill in a set of data

1.1 Preparation Template

Excel tables use {} to wrap variables to be filled in. If there are {,} around braces in the cell text, you need to escape {, } with a slash before the brackets.

The member variable name of the entity object used to populate the data in the code or the key of the populated map collection needs to be the same as the variable name wrapped in {} in Excel.

Styles in Excel tables will not be changed after operation

1.2 Encapsulating data

Write classes that encapsulate populated data or choose Map

/**
 * Use entity class encapsulation to populate data
 *  The member variable names in the entity need to match the variable names of the various {} packages in the Excel table
 */
@Data
public class FillData {
    private String name;
    private int age;
}

1.3 Fill

How objects are used

    /**
     * Single group fill
     */
    @Test
    public void test01(){
        //1. Prepare templates
        String templateFile="briup Student List-demo01.xlsx";
        //2. Create Workbook objects: parameter list: target file, template class object,, template file
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Single Group Data Population.xlsx",FillData.class).withTemplate(templateFile);
        //3. Create worksheet objects
        ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();

        //4. Preparing to populate data: how objects are used
        FillData fillData=new FillData();
        fillData.setName("Zhang San");
        fillData.setAge(18);

        //5. Fill, doWrite will automatically close the stream
        sheet.doFill(fillData);

    }

How to use Map

    /**
     * Single group fill, using Map
     */
    @Test
    public void test001(){
        //1. Prepare templates
        String templateFile="briup Student List-demo01.xlsx";
        //2. Create workbook objects
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Single Group Data Population.xlsx",FillData.class).withTemplate(templateFile);
        //3. Create worksheet objects
        ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();

        //4. Preparing to populate data: how to use Map
        Map<String,Object> map=new HashMap<>();
        map.put("name","Zhang San");
        map.put("age",18);

        //5. Fill, doWrite will automatically close the stream
        sheet.doFill(map);

    }

1.4 Effect

2. Fill in groups of data

2.1 Preparation Template

Excel tables use {.} to represent the variables that the package will fill. If there are {,} around braces in the cell text, you need to escape \{, \} with a slash before the brackets.

The member variable name of the entity object populated with data in the code or the key of the populated map collection needs to match the variable name wrapped in {} in Excel.

2.2 Encapsulating data

Write classes that encapsulate populated data or choose Map

// Ditto

2.3 Filling

Prepare the data and populate the file

   /**
     * Multigroup Fill
     */
    @Test
    public void test02() {
        //1. Prepare templates
        String templateFile = "briup Student List-demo02.xlsx";
        //2. Create workbook objects
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write("Multi-group data filling.xlsx",
                FillData.class).withTemplate(templateFile);
        //3. Create worksheet objects
        ExcelWriterSheetBuilder sheet = excelWriterBuilder.sheet();

        //4. Preparing to populate data: multiple sets of data
        List<FillData> fillData = initFillData();

        //5. Fill, doWrite will automatically close the stream
        sheet.doFill(fillData);

    }


    //Generate multiple sets of data code
    private static List<FillData> initFillData() {
        ArrayList<FillData> fillDatas = new ArrayList<FillData>();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("Student" + i);
            fillData.setAge(10 + i);
            fillDatas.add(fillData);
        }
        return fillDatas;
    }

2.4 Effect

3. Combination Filling

3.1 Preparation Template

That is, there are multiple sets of data padding and a single data padding. To avoid conflicting data overrides between the two, you need to set line breaks through the FillConfig object when there are multiple sets of padding.

3.2 Encapsulating data

Write classes that encapsulate populated data or choose Map

// Ditto

3.3 Fill: Error Demo

Note: The API has changed when using composite padding

Prepare the data and populate the file

    /**
     * Combination Fill (Error Demo)
     */
    @Test
    public void test03() {
        //1. Prepare templates
        String templateFile = "briup Student List-demo03.xlsx";
        //2. Create workbook objects
        ExcelWriter workBook = EasyExcel.write("Composite data filling.xlsx",
                FillData.class).withTemplate(templateFile).build();
        //3. Create worksheet objects
        WriteSheet sheet = EasyExcel.writerSheet().build();

        //4. Preparing to populate data: multiple sets of data
        List<FillData> fillData = initFillData();
        //6. Multi-group Filling
        workBook.fill(fillData,sheet);

        //7. Preparing to populate data: a single set of data
        Map<String,Object> map=new HashMap<>();
        map.put("date","2021/10/2");
        map.put("total",10000);
        //8. Single Group filling
        workBook.fill(map,sheet);

        //9. Turn off the stream yourself
        workBook.finish();
    }

Effect: Filled single-line data does not appear where we expected it to be

Solution: Add line breaks

3.4 Fill: Add line breaks

Core Code

        //When combining fills, because the number of groups of fills is uncertain, you need to start a new line after the groups have been filled
        FillConfig fillConfig=FillConfig.builder().forceNewRow(true).build();
        //6. Fill in groups and wrap lines
        workBook.fill(fillData,fillConfig,sheet);

Complete Code

    /**
     * Combination Fill (Error Demo)
     */
    @Test
    public void test04() {
        //1. Prepare templates
        String templateFile = "briup Student List-demo03.xlsx";
        //2. Create workbook objects
        ExcelWriter workBook = EasyExcel.write("Composite data filling.xlsx",
                FillData.class).withTemplate(templateFile).build();
        //3. Create worksheet objects
        WriteSheet sheet = EasyExcel.writerSheet().build();

        //When combining fills, because the number of groups of fills is uncertain, you need to start a new line after the groups have been filled
        FillConfig fillConfig=FillConfig.builder().forceNewRow(true).build();

        //4. Preparing to populate data: multiple sets of data
        List<FillData> fillData = initFillData();
        //6. Fill in groups and wrap lines
        workBook.fill(fillData,fillConfig,sheet);

        //7. Preparing to populate data: a single set of data
        Map<String,Object> map=new HashMap<>();
        map.put("date","2021/10/2");
        map.put("total",10000);
        //8. Single Group filling
        workBook.fill(map,sheet);

        //9. Turn off the stream yourself
        workBook.finish();
    }

4. Horizontal Filling

4.1 Preparation Template

Horizontal padding is the same as multiple sets of padding templates, except that the FillConfig object sets the horizontal padding when padding.

4.2 Encapsulating data

Write classes that encapsulate populated data or choose Map

// Ditto

4.3 Fill

Prepare the data and populate the file

    /**
     * Horizontal Fill
     */
    @Test
    public void test05() {
        //1. Prepare templates
        String templateFile = "briup Student List-demo04.xlsx";
        //2. Create workbook objects
        ExcelWriter workBook = EasyExcel.write("Horizontal data filling.xlsx",
                FillData.class).withTemplate(templateFile).build();
        //3. Create worksheet objects
        WriteSheet sheet = EasyExcel.writerSheet().build();

        //
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();

        //4. Preparing to populate data: multiple sets of data
        List<FillData> fillData = initFillData();
        //6. Fill in groups and wrap lines
        workBook.fill(fillData,fillConfig,sheet);

        //7. Preparing to populate data: a single set of data
        Map<String,Object> map=new HashMap<>();
        map.put("date","2021/10/2");
        map.put("total",10000);
        //8. Single Group filling
        workBook.fill(map,sheet);

        //9. Turn off the stream yourself
        workBook.finish();
    }

5. Notes

In order to save memory, instead of organizing the entire document in memory and writing it to the file as a whole, we use a line-by-line writing method, which does not allow deletion and movement of rows, nor memo writing. When multiple sets of data are written, new rows can only be added in the last line, not in the middle.

6. Filling in Comprehensive Exercises

6.1 Preparation

@Data
public class FillData {
    private String name;
    private int age;
}

@Data
public class Student {
    private String name;
    private String gender;
    private Date birthday;
    private String id;
}

6.2 Comprehensive Cases

    /**
     * Comprehensive case
     */
    @Test
    public void test06() {
        //1. Prepare templates
        String templateFile = "baobiaodemo.xlsx";
        //2. Create workbook objects
        ExcelWriter workBook = EasyExcel.write("Comprehensive case.xlsx",
                FillData.class).withTemplate(templateFile).build();
        //3. Create worksheet objects
        WriteSheet sheet = EasyExcel.writerSheet().build();

        // ********Prepare data*****
        // date
        HashMap<String, String> dateMap = new HashMap<String, String>();
        dateMap.put("date", "2021/10/2");

        // Total membership
        HashMap<String, String> totalCountMap = new HashMap<String, String>();
        dateMap.put("totalCount", "1000");

        // Number of new members
        HashMap<String, String> increaseCountMap = new HashMap<String, String>();
        dateMap.put("increaseCount", "100");

        // Number of new members added this week
        HashMap<String, String> increaseCountWeekMap = new HashMap<String, String>();
        dateMap.put("increaseCountWeek", "50");

        // Number of new members added this month
        HashMap<String, String> increaseCountMonthMap = new HashMap<String, String>();
        dateMap.put("increaseCountMonth", "100");

        // New Membership Data
        List<Student> students = initData();
        // ****End of preparing data****

        // Write statistics
        workBook.fill(dateMap, sheet);
        workBook.fill(totalCountMap, sheet);
        workBook.fill(increaseCountMap, sheet);
        workBook.fill(increaseCountWeekMap, sheet);
        workBook.fill(increaseCountMonthMap, sheet);

        // Write new members
        workBook.fill(students, sheet);
        workBook.finish();


        //9. Turn off the stream yourself
        workBook.finish();
    }


    //Generate multiple sets of data code
    private static List<Student> initData() {
        ArrayList<Student> arrayList = new ArrayList<Student>();
        for (int i = 0; i < 10; i++) {
            Student student = new Student();
            student.setName("Member"+i);
            student.setGender("male");
            student.setBirthday(new Date());
            arrayList.add(student);
        }
        return arrayList;
    }

5. Common API s and Comments

1. Common Classes

EasyExcel entry class for building objects and starting operations;
ExcelReaderBuilder constructs a ReadWorkbook object, a workbook object, corresponding to an Excel file.
ExcelWriterBuilder builds a WriteWorkbook object, a workbook object, corresponding to an Excel file.
ExcelReaderSheetBuilder constructs a ReadSheet object, that is, an object of a worksheet, corresponding to each sheet in Excel, a workbook can have multiple worksheets;
ExcelWriterSheetBuilder constructs a WriteSheet object, that is, an object of a worksheet, corresponding to each sheet in Excel, a workbook can have multiple worksheets;
ReadListener calls ReadListener to process data after each line has been read. We can write the code that calls the service inside its invoke method.
WriteHandler calls WriteHandler to process data in every operation including creating cells, creating tables, etc., which is not transparent to the user.
All configurations are inherited workBook configurations are inherited by Sheet. So when setting parameters with EasyExcel, the scope is all sheets of the entire workBook before the EasyExcel...sheet() method, and then for a single sheet.

2. Notes on reading

@ExcelProperty

Use Location: Standards work on member variables to associate attributes in entity classes with columns in excel tables

Optional properties:

Property Name	Meaning	Explain
index	Number of columns in the corresponding Excel table	Default-1, 0 is recommended when specified
value	Corresponds to the column header in the Excel table
converter	Member Variable Converter	Custom converters require a real Converter interface

Usage effect: The index property specifies which column in the excel corresponds to the current field, matches based on the column name value, or does not write.

If the @ExcelProperty annotation is not used, the order of member variables from top to bottom corresponds to the order from left to right in the table.

**Suggestions for use: ** Either don't write it all, use index all, or match it all with value, try not to mix the three.

@ExcelIgnore

Labeled on member variables, all fields will match excel by default, adding this comment will ignore the field

@DateTimeFormat

This annotation is called when a member variable is labeled on a member variable, a date conversion, or a member variable of type String used in code to receive data in the date format in excel. The value inside refers to java.text.SimpleDateFormat

@NumberFormat

This annotation is called when a member variable is labeled on a member variable, a number conversion, or a member variable of type String used in code to receive data in the excel numeric format. The value inside refers to java.text.DecimalFormat

@ExcelIgnoreUnannotated

Label on the class.

When the annotation is not labeled, all member variables in the default class are read and written, regardless of whether @ExcelProperty is commented on the member variable.

When this annotation is labeled, member variables in the class will not participate in reading and writing if the @ExcelProperty annotation is not labeled.

3. General parameters for reading

ReadWorkbook,ReadSheet will have some parameters, if empty, the default is to use the parent.

Converter converter, many converters are loaded by default. It can also be customized.
The readListener listener, which is constantly called during data reading.
headRowNumber specifies the number of header rows in the table that need to be read. By default, there is a header row, which means the second row is considered to start with data.
head and clazz are optional. Reading the list corresponding to the file header matches the data based on the list. It is recommended to use class, which is the entity type in the code corresponding to each line of data in the file.
clazz and header are optional. Notes can also be used to read the class corresponding to the header of the file. If neither is specified, all data will be read.
autoTrim string, header, etc.
Does password need to be read

4. ReadWorkbook (workbook object) parameters

The current excel type of excelType, which is automatically determined when read, does not need to be set.
inputStream and file are optional. File is recommended.
File and inputStream are optional. Read the file of the file.
autoCloseStream automatically closes the stream.
readCache uses less than 5M memory by default, and EhCache is used for more than 5M. This parameter is not recommended.
useDefaultListener @since 2.1.4 joins ModelBuildEventListener by default to help convert objects into incoming classes. Setting to false will not help convert objects. Custom listeners will receive Map<Integer.CellData > object, if you want to continue listening to the class object, call the readListener method, add a custom beforeListener, ModelBuildEventListener, and a custom afterListener.

5. ReadSheet parameters

Sheet Nos need to read the number of the Sheet, which is recommended to specify which Sheet to read
Sheet Name matches by name, excel 2003 does not support matching by name

6. Notes and Comments on Writing

@ExcelProperty

Use Location: Standards work on member variables

Optional properties:

Property Name	Meaning	Explain
index	Number of columns in the corresponding Excel table	Default-1, 0 is recommended when specified
value	Corresponds to the column header in the Excel table
converter	Member Variable Converter	Custom converters require a real Converter interface

Usage effect: index specifies to write to the first column, if not specified, then sort by member variable position;

value specifies the column header to be written, or if not, the name of the member variable to be used as the column header;

If you want to set complex headers, you can specify multiple values for the value.

Code demonstration:

// 5.Set up a unified header "Simulated Student Information Table" for student information in "Simulated Student Table.xlsx" file

Other notes:

Basic and Read Consistent

@ContentRowHeight() is labeled on a class or attribute, specifying the content row height
@HeadRowHeight() is labeled on a class or attribute, specifying the column header row height
@ColumnWidth() is labeled on a class or property, specifying the column width
@ExcelIgnore defaults to all fields being written to excel, and this comment ignores this field
@DateTimeFormat date conversion, this annotation is called when Date is written to excel. The value inside refers to java.text.SimpleDateFormat
The @NumberFormat number conversion, which is called when excel is written in Number. The value inside refers to java.text.DecimalFormat
@ExcelIgnoreUnannotated defaults to read and write without commenting on ExcelProperty, with or without commenting

7. General Writing Parameters

WriteWorkbook, WriteSheet all have parameters, if empty, the default is to use the parent.

Converter converter, many converters are loaded by default. It can also be customized.
Processor that writeHandler writes to. Implements Workbook WriteHandler, SheetWriteHandler, RowWriteHandler, CellWriteHandler, which is called at different stages of excel writing and is not visible to users transparently.
The relativeHeadRowIndex starts after how many lines it is. That is, the first few empty lines
Does needHead export headers
head and clazz are optional. For a list of headers written to a file, class is recommended.
clazz and header are optional. Notes can also be used for the class corresponding to the header that is written to the file.
autoTrim string, header, etc.

8. WriteWorkbook (workbook object) parameters

ExceelType The type of the current excel, defaulting to xlsx
OutStream versus file. Stream to write to file
Either file or outputStream. Written file
File stream for template InputStream template
TempeFile Template File
autoCloseStream automatically closes the stream.
Does password need to be used when writing
Whether the default header is used when useDefaultStyle is written

9. WriteSheet (worksheet object) parameters

Sheet No needs to be written number. Default 0
Sheet Name needs some Sheet name, which is the same as sheetNo by default

Time: October 2021
Author: Genghui won't laugh

Posted by lehara on Sat, 02 Oct 2021 10:40:37 -0700

Programmer Group

EasyExcel Learning Notes

EasyExcel experience

First, First Identity EasyExcel

1. Apache POI

1.1 Higher cost of learning and using

1.2 POI consumes more memory

Characteristic

2. EasyExcel

2.1 Overrides POI's parsing of version 07 Excel

Characteristic

2. Quick Start

0. Import Dependent Coordinates

1. Easiest reading

1.1. Requirements, Preparations

1.2. Entities that write export data

1.3. Read Excel files

2. The simplest writing

2.1. Requirements, Preparations

2.2. Entities that write export data

2.3, Prepare data and write to file

2.4. Simple use of some notes

3. Upload and download of documents

1. Preparations

2. File upload

3. File Download

4. Template Filling: Customize Cell Styles

1. Fill in a set of data

1.1 Preparation Template

1.2 Encapsulating data

1.3 Fill

1.4 Effect

2. Fill in groups of data

2.1 Preparation Template

2.2 Encapsulating data

2.3 Filling

2.4 Effect

3. Combination Filling

3.1 Preparation Template

3.2 Encapsulating data

3.3 Fill: Error Demo

3.4 Fill: Add line breaks

4. Horizontal Filling

4.1 Preparation Template

4.2 Encapsulating data

4.3 Fill

5. Notes

6. Filling in Comprehensive Exercises

6.1 Preparation

6.2 Comprehensive Cases

5. Common API s and Comments

1. Common Classes

2. Notes on reading

@ExcelProperty

@ExcelIgnore

@DateTimeFormat

@NumberFormat

@ExcelIgnoreUnannotated

3. General parameters for reading

4. ReadWorkbook (workbook object) parameters

5. ReadSheet parameters

6. Notes and Comments on Writing

@ExcelProperty

Other notes:

7. General Writing Parameters

8. WriteWorkbook (workbook object) parameters

9. WriteSheet (worksheet object) parameters

Hot Keywords