package com.persagy.person.manage; import java.util.ArrayList; import java.util.Date; import java.util.HashSet; import java.util.List; import java.util.Set; import org.springframework.beans.BeanUtils; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.exception.ExcelAnalysisException; import com.persagy.common.constant.SaasCommonConstant; import com.persagy.common.model.BaseUserModel; import com.persagy.common.utils.IdGenerator; import com.persagy.common.utils.StringUtil; import com.persagy.person.pojo.dto.SaasArea; import com.persagy.person.pojo.dto.SaasProject; import com.persagy.person.pojo.vo.project.SaasProjectExcel; import com.persagy.person.service.ISaasAreaService; import com.persagy.person.service.ISaasProjectService; import cn.hutool.core.collection.CollectionUtil; import lombok.extern.slf4j.Slf4j; /** * 传感器数据导入, 此类不能被spring管理,要每次读取excel都要new * * @version v1.0.0 * @company persagy * @author zhangqiankun * @date 2020-11-25 19:52:37 */ @Slf4j public class SaasProjectExcelDataHandler extends AnalysisEventListener { private BaseUserModel baseVO; private ISaasAreaService saasAreaService; private ISaasProjectService saasProjectService; /** * 上传的数据 */ private List list = new ArrayList(); private Set validProjectId = new HashSet(); private Set validLocalId = new HashSet(); private Set validProjectName = new HashSet(); public SaasProjectExcelDataHandler() { } public SaasProjectExcelDataHandler(BaseUserModel baseVO, ISaasAreaService saasAreaService, ISaasProjectService saasProjectService) { this.baseVO = baseVO; this.saasAreaService = saasAreaService; this.saasProjectService = saasProjectService; } /** * 每一条数据解析都会来调用 * * @param data one row value {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(SaasProjectExcel data, AnalysisContext context) { // 数据不可为空校验 int rowIndex = context.readRowHolder().getRowIndex(); if (list.size() > 299) { throw new ExcelAnalysisException("导入数据量过大,不允许超过300条!"); } if (!StringUtil.isAllNotBlank(data.getGroupCode(), data.getProjectId(), data.getProjectLocalId(), data.getProjectName())) { throw new ExcelAnalysisException("第" + rowIndex + "行数据,红色必填项存在为空的单元格,请检查!"); } String projectId = data.getGroupCode() + data.getProjectId(); String localId = data.getGroupCode() + data.getProjectLocalId(); String projectName = data.getGroupCode() + data.getProjectName(); if (validProjectId.contains(projectId)) { throw new ExcelAnalysisException("第" + rowIndex + "行数据,项目ID已存在表中,请检查!"); } if (validLocalId.contains(localId)) { throw new ExcelAnalysisException("第" + rowIndex + "行数据,项目本地编码已存在表中,请检查!"); } if (validProjectName.contains(projectName)) { throw new ExcelAnalysisException("第" + rowIndex + "行数据,项目名称已存在表中,请检查!"); } // 格式转换 list.add(this.convertSaasProjectData(data, rowIndex)); } /** * 所有数据解析完成了都会来调用,在这里进行数据存储,可确保最后遗留的数据也被存储到数据库 * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { if (CollectionUtil.isNotEmpty(list)) { this.saasProjectService.batchInsertProjectData(list); } log.info("项目数据导入成功,操作者: {}", baseVO.getAccountId()); } /** * 转变为能够直接存入数据库的数据形式 * @param data */ private SaasProject convertSaasProjectData(SaasProjectExcel data, int rowIndex) { SaasProject info = new SaasProject(); BeanUtils.copyProperties(data, info); Date date = new Date(); info.setCreateTime(date); info.setUpdateTime(date); info.setGroupCode(data.getGroupCode()); info.setUpdateUser(baseVO.getAccountId()); info.setId(IdGenerator.getSnowIdStr(SaasCommonConstant.SAAS_PROJECT_ID_PREFIX)); // 数据分割 String groupCode = data.getGroupCode().split("_")[0]; info.setGroupCode(groupCode); String areaName = data.getAreaId(); if (StringUtil.isBlank(areaName)) { info.setAreaId(SaasCommonConstant.STR_STATUS_MINUS_1); } else { // 查询出当前name对应的ID SaasArea areaOne = this.saasAreaService.getAreaOne(null, groupCode, null, areaName); if (areaOne == null) { throw new ExcelAnalysisException("第" + rowIndex + "行数据,项目所属区域不存在,请检查!"); } info.setAreaId(areaOne.getId()); } // 验证数据的唯一性 boolean result = this.saasProjectService.validProjectData(info, false); if (!result) { throw new ExcelAnalysisException("第" + rowIndex + "行数据,数据库中存已存在该条记录,请检查!"); } return info; } }