云上快速入门,热门云上应用快速查找
丰富的线上&线下活动,深入探索云世界
做任务,得社区积分和周边
最真实的开发者用云体验
让每位学生受益于普惠算力
让创作激发创新
资深技术专家手把手带教
遇见技术追梦人
畅聊无限,分享你的技术见解
技术交流,直击现场
海量开发者使用工具、手册,免费下载
极速、全面、稳定、安全的开源镜像
开发手册、白皮书、案例集等实战精华
为开发者定制的Chrome浏览器插件
热门
EasyExcel复杂excel导入
easyexcel官方都是一些简单的导入到处示例,复杂的excel文档导入,还得自己去慢慢琢磨、百度、思考、总结、学习、观察。
代码地址在文档的最后,如果你也遇到这种需求,不妨动动你的小拇指,点个关注,要是可以点个赞,那就更好咯,做人嘛,不要这么小气,不要吝啬你的赞美,哈哈。
要导入的excel格式,如下图:
第一个sheet内容:第二个sheet内容,一对多,一个阶段对应多个任务,一个任务对应多个动作:废话不多说,直接上代码,能看懂多少就看你自己的功力了
controller层@RestController@RequestMapping("/sakura/easyexcel")@Api(value = "复杂excel导入", tags = {"复杂excel导入"})public class ProjectExcelController { @Autowired ProjectEasyExcelService 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 sheets = excelReader.excelExecutor().sheetList();// step3. 获取各个Shhet页表格内容存于mapMap>> 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类@Slf4jpublic class EasyExcelListener extends AnalysisEventListener { // 创建list集合封装最终的数据private List 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 headMap, AnalysisContext context) { } // 读取完成后执行@Overridepublic 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放下面了,可以拿去看看,参考参考。
// 创建list集合封装最终的数据private List 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 headMap, AnalysisContext context) { } // 读取完成后执行@Overridepublic 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放下面了,可以拿去看看,参考参考。
@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 headMap, AnalysisContext context) { }
// 读取完成后执行@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) { }
}上面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放下面了,可以拿去看看,参考参考。