package com.ruoyi.basic.excel;
|
|
import cn.hutool.core.util.ObjectUtil;
|
import com.alibaba.excel.context.AnalysisContext;
|
import com.alibaba.excel.exception.ExcelDataConvertException;
|
import com.alibaba.excel.metadata.data.ReadCellData;
|
import com.alibaba.excel.read.listener.ReadListener;
|
import com.alibaba.excel.util.ListUtils;
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
|
import com.ruoyi.basic.pojo.StandardTemplate;
|
import com.ruoyi.basic.pojo.StructureItemParameter;
|
import com.ruoyi.basic.service.StandardTemplateService;
|
import com.ruoyi.basic.service.StructureItemParameterService;
|
import com.ruoyi.common.core.domain.entity.SysDictData;
|
import com.ruoyi.system.service.ISysDictTypeService;
|
import lombok.AllArgsConstructor;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.ObjectUtils;
|
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Slf4j
|
@AllArgsConstructor
|
public class MultiSheetImportListener implements ReadListener<StructureItemParameter> {
|
|
// 每批处理1000条数据
|
private static final int BATCH_COUNT = 1000;
|
private List<StructureItemParameter> cachedList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
|
private final StructureItemParameterService parameterService;
|
private String currentSheetName;
|
|
private final ISysDictTypeService dictTypeService;
|
|
private final StandardTemplateService standardTemplateService;
|
|
// 新增：内存去重集合（记录唯一键）
|
private Set<String> uniqueKeys = new HashSet<>();
|
|
public MultiSheetImportListener(StructureItemParameterService parameterService, StandardTemplateService standardTemplateService,
|
ISysDictTypeService dictTypeService) {
|
this.parameterService = parameterService;
|
this.standardTemplateService = standardTemplateService;
|
this.dictTypeService = dictTypeService;
|
}
|
|
|
@Override
|
public void invoke(StructureItemParameter data, AnalysisContext context) {
|
|
String uniqueKey = buildUniqueKey(data);
|
if (uniqueKeys.contains(uniqueKey)) {
|
log.warn("发现重复数据，已跳过: {} (Sheet: {}, 行号: {})",
|
uniqueKey,
|
currentSheetName,
|
context.readRowHolder().getRowIndex() + 1
|
);
|
return;
|
}
|
uniqueKeys.add(uniqueKey);
|
|
data.setId(null);
|
// 测试对象
|
if (data.getSample() == null) {
|
data.setSample(null);
|
} else {
|
String brand = data.getSample();
|
StringBuilder builder = new StringBuilder();
|
builder.append("[");
|
// 产品
|
if (ObjectUtil.isNotEmpty(data.getProduct())) {
|
String production = data.getProduct();
|
if (!production.contains("；")) {
|
builder.append(String.format("[\"%s\",\"%s\"]", brand, production));
|
} else {
|
Arrays.stream(production.split("；")).forEach(item -> {
|
builder.append(String.format("[\"%s\",\"%s\"],", brand, item));
|
});
|
builder.deleteCharAt(builder.length() - 1);
|
}
|
} else {
|
builder.append("[");
|
builder.append(String.format("\"%s\"", brand));
|
builder.append("]");
|
}
|
builder.append("]");
|
data.setSample(builder.toString());
|
}
|
|
// 查询数据库中是否存在相同记录
|
LambdaQueryWrapper<StructureItemParameter> wrapper = Wrappers.lambdaQuery(StructureItemParameter.class)
|
.eq(StructureItemParameter::getInspectionItem, data.getInspectionItem())
|
.eq(StructureItemParameter::getSample, data.getSample());
|
|
// 判断是否有检验项类型
|
if (ObjectUtils.isNotEmpty(data.getInspectionItemClass())) {
|
wrapper.eq(StructureItemParameter::getInspectionItemClass, data.getInspectionItemClass());
|
}
|
|
// 判断是否有检验子项
|
if (ObjectUtils.isNotEmpty(data.getInspectionItemSubclass())) {
|
wrapper.eq(StructureItemParameter::getInspectionItemSubclass, data.getInspectionItemSubclass());
|
}
|
|
StructureItemParameter db_str = parameterService.getOne(wrapper);
|
if (db_str != null) {
|
// 若需更新，设置 id
|
data.setId(db_str.getId());
|
}
|
|
// 原始记录模板
|
StandardTemplate standTempIdByName = standardTemplateService.getStandTempIdByName(String.valueOf(data.getTemplateName()));
|
if (standTempIdByName != null) {
|
data.setTemplateId(standTempIdByName.getId());
|
} else {
|
data.setTemplateId(null);
|
}
|
|
// 数据类型
|
String jy;
|
String inspectionType = data.getInspectionItemType() != null ?
|
data.getInspectionItemType().toString() : "";
|
if ("非采集类型".equals(inspectionType)) {
|
jy = "0";
|
} else {
|
jy = "1";
|
}
|
data.setInspectionItemType(jy);
|
|
// 方法名称
|
if (data.getMethod() == null) {
|
data.setMethod(null);
|
} else {
|
StringBuffer buffer = new StringBuffer();
|
String input = data.getMethod().toString();
|
buffer.append("[");
|
String[] values = input.split("；");
|
for (String value : values) {
|
buffer.append("\"").append(value.trim()).append("\",");
|
}
|
buffer.deleteCharAt(buffer.length() - 1);
|
buffer.append("]");
|
data.setMethod(buffer.toString());
|
}
|
|
// 特殊标识
|
String bs;
|
String bsmValue = data.getBsm() == null ? "" : data.getBsm().trim();
|
if ("否".equals(bsmValue)) {
|
bs = "0";
|
} else {
|
bs = "1";
|
}
|
data.setBsm(bs);
|
|
//检验项类型
|
String validateValueType = data.getInspectionValueType().toString();
|
if (ObjectUtils.isNotEmpty(validateValueType)) {
|
List<SysDictData> enums = dictTypeService.selectDictDataByName("检验值类型")
|
.stream().filter(sysDictData -> sysDictData.getDictLabel().equals(validateValueType)).collect(Collectors.toList());
|
data.setInspectionValueType(enums.get(0).getDictValue());
|
}
|
|
// 条件
|
if (data.getRadiusList() == null) {
|
data.setRadiusList(null);
|
} else {
|
StringBuffer buffer = new StringBuffer();
|
String input = data.getRadiusList();
|
buffer.append("[");
|
String[] values = input.split("；");
|
for (String value : values) {
|
buffer.append("\"").append(value.trim()).append("\",");
|
}
|
buffer.deleteCharAt(buffer.length() - 1);
|
buffer.append("]");
|
data.setRadiusList(buffer.toString());
|
}
|
|
if (data.getManDay() == null) {
|
data.setManDay(null);
|
}
|
|
// 添加到缓存列表
|
cachedList.add(data);
|
|
// 达到批次处理阈值时入库
|
if (cachedList.size() >= BATCH_COUNT) {
|
saveData();
|
cachedList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
|
}
|
}
|
|
@Override
|
public void doAfterAllAnalysed(AnalysisContext context) {
|
// 最后一批数据处理
|
if (!cachedList.isEmpty()) {
|
saveData();
|
}
|
log.info("Sheet [{}] 导入完成", currentSheetName);
|
}
|
|
@Override
|
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
|
this.currentSheetName = context.readSheetHolder().getSheetName();
|
}
|
|
private void saveData() {
|
// 批量插入（使用若依Service层方法）
|
parameterService.saveOrUpdateBatch(cachedList);
|
}
|
|
@Override
|
public void onException(Exception exception, AnalysisContext context) {
|
String sheetName = context.readSheetHolder().getSheetName();
|
int rowIndex = context.readRowHolder().getRowIndex() + 1;
|
int columnIndex = -1;
|
String errorMsg = exception.getMessage();
|
|
// 针对 ExcelDataConvertException 提取列号
|
if (exception instanceof ExcelDataConvertException) {
|
ExcelDataConvertException ex = (ExcelDataConvertException) exception;
|
columnIndex = ex.getColumnIndex() + 1;
|
errorMsg = String.format("列[%s]数据格式错误: %s",
|
ex.getCellData().getStringValue(),
|
errorMsg
|
);
|
}
|
// 打印完整错误堆栈（调试时开启）
|
log.error("Sheet[{}] 第{}行第{}列解析失败: {}", sheetName, rowIndex, columnIndex, errorMsg);
|
}
|
|
private String buildUniqueKey(StructureItemParameter data) {
|
return String.join("|",
|
ObjectUtils.defaultIfNull(data.getInspectionItem(), ""),
|
ObjectUtils.defaultIfNull(data.getInspectionItemEn(),""),
|
ObjectUtils.defaultIfNull(data.getProduct(),""),
|
ObjectUtils.defaultIfNull(data.getInspectionItemSubclass(), ""),
|
ObjectUtils.defaultIfNull(data.getSample(), ""),
|
ObjectUtils.defaultIfNull(data.getInspectionItemClass(), "")
|
);
|
}
|
}
|
