[Java Spring FAQs] Use Alibaba EasyExcel to read or write Excel

Posted on 2021-02-27 Edited on 2024-09-12 In Programming Language , Java , Spring , Spring Boot Views: Word count in article: 6.6k Reading time ≈ 6 mins.

EasyExcel

EasyExcel rewrote Apache POI’s analysis of version 07 of Excel. A 3M excel using POI sax analysis still requires about 100M of memory. Switching to easyexcel can be reduced to a few M, and no amount of memory overflow will occur in the larger excel; version 03 depends on The sax mode of POI is encapsulated in the upper layer for model conversion, which makes users more simple and convenient.

Dependencies

Maven:

<!-- pom.xml -->

    <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.0.1</version>
    </dependency>

Gradle:

// build.gradle

dependencies {
    // https://mvnrepository.com/artifact/com.alibaba/easyexcel
    implementation group: 'com.alibaba', name: 'easyexcel', version: '3.0.1'
}

Usages

Read Excel

/**
  * The easiest to read
  * <p>1. Create an entity object corresponding to excel Refer to {@link DemoData}
  * <p>2. Because excel is read line by line by default, it is necessary to create a callback listener for excel line by line, refer to {@link DemoDataListener}
  * <p>3. Just read it directly
  */
@Test
public void simpleRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // Here you need to specify which class to read, and then read the first sheet file stream will be automatically closed
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet("Sheet1").doRead();
}

See Read Excel Demo - https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/read/ReadTest.java to learn more.

See 读Excel · 语雀 - https://www.yuque.com/easyexcel/doc/read#205d07d1 to learn more.

Write Excel

/**
  * The easiest to write
  * <p>1. Create entity objects corresponding to excel Refer to {@link com.alibaba.easyexcel.test.demo.write.DemoData}
  * <p>2. Just write directly
  */
@Test
public void simpleWrite() {
    String fileName = TestFileUtil.getPath() + "write" + System.currentTimeMillis() + ".xlsx";
    // Here you need to specify which class to read, and then write to the first sheet, the name is template, and the file stream will be automatically closed
     // If you want to use 03 here, just pass in the excelType parameter
    EasyExcel.write(fileName, DemoData.class).sheet("Sheet1").doWrite(data());
}

See easyexcel/WriteTest.java at master · alibaba/easyexcel - https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/write/WriteTest.java to learn more.

See [写excel · 语雀 - https://www.yuque.com/easyexcel/doc/write) to learn more.

Web upload and download

/**
  * File download (If it fails, an Excel with partial data will be returned)
  * <p>
  * 1. Create an entity object corresponding to excel. Refer to {@link DownloadData}
  * <p>
  * 2. Set the returned parameters
  * <p>
  * 3. Write directly. Note here that the OutputStream will be automatically closed when finishing. Of course, it is not a big problem if you close the stream outside
  */
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    // Note here that some students have reported that using swagger will cause various problems, please use the browser or postman directly
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // Here URLEncoder.encode can prevent Chinese garbled, of course it has nothing to do with easyexcel
    String fileName = URLEncoder.encode("Test", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("Sheet1").doWrite(data());
}

/**
  * File Upload
  * <p>1. Create an entity object corresponding to excel. Refer to {@link UploadData}
  * <p>2. Because excel is read line by line by default, it is necessary to create a callback listener for excel line by line, refer to {@link UploadDataListener}
  * <p>3. Just read it directly
  */
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet("Sheet1").doRead();
    return "success";
}

See alibaba/easyexcel: 快速、简单避免OOM的java处理Excel工具 - https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/web/WebTest.java to learn more.

FAQs

Could not initialize class net.sf.cglib.beans.BeanMap$Generator

1	i.g.w.e.ErrorHandlingControllerAdvice : java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.beans.BeanMap$Generator

Remember to import cglib to solve that issue.

Maven:

<!-- pom.xml -->

<!-- https://mvnrepository.com/artifact/cglib/cglib -->
<dependency>
    <groupId>cglib</groupId>
    <artifactId>cglib</artifactId>
    <version>3.3.0</version>
</dependency>

Gradle:

// build.gradle

dependencies {
    // https://mvnrepository.com/artifact/cglib/cglib
    implementation group: 'cglib', name: 'cglib', version: '3.3.0'
}

java.lang.reflect.InaccessibleObjectException: Unable to make protected final java.lang.Class java.lang

1
2
3

Caused by: java.lang.reflect.InaccessibleObjectException: Unable to make protected final java.lang.Class java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain) throws java.lang.ClassFormatError accessible: module java.base does not "opens java.lang" to unnamed module @5c909414
 at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:357) ~[na:na]
 at java.base/java.lang.reflect.AccessibleObject.checkCanSetAccessible(AccessibleObject.java:297) ~[na:na]

Get your code running on the Java 9 Module System with the command line options --add-exports, --add-opens, --add-modules, --add-reads, and --patch-module.