快速上手：轻松处理复杂Excel数据导入
// sheet页索引
private int sheetNo = 0;
@Override
public void invoke(Object t, AnalysisContext context) {
// 读取excle内容
int currentSheetNo = context.readSheetHolder().getSheetNo();
if (currentSheetNo != sheetNo) {
// 如果不根据sheet页索引更新状态重新创建list，list会反复添加前面的sheet页对象值
list = new ArrayList<>();
sheetNo = currentSheetNo;
}
list.add(t);
}
// 读取excel表头信息
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
}
// 读取完成后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
/**
- 将表格转化为map集合（复杂excel读取用此方法）
* - @return map集合
*/
public List> getListMap() {
String jsonObj = JSON.toJSONString(list);
return JSON.parseArray(jsonObj, LinkedHashMap.class);
}
}
上面projectRead中的方法
public void saveExcelInfo(Map>> sheetInfos) {
for (Integer sheetNo : sheetInfos.keySet()) {
List> maps = sheetInfos.get(sheetNo);
// 不同sheet页数据处理方式不同
switch (sheetNo) {
case 0:
saveProject(maps);
break;
case 1:
saveTarget(maps);
break;
default:
break;
}
}
}
保存第一个sheet的项目信息，并返回全局的项目id，代码很长，简化了一部分，全部代码可以看最后github的地址
public void saveProject(List> maps) {
ProjectManage projectManage = new ProjectManage();
for (LinkedHashMap map : maps) {
if (map.containsValue("项目名称")) {
String projectName = map.getOrDefault("1", "");
if (StringUtils.isBlank(projectName)) {
throw new YWarmingException("项目名称不能为空！");
}
projectManage.setProjectName(projectName);
}
if (map.containsValue("项目编号")) {
String projectCode = map.getOrDefault("4", "");
projectManage.setProjectCode(projectCode);
}
if (map.containsValue("合同签订时间")) {
String contractSignTimeStr = map.getOrDefault("1", "");
if (StringUtils.isNotBlank(contractSignTimeStr)) {
Date contractSignDate = DateUtil.parse(contractSignTimeStr);
Instant instant = contractSignDate.toInstant();
LocalDateTime contractSignTime = instant.atZone(ZoneId.systemDefault()).toLocalDateTime();
projectManage.setContractSignTime(contractSignTime);
}
}
if (map.containsValue("合同规定终验时间")) {
String contractRuleFinalAcceptTimeStr = map.getOrDefault("3", "");
if (StringUtils.isNotBlank(contractRuleFinalAcceptTimeStr)) {
Date contractSignDate = DateUtil.parse(contractRuleFinalAcceptTimeStr);
Instant instant = contractSignDate.toInstant();
LocalDateTime contractRuleFinalAcceptTime = instant.atZone(ZoneId.systemDefault()).toLocalDateTime();
projectManage.setContractRuleFinalAcceptTime(contractRuleFinalAcceptTime);
}
}
if (map.containsValue("开工时间")) {
String startTimeStr = map.getOrDefault("5", "");
if (StringUtils.isNotBlank(startTimeStr)) {
Date startDate = DateUtil.parse(startTimeStr);
Instant instant = startDate.toInstant();
LocalDateTime startTime = instant.atZone(ZoneId.systemDefault()).toLocalDateTime();
projectManage.setStartTime(startTime);
}
}
}
//这里项目背景的行数，写死了，后面调整表格时，会影响这一块
Map projectBackgroudMap = maps.get(8);
String projectBackgroud = projectBackgroudMap.getOrDefault("0", "").toString();
projectManage.setProjectBackgroud(projectBackgroud);
projectManageService.save(projectManage);
projectId = projectManage.getId();
}
保存第二个sheet的目标，将数据组装成多级嵌套的集合。有点绕，要花点时间去想想，项目赶工，瞎几把乱写了，没想到可以凑合着用。
public void saveTarget(List> list) {
if (projectId == 0) {
throw new YErrorException("请先成功导入项目！");
}
FirstTarget firstTarget = new FirstTarget();
SecondTarget secondTarget = new SecondTarget();
List<FirstTarget> firstTargetList = new ArrayList<>();
List<SecondTarget> secondTargetList = new ArrayList<>();
List<ThirdTarget> thirdTargetList = new ArrayList<>();
for (Map<String, String> map : list) {
System.err.println(map);
String firstTargetName = map.getOrDefault("0", "");
String firstTargetType = map.getOrDefault("1", "");
String secondTargetName = map.getOrDefault("2", "");
String thirdTargetName = map.getOrDefault("3", "");
String thirdTargetBudget = map.getOrDefault("4", "");
String positions = map.getOrDefault("5", "");
if (StringUtils.isNotBlank(firstTargetName)) {
//第一阶段名称不为空时，二三阶段一定不为空
if (StringUtils.isNotBlank(secondTargetName)) {
//第二阶段名称不为空
if (secondTargetList.size() != 0) {
List<SecondTarget> secondTargets = firstTarget.getSecondTargets();
secondTargets.addAll(secondTargetList);
}
secondTarget = new SecondTarget();
thirdTargetList = new ArrayList<>();
firstTarget = new FirstTarget();
secondTargetList = new ArrayList<>();
secondTarget.setSecondTargetName(secondTargetName);
ThirdTarget thirdTarget = new ThirdTarget();
thirdTarget.setThirdTargetName(thirdTargetName);
thirdTarget.setThirdTargetBudget(thirdTargetBudget);
thirdTargetList.add(thirdTarget);
secondTarget.setThirdTargets(thirdTargetList);
secondTargetList.add(secondTarget);
firstTarget.setSecondTargets(secondTargetList);
firstTarget.setFirstTargetName(firstTargetName);
firstTarget.setFirstTargetType(firstTargetType);
firstTargetList.add(firstTarget);
}
} else {
//第一阶段名称为空，且第二阶段名称不为空
if (StringUtils.isNotBlank(secondTargetName)) {
//去重
if (secondTargetList.size() != 0) {
SecondTarget st = secondTargetList.get(0);
List<SecondTarget> secondTargets = firstTarget.getSecondTargets();
SecondTarget secondT = secondTargets.get(0);
if (!st.getSecondTargetName().equals(secondT.getSecondTargetName())) {
secondTargets.addAll(secondTargetList);
}
}
secondTarget = new SecondTarget();
thirdTargetList = new ArrayList<>();
secondTargetList = new ArrayList<>();
secondTarget.setSecondTargetName(secondTargetName);
ThirdTarget thirdTarget = new ThirdTarget();
thirdTarget.setThirdTargetName(thirdTargetName);
thirdTarget.setThirdTargetBudget(thirdTargetBudget);
thirdTargetList.add(thirdTarget);
secondTarget.setSecondTargetName(secondTargetName);
secondTarget.setThirdTargets(thirdTargetList);
secondTargetList.add(secondTarget);
} else {
//第一阶段名称为空，第二阶段名称为空，第三阶段不为空
ThirdTarget thirdTarget = new ThirdTarget();
thirdTarget.setThirdTargetBudget(thirdTargetBudget);
thirdTarget.setThirdTargetName(thirdTargetName);
thirdTargetList.add(thirdTarget);
}
}
}
// 保存最后一条数据
if (secondTargetList.size() != 0) {
List<SecondTarget> secondTargets = firstTarget.getSecondTargets();
secondTargets.addAll(secondTargetList);
}
System.err.println(firstTargetList);
}
还有几个实体类就不放上来了，博客太长了，看完有一点点思路，可以帮助到你，我还是很开心的，全部代码和导入的excel放下面了，可以拿去看看，参考参考。