/**
* 【 execl 门店导入实体 】
*
* @author yangjunxiong
* Created on 2021/10/12 15:57
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StoreInfoFilterImportDTO implements Verify, Serializable {
private static final long serialVersionUID = 6549827151178326906L;
@ApiModelProperty(name = "name", value = "门店名称")
@ExcelProperty(index = 0, value = "门店名称(必填,7位数字,例:必胜客锦江乐园店)")
@NotBlank(message = "门店名称 不能为空")
@ColumnWidth(30)
private String name;
@ApiModelProperty(name = "areaName", value = "区域名称")
@ExcelProperty(index = 1, value = "区域名称(请输入下拉选择框存在的类型)")
@NotBlank(message = "区域名称 不能为空")
@ColumnWidth(30)
private String areaName;
@ApiModelProperty(name = "address", value = "门店地址")
@ExcelProperty(index = 2, value = "门店地址(必填,文本,例:上海市闵行区梅陇路1209号)")
@NotBlank(message = "门店地址 不能为空")
@ColumnWidth(30)
private String address;
@ApiModelProperty(name = "managerName", value = "联络人")
@ExcelProperty(index = 3, value = "联络人(必填,文本,例:张三)")
@NotBlank(message = "联络人 不能为空")
@ColumnWidth(30)
private String managerName;
@ApiModelProperty(name = "managerMobile", value = "联络人联系方式")
@ExcelProperty(index = 4, value = "联络人联系方式(必填,11位数字,例:13780000456)")
@NotBlank(message = "联络人联系方式 不能为空")
@ColumnWidth(30)
private String managerMobile ;
@ApiModelProperty(name = "timeStartEnd", value = "营业时间")
@ExcelProperty(index = 5, value = "营业时间(必填,两位小数点,例:10:00-20:00)")
@NotBlank(message = "营业时间 不能为空")
@ColumnWidth(30)
private String timeStartEnd;
@ApiModelProperty(name = "status", value = "门店状态")
@ExcelProperty(index = 6, value = "门店状态(必填,门店状态(0:营业中,1:暂停营业,2:停业),例:1)")
@NotNull(message = "门店状态 不能为空")
@ColumnWidth(30)
private Integer status;
@ApiModelProperty(name = "companyName0", value = "所属企业")
@ExcelProperty(index = 7, value = "所属企业(必填,例:饿了么)")
@NotBlank(message = "所属企业 不能为空")
@ColumnWidth(30)
private String companyName0;
@ApiModelProperty(name = "companyName1", value = "所属供应商")
@ExcelProperty(index = 8, value = "所属供应商(必填,例:供应商1)")
@NotBlank(message = "所属供应商 不能为空")
@ColumnWidth(30)
private String companyName1;
@ExcelIgnore
@ApiModelProperty(name = "companyId1", value = "所属供应商Id", hidden = true)
private Long companyId1;
@ExcelIgnore
@ApiModelProperty(name = "operateUserId", value = "操作用户id", hidden = true)
private Long operateUserId;
@ExcelIgnore
@ApiModelProperty(name = "now", value = "操作时间", hidden = true)
private Date now;
@ExcelIgnore
@ApiModelProperty(name = "areaId", value = "区域id", hidden = true)
private Long areaId;
}
使用方式
/**
* 获取导入门店 导入模板
*/
@ApiOperation(value = "execl导入门店模板下载", tags = {SWTag.sys})
@GetMapping(value = "/getStoreInfosFilterExcelTemplate")
public void getStoreInfosFilterExcelTemplate(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("门店模板", StandardCharsets.UTF_8);
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//k是列,v是下拉的选项
Map<Integer, String[]> map = new HashMap<>();
List<SysDictDTO> dictDTOList = this.sysDictService.selectList("bus_circle_district");
if (CollectionUtils.isNotEmpty(dictDTOList)) {
map.put(1, dictDTOList.stream().map(SysDictDTO::getName).toArray(String[]::new));
}
List<CompanyInfoEntity> list = this.companyInfoService.list(new LambdaQueryWrapper<CompanyInfoEntity>()
.eq(CompanyInfoEntity::getCompanyType, CommonConstant._1));
if (CollectionUtils.isNotEmpty(list)) {
map.put(7, list.stream().map(CompanyInfoEntity::getName).toArray(String[]::new));
}
Map<Integer, String> annotationsMap = new HashMap<>();
annotationsMap.put(0,"门店名称 不能为空");
annotationsMap.put(1,"区域名称 不能为空");
annotationsMap.put(2,"门店地址 不能为空");
annotationsMap.put(3,"联络人 不能为空");
annotationsMap.put(4,"联络人联系方式 不能为空");
annotationsMap.put(5,"营业时间 不能为空");
annotationsMap.put(6,"门店状态 不能为空");
annotationsMap.put(7,"所属企业 不能为空");
annotationsMap.put(8,"所属供应商 不能为空");
EasyExcel.write(response.getOutputStream(), StoreInfoFilterImportDTO.class)
.registerWriteHandler(new PlusSheetWriteHandler(map))
.registerWriteHandler(new HorizontalCellStyleStrategy(new WriteCellStyle(), new WriteCellStyle()))
.registerWriteHandler(new PlusCellWriteHandler(new ArrayList<>(annotationsMap.keySet()), IndexedColors.RED.index, annotationsMap))
.sheet("门店模板")
.doWrite(new ArrayList<StoreInfoFilterImportDTO>());
}
/**
* 导入B端门店
*/
@ApiOperation(value = "execl导入门店", tags = {SWTag.sys})
@PostMapping("storeInfosFilterExcel")
public Result<String> storeInfosFilterExcel(MultipartFile file) {
//导入execl不允许并发 加锁处理
String key = CacheConfigs.LOCK_CACHE.getKey() + ":storeInfosFilterExcel";
boolean b = redisService.tryLock(key, (int) CacheConfigs.LOCK_CACHE.getTTL().getSeconds());
if (b) {
try {
EasyExcel.read(file.getInputStream(), StoreInfoFilterImportDTO.class, storeInfoExeclImportListener).sheet().doRead();
} catch (Exception e) {
return Result.fail(e.getMessage());
} finally {
redisService.unLock(key);
}
} else {
return Result.fail("请稍等,目前已经有上传中的文件");
}
return Result.success();
}
/*
* Copyright 2021 Wicrenet, Inc. All rights reserved.
*/
package com.hq.cloud.business.controller.helper;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 【easyExecl 设置多个下拉选项】
*
* @author yangjunxiong
* Created on 2021/12/28 09:33
*/
public class PlusSheetWriteHandler implements SheetWriteHandler {
private Map<Integer, String[]> map;
private int index;
public PlusSheetWriteHandler(Map<Integer, String[]> map) {
this.map = map;
this.index = 0;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// TODO Auto-generated method stub
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (map.isEmpty()) {
return;
}
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
// 获取一个workbook
Workbook workbook = writeWorkbookHolder.getWorkbook();
// k 为存在下拉数据集的单元格下表 v为下拉数据集
List<Integer> setSheetHidden = new ArrayList<>();
map.forEach((k, v) -> {
// 创建sheet,突破下拉框255的限制
// 定义sheet的名称
String sheetName = "sheet" + k;
// 1.创建一个隐藏的sheet 名称为 proviceSheet
Sheet proviceSheet = workbook.createSheet(sheetName);
// 从第二个工作簿开始隐藏
this.index++;
setSheetHidden.add(this.index);
// 2.循环赋值(为了防止下拉框的行数与隐藏域的行数相对应,将隐藏域加到结束行之后)
for (int i = 0, length = v.length; i < length; i++) {
// i:表示你开始的行数 0表示你开始的列数
proviceSheet.createRow(i).createCell(0).setCellValue(v[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
// 4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (v.length));
// 5 将刚才设置的sheet引用到你的下拉列表中
CellRangeAddressList addressList = new CellRangeAddressList(1, 65536, k, k);
DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
DataValidation dataValidation3 = helper.createValidation(constraint8, addressList)
dataValidation3.setSuppressDropDownArrow(true);
dataValidation3.createErrorBox("提示","请输入下拉框存在的选项");
dataValidation3.setShowErrorBox(true);
writeSheetHolder.getSheet().addValidationData(dataValidation3);
});
// 设置隐藏sheet
setSheetHidden.forEach(index-> workbook.setSheetHidden(index, true));
}
}
表头颜色颜色批注
package com.hq.cloud.business.controller.helper;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.List;
import java.util.Map;
public class PlusCellWriteHandler implements CellWriteHandler {
public PlusCellWriteHandler(List<Integer> columnIndexs, Short colorIndex, Map<Integer, String> annotationsMap) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
}
public PlusCellWriteHandler(List<Integer> columnIndexs, Short colorIndex) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
//操作列
private final List<Integer> columnIndexs;
//颜色
private final Short colorIndex;
// 批注<列的下标,批注内容>
private Map<Integer, String> annotationsMap;
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if(isHead){
// 设置列宽
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Drawing<?> drawing = sheet.createDrawingPatriarch();
// 设置标题字体样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
if (CollectionUtils.isNotEmpty(columnIndexs) &&
colorIndex != null &&
columnIndexs.contains(cell.getColumnIndex())) {
// 设置字体颜色
headWriteFont.setColor(colorIndex);
}
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
cell.setCellStyle(cellStyle);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
// 批注内容
String context = annotationsMap.get(cell.getColumnIndex());
// 创建绘图对象
Comment comment=drawing.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) cell.getColumnIndex(), 0, (short) 1, 3));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
}
}
}
清洗数据并入库
/*
* Copyright 2021 Wicrenet, Inc. All rights reserved.
*/
package com.hq.cloud.business.controller.helper;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.hq.cloud.business.domain.dto.CompanyInfoDTO;
import com.hq.cloud.business.domain.dto.StoreInfoFilterImportDTO;
import com.hq.cloud.business.domain.entity.StoreInfoEntity;
import com.hq.cloud.business.domain.entity.SysDictEntity;
import com.hq.cloud.business.domain.enums.BusinessScopeEnum;
import com.hq.cloud.business.service.CompanyInfoService;
import com.hq.cloud.business.service.StoreInfoService;
import com.hq.cloud.business.service.SysDictService;
import com.hq.cloud.core.common.dict.CommonConstant;
import com.hq.cloud.core.common.util.SecureUtil;
import com.hq.cloud.core.common.verify.VerifyException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.*;
import java.util.stream.Collectors;
/**
* 【 B端 门店导入 】
*
* @author yangjunxiong
* Created on 2021/10/11 11:24
*/
@Slf4j
@Component
public class StoreInfoExeclImportListener extends AnalysisEventListener<StoreInfoFilterImportDTO> {
@Resource
private HttpServletRequest request;
@Autowired
private StoreInfoService storeInfoService;
@Autowired
private CompanyInfoService companyInfoService;
@Autowired
private SysDictService sysDictService;
List<StoreInfoFilterImportDTO> importDTOList = new ArrayList<>();
public StoreInfoExeclImportListener() {
}
/**
* 这个每一条数据解析都会来调用
*
* @param filterImportDTO
* @param context
*/
@Override
public void invoke(StoreInfoFilterImportDTO filterImportDTO, AnalysisContext context) {
try {
long operateUserId = (long) SecureUtil.getUser(request).getUserId();
Date now = new Date();
filterImportDTO.setOperateUserId(operateUserId);
filterImportDTO.setNow(now);
List<SysDictEntity> areas = this.sysDictService.list(new LambdaQueryWrapper<SysDictEntity>()
.eq(SysDictEntity::getCode, "bus_circle_district")
.eq(SysDictEntity::getDelFlag, CommonConstant._0)
);
List<String> areaNames = areas.stream().map(SysDictEntity::getName).collect(Collectors.toList());
try {
filterImportDTO.verify();
} catch (VerifyException e) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, e.getMessage()));
}
importDTOList.forEach(item->{
if (item.getName().equals(filterImportDTO.getName())) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "门店名称不能重复"));
}
});
if (!StringUtils.contains(filterImportDTO.getTimeStartEnd(), "-")) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "营业时间错误 (必填,两位小数点,例:10:00-20:00)"));
} else {
String[] split = StringUtils.split(filterImportDTO.getTimeStartEnd(), "-");
if (split.length != 2) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "营业时间错误 (必填,两位小数点,例:10:00-20:00)"));
} else {
this.storeInfoService.verifyTime(split[0], split[1]);
}
}
CompanyInfoDTO companyInfoDTO = this.companyInfoService.getNameAndCompanyType(filterImportDTO.getCompanyName0(), CommonConstant._1);
if (Objects.isNull(companyInfoDTO)) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "所属企业不存在"));
}
CompanyInfoDTO agentInfoDTO = this.companyInfoService.getNameAndCompanyType(filterImportDTO.getCompanyName1(), CommonConstant._2);
if (Objects.isNull(agentInfoDTO)) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "所属供应商不存在"));
}
if (!agentInfoDTO.getParentId().equals(companyInfoDTO.getId())) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "所属供应商不属于企业:" + filterImportDTO.getCompanyName0()));
}
int count = this.storeInfoService.count(new LambdaQueryWrapper<StoreInfoEntity>()
.eq(StoreInfoEntity::getName, filterImportDTO.getName())
.last(CommonConstant.LIMIT1)
);
if (count >= 1) {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "门店名称已存在!"));
}
filterImportDTO.setCompanyId1(agentInfoDTO.getId());
if (areaNames.contains(filterImportDTO.getAreaName())) {
Optional<SysDictEntity> first = areas.stream()
.filter(area -> area.getName().equals(filterImportDTO.getAreaName()))
.findFirst();
first.ifPresent(brandInfoDTO -> {
filterImportDTO.setAreaId(brandInfoDTO.getId());
});
} else {
throw new VerifyException(String.format("第%s行,异常:%s", context.readRowHolder().getRowIndex() + 1, "区域名称错误 (必填,请输入下拉框存在的区域"));
}
List<BusinessScopeEnum> businessScopeEnums = BusinessScopeEnum.mark(filterImportDTO.getBusinessScope());//校验掩码
this.storeInfoService.verifyBusinessScope(companyInfoDTO.getId(), filterImportDTO.getBusinessType(), businessScopeEnums);//校验业务范围是否是企业业务范围所包含
importDTOList.add(filterImportDTO);
} catch (Exception e) {
importDTOList.clear();
throw new VerifyException(e.getMessage());
}
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
@Transactional
public void doAfterAllAnalysed(AnalysisContext context) {
try {
log.info("{}条数据,开始存储数据库!", importDTOList.size());
if (CollectionUtils.isNotEmpty(importDTOList)) {
// TODO yangjunxiong 2022/2/5 10:20 入库
}
log.info("所有数据解析完成!");
} finally {
importDTOList.clear();
}
}
}
效果