excel的导入导出

1.使用easyPoi依赖
easyPoi官方文档添加链接描述

 <!--excel导入导出-->
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-spring-boot-starter</artifactId>
      <version>4.4.0</version>
    </dependency>

---

导出功能实现

2.在需要导出的实体类pojo属性上加@Excel(name = “性别”)，
例如 @Excel(name = “出生日期”,width = 20,format = “yyyy-MM-dd”)， @Excel(name = “合同期限”,suffix = “年”)
3.Employee对象中存在Nation（民族），PoliticsStatus（政治面貌），Department（部门），Position(职位)等对象

   @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;

在Nation（民族）pojo中只在要导出的字段上加 @Excel(name = “民族”)，就好。 其他的pojo也如此

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

要导出的pojo如下：

/**
 * @author haoStar
 * @since 2021-08-18
 */
@Data
@EqualsAndHashCode(callSuper = false)
@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 = "员工姓名")   //easypoi 导入导出的列名
    private String name;

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

    @ApiModelProperty(value = "出生日期")
    @Excel(name = "出生日期",width = 20,format = "yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone ="Asia/Shanghai" ) //返回给前端的格式化
    private LocalDate birthday;

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

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

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

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

    @ApiModelProperty(value = "政治面貌")
    @TableField("politicId")
    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 = "所属部门")
    @TableField("departmentId")
    private Integer departmentId;

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

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

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

    @ApiModelProperty(value = "最高学历")
    @TableField("tiptopDegree")
    @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" ) //返回给前端的格式化
    @TableField("beginDate")
    @Excel(name = "入职日期",format = "yyyy-MM-dd",width = 20)
    private LocalDate beginDate;

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

    @ApiModelProperty(value = "工号")
    @TableField("workID")
    @Excel(name = "工号",width = 10)
    private String workID;

    @ApiModelProperty(value = "合同期限")
    @TableField("contractTerm")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone ="Asia/Shanghai" ) //返回给前端的格式化
    @Excel(name = "合同期限",suffix = "年")
    private Double contractTerm;

    @ApiModelProperty(value = "转正日期")
    @TableField("conversionTime")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone ="Asia/Shanghai" ) //返回给前端的格式化
    @Excel(name="转正日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate conversionTime;

    @ApiModelProperty(value = "离职日期")
    @TableField("notWorkDate")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone ="Asia/Shanghai" ) //返回给前端的格式化
    private LocalDate notWorkDate;

    @ApiModelProperty(value = "合同起始日期")
    @TableField("beginContract")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone ="Asia/Shanghai" ) //返回给前端的格式化
    @Excel(name = "合同起始日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate beginContract;

    @ApiModelProperty(value = "合同终止日期")
    @TableField("endContract")
    @JsonFormat(pattern = "yyyy-MM-dd",timezone ="Asia/Shanghai" ) //返回给前端的格式化
    @Excel(name = "合同终止日期",width = 20,format = "yyyy-MM-dd")
    private LocalDate endContract;

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

    @ApiModelProperty(value = "工资账套ID")
    @TableField("salaryId")
    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;

}

exportEmployee的controller

 /**
     *  excel的导入导出
     *easyPoi依赖
     * produces = "application/octet-stream"解决流输出，页面乱码
     */
    @ApiOperation(value = "导出员工数据")
    @GetMapping(value = "/export",produces = "application/octet-stream")
    public void exportEmployee(HttpServletResponse response){
        //查询员工数据 id==null查全部，id！=null查id对应的员工
        List<Employee> list = employeeService.getEmployees(null);
        //参数1:文件名名字，参数2：指的是：表格的名称，参数3：导出excel的类型
        ExportParams exportParams = new ExportParams("员工表","员工表", ExcelType.HSSF);
        //工作布
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Employee.class, list);
        ServletOutputStream outputStream = null;
        try {
            //输出的响应头,流的形式
            response.setHeader("content-type","application/octet-stream");
            //防止中文乱码
            response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("员工表.xls","UTF-8"));
            //输出流
           outputStream = response.getOutputStream();
           workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if (outputStream != null){
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

IEmployeeService

/**
 *导出员工数据
 */
    List<Employee> getEmployees(Integer id);

---

   <!--导出员工数据-->
    <select id="getEmployees" resultMap="EmployeeInfo">
        SELECT
            e.*,
            n.id AS nid,
            n.`name` AS nname,
            p.id AS pid,
            p.`name` AS pname,
            d.id AS did,
            d.`name` AS dname,
            j.id AS jid,
            j.`name` AS jname,
            pos.id AS posid,
            pos.`name` AS posname
        FROM
            t_employee AS e,
            t_nation AS n ,
            t_politics_status AS p ,
            t_department AS d ,
            t_joblevel AS j ,
            t_position AS pos
        WHERE
            e.nationId = n.id
            AND e.politicId = p.id
            AND e.departmentId = d.id
            AND e.jobLevelId = j.id
            AND e.posId = pos.id
        <if test="null!=id and '' !=id">
            AND e.id = #{id}
        </if>
            ORDER BY e.id
    </select>

    <resultMap id="EmployeeInfo" type="com.hao.server.pojo.Employee" extends="BaseResultMap">
       <association property="nation" javaType="com.hao.server.pojo.Nation">
           <id column="nid" property="id"/>
           <result column="nname" property="name"/>
       </association>
        <association property="politicsStatus" javaType="com.hao.server.pojo.PoliticsStatus">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
        </association>
        <association property="department" javaType="com.hao.server.pojo.Department">
            <id column="did" property="id"/>
            <result column="dname" property="name"/>
        </association>
        <association property="joblevel" javaType="com.hao.server.pojo.Joblevel">
            <id column="jid" property="id"/>
            <result column="jname" property="name"/>
        </association>
        <association property="position" javaType="com.hao.server.pojo.Position">
            <id column="posid" property="id"/>
            <result column="posname" property="name"/>
        </association>
    </resultMap>

导出结果：如图

3.excel导入

1.Employee实体类没有变化
2.只是在Nation（民族）PoliticsStatus（政治面貌）Department（部门）Joblevel（职称）上存在变化
3.例如：在Nation的pojo加上
@NoArgsConstructor
@RequiredArgsConstructor
@EqualsAndHashCode(callSuper = false,of = “name”)// of = "name"表示重写Equals和HashCode方法
@NonNull 加在要导入的字段上
4.其他PoliticsStatus（政治面貌）Department（部门）Joblevel（职称）同样如此

/**
 * @author haoStar
 * @since 2021-08-18
 */
@Data
@NoArgsConstructor
@RequiredArgsConstructor    //有参构造器
@EqualsAndHashCode(callSuper = false,of = "name")// of = "name"表示重写Equals和HashCode方法
@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 importParams = new ImportParams();
        //去掉标题
        importParams.setTitleRows(1);
        List<Nation> nationList = nationService.list();
        List<PoliticsStatus> politicsStatusList = politicsStatusService.list();
        List<Department> departmentList = departmentService.list();
        List<Joblevel> joblevelList = joblevelService.list();
        try {
            List<Employee> list = ExcelImportUtil.importExcel(file.getInputStream(), Employee.class, importParams);
            list.forEach(employee -> {
                //政治面貌
                PoliticsStatus politicsStatus = new PoliticsStatus(employee.getPoliticsStatus().getName());
                politicsStatusList.indexOf(politicsStatus);
                Integer politicsStatusId = politicsStatusList.get(politicsStatusList.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId();
                employee.setPoliticId(politicsStatusId);
                /**
                 *  employee.getNation().getName() 可以从employee的对象中拿到Nation对象中的name
                 *  indexOf() 方法返回动态数组中元素的索引值。
                 *  nationList.indexOf(new Nation(employee.getNation().getName())) 拿到对象的索引值
                 *  nationList.get(nationList.indexOf(new Nation(employee.getNation().getName()))).getId() 拿到Nation对象中的id
                 */
                //拿到民族id
                employee.setNationId(nationList.get(nationList.indexOf(new Nation(employee.getNation().getName()))).getId());
               //部门
                employee.setDepartmentId(departmentList.get(departmentList.indexOf(new Department(employee.getDepartment().getName()))).getId());
                //职称
                employee.setJobLevelId(joblevelList.get(joblevelList.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());
            });
            if (employeeService.saveBatch(list)){
                return RespBean.success("导入成功！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return RespBean.error("导入失败！");
    }