Simplify Excel Import/Export in Spring Boot with EasyPoi – A Complete Guide

In everyday development we often need to export user or order data to Excel; while POI can do this, its API is cumbersome. EasyPoi offers a much simpler annotation‑based approach.

EasyPoi Overview

For Spring Boot users, EasyPoi lets you define data objects with a few annotations to automatically handle Excel import and export, without writing low‑level POI code.

Integration

Integrating EasyPoi into a Spring Boot project is as easy as adding a single Maven dependency.

<code><dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.4.0</version>
</dependency>
</code>

Usage

The following examples demonstrate EasyPoi for member and order data, covering simple export, simple import, complex export, and custom handling.

Simple Export

Export a list of members with a single annotation on the data class.

Create a

Member

class and annotate fields with

@Excel

to specify column name, width, formatting, merging, desensitization, replacement, and suffix.

<code>/**
 * Shopping member
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
    @Excel(name = "ID", width = 10)
    private Long id;
    @Excel(name = "用户名", width = 20, needMerge = true)
    private String username;
    private String password;
    @Excel(name = "昵称", width = 20, needMerge = true)
    private String nickname;
    @Excel(name = "出生日期", width = 20, format = "yyyy-MM-dd")
    private Date birthday;
    @Excel(name = "手机号", width = 20, needMerge = true, desensitizationRule = "3_4")
    private String phone;
    private String icon;
    @Excel(name = "性别", width = 10, replace = {"男_0", "女_1"})
    private Integer gender;
}
</code>

The core annotation

@Excel

maps object fields directly to Excel columns. Its attributes include:

name : column header

width : column width

needMerge : vertical merge

format : date format

desensitizationRule : e.g.,

3_4

shows first 3 and last 4 characters, masking the rest with

*

replace : value replacement

suffix : suffix appended to the value

In a controller, add an endpoint to export the member list:

<code>/**
 * EasyPoi import/export test controller
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi import/export test")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation(value = "Export member list Excel")
    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
    public void exportMemberList(ModelMap map, HttpServletRequest request, HttpServletResponse response) {
        List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
        ExportParams params = new ExportParams("会员列表", "会员列表", ExcelType.XSSF);
        map.put(NormalExcelConstants.DATA_LIST, memberList);
        map.put(NormalExcelConstants.CLASS, Member.class);
        map.put(NormalExcelConstants.PARAMS, params);
        map.put(NormalExcelConstants.FILE_NAME, "memberList");
        PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }
}
</code>

The

LocalJsonUtil

loads sample data from

members.json

.

Simple Import

Importing member data from an Excel file is equally straightforward.

<code>/**
 * EasyPoi import/export test controller
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi import/export test")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation("Import member list from Excel")
    @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
    @ResponseBody
    public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        try {
            List<Member> list = ExcelImportUtil.importExcel(file.getInputStream(), Member.class, params);
            return CommonResult.success(list);
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.failed("Import failed!");
        }
    }
}
</code>

Complex Export

EasyPoi also supports nested data structures, such as exporting orders that contain member and product information.

Create a

Product

class with

@Excel

annotations for product fields.

<code>/**
 * Product
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Product {
    @Excel(name = "ID", width = 10)
    private Long id;
    @Excel(name = "商品SN", width = 20)
    private String productSn;
    @Excel(name = "商品名称", width = 20)
    private String name;
    @Excel(name = "商品副标题", width = 30)
    private String subTitle;
    @Excel(name = "品牌名称", width = 20)
    private String brandName;
    @Excel(name = "商品价格", width = 10)
    private BigDecimal price;
    @Excel(name = "购买数量", width = 10, suffix = "件")
    private Integer count;
}
</code>

Create an

Order

class. Use

@ExcelEntity

for the one‑to‑one member relationship and

@ExcelCollection

for the one‑to‑many product list.

<code>/**
 * Order
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Order {
    @Excel(name = "ID", width = 10, needMerge = true)
    private Long id;
    @Excel(name = "订单号", width = 20, needMerge = true)
    private String orderSn;
    @Excel(name = "创建时间", width = 20, format = "yyyy-MM-dd HH:mm:ss", needMerge = true)
    private Date createTime;
    @Excel(name = "收货地址", width = 20, needMerge = true)
    private String receiverAddress;
    @ExcelEntity(name = "会员信息")
    private Member member;
    @ExcelCollection(name = "商品列表")
    private List<Product> productList;
}
</code>

Export the order list while excluding some fields:

<code>/**
 * EasyPoi import/export test controller
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi import/export test")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation(value = "Export order list Excel")
    @RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
    public void exportOrderList(ModelMap map, HttpServletRequest request, HttpServletResponse response) {
        List<Order> orderList = getOrderList();
        ExportParams params = new ExportParams("订单列表", "订单列表", ExcelType.XSSF);
        // Exclude fields from export
        params.setExclusions(new String[]{"ID", "出生日期", "性别"});
        map.put(NormalExcelConstants.DATA_LIST, orderList);
        map.put(NormalExcelConstants.CLASS, Order.class);
        map.put(NormalExcelConstants.PARAMS, params);
        map.put(NormalExcelConstants.FILE_NAME, "orderList");
        PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }
}
</code>

Custom Data Handling

For fields that need special processing, such as providing a default value when a nickname is missing, EasyPoi allows custom handlers.

Implement a handler extending

ExcelDataHandlerDefaultImpl

and override

exportHandler

to supply a default string.

<code>/**
 * Custom field handler
 */
public class MemberExcelDataHandler extends ExcelDataHandlerDefaultImpl<Member> {
    @Override
    public Object exportHandler(Member obj, String name, Object value) {
        if ("昵称".equals(name)) {
            String emptyValue = "暂未设置";
            if (value == null) {
                return super.exportHandler(obj, name, emptyValue);
            }
            if (value instanceof String && StrUtil.isBlank((String) value)) {
                return super.exportHandler(obj, name, emptyValue);
            }
        }
        return super.exportHandler(obj, name, value);
    }
    @Override
    public Object importHandler(Member obj, String name, Object value) {
        return super.importHandler(obj, name, value);
    }
}
</code>

Register the handler in the export endpoint:

<code>@ApiOperation(value = "Export member list Excel")
@RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
public void exportMemberList(ModelMap map, HttpServletRequest request, HttpServletResponse response) {
    List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
    ExportParams params = new ExportParams("会员列表", "会员列表", ExcelType.XSSF);
    // Custom processing
    MemberExcelDataHandler handler = new MemberExcelDataHandler();
    handler.setNeedHandlerFields(new String[]{"昵称"});
    params.setDataHandler(handler);
    map.put(NormalExcelConstants.DATA_LIST, memberList);
    map.put(NormalExcelConstants.CLASS, Member.class);
    map.put(NormalExcelConstants.PARAMS, params);
    map.put(NormalExcelConstants.FILE_NAME, "memberList");
    PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}
</code>

Conclusion

EasyPoi makes Excel import and export in Spring Boot projects extremely convenient through annotations, and it also supports complex nested data and custom processing when needed.

References

Project homepage: https://gitee.com/lemur/easypoi

Source code: https://github.com/macrozheng/mall-learning/tree/master/mall-tiny-easypoi