1, Introduction

The EasyPoi function is like the name easy. The main function is easy, so that a person who has not seen and contacted the poi can easily write Excel export, Excel template export, Excel import and Word template export, and complete the previous complex writing through simple comments and template language (familiar expression syntax)

If you want to know about JeecgBoot's Autopoi, you can refer to another blog of the original author, which uses JeecgBoot in spring boot Autopoi export Excel

https://blog.csdn.net/qq_40065776/article/details/107824221

2, Introducing EasyPoi

EasyPoi is also well encapsulated in SpringBoot, so that we can quickly use EasyPoi for development in SpringBoot

<!-- easypoi -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.2.0</version>
</dependency>

We only need to introduce this dependency, which is a good support for SpringBoot

3, Source code interpretation

1. @ Excel source code interpretation
By consulting the source code, it is not difficult for us to start from CN afterturn. easypoi. excel. annotation. Found in Excel comments

/**
 * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com)
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.afterturn.easypoi.excel.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel Export basic notes
 * @author JueYue
 *  2014 10:25:12 PM, June 20, 2014
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {

    /**
     * When exporting, the fields of the corresponding database are mainly the column names that users distinguish between each field. There can be no column names with the same name of annotation when exporting
     * Export sorting is related to the order of the fields with annotation defined. You can use a_id,b_id to confirm whether to use
     */
    public String name();

    /**
     * A can be used in the first few_ id,b_ ID to determine the different sort
     */
    public String orderNum() default "0";

    /**
     * The export worth replacing is {a_id, and the b_id} import is the reverse, so only write one
     */
    public String[] replace() default {};
    /**
     *  Dictionary name
     */
    public String dict() default  "";
}

The above is the code snippet of @ Excel annotation. We can see that the annotation supports two dictionary replacement methods

• 1. replace, which supports direct writing into annotation parameters, such as:

@Excel(name = "Gender", width = 15, replace = "male_1,female_2")
@TableField("sex")
private Integer sex;

We use 1 for male and 2 for female, so that we can automatically replace the magic value in the data when exporting. However, in this way, we often have to write too much code in the annotation parameters, and our dictionary often changes dynamically, which is too limited

• 2. dict, dictionary mode, passed into dictionary parameters, such as:

@Excel(name = "Gender", width = 15, dict = "sex")
@TableField("sex")
private Integer sex;

Here, we only pass in the key of the dictionary, so that when we query the data and write to the Excel file, we can replace the magic value in the data and increase the readability of the data

2. Interpretation of IExcelDictHandler source code
In the previous step, we already know that EasyPoi supports custom dictionary query and export, so how do we implement it? By reading CN afterturn. EasyPoi. handler. inter. The code in iexceldicthandler interface is as follows:

package cn.afterturn.easypoi.handler.inter;

import java.util.List;
import java.util.Map;

/**
 * @author jueyue on 18-2-2.
 * @version 3.0.4
 */
public interface IExcelDictHandler {

    /**
     * Returns all values of the dictionary
     * key: dictKey
     * value: dictValue
     * @param dict  Dictionary Key
     * @return
     */
    default public List<Map> getList(String dict) {
        return null;
    }

    /**
     * Translate from value to name
     *
     * @param dict  Dictionary Key
     * @param obj   object
     * @param name  Attribute name
     * @param value Attribute value
     * @return
     */
    public String toName(String dict, Object obj, String name, Object value);

    /**
     * Translate from name to value
     *
     * @param dict  Dictionary Key
     * @param obj   object
     * @param name  Attribute name
     * @param value Attribute value
     * @return
     */
    public String toValue(String dict, Object obj, String name, Object value);
}

Three methods are provided in the interface:

• 1. getList, query all dictionary data under the key through the dictionary key, for example, {"1": "male", "2": "female"} under sex

• 2. toName, the translation function of the dictionary, which translates from value to name, for example: sex: 1 -- > "male", which is generally used when exporting

• 3. toValue, contrary to toName, translates from name to value, for example: sex: "male" - > 1, which is generally used when importing

Since we know that the dictionary translation interface is provided in EasyPoi, we only need to provide an implementation class and rewrite the methods in the interface, iexceldicthandlerimpl Java implements the IExcelDictHandler interface. The code is as follows:

package com.zyxx.common.excel;

import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import com.zyxx.sys.service.SysDictDetailService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

/**
 * Dictionary parameter setting is supported
 * Example: @ Excel(name = "gender", width = 15, dicCode = "sex")
 * 1,When exporting, the values 1 and 2 will be translated into: male and female according to the dictionary configuration;
 * 2,When importing, the male and female will be translated into 1 and 2 and stored in the database;
 *  * @Author lizhou
 */
@Slf4j
@Component
public class IExcelDictHandlerImpl implements IExcelDictHandler {

    @Autowired
    private SysDictDetailMapper testSysDictDetailMapper;
    private static SysDictDetailMapper sysDictDetailMapper;

    @PostConstruct
    public void init() {
        sysDictDetailMapper = this.testSysDictDetailMapper;
    }

    /**
     * Translate from value to name
     *
     * @param dict  Dictionary Key
     * @param obj   object
     * @param name  Attribute name
     * @param value Attribute value
     * @return
     */
    @Override
    public String toName(String dict, Object obj, String name, Object value) {
        return sysDictDetailMapper.getTextByDictAndValue(dict, String.valueOf(value));
    }

    /**
     * Translate from name to value
     *
     * @param dict  Dictionary Key
     * @param obj   object
     * @param name  Attribute name
     * @param value Attribute value
     * @return
     */
    @Override
    public String toValue(String dict, Object obj, String name, Object value) {
        return null;
    }
}

• 1. Here, we export and only use the method of toName (translation from value to name), so we only write one method

• 2. We need to load it into the Spring container using the @ Component annotation

• 3. @ PostConstruct this annotation is used to modify a non static void() method. By @ PostConstruct
  The decorated method will run when the server loads the Servlet and will only be executed by the server once PostConstruct
  Executed after the constructor and before the init() method

4, Start export

1. Define entity classes

package com.zyxx.sys.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.*;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.zyxx.common.annotation.Dict;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * <p>
 * User information table
 * </p>
 *  * @author lizhou
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("sys_user_info")
@ApiModel(value = "SysUserInfo object", description = "User information table")
public class SysUserInfo extends Model<SysUserInfo> {


    @ApiModelProperty(value = "ID")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @Excel(name = "full name", width = 15)
    @ApiModelProperty(value = "full name")
    @TableField("name")
    private String name;

    @Excel(name = "Telephone", width = 15)
    @ApiModelProperty(value = "Telephone")
    @TableField("phone")
    private String phone;

    @Excel(name = "Gender", width = 15, dict = "sex")
    @TableField("sex")
    @Dict(dictCode = "user_sex")
    private Integer sex;

    @Excel(name = "state", width = 15, dict = "status")
    @TableField("status")
    private Integer status;
}

@Excel notes are explained as follows:

• Name, header name
• Width, column width
• dict, dictionary key

2. Export API interface
The controller layer provides an export API

@ApiOperation(value = "Export user information", notes = "Export user information")
@GetMapping(value = "/export")
public void exportXls(HttpServletResponse response) {
	// Query data
    List<SysUserInfo> list = sysUserInfoService.list(1, Integer.MAX_VALUE);
    // Export data, data, data type, file name, table name, response object
    ExportExcelUtil.exportExcel(list, SysUserInfo.class, "User information table", "User information statistics", response);
}

3. Export tool class

/**
* Export excel
*
* @param list Data set
* @param pojoClass data type
* @param fileName File name
* @param title indicate
* @param response Response object
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, String title, HttpServletResponse response) {
   ExportParams exportParams = new ExportParams(title, null);
   // Custom dictionary query rules
   exportParams.setDictHandler(new IExcelDictHandlerImpl());
   Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
   if (workbook != null) {
       try {
           response.setCharacterEncoding("UTF-8");
           response.setHeader("content-Type", "application/vnd.ms-excel");
           response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
           workbook.write(response.getOutputStream());
       } catch (IOException e) {
           e.printStackTrace();
       }
   }
}

exportParams.setDictHandler(new IExcelDictHandlerImpl());， We passed in custom dictionary query rules

5, Test export

We can export files by calling the API interface of exported data. The export effect is as follows:

6, Summary

It can be seen that the custom dictionary query and export method is actually similar to the Autopoi method of JeecgBoot. Later, it is found that there is a conflict between the Autopoi of JeecgBoot and the reading file ExcelReader of hutool, and the Autopoi of JeecgBoot is abandoned. EasyPoi is indeed a powerful Excel operation product!!!

If you find any deficiency in reading, please leave a message!!!