I. Introduction and introduction

1. Application scenarios of Excel

Data import: Reduces input workload Data export: archives statistics Data transfer: transfers data between heterogeneous systems

1.1, introduced

EasyExcel is a Java – based simple, save – memory Excel reading and writing open source project. Save as much memory as possible to support reading and writing Excel 100 MB.

1.2, characteristics

  • Java domain parsing, Excel generation more well-known frameworks have Apache POI, JXL and so on. But they all have one serious problem: they consume a lot of memory. This may be fine if your system has low concurrency, but if it does, it will be OOM or JVM full gc frequently.
  • EasyExcel is an open source Excel processing framework of Alibaba, known for its simplicity and memory saving. The main reason EasyExcel can greatly reduce the memory footprint is that when parsing Excel, the file data is not loaded into memory all at once. Instead, the data is read from disk row by row and parsed one by one.
  • EasyExcel parses a row by row and notifys the result of the row in observer mode (AnalysisEventListener).

2. For entry

2.1. Create projects and introduce dependencies

Create a Maven project for test use and import the following dependencies

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.7</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId> <version>1.18.10</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId> The < version > 4.12 < / version > < / dependency > < / dependencies >Copy the code

2.2, the simplest write

Creating an entity Class

  • Using the @excelProperty annotation, you can specify the excel column name: name corresponds to the “name” column in Excel, birthday corresponds to the birthday column in Excel…

  • Use the Lombok plug-in to simplify development

  • You can use the @Excelignore field to make EasyExcel ignore this property

    @data public class ExcelEmpData {@excelProperty (” name “) private String name; @excelProperty (” birthday “) private Date birthday; @excelProperty (” salary “) private Double salary; /*** * use @excelignore to make EasyExcel ignore this property */ @excelignore private String password; }

Test case, create a TestWriteExcelData test class under the Test package to test EasyExcel’s write capabilities

  • Excel 2007 and Excel 03 have different writes
  • Excel version 03 can write 65536 lines at most at one time

Write a static method that generates test data

private static List<ExcelEmpData> getEmpData() { List<ExcelEmpData> excelEmpDataList = new ArrayList<>(); ExcelEmpData data = null; for (int i = 0; i < 65535; i++) { data = new ExcelEmpData(); data.setName("java" + i); // The value of the password property will not be written to Excel data.setpassword ("123"); Data. SetSalary (43.96); data.setBirthday(new Date()); excelEmpDataList.add(data); } return excelEmpDataList; }Copy the code

Write a testWrite07 method in the test class to write data to an.xlsx file

@test public void testWrite07() {// Specify an Excel file to write data to. String fileName = "F:/testExcel/ 01-simplewrite-07.xlsx "; // We need to specify which class to write to, and then write to the first sheet, EasyExcel. Write(fileName, excelempdata.class).sheet(" template ").dowrite (getEmpData()); System.out.println(" Excel write successfully!" ); }Copy the code

The results of

Write a testWrite03 method in your test class to write data to an. XLS file

@Test public void simpleWrite03() { String fileName = "F:/testExcel/02-simpleWrite-03.xls"; // EasyExcel. Write (fileName, ExcelEmpData. Class). ExcelType (ExcelTypeEnum. XLS). Sheet (" template "). DoWrite (getEmpData ()); System.out.println(" Excel write successfully!" ); }Copy the code
  • The results of

When writing data to an. XLS file, a maximum of 65536 lines can be written at a time

  • Increase the number of loops in the getData() function to 65537

    @Test public void testWrongWrite03() { String fileName = “F:/testExcel/03-simpleWrite-03.xls”; // EasyExcel. Write (fileName, ExcelEmpData. Class). ExcelType (ExcelTypeEnum. XLS). Sheet (” template “). DoWrite (getEmpData ()); System.out.println(” Excel write successfully!” ); }

The results of

2.3, specify easyExcel write column

Configure the index attribute for the field in the entity class

@data public class ExcelEmpData {@excelProperty (value = "name ",index = 1) private String name; @excelProperty (value = "birthday ",index = 3) private Date birthday; @excelProperty (value = "salary ",index = 5) private Double salary; /*** * use @excelignore to make EasyExcel ignore this property */ @excelignore private String password; }Copy the code

Re-execute the test method

@test public void testWrite07() {// Specify which Excel file to write data to. String fileName = "F:/testExcel/04- simplewrite-07.xlsx "; // We need to specify which class to write to, and then write to the first sheet, EasyExcel. Write(fileName, excelempdata.class).sheet(" template ").dowrite (getEmpData()); System.out.println(" Excel write successfully!" ); }Copy the code

The results of

2.4. Specify the formatting of attributes

Add different formatting annotations for attributes based on the type of the entity-class attributes

@data public class ExcelEmpData {@excelProperty (value = "name ") private String name; @dateTimeFormat (" YYYY ") @excelProperty (value = "Date ") private Date birthday; @numberFormat ("#.##%") @excelProperty (value = "salary ") private Double salary; /*** * use @excelignore to make EasyExcel ignore this property */ @excelignore private String password; }Copy the code

Re-execute method

@test public void testWrite07() {// Specify an Excel file to write data to. String fileName = "F:/testExcel/ 05-simplewrite-07.xlsx "; // We need to specify which class to write to, and then write to the first sheet, EasyExcel. Write(fileName, excelempdata.class).sheet(" template ").dowrite (getEmpData()); System.out.println(" Excel write successfully!" ); }Copy the code

2.5. Differences between XLSX and XLS

XLSX takes up less space to store the same amount of data

XLS can write a maximum of 65536 pieces of data at a time

3. Use EasyExcel to read files

3.1. Create listeners

Slf4j public class ExcelEmpDataListener extends AnalysisEventListener<ExcelEmpData> { */ private static final int BATCH_COUNT = 5; List<ExcelEmpData> list = new ArrayList<>(); ** @param data * one row value. Is the same as {@link AnalysisContext#readRowHolder()} * @param Context */ @override public void invoke(ExcelEmpData data, AnalysisContext context) {log.info(" Parsed to a data :{}", data); list.add(data); OOM if (list.size() >= BATCH_COUNT) {log.info(" save database "); if (list.size() >= BATCH_COUNT) {log.info(" save database "); List list.clear(); }} @override public void doAfterAllAnalysed(AnalysisContext) { Log.info (" All data parsed!" ); }}Copy the code

3.2, tests,

Create the testread. XLSX file and add the test data

Create test methods

The simplest read * / / * * * @ Test public void simpleRead07 () {String fileName = "F: / testExcel testRead. XLSX"; Read(fileName, excelempData.class, new ExcelEmpDataListener()).sheet().doread (); } @Test public void simpleRead03() { String fileName = "F:/testExcel/testRead.xls"; Read (fileName, excelempdata.class, new ExcelEmpDataListener()).excelType(ExcelTypeEnum.XLS).sheet().doRead(); }Copy the code

The results of

Success!