【excel复杂一对多动态表头导出】_java excel复杂表头生成

POI构建一对多动态表头 , 三层动态表头导出样例

一 , 引入maven依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11

二 , 动态表头实体类

定义动态表头数据实体类

@Data
public class CellDTO {

    private String name;

    private List<CellDTO> childList;

}
1
2
3
4
5
6
7
8

三 , 设置动态表头数据

dataList: 三层数据结构, 基本情况, 包含第一层表头, 第一层表头里包含第二层表头
基本情况表头是固定的, 可以手动写死, 三层动态去构建表头

    /**
     * 构建表头
     *
     * @param dataList 表头数据
     * @return {@code List<CellDTO>}
     */
    private List<CellDTO> buildHeaderData(List<BasicData> dataList) {
        List<CellDTO> list = new ArrayList<>();
        if (!CollectionUtils.isEmpty(dataList)){
            CellDTO cell1 = new CellDTO();
            cell1.setName("基本情况");

            List<CellDTO> childList1 = new ArrayList<>();
            CellDTO child1 = new CellDTO();
            child1.setName("年份");

            CellDTO child2 = new CellDTO();
            child2.setName("所属市");

            CellDTO child3 = new CellDTO();
            child3.setName("所属县");

            CellDTO child4 = new CellDTO();
            child4.setName("所属乡镇");

            CellDTO child5 = new CellDTO();
            child5.setName("名称");

            CellDTO child6 = new CellDTO();
            child6.setName("层级");

            childList1.add(child1);
            childList1.add(child2);
            childList1.add(child3);
            childList1.add(child4);
            childList1.add(child5);
            childList1.add(child6);

            cell1.setChildList(childList1);
            list.add(cell1);

            //取出第一条数据指标树集合, 组成动态表头
            BasicData basicData= dataList.get(0);
            //取出
            List<ClassifyDTO> classifyList = basicData.getChildren();
            for (ClassifyDTO classifyDTO : classifyList) {
                //第一层
                CellDTO cell11 = new CellDTO();
                cell11.setName(classifyDTO.getName());
                //第二层 总
                List<CellDTO> childList22 = new ArrayList<>();
                //取出
                List<IndicatorDTO> indicatorList = classifyDTO.getChildren();
                for (IndicatorDTO indicatorDTO : indicatorList) {
                    //第二层 
                    CellDTO child22 = new CellDTO();
                    child22.setName(indicatorDTO.getName());
                    //第三层 
                    List<CellDTO> childList33 = new ArrayList<>();
                    CellDTO child555 = new CellDTO();
                    child555.setName("计划值");
                    CellDTO child666 = new CellDTO();
                    child666.setName("完成值");
                    childList33.add(child555);
                    childList33.add(child666);
                    //第二层单 添加第三层总
                    child22.setChildList(childList33);
                    //第二层 指标总 添加第二层单
                    childList22.add(child22);
                }
                //第一层  添加第二层总
                cell11.setChildList(childList22);
                //list添加 第一层单
                list.add(cell11);
            }
        }
        return list;
    }
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

四 , excel构建动态表头

用设置好的动态表头数据对象, 去构建excel动态表头

    /**
     * 构建动态表头
     *
     * @param list     动态表头数据
     * @param sheetName    sheet页名称
     * @return {@code HSSFWorkbook}
     */
    public static HSSFWorkbook buildHeader(List<CellDTO> list,String sheetName) {

        HSSFWorkbook workbook = new HSSFWorkbook();

        CellStyle cellStyle = workbook.createCellStyle();
        // 文字居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置单元格内容垂直对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        cellStyle.setWrapText(true);

        HSSFSheet sheet = workbook.createSheet(sheetName);

        HSSFRow row0 = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);
        HSSFRow row2 = sheet.createRow(2);

        CellDTO index0Data = list.get(0);
        int index0DataChildSize = index0Data.getChildList().size();

        //第一层递增索引(从0开始)
        int oneIncrementalIndex = 0;

        //三层起始位置索引
        int startPosition = index0DataChildSize - 1;

        for (CellDTO one : list) {
            String oneName = one.getName();
            List<CellDTO> twoChildList = one.getChildList();

            if (CollUtil.isNotEmpty(twoChildList)) {
                //递增索引(从0开始) - 列
                int incrementalIndex = 0;
                for (CellDTO two : twoChildList) {
                    String twoName = two.getName();
                    List<CellDTO> threeChildList = two.getChildList();

                    //第三层
                    if (CollUtil.isNotEmpty(threeChildList)) {
                        for (CellDTO three : threeChildList) {
                            String threeName = three.getName();
                            HSSFCell row2Cell = row2.createCell(startPosition + incrementalIndex + 1);
                            row2Cell.setCellValue(threeName);
                            row2Cell.setCellStyle(cellStyle);
                            //+1
                            incrementalIndex = incrementalIndex + 1;
                        }

                        //重置
                        incrementalIndex = 0;

                        HSSFCell row1Cell = row1.createCell(startPosition + incrementalIndex + 1);
                        row1Cell.setCellValue(twoName);
                        row1Cell.setCellStyle(cellStyle);

                        //合并单元格(行数不变,列数进行合并)
                        CellRangeAddress region = new CellRangeAddress(1, 1, startPosition + incrementalIndex + 1, startPosition + incrementalIndex + threeChildList.size());
                        sheet.addMergedRegion(region);

                        //第三层 列 + 2
                        startPosition = startPosition + 2;

                    } else {
                        HSSFCell row1Cell = row1.createCell(incrementalIndex);
                        row1Cell.setCellValue(twoName);
                        row1Cell.setCellStyle(cellStyle);

                        //合并单元格(列数不变,行数进行合并)
                        CellRangeAddress region = new CellRangeAddress(1, 2, incrementalIndex, incrementalIndex);
                        sheet.addMergedRegion(region);

                        //列+1
                        incrementalIndex = incrementalIndex + 1;
                    }
                }

                HSSFCell row0Cell = row0.createCell(oneIncrementalIndex);
                row0Cell.setCellValue(oneName);
                row0Cell.setCellStyle(cellStyle);

                //合并单元格(行数不变,列数进行合并)
                //判断是基本情况还是指标
                if ("基本情况".equals(oneName)){
                    int lastCol = twoChildList.size() == 1 ? oneIncrementalIndex + 1 : oneIncrementalIndex + twoChildList.size() - 1;
                    CellRangeAddress region = new CellRangeAddress(0, 0, oneIncrementalIndex, lastCol);
                    sheet.addMergedRegion(region);

                    //下个第一层起始索引
                    oneIncrementalIndex = oneIncrementalIndex + twoChildList.size();
                }else {

                    int lastCol = twoChildList.size() == 1 ? oneIncrementalIndex + 1 : oneIncrementalIndex + twoChildList.size() * 2 - 1;
                    CellRangeAddress region = new CellRangeAddress(0, 0, oneIncrementalIndex, lastCol);
                    sheet.addMergedRegion(region);

                    //下个第一层起始索引
                    oneIncrementalIndex = oneIncrementalIndex + twoChildList.size() * 2;
                }
            }
        }
        return workbook;
    }
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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

五 , 塞入业务数据

dataList: 三层数据结构, 基本情况, 包含第一层表头, 第一层表头里包含第二层表头
workbook: 设置好的动态表头
取出设置好表头的sheet页, 然后循环去设置每一个格子的值

/**
 *  塞入业务数据
 *
 * @param workbook excel
 * @param list     数据
 */
private void insertValue(HSSFWorkbook workbook, List<BasicData> list) {
    if (!CollectionUtils.isEmpty(list)){
        //取出sheet页
        HSSFSheet sheet = workbook.getSheetAt(0);
        //行起始索引
        int rowStartIndex = 3;
        //指标值-列起始索引
        int columnStartIndex = 6;

        //设置单元格样式
        CellStyle cellStyle = workbook.createCellStyle();

        //遍历数据, 塞入表格
        for (BasicData basicData : list) {
            //创建行
            HSSFRow row = sheet.createRow(rowStartIndex);
            //列0
            HSSFCell rowCell0 = row.createCell(0);
            rowCell0.setCellValue(basicData.getYear());
            rowCell0.setCellStyle(cellStyle);
            //列1
            HSSFCell rowCell1 = row.createCell(1);
            rowCell1.setCellValue(basicData.getCity());
            rowCell1.setCellStyle(cellStyle);
            //列2
            HSSFCell rowCell2 = row.createCell(2);
            rowCell2.setCellValue(basicData.getCounty());
            rowCell2.setCellStyle(cellStyle);
            //列3
            HSSFCell rowCell3 = row.createCell(3);
            rowCell3.setCellValue(basicData.getTownship());
            rowCell3.setCellStyle(cellStyle);
            //列4
            HSSFCell rowCell4 = row.createCell(4);
            rowCell4.setCellValue(basicData.getVillage());
            rowCell4.setCellStyle(cellStyle);
            //列5 
            HSSFCell rowCell5 = row.createCell(5);
            rowCell5.setCellValue(basicData.getLevel);
        
            rowCell5.setCellStyle(cellStyle);
            //第一层
            List<ClassifyDTO> classifyDTOS = basicData.getChildren();
            for (ClassifyDTO classifyDTO : classifyDTOS) {
                //第二层
                List<IndicatorDTO> indicatorDTOList = classifyDTO.getChildren();
                for (IndicatorDTO indicatorDTO : indicatorDTOList) {
                    //第三层
                    //列 - 计划值
                    HSSFCell rowCell11 = row.createCell(columnStartIndex);
                    rowCell11.setCellValue(String.valueOf(indicatorDTO.getPlanValue()));
                    rowCell11.setCellStyle(cellStyle);
                    //列 - 完成值
                    HSSFCell rowCell22 = row.createCell(columnStartIndex + 1);
                    rowCell22.setCellValue(String.valueOf(indicatorDTO.getCompletionValue()));
                    rowCell22.setCellStyle(cellStyle);
                    //第三层 列索引 + 2
                    columnStartIndex = columnStartIndex + 2;
                }
            }

            //行索引 + 1
            rowStartIndex = rowStartIndex + 1;
            //重置 第三层-列起始索引
            columnStartIndex = 6;
        }
    }
}
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

六 , 导出excel

    /**
     * 导出
     *
     * @param workbook excel
     * @param response 响应
     */
    protected static void write(HSSFWorkbook workbook, HttpServletResponse response){
        try {
            String fileType = ".xlsx";
            String fileName = "excel" + fileType;
            String attachment = "attachment; filename=" + URLEncoder.encode(fileName,"UTF-8");
            response.setHeader("Content-disposition", attachment);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");

            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);

            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("导出Excel失败！");
        }
    }
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