EasyPOI使用 导出导入Excel数据

导入依赖

<!--easy poi依赖-->
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-spring-boot-starter</artifactId>
	<version>4.1.3</version>
</dependency>

1.导出数据

一.员工类

• 员工类使用 Excel 定义了需要导出的属性， name 为导出的列名， width 可以定义列的宽度， format可以定义导入和导出的日期格式
• 员工类中还有其他类作为属性，例如民族，政治面貌，职称，职位等。这些使用 ExcelEntity 标记为实体类

package com.xxxx.server.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
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.time.LocalDate;

/**
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_employee")
@ApiModel(value="Employee对象", description="")
public class Employee implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "员工编号")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "员工姓名")
    @Excel(name = "员工姓名")
    private String name;

    @ApiModelProperty(value = "性别")
    @Excel(name = "性别")
    private String gender;

    @ApiModelProperty(value = "出生日期")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    @Excel(name = "出生日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate birthday;

    @ApiModelProperty(value = "身份证号")
    @Excel(name = "身份证号",width = 30)
    private String idCard;

    @ApiModelProperty(value = "婚姻状况")
    @Excel(name = "婚姻状况")
    private String wedlock;

    @ApiModelProperty(value = "民族")
    private Integer nationId;

    @ApiModelProperty(value = "籍贯")
    @Excel(name = "籍贯")
    private String nativePlace;

    @ApiModelProperty(value = "政治面貌")
    private Integer politicId;

    @ApiModelProperty(value = "邮箱")
    @Excel(name = "邮箱",width = 30)
    private String email;

    @ApiModelProperty(value = "电话号码")
    @Excel(name = "电话号码",width = 15)
    private String phone;

    @ApiModelProperty(value = "联系地址")
    @Excel(name = "联系地址",width = 40)
    private String address;

    @ApiModelProperty(value = "所属部门")
    private Integer departmentId;

    @ApiModelProperty(value = "职称ID")
    private Integer jobLevelId;

    @ApiModelProperty(value = "职位ID")
    private Integer posId;

    @ApiModelProperty(value = "聘用形式")
    @Excel(name = "聘用形式")
    private String engageForm;

    @ApiModelProperty(value = "最高学历")
    @Excel(name = "最高学历")
    private String tiptopDegree;

    @ApiModelProperty(value = "所属专业")
    @Excel(name = "所属专业",width = 20)
    private String specialty;

    @ApiModelProperty(value = "毕业院校")
    @Excel(name = "毕业院校",width = 20)
    private String school;

    @ApiModelProperty(value = "入职日期")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    @Excel(name = "入职日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate beginDate;

    @ApiModelProperty(value = "在职状态")
    @Excel(name = "在职状态")
    private String workState;

    @ApiModelProperty(value = "工号")
    @Excel(name = "工号")
    private String workID;

    @ApiModelProperty(value = "合同期限")
    @Excel(name = "合同期限",suffix = "年")//suffix = "年"  在数据后面加上年
    private Double contractTerm;

    @ApiModelProperty(value = "转正日期")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    @Excel(name = "转正日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate conversionTime;

    @ApiModelProperty(value = "离职日期")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    private LocalDate notWorkDate;

    @ApiModelProperty(value = "合同起始日期")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    @Excel(name = "合同起始日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate beginContract;

    @ApiModelProperty(value = "合同终止日期")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone = "Asia/Shanghai")
    @Excel(name = "合同终止日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate endContract;

    @ApiModelProperty(value = "工龄")
    private Integer workAge;

    @ApiModelProperty(value = "工资账套ID")
    private Integer salaryId;

    @ApiModelProperty(value = "民族")
    @TableField(exist = false)
    @ExcelEntity(name = "民族")
    private Nation nation;

    @ApiModelProperty(value = "政治面貌")
    @TableField(exist = false)
    @ExcelEntity(name = "政治面貌")
    private PoliticsStatus politicsStatus;

    @ApiModelProperty(value = "部门")
    @TableField(exist = false)
    @ExcelEntity(name = "部门")
    private Department department;

    @ApiModelProperty(value = "职称")
    @TableField(exist = false)
    @ExcelEntity(name = "职称")
    private Joblevel joblevel;

    @ApiModelProperty(value = "职位")
    @TableField(exist = false)
    @ExcelEntity(name = "职位")
    private Position position;

    @ApiModelProperty(value = "工资账套")
    @TableField(exist = false)
    private Salary salary;
}

二.修改其他实体类
在员工类中仅仅用 ExcelEntity 标记为实体类仍然无法导出实体类中具体属性，需要到被标记
的实体类添加 Excel 标记导出的属性
例如：

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_nation")
@ApiModel(value="Nation对象", description="")
public class Nation implements Serializable {
	@ApiModelProperty(value = "id")
	@TableId(value = "id", type = IdType.AUTO)
	private Integer id;

	@ApiModelProperty(value = "民族")
	@Excel(name = "民族")
	private String name;
}

三.Controller

	@ApiOperation(value = "导出员工数据")
	@GetMapping(value = "/export",produces = "application/octet-stream")
	public void exportEmployee(HttpServletResponse response){//以流的形式导出
		List<Employee> list = employeeService.getEmployee(null);//查询所有员工
		ExportParams params = new ExportParams("员工表","员工表", ExcelType.HSSF);
		Workbook workbook = ExcelExportUtil.exportExcel(params, Employee.class, list);
		ServletOutputStream out = null;
		try {
			//流形式
			response.setHeader("content-type","application/octet-stream");
			//防止中文乱码
			response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("员工表.xls","UTF-8"));
			out = response.getOutputStream();
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if (null!=out){
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

四.测试

2.导入数据

导入时，我们需要获取到对应的民族id，政治面貌id，职称id，职位id等。有两种方法

1. 根据name属性的值去数据库查询对应的id，显然在循环里面不断去查询数据库非常消耗性
   能，不推荐
2. 重写equals和hashCode方法，只要name属性的值一致就表示对象一致。前提是name属
   性的值基本不会变动
   我们选择第二种方法实现

例如：

package com.xxxx.server.pojo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;

/**
 */
@Data
@NoArgsConstructor
@RequiredArgsConstructor //加入有参构造
@EqualsAndHashCode(callSuper = false,of = "name")//表示重写了equals和hashcode方法
@Accessors(chain = true)
@TableName("t_nation")
@ApiModel(value="Nation对象", description="")
public class Nation implements Serializable {

    private static final long serialVersionUID = 1L;

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

    @ApiModelProperty(value = "民族")
    @Excel(name = "民族")
    @NonNull //表示必填
    private String name;
}

Controller上传员工数据

	@ApiOperation(value = "导入员工数据")
	@PostMapping("/import")
	public RespBean importEmployee(MultipartFile file){
		ImportParams params = new ImportParams();
		//去掉标题行
		params.setTitleRows(1);
		List<Nation> nationList = nationService.list();
		List<PoliticsStatus> politicsStatusList = politicsStatusService.list();
		List<Department> departmentList = departmentService.list();
		List<Joblevel> joblevelList = joblevelService.list();
		List<Position> positionList = positionService.list();
		try {
			List<Employee> list = ExcelImportUtil.importExcel(file.getInputStream(), Employee.class, params);
			list.forEach(employee -> {
				//民族id  new Nation(employee.getNation().getName()))  这是重写了equals和hashcode方法
				employee.setNationId(nationList.get(nationList.indexOf(new Nation(employee.getNation().getName()))).getId());
				//政治面貌id
				employee.setPoliticId(politicsStatusList.get(politicsStatusList.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());
				//部门id
				employee.setDepartmentId(departmentList.get(departmentList.indexOf(new Department(employee.getDepartment().getName()))).getId());
				//职称id
				employee.setJobLevelId(joblevelList.get(joblevelList.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());
				//职位id
				employee.setPosId(positionList.get(positionList.indexOf(new Position(employee.getPosition().getName()))).getId());
			});
			if (employeeService.saveBatch(list)){
				return RespBean.success("导入成功!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return RespBean.error("导入失败！");
	}

测试