easypoi excel导入导出（多sheet）

一、导入jar包

<!--EasyPoi导入导出-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.3</version>
    <exclusions>
        <exclusion>
            <artifactId>guava</artifactId>
            <groupId>com.google.guava</groupId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.0.3</version> </dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.3</version>
</dependency>

二、封装实体类

UserExcel

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
import java.io.Serializable;
import java.util.Date;
 
/**
 * @author gourd
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("userExcel")
public class UserExcel implements Serializable {
 
    /**
     * 名称
     */
    @Excel(name = "姓名",orderNum = "1")
    private String name;
    /**
     * 年龄
     */
    @Excel(name = "年龄",orderNum = "2",groupName = "基本信息")
    private Integer age;
 
    /**
     * 性别
     */
    @Excel(name = "性别",orderNum = "2",replace = {"男_M", "女_F","未知_X"},groupName = "基本信息")
    private String sex;
 
 
    /**
     * 手机
     */
    @Excel(name = "手机",orderNum = "3",width = 20,groupName = "联系方式")
    private String mobilePhone;
 
    /**
     * 邮箱
     */
    @Excel(name = "邮箱",orderNum = "4",width = 20,groupName = "联系方式", isWrap=true)
    private String email;
 
    /**
     * 昵称
     */
    @Excel(name = "昵称",orderNum = "5",groupName = "其他信息")
    private String nickName;
 
    @Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "6",groupName = "其他信息")
    private Date birth;
}

DeptExcel

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
import java.io.Serializable;
import java.util.List;
 
/**
 * @author gourd
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("deptExcel")
public class DeptExcel implements Serializable {
 
    /**
     * 部门代码
     */
    @Excel(name = "部门代码", orderNum = "2", width = 20, needMerge = true)
    private String code;
    /**
     * 部门名称
     */
    @Excel(name = "部门名称", orderNum = "1", width = 20, needMerge = true)
    private String name;
 
    @ExcelCollection(name = "员工信息", orderNum = "3")
    private List<UserExcel> users;
 
 
}

excel导入导出工具类

import cn.afterturn.easypoi.excel.ExcelExportUtil;
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile