网站导航
> 文档中心 > EasyPOI使用 导出导入Excel数据

EasyPOI使用 导出导入Excel数据

网友: 文档中心 2022-03-28 21:31:13

导入依赖

<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();}}}}

四.测试
EasyPOI使用 导出导入Excel数据

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());//政治面貌idemployee.setPoliticId(politicsStatusList.get(politicsStatusList.indexOf(new PoliticsStatus(employee.getPoliticsStatus().getName()))).getId());//部门idemployee.setDepartmentId(departmentList.get(departmentList.indexOf(new Department(employee.getDepartment().getName()))).getId());//职称idemployee.setJobLevelId(joblevelList.get(joblevelList.indexOf(new Joblevel(employee.getJoblevel().getName()))).getId());//职位idemployee.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("导入失败！");}

测试
在这里插入图片描述

网络标签：

相关问题