Using EasyExcel for Excel Import and Export in Java

EasyExcel is a Java library that simplifies Excel import and export operations.

First, add the Maven dependency:

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

Define an entity class with @ExcelProperty annotations to map Excel columns to fields:

@Data
public class ReqCustomerDailyImport {
    @ExcelProperty(index = 0)
    private String customerName;
    @ExcelProperty(index = 1)
    private String misCode;
    @ExcelProperty(index = 3)
    private BigDecimal monthlyQuota;
    @ExcelProperty(index = 4)
    private BigDecimal accountReceivableQuota;
    @ExcelProperty(index = 5)
    private BigDecimal dailyInterestRate;
}

Read an uploaded file in a Spring controller using EasyExcel.read, specifying the head row and registering a listener and a custom converter for validation and type conversion:

@PostMapping("/import")
public void importCustomerDaily(@RequestParam MultipartFile file) throws IOException {
    InputStream inputStream = file.getInputStream();
    List
list = EasyExcel.read(inputStream)
        .head(ReqCustomerDailyImport.class)
        .sheet()
        .headRowNumber(2)
        .registerConverter(new StringConverter())
        .registerReadListener(new CustomerDailyImportListener())
        .doReadSync();
}

The listener checks for empty or duplicate MIS codes and throws descriptive runtime exceptions; the custom StringConverter prefixes values with a fixed string.

public class CustomerDailyImportListener extends AnalysisEventListener {
    List
misCodes = Lists.newArrayList();
    @Override
    public void invoke(Object data, AnalysisContext context) {
        String misCode = ((ReqCustomerDailyImport) data).getMisCode();
        if (StringUtils.isEmpty(misCode)) {
            throw new RuntimeException(String.format("Row %s MIS code is empty", context.readRowHolder().getRowIndex() + 1));
        }
        if (misCodes.contains(misCode)) {
            throw new RuntimeException(String.format("Row %s MIS code is duplicated", context.readRowHolder().getRowIndex() + 1));
        }
        misCodes.add(misCode);
    }
    // onException and doAfterAllAnalysed omitted for brevity
}

For exporting, create a response entity class and write data to the HTTP response:

@Data @Builder
public class RespCustomerDailyImport {
    @ExcelProperty("客户编码")
    private String customerName;
    @ExcelProperty("MIS编码")
    private String misCode;
    @ExcelProperty("月度滚动额")
    private BigDecimal monthlyQuota;
    @ExcelProperty("最新应收账款余额")
    private BigDecimal accountReceivableQuota;
    @NumberFormat("#.##%")
    @ExcelProperty("本月利率(年化）")
    private BigDecimal dailyInterestRate;
}

@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
    List
data = new ArrayList<>();
    // generate sample data
    EasyExcel.write(response.getOutputStream(), RespCustomerDailyImport.class)
        .sheet("sheet0")
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .doWrite(data);
}

The article also lists common EasyExcel annotations such as ExcelProperty, ExcelIgnore, DateTimeFormat, NumberFormat, and parameters like readListener, converter, headRowNumber, sheetNo, and autoTrim for both import and export scenarios.

In conclusion, EasyExcel reduces boilerplate code for Excel processing to a few annotated classes and a single API call, while supporting advanced features like multiple sheets, custom converters, listeners, and complex headers.