Excal导入

excal导入

• controller层

/**
 * 线索导入
 */
@PostMapping("importLeads/{createId}")
public YyptResponse outExcel(@RequestParam("file") MultipartFile[] file, @PathVariable("createId") Integer createId) {
    if (createId == null) {
        return YyptResponse.failure("导入人不id不能为空!");
    }
    for (MultipartFile multipartFile : file) {
        String s = leadsService.importLeads(multipartFile, createId);
        if (s != null) {
            return YyptResponse.failure("文件名称:"+multipartFile.getOriginalFilename()+"\n"+"出现异常原因:"+s);
        }
    }
    return YyptResponse.success(null);
}

• service层

String importLeads(MultipartFile file,Integer createId);

• serviceImpl层

@Service
@AllArgsConstructor
public class LeadsServiceImpl extends ServiceImpl<LeadsMapper, Leads> implements LeadsService {
    private final WechatExternalContactYtyfMapper wechatExternalContactYtyfMapper;


    @Override
    public String checkPhone(Leads leads) {
        if (leads == null || leads.getCreateId() == null) {
            return  "创建人id不能为空";
        }
        if (StringUtils.isEmpty(leads.getName())) {
            return "用户姓名不能为空";
        }
        if (StringUtils.isEmpty(leads.getPhone())) {
            return "电话不能为空";
        }
        //查询此线索的所对应的电话是否已是线索
        Integer count = this.getBaseMapper().selectCount(Wrappers.<Leads>lambdaQuery().eq(Leads::getPhone,
                leads.getPhone()));
        if (count>0){
            return SysConstants.REPEATED_CLUES;
        }
        //查询此线索的所对应的电话是否已是客户
        //todo 暂时无法确认wechatExternalContactYtyf表中的mobile是否唯一,先用集合取第一条记录
        List<UserDto> userList = wechatExternalContactYtyfMapper.getUserId(leads.getPhone());
        if (userList != null && userList.size() > 0) {
            //代表已经是客户
            UserDto userDto = userList.get(0);
            //因为已经是客户，所以将建立联系业务员跟建立时间存进
            leads.setBuildId(userDto.getUserId());
            leads.setBuildDate(userDto.getCreDate());
            //线索状态改为 成为客户
            leads.setStatus(SysConstants.LEADS_STATUS_SUCCESS);
        }

        //新线索存入
        this.baseMapper.insert(leads);
        return null;
    }

    @Override
    public String importLeads(MultipartFile file,Integer createId){

        ImportParams importParams = new ImportParams();
        //表格标题行数
        importParams.setTitleRows(1);
        //表头行数
        importParams.setHeadRows(1);
        //list集合用来将每一行数据存进一个leads对象
        List<Leads> leadsList = null;
        try {
            //参数1 excal文件的输入流，2 lead对象的class文件 3 importParams对象
            leadsList = ExcelImportUtil.importExcel(file.getInputStream(), Leads.class,importParams);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
        Map<String,String> map=null;
        //将excal表格中的数据存入list集合中，对集合进行遍历
        for (int i = 0; i < leadsList.size(); i++) {
            Leads leads = leadsList.get(i);
            //将createId参数设置到lead对象中（代表此条数据是该创建人导入的）
            leads.setCreateId(createId);
           /*if (leads.getCreTime()!=null){
                try {
                    String format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(sdf.parse(leads.getCreTime()));
                    leads.setCreateTime(LocalDateTime.parse(format,DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
                }catch (Exception e){
                    return "日期格式不正确,位于"+(i+1)+"条";
                }
            }*/
           //代表手机号重复了，该手机号已经是线索
            if (SysConstants.REPEATED_CLUES.equals(checkPhone(leads))) {
                if (map==null){
                    map=new HashMap<>();
                }
                map.put(leads.getName(),leads.getPhone());
            }

        }
        if (map!=null&&map.size()>0){
            return "除已有线索,都已成功导入!\n重复线索列表如下:"+map.toString();
        }
        return null;
    }

• 实体类lead

package com.yypt.scrm.domain;
import java.time.LocalDateTime;
import java.io.Serializable;
import java.util.List;
import java.util.Set;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

/**
 * 潜在客户(线索)
 *
 * @author YangJinChuan
 */
@Data
public class Leads implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId(type = IdType.AUTO)
    private Integer id;
    /**
     * 线索id
     */
    @Excel(name="线索id")
    private String leadsId;

    /**
     * 姓名
     */
    @Excel(name="姓名")
    private String name;

    /**
     * 电话
     */
    @Excel(name="电话")
    private String phone;

    /**
     * 所在地
     */
    @Excel(name="所在地")
    private String address;

    /**
     * 流量来源
     */
    @Excel(name="流量来源")
    private String trafficSource;

    /**
     * 素材内容
     */
    @Excel(name = "素材内容")
    private String content;

    /**
     * 推广链接
     */
    @Excel(name="推广链接")
    private String sponsoredLinks;

    /**
     * 备注
     */
    @Excel(name="备注")
    private String remark;

    /**
     * 创建时间
     */
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    /*
    @Excel(name="创建时间")
    @TableField(exist = false)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private String creTime;
    */

    /**
     * 最后更新时间
     */
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private LocalDateTime updateTime;

    /**
     * 对接人id
     */
    private Integer dockingId;

    /**
     * 建立联系日期
     */
    private LocalDateTime buildDate;

    /**
     * 建立联系业务员
     */
    private Integer buildId ;

    /**
     * 创建人id
     */
    private Integer createId;

    /**
     * 0.初始 1.作废 2.指定成功 3.成为客户
     */
    private Integer status;
    @TableField(exist = false)
    private String dockingName;
    @TableField(exist = false)
    private String buildName;
    @TableField(exist = false)
    private String createName;
    @TableField(exist = false)
    private String roleCodeList;
    @TableField(exist = false)
    private Integer loginId;
    @TableField(exist = false)
    private Set<Integer> userSet;
}