最近项目中用到了阿里的EasyExcel，一个简单方便读写excel的工具。
需求场景
在项目中会定义一些枚举类来保存一些状态值，在存储进数据库时保存的是code,而我们在生成excel时需要展示对应的值。
代码实现
原
需要写入excel的对象
1
2
3
4
5
6
7
8
9
| @Data
public class TestExcel {
@ExcelProperty(value = "状态",converter = StatusEnumConvert.class)
private String status;
}
|
枚举类
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
| public enum StatusEnum {
WAIT_COMMIT("1","待提交"),
WAIT_AUDIT("2","待审核"),
AUDITED("3","已审核"),
RETURNED("4","已退回");
private final String code;
private final String info;
StatusEnum(String code, String info)
{
this.code = code;
this.info = info;
}
public String getCode()
{
return code;
}
public String getInfo()
{
return info;
}
public static String getInfoByCode(String code){
for (StatusEnum statusEnum : StatusEnum.values()) {
if (statusEnum.getCode().equals(code)){
return statusEnum.getInfo();
}
}
return null;
}
public static String getCodeByInfo(String info){
for (StatusEnum statusEnum : StatusEnum.values()) {
if (statusEnum.getInfo().equals(info)){
return statusEnum.getCode();
}
}
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
| public class StatusEnumConvert implements Converter<String> {
@Override
public Class supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String info = cellData.toString();
return StatusEnum.getCodeByInfo(info);
}
@Override
public CellData convertToExcelData(String s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new CellData(Objects.requireNonNull(StatusEnum.getInfoByCode(s)));
}
}
|
应网友反馈，使用注解和反射改进了转换器，提高复用性；
改进后
先自定义一个注解
1
2
3
4
5
| @Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TargetEnum {
Class<? extends Enum> targetEnum();
}
|
需要转换的枚举类
不再需要getCodeByInfo()与getInfoById()方法
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
| public enum StatusEnum implements GetEnum {
WAIT_COMMIT("1","待提交"),
WAIT_AUDIT("2","待审核"),
AUDITED("3","已审核"),
RETURNED("4","已退回");
private final String code;
private final String info;
StatusEnum(String code, String info)
{
this.code = code;
this.info = info;
}
public String getCode()
{
return code;
}
public String getInfo()
{
return info;
}
}
|
需要写入excel的对象
1
2
3
4
5
6
7
| @Data
public class TestExcel {
@TargetEnum(targetEnum = StatusEnum.class)
@ExcelProperty(value = "状态",converter = StatusEnumConverter.class)
private String status;
}
|
修改后的转换器
利用反射获取字段上注解的枚举类型，然后调用get..()方法取值
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
| public class StatusEnumConverter implements Converter<String> {
@Override
public Class supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String code="";
String info = cellData.toString();
Field field = excelContentProperty.getField();
TargetEnum annotation = field.getDeclaredAnnotation(TargetEnum.class);
Class clazz = annotation.targetEnum();
Object[] enumConstants = clazz.getEnumConstants();
Method getInfo = clazz.getMethod("getInfo");
Method getCode = clazz.getMethod("getCode");
for (Object enumConstant : enumConstants) {
if (getInfo.invoke(enumConstant, null).equals(info)) {
code = (String) getCode.invoke(enumConstant, null);
break;
}
}
return code;
}
@Override
public CellData convertToExcelData(String s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String info = "";
Field field = excelContentProperty.getField();
TargetEnum annotation = field.getDeclaredAnnotation(TargetEnum.class);
Class clazz = annotation.targetEnum();
Object[] enumConstants = clazz.getEnumConstants();
Method getInfo = clazz.getMethod("getInfo");
Method getCode = clazz.getMethod("getCode");
for (Object enumConstant : enumConstants) {
if (getCode.invoke(enumConstant, null).equals(s)) {
info = (String) getInfo.invoke(enumConstant, null);
break;
}
}
return new CellData(info);
}
}
|
调用
1
| EasyExcel.write(filePath,TestExcel.class).sheet("枚举转换器").doWrite(dataList());
|
效果
