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 codeCopy 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;
*/ @data @equalSandhashCode (callSuper = false) public class Member {/** * Created by macro on 2021/10/12. @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 = "yyyy-MM-DD ", width = 20, format =" YYYY-MM-DD ") private Date birthday; @excel (name = "phone ", width = 20, needMerge = true, desensitizationRule = "3_4") private String phone; @excel (name =" phone ", 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; } Duplicate codeCopy the code

  • Here we can see EasyPoi’s core annotation @excel, by adding the @excel annotation on the object, you can directly export the object information to Excel. The following is an introduction to the attributes in the annotation.

    • 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.

/** * Created by macro on 2021/10/12. */ @controll@api (tags = "EasyPoiController", RequestMapping("/ EasyPoi ") public class EasyPoiController {@apiOperation (value = "EasyPoi ") public class EasyPoiController {@apiOperation (value = "EasyPoi" @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); }} Copy the codeCopy 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
/** * Created by macro on 2021/10/12. */ @controll@api (tags = "EasyPoiController", RequestMapping("/ EasyPoi ") public class EasyPoiController {RequestMapping("/ EasyPoi ") public class EasyPoiController { @apioperation (" importMemberList 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 codeCopy 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 = "productSn ", width = 20) private String productSn; @excel (name = "@excel ", width = 20) private String name; @excel (name = "name ", width = 30) private String subTitle; @excel (name = "brandName ", width = 20) private String brandName; @excel (name = "c ", width = 10) private BigDecimal price; @excel (name = "number ", width = 10, suffix =" number ") private Integer count; } Duplicate codeCopy 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;
/** * 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 = "createTime ", width = 20, format =" YYYY-MM-DD HH: MM :ss",needMerge = true) private Date createTime; @excel (name = "receiverAddress ", width = 20,needMerge = true) private String receiverAddress; @excelentity (name = "Member info ") private Member Member; @excelCollection (name = "Product ") private List<Product> productList; } Duplicate codeCopy 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;
/** * Created by macro on 2021/10/12. */ @controll@api (tags = "EasyPoiController", RequestMapping("/ EasyPoi ") public class EasyPoiController {@apiOperation (value = "EasyPoi ") public class EasyPoiController {@apiOperation (value = "EasyPoi" @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); 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 codeCopy 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 codeCopy the code
  • Then modify the interface in Controller, callMemberExcelDataHandlerThe processorsetNeedHandlerFieldsSets the fields that need custom processing, and callsExportParamsthesetDataHandlerSet up custom processors;
/** * Created by macro on 2021/10/12. */ @controll@api (tags = "EasyPoiController", RequestMapping("/ EasyPoi ") public class EasyPoiController {@apiOperation (value = "EasyPoi ") public class EasyPoiController {@apiOperation (value = "EasyPoi" @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); 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 codeCopy 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…