SpringBoot implements Excel import and export, the performance is explosive, and it is elegant enough to use!

It is a very common requirement to operate Excel to import and export. I introduced a very useful tool EasyPoi . Some readers have suggested that EasyPoi occupies a large amount of memory and its performance is not good enough in the case of a large amount of data. Today, I would like to recommend an Excel import and export tool EasyExcel with better performance. I hope it will be helpful to you!

SpringBoot actual e-commerce project mall (50k+star) address: https://github.com/macrozheng/mall

Introduction to EasyExcel

22k+ . It has the characteristics of fast processing, small memory consumption and convenient use. It has 061de36303fb17 Star on Github, which shows that it is very popular.

EasyExcel reads 75M Excel (46W rows and 25 columns), only needs to use 64M memory, it takes 20s, and the speed mode can be even faster!

integrated

Integrating EasyExcel in SpringBoot is very simple and requires only one dependency.

<!--EasyExcel相关依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

use

The use of EasyExcel and EasyPoi are very similar, both import and export are controlled by annotations. Next, we take the import and export of member information and order information as an example, to implement a simple single-table export and a complex export with a one-to-many relationship.

Simple export

Let's take the export of member information as an example to experience the export function of EasyExcel.

• First, create a member object Member to encapsulate the member information. The annotation of EasyExcel is used here;

/**
 * 购物会员
 * Created by macro on 2021/10/12.
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Member {
    @ExcelProperty("ID")
    @ColumnWidth(10)
    private Long id;
    @ExcelProperty("用户名")
    @ColumnWidth(20)
    private String username;
    @ExcelIgnore
    private String password;
    @ExcelProperty("昵称")
    @ColumnWidth(20)
    private String nickname;
    @ExcelProperty("出生日期")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;
    @ExcelProperty("手机号")
    @ColumnWidth(20)
    private String phone;
    @ExcelIgnore
    private String icon;
    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}

• The above code uses the core annotations of EasyExcel, let's take a look at them separately:

  • @ExcelProperty: core annotation, the value property can be used to set the header name, and the converter property can be used to set the type converter;
  • @ColumnWidth: used to set the width of the table columns;
  • @DateTimeFormat: used to set the date conversion format.
• In EasyExcel, if you want to convert an enumeration type to a string (for example, in the gender attribute, 0->male, 1->female), you need a custom converter, the following is the custom GenderConverter code implementation;

/**
 * excel性别转换器
 * Created by macro on 2021/12/29.
 */
public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        //对象属性类型
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        //CellData属性类型
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //CellData转对象属性
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) return null;
        if ("男".equals(cellStr)) {
            return 0;
        } else if ("女".equals(cellStr)) {
            return 1;
        } else {
            return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        //对象属性转CellData
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }
        if (cellValue == 0) {
            return new WriteCellData<>("男");
        } else if (cellValue == 1) {
            return new WriteCellData<>("女");
        } else {
            return new WriteCellData<>("");
        }
    }
}

• Next, we add an interface in the Controller to export the member list to Excel, and also need to set the property of downloading excel for the response header. The specific code is as follows;

/**
 * EasyExcel导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {

    @SneakyThrows(IOException.class)
    @ApiOperation(value = "导出会员列表Excel")
    @RequestMapping(value = "/exportMemberList", method = RequestMethod.GET)
    public void exportMemberList(HttpServletResponse response) {
        setExcelRespProp(response, "会员列表");
        List<Member> memberList = LocalJsonUtil.getListFromJson("json/members.json", Member.class);
        EasyExcel.write(response.getOutputStream())
                .head(Member.class)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("会员列表")
                .doWrite(memberList);
    }
    
  /**
   * 设置excel下载响应头属性
   */
  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");
  }
}

• Run the project and test the interface through Swagger. Note that the interface cannot be downloaded directly in Swagger. You need to click the download button in the returned result. Access address: http://localhost:8088/swagger-ui/

• After the download is complete, check the file, a standard Excel file has been exported.

simple import

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

• Add an interface for importing member information in the Controller. It should be noted here that the @RequestPart annotation is used to modify the file upload parameters, otherwise the upload button will not be displayed in Swagger;

/**
 * EasyExcel导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    
    @SneakyThrows
    @ApiOperation("从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();
        return CommonResult.success(memberList);
    }
}

• Then test the interface in Swagger, select the previously exported Excel file, and the parsed data will be returned after the import is successful.

complex export

Of course, EasyExcel can also implement more complex export, such as exporting an order list with nested product information, let's implement it below!

Implemented with EasyPoi

We have used EasyPoi to implement this function before. Since EasyPoi originally supports the export of nested objects, it @ExcelCollection annotation, which is very convenient and conforms to the object-oriented idea.

Find a solution

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

We can search directly from issues open source project. For example, if we search for one-to-many, we will directly find the more elegant one-to-many export solution.

From the reply to this issue, we can find that the project maintainer suggested create a custom merge strategy to implement. One of the replying brothers has given the implementation code, and then we will use this solution to implement it.

Solutions

Why can the custom cell merging strategy realize the export of one-to-many list information? First, let's take a look at Excel that flattens nested data without merging and exporting.

After reading it, it is easy to understand the solution. As long as the columns that need to be merged in the columns with the same order ID of merged, the export of this one-to-many nested information can be realized.

Implementation process

• First, we have to tile the original nested order product information, and create a special export object OrderData , which contains order and product information. The secondary header can be achieved by setting @ExcelProperty as an array;

/**
 * 订单导出
 * Created by macro on 2021/12/30.
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class OrderData {
    @ExcelProperty(value = "订单ID")
    @ColumnWidth(10)
    @CustomMerge(needMerge = true, isPk = true)
    private String id;
    @ExcelProperty(value = "订单编码")
    @ColumnWidth(20)
    @CustomMerge(needMerge = true)
    private String orderSn;
    @ExcelProperty(value = "创建时间")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    @CustomMerge(needMerge = true)
    private Date createTime;
    @ExcelProperty(value = "收货地址")
    @CustomMerge(needMerge = true)
    @ColumnWidth(20)
    private String receiverAddress;
    @ExcelProperty(value = {"商品信息", "商品编码"})
    @ColumnWidth(20)
    private String productSn;
    @ExcelProperty(value = {"商品信息", "商品名称"})
    @ColumnWidth(20)
    private String name;
    @ExcelProperty(value = {"商品信息", "商品标题"})
    @ColumnWidth(30)
    private String subTitle;
    @ExcelProperty(value = {"商品信息", "品牌名称"})
    @ColumnWidth(20)
    private String brandName;
    @ExcelProperty(value = {"商品信息", "商品价格"})
    @ColumnWidth(20)
    private BigDecimal price;
    @ExcelProperty(value = {"商品信息", "商品数量"})
    @ColumnWidth(20)
    private Integer count;
}

• Then convert the original nested Order objects into a list of OrderData objects;

/**
 * EasyExcel导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@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 = new OrderData();
                BeanUtil.copyProperties(product,orderData);
                BeanUtil.copyProperties(order,orderData);
                result.add(orderData);
            }
        }
        return result;
    }
}

• Create another custom annotation CustomMerge to mark which attributes need to be merged and which is the primary key;

/**
 * 自定义注解，用于判断是否需要合并以及合并的主键
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {

    /**
     * 是否需要合并单元格
     */
    boolean needMerge() default false;

    /**
     * 是否是主键,即该字段相同的行合并
     */
    boolean isPk() default false;
}

• Then create a custom cell merge strategy class CustomMergeStrategy . When the primary keys of two columns in Excel are the same, the columns marked to be merged are merged;

/**
 * 自定义单元格合并策略
 */
public class CustomMergeStrategy implements RowWriteHandler {
    /**
     * 主键下标
     */
    private Integer pkIndex;

    /**
     * 需要合并的列的下标集合
     */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    /**
     * DTO数据类型
     */
    private Class<?> elementType;

    public CustomMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是标题,则直接返回
        if (isHead) {
            return;
        }

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(0);

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

        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        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);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取DTO的类型
        Class<?> eleType = this.elementType;

        // 获取DTO所有的属性
        Field[] fields = eleType.getDeclaredFields();

        // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
        for (Field theField : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // 没有@CustomMerge注解的默认不合并
            if (null == customMerge) {
                continue;
            }

            for (int index = 0; index < fields.length; index++) {
                Cell theCell = titleRow.getCell(index);
                // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                if (null == theCell) {
                    continue;
                }
                // 将字段和excel的表头匹配上
                if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
                    if (customMerge.isPk()) {
                        pkIndex = index;
                    }

                    if (customMerge.needMerge()) {
                        needMergeColumnIndex.add(index);
                    }
                }
            }
        }

        // 没有指定主键，则异常
        if (null == this.pkIndex) {
            throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
        }

    }
}

• Next, add an interface for exporting the order list in the Controller, and register CustomMergeStrategy

/**
 * EasyExcel导入导出测试Controller
 * Created by macro on 2021/10/12.
 */
@Controller
@Api(tags = "EasyExcelController", description = "EasyExcel导入导出测试")
@RequestMapping("/easyExcel")
public class EasyExcelController {
    
    @SneakyThrows
    @ApiOperation(value = "导出订单列表Excel")
    @RequestMapping(value = "/exportOrderList", method = RequestMethod.GET)
    public void exportOrderList(HttpServletResponse response) {
        List<Order> orderList = getOrderList();
        List<OrderData> orderDataList = convert(orderList);
        setExcelRespProp(response, "订单列表");
        EasyExcel.write(response.getOutputStream())
                .head(OrderData.class)
                .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("订单列表")
                .doWrite(orderDataList);
    }
}

• Access the interface test in Swagger, and export the order list corresponding to Excel;

• After the download is complete, check the file. Since EasyExcel needs to be implemented by itself, it is a lot more troublesome than using EasyPoi before.

other use

Since the official documentation of EasyExcel is relatively simple, if you want to use it more deeply, it is recommended that you take a look at the official Demo.

Summarize

Experienced an EasyExcel, it is quite convenient to use, and the performance is also very good. But the more common one-to-many export implementation is more complicated, and the function is not as powerful EasyPoi If the amount of data exported from Excel is not large, you can use EasyPoi. If the amount of data is large and you care about performance, use EasyExcel.

References

• Project address: https://github.com/alibaba/easyexcel
• Official document: https://www.yuque.com/easyexcel/doc/easyexcel

Project source code address

https://github.com/macrozheng/mall-learning/tree/master/mall-tiny-easyexcel

This article GitHub https://github.com/macrozheng/mall-learning has been included, welcome to Star!