[网络协议]EasyExcel导入导出操作

?实体类

@Data
@Builder
@HeadRowHeight(100)
public class EmpExcel {
    @ExcelProperty("人员编号")
    private String empCode;
    @ExcelProperty("姓名")
    private String empName;
    @ExcelProperty("定位卡号")
    private String idCard;
    @ExcelProperty("性别")
    private String gender;

    @JsonFormat(pattern="yyyy-MM-dd",timezone="GMT+8")
    @JSONField(format="yyyy-MM-dd")
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("生日")
    private java.util.Date birth;
    @ExcelProperty("政治面貌")
    private String politicCountenance;
    @ExcelProperty("学历")
    private String education;
    @ExcelProperty("婚姻状况")
    private String maritalStatus;
    @ExcelProperty("手机号")
    private String mobile;
    @ExcelProperty("部门名称")
    private String deptName;
    @ExcelProperty("班次名称")
    private String className;
    @ExcelProperty("岗位名称")
    private String postName;
    @ExcelProperty("职务名称")
    private String dutyName;
    @ExcelProperty("企业卡号")
    private String enterpriseCardno;
    @ExcelProperty("连接卡号")
    private String connnetCardno;
    @ExcelProperty("工种")
    private String empType;
    @ExcelProperty("工作许可证")
    private String workLicense;

    @JsonFormat(pattern="yyyy-MM-dd",timezone="GMT+8")
    @JSONField(format="yyyy-MM-dd")
    @DateTimeFormat("yyyy-MM-dd")
    private java.util.Date workLicenseEffectiveData;
    @ColumnWidth(18)
    private URL photo;

    @TableField(fill = FieldFill.INSERT)
    @ExcelProperty("创建时间")
    private Date createTime;
    @ExcelProperty("pid")
    private String pid;
    @ExcelProperty("是否管理员")
    private String isAdmin;
}

?导出：包括图片导出，以及失败后返回json

 @Autowired
    EmpService empService;
    @Autowired
    WorkPlanService workPlanService;
    @Value("${minio.url}")
    String path;
    @RequestMapping(value = "/emp/download",method = RequestMethod.GET)
    public void downloadExcel(HttpServletResponse response) throws IOException {
        try {
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmm");

            List<CmemEmp> list = empService.list(new QueryWrapper<>());
            List<EmpExcel> empExcelList = new ArrayList<>();
            for (CmemEmp emp : list) {
                EmpExcel empExcel = EmpExcel.builder()
                        .empCode(emp.getEmpCode())
                        .empName(emp.getEmpName())
                        .idCard(emp.getIdCard())
                        .gender(emp.getGender().equals(1) ? "男" : "女")
                        //.birth(emp.getBirth())
                        //.politicCountenance(emp.getPoliticCountenance())
                        .mobile(emp.getMobile())
                        //.deptName(emp.getDeptName())
                        //.className(emp.getClassName())
                        // .postName(emp.getPostName())
                        //.dutyName(emp.getDutyName())
                        .enterpriseCardno(emp.getEnterpriseCardno())
                        .connnetCardno(emp.getConnnetCardno())
                        // .empType(emp.getEmpType())
                        // .workLicense(emp.getWorkLicense())
                        //.workLicenseEffectiveData(emp.getWorkLicenseEffectiveData())
                        // .photo(new URL(path+"/"+emp.getPhoto()))
                        .pid(System.currentTimeMillis() + "")
                        .createTime(new Date())
                        .isAdmin(emp.getIsAdmin() + "").build();

                empExcelList.add(empExcel);
            }
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = format.format(new Date()) + URLEncoder.encode("档案数据", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

            EasyExcel.write(response.getOutputStream(), EmpExcel.class).sheet("模板").doWrite(empExcelList);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

导入：需要new 监听器时，将DAO 或者Service传入，否则会空指针，楼主亲测

package com.jylink.cmem.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.jylink.cmem.entity.CmemEmp;
import com.jylink.cmem.service.EmpService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;

// 有个很重要的点 OrderDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class EmpDataListener extends AnalysisEventListener<CmemEmp> {
    private static final Logger LOGGER = LoggerFactory.getLogger(EmpDataListener.class);

    private static final int BATCH_COUNT = 5;
    List<CmemEmp> list = new ArrayList<>();
   public Map<String,String> map = new HashMap<>();

    private EmpService empService;


    public EmpDataListener(EmpService empService) {
        this.empService = empService;
    }
    public EmpDataListener(){

    }

    @Override
    public void invoke(CmemEmp data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        if(data.getEmpCode()==null || data.getEmpCode()==""){
            map.put("code","400");
            map.put("msg","表中有未填写的人员编号");
            return;
        }
        if(data.getEmpName().length()>30){
            map.put("code","400");
            map.put("msg","人员姓名值过多");
            return;
        }
        if(data.getIdCard().length()>18){
            map.put("code","400");
            map.put("msg","卡号值过多");
            return;
        }
        if(!"1".equals(data.getGender())&&!"0".equals(data.getGender())&&data.getGender()!=null){
            map.put("code","400");
            map.put("msg","性别格式有误，请参照以下格式 男：1   女：0");
            return;
        }
        if (data.getMobile() ==null || data.getMobile()=="" ){
            map.put("code","400");
            map.put("msg","表中有未填写的手机号");
            return;
        }if(data.getIsAdmin()==null || data.getMobile()==""){
            map.put("code","400");
            map.put("msg","是否管理员未填写");
            return;
        }
        data.setPid(System.currentTimeMillis());
        data.setCreateTime(new Date());
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        empService.saveBatch(list);
        LOGGER.info("存储数据库成功！");
    }
}

Crotroller:

  @PostMapping("/emp/upload")
    @ResponseBody
    public JSONObject empUpload(MultipartFile file) throws IOException {
        JSONObject jsonObject = new JSONObject();
        EmpDataListener empDataListener = new EmpDataListener(empService);
        EasyExcel.read(file.getInputStream(), CmemEmp.class, empDataListener).sheet().doRead();
        Map<String, String> map = empDataListener.map;
        try{
        if (map.size()!=0){
            jsonObject.put("code",map.get("code"));
            jsonObject.put("msg",map.get("msg"));
            return jsonObject;
        }
        }catch (Exception e){
            e.printStackTrace();
        }
        jsonObject.put("code",200);
        jsonObject.put("msg","导入成功");
        return jsonObject;
    }

导入导出都完成了，楼主小白，欢迎讨论