In our daily work, we often encounter the functions of Operating Excel, such as exporting an Excel report of user information or order information. I’m sure you’ve heard of POI. But the API for the POI implementation is really cumbersome, requiring code that parses line by line (similar to Xml parsing). Today I recommend you a very good Excel import and export tool EasyPoi, I hope to help you!

SpringBoot e-commerce project mall (50K + STAR) address: github.com/macrozheng/…

EasyPoi profile

For those of you who are used to SpringBoot, is there a way to automatically import and export Excel by defining the data objects you want to export and then adding a few annotations?

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

The goal of EasyPoi is not to replace POI, but to make it possible for someone who doesn’t know how to import or export Excel to quickly use POI to complete various operations, rather than reading a lot of apis.

integration

Integrating EasyPoi into SpringBoot is as simple as adding the following dependency, truly out of the box!

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

use

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

Simple export

We take the member information list export as an example, using EasyPoi to achieve the export function, see if it is simple enough!

  • Start by creating a member objectMember, 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 = "username ", 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 = "phone number ", width = 20, needMerge = true, desensitizationRule = "3_4")
    private String phone;
    private String icon;
    @ Excel (name = "gender", width = 10, the replace = {" male _0 ", "female _1"})
    private Integer gender;
}
Copy the code
  • Here we can see the core notes of EasyPoi@ExcelBy adding to the object@ExcelAnnotations, which can export object information directly to Excel. Here is an introduction to attributes in annotations.
    • Name: column name in Excel;
    • Width: specifies the width of the column;
    • NeedMerge: Whether vertical merging of cells is required;
    • Format: Sets the time format when the time type is set.
    • DesensitizationRule: Data desensitization,3 _4Indicates that only the beginning of the string is displayed3And after4Bit, and the others are*Number;
    • Replace: To replace an attribute;
    • Suffix: Adds a suffix to data.
  • Next, we add an interface in Controller to export the membership list to Excel. The specific code is as follows.
/** * EasyPoi import/export test Controller * Created by macro on 2021/10/12
@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("Membership List"."Membership 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); }}Copy the code
  • The LocalJsonUtil utility class, which takes JSON data directly from the Resources directory and turns it into an object, such as the one used heremembers.json;

  • Run the project, directly through the Swagger access interface, pay attention to in Swagger access interface cannot download directly, they only need to click on the download button to return the result, access to the address: http://localhost:8088/swagger-ui/

  • Once the download is complete, take a look at the file. A standard Excel file has been exported.

Simple import

Import function is also very simple to achieve, the following member information list import as an example.

  • Add the interface of member information import in Controller, and pay attention to use@RequestPartAnnotations modify file upload parameters, otherwise upload button will not be displayed in Swagger
/** * EasyPoi import/export test Controller * Created by macro on 2021/10/12
@Controller
@API (tags = "EasyPoiController", description = "EasyPoi import/export test ")
@RequestMapping("/easyPoi")
public class EasyPoiController {

    @apiOperation (" Import membership 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!"); }}}Copy the code
  • Then test the interface in Swagger and select the Excel file exported before. After importing successfully, the parsed data will be returned.

Complex export

Of course, EasyPoi can also achieve more complex Excel operations, such as export a nested member information and commodity information of the order list, let’s implement next!

  • First add the goods objectProduct, used 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 = "x ", width = 20)
    private String productSn;
    @excel (name = "name ", width = 20)
    private String name;
    @excel (name = "product subtitle ", width = 30)
    private String subTitle;
    @excel (name = "name ", width = 20)
    private String brandName;
    @excel (name = "width = 10")
    private BigDecimal price;
    @excel (name = "number of purchases ", width = 10, suffix =" pieces ")
    private Integer count;
}
Copy the code
  • Then add the order objectOrder, order and membership is a one-to-one relationship, use @ExcelEntityAnnotations that indicate that an order and an item are a one-to-many relationship are used@ExcelCollectionNotes indicate,OrderIs the nested order data that 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 = "order number ", width = 20,needMerge = true)
    private String orderSn;
    @excel (name = "create time ", width = 20, format =" YYYY-MM-DD HH: MM :ss",needMerge = true)
    private Date createTime;
    @excel (name = "address ", width = 20,needMerge = true)
    private String receiverAddress;
    @excelEntity (name = "Member info ")
    private Member member;
    @excelCollection (name = "product list ")
    private List<Product> productList;
}
Copy the code
  • Next, add the interface to export the order list in Controller. Since we do not need to export some member information, we can call itExportParamsIn thesetExclusionsMethod excluded;
/** * EasyPoi import/export test Controller * Created by macro on 2021/10/12
@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("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); }}Copy the code
  • Access interface test in Swagger, export order list corresponding to Excel;

  • After downloading, take a look at the file, EasyPoi export complex Excel is also very simple!

Custom processing

If you want to customize the exported field, 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 handler that inherits the defaultExcelDataHandlerDefaultImplClass, and then inexportHandlerMethod to implement custom processing logic;
/** * 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); }}Copy the code
  • Then modify the interface in Controller, callMemberExcelDataHandlerThe processorsetNeedHandlerFieldsSets the fields that need custom processing, and callsExportParamsthesetDataHandlerSet up custom processors;
/** * EasyPoi import/export test Controller * Created by macro on 2021/10/12
@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("Membership List"."Membership List", ExcelType.XSSF);
        // Customize the exported result
        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); }}Copy the code
  • Calling the export interface again, we can see that the nickname has added default Settings.

conclusion

Having experienced a wave of EasyPoi, the way it uses annotations to manipulate Excel is really nice. If you want to build more sophisticated Excel, consider its templating capabilities.

The resources

Project official website: gitee.com/lemur/easyp…

Project source code address

Github.com/macrozheng/…

In this paper, making github.com/macrozheng/… Already included, welcome everyone Star!