以下内容都是我在学习过程中学到的知识，都是根据自己的想法写的。
一般流程如下：
一、创建实体类，注意导入模板实体模型类（CustomManageAndCustomerInformationReadVO），一个是该模块的主表实体（CustomManage），只有模块主表实体才能连接到数据库的数据，而模板实体模型类是导入所需的字段，哪个字段在哪个列从0开始。
二、在controller层调用ExcelUtils工具类下的customerInformationReadExcel()方法。 三、进入ExcelUtils工具类编写customerInformationReadExcel()方法需要调用CustomManageListener()监听器也可以称为分析器。
第四，导入的难点在于监听器的编写。如果导入的数据属于一张表，那就不难了。关键是我这次做的是多表，需要连表操作，调用其他表。mapper和service。如果你做不到，你会感到困难。这样做并不是特别困难，就像地上有更多的人没有路可走一样。监听器调用了公共枚举中文到数字的方法。
五、进入公共枚举类，编写中文转数字的getEnumNum()实现中文数字获取和导入的方法excel将数据导入数据库)。
1.创建实体类，注意导入模板实体模型类（CustomManageAndCustomerInformationReadVO），一个是该模块的主表实体（CustomManage），只有模块主表实体才能连接到数据库的数据，而模板实体模型类是导入所需的字段，哪个字段在哪个列从0开始。
package com.theiavis.workcloud.project.sale.bean; import java.io.Serializable; import java.util.Date; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import com.fasterxml.jackson.annotation.JsonFormat; import com.fasterxml.jackson.annotation.JsonIgnoreProperties; import com.theiavis.workcloud.common.entity.BaseEntity; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.*; import lombok.experimental.Accessors; import org.springframework.web.bind.annotation.ModelAttribute; /** * custom_manage : */ @Data @Accessors(chain=true) @AllArgsConstructor @NoArgsConstructor @ApiModel("客户管理对象") public class CustomManage extends BaseEntity implements Serializable { private static final long serialVersionUID = 1L; /** * 主键 : id, */ @ExcelIgnore @TableId(value = "id", type = IdType.AUTO) private Integer id; /** * sale_time, 销售时间,kpi关联，需拆分-年 */ @ExcelIgnore @ExcelProperty(value = {"年份"}, index = 0) @ApiModelProperty(value = "销售时间-年", required = true) private String saleTimeYear; /** * sale_time, 销售时间,kpi关联，需拆分-月 */ @ExcelIgnore @ExcelProperty(value = {"所属月份"}, index = 1) @ApiModelProperty(value = "销售时间-月", required = true) private String saleTimeMonth; /** * proj_num, project-》projo_num，创建时间与项目表相关 */ @ExcelIgnore @ExcelProperty(value = {"创建时间与项目表相关"}, index = 2) @ApiModelProperty(value = "创建时间与项目表相关", required = true) private String projNum; /** * follow_id, 跟进人标识user->user_id */ @ExcelIgnore @ExcelProperty(value = {"跟进人标识user->user_id"}, index = 3) @ApiModelProperty(value = "跟进人标识user->user_id", required = true) private String followId; /** * create_id, 创建人标识user->user_id */ @ExcelIgnore @ExcelProperty(value = {"创建人标识user->user_id"}, index = 4) @ApiModelProperty(value = "创建人标识user->user_id", required = true) private String createId; /** * company, 对接公司名称 */ @ExcelIgnore @ExcelProperty(value = {"公司名称"}, index = 5) @ApiModelProperty(value = "对接公司名称", required = true) private String company; /** * custom_type, 客户类型(0) */ @ExcelIgnore @ExcelProperty(value = {"客户类型(0)"}, index = 6) @ApiModelProperty(value = "客户类型(0)", required = true) private String customType; /** * city, 客户所在城市 */ @ExcelIgnore @ExcelProperty(value = {"客户所在城市"}, index = 7) @ApiModelProperty(value = "客户所在城市", required = true) private String city; /** * custom_department, 客户所在部门 */ @ExcelIgnore @ExcelProperty(value = {"客户所在部门"}, index = 8) @ApiModelProperty(value = "客户所在部门", required = true) private String customDepartment; /** * contact, 联系人姓名(全称) */ @ExcelIgnore @ExcelProperty(value = {"联系人姓名(全称)"}, index = 9) @ApiModelProperty(value = "联系人姓名(全称)", required = true) private String contact; /** * mobile, 联系人电话 */ @ExcelIgnore @ExcelProperty(value = {"联系人电话"}, index = 10) @ApiModelProperty(value = "联系人电话", required = true) private String mobile; /** * email, 联系人邮箱 */ @ExcelIgnore @ExcelProperty(value = {"联系人邮箱"}, index = 11) @ApiModelProperty(value = "联系人邮箱", required = true) private String email; /** * custom_source, 新旧客户类型(0.移交客户1.新客户) */ @ExcelIgnore @ExcelProperty(value = {"新旧客户类型(0.移交客户1.新客户)"}, index = 12) @ApiModelProperty(value = "新旧客户类型(0.移交客户1.新客户)", required = true) private String customSource; /** * custom_id, 客户id */ @ExcelIgnore @ExcelProperty(value = {"客户id"}, index = 13) @ApiModelProperty(value = "客户id", required = false) private String customId; /** * permit_code, 权限码 */
@ExcelIgnore
@ExcelProperty(value = {"权限码"}, index = 14)
@ApiModelProperty(value = "权限码", required = true)
private String permitCode;
/**
* dept_id, 部门id
*/
@ExcelIgnore
// @ExcelProperty(value = {"部门id"}, index = 15)
@ApiModelProperty(value = "部门id", required = true)
private Integer deptId;
/**
* agreement_id, proj_agreement->id合同标识
*/
@ExcelIgnore
// @ExcelProperty(value = {"id合同标识"}, index = 16)
@ApiModelProperty(value = "id合同标识", required = false)
private Integer agreementId;
/**
* creat_Time, create_Time创建时间
*/
@ExcelIgnore
@ExcelProperty(value = {"create_Time创建时间"}, index = 17)
@ApiModelProperty(value = "create_Time创建时间", required = false)
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date createTime;
/**
* is_delete, 是否删除
*/
@ExcelIgnore
@ExcelProperty(value = {"是否删除0.否1.是"}, index = 18)
@ApiModelProperty(value = "是否删除0.否1.是", required = true)
private String isDelete;
}package com.theiavis.workcloud.project.sale.vo;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import java.util.Date;
/**
* 根据"客户信息导入模板"批量导入
* @author xql
*/
@Data
//@Accessors(chain=true)
@AllArgsConstructor
@NoArgsConstructor
public class CustomManageAndCustomerInformationReadVO {
/**
* stage, 商机寻找（0.商机线索，1.在谈项目 2.签约项目 3.已归档项目4.前期介入5.项目报价6.关键人物接触7.方案汇报8.方案阶段9.投标阶段10.合同阶段11.项目暂停）
*/
@ApiModelProperty(value = "商机寻找（0.商机线索，1.在谈项目 2.签约项目 3.已归档项目4.前期介入5.项目报价6.关键人物接触7.方案汇报8.方案阶段9.投标阶段10.合同阶段11.项目暂停）", required = true)
@ExcelProperty(value = "所属阶段", index = 0)
private String stage;
/**
* sale_time, 销售时间,kpi关联，需拆分-年
*/
@ExcelProperty(value = "年份", index = 1)
private String saleTimeYear;
/**
* sale_time, 销售时间,kpi关联，需拆分-月
*/
@ExcelProperty(value = "所属月份", index = 2)
private String saleTimeMonth;
/**
* 客户来源
*/
@ExcelProperty(value = "客户来源", index = 3)
@TableField(exist = false)
@ApiModelProperty(value = "客户来源", required = true)
private String source;
/**
* company, 对接公司名称
*/
@ExcelProperty(value = "公司名称", index = 4)
private String company;
/**
* 项目名 项目名称
*/
@ExcelProperty(value = "项目名称", index = 5)
private String projName;
/**
* 项目地址
*/
@ExcelProperty(value = "项目地址", index = 6)
@TableField(exist = false)
@ApiModelProperty(value = "项目地址", required = true)
private String address;
/**
* 项目类型
*/
@ExcelProperty(value = "项目类型", index = 7)
@TableField(exist = false)
@ApiModelProperty(value = "项目类型（项目类型（0、数字大屏1、数字沙盘2、数字展项3、数字展厅4、CIM 5、VRXR 6：其他）", required = true)
private String projectType;
/**
* custom_type, 客户类型（0、商业公司1、政府2、地产商3、规划院）
*/
@ExcelProperty(value = "客户类型", index = 8)
@ApiModelProperty(value = "客户类型（0、商业公司1、政府2、地产商3、规划院）", required = true)
private String customType;
/**
* city, 客户所在城市
*/
@ExcelProperty(value = {"客户所在城市"}, index = 9)
@ApiModelProperty(value = "客户所在城市", required = true)
private String city;
/**
* custom_department, 客户所在部门
*/
@ExcelProperty(value = {"客户所属部门"}, index = 10)
@ApiModelProperty(value = "客户所在部门", required = true)
private String customDepartment;
/**
* contact, 联系人名字(全称)
*/
@ExcelProperty(value = "联系人", index = 11)
private String contact;
/**
* email, 联系人邮箱
*/
@ExcelProperty(value = {"联系人邮箱"}, index = 12)
@ApiModelProperty(value = "联系人邮箱", required = true)
private String email;
/**
* mobile, 联系人电话
*/
@ExcelProperty(value = "联系人电话", index = 13)
private String mobile;
/**
* follow_id, 跟进人名称
*/
@ExcelProperty(value = "跟进人", index = 14)
private String followName;
/**
* 跟进结果
*/
@ExcelProperty(value = "跟进结果", index = 15)
@TableField(exist = false)
@ApiModelProperty(value = "跟进结果", required = true)
private String followResult;
/**
* 失败原因
*/
@ExcelProperty(value = "失败原因", index = 16)
@TableField(exist = false)
@ApiModelProperty(value = "失败原因", required = true)
private String followFailResult;
/**
* 计划开始时间
*/
@TableField(exist = false)
@ExcelProperty(value = "计划开始时间(yyyy/mm/dd)", index = 17)
@JsonIgnore
private Date planStartTime;
/**
* proj_need, 项目需求
*/
@TableField(exist = false)
@ExcelProperty(value = "项目需求", index = 18)
@ApiModelProperty(value = "项目需求", required = true)
private String projNeed;
/**
* 计划结束时间
*/
@TableField(exist = false)
@ExcelProperty(value = "计划结束时间(yyyy/mm/dd)", index = 19)
@JsonIgnore
private Date planEndTime;
/**
* custom_source, 新旧客户类型(0.移交客户1.新客户)
*/
@TableField(exist = false)
@ExcelProperty(value = "新旧客户类型", index = 20)
private String customSource;
/**
* product_count, 产品数量
*/
@TableField(exist = false)
@ExcelProperty(value = "产品数量", index = 21)
@ApiModelProperty(value = "产品数量", required = true)
private String productCount;
/**
* contract_cash, 预计合同金额
*/
@TableField(exist = false)
@ExcelProperty(value = "预计合同金额(万元)", index = 22)
@ApiModelProperty(value = "预计合同金额", required = true)
private String contractCash;
/**
* place_contract, 是否签订合同(0.未签约1.已签约)
*/
@TableField(exist = false)
@ExcelProperty(value = "是否签订保密协议", index = 23)
@ApiModelProperty(value = "是否签订保密协议(0.未签约1.已签约)", required = true)
private String placeContract;
/**
* effect_pj, 是否当地影响力(0.否1.是)
*/
@TableField(exist = false)
@ExcelProperty(value = "是否当地影响力", index = 24)
@ApiModelProperty(value = "是否当地影响力(0.否1.是)", required = true)
private String effectPj;
/**
* 项目进度
*/
@TableField(exist = false)
@ExcelProperty(value = "项目进度",index = 25)
@ApiModelProperty(value = "项目进度(0:前期介入1:项目报价2:关键人物接触3:方案制作阶段4:方案汇报5:DEMO制作6:投标阶段7:签约阶段8:项目站厅)", required = true)
private String projProgress;
/**
* star, 项目星级（数字标识1-5）
*/
@TableField(exist = false)
@ExcelProperty(value = "项目靠谱程度",index = 26)
@ApiModelProperty(value = "项目靠谱程度", required = true)
private String star;
/**
* contract_cash_count, 合同金额
*/
@TableField(exist = false)
@ExcelProperty(value = "合同金额(万元)",index = 27)
@ApiModelProperty(value = "合同金额", required = true)
private String contractCashCount;
/**
* realcontract_cash, 合同实际金额
*/
@TableField(exist = false)
@ExcelProperty(value = "合同实际金额(万元)",index = 28)
@ApiModelProperty(value = "合同实际金额", required = true)
private String realcontractCash;
/**
* contract_mode, 合同模式（0.分期付款1.背靠背式2:赊账）
*/
@TableField(exist = false)
@ExcelProperty(value = "合同模式",index = 29)
@ApiModelProperty(value = "合同模式（0.分期付款1.背靠背式2:赊账）", required = true)
private String contractMode;
/**
* intexamine_time, 初验收时间
*/
@TableField(exist = false)
@ExcelProperty(value = "初验收时间(yyyy/mm/dd)",index =30)
@ApiModelProperty(value = "初验收时间", required = true)
private Date intexamineTime;
/**
* init_cash, 初步收入金额
*/
@TableField(exist = false)
@ExcelProperty(value = "初步收入金额(万元)",index =31)
@ApiModelProperty(value = "初步收入金额", required = true)
private String initCash;
/**
* endexamine_time, 最终验收时间
*/
@TableField(exist = false)
@ExcelProperty(value = "最终验收时间(yyyy/mm/dd)",index =32)
@ApiModelProperty(value = "最终验收时间", required = true)
private Date endexamineTime;
/**
* end_cash, 最终金额
*/
@TableField(exist = false)
@ExcelProperty(value = "最终金额(万元)",index =33)
@ApiModelProperty(value = "最终金额", required = true)
private String endCash;
/**
* receive_cash_time, 收款最终时间
*/
@TableField(exist = false)
@ExcelProperty(value = "收款日期(yyyy/mm/dd)",index =34)
@ApiModelProperty(value = "收款最终时间", required = true)
private Date receiveCashTime;
/**
* contract_deposit, 合同预付款(定金)
*/
@TableField(exist = false)
@ExcelProperty(value = "预付款(万元)",index =35)
@ApiModelProperty(value = "合同预付款(定金)", required = true)
private String contractDeposit;
/**
* proj_state, 项目状态(0项目进行中/1暂停项目/2催款中/3已完成需催款/4已清项目)
*/
@TableField(exist = false)
@ExcelProperty(value = "项目状态",index =36)
@ApiModelProperty(value = "项目状态(0项目进行中/1暂停项目/2催款中/3已完成需催款/4已清项目)", required = true)
private String projState;
/**
* back_cash, 回款跟催状态(1.催款中2.已完成催款3.已清项目4.异常5.拖款)
*/
@TableField(exist = false)
@ExcelProperty(value = "回款跟催阶段",index =37)
@ApiModelProperty(value = "回款跟催状态(0. 正在催款 1. 常规（预计三个月内收款 ） 2. 异常（预计超半年催款） 3.拖款（预计3~6个月回款）)", required = true)
private String backCash;
/**
* market_commision, 市场提成系数
*/
@TableField(exist = false)
@ExcelProperty(value = "市场提成系数",index =38)
@ApiModelProperty(value = "市场提成系数", required = true)
private String marketCommision;
/**
* commision_month, 市场提成月数
*/
@TableField(exist = false)
@ExcelProperty(value = "市场提成月份",index =39)
@ApiModelProperty(value = "市场提成月数", required = true)
private String commisionMonth;
/**
* feedback, 具体反馈(0.成果质量1.服务态度2.过程技术配合3.时间配合4.交付时间)
*/
@TableField(exist = false)
@ExcelProperty(value = "具体反馈",index =40)
@ApiModelProperty(value = "具体反馈(0.成果质量1.服务态度2.过程技术配合3.时间配合4.交付时间)", required = true)
private String feedback;
/**
* remark, 备注(项目注意事宜)
*/
@TableField(exist = false)
@ExcelProperty(value = "备注",index =41)
@ApiModelProperty(value = "备注(项目注意事宜)", required = true)
private String remark;
/**
* detail_need,详细需求
*/
@TableField(exist = false)
@ExcelProperty(value = "详细需求",index =42)
@ApiModelProperty(value = "详细需求", required = true)
private String detailNeed;
}
2.在controller层调用ExcelUtils工具类下的customerInformationReadExcel()方法。
/**
* 根据客户信息导入模板批量导入
*
* @param
* @return
* @throws Exception
*/
@PreAuthorize("hasPermit('sale:client')")
@RequestMapping(value = "/customerInformationReadExcel", method = RequestMethod.POST, produces = "application/octet-stream")
@ApiOperation(value = "客户信息导入(按照模板导入)", tags = {"销售模块", "客户信息导入(按照模板导入)"})
public Result customerInformationReadExcel(@RequestPart @RequestParam(value = "file", required = false) MultipartFile file)
throws Exception {
try {
ExcelUtils.customerInformationReadExcel(file.getInputStream());
return Result.success(ResultCode.成功, ResultCode.成功.getCode());
} catch (Exception e) {
return Result.error(ResultCode.失败.getMsg(), ResultCode.失败.getCode(), null);
}
}3.进入ExcelUtils工具类编写customerInformationReadExcel()方法，其中需要调用到CustomManageListener()监听器也可称之为解析器。
/**
* 根据客户信息导入模板批量导入
* @param inputStream
*/
public static void customerInformationReadExcel(InputStream inputStream) {
EasyExcel.read(inputStream, CustomManageAndCustomerInformationReadVO.class,new CustomManageListener()).registerConverter(new LocalDateTimeConverter()).sheet().doRead();
}4.导入的难点就在于监听器的编写，如若导入的数据属于一张表还不难，重点是我这次做的是多表的，需要进行连表操作，调用其他表的mapper和service。不会做就觉得难。做了觉得也还行不是特别难，就像地上本没有路走的人多了也就成了路。监听器中调用了公共枚举中文转数字的方法。
package com.theiavis.workcloud.project.sale.enums;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.StringUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.theiavis.security.jwt.UserOperator;
import com.theiavis.workcloud.project.agreement.bean.ProjAgreement;
import com.theiavis.workcloud.project.agreement.mapper.ProjAgreementMapper;
import com.theiavis.workcloud.project.agreement.service.IProjAgreementService;
import com.theiavis.workcloud.project.dingtalk.bean.User;
import com.theiavis.workcloud.project.dingtalk.mapper.UserMapper;
import com.theiavis.workcloud.project.proj.VO.proj.ProjIdOutputVo;
import com.theiavis.workcloud.project.proj.VO.proj.ProjInsertVO;
import com.theiavis.workcloud.project.proj.bean.Project;
import com.theiavis.workcloud.project.proj.mapper.ProjectMapper;
import com.theiavis.workcloud.project.proj.service.ProjectService;
import com.theiavis.workcloud.project.sale.bean.CustomManage;
import com.theiavis.workcloud.project.sale.mapper.CustomManageMapper;
import com.theiavis.workcloud.project.sale.service.ICustomManageService;
import com.theiavis.workcloud.project.sale.vo.CustomManageAndCustomerExportVO;
import com.theiavis.workcloud.project.sale.vo.CustomManageAndCustomerInformationReadVO;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.PostConstruct;
import java.util.List;
import java.util.logging.Logger;
@Slf4j
@Component
public class CustomManageListener implements ReadListener<CustomManageAndCustomerInformationReadVO> {
private static final Logger logger = Logger.getLogger(CustomManageListener.class.getName());
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<CustomManageAndCustomerInformationReadVO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
@Autowired
ICustomManageService iCustomManageService;//相当于注册到spring的一个bean
@Autowired
UserOperator userOperator;
@Autowired
UserMapper userMapper;//相当于注册到spring的一个bean
@Autowired
IProjAgreementService iProjAgreementService;//相当于注册到spring的一个bean
@Autowired
ProjectService projectService;//相当于注册到spring的一个bean
//把他自身的类实例化，并定义成静态类
public static CustomManageListener customManageListener;
@PostConstruct
public void init(){//当前指针去指向这个类的对象，因为spring容器里面有相同的对象
customManageListener =this;
}
/**
* 这个每一条数据解析都会来调用
*
* @param customManageAndCustomerInformationReadVO one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param analysisContext
*/
@Transactional(rollbackFor = Exception.class)
@Override
public void invoke(CustomManageAndCustomerInformationReadVO customManageAndCustomerInformationReadVO, AnalysisContext analysisContext) {
logger.info("解析到一条数据:{}");
cachedDataList.add(customManageAndCustomerInformationReadVO);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Transactional(rollbackFor = Exception.class)
public void saveData() {
cachedDataList.stream().forEach(i->{
logger.info("{}条数据，开始存储数据库！");
try {
// String 数字=StatusEnum.getEnumNum("错误","Source");
// User createId=customManageListener.userMapper.selectOne(new QueryWrapper<User>().groupBy("user_id").like("name",i.getCreaterName()));
//查询出的结果绝对有一个用selectOne()方法(如若有多条数据就会报错)，QueryWrapper<User>()方法是MybatisPlus函数的用法，实现查询的对象封装操作类。groupBy()分组，like()模糊查询。根据followId(也就是user_id)查询对应的followName。主表中没有followName，只有followId，所以在user表(userMapper)中查询对应的name。
User followId=customManageListener.userMapper.selectOne(new QueryWrapper<User>().groupBy("user_id").like("name",i.getFollowName()));
//链式编程。数据存在主表(custom_manage)中
CustomManage customManage=new CustomManage().setFollowId(followId.getUserId()!=null?followId.getUserId():null).setSaleTimeYear(i.getSaleTimeYear()).setSaleTimeMonth(i.getSaleTimeMonth()).setCompany(i.getCompany()).setCustomType(StatusEnum.getEnumNum(i.getCustomType(),"CustomType")).setCity(i.getCity()).setCustomDepartment(i.getCustomDepartment()).setContact(i.getContact()).setEmail(i.getEmail()).setMobile(i.getMobile()).setCustomSource(StatusEnum.getEnumNum(i.getCustomSource(),"CustomSource"));
if (StringUtils.isNotBlank(i.getProjName())){
//数据存储在project次表中
ProjIdOutputVo projIdOutputVo =customManageListener.projectService.insertProj(new ProjInsertVO().setProjName(i.getProjName()));
//数据存储在proj_agreement次表中
int projAgreementId=customManageListener.iProjAgreementService.addProjAgreement(new ProjAgreement().setStage(StatusEnum.getEnumNum(i.getStage(),"Stage")).setSource(StatusEnum.getEnumNum( i.getSource(),"Source")).setAddress(i.getAddress()).setProjectType(StatusEnum.getEnumNum( i.getProjectType(),"ProjectType")).setFollowResult(StatusEnum.getEnumNum( i.getFollowResult(),"FollowResult")).setFollowFailResult(i.getFollowFailResult()).setPlanStartTime(i.getPlanStartTime()).setProjNeed(i.getProjNeed()).setPlanEndTime(i.getPlanEndTime()).setProductCount(i.getProductCount()).setContractCash(i.getContractCash()).setPlaceContract(StatusEnum.getEnumNum(i.getPlaceContract(),"PlaceContract")).setEffectPj(StatusEnum.getEnumNum(i.getEffectPj(),"EffectPj")).setProjProgress(StatusEnum.getEnumNum(i.getProjProgress(),"ProjProgress")).setStar(i.getStar()).setContractCashCount(i.getContractCashCount()).setRealcontractCash(i.getRealcontractCash()).setContractMode(StatusEnum.getEnumNum(i.getContractMode(),"ContractMode")).setIntexamineTime(i.getIntexamineTime()).setInitCash(i.getInitCash()).setEndexamineTime(i.getEndexamineTime()).setEndCash(i.getEndCash()).setReceiveCashTime(i.getReceiveCashTime()).setContractDeposit(i.getContractDeposit()).setProjState(StatusEnum.getEnumNum(i.getProjState(),"ProjState")).setBackCash(StatusEnum.getEnumNum(i.getBackCash(),"BackCash")).setMarketCommision(i.getMarketCommision()).setCommisionMonth(i.getCommisionMonth()).setFeedback(StatusEnum.getEnumNum(i.getFeedback(),"Feedback")).setRemark(i.getRemark()).setDetailNeed(i.getDetailNeed()));
customManageListener.iCustomManageService.addCustomManage(customManage.setProjNum(projIdOutputVo.getProjNum()).setAgreementId(projAgreementId));//指定到静态的Usermapper注入的对象
}
} catch (Exception e) {
e.printStackTrace();
}
});
logger.info("存储数据库成功！");
}
/**
* 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*
// * @param exception
// * @param context
* @throws Exception
*/
// @Override
// public void onException(Exception exception, AnalysisContext context) {
// log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
// // 如果是某一个单元格的转换异常 能获取到具体行号
// // 如果要获取头的信息 配合invokeHeadMap使用
// if (exception instanceof ExcelDataConvertException) {
// ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
// log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
// excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
// }
// }
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
logger.info("所有数据解析完成！");
}
}
5.进入公共枚举类，编写中文转数字的getEnumNum()方法,实现中文获取数字,导入，(从excel中导入数据至数据库)。
package com.theiavis.workcloud.project.sale.enums;
import com.baomidou.mybatisplus.annotation.EnumValue;
import com.fasterxml.jackson.annotation.JsonValue;
import lombok.Getter;
import lombok.NoArgsConstructor;
/**
*
* @author lyh and xql
*/
@NoArgsConstructor
@Getter
public enum StatusEnum {
/**
* 客户来源（页面名称从0开始） 项目信息来源（数据库名称从1开始）
*/
信息("Source","0"),
公司转交("Source","1"),
自我开拓("Source","2"),
合作客户转介绍("Source","3"),
丝路分公司("Source","4"),
展会信息("Source","5"),
协会资源转换("Source","6"),
其他渠道("Source","7"),
/**
* 项目类型 页面字段与数据库不符
*/
数字大屏("ProjectType","0"),
数字沙盘("ProjectType","1"),
数字展项("ProjectType","2"),
数字展厅("ProjectType","3"),
CIM("ProjectType","4"),
VRXR("ProjectType","5"),
其他("ProjectType","6"),
/**
* 客户类型
*/
商业公司("CustomType","0"),
政府("CustomType","1"),
地产商("CustomType","2"),
规划院("CustomType","3"),
/**
* 跟进结果
*/
成功("FollowResult","0"),
失败("FollowResult","1"),
跟进中("FollowResult","2"),
/**
* 新旧客户类型
*/
移交客户("CustomSource","0"),
新客户("CustomSource","1"),
/**
* 是否签订保密协议
*/
未签约("PlaceContract","0"),
已签约("PlaceContract","1"),
/**
* 是否当地影响力
*/
否("EffectPj","0"),
是("EffectPj","1"),
/**
* 项目进度
*/
前期介入("ProjProgress","0"),
项目报价("ProjProgress","1"),
关键人物接触("ProjProgress","2"),
方案制作阶段("ProjProgress","3"),
方案汇报("ProjProgress","4"),
DEMO制作("ProjProgress","5"),
投标阶段("ProjProgress","6"),
签约阶段("ProjProgress","7"),
/**
* 合同模式
*/
背靠背("ContractMode","0"),
分期付款("ContractMode","1"),
赊销("ContractMode","2"),
/**
* 项目状态
*/
项目进行中("ProjState","0"),
暂停项目("ProjState","1"),
催款中("ProjState","2"),
已完成需催款("ProjState","3"),
已清项目("ProjState","4"),
/**
* 回款跟催阶段
*/
正在催款("BackCash","0"),
常规("BackCash","1"),
异常("BackCash","2"),
拖款("BackCash","3"),
/**
* 具体反馈
*/
成果质量("Feedback","0"),
服务态度("Feedback","1"),
过程技术配合("Feedback","2"),
时间配合("Feedback","3"),
交付时间("Feedback","4"),
/**
* 所处阶段,所属阶段
*/
商机线索("Stage","0"),
在谈项目("Stage","1"),
签约项目("Stage","2"),
已归档("Stage","3"),
/**
* 状态
*/
新领取("Status","0"),
占用("Status","1"),
新归还("Status","2"),
空闲("Status","3"),
接收中("Status","4"),
/**
* 资产类型0笔记本1显示器2ipad3AR/VA设备4相机5鼠标6其他
*/
笔记本("Type","0"),
显示器("Type","1"),
ipad("Type","2"),
AR或VA设备("Type","3"),
相机("Type","4"),
鼠标("Type","5"),
其他设备("Type","6"),
/**
* 申报视图的申报状态
*/
未申报("DeclareStatus","0"),
已申报("DeclareStatus","1"),
;
/**
* 字段 (英文)
*/
@EnumValue
private String key;
/**
* 属性 (数字)
*/
@JsonValue
private String value;
StatusEnum(String key, String value) {
this.value = value;
this.key = key;
}
public String getKey() {
return key;
}
public String getValue() {
return value;
}
/**
* 数字获取中文，导出，从数据库中导出数据至excel
* @param key 英文，对应bean的字段名
* @param value 数字，对应数据库的0123的标识注释
* @return
*/
public static String getEnum(String key,String value){
//用数组存放这些属性（也就是枚举的值）
StatusEnum[] applicationStateEnums = values();
//循环判断key和value是否相等，相等就返回枚举中对应的中文name
for (StatusEnum itemEnum : applicationStateEnums) {
if (itemEnum.key.equals(key) && itemEnum.value.equals(value)) {
if (key.equals("Source")&&value!=null){
return itemEnum.name().replace("信息","400信息");
}else{
return itemEnum.name();
}
}
}
return null;
}
/**
* 中文获取数字,导入，从excel中导入数据至数据库中
* @param name 中文,枚举中有定义，对应模板的下拉选项
* @param key 英文,对应bean的字段名
* @return
*/
public static String getEnumNum(String name,String key){
//用数组存放这些属性（也就是枚举的值）
StatusEnum[] applicationStateEnums = values();
//循环判断name和key是否相等，相等就返回枚举中对应的数字value
for (StatusEnum itemEnum : applicationStateEnums) {
if (key.equals("Source")&& name.equals("400信息")){
return "0";
}
if (itemEnum.name().equals(name)&& itemEnum.key.equals(key)) {
return itemEnum.value;
}
}
return null;
}
}
6.进入主表的mappe进行连表操作，需要用到的字段都要在前面标明是哪个表里的什么字段as别名是什么,进行连表的字段还需在主表也就是本模块的实体类中标注注解此字段不在本表中，不然导出的数据会出现有些字段无数据。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.theiavis.workcloud.project.sale.mapper.CustomManageMapper">
<!-- 通用设置 -->
<!-- 通用查询列 -->
<sql id="Base_Column_List">
a.id
,a.sale_time_year
,a.sale_time_month
,a.proj_num
,a.follow_id
,a.create_id
,a.company
,a.custom_type
,a.city
,a.custom_department
,a.contact
,a.mobile
,a.email
,a.custom_source
,a.custom_id
,a.permit_code
,a.dept_id
,a.agreement_id
,a.create_time
,a.is_delete
</sql>
<select id="findCustomManageByAll" resultType="com.theiavis.workcloud.project.sale.vo.CustomManageAndProjectVO">
SELECT
<include refid="Base_Column_List"/>,
a.custom_type as CustomType,
b.proj_name,
f.name as follow_name,
d.name as creater_name,
c.stage,
c.source as Source,
c.address,
c.project_type,
c.follow_result,
c.follow_fail_result,
c.plan_start_time,
c.proj_need,
c.plan_end_time,
c.product_count,
c.contract_cash,
c.place_contract,
c.effect_pj,
c.proj_progress,
c.star,
c.contract_cash_count,
c.realcontract_cash,
c.contract_mode,
c.intexamine_time,
c.init_cash,
c.endexamine_time,
c.end_cash,
c.receive_cash_time,
c.contract_deposit,
c.proj_state,
c.back_cash,
c.market_commision,
c.commision_month,
c.feedback,
c.remark,
c.detail_need,
d.avatar as creater_avatar,
f.avatar as follow_avatar
<include refid="Condition"/>
</select>
<sql id="Condition">
FROM custom_manage AS a
LEFT JOIN project b On b.proj_num=a.proj_num
LEFT JOIN proj_agreement c ON c.id =a.agreement_id
LEFT JOIN user d On d.user_id=a.create_id
LEFT JOIN user f On f.user_id=a.follow_id
where
<if test="customManage!=null and customManage.id !=null and customManage.id !=''">a.id =#{customManage.id}
and
</if>
<if test="customManage!=null and customManage.stage !=null and customManage.stage !=''">c.stage like
CONCAT('%',#{customManage.stage}, '%') and
</if>
<if test="customManage!=null and customManage.company !=null and customManage.company !=''">a.company like
CONCAT('%',#{customManage.company}, '%') or
</if>
<if test="customManage!=null and customManage.projName !=null and customManage.projName !=''">b.proj_name like
CONCAT('%',#{customManage.projName}, '%') and
</if>
<if test="customManage!=null and customManage.saleTimeYear !=null and customManage.saleTimeYear !=''">a.sale_time_year like
CONCAT('%',#{customManage.saleTimeYear}, '%') and
</if>
<if test="customManage!=null and customManage.saleTimeMonth !=null and customManage.saleTimeMonth !=''">a.sale_time_month like
CONCAT('%',#{customManage.saleTimeMonth}, '%') and
</if>
<if test="customManage!=null and customManage.followId !=null and customManage.followId !=''">a.follow_id =
#{customManage.followId} and
</if>
<if test="customManage!=null and customManage.createId !=null and customManage.createId !=''">a.create_id =
#{customManage.createId} and
</if>
<if test="customManage!=null and customManage.userId !=null and customManage.userId !=''">(a.create_id =
#{customManage.userId} or a.follow_id = #{customManage.userId}) and
</if>
a.is_delete=0 and a.id
Order By a.create_time Desc
</sql>