@PostMapping("/importExcelOther")
@ApiOperationSupport(order = 9)
@ApiOperation(value = "导入excel数据", notes = "传入excel文件")
public R importExcelOther(@RequestParam("file") MultipartFile file) {
if (file.getSize()==0){
throw new RuntimeException("文件大小不能为空");
}
ETLExecutionThreadLocal.setStartTime(System.currentTimeMillis());
String s = odsPvtService.importAllSheet(file);
dwPvtServiceImpl.ODdsToDw();
if (s.equals("success")) {
return R.success("导入成功");
} else {
throw new RuntimeException(s);
}
}
下面开始进入正题
public interface IOdsPvtService {
String importAllSheet(MultipartFile file);
}
@EtlLog
@Transactional
@Override
public String importAllSheet(MultipartFile file) {
try (InputStream inputStream = file.getInputStream()) {
// 创建临时文件
File tempFile = createTempFile(file);
// 读取Excel数据
List<LunchDataMultiSheetListener> listenerList = readExcelData(tempFile);
// 处理Excel数据
Map<String, List<Object>> stringListMap = handleExcelData(listenerList);
for (String s : stringListMap.keySet()) {
List<Object> objects = stringListMap.get(s);
switch (s) {
case "特质焦虑问卷":
List<OdsPvtSasEntity> entities = (List<OdsPvtSasEntity>) (List<?>) objects;
odsPvtSasService.saveBatch(entities);
break;
case "压力知觉量表":
List<OdsPvtPssEntity> pssEntities = (List<OdsPvtPssEntity>) (List<?>) objects;
odsPvtPssService.saveBatch(pssEntities);
break;
case "生活满意度量表":
List<OdsPvtSwlsEntity> shsEntities = (List<OdsPvtSwlsEntity>) (List<?>) objects;
odsPvtSwlsService.saveBatch(shsEntities);
break;
case "主观幸福感量表":
List<OdsPvtShsEntity> sdsEntities = (List<OdsPvtShsEntity>) (List<?>) objects;
odsPvtShsService.saveBatch(sdsEntities);
break;
case "抑郁自评量表":
List<OdsPvtSdsEntity> mlqEntities = (List<OdsPvtSdsEntity>) (List<?>) objects;
odsPvtSdsService.saveBatch(mlqEntities);
break;
case "生命意义感量表":
List<OdsPvtMlqEntity> mlqEntities1 = (List<OdsPvtMlqEntity>) (List<?>) objects;
odsPvtMlqService.saveBatch(mlqEntities1);
break;
case "自尊量表":
List<OdsPvtSesEntity> sesEntities = (List<OdsPvtSesEntity>) (List<?>) objects;
odsPvtSesService.saveBatch(sesEntities);
break;
case "人格特质维度问卷":
List<OdsPvtDyEntity> dyEntities = (List<OdsPvtDyEntity>) (List<?>) objects;
odsPvtDyService.saveBatch(dyEntities);
break;
case "大五人格问卷":
List<OdsPvtNeoFfiEntity> neoFfiEntity = (List<OdsPvtNeoFfiEntity>) (List<?>) objects;
odsPvtNeoFfiService.saveBatch(neoFfiEntity);
break;
case "TKI冲突模式量表":
List<OdsPvtTkiEntity> tkiFfiEntity = (List<OdsPvtTkiEntity>) (List<?>) objects;
odsPvtTkiService.saveBatch(tkiFfiEntity);
break;
case "人际关系量表":
List<OdsPvtNriEntity> nriEntity = (List<OdsPvtNriEntity>) (List<?>) objects;
odsPvtNriService.saveBatch(nriEntity);
break;
case "亲社会行为倾向量表":
List<OdsPvtPtmEntity> ptmEntity = (List<OdsPvtPtmEntity>) (List<?>) objects;
odsPvtPtmService.saveBatch(ptmEntity);
break;
case "Buss-Warren攻击问卷":
List<OdsPvtBwaqEntity> waqEntity = (List<OdsPvtBwaqEntity>) (List<?>) objects;
odsPvtBwaqService.saveBatch(waqEntity);
break;
case "加工速度测评":
List<OdsPvtSgEntity> waqEntity1 = (List<OdsPvtSgEntity>) (List<?>) objects;
odsPvtSgService.saveBatch(waqEntity1);
break;
case "执行功能测评":
List<OdsPvtTsEntity> tsEntity = (List<OdsPvtTsEntity>) (List<?>) objects;
odsPvtTsService.saveBatch(tsEntity);
break;
case "情景记忆测评":
List<OdsPvtEmEntity> waqEntity2 = (List<OdsPvtEmEntity>) (List<?>) objects;
odsPvtEmService.saveBatch(waqEntity2);
break;
case "工作记忆测评":
List<OdsPvtWmEntity> waqEntity3 = (List<OdsPvtWmEntity>) (List<?>) objects;
odsPvtWmService.saveBatch(waqEntity3);
break;
case "注意力测评":
List<OdsPvtSzhxEntity> waqEntity4 = (List<OdsPvtSzhxEntity>) (List<?>) objects;
odsPvtSzhxService.saveBatch(waqEntity4);
break;
case "逻辑推理测评":
List<OdsPvtRpmsEntity> waqEntity5 = (List<OdsPvtRpmsEntity>) (List<?>) objects;
odsPvtRpmsService.saveBatch(waqEntity5);
break;
case "视空间测评":
List<OdsPvtMrEntity> waqEntity6 = (List<OdsPvtMrEntity>) (List<?>) objects;
odsPvtMrService.saveBatch(waqEntity6);
break;
case "特质应对方式问卷":
List<OdsPvtTcsqEntity> waqEntity7 = (List<OdsPvtTcsqEntity>) (List<?>) objects;
odsPvtTcsqService.saveBatch(waqEntity7);
break;
case "情绪调节量表":
List<OdsPvtErqEntity> waqEntity8 = (List<OdsPvtErqEntity>) (List<?>) objects;
odsPvtErqService.saveBatch(waqEntity8);
break;
case "心理弹性量表":
List<OdsPvtCdRiscEntity> waqEntity9 = (List<OdsPvtCdRiscEntity>) (List<?>) objects;
odsPvtCdRiscService.saveBatch(waqEntity9);
break;
case "自我效能感量表":
List<OdsPvtGsesEntity> waqEntity10 = (List<OdsPvtGsesEntity>) (List<?>) objects;
odsPvtGsesService.saveBatch(waqEntity10);
break;
case "基本信息调查表":
List<OdsPvtBaseInfoEntity> odsPvtBaseInfoEntities = (List<OdsPvtBaseInfoEntity>) (List<?>) objects;
odsPvtBaseInfoService.saveBatch(odsPvtBaseInfoEntities);
break;
default:
// 可选:如果存在无法识别的实体类名称,可以选择抛出异常或进行适当处理
break;
}
}
return "success";
} catch (IOException e) {
e.printStackTrace();
return e.getMessage();
}
}
/**
* 读取Excel数据
*/
private List<LunchDataMultiSheetListener> readExcelData(File file) {
ExcelReader excelReader = EasyExcel.read(file).build();
List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();
List<LunchDataMultiSheetListener> listenerList = sheets.stream()
.map(sheet -> {
LunchDataMultiSheetListener listener = new LunchDataMultiSheetListener();
ReadSheet readSheet = EasyExcel.readSheet(sheet.getSheetNo()).registerReadListener(listener).build();
excelReader.read(readSheet);
List<List<String>> dataList = listener.getDataList();
return listener;
})
.collect(Collectors.toList());
excelReader.finish();
return listenerList;
}
/**
* 创建临时文件
*/
private File createTempFile(MultipartFile file) throws IOException {
String originalFilename = file.getOriginalFilename();
String[] filename = originalFilename.split("\\.");
File tempFile = File.createTempFile(filename[0], "." + filename[1] + ".");
file.transferTo(tempFile);
tempFile.deleteOnExit();
return tempFile;
}
//业务层
private Map<String, List<Object>> handleExcelData(List<LunchDataMultiSheetListener> listenerList) {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
Map<String, List<Object>> entityMap = new HashMap<>();
listenerList.forEach(listener -> listener.getDataList().forEach(lineData -> {
System.out.println("lineData = " + lineData);
// 其它业务处理。。
String type = lineData.get(2);
Class entityClassBySheetName = getEntityClassBySheetName(type);
if (entityClassBySheetName == null) {
return;
}
// 使用反射创建新的对象
try {
Object entity = entityClassBySheetName.newInstance();
// 设置属性值
Field[] fields = entityClassBySheetName.getDeclaredFields();
for (int i = 0; i < lineData.size(); i++) {
if (i == lineData.size() - 1) {
try {
Field field = fields[i];
field.setAccessible(true);
String dateTimeString = lineData.get(i);
LocalDateTime localDateTime = LocalDateTime.parse(dateTimeString, formatter);
field.set(entity, localDateTime);
continue;
} catch (Exception e) {//最后一个字段类型可能是String类型,如果出问题,那就直接填字符串
Field field = fields[i];
field.setAccessible(true);
field.set(entity, lineData.get(i));
continue;
}
}
Field field = fields[i];
field.setAccessible(true);
field.set(entity, lineData.get(i));
}
Field importTimeField =fields[lineData.size()];
importTimeField.setAccessible(true);
importTimeField.set(entity, LocalDateTime.now());
// ...
// 将对象添加到对应的列表中
List<Object> entityList = entityMap.getOrDefault(type, new ArrayList<>());
entityList.add(entity);
entityMap.put(type, entityList);
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
// 处理异常
}
}));
return entityMap;
}
// 根据 sheetName 返回对应的实体类型
private Class getEntityClassBySheetName(String sheetName) {
// 在此根据 sheetName 返回对应的实体类型,例如:
if (sheetName.equals("特质焦虑问卷")) {
return OdsPvtSasEntity.class;
} else if (sheetName.equals("压力知觉量表")) {
return OdsPvtPssEntity.class;
} else if (sheetName.equals("生活满意度量表")) {
return OdsPvtSwlsEntity.class;
} else if (sheetName.equals("主观幸福感量表")) {
return OdsPvtShsEntity.class;
} else if (sheetName.equals("抑郁自评量表")) {
return OdsPvtSdsEntity.class;
} else if (sheetName.equals("生命意义感量表")) {
return OdsPvtMlqEntity.class;
} else if (sheetName.equals("自尊量表")) {
return OdsPvtSesEntity.class;
} else if (sheetName.equals("人格特质维度问卷")) {
return OdsPvtDyEntity.class;
} else if (sheetName.equals("大五人格问卷")) {
return OdsPvtNeoFfiEntity.class;
} else if (sheetName.equals("TKI冲突模式量表")) {
return OdsPvtTkiEntity.class;
} else if (sheetName.equals("人际关系量表")) {
return OdsPvtNriEntity.class;
} else if (sheetName.equals("亲社会行为倾向量表")) {
return OdsPvtPtmEntity.class;
} else if (sheetName.equals("Buss-Warren攻击问卷")) {
return OdsPvtBwaqEntity.class;
} else if (sheetName.equals("加工速度测评")) {
return OdsPvtSgEntity.class;
} else if (sheetName.equals("执行功能测评")) {
return OdsPvtTsEntity.class;
} else if (sheetName.equals("情景记忆测评")) {
return OdsPvtEmEntity.class;
} else if (sheetName.equals("工作记忆测评")) {
return OdsPvtWmEntity.class;
} else if (sheetName.equals("注意力测评")) {//注意力测评的表结构有问题 todo
return OdsPvtSzhxEntity.class;
} else if (sheetName.equals("逻辑推理测评")) {
return OdsPvtRpmsEntity.class;
} else if (sheetName.equals("视空间测评")) {
return OdsPvtMrEntity.class;
} else if (sheetName.equals("特质应对方式问卷")) {
return OdsPvtTcsqEntity.class;
} else if (sheetName.equals("情绪调节量表")) {
return OdsPvtErqEntity.class;
} else if (sheetName.equals("心理弹性量表")) {
return OdsPvtCdRiscEntity.class;
} else if (sheetName.equals("自我效能感量表")) {
return OdsPvtGsesEntity.class;
} else if (sheetName.equals("基本信息调查表")){
return OdsPvtBaseInfoEntity.class;
}
return null;//返回空为了后续做准备
}
}
必须建立一个监听器
package org.springblade.common.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
@Slf4j
@Data
public class LunchDataMultiSheetListener extends AnalysisEventListener<Map<Integer, String>> {
private List<List<String>> dataList;
public LunchDataMultiSheetListener() {
this.dataList = new ArrayList<>();
}
/**
* 每读到一行数据都调用invoke方法
*
* @param integerObjectMap
* @param context
*/
@Override
public void invoke(Map<Integer, String> integerObjectMap, AnalysisContext context) {
Integer rowIndex = context.readRowHolder().getRowIndex();
System.out.println("rowIndex = " + rowIndex);
// key为列号,value为单元格的内容
log.info("解析到数据:{}", integerObjectMap);
// 获取当前解析的sheet的信息
String sheetName = context.readSheetHolder().getSheetName();
// Integer sheetIndex = context.readSheetHolder().getSheetNo();
// System.out.println("当前解析的sheet名称:" + sheetName);
// System.out.println("当前解析的sheet索引:" + sheetIndex);
// 把数据放到dataList里面,便于统一处理
LinkedList<String> strings = new LinkedList<>();
integerObjectMap.forEach((k, v) -> {
strings.add(v);
});
this.dataList.add(strings);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 读完所有数据,做统一处理。
// 当然还可以拿到listener之外处理
log.info("数据读取完成");
}
}