preface

Springboot version: 2.6.2

A, 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> The < 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;
package com.example.pojo; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import lombok.EqualsAndHashCode; import java.sql.Date; */ @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; }Copy 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 {@autoWired private EasyPoiService easyPoiService; @apiOperation (value = "exportMemberList") @requestMapping (value = "/exportMemberList") method = RequestMethod.GET) public void exportMemberList(ModelMap modelMap, HttpServletRequest request, HttpServletResponse response) { modelMap = easyPoiService.exportMemberExcel(modelMap); PoiBaseView.render(modelMap, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW); }}Copy the code
  • Corresponding service and serviceImpl

EasyPoiService:

package com.example.service; import com.example.pojo.Member; import org.springframework.ui.ModelMap; import org.springframework.web.multipart.MultipartFile; import java.util.List; Public interface EasyPoiService {/** * exportMemberExcel data * @param modelMap * @return */ modelMap exportMemberExcel(modelMap) modelMap); }Copy the code

EasyPoiServiceImpl:

package com.example.service.impl; import cn.afterturn.easypoi.entity.vo.NormalExcelConstants; import cn.afterturn.easypoi.excel.ExcelImportUtil; import cn.afterturn.easypoi.excel.entity.ExportParams; import cn.afterturn.easypoi.excel.entity.ImportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import com.example.pojo.Member; import com.example.pojo.Order; import com.example.pojo.Product; import com.example.service.EasyPoiService; import com.example.util.LocalJsonUtil; import com.example.util.MemberExcelDataHandler; import org.springframework.stereotype.Service; import org.springframework.ui.ModelMap; import org.springframework.web.multipart.MultipartFile; import java.util.List; @Service("easyPoiService") public class EasyPoiServiceImpl implements EasyPoiService { @Override public ModelMap exportMemberExcel(ModelMap modelMap) { List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class); ExportParams params = new ExportParams(" member list ", "member list ", exceltype.xssf); modelMap.put(NormalExcelConstants.DATA_LIST, memberList); modelMap.put(NormalExcelConstants.CLASS, Member.class); modelMap.put(NormalExcelConstants.PARAMS, params); modelMap.put(NormalExcelConstants.FILE_NAME, "memberList"); return modelMap; }}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;
package com.example.util; import cn.hutool.core.io.IoUtil; import cn.hutool.core.io.resource.ClassPathResource; import cn.hutool.json.JSONArray; import cn.hutool.json.JSONUtil; import java.nio.charset.Charset; import java.util.List; /** * create by macro on 2021/10/16. */ public class LocalJsonUtil {/** * get JSON from the specified path and convert it to List * @param path * @param elementType List elementType */ public static <T> List<T> getListFromJson(String path, Class<T> elementType) { ClassPathResource resource = new ClassPathResource(path); String jsonStr = IoUtil.read(resource.getStream(), Charset.forName("UTF-8")); JSONArray jsonArray = new JSONArray(jsonStr); return JSONUtil.toList(jsonArray, elementType); }}Copy the code
  • Run the project and access the interface directly through Swagger. Note that accessing the interface in Swagger cannot be downloaded directly. You need to click the download button in the returned result to access the address: http://localhost:8888/springboot-service/swagger-ui/index.html

  • 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
/ * * * it is important to note here using the @ RequestPart annotations modified file upload parameters, * otherwise couldn't show upload button in the Swagger; * @param file * @return */ @apiOperation (" import from Excel ") @requestMapping (value = "/importMemberList", method = RequestMethod.POST) @ResponseBody public ResponseResult importMemberList(@RequestPart("file") MultipartFile file) throws Exception { List<Member> memberList = easyPoiService.importMemberExcel(file); return ResponseResult.ok().data(memberList); }Copy the code
  • Add corresponding implementations to the Service and serviceImpl

service:

/** * importMemberExcel data * @param file * @return */ List<Member> importMemberExcel(MultipartFile file) throws Exception;Copy the code

serviceImpl:

@Override
public List<Member> importMemberExcel(MultipartFile file) throws Exception {
    ImportParams importParams = new ImportParams();
    importParams.setTitleRows(1);
    importParams.setHeadRows(1);

    List<Member> list = ExcelImportUtil.importExcel(
            file.getInputStream(),
            Member.class, importParams);

    return list;
}
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;
package com.example.pojo; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import lombok.EqualsAndHashCode; import java.math.BigDecimal; /** ** 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; }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;
package com.example.pojo; import cn.afterturn.easypoi.excel.annotation.Excel; import cn.afterturn.easypoi.excel.annotation.ExcelCollection; import cn.afterturn.easypoi.excel.annotation.ExcelEntity; import lombok.Data; import lombok.EqualsAndHashCode; import java.sql.Date; import java.util.List; /** * 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; }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;
@apiOperation (value = "Excel") @requestMapping (value = "/exportOrderList") method = RequestMethod.GET) public void exportOrderList(ModelMap modelMap, HttpServletRequest request, HttpServletResponse response) { modelMap = easyPoiService.exportOrderExcel(modelMap); PoiBaseView.render(modelMap, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW); }Copy the code

Add corresponding implementations in service and serviceImpl

service:

/** * exportOrderExcel data * @param modelMap * @return */ modelMap exportOrderExcel(modelMap modelMap);Copy the code

serviceImpl:

@Override public ModelMap exportOrderExcel(ModelMap modelMap) { List<Order> orderList = getOrderList(); ExportParams = new ExportParams(" order list ", "order list ", exceltype.xssf); . / / export ruled out some fields exportParams setExclusions (new String [] {" ID ", "date of birth", "gender"}); modelMap.put(NormalExcelConstants.DATA_LIST, orderList); modelMap.put(NormalExcelConstants.CLASS, Order.class); modelMap.put(NormalExcelConstants.PARAMS, exportParams); modelMap.put(NormalExcelConstants.FILE_NAME, "orderList"); return modelMap; } private List<Order> getOrderList() { List<Order> orderList = LocalJsonUtil.getListFromJson("json/orders.json", Order.class); List<Product> productList = LocalJsonUtil.getListFromJson("json/products.json", Product.class); List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class); for (int i = 0; i < orderList.size(); i++) { Order order = orderList.get(i); order.setMember(memberList.get(i)); order.setProductList(productList); } return orderList; }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;
package com.example.util; import cn.afterturn.easypoi.handler.impl.ExcelDataHandlerDefaultImpl; import cn.hutool.core.util.StrUtil; import com.example.pojo.Member; /** * 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;
@Override public ModelMap exportMemberExcel(ModelMap modelMap) { 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); modelMap.put(NormalExcelConstants.DATA_LIST, memberList); modelMap.put(NormalExcelConstants.CLASS, Member.class); modelMap.put(NormalExcelConstants.PARAMS, params); modelMap.put(NormalExcelConstants.FILE_NAME, "memberList"); return modelMap; }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…