使用EasyExcel导出复杂报表

1.效果图

2.请引入EasyExcel相关依赖，自行百度

3.controller

@Api(tags = "下载中心接口")
@RestController
@RequestMapping("/basic/download")
public class CspDownloadCenter extends BaseController {
    @ApiOperation(value = "复杂的excel测试导出")
    @GetMapping("/excelExportTest")
    public void excelExportTest() throws IOException {
        excelExportService.excelExportTest();
    }

}
1
2
3
4
5
6
7
8
9
10
11

4.Service

public interface ICspExcelExportService {
void excelExportTest() throws IOException;
}
1
2
3

5.Service实现层

public void excelExportTest() throws IOException {

    List> headList = getHeadList();
    List> dataList = getDateList();
1
2

// 这个自己更改
String downloadPath = “D:\sunsiny\2023\work_day\20231010\” + “复杂的excel导出案例” + DateUtils.dateTimeNow() + “.xlsx”;

// 这个自己更改
String downloadBusinessFileName = “D:\sunsiny\2023\work_day\20231010\” + “复杂的excel导出案例” + DateUtils.dateTimeNow() + “1” + “.xlsx”;

    EasyExcel.write(downloadPath)
            .head(roateHeadFields(headList))
            .sheet("sheet1")
            .registerWriteHandler(registerWriteHandler())
            .doWrite(dataList);


    // 加载已导出的文件
    File exportedFile = new File(downloadPath);
    Workbook workbook = WorkbookFactory.create(exportedFile);
    Sheet sheet = workbook.getSheet("sheet1");
    // 移除已有的合并区域
1
2
3
4
5
6
7
8
9
10
11
12

// sheet.removeMergedRegion(0);

    // 创建样式对象
    CellStyle cellStyle = workbook.createCellStyle();
    // 设置边框
1
2
3

// cellStyle.setBorderTop(BorderStyle.THIN);
// cellStyle.setBorderBottom(BorderStyle.THIN);
// cellStyle.setBorderLeft(BorderStyle.THIN);
// cellStyle.setBorderRight(BorderStyle.THIN);
// 设置水平对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中对齐
// 设置合并单元格区域1 月度客诉率
CellRangeAddress mergedRegionRemark = new CellRangeAddress(3, 3, 1, 3);
sheet.addMergedRegion(mergedRegionRemark);
Cell remarkCell = sheet.getRow(3).getCell(0);
remarkCell.setCellStyle(cellStyle);

    // 设置合并单元格区域2   月度工单总量
    CellRangeAddress mergedRegionRemark2 = new CellRangeAddress(4, 4, 1, 3);
    sheet.addMergedRegion(mergedRegionRemark2);
    Cell remarkCell2 = sheet.getRow(4).getCell(0);
    remarkCell2.setCellStyle(cellStyle);

    // 设置合并单元格区域3   客诉类型序号
    CellRangeAddress mergedRegionRemark3 = new CellRangeAddress(5, 7, 0, 0);
    sheet.addMergedRegion(mergedRegionRemark3);
    Cell remarkCell3 = sheet.getRow(5).getCell(0);
    remarkCell3.setCellStyle(cellStyle);

    // 设置合并单元格区域4   客诉类型
    CellRangeAddress mergedRegionRemark4 = new CellRangeAddress(5, 7, 1, 1);
    sheet.addMergedRegion(mergedRegionRemark4);
    Cell remarkCell4 = sheet.getRow(5).getCell(0);
    remarkCell4.setCellStyle(cellStyle);

    // 设置合并单元格区域5 客户原因
    CellRangeAddress mergedRegionRemark5 = new CellRangeAddress(5, 5, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark5);
    Cell remarkCell5 = sheet.getRow(5).getCell(0);
    remarkCell5.setCellStyle(cellStyle);

    // 设置合并单元格区域6 绝配原因
    CellRangeAddress mergedRegionRemark6 = new CellRangeAddress(6, 7, 2, 2);
    sheet.addMergedRegion(mergedRegionRemark6);
    Cell remarkCell6 = sheet.getRow(6).getCell(0);
    remarkCell6.setCellStyle(cellStyle);

    // 设置合并单元格区域7   工单类型
    CellRangeAddress mergedRegionRemark7 = new CellRangeAddress(8, 9, 1, 1);
    sheet.addMergedRegion(mergedRegionRemark7);
    Cell remarkCell7 = sheet.getRow(8).getCell(0);
    remarkCell7.setCellStyle(cellStyle);

    // 设置合并单元格区域7   工单类型-序号
    CellRangeAddress mergedRegionRemark7Number = new CellRangeAddress(8, 9, 0, 0);
    sheet.addMergedRegion(mergedRegionRemark7Number);
    Cell remarkCell7Number = sheet.getRow(8).getCell(0);
    remarkCell7Number.setCellStyle(cellStyle);

    // 设置合并单元格区域8   工单类型-普通工单
    CellRangeAddress mergedRegionRemark8 = new CellRangeAddress(8, 8, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark8);
    Cell remarkCell8 = sheet.getRow(8).getCell(0);
    remarkCell8.setCellStyle(cellStyle);

    // 设置合并单元格区域9   工单类型-理赔工单
    CellRangeAddress mergedRegionRemark9 = new CellRangeAddress(9, 9, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark9);
    Cell remarkCell9 = sheet.getRow(9).getCell(0);
    remarkCell9.setCellStyle(cellStyle);

    // 设置合并单元格区域10   盘点差异
    CellRangeAddress mergedRegionRemark10 = new CellRangeAddress(10, 12, 1, 1);
    sheet.addMergedRegion(mergedRegionRemark10);
    Cell remarkCell10 = sheet.getRow(10).getCell(0);
    remarkCell10.setCellStyle(cellStyle);

    // 设置合并单元格区域10   盘点差异-序号
    CellRangeAddress mergedRegionRemark10Number = new CellRangeAddress(10, 12, 0, 0);
    sheet.addMergedRegion(mergedRegionRemark10Number);
    Cell remarkCell10Number = sheet.getRow(10).getCell(0);
    remarkCell10Number.setCellStyle(cellStyle);


    // 设置合并单元格区域11   盘点差异-差异SKU数量
    CellRangeAddress mergedRegionRemark11 = new CellRangeAddress(10, 10, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark11);
    Cell remarkCell11 = sheet.getRow(10).getCell(0);
    remarkCell11.setCellStyle(cellStyle);

    // 设置合并单元格区域12   盘点差异-库存准确率
    CellRangeAddress mergedRegionRemark12 = new CellRangeAddress(11, 11, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark12);
    Cell remarkCell12 = sheet.getRow(11).getCell(0);
    remarkCell12.setCellStyle(cellStyle);

    // 设置合并单元格区域13   盘点差异-库存准确率
    CellRangeAddress mergedRegionRemark13 = new CellRangeAddress(12, 12, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark13);
    Cell remarkCell13 = sheet.getRow(12).getCell(0);
    remarkCell13.setCellStyle(cellStyle);

    // 设置合并单元格区域14   理赔承担
    CellRangeAddress mergedRegionRemark14 = new CellRangeAddress(13, 17, 1, 1);
    sheet.addMergedRegion(mergedRegionRemark14);
    Cell remarkCell14 = sheet.getRow(13).getCell(0);
    remarkCell14.setCellStyle(cellStyle);

    // 设置合并单元格区域14   理赔承担-序号
    CellRangeAddress mergedRegionRemark14Number = new CellRangeAddress(13, 17, 0, 0);
    sheet.addMergedRegion(mergedRegionRemark14Number);
    Cell remarkCell14Number = sheet.getRow(13).getCell(0);
    remarkCell14Number.setCellStyle(cellStyle);

    // 设置合并单元格区域15   理赔承担-客户承担
    CellRangeAddress mergedRegionRemark15 = new CellRangeAddress(13, 13, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark15);
    Cell remarkCell15 = sheet.getRow(13).getCell(0);
    remarkCell15.setCellStyle(cellStyle);

    // 设置合并单元格区域16   理赔承担-下游承担
    CellRangeAddress mergedRegionRemark16 = new CellRangeAddress(14, 15, 2, 2);
    sheet.addMergedRegion(mergedRegionRemark16);
    Cell remarkCell16 = sheet.getRow(14).getCell(0);
    remarkCell16.setCellStyle(cellStyle);

    // 设置合并单元格区域17   理赔承担-绝配承担
    CellRangeAddress mergedRegionRemark17 = new CellRangeAddress(16, 16, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark17);
    Cell remarkCell17 = sheet.getRow(16).getCell(0);
    remarkCell17.setCellStyle(cellStyle);

    // 设置合并单元格区域18   理赔承担-月度理赔总额
    CellRangeAddress mergedRegionRemark18 = new CellRangeAddress(17, 17, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark18);
    Cell remarkCell18 = sheet.getRow(17).getCell(0);
    remarkCell18.setCellStyle(cellStyle);

    // 设置合并单元格区域19   原因归类
    CellRangeAddress mergedRegionRemark19 = new CellRangeAddress(18, 19, 1, 1);
    sheet.addMergedRegion(mergedRegionRemark19);
    Cell remarkCell19 = sheet.getRow(18).getCell(0);
    remarkCell19.setCellStyle(cellStyle);

    // 设置合并单元格区域19   原因归类-序号
    CellRangeAddress mergedRegionRemark19Number = new CellRangeAddress(18, 19, 0, 0);
    sheet.addMergedRegion(mergedRegionRemark19Number);
    Cell remarkCell19Number = sheet.getRow(18).getCell(0);
    remarkCell19Number.setCellStyle(cellStyle);

    // 设置合并单元格区域20   原因归类-月度工单量产生比例最高
    CellRangeAddress mergedRegionRemark20 = new CellRangeAddress(18, 18, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark20);
    Cell remarkCell20 = sheet.getRow(18).getCell(0);
    remarkCell20.setCellStyle(cellStyle);

    // 设置合并单元格区域21   原因归类-单笔理赔金额最高
    CellRangeAddress mergedRegionRemark21 = new CellRangeAddress(19, 19, 2, 3);
    sheet.addMergedRegion(mergedRegionRemark21);
    Cell remarkCell21 = sheet.getRow(19).getCell(0);
    remarkCell21.setCellStyle(cellStyle);

    // 自定义列宽
    int maxLength = 50;
    if (!CollectionUtils.isEmpty(dataList)) {
        for (int i = 0; i < dataList.get(0).size(); i++) {
            sheet.setColumnWidth(i, (maxLength) * 125);
        }
    }


    // 保存文件
    try (FileOutputStream fos = new FileOutputStream(downloadBusinessFileName)) {
        workbook.write(fos);
        workbook.close();
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

6.表格样式设置

/**
     * 表格样式设置
     */
    private HorizontalCellStyleStrategy registerWriteHandler() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 头背景设置
        headWriteCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        WriteFont font = new WriteFont();
//        font.setFontName("微软雅黑");
        font.setColor(IndexedColors.WHITE.getIndex()); // 设置文本颜色为白色
        headWriteCellStyle.setWriteFont(font);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
//        contentWriteFont.setFontName("微软雅黑");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true); // 设置自动换行
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置边框
//        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
//        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);

        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
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

private List<List<String>> roateHeadFields(List<List<String>> headFields) {
        List<List<String>> result = new ArrayList<>();
        for (List<String> row : headFields) {
            for (int j = 0; j < row.size(); j++) {
                if (result.size() > j) {
                    // 往对应第j个List 添加添加值
                    result.get(j).add(row.get(j));
                } else {
                    // 分割成单个List
                    result.add(new ArrayList<>(Collections.singletonList(row.get(j))));
                }
            }
        }
        return result;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7.表头

private List<List<String>> getHeadList() {
        List<List<String>> headList = new ArrayList<>();
        List<String> titleOne = Lists.newArrayList("XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据", "XX项目管理基础数据");
        headList.add(makeUpTitle(titleOne));
        List<String> titleTwo = Lists.newArrayList("序号", "分析维度", "分析维度", "分析维度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度", "2023年时间跨度");
        headList.add(makeUpTitle(titleTwo));
        List<String> titleThree = Lists.newArrayList("序号", "一级", "二级", "三级", "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月");
        headList.add(makeUpTitle(titleThree));
        return headList;
    }
1
2
3
4
5
6
7
8
9
10

private List<String> makeUpTitle(List<String> titleList) {
        List<String> makeUpList = new ArrayList<>();
        String temp = "";
        for (String e : org.apache.commons.collections4.ListUtils.emptyIfNull(titleList)) {
            if (StringUtils.isNotEmpty(e)) {
                temp = e;
            }
            makeUpList.add(temp);
        }
        return makeUpList;
    }
1
2
3
4
5
6
7
8
9
10
11

8.行数据

private List<List<String>> getDateList() {
        List<List<String>> dataList = new ArrayList<>();
        for (int i = 0; i < 17; i++) {
            dataList.add(buildData(i));
        }
        return dataList;
    }
1
2
3
4
5
6
7

public List<String> buildData(int i) {
        List<String> list = new ArrayList<>();
        switch (i) {
            case 0:
                list.add(String.valueOf(i + 1));
                list.add("月度客诉率-%");
                list.add("月度客诉率-%");
                list.add("月度客诉率-%");
                list.add("3.01");
                return list;
            case 1:
                list.add(String.valueOf(i + 1));
                list.add("月度工单总量");
                list.add("月度工单总量");
                list.add("月度工单总量");
                list.add("10");
                return list;
            case 2:
                list.add(String.valueOf(i + 1));
                list.add("客诉类型");
                list.add("客户原因");
                list.add("客户原因");
                list.add("2");
                return list;
            case 3:
                list.add(String.valueOf(i));
                list.add("客诉类型");
                list.add("绝配原因");
                list.add("仓");
                list.add("5");
                return list;
            case 4:
                list.add(String.valueOf(i));
                list.add("客诉类型");
                list.add("绝配原因");
                list.add("配");
                list.add("1");
                return list;
            case 5:
                list.add(String.valueOf(i - 1));
                list.add("工单类型");
                list.add("普通工单");
                list.add("普通工单");
                list.add("8");
                return list;
            case 6:
                list.add(String.valueOf(i));
                list.add("工单类型");
                list.add("理赔工单");
                list.add("理赔工单");
                list.add("2");
                return list;
            case 7:
                list.add(String.valueOf(i - 2));
                list.add("盘点差异");
                list.add("差异SKU数量");
                list.add("差异SKU数量");
                list.add("13");
                return list;
            case 8:
                list.add(String.valueOf(i));
                list.add("盘点差异");
                list.add("库存准确率");
                list.add("库存准确率");
                list.add("");
                return list;
            case 9:
                list.add(String.valueOf(i));
                list.add("盘点差异");
                list.add("月度理赔金额");
                list.add("月度理赔金额");
                list.add("1500.00");
                return list;
            case 10:
                list.add(String.valueOf(i - 4));
                list.add("理赔承担");
                list.add("客户承担");
                list.add("客户承担");
                list.add("1500.00");
                return list;
            case 11:
                list.add(String.valueOf(i));
                list.add("理赔承担");
                list.add("下游承担");
                list.add("仓");
                list.add("1500.00");
                return list;
            case 12:
                list.add(String.valueOf(i));
                list.add("理赔承担");
                list.add("下游承担");
                list.add("配");
                list.add("1500.00");
                return list;
            case 13:
                list.add(String.valueOf(i));
                list.add("理赔承担");
                list.add("绝配承担");
                list.add("绝配承担");
                list.add("1500.00");
                return list;
            case 14:
                list.add(String.valueOf(i));
                list.add("理赔承担");
                list.add("月度理赔总额");
                list.add("月度理赔总额");
                list.add("1500.00");
                return list;
            case 15:
                list.add(String.valueOf(i - 8));
                list.add("原因归类");
                list.add("月度工单量产生比例最高");
                list.add("月度工单量产生比例最高");
                list.add("少货（75%）");
                return list;
            case 16:
                list.add(String.valueOf(i));
                list.add("原因归类");
                list.add("单笔理赔金额最高");
                list.add("单笔理赔金额最高");
                list.add("库存差异（30%）");
                return list;
            default:
                return null;
        }
    }
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126