EasyExcel动态表头实现
日常使用EasyExcel导出报表时，如果实体类字段较多，使用原始方式生成动态表头文件时会产生大量繁琐重复的代码，使用自定义注解和工具类处理可以大大简化代码开发
简单实现例子
准备一个实体类,注意@ExcelColumnIgnore注解为自定义注解，参数branchLv 表示当前机构层级
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
| @Data
public class DemoEntity{
@ExcelColumnIgnore(branchLv = 2)
@ExcelProperty(value = {"二级机构代码"})
@ApiModelProperty(value = "二级机构代码",example = "1110000")
private String organIdTwo = "-";
@ExcelColumnIgnore(branchLv = 2)
@ExcelProperty(value = {"二级机构名称"})
@ApiModelProperty(value = "二级机构名称",example = "北京市分公司")
private String organNameTwo = "-";
@ExcelColumnIgnore(branchLv = 3)
@ExcelProperty(value = {"三级机构代码"})
@ApiModelProperty(value = "三级机构代码",example = "1110100")
private String organIdThree = "-";
@ExcelColumnIgnore(branchLv = 3)
@ExcelProperty(value = {"三级机构名称"})
@ApiModelProperty(value = "三级机构名称",example = "北京市中心支公司本部")
private String organNameThree = "-";
@ExcelColumnIgnore(branchLv = 4)
@ExcelProperty(value = {"四级机构代码"})
@ApiModelProperty(value = "四级机构代码",example = "1110104")
private String organIdFour = "-";
@ExcelColumnIgnore(branchLv = 4)
@ExcelProperty(value = {"四级机构名称"})
@ApiModelProperty(value = "四级机构名称",example = "北京市朝阳区支公司")
private String organNameFour = "-";
@ExcelProperty(value = {"查询时间"})
@ApiModelProperty(value = "查询时间",example = "2023-03-01至2023-06-26")
private String queryTime = "-";
@ExcelColumnIgnore
@ExcelProperty(value = {"用户名"})
@ApiModelProperty(value = "查询时间",example = "2023-03-01至2023-06-26")
private String agentName = "-";
}
|
直接生成Excel
PS: EasyExcelUtils ExcelTitleWriteHandler 均在DEV环境，也可以从文章后看到具体实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| public static void main(String[] args) {
String title = "测试标题";
int showLv = 2;
List<String> Strings = EasyExcelUtils.getExcludeColumnFiledList(DemoEntity.class, showLv);
List<DemoEntity> list = new ArrayList<>();
list.add(new DemoEntity());
EasyExcel.write("file.xlsx", DemoEntity.class)
.excludeColumnFiledNames(Strings)
.sheet("title")
.relativeHeadRowIndex(1)
.registerWriteHandler(new ExcelTitleWriteHandler<>(title, DemoEntity.class, showLv))
.doWrite(list);
}
|
生成的结果会跟随showLv 展示层级不同出现不同的列数，实现动态表头并且跟随字段控制。
这里用到的两个自定义实现类EasyExcelUtils 、ExcelTitleWriteHandler ，和一个自定义注解@ExcelColumnIgnore 实现这个功能，背后的原理就是使用注解判断展示层级动态控制字段呈现
@ExcelColumnIgnore
1
2
3
4
5
| @Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelColumnIgnore {
int branchLv() default 1000;
}
|
ExcelColumnIgnore 是一个自定义注解，只包含一个属性字段 branchLv 也就是标识当前字段的层级，默认1000是为了实现 无参数指定下 字段排除功能，也就是 在使用ExcelColumnIgnore 不指定参数 branchLv ,这个字段也将会被排除。
EasyExcelUtils
EasyExcelUtils 是一个自定义Easyexcel处理的工具类，getExcludeColumnFiledList方法就是配合ExcelColumnIgnore注解 动态获取需要排除的字段
getExcludeColumnFiledList(Class<T> t)
第一个方法需要指定实体类的类型，这样是为了获取当前实体类的所有字段和注解，这个方法默认排除所有带有@ExcelColumnIgnore 注解的字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
public static<T> List<String> getExcludeColumnFiledList(Class<T> t){
List<String> excludeColumnFiledList = new ArrayList<>();
Field[] declaredFields = t.getDeclaredFields();
for (Field field : declaredFields) {
Annotation[] declaredAnnotations = field.getDeclaredAnnotations();
for (Annotation declaredAnnotation : declaredAnnotations) {
if(declaredAnnotation instanceof ExcelColumnIgnore){
excludeColumnFiledList.add(field.getName());
break;
}
}
}
return excludeColumnFiledList;
}
|
getExcludeColumnFiledList(Class<T> t, int showLv)
重载方法增加了showLv 参数，表示要展示的层级，会保留 @ExcelColumnIgnore 注解上 branchLv <= showLv 的字段
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
| public static<T> List<String> getExcludeColumnFiledList(Class<T> t, int showLv){
List<String> excludeColumnFiledList = new ArrayList<>();
Field[] declaredFields = t.getDeclaredFields();
for (Field field : declaredFields) {
Annotation[] declaredAnnotations = field.getDeclaredAnnotations();
for (Annotation declaredAnnotation : declaredAnnotations) {
if(declaredAnnotation instanceof ExcelColumnIgnore){
ExcelColumnIgnore annotation = field.getAnnotation(ExcelColumnIgnore.class);
int branchLv = annotation.branchLv();
if (branchLv > showLv){
excludeColumnFiledList.add(field.getName());
break;
}
}
}
}
return excludeColumnFiledList;
}
|
ExcelTitleWriteHandler
这个方法是动态生成表头的实现类, 构造方法如下
public ExcelTitleWriteHandler(String title,int column) //指定标题和列数 ExcelTitleWriteHandler(String title,Class<T> responseClass)// 指定标题和实体类（根据注解处理）public ExcelTitleWriteHandler(String title,Class<T> responseClass,int showLv)// 指定标题、实体类、展示层级 （动态控制展示层级列数）
代码如下
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
| package com.excel;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.aop.annotation.ExcelColumnIgnore;
import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
public class ExcelTitleWriteHandler<T> implements SheetWriteHandler {
private String title = "";
private int column = 0;
private Class<T> responseClass = null;
private int showLv = 0;
public ExcelTitleWriteHandler(String title,int column) {
this.title = title;
this.column = column;
}
public ExcelTitleWriteHandler(String title,Class<T> responseClass) {
this.title = title;
this.responseClass = responseClass;
}
public ExcelTitleWriteHandler(String title,Class<T> responseClass,int showLv) {
this.title = title;
this.responseClass = responseClass;
this.showLv = showLv;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 800);
Cell cell = row1.createCell(0);
cell.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
font.setFontName("宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
if(column == 0 ){
Field[] declaredFields = responseClass.getDeclaredFields();
for (Field field : declaredFields) {
Annotation[] declaredAnnotations = field.getDeclaredAnnotations();
for (Annotation declaredAnnotation : declaredAnnotations) {
if(declaredAnnotation instanceof ExcelProperty)
column++;
}
}
for (Field declaredField : declaredFields) {
Annotation[] declaredAnnotations = declaredField.getDeclaredAnnotations();
for (Annotation declaredAnnotation : declaredAnnotations) {
if(declaredAnnotation instanceof ExcelColumnIgnore) {
ExcelColumnIgnore annotation = declaredField.getAnnotation(ExcelColumnIgnore.class);
if (showLv == 0){
column--;
} else if (showLv > 0 && annotation.branchLv() > showLv){
column--;
}
}
}
}
}
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, column-1));
}
}
|
原理大同小异，配合注解可以动态处理字段的展示，从而获取准确的标题表头
使用建议
明细表
明细表因为没有动态展示的功能,一般不需要使用@ExcelColumnIgnore注解，如果要使用@ExcelColumnIgnore时也不需要指定参数branchLv
步骤建议
- 获取导出实体类列表
- 导出时使用
ExcelTitleWriteHandler 动态生成表头，传入 （title class ），即可计算列数，或指定column 参数
统计表
统计表大部分需要根据不同机构层级展示，在实体类 中需要在各层级字段使用@ExcelColumnIgnore注解，并标注准确的showLv参数，
步骤建议
- 实体类各层级字段使用
@ExcelColumnIgnore 注解 二级机构 2、三级机构 3 、依次类推
- 获取导出实体类列表
- 使用
EasyExcelUtils getExcludeColumnFiledList(Class<T> t, int showLv) 方法获取动态排除字段，注意传入展示层级
- 生成表头时
ExcelTitleWriteHandler 使用public ExcelTitleWriteHandler(String title,Class<T> responseClass,int showLv) 构造方法，指定实体类和展示层级
