SpringBoot implements Excel import and export, so easy to use, POI can be thrown away!

In our daily work, we often encounter functions to operate Excel, such as exporting an Excel report of user information or order information. You must have heard of POI, it can be realized. But the API implemented by POI is really troublesome, it needs to write the kind of line-by-line parsing code (similar to Xml parsing). Today I recommend a very useful Excel import and export tool, EasyPoi, and I hope it will help you!

SpringBoot actual combat e-commerce project mall (50k+star) address: https://github.com/macrozheng/mall

Introduction to EasyPoi

Friends who are accustomed to SpringBoot will probably think, is there any way to directly define the data objects that need to be exported, and then add a few annotations to directly realize the Excel import and export function?

EasyPoi is just such a tool. If you are not familiar with POI and want to simply implement Excel operations, just use it!

The goal of EasyPoi is not to replace POI, but to allow a person who does not understand import and export can quickly use POI to complete various operations of Excel, instead of looking at many APIs to complete such work.

integrated

Integrating EasyPoi in SpringBoot is very simple, just add the following dependency, and it's really out of the box!

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

use

Next, we will introduce the use of EasyPoi. Take the import and export of member information and order information as an example to implement simple single-table export and complex export with related information.

Simple export

Let's take the export of member information list as an example, use EasyPoi to realize the export function, and see if it is simple enough!

• First create a member object Member to encapsulate member information;

/**
 * 购物会员
 * Created by macro on 2021/10/12.
 */
@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;
}

• Here we can see the core EasyPoi comment @Excel , by adding on an object @Excel annotations can be exported directly to Excel object information go to the following properties of the annotation to be introduced;

  • name: column name in Excel;
  • width: specify the width of the column;
  • needMerge: whether to merge cells vertically;
  • format: When the attribute is a time type, set the time export format;
  • desensitizationRule: data desensitization processing, 3_4 means that only the first 3 bits and the last 4 bits of the string are displayed, and the others are * ;
  • replace: replace the attribute;
  • suffix: Add a suffix to the data.
• Next, we add an interface to the Controller to export the member list to Excel, the specific code is as follows;

/**
 * EasyPoi导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation(value = "导出会员列表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);
    }
}

• The LocalJsonUtil tool class can directly obtain JSON data from the resources directory and convert it into objects, such as members.json used here;

• Run the project and access the interface directly through Swagger. Note that the interface in Swagger cannot be downloaded directly. You need to click the download button in the returned result. Access address: http://localhost:8088/swagger-ui/

• After the download is complete, check the file, a standard Excel file has been exported.

Simple import

The import function is also very simple to implement. The following takes the import of the member information list as an example.

• Add an interface for importing member information in the Controller. What you need to pay attention to here is to use the @RequestPart annotation to modify the file upload parameters, otherwise the upload button will not be displayed in Swagger;

/**
 * EasyPoi导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation("从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("导入失败！");
        }
    }
}

• Then test the interface in Swagger and select the Excel file that was exported before. After the import is successful, the parsed data will be returned.

Complex export

Of course EasyPoi can also implement more complex Excel operations, such as exporting an order list nested with member information and product information, let's implement it below!

• First add the commodity object Product to encapsulate commodity information;

/**
 * 商品
 * Created by macro on 2021/10/12.
 */
@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;
}

• Then add the order object Order , the order and the member are a one-to-one relationship, using the @ExcelEntity annotation to indicate that the order and the product are a one-to-many relationship, using the @ExcelCollection annotation to indicate that Order is the nested order data we need to export;

/**
 * 订单
 * Created by macro on 2021/10/12.
 */
@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;
}

• Next add the interface to export the list of orders in the Controller, since some members of the information we do not need to export, you can call ExportParams in setExclusions method excluded;

/**
 * EasyPoi导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation(value = "导出订单列表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);
        //导出时排除一些字段
        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);
    }
}

• Access interface test in Swagger, export order list corresponding to Excel;

• After the download is complete, check the file, EasyPoi export complex Excel is also very simple!

Custom processing

If you want to do some custom processing on the exported fields, EasyPoi also supports it. For example, in the member information, if the user does not set a nickname, we add setting information.

• We need to add a processor to inherit the default ExcelDataHandlerDefaultImpl class, and then implement custom processing logic exportHandler

/**
 * 自定义字段处理
 * Created by macro on 2021/10/13.
 */
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);
  }
}

• Then modify the Controller interface, call MemberExcelDataHandler processor setNeedHandlerFields setting fields require custom processing and call ExportParams of setDataHandler set custom processor;

/**
 * EasyPoi导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi导入导出测试")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation(value = "导出会员列表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);
        //对导出结果进行自定义处理
        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);
    }
}

• Call the export interface again, we can find that the nickname has been added by default.

Summarize

I have experienced a wave of EasyPoi, and the way it uses annotations to operate Excel is really very easy to use. If you want to generate more complex Excel, you can consider its template function.

Reference

Project official website: https://gitee.com/lemur/easypoi

Project source address

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

This article GitHub https://github.com/macrozheng/mall-learning has been included, welcome to Star!