（B站云e办）SpringBoot开发项目实战记录（八）(Easy poi 完成excel导出导入)

本文链接：https://blog.csdn.net/Seven597/article/details/122689505

（B站云e办）SpringBoot开发项目实战记录（八）

一、 pom依赖
二、下载文件
三、导入文件
- 3.1 这里通过改hash值的方式来查外键属性对应的id因为要入库
- 3.2 controller层(具体导入逻辑)

一、 pom依赖

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

二、下载文件

2.1 jopo注释注解@Excel与@ExcelEntry

⭐知识点：
第一种用法：@Excel(name = “合同终止日期”, width = 20, format = “yyyy-MM-dd”)

第二种有外键的：@ExcelEntity(name = “政治面貌”)，外键关联的实体上属性注上@Excel(name=“政治面貌”)

/**
 * <p>
 * 
 * </p>
 *
 * @author seven
 * @since 2022-01-02
 */
@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 = "所属专业")
    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 = "年")
    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")
    @Excel(name = "离职日期", width = 20, format = "yyyy-MM-dd")
    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 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)
    @ExcelEntity(name = "民族")
    private Nation nation;
}

2.2 controller层（完成excel表格的转换与输出到浏览器）

⭐知识点：
注意@GetMapping的注解标注produces属性，不然返回的就是数据流乱码。不是文件

@Autowired
    private IEmployeeService employeeService;

    @ApiOperation(value = "导出员工表")
    @GetMapping(value = "/export/employee", 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.3 service层

/**
     *  查询员工（不传就是查所有）
     * @param id
     * @return
     */
    @Override
    public List<Employee> getEmployee(Integer id) {
        return employeeMapper.getEmployee(id);
    }

2.4 mapper的xml

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

<select id="getEmployee" 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 e,
            t_nation n,
            t_politics_status p,
            t_department d,
            t_joblevel j,
            t_position 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 e.id = #{id}
        </if>
        ORDER BY e.id

    </select>

三、导入文件

3.1 这里通过改hash值的方式来查外键属性对应的id因为要入库

这个PoliticsStatus 是一个外键对应的类
加入传属性对应该类的name，那么@EqualsAndHashCode通过of指定一下计算属性，然后加入有参和无参构造（@RequiredArgsConstructor | @NoArgsConstructor），有参构造name属性指定必传 @NonNull

/**
 * <p>
 * 
 * </p>
 *
 * @author seven
 * @since 2022-01-02
 */
@Data
@RequiredArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false, of = "name")
@Accessors(chain = true)
@TableName("t_politics_status")
@ApiModel(value="PoliticsStatus对象", description="")
public class PoliticsStatus 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;
}

3.2 controller层(具体导入逻辑)

/**
 * 用于处理excel的导出和导入
 */

@Api(tags = "excel导入导出")
@RestController
@RequestMapping("/api")
public class ExcelController {

    @Autowired
    private IEmployeeService employeeService;
    @Autowired
    private INationService nationService;
    @Autowired
    private IPoliticsStatusService politicsStatusService;
    @Autowired
    private IDepartmentService departmentService;
    @Autowired
    private IJoblevelService joblevelService;
    @Autowired
    private IPositionService positionService;

    @ApiOperation(value = "导出员工表")
    @GetMapping(value = "/export/employee", 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();
                }
            }
        }
    }

    @ApiOperation(value = "导入员工表")
    @PostMapping(value = "/import/employee")
    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
                employee.setNationId(nationList.get(nationList.indexOf(new Nation(employee.getNation().getName()))).getId());
                // 政治面貌id
                employee.setNationId(politicsStatusList.get(politicsStatusList.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());
                // 部门id
                employee.setNationId(departmentList.get(departmentList.indexOf(new Department(employee.getDepartment().getName()))).getId());
                // 职称id
                employee.setNationId(joblevelList.get(joblevelList.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());
                // 职位id
                employee.setNationId(positionList.get(positionList.indexOf(new Position(employee.getPosition().getName()))).getId());
            });

            if (employeeService.saveBatch(list)) {
                return RespBean.success("导入员工信息成功!");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return RespBean.warning("导入员工信息失败!");
    }
}