SpringBoot implements Excel import and export, which is easy to use, and POI can be thrown away

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

Introduction to EasyPoi

Friends who are used to SpringBoot will probably think that there is no way to directly define the data objects to be exported, and then add several annotations to directly and automatically realize the Excel import and export function?

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

The goal of EasyPoi is not to replace POI, but to enable a person who does not understand import and export to quickly use POI to complete various Excel operations, rather than reading many API s.

integrate

Integrating EasyPoi in SpringBoot is very simple. Just add the following dependency, which is really out of the box!

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

use

Next, the use of EasyPoi is introduced. Taking the import and export of member information and order information as an example, the simple single table export and complex export with associated information are realized respectively.

Simple export

Taking the export of member information list as an example, we use EasyPoi to realize the export function to see if it is simple enough!

First, create a Member object Member to encapsulate the Member information;

/**
 * Shopping member
 * Created by macro on 2021/10/12.
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
    @Excel(name = "ID", width = 10)
    private Long id;
    @Excel(name = "user name", width = 20, needMerge = true)
    private String username;
    private String password;
    @Excel(name = "nickname", width = 20, needMerge = true)
    private String nickname;
    @Excel(name = "date of birth", width = 20, format = "yyyy-MM-dd")
    private Date birthday;
    @Excel(name = "cell-phone number", width = 20, needMerge = true, desensitizationRule = "3_4")
    private String phone;
    private String icon;
    @Excel(name = "Gender", width = 10, replace = {"male_0", "female_1"})
    private Integer gender;
}

Here we can see EasyPoi's core annotation @ Excel. By adding @ Excel annotation to the object, the object information can be directly exported to Excel. The attributes in the annotation are introduced below;
- Name: column name in Excel;
- Width: Specifies the width of the column;
- needMerge: whether to merge cells vertically;
- Format: when the attribute is time type, set the export format of time;
- Desensitization rule: Data desensitization processing, 3_ 4 indicates that only the first 3 and last 4 digits of the string are displayed, and the others are * signs;
- Replace: replace attributes;
- Suffix: adds a suffix to the data.
Next, we add an interface in the Controller to export the member list to Excel. The specific code is as follows:;

/**
 * EasyPoi Import and export test Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi Import and export tests")
@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("Member list", "Member list", 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 directly through the Swagger access interface. Note that the access interface in Swagger cannot be downloaded directly. You need to click the download button in the return result. Access address: http://localhost:8088/swagger-ui/

After downloading, check the next file. A standard Excel file has been exported.

Simple import

The import function is also very simple to implement. Let's take the import of member information list as an example.

Add an interface for importing Member information in the Controller. Note that the @ RequestPart annotation is used to modify the file upload parameters, otherwise the upload button cannot be displayed in Swagger;

/**
 * EasyPoi Import and export test Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi Import and export tests")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @ApiOperation("from Excel Import member list")
    @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!");
        }
    }
}

Then test the interface in Swagger and select the Excel file exported before. After successful import, 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 the commodity information;

/**
 * commodity
 * Created by macro on 2021/10/12.
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Product {
    @Excel(name = "ID", width = 10)
    private Long id;
    @Excel(name = "commodity SN", width = 20)
    private String productSn;
    @Excel(name = "Trade name", width = 20)
    private String name;
    @Excel(name = "Product subtitle", width = 30)
    private String subTitle;
    @Excel(name = "Brand name", width = 20)
    private String brandName;
    @Excel(name = "commodity price", width = 10)
    private BigDecimal price;
    @Excel(name = "Purchase quantity", width = 10, suffix = "piece")
    private Integer count;
}

Then add the order object order. The order and member are one-to-one, expressed with @ ExcelEntity annotation. The order and commodity are one to many, expressed with @ ExcelCollection annotation. Order is the nested order data we need to export;

/**
 * order
 * 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 = "order number", width = 20,needMerge = true)
    private String orderSn;
    @Excel(name = "Creation time", width = 20, format = "yyyy-MM-dd HH:mm:ss",needMerge = true)
    private Date createTime;
    @Excel(name = "Receiving address", width = 20,needMerge = true )
    private String receiverAddress;
    @ExcelEntity(name = "Member information")
    private Member member;
    @ExcelCollection(name = "Product list")
    private List<Product> productList;
}

Next, add an interface to export the order list in the Controller. Since we do not need to export some member information, we can call the setExclusions method in ExportParams to exclude it;

/**
 * EasyPoi Import and export test Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi Import and export tests")
@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("Order list", "Order list", ExcelType.XSSF);
        //Exclude some fields when exporting
        params.setExclusions(new String[]{"ID", "date of birth", "Gender"});
        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 and export Excel corresponding to order list;

After downloading, check the file. It is also very simple for EasyPoi to export complex Excel!

Custom processing

If you want to customize the exported fields, EasyPoi also supports it. For example, in the member information, if the user does not set a nickname, we will add the information that has not been set yet.

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

/**
 * Custom field processing
 * Created by macro on 2021/10/13.
 */
public class MemberExcelDataHandler extends ExcelDataHandlerDefaultImpl<Member> {

  @Override
  public Object exportHandler(Member obj, String name, Object value) {
    if("nickname".equals(name)){
      String emptyValue = "Not set yet";
      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 interface in the Controller, call setNeedHandlerFields of the memberexcelddatahandler processor to set the fields to be customized, and call setDataHandler of ExportParams to set the custom processor;

/**
 * EasyPoi Import and export test Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyPoiController", description = "EasyPoi Import and export tests")
@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("Member list", "Member list", ExcelType.XSSF);
        //Customize the export results
        MemberExcelDataHandler handler = new MemberExcelDataHandler();
        handler.setNeedHandlerFields(new String[]{"nickname"});
        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);
    }
}

Calling the export interface again, we can find that the nickname has been added to the default setting.

summary

I've experienced a wave of EasyPoi, which uses annotations to manipulate Excel. It's really easy to use. If you want to generate more complex Excel, you can consider its template function.

Keywords: Java Spring Spring Boot

Added by andrin on Wed, 03 Nov 2021 09:52:49 +0200

Programming VIP