easyExcel导出下拉选择框，多sheet数据excle导入导出

文中涉及的代码，已上传码云：https://gitee.com/yaoXu9956/easy-excel

一、数据准备

第一步：项目搭建
新建maven项目，导入pom依赖，书写启动类；

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>org.example</groupId>
    <artifactId>easyExcel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--EasyExcel相关依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!-- 糊涂工具包 -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.20</version>
        </dependency>
    </dependencies>

</project>

package com.ifly.task;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @program: easyExcel
 * @description:
 * @create: 2022-07-29 14:21
 **/
@SpringBootApplication
public class EasyExcelApplication {
    public static void main(String[] args) {
        SpringApplication.run(EasyExcelApplication.class, args);
    }
}

第二步：集成JPA

书写 application.properties

server.port=80

# 数据库基本配置
# 只需要创建库，无需创建表
spring.datasource.url=jdbc:mysql://localhost:3306/jpa?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# 表中有数据时不会清空，只会更新
spring.jpa.hibernate.ddl-auto=update

# 控制台显示sql
spring.jpa.show-sql=true

第三步：构建实体类，jpa自动创建表

注意这些注解的使用；

@Entity : 表示这是一个ORM实体，根据这个实体创建表；

@Table(name = “user”) ：指定表的名称；

@Id：指定某个属性是主键；

@Column(name = “user_name”, columnDefinition = “varchar(128) COMMENT ‘人物名称’”)：该注解的name属性表示该注解对应的实体类属性在表中的名称，columnDefinition 是对表的字段做一个限制和说明；

package com.ifly.task.pojo;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;


@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "user")
public class User {

    @Id
    @Column(nullable = false, columnDefinition = "bigint comment '主键id'")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_name", columnDefinition = "varchar(128) COMMENT '人物名称'")
    private String userName;

    /**
     * 性别
     * 1:男 2:女 3:保密
     */
    @Column(name = "gender", columnDefinition = "int(4) DEFAULT 1 COMMENT '性别, 1:男 2:女 3:保密'")
    private Integer gender;

    /**
     * 人物所属类型
     */
    @Column(name = "type", columnDefinition = "int DEFAULT 11 COMMENT '人物所属类型'")
    private Integer type;

}

package com.ifly.task.pojo;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;

/**
 * @program: excel
 * @description: todo
 * @create: 2022-07-21 23:28
 **/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "country")
public class Country {

    @Id
    @Column(nullable = false, columnDefinition = "bigint comment '主键id'")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "country_name", columnDefinition = "varchar(128) COMMENT '国家名称'")
    private String countryName;

    @Column(name = "power", columnDefinition = "varchar(128) COMMENT '掌权者名字'")
    private String power;

    @Column(name = "prime_minister", columnDefinition = "varchar(128) COMMENT '丞相名字'")
    private String primeMinister;

    @Column(name = "create_time", columnDefinition = "datetime COMMENT '国家建立时间'")
    private Date createTime;
}

package com.ifly.task.pojo;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

/**
 * @program: mypro
 * @description:
 * @create: 2022-07-22 14:14
 **/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "type")
public class Type {

    @Id
    @Column(nullable = false, columnDefinition = "bigint comment '主键id'")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name", columnDefinition = "varchar(128) COMMENT '类型名称'")
    private String name;

    @Column(name = "code", columnDefinition = "varchar(128) COMMENT '编码'")
    private String code;

}

第四步：实体类对应的 repository

package com.ifly.task.repository;

import com.ifly.task.pojo.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 14:17
 **/
@Repository
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

package com.ifly.task.repository;

import com.ifly.task.pojo.Country;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 14:17
 **/
@Repository
public interface CountryRepository extends JpaRepository<Country, Long>, JpaSpecificationExecutor<Country> {
}

package com.ifly.task.repository;

import com.ifly.task.pojo.Type;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 14:17
 **/
@Repository
public interface TypeRepository extends JpaRepository<Type, Long>, JpaSpecificationExecutor<Type> {
    /**
     * 获取所有的类型名称
     * @return
     */
    @Query(value = "SELECT name FROM type", nativeQuery = true)
    List<String> findAllType();
}

第五步：写一个controller初始化数据

package com.ifly.task.controller;

import cn.hutool.core.lang.UUID;
import cn.hutool.core.util.RandomUtil;
import com.ifly.task.pojo.Country;
import com.ifly.task.pojo.Type;
import com.ifly.task.pojo.User;
import com.ifly.task.repository.CountryRepository;
import com.ifly.task.repository.TypeRepository;
import com.ifly.task.repository.UserRepository;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @program: easyExcel
 * @description:
 * @create: 2022-07-29 14:34
 **/
@RestController
@RequestMapping("api")
public class InitController {

    private final UserRepository userRepository;
    private final CountryRepository countryRepository;
    private final TypeRepository typeRepository;

    public InitController(UserRepository userRepository, CountryRepository countryRepository, TypeRepository typeRepository){
        this.userRepository = userRepository;
        this.countryRepository = countryRepository;
        this.typeRepository = typeRepository;
    }

    @PostMapping("/init")
    public void init(){

        ArrayList<User> userList = new ArrayList<>();
        userList.add(new User(1L, "赢政", 1, 1));
        userList.add(new User(2L, "孙尚香", 2, 12));
        userList.add(new User(3L, "宫本武藏", 1, 3));
        userList.add(new User(4L, "赢虔", 1, 4));
        userList.add(new User(5L, "欧阳修", 1, 5));
        userList.add(new User(6L, "郦食其", 1, 6));
        userList.add(new User(7L, "贾诩", 1, 7));
        userList.add(new User(8L, "法正", 1, 8));
        userList.add(new User(9L, "韩信", 1, 9));
        userList.add(new User(10L, "黄继光", 1, 10));
        userList.add(new User(11L, "李狗儿", 3, 11));
        userList.add(new User(12L, "滨崎步", 2, 12));
        userRepository.saveAll(userList);

        List<Country> countryList = new ArrayList<Country>();
        countryList.add(new Country(1L, "秦国", "嬴渠梁", "商鞅", new Date()));
        countryList.add(new Country(2L, "赵国", "赵雍", "楼缓", new Date()));
        countryList.add(new Country(3L, "齐国", "姜小白", "管仲", new Date()));
        countryList.add(new Country(4L, "魏国", "魏斯", "李悝", new Date()));
        countryList.add(new Country(5L, "燕国", "燕职", "乐毅", new Date()));
        countryRepository.saveAll(countryList);

        ArrayList<Type> typeDtoArrayList = new ArrayList<>();
        typeDtoArrayList.add(new Type(1L, "君王", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(2L, "勇士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(3L, "武士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(4L, "将军", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(5L, "文士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(6L, "辩士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(7L, "谋士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(8L, "军师", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(9L, "国士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(10L, "烈士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(11L, "男士", UUID.randomUUID().toString()));
        typeDtoArrayList.add(new Type(12L, "女士", UUID.randomUUID().toString()));
        typeRepository.saveAll(typeDtoArrayList);
    }
}

浏览器访问请求：http://localhost/api/init
将上面controller的数据写入到数据库；

二、导出到excel

将数据库中的数据导出到excel，并实现对用户的友好展示

需求：导出user表中的内容和country表中的内容，现在user表中的数据，性别和类型这两列是数字类型，导出的时候需要做一个转换（Converter）；而且我要将这两张表的内容导出到一张excel的不同的sheet；还要对导出的user信息，进行主键隐藏，因为有的项目需要根据导入的excel中的主键进行更新，但是这个主键我不想给用户看到；

第一步：设置导出导出到excel的实体类

这些类是告知程序我们导入导出的excel样式，导入导出时的数据结构；

package com.ifly.task.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.ifly.task.converter.GenderConverter;
import com.ifly.task.converter.TypeConverter;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 15) // 内容行高
@ColumnWidth(value = 20) // 列宽
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 表头样式，水平居中
@HeadFontStyle(fontName = "黑体") // 表头字体
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 内容样式
public class UserExcelVo {

    // 默认不展示的字段（隐藏字段）
    @ColumnWidth(value = 0)
    @ExcelProperty(value = {"用户列表", "编号"})
    private Long id;

    @ExcelProperty(value = {"用户列表", "姓名"})
    private String userName;

    @ExcelProperty(value = {"用户列表", "性别"})
    private Integer gender;

    @ExcelProperty(value = {"用户列表", "类型"})
    private Integer type;

}

package com.ifly.task.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @program: excel
 * @description: todo
 * @create: 2022-07-21 23:28
 **/
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 15) // 内容行高
@ColumnWidth(value = 20) // 列宽
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 表头样式，水平居中
@HeadFontStyle(fontName = "黑体") // 表头字体
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 内容样式
public class CountryExcelVo {

    // 默认不展示的字段（隐藏字段）
    @ColumnWidth(value = 0)
    @ExcelProperty(value = {"国家列表", "编号"})
    private Long id;

    @ExcelProperty(value = {"国家列表", "国名"})
    private String countryName;

    @ExcelProperty(value = {"国家列表", "掌权者"})
    private String power;

    @ExcelProperty(value = {"国家列表", "丞相"})
    private String primeMinister;

    @ExcelProperty(value = {"国家列表", "建朝时间"})
    @DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;
}

第二步：设置导入导出到excel的转换器

现在直接从数据库拿到的user数据，性别和人物类型都是数字类型，我们需要在导出的时候，将数据库中查询到的数字（Java代码中看到的）转换成用户能够直观看到的，比如1表示的就是性别男，2表示的是性别女，3表示对性别保密这样；而且当导入excel时，也能把excel中保存的String类型的数据转换成数字，到时候更新或者新增数据就好办了，直接save或者update；

package com.ifly.task.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.ifly.task.converter.GenderConverter;
import com.ifly.task.converter.TypeConverter;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 15) // 内容行高
@ColumnWidth(value = 20) // 列宽
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 表头样式
@HeadFontStyle(fontName = "黑体")
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 内容样式
public class UserExcelVo {

    // 默认不展示的字段
    @ColumnWidth(value = 0)
    @ExcelProperty(value = {"用户列表", "编号"})
    private Long id;

    @ExcelProperty(value = {"用户列表", "姓名"})
    private String userName;

    @ExcelProperty(value = {"用户列表", "性别"}, converter = GenderConverter.class)
    private Integer gender;

    @ExcelProperty(value = {"用户列表", "类型"}, converter = TypeConverter.class)
    private Integer type;

}

第三步：样式转换器，实现com.alibaba.excel.converters.Converter 接口；

性别转换器【GenderConverter.java】

package com.ifly.task.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.ifly.task.enums.GenderEnum;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 11:10
 **/
public class GenderConverter implements Converter<Integer> {

    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    // 这里我直接使用一个枚举类
    // 这块是将excel表格中的数据转换成需要存入到数据库中的数据
    // 比如在excel中出现的别男，我存入数据库的应该是数字1
    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        return GenderEnum.getValue(context.getReadCellData().getStringValue());
    }

    // 这里是将数据库中查到的值，比如性别栏是数字1，那么在excel中应该显示 男
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        return new WriteCellData<String>(GenderEnum.getDescribe(context.getValue()));
    }
}

【性别枚举类】

package com.ifly.task.enums;

public enum GenderEnum {

    NAIL(1, "男"),
    FEMALE(2, "女"),
    UNKNOWN(3, "保密");

    private final Integer value;
    private final String describe;

    GenderEnum(Integer value, String detail){
        this.value = value;
        this.describe = detail;
    }

    public Integer getValue(){
        return value;
    }

    public String getDescribe(){
        return describe;
    }

    /**
     * 根据value获取describe
     * @param value
     * @return
     */
    public static String getDescribe(Integer value) {
        for (GenderEnum en : GenderEnum.values()) {
            if (en.getValue().equals(value)) {
                return en.getDescribe();
            }
        }
        return "error";
    }

    /**
     * 根据describe获取value
     * @param describe
     * @return
     */
    public static Integer getValue(String describe){
        for (GenderEnum en : GenderEnum.values()) {
            if (en.getDescribe().equals(describe)) {
                return en.getValue();
            }
        }
        return -1;
    }

}

同上理，我再添加一个 任务类型的转换器 【TypeConverter.java】

package com.ifly.task.converter;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.ifly.task.enums.TypeEnum;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 11:10
 **/
public class TypeConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        return TypeEnum.getValue(context.getReadCellData().getStringValue());
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        return new WriteCellData<String>(TypeEnum.getDescribe(context.getValue()));
    }
}

类型枚举类【TypeEnum.java】

package com.ifly.task.enums;

/**
 * @program: mypro
 * @description: 
 * @create: 2022-07-22 14:07
 **/
public enum TypeEnum {
    EMPEROR(1, "君王"),
    BRAVE_MAN(2, "勇士"),
    SAMURAI(3, "武士"),
    GENERAL(4, "将军"),
    LITERATI(5, "文士"),
    TRAIN_SERVICE(6, "辩士"),
    SOPHIST(7, "谋士"),
    MILITARY_ADVISER(8, "军师"),
    UNIQUE(9, "国士"),
    MARTYR(10, "烈士"),
    MALE(11, "男士"),
    FEMALE(12, "女士");

    private final Integer value;
    private final String describe;

    TypeEnum(Integer value, String detail){
        this.value = value;
        this.describe = detail;
    }

    public Integer getValue(){
        return value;
    }

    public String getDescribe(){
        return describe;
    }

    /**
     * 根据value获取describe
     * @param value
     * @return
     */
    public static String getDescribe(Integer value) {
        for (TypeEnum en : TypeEnum.values()) {
            if (en.getValue().equals(value)) {
                return en.getDescribe();
            }
        }
        return "error";
    }

    /**
     * 根据describe获取value
     * @param describe
     * @return
     */
    public static Integer getValue(String describe){
        for (TypeEnum en : TypeEnum.values()) {
            if (en.getDescribe().equals(describe)) {
                return en.getValue();
            }
        }
        return -1;
    }

}

第四步：导出excel

导出到不同的sheet，隐藏主键id，友好的展示在excel中（比如：性别一栏，男就是男，女就是女，不是1和2）

package com.ifly.task.controller;

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.ifly.task.pojo.Country;
import com.ifly.task.pojo.User;
import com.ifly.task.repository.CountryRepository;
import com.ifly.task.repository.TypeRepository;
import com.ifly.task.repository.UserRepository;
import com.ifly.task.vo.CountryExcelVo;
import com.ifly.task.vo.UserExcelVo;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * @program: easyExcel
 * @description:
 * @author: xuYao2
 * @create: 2022-07-29 15:27
 **/
@RestController
@RequestMapping("excel")
public class EasyExcelController {

    private final UserRepository userRepository;
    private final CountryRepository countryRepository;

    public EasyExcelController(UserRepository userRepository, CountryRepository countryRepository){
        this.userRepository = userRepository;
        this.countryRepository = countryRepository;
    }

    @GetMapping("/export")
    public void write(HttpServletResponse response) throws IOException {

        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 设置防止中文名乱码
        String filename = URLEncoder.encode(DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss"), "utf-8");
        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                           filename + ".xlsx");

        // easyExcel 获取写出工具
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

        /***************************************
         注意：实际项目中需要导出的数据，可能需要重新拼装成导入导出定义的excelVo；
         比如我下面的User实体类List集合，是我从数据库直接查出来的，实际项目中我可能只需要id、name、其他的不用导出
         那我在设置下面的UserExcelVo时，就只设置这两个字段就行，而且数据库中查到的数据list集合中的对象，也要跟excelVo对应上；
         再通俗点，User和UserExcelVo字段要一模一样；
        ***************************************/
        List<User> userList = userRepository.findAll();
        List<Country> countryList = countryRepository.findAll();

        excelWriter.write(userList,
                          EasyExcel.writerSheet(0, "用户")
                          .head(UserExcelVo.class)
                          .build());

        excelWriter.write(countryList,
                          EasyExcel.writerSheet(1, "国家").head(CountryExcelVo.class).build());

        // 写入数据到excel
        excelWriter.finish();

    }

}

浏览器访问：http://localhost/excel/export

导出的excel

三、下拉选择框

需求：导入存在的问题，user对应的sheet，性别和类型这块，用户怎么知道输入什么呢？所以这里必须对excel表格，或者说导出的excel模板做下拉框的限定，用户必须有选择的输入；所以导出的时候，就要对excel某些列做下拉选择框的限定；

第一步：写一个注解，指定excelVo中的哪些字段是做下拉选择框限定的，这里的下拉选择框，可以做成静态的（固定的几项），比如这里的性别，就三种：男、女、保密；动态的（从数据库中获取），比如这里的类型，有很多，需要从数据库获取（这里的类型我也做了枚举限制，其实也可以遍历枚举类型，将类型名放在一个String数组，作为动态下拉选择框）；

package com.ifly.task.anno;

import com.ifly.task.selector.ExcelDynamicSelect;

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

/**
 * 标注导出的列为下拉框类型，并为下拉框设置内容
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;
}

第二步：动态下拉选择框的接口，然后写一个类型选择下拉框的数据获取；

package com.ifly.task.selector;

public interface ExcelDynamicSelect {
    /**
     * 获取动态生成的下拉框可选数据
     * @return 动态生成的下拉框可选数据
     */
    String[] getSource();
}

package com.ifly.task.selector.impl;

import com.ifly.task.repository.TypeRepository;
import com.ifly.task.selector.ExcelDynamicSelect;
import com.ifly.task.util.SpringContextUtil;

import java.util.List;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 14:04
 **/
public class TypeSelector implements ExcelDynamicSelect {
    @Override
    public String[] getSource() {
        TypeRepository repository = SpringContextUtil.getBean(TypeRepository.class);
        List<String> allType = repository.findAllType();
        String[] strings = allType.toArray(new String[0]);
        return strings;
    }
}

这里使用到工具类：

package com.ifly.task.util;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 13:45
 **/
@Component
public class SpringContextUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }

    // 通过class获取Bean
    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }

    // 通过name以及class获取Bean
    public static <T> T getBean(String name, Class<T> clazz) {
        return applicationContext.getBean(name, clazz);
    }

}

第三步：给UserExcelVo 加上下拉选择框的注解

package com.ifly.task.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.ifly.task.anno.ExcelSelected;
import com.ifly.task.converter.GenderConverter;
import com.ifly.task.converter.TypeConverter;
import com.ifly.task.selector.impl.TypeSelector;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;


@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
@HeadRowHeight(value = 25) // 头部行高
@ContentRowHeight(value = 15) // 内容行高
@ColumnWidth(value = 20) // 列宽
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 表头样式
@HeadFontStyle(fontName = "黑体")
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER) // 内容样式
public class UserExcelVo {

    // 默认不展示的字段
    @ColumnWidth(value = 0)
    @ExcelProperty(value = {"用户列表", "编号"})
    private Long id;

    @ExcelProperty(value = {"用户列表", "姓名"})
    private String userName;

    //静态下拉框
    @ExcelSelected(source = {"男","女","保密"})
    @ExcelProperty(value = {"用户列表", "性别"}, converter = GenderConverter.class)
    private Integer gender;

    //动态下拉框
    @ExcelSelected(sourceClass = TypeSelector.class)
    @ExcelProperty(value = {"用户列表", "类型"}, converter = TypeConverter.class)
    private Integer type;

}

第四步：如何处理这个下拉选择框？

【ExcelSelectedResolve.java】

静态或者动态的获取下拉选择框内容，静态的是注解中 source 属性对应的字符串数组；动态下拉选择框是实现了 ExcelDynamicSelect 接口的实现类，实现类中重写的 getSource() 方法，就是从数据库中动态获取String数组；这里使用到了反射的基本原理；

package com.ifly.task.anno;

import com.ifly.task.selector.ExcelDynamicSelect;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

/**
 * @program: mypro
 * @description: todo
 * @author: xuYao2
 * @create: 2022-07-22 13:35
 **/
@Data
@Slf4j
public class ExcelSelectedResolve {
    /**
     * 下拉内容
     */
    private String[] source;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;

    public String[] resolveSelectedSource(ExcelSelected excelSelected) {
        if (excelSelected == null) {
            return null;
        }

        // 获取固定下拉框的内容
        String[] source = excelSelected.source();
        if (source.length > 0) {
            return source;
        }

        // 获取动态下拉框的内容
        Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
        if (classes.length > 0) {
            try {
                ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
                String[] dynamicSelectSource = excelDynamicSelect.getSource();
                if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
                    return dynamicSelectSource;
                }
            } catch (InstantiationException | IllegalAccessException e) {
                log.error("解析动态下拉框数据异常", e);
            }
        }
        return null;
    }
}

还需要借助工具类【EasyExcelUtil.java】，解析表头类中的下拉注解。看代码

package com.ifly.task.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.ifly.task.anno.ExcelSelected;
import com.ifly.task.anno.ExcelSelectedResolve;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 13:40
 **/
@Slf4j
public class EasyExcelUtil {

    /**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    public static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                // 处理下拉框内容
                String[] source = excelSelectedResolve.resolveSelectedSource(selected);
                if (source != null && source.length > 0){
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property != null && property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }

        return selectedMap;
    }
}

第五步：在导出代码中，处理下拉选择框

使用工具类，将下拉框做成一个map的形式（列号，下拉选择框String数组），注意列号是从0开始的；

【EasyExcelController.java】

@GetMapping("/export")
public void write(HttpServletResponse response) throws IOException {

    // 设置响应头
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 设置防止中文名乱码
    String filename = URLEncoder.encode(DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss"), "utf-8");
    // 文件下载方式(附件下载还是在当前浏览器打开)
    response.setHeader("Content-disposition", "attachment;filename=" +
                       filename + ".xlsx");

    // easyExcel 获取写出工具
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
    // 设置下拉列表
    Map<Integer, ExcelSelectedResolve> selectedMap = EasyExcelUtil.resolveSelectedAnnotation(UserExcelVo.class);

    List<User> userList = userRepository.findAll();
    List<Country> countryList = countryRepository.findAll();

    excelWriter.write(userList,
                      EasyExcel.writerSheet(0, "用户")
                      .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                      .head(UserExcelVo.class)
                      .build());

    excelWriter.write(countryList,
                      EasyExcel.writerSheet(1, "国家").head(CountryExcelVo.class).build());

    // 写入数据到excel
    excelWriter.finish();


}

【SelectedSheetWriteHandler.java】

package com.ifly.task.handler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.ifly.task.anno.ExcelSelectedResolve;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

/**
 * @program: mypro
 * @description: todo
 * @create: 2022-07-22 13:36
 **/
@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        selectedMap.forEach((k, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            // 设置下拉列表的值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
    }
}

最终导出效果：

四、excle导入

@PostMapping("/import")
public void importExcel(@RequestParam("file") MultipartFile file) throws IOException {
    List<UserExcelVo> userList = EasyExcel.read(file.getInputStream())
        .head(UserExcelVo.class)
        .sheet(0)
        .doReadSync();

    List<CountryExcelVo> countryList = EasyExcel.read(file.getInputStream())
        .head(CountryExcelVo.class)
        .sheet(1)
        .doReadSync();

    log.info("userList: {}", userList);
    log.info("country: {}", countryList);

}

看控制台：

userList: [UserExcelVo(id=1, userName=赢政, gender=1, type=1), UserExcelVo(id=2, userName=孙尚香, gender=2, type=12), UserExcelVo(id=3, userName=宫本武藏, gender=1, type=3), UserExcelVo(id=4, userName=赢虔, gender=1, type=4), UserExcelVo(id=5, userName=欧阳修, gender=1, type=5), UserExcelVo(id=6, userName=郦食其, gender=1, type=6), UserExcelVo(id=7, userName=贾诩, gender=1, type=7), UserExcelVo(id=8, userName=法正, gender=1, type=8), UserExcelVo(id=9, userName=韩信, gender=1, type=9), UserExcelVo(id=10, userName=黄继光, gender=1, type=10), UserExcelVo(id=11, userName=李狗儿, gender=3, type=11), UserExcelVo(id=12, userName=滨崎步, gender=2, type=12)]

country: [CountryExcelVo(id=1, countryName=秦国, power=嬴渠梁, primeMinister=商鞅, createTime=Fri Jul 29 15:19:56 CST 2022), CountryExcelVo(id=2, countryName=赵国, power=赵雍, primeMinister=楼缓, createTime=Fri Jul 29 15:19:56 CST 2022), CountryExcelVo(id=3, countryName=齐国, power=姜小白, primeMinister=管仲, createTime=Fri Jul 29 15:19:56 CST 2022), CountryExcelVo(id=4, countryName=魏国, power=魏斯, primeMinister=李悝, createTime=Fri Jul 29 15:19:56 CST 2022), CountryExcelVo(id=5, countryName=燕国, power=燕职, primeMinister=乐毅, createTime=Fri Jul 29 15:19:56 CST 2022)]

需求：导入刚刚导出的excel，但是对导出的excel做了修改，直接导入excel对数据库中的数据进行修改；

第一步：这里我使用的了mapStruct；导入依赖；

<!-- mapStruct -->
<dependency>
    <groupId>org.mapstruct</groupId>
    <artifactId>mapstruct-jdk8</artifactId>
    <version>1.3.1.Final</version>
</dependency>
<dependency>
    <groupId>org.mapstruct</groupId>
    <artifactId>mapstruct-processor</artifactId>
    <version>1.3.1.Final</version>
</dependency>

再新建包，写两个接口，注意接口上加上注解：@Mapper(componentModel = "spring")

package com.ifly.task.mapper;

import com.ifly.task.pojo.User;
import com.ifly.task.vo.UserExcelVo;
import org.mapstruct.Mapper;
import java.util.List;

@Mapper(componentModel = "spring")
public interface UserConvertMapper {
    List<User> toUserList(List<UserExcelVo> userExcelVoList);
}

package com.ifly.task.mapper;

import com.ifly.task.pojo.Country;
import com.ifly.task.vo.CountryExcelVo;
import org.mapstruct.Mapper
import java.util.List;

@Mapper(componentModel = "spring")
public interface CountryConvertMapper {
    List<Country> toCountryList(List<CountryExcelVo> countryExcelVoList);
}

第二步：修改Country 实体类

package com.ifly.task.pojo;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;

/**
 * @program: excel
 * @description: todo
 * @author: xuYao2
 * @create: 2022-07-21 23:28
 **/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "country")
@EntityListeners(AuditingEntityListener.class)
public class Country {

    @Id
    @Column(nullable = false, columnDefinition = "bigint comment '主键id'")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "country_name", columnDefinition = "varchar(128) COMMENT '国家名称'")
    private String countryName;

    @Column(name = "power", columnDefinition = "varchar(128) COMMENT '掌权者名字'")
    private String power;

    @Column(name = "prime_minister", columnDefinition = "varchar(128) COMMENT '丞相名字'")
    private String primeMinister;

    /**
     * 自动创建时间：https://blog.csdn.net/jj89929665/article/details/119487947
     */
    @Column(name = "create_time", columnDefinition = "datetime COMMENT '国家建立时间'")
    @CreatedDate
    private Date createTime;
}

还需要在启动类上加上@EnableJpaAuditing 注解

第三步：修改导出接口

@Resource
private UserConvertMapper userConvertMapper;

@Resource
private CountryConvertMapper countryConvertMapper;

@PostMapping("/import")
public void importExcel(@RequestParam("file") MultipartFile file) throws IOException {
    List<UserExcelVo> userList = EasyExcel.read(file.getInputStream())
        .head(UserExcelVo.class)
        .sheet(0)
        .doReadSync();

    List<CountryExcelVo> countryList = EasyExcel.read(file.getInputStream())
        .head(CountryExcelVo.class)
        .sheet(1)
        .doReadSync();

    log.info("userList: {}", userList);
    log.info("country: {}", countryList);

    // mapStruct对象转换
    List<User> toUserList = userConvertMapper.toUserList(userList);
    List<Country> toCountryList = countryConvertMapper.toCountryList(countryList);

    // 数据库中的数据
    List<User> userListInDataBase = userRepository.findAll();
    List<Country> countryListInDataBase = countryRepository.findAll();

    // 处理导出的excel中被删除的数据
    List<Long> userIdsInDataBase = userListInDataBase.stream().map(User::getId).collect(Collectors.toList());
    List<Long> userIdsInExcel = toUserList.stream().map(User::getId).collect(Collectors.toList());
    // excel中没有，但数据库中存在的id，是做了删除操作的
    userIdsInDataBase.removeAll(userIdsInExcel);
    userIdsInDataBase.forEach(userRepository::deleteById);

    List<Long> countryIdsInDataBase = countryListInDataBase.stream().map(Country::getId).collect(Collectors.toList());
    List<Long> countryIdsInExcel = toCountryList.stream().map(Country::getId).collect(Collectors.toList());
    countryIdsInDataBase.removeAll(countryIdsInExcel);
    countryIdsInDataBase.forEach(countryRepository::deleteById);

    userRepository.saveAll(toUserList);
    countryRepository.saveAll(toCountryList);

}

第四步：测试

在postman上导入数据：

查看数据库：