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();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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页表格内容存于map
    Map<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);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

上面projectRead方法用到的EasyExcelListener类

@Slf4j
public class EasyExcelListener extends AnalysisEventListener<Object> {

    // 创建list集合封装最终的数据
    private List<Object> list = new ArrayList<>();
    // 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<Integer, String> headMap, AnalysisContext context) {
    }

    // 读取完成后执行
    @Override
    public 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);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

上面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;
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

保存第一个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();
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

保存第二个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);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

还有几个实体类就不放上来了，博客太长了，看完有一点点思路，可以帮助到你，我还是很开心的，全部代码和导入的excel放下面了，可以拿去看看，参考参考。

excel地址：项目导入.xlsx

代码地址：sakura-boot-demo · GitHub