EasyExcel导出excel实战(续)
本文最后更新于：2023年8月15日 晚上
接上一篇Easyexcel导出excel实战。
重复一遍需求:
假设有一批商品房,我们需要将其导出到excel上,批量维护价格和业态,再导入回系统.
| id | 业态 | 房间名称 | 面积 | 单价(元/m2) | 总价(元) |
|---|---|---|---|---|---|
| 1 | 商铺 | 万科城市之光-一期-一栋-101 | 50 | ||
| 2 | 商铺 | 万科城市之光-一期-一栋-102 | 50 | ||
| 3 | 商铺 | 万科城市之光-一期-一栋-103 | 50 | ||
| 4 | 住宅 | 万科城市之光-一期-一栋-201 | 24 | ||
| 5 | 住宅 | 万科城市之光-一期-一栋-202 | 35 | ||
| 6 | 住宅 | 万科城市之光-一期-一栋-203 | 31 |
- 其中,业态为枚举值,具体取值如下:
2
3
4
5
6
7
8
9
10
11
12
13@AllArgsConstructor
@Getter
public enum HouseTypeEnum {
RESIDENTIAL("residential", "住宅"),
PARKING("parking", "车位"),
SHOP("shop","商铺"),
;
private final String code;
private final String desc;
}
- 要求输入总价后,根据面积计算出单价;输入单价后,根据面积计算出总价. 总价=单价*面积
上一篇文章中,我们已经实现了大部分需求,还剩下最后一个小尾巴:根据单价自动计算出总价,或根据总价自动计算出单价。
公式
在excel中要实现这个功能,不用想肯定是用公式了,我们还是可以用注解+Handler的方式来解决这个问题
首先定义一个公式枚举类,用来携带相关信息
1 | |
这里我对公式模版做了一些格式上的定义,增加了两个预制的占位符
使用
$env的格式定义环境变量,如${rowNo}标识当前行号使用
${funcName(*args)}的格式来定义方法调用(注意,这里不是excel的函数,而是我自己定义的额外的方法),如${getCellAddress("<fieldName>",<rowNo>)}获取对应字段的单元格地址接下来是解析公式注解的
WriteHandler: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/**
* 前提条件:
* 1. class模式
*
* @author jingwen
*/
@Slf4j
public class FormulaCellWriteHandler extends AbstractCellWriteHandler implements SheetWriteHandler {
private static final String ENV_ROW_NO = "rowNo";
private static final Pattern PATTERN_ENV = Pattern.compile("\\$\\{([a-zA-Z]\\w+)}");
private static final Pattern PATTERN_FUNC = Pattern.compile("\\$\\{([a-zA-Z]\\w+)\\((((('[a-zA-Z]\\w+')|\\d+),?)*)\\)}");
private static final Pattern PATTERN_ARGS = Pattern.compile("('([a-zA-Z]\\w*)')|(\\d+)");
/**
* easyExcel的元数据
*/
private Map<String, ExcelContentProperty> contentPropertyMap = null;
/**
* 公式映射
* 因为不会进行删除操作,且即使更新也是用同样的数据覆盖,故可以用HashMap
*/
private final Map<String, ExcelFormula> formulaMap = new HashMap<>();
private final Function<String[], String> getCellAddressFunc = (args) -> getCellAddress(args[0], Integer.parseInt(args[1]));
private final Map<String, Function<String[], String>> functionMap = ImmutableMap.of(
"getCellAddress", getCellAddressFunc
);
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//跳过头信息,如果不是class模式,也跳过
if (!isHead && head != null && head.getFieldName() != null) {
ExcelFormula excelFormula;
if (formulaMap.containsKey(head.getFieldName())) {
excelFormula = formulaMap.get(head.getFieldName());
} else {
ExcelContentProperty excelContentProperty = contentPropertyMap.get(head.getFieldName());
excelFormula = excelContentProperty.getField().getAnnotation(ExcelFormula.class);
//为空也put一下,这样下次containsKey会返回true
formulaMap.put(head.getFieldName(), excelFormula);
}
if (excelFormula == null) {
return;
}
String formulaTemplate = excelFormula.value();
//解析公式和环境变量
String formula= parseFunctionAndExecute(formulaTemplate, cell);
cell.setCellFormula(formula);
}
}
private String parseFunctionAndExecute(String formulaTemplate, Cell cell) {
//先解析环境变量
formulaTemplate = setEnvProperties(formulaTemplate, cell);
log.info("=============开始编译公式模板: {}=============", formulaTemplate);
Matcher matcher = PATTERN_FUNC.matcher(formulaTemplate);
StringBuilder stringBuffer = new StringBuilder();
while (matcher.find()) {
String methodName = matcher.group(1);
log.info("-----------methodName:{}-----------", methodName);
Matcher argsMatcher = PATTERN_ARGS.matcher(matcher.group(2));
//一般不会有这么多参数了
List<String> args = new ArrayList<>(5);
while (argsMatcher.find()) {
//可能是字符串或者数字
String strArgument = argsMatcher.group(2);
String digitArgument = argsMatcher.group(3);
String actualArgument = strArgument != null ? strArgument : digitArgument;
log.info("arg[{}]: {}", args.size(), actualArgument);
args.add(actualArgument);
}
log.info("-----------method解析完毕-----------");
Function<String[], String> function = functionMap.get(methodName);
if (function == null) {
throw new IllegalArgumentException("找不到对应的方法:" + methodName);
}
String result = function.apply(args.toArray(new String[0]));
log.info("execute method, result: {}", result);
matcher.appendReplacement(stringBuffer, result);
}
matcher.appendTail(stringBuffer);
String formula = stringBuffer.toString();
log.info("=============公式模板解析完毕: {}=============", formula);
return formula;
}
private String setEnvProperties(String formulaTemplate, Cell cell) {
CellAddress cellAddress = cell.getAddress();
int currentRow = cellAddress.getRow() + 1;
Matcher matcher = PATTERN_ENV.matcher(formulaTemplate);
StringBuilder stringBuffer = new StringBuilder();
while (matcher.find()) {
String envName = matcher.group(1);
if (ENV_ROW_NO.equals(envName)) {
matcher.appendReplacement(stringBuffer, String.valueOf(currentRow));
} else {
throw new IllegalArgumentException("无法识别的变量");
}
}
matcher.appendTail(stringBuffer);
return stringBuffer.toString();
}
public String getCellAddress(String fieldName, int rowIndex) {
ExcelContentProperty excelContentProperty = contentPropertyMap.get(fieldName);
if (excelContentProperty == null) {
throw new IllegalArgumentException("无效的字段名:" + fieldName);
}
int columnIndex = excelContentProperty.getHead().getColumnIndex();
String columnStr = CellReference.convertNumToColString(columnIndex);
return columnStr + rowIndex;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (writeSheetHolder.getClazz() == null) {
throw new UnsupportedOperationException("只支持class模式写入");
}
initHeadMap(writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap());
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
/**
* 保存EasyExcel的模型数据
* @param excelContentPropertyMap
*/
private synchronized void initHeadMap(Map<Integer, ExcelContentProperty> excelContentPropertyMap) {
if (this.contentPropertyMap == null) {
this.contentPropertyMap = excelContentPropertyMap.values()
.stream()
.collect(Collectors.toMap(
e -> e.getHead().getFieldName(),
Function.identity()
));
}
}
} 这里我做了几件事:
- 定义了获取单元格地址的方法,同时将其封装成
Function<String[],String>的形式,注册到方法列表中,方便调用 - 在创建工作表时,将EasyExcel的模型信息保存起来,方便后续使用
- 使用Map缓存公式注解信息,以免重复调用反射来获取,这里我使用的是HashMap,能够将null值也缓存起来,避免击穿
- 使用正则表达式来解析之前定义的公式模板中的环境变量和方法
- 定义了获取单元格地址的方法,同时将其封装成
环境变量
对于${env}形式的环境变量,我们使用正则\$\{([a-zA-Z]\w+)}来解析,可以看到只有一个捕获组,比较简单
方法
方法的解析我将其分为两步
- 解析方法主体,获取方法名称及参数块,使用正则
\$\{([a-zA-Z]\w+)\((((('[a-zA-Z]\w+')|\d+),?)*)\)},这个比较复杂,主要是因为需要同时匹配正确格式的参数块
- 由参数块解析每一个参数,使用正则
('([a-zA-Z]\w*)')|(\d+)，捕获组如图
接下来试试看效果吧,修改模型类,增加公式注解:
1 | |
注册FormulaCellWriteHandler,然后导出,可以看到日志输出了解析过程。
1 | |
再来看看导出的excel
可以看到有公式了!
简化版公式
实际上,对于文中的需求,我们需要的只是获取同一行中其他单元格的值,并进行计算,并不需要用到excel自带的函数等,所以我们可以简化一下这个公式模板,直接使用<fieldName> <运算法> <fieldName>这样的格式,下面对代码改造一下.
调整公式注解,增加简化版公式的开关
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@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExcelFormula {
/**
* 对应公式模板
* 可以使用预制参数如${RowNo}表示当前行号,预制函数如${GetCellAddress("id",${RowNo})}获取单元格地址,可用的参数有
* RowNo 当前行号
*/
String value();
FormulaType type() default FormulaType.SIMPLE;
enum FormulaType {
/**
* 简单模式,不支持调用excel函数,只支持加减乘除括号
* 但可以直接使用变量名来指向该行的某个字段
*/
SIMPLE,
/**
* 复杂模式,支持excel函数调用,支持引入自定义的环境变量
*/
COMPLEX,
}
}- 在
FormulaCellWriteHandler中增加对简化版公式的支持
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@Slf4j
public class FormulaCellWriteHandler extends AbstractCellWriteHandler implements SheetWriteHandler {
private static final String ENV_ROW_NO = "rowNo";
private static final Pattern PATTERN_ENV = Pattern.compile("\\$\\{([a-zA-Z]\\w+)}");
private static final Pattern PATTERN_FUNC_COMPLEX = Pattern.compile("\\$\\{([a-zA-Z]\\w+)\\((((('[a-zA-Z]\\w+')|\\d+),?)*)\\)}");
private static final Pattern PATTERN_FUNC_SIMPLE = Pattern.compile("([a-zA-Z]\\w*)");
private static final Pattern PATTERN_ARGS = Pattern.compile("('([a-zA-Z]\\w*)')|(\\d+)");
/**
* easyExcel的元数据
*/
private Map<String, ExcelContentProperty> contentPropertyMap = null;
/**
* 公式映射
* 因为不会进行删除操作,且即使更新也是用同样的数据覆盖,故可以用HashMap
*/
private final Map<String, ExcelFormula> formulaMap = new HashMap<>();
private final Function<String[], String> getCellAddressFunc = (args) -> getCellAddress(args[0], Integer.parseInt(args[1]));
private final Map<String, Function<String[], String>> functionMap = ImmutableMap.of(
"getCellAddress", getCellAddressFunc
);
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//跳过头信息,如果不是class模式,也跳过
if (!isHead && head != null && head.getFieldName() != null) {
ExcelFormula excelFormula;
if (formulaMap.containsKey(head.getFieldName())) {
excelFormula = formulaMap.get(head.getFieldName());
} else {
ExcelContentProperty excelContentProperty = contentPropertyMap.get(head.getFieldName());
excelFormula = excelContentProperty.getField().getAnnotation(ExcelFormula.class);
//为空也put一下,这样下次containsKey会返回true
formulaMap.put(head.getFieldName(), excelFormula);
}
if (excelFormula == null) {
return;
}
String formulaTemplate = excelFormula.value();
//替换环境变量
String formula;
if (excelFormula.type() == ExcelFormula.FormulaType.COMPLEX) {
formula = parseComplexFunctionAndExecute(formulaTemplate, cell);
} else {
formula = parseSimpleFunctionAndExecute(formulaTemplate, cell);
}
cell.setCellFormula(formula);
}
}
private String parseSimpleFunctionAndExecute(String formulaTemplate, Cell cell) {
log.info("=============开始编译公式模板: {}=============", formulaTemplate);
StringBuilder stringBuffer = new StringBuilder();
Matcher matcher = PATTERN_FUNC_SIMPLE.matcher(formulaTemplate);
int currentRow = cell.getAddress().getRow() + 1;
while (matcher.find()) {
String fieldName = matcher.group(1);
String fieldCellAddress = getCellAddress(fieldName, currentRow);
log.info("-----------fieldName:{},fieldCellAddress:{}-----------", fieldName, fieldCellAddress);
matcher.appendReplacement(stringBuffer, fieldCellAddress);
}
matcher.appendTail(stringBuffer);
String formula = stringBuffer.toString();
log.info("=============公式模板解析完毕: {}=============", formula);
return formula;
}
private String parseComplexFunctionAndExecute(String formulaTemplate, Cell cell) {
formulaTemplate = setEnvProperties(formulaTemplate, cell);
log.info("=============开始编译公式模板: {}=============", formulaTemplate);
Matcher matcher = PATTERN_FUNC_COMPLEX.matcher(formulaTemplate);
StringBuilder stringBuffer = new StringBuilder();
while (matcher.find()) {
String methodName = matcher.group(1);
log.info("-----------methodName:{}-----------", methodName);
Matcher argsMatcher = PATTERN_ARGS.matcher(matcher.group(2));
//一般不会有这么多参数了
List<String> args = new ArrayList<>(5);
while (argsMatcher.find()) {
//可能是字符串或者数字
String strArgument = argsMatcher.group(2);
String digitArgument = argsMatcher.group(3);
String actualArgument = strArgument != null ? strArgument : digitArgument;
log.info("arg[{}]: {}", args.size(), actualArgument);
args.add(actualArgument);
}
log.info("-----------method解析完毕-----------");
Function<String[], String> function = functionMap.get(methodName);
if (function == null) {
throw new IllegalArgumentException("找不到对应的方法:" + methodName);
}
String result = function.apply(args.toArray(new String[0]));
log.info("execute method, result: {}", result);
matcher.appendReplacement(stringBuffer, result);
}
matcher.appendTail(stringBuffer);
String formula = stringBuffer.toString();
log.info("=============公式模板解析完毕: {}=============", formula);
return formula;
}
private String setEnvProperties(String formulaTemplate, Cell cell) {
CellAddress cellAddress = cell.getAddress();
int currentRow = cellAddress.getRow() + 1;
Matcher matcher = PATTERN_ENV.matcher(formulaTemplate);
StringBuilder stringBuffer = new StringBuilder();
while (matcher.find()) {
String envName = matcher.group(1);
if (ENV_ROW_NO.equals(envName)) {
matcher.appendReplacement(stringBuffer, String.valueOf(currentRow));
} else {
throw new IllegalArgumentException("无法识别的变量");
}
}
matcher.appendTail(stringBuffer);
return stringBuffer.toString();
}
public String getCellAddress(String fieldName, int rowIndex) {
ExcelContentProperty excelContentProperty = contentPropertyMap.get(fieldName);
if (excelContentProperty == null) {
throw new IllegalArgumentException("无效的字段名:" + fieldName);
}
int columnIndex = excelContentProperty.getHead().getColumnIndex();
String columnStr = CellReference.convertNumToColString(columnIndex);
return columnStr + rowIndex;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
if (writeSheetHolder.getClazz() == null) {
throw new UnsupportedOperationException("只支持class模式写入");
}
initHeadMap(writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap());
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
private synchronized void initHeadMap(Map<Integer, ExcelContentProperty> excelContentPropertyMap) {
if (this.contentPropertyMap == null) {
this.contentPropertyMap = excelContentPropertyMap.values()
.stream()
.collect(Collectors.toMap(
e -> e.getHead().getFieldName(),
e -> e
));
}
}
}这里我增加了判断,如果公式类型为简化版,则使用简化版的正则进行解析,简化版的正则为:
([a-zA-Z]\w*),真的非常简单= =- 修改模型类使用的公式模板
1
2
3
4
5
6
7/**
* 总价
*/
@ExcelProperty("总价(元)")
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
@ExcelFormula("=unitPrice*area")
private BigDecimal totalPrice;导出一下,可以看到日志输出解析过程:
1
2
3
4=============开始编译公式模板: =unitPrice*area=============
-----------fieldName:unitPrice,fieldCellAddress:D2-----------
-----------fieldName:area,fieldCellAddress:C2-----------
=============公式模板解析完毕: =D2*C2=============再看导出效果:
- 在
跟之前的效果是一样的.
结语
本篇EasyExcel导出excel到这里就结束了,这里我没有设计到导入,是因为目前遇到的导入场景都比较简单,官网上的demo就可以解决问题,就不搬运代码了.其实如果只是为了完成本文中的需求,也没必要搞这么多复杂的东西,但如果我们写的通用些,下次遇到类似的需求,就可以省下来一些时间摸鱼了不是。
后面列举了一些我在使用过程中遇到的坑,希望能对你有所帮助.
- 隐藏列,这个在文章中有提到过,
@ExcelProperty中的hidden参数并不能隐藏列,要隐藏某列,需要用@ColumnWidth(0)将该列的宽度设置为0 - EasyExcel解析模型类时会解析父类和子类的同名字段,所以要求父类和子类不能存在标注了
@ExcelProperty的同名字段 @ContentStyle和@HeadStyle等注解可以使用在类上或字段上,但注意,类上的和字段上的注解参数并不会合并,而是优先取字段上,字段没有则取类上的.
EasyExcel导出excel实战(续)
http://example.com/2021/06/26/java/EasyExcel导出excel实战-续/