EasyExcel复杂excel导入

easyexcel官方都是一些简单的导入到处示例，复杂的excel文档导入，还得自己去慢慢琢磨、百度、思考、总结、学习、观察。

代码地址在文档的最后，如果你也遇到这种需求，不妨动动你的小拇指，点个关注，要是可以点个赞，那就更好咯，做人嘛，不要这么小气，不要吝啬你的赞美，哈哈。

要导入的excel格式，如下图：

第一个sheet内容：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MqfRrgNN-1660632070755)(D:\yangfan\mdpic\2022-08-16-13-58-28-image.png)]$

第二个sheet内容，一对多，一个阶段对应多个任务，一个任务对应多个动作：
在这里插入图片描述

废话不多说，直接上代码，能看懂多少就看你自己的功力了

controller层

@RestController
@RequestMapping("/sakura/easyexcel")
@Api(value = "复杂excel导入", tags = {"复杂excel导入"})
public class ProjectExcelController {@AutowiredProjectEasyExcelService projectEasyExcelService;@ApiOperation("复杂excel导入")@PostMapping(value = "/complex/upload")public CommonResult<Object> upload(@RequestParam("file") MultipartFile file){projectEasyExcelService.projectRead(file);return CommonResult.success();}
}

service层

/*** 项目信息excel*/
@Transactional(rollbackFor = Exception.class)
public void projectRead(MultipartFile file) {EasyExcelListener easyExcelListener = new EasyExcelListener();ExcelReader excelReader = null;try {excelReader = EasyExcelFactory.read(file.getInputStream(), easyExcelListener).build();} catch (IOException e) {throw new YErrorException("项目信息导入出错！");}// step2. 获取各个sheet页信息List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();// step3. 获取各个Shhet页表格内容存于mapMap<Integer, List<LinkedHashMap<String, String>>> sheetInfos = new HashMap<>(sheets.size());for (ReadSheet sheet : sheets) {Integer sheetNo = sheet.getSheetNo();excelReader.read(sheet);sheetInfos.put(sheetNo, easyExcelListener.getListMap());}//保存数据到数据库saveExcelInfo(sheetInfos);
}

上面projectRead方法用到的EasyExcelListener类

@Slf4j
public class EasyExcelListener extends AnalysisEventListener<Object> {// 创建list集合封装最终的数据private List<Object> list = new ArrayList<>();// sheet页索引private int sheetNo = 0;@Overridepublic 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表头信息@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {}// 读取完成后执行@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}/*** 将表格转化为map集合（复杂excel读取用此方法）** @return map集合*/public List<LinkedHashMap<String, String>> getListMap() {String jsonObj = JSON.toJSONString(list);return JSON.parseArray(jsonObj, LinkedHashMap.class);}}

上面projectRead中的方法

public void saveExcelInfo(Map<Integer, List<LinkedHashMap<String, String>>> sheetInfos) {for (Integer sheetNo : sheetInfos.keySet()) {List<LinkedHashMap<String, String>> 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<LinkedHashMap<String, String>> maps) {ProjectManage projectManage = new ProjectManage();for (LinkedHashMap<String, String> 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<String, String> projectBackgroudMap = maps.get(8);  String projectBackgroud = projectBackgroudMap.getOrDefault("0", "").toString();projectManage.setProjectBackgroud(projectBackgroud);projectManageService.save(projectManage);projectId = projectManage.getId();
}

保存第二个sheet的目标，将数据组装成多级嵌套的集合。有点绕，要花点时间去想想，项目赶工，瞎几把乱写了，没想到可以凑合着用。

public void saveTarget(List<LinkedHashMap<String, String>> 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);}