Generating and Parsing Excel Using EasyExcel

In many enterprise applications, reading from and writing to Excel files is a routine requirement. While Apache POI offers a comprehensive API, its heavy memory footprint and verbose syntax can become bottlenecks when dealing with large datasets. Alibaba’s EasyExcel library tackles these pain points by re‑implementing the underlying parsing logic, resulting in dramatically lower memory consumption and smoother handling of millions of rows.

Below is a practical guide that walks through setting up EasyExcel in a Spring Boot project, creating a simple POJO for mapping Excel columns, and demonstrating both export and import workflows. The examples also highlight key best practices for production use.

Add the latest EasyExcel dependency to your pom.xml:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

Tip: Keep the version up to date to benefit from performance improvements and bug fixes.

Defining a Data Transfer Object

EasyExcel maps Excel rows to Java objects via annotations. Below is a concise example that represents a user record. The annotations specify column headers and optional styling such as column width.

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.util.Date;

@Data
public class UserExcelVO {

    @ExcelProperty("User ID")
    @ColumnWidth(15)
    private Long id;

    @ExcelProperty("Username")
    @ColumnWidth(20)
    private String username;

    @ExcelProperty("Email")
    @ColumnWidth(25)
    private String email;

    @ExcelProperty("Registration Date")
    @ColumnWidth(20)
    private Date createTime;
}

• @ExcelProperty – defines the header text that will appear in the Excel sheet.
• @ColumnWidth – controls the visual width of the column when the file is opened.

Exporting Data to Excel

The export process is straightforward: prepare a list of DTOs and hand it to EasyExcel.write(). The library handles streaming the data to the output stream, which keeps memory usage minimal even for large collections.

import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelExportDemo {

    public static void main(String[] args) {
        // 1. Generate sample data
        List<UserExcelVO> dataList = new ArrayList<>();
        for (long i = 1; i <= 100; i++) {
            UserExcelVO user = new UserExcelVO();
            user.setId(i);
            user.setUsername("user_" + i);
            user.setEmail("user_" + i + "@example.com");
            user.setCreateTime(new Date());
            dataList.add(user);
        }

        // 2. Define output file path
        String fileName = "user_output.xlsx";

        // 3. Write to Excel
        EasyExcel.write(fileName, UserExcelVO.class)
                .sheet("User Information")
                .doWrite(dataList);

        System.out.println("Excel export completed: " + fileName);
    }
}

Explanation of key steps

Importing Data from Excel

Reading large Excel files efficiently requires a streaming approach. EasyExcel provides AnalysisEventListener, which receives rows one at a time, allowing you to process or persist data incrementally.

1. Create a Listener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;
import java.util.List;

@Slf4j
public class UserDataListener implements ReadListener<UserExcelVO> {

    private static final int BATCH_SIZE = 5;
    private List<UserExcelVO> cache = ListUtils.newArrayListWithExpectedSize(BATCH_SIZE);

    @Override
    public void invoke(UserExcelVO data, AnalysisContext context) {
        log.info("Parsed row: {}", data);
        cache.add(data);

        if (cache.size() >= BATCH_SIZE) {
            persistBatch();
            cache = ListUtils.newArrayListWithExpectedSize(BATCH_SIZE);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        persistBatch(); // Flush remaining rows
        log.info("All rows processed.");
    }

    private void persistBatch() {
        log.info("Persisting {} rows to the database.", cache.size());
        // TODO: Call service layer to batch insert
    }
}

• Batching – By flushing the cache after a fixed number of rows, you avoid loading the entire file into memory.
• Error handling – If a row fails validation, you can collect errors and report them after the import completes.

2. Execute the Import

import com.alibaba.excel.EasyExcel;

public class ExcelImportDemo {

    public static void main(String[] args) {
        String fileName = "user_output.xlsx";

        EasyExcel.read(fileName, UserExcelVO.class, new UserDataListener())
                .sheet()          // defaults to the first sheet
                .doRead();
    }
}

Integrating with Spring Boot

When exposing export functionality through a REST endpoint, you must set the correct HTTP headers so that browsers download the file instead of displaying it.

import com.alibaba.excel.EasyExcel;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;

@RestController
public class ExcelController {

    @GetMapping("/export/users")
    public void exportUsers(HttpServletResponse response) throws Exception {
        List<UserExcelVO> data = userService.fetchAll(); // Replace with real data source

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("User_Report", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), UserExcelVO.class)
                .sheet("Users")
                .doWrite(data);
    }
}

Key points

• Use application/vnd.openxmlformats-officedocument.spreadsheetml.sheet as the MIME type.
• Encode the filename to handle spaces and non‑ASCII characters.
• Write directly to the servlet output stream to avoid creating a temporary file on the server.

Best Practices for Production

Summary

EasyExcel offers a lightweight, high‑performance alternative to Apache POI for handling Excel files in Java. By leveraging its streaming API and annotation‑based mapping, developers can:

• Export large datasets to Excel with minimal memory usage.
• Import data row‑by‑row, applying validation and batch persistence on the fly.
• Integrate seamlessly into Spring Boot applications, exposing endpoints that deliver Excel files directly to clients.

With the patterns and code snippets above, you should be well equipped to implement robust Excel import/export features in any enterprise Java project.