Operating Excel to achieve import and export is a very common requirement, before the introduction of a very useful tool EasyPoi. Some readers put forward that in the case of large data volume, EasyPoi occupies large memory and its performance is not good enough. Today I recommend a better Excel import and export tool EasyExcel, I hope to help you!

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

EasyExcel profile

EasyExcel is an Ali open source Excel import and export tool, with the characteristics of fast processing, small memory, easy to use, on Github has 22K +Star, it is very popular.

EasyExcel reads 75M of Excel (46W rows and 25 columns) using only 64M of memory, taking 20 seconds and even faster in extreme speed mode!

integration

Integrating EasyExcel into SpringBoot is simple and requires only one dependency.

<! EasyExcel dependencies -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>
Copy the code

use

EasyExcel and EasyPoi are very similar in that they both use annotations to control import and export. Next, we take the import and export of member information and order information as an example to realize the simple single table export and the complex export with one-to-many relationship respectively.

Simple export

We take the export of member information as an example to experience the export function of EasyExcel.

  • Start by creating a member objectMember, encapsulate member information, here using EasyExcel annotations;
/** * Created by macro on 2021/10/12. */
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
    @ExcelProperty("ID")
    @ColumnWidth(10)
    private Long id;
    @excelProperty (" Username ")
    @ColumnWidth(20)
    private String username;
    @ExcelIgnore
    private String password;
    @ ExcelProperty (" nickname ")
    @ColumnWidth(20)
    private String nickname;
    @ExcelProperty(" Date of birth ")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;
    @ExcelProperty(" Phone number ")
    @ColumnWidth(20)
    private String phone;
    @ExcelIgnore
    private String icon;
    @excelProperty (value = "gender ", Converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}
Copy the code
  • The above code uses EasyExcel’s core annotations. Let’s take a look at them separately:
    • ExcelProperty: Core notes,valueProperty to set the header name,converterProperty can be used to set type converters;
    • @columnWidth: Used to set the width of the table column;
    • @datetimeFormat: Used to set the date conversion format.
  • In EasyExcel, if you want to convert enumerated types to strings (such as in the gender property,0 - > male.1 - > female), requires a custom converter, which followsGenderConverterCode implementation;
/** * Created by macro on 2021/12/29. */
public class GenderConverter implements Converter<Integer> {
    @Override
    publicClass<? > supportJavaTypeKey() {// Object attribute type
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey(a) {
        //CellData attribute type
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext
        context) throws Exception {
        //CellData goes to object properties
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) return null;
        if ("Male".equals(cellStr)) {
            return 0;
        } else if ("Female".equals(cellStr)) {
            return 1;
        } else {
            return null; }}@Override
    publicWriteCellData<? > convertToExcelData(WriteConverterContext<Integer> context)throws Exception {
        // Object property goes to CellData
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }
        if (cellValue == 0) {
            return new WriteCellData<>("Male");
        } else if (cellValue == 1) {
            return new WriteCellData<>("Female");
        } else {
            return new WriteCellData<>(""); }}}Copy the code
  • Next, we added an interface in Controller to export the membership list to Excel, and set the attributes of downloading Excel for the response header. The specific codes are as follows:
/** * EasyExcel test Controller * Created by macro on 2021/10/12. */
@Controller
@API (tags = "EasyExcelController", description = "EasyExcel import/export test ")
@RequestMapping("/easyExcel")
public class EasyExcelController {

    @SneakyThrows(IOException.class)
    @apiOperation (value = "export member list Excel")
    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "Membership List");
        List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
        EasyExcel.write(response.getOutputStream())
                .head(Member.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("Membership List")
                .doWrite(memberList);
    }
    
  /** * Set the Excel download response header property */
  private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\ +"."% 20");
    response.setHeader("Content-disposition"."attachment; filename*=utf-8''" + fileName + ".xlsx"); }}Copy the code
  • Run the project and test the interface through Swagger. Note that accessing the interface in Swagger cannot be downloaded directly, you need to click in the returned resultDownload buttonAccess 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

Next, we take the member information import as an example to experience the import function of EasyExcel.

  • 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
/** * EasyExcel test Controller * Created by macro on 2021/10/12. */
@Controller
@API (tags = "EasyExcelController", description = "EasyExcel import/export test ")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    
    @SneakyThrows
    @apiOperation (" Import membership list from Excel ")
    @RequestMapping(value = "/importMemberList", method = RequestMethod.POST)
    @ResponseBody
    public CommonResult importMemberList(@RequestPart("file") MultipartFile file) {
        List<Member> memberList = EasyExcel.read(file.getInputStream())
                .head(Member.class)
                .sheet()
                .doReadSync();
        returnCommonResult.success(memberList); }}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, EasyExcel can also achieve more complex export, such as export a nested commodity information order list, let’s implement next!

Implementation using EasyPoi

Before we used EasyPoi to achieve this function, because EasyPoi originally supports the export of nested objects, directly use the built-in @ExcelCollection annotation can be achieved, very convenient and conforms to the idea of object-oriented.

Looking for solutions

Since EasyExcel itself does not support this one-to-many information export, so we have to implement it ourselves. Here is a quick way to find a solution that I usually use.

We can search directly from the issues of open source projects, such as searching the next one-to-many, and will directly find the issue whether there is an elegant scheme to export one-to-many.

From the reply to the issue, we can find that the project maintainer suggested to create a custom merge strategy to realize it. A reply brother has given the implementation code, so we will use this solution to realize it.

solution

Why does a custom cell merge strategy enable the export of one-to-many list information? First, let’s take a look at Excel that tiles nested data without merging it.

We can easily understand the solution, as long as the order ID of the same column to merge the column to merge, you can implement the one-to-many nested information export.

The implementation process

  • First we need to tile the previously nested order item information and create a special export objectOrderData, contains the order and product information, the secondary table header can be set@ExcelPropertyValue is an array.
/** * Created by macro on 2021/12/30. */
@Data
@EqualsAndHashCode(callSuper = false)
public class OrderData {
    @excelProperty (value = "order ID")
    @ColumnWidth(10)
    @CustomMerge(needMerge = true, isPk = true)
    private String id;
    @excelProperty (value = "order code ")
    @ColumnWidth(20)
    @CustomMerge(needMerge = true)
    private String orderSn;
    @excelProperty (value = "created at ")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    @CustomMerge(needMerge = true)
    private Date createTime;
    @excelProperty (value = "shipping address ")
    @CustomMerge(needMerge = true)
    @ColumnWidth(20)
    private String receiverAddress;
    @excelProperty (value = {" product info ", "product code "})
    @ColumnWidth(20)
    private String productSn;
    @excelProperty (value = {" product info ", "product name "})
    @ColumnWidth(20)
    private String name;
    @excelProperty (value = {" ExcelProperty ", "ExcelProperty "})
    @ColumnWidth(30)
    private String subTitle;
    @excelProperty (value = {" product info ", "brand name "})
    @ColumnWidth(20)
    private String brandName;
    @excelProperty (value = {" ExcelProperty ", "ExcelProperty "})
    @ColumnWidth(20)
    private BigDecimal price;
    @excelProperty (value = {" ExcelProperty ", "ExcelProperty "})
    @ColumnWidth(20)
    private Integer count;
}
Copy the code
  • And then the original is nestedOrderThe object list is converted toOrderDataObject list;
/** * EasyExcel test Controller * Created by macro on 2021/10/12. */
@Controller
@API (tags = "EasyExcelController", description = "EasyExcel import/export test ")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    private List<OrderData> convert(List<Order> orderList) {
        List<OrderData> result = new ArrayList<>();
        for (Order order : orderList) {
            List<Product> productList = order.getProductList();
            for (Product product : productList) {
                OrderData orderData = newOrderData(); BeanUtil.copyProperties(product,orderData); BeanUtil.copyProperties(order,orderData); result.add(orderData); }}returnresult; }}Copy the code
  • Create a custom annotationCustomMerge, to mark which attributes need to be merged and which is the primary key;
/** * custom annotations to determine whether a merge is required and the merged primary key */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /** * whether to merge cells */
    boolean needMerge(a) default false;

    /** * is the primary key, that is, the same line of the field is merged with */
    boolean isPk(a) default false;
}
Copy the code
  • Create a custom cell merge policy classCustomMergeStrategyWhen two columns in Excel have the same primary key, merge the columns marked to be merged.
/** * Custom cell merge strategy */
public class CustomMergeStrategy implements RowWriteHandler {
    /** * primary key subscript */
    private Integer pkIndex;

    /** * The set of subscripts for the columns to be merged */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    /** * DTO data type */
    privateClass<? > elementType;public CustomMergeStrategy(Class
        elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // If it is a title, return it directly
        if (isHead) {
            return;
        }

        // Get the current sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // Get the title line
        Row titleRow = sheet.getRow(0);

        if (null == pkIndex) {
            this.lazyInit(writeSheetHolder);
        }

        // Determine whether to merge with the previous row
        // Can not merge with headings, only between data rows
        if (row.getRowNum() <= 1) {
            return;
        }
        // Get the last row of data
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // If this row is the same type of data as the previous row (as determined by the primary key field), merge it
        if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
            for (Integer needMerIndex : needMergeColumnIndex) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), needMerIndex, needMerIndex); sheet.addMergedRegionUnsafe(cellRangeAddress); }}}/** * Initializes the primary key subscript and the subscript of the field to be merged */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // Get the current sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // Get the title line
        Row titleRow = sheet.getRow(0);
        // Get the type of the DTOClass<? > eleType =this.elementType;

        // Get all attributes of the DTO
        Field[] fields = eleType.getDeclaredFields();

        Excel is built on DTO fields, so the number of fields >= the number of columns in Excel
        for (Field theField : fields) {
            // Get the @excelProperty annotation to get the subscript of the column in Excel for this field
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // Empty, this field does not need to be imported into Excel, and the next field is processed directly
            if (null == easyExcelAnno) {
                continue;
            }
            // Get custom annotations to merge cells
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // Default does not merge without @customMerge annotation
            if (null == customMerge) {
                continue;
            }

            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // If no export is required, null is returned to prevent NPE
                if (null == theCell) {
                    continue;
                }
                // Match the fields to the excel header
                if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isPk()) {
                        pkIndex = index;
                    }

                    if(customMerge.needMerge()) { needMergeColumnIndex.add(index); }}}}// No primary key is specified
        if (null= =this.pkIndex) {
            throw new IllegalStateException("Primary key must be specified to use the @customMerge annotation"); }}}Copy the code
  • Next add an interface to the Controller to export the order list, adding our custom merge strategyCustomMergeStrategyTo register;
/** * EasyExcel test Controller * Created by macro on 2021/10/12. */
@Controller
@API (tags = "EasyExcelController", description = "EasyExcel import/export test ")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    
    @SneakyThrows
    @apiOperation (value = "export order list Excel")
    @RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
    public void exportOrderList(HttpServletResponse response) {
        List<Order> orderList = getOrderList();
        List<OrderData> orderDataList = convert(orderList);
        setExcelRespProp(response, "Order List");
        EasyExcel.write(response.getOutputStream())
                .head(OrderData.class)
                .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("Order List") .doWrite(orderDataList); }}Copy the code
  • Access interface test in Swagger, export order list corresponding to Excel;

  • Download after completion, check the file, because EasyExcel needs to achieve their own, compared with the previous use of EasyPoi to achieve a lot of trouble.

Other use

Because the official documentation of EasyExcel is relatively simple, if you want to use it in more depth, I suggest you see the official Demo.

conclusion

Experienced an EasyExcel, or very convenient to use, performance is also very good. But the more common one-to-many export implementation is more complex and not as powerful as EasyPoi. If your Excel export data volume is not large, you can use EasyPoi, if the data volume is large, more concerned about performance, or use EasyExcel.

The resources

  • Project address: github.com/alibaba/eas…
  • The official document: www.yuque.com/easyexcel/d…

Project source code address

Github.com/macrozheng/…

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