说在前面
封装的easyexcel，基于注解实现excel的导入导出，以场景来说，就是你有一个现成的分页接口或者一个list接口，只需要添加几个简单的注解，就可以实现excel的导出，也是为了方便有模板生成代码的情况下直接生成导出功能。
这是封装的依赖库源码：https://github.com/chenqi92/allbs-excel
这是这个依赖库的使用示例：https://github.com/chenqi92/allbs-excel-test
依赖库运行后在浏览器中打开：http://localhost:8080/ 即可测试各种示例，参照示例进行使用可以不用看后续的使用说明。
前面三篇功能点较为分散，没有特意合并测试，想了想，如果真要那么复杂的表格设置，貌似写模板最方便。
使用说明
添加maven依赖
1
2
3
4
5
| <dependency>
<groupId>cn.allbs</groupId>
<artifactId>allbs-excel</artifactId>
<version>3.0.0</version>
</dependency>
|
基本导出
第一个导出我贴比较详细点的代码，后续就直接说需要添加的内容了。
假设你原本有一个这样的接口
1
2
3
4
| @GetMapping("/simple")
public List<UserDTO> simpleExport(@RequestParam(defaultValue = "10") int count) {
return testDataService.generateUsers(count);
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| @Data
public class UserDTO {
@NotNull(message = "用户ID不能为空")
private Long id;
@NotBlank(message = "用户名不能为空")
@Size(min = 2, max = 20, message = "用户名长度必须在2-20之间")
private String username;
@Email(message = "邮箱格式不正确")
private String email;
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
private Integer age;
private String status;
}
|
那么将这个接口变成导出接口，可以增加这些注解：
@ExportExcel,@ExcelProperty就可以变成一个导出接口。
上面的代码就变成了：
1
2
3
4
5
6
7
8
9
| @GetMapping("/simple")
@ExportExcel(
name = "用户列表",
sheets = @Sheet(sheetName = "用户信息")
)
public List<UserDTO> simpleExport(@RequestParam(defaultValue = "10") int count) {
return testDataService.generateUsers(count);
}
|
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
| @Data
public class UserDTO {
@ExcelProperty(value = "用户ID", index = 0)
@NotNull(message = "用户ID不能为空")
private Long id;
@ExcelProperty(value = "用户名", index = 1)
@NotBlank(message = "用户名不能为空")
@Size(min = 2, max = 20, message = "用户名长度必须在2-20之间")
private String username;
@ExcelProperty(value = "邮箱", index = 2)
@Email(message = "邮箱格式不正确")
private String email;
@ExcelProperty(value = "创建时间", index = 3)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
@ExcelProperty(value = "年龄", index = 4)
private Integer age;
@ExcelProperty(value = "状态", index = 5)
private String status;
}
|
导出的效果为：
这是一个最简单的用法，基本上实际业务有这样的也就可以了。
空表头导出
如果实际情况中你的返回结果列表中没有数据，上面的代码不足以导出一个文件，需要添加一个属性clazz = UserDTO.class，那么需要修改一下controller注解为
1
2
3
4
5
6
7
8
9
10
11
12
| @GetMapping("/empty")
@ExportExcel(
name = "空用户列表",
sheets = @Sheet(
sheetName = "用户信息",
// 指定数据类型用于生成表头
clazz = UserDTO.class
)
)
public List<UserDTO> emptyExport() {
return Collections.emptyList();
}
|
只导出添加了@ExcelProperty注解的字段
当前的实现为如果你的返回结果类中字段没有添加@ExcelProperty注解，那么导出到excel中列名实际上是字段名，那么如何让导出时只展示添加了注解的字段呢？那就是添加onlyExcelProperty = true属性，同时这种方式也可以避免添加过多的@ExcelIgnore。
1
2
3
4
5
6
7
8
9
10
| @GetMapping("/only-annotated")
@ExportExcel(
name = "用户列表-仅注解字段",
sheets = @Sheet(sheetName = "用户信息"),
// 只导出有 @ExcelProperty 注解的字段
onlyExcelProperty = true
)
public List<UserDTO> onlyAnnotatedExport(@RequestParam(defaultValue = "10") int count) {
return testDataService.generateUsers(count);
}
|
多 Sheet 导出
上述内容讲的都是单个sheet导出，实际业务过程肯定会有多个sheet的导出，以下为示例：
实际上就是设置了多个sheet的名称，并返回对于数量的列表。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| @GetMapping("/multi-sheet")
@ExportExcel(
name = "综合报表",
sheets = {
@Sheet(sheetName = "用户信息", clazz = UserDTO.class),
@Sheet(sheetName = "订单信息", clazz = OrderDTO.class)
})
public List<List<?>> multiSheetExport(
@RequestParam(defaultValue = "10") int userCount,
@RequestParam(defaultValue = "20") int orderCount
) {
List<UserDTO> users = testDataService.generateUsers(userCount);
List<OrderDTO> orders = testDataService.generateOrders(orderCount);
return Arrays.asList(users, orders);
}
|
导出效果：
设定表格的列宽和行高
通过注解@ContentRowHeight(25)和@ColumnWidth实现
代码示例：
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
| @Data
@ContentRowHeight(25)
public class ProductDTO {
@ExcelProperty(value = "产品ID", index = 0)
@ColumnWidth(10)
private Long id;
@ExcelProperty(value = "产品名称", index = 1)
@ColumnWidth(20)
private String name;
@ExcelProperty(value = "产品分类", index = 2)
@ColumnWidth(15)
private String category;
@ExcelProperty(value = "价格", index = 3)
@ColumnWidth(15)
private BigDecimal price;
@ExcelProperty(value = "库存", index = 4)
@ColumnWidth(10)
private Integer stock;
@ExcelProperty(value = "产品描述", index = 5)
@ColumnWidth(50)
private String description;
}
|
实现效果：
自动行号
实际上没啥用的功能，就是多一列序号。
通过添加注解@ExcelLine实现
代码示例：
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
| @Data
@NoArgsConstructor
@AllArgsConstructor
public class RowNumberDTO {
@ExcelLine
@ExcelProperty(value = "行号", index = 0)
private Long rowNum;
@ExcelProperty(value = "产品名称", index = 1)
private String productName;
@ExcelProperty(value = "产品代码", index = 2)
private String productCode;
@ExcelProperty(value = "价格", index = 3)
private Double price;
@ExcelProperty(value = "库存", index = 4)
private Integer stock;
@ExcelProperty(value = "分类", index = 5)
private String category;
public RowNumberDTO(String productName, String productCode, Double price, Integer stock, String category) {
this.productName = productName;
this.productCode = productCode;
this.price = price;
this.stock = stock;
this.category = category;
}}
|
数据验证功能
限制单元格中的内容，作为导入模板还是挺有用的。
代码示例：
1
2
3
4
5
6
7
8
9
| @GetMapping("/validation")
@ExportExcel(
name = "员工信息-数据验证",
sheets = @Sheet(sheetName = "员工信息"),
writeHandler = {ExcelValidationWriteHandler.class}
)
public List<DataValidationDTO> validationExport(@RequestParam(defaultValue = "10") int count) {
return testDataService.generateDataValidationData(count);
}
|
如果是要设置指定返回行进行验证可以这么写（1~1000行会有验证，其他行不会）：
1
2
3
4
5
6
7
8
9
10
11
12
| @GetMapping("/validation-custom")
@ExportExcel(
name = "员工信息-自定义验证",
sheets = @Sheet(
sheetName = "员工信息",
validationStartRow = 1,
validationEndRow = 1000
)
)
public List<DataValidationDTO> validationCustomExport(@RequestParam(defaultValue = "10") int count) {
return testDataService.generateDataValidationData(count);
}
|
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
| @Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DataValidationDTO {
@ExcelProperty(value = "姓名", index = 0)
@ExcelValidation(type = ValidationType.TEXT_LENGTH, minLength = 2, maxLength = 10, errorMessage = "姓名长度必须在2-10个字符之间", promptMessage = "请输入2-10个字符的姓名", showPromptBox = true)
private String name;
@ExcelProperty(value = "性别", index = 1)
@ExcelValidation(type = ValidationType.LIST, options = { "男", "女" }, errorMessage = "性别只能选择：男、女", promptMessage = "请选择性别", showPromptBox = true)
private String gender;
@ExcelProperty(value = "年龄", index = 2)
@ExcelValidation(type = ValidationType.INTEGER, min = 18, max = 65, errorMessage = "年龄必须在18-65之间", promptMessage = "请输入18-65之间的整数", showPromptBox = true)
private Integer age;
@ExcelProperty(value = "工资", index = 3)
@ExcelValidation(type = ValidationType.DECIMAL, min = 3000.0, max = 50000.0, errorMessage = "工资必须在3000-50000之间", promptMessage = "请输入3000-50000之间的数值", showPromptBox = true)
private Double salary;
@ExcelProperty(value = "入职日期", index = 4)
@ExcelValidation(type = ValidationType.DATE, dateFormat = "yyyy-MM-dd", errorMessage = "请输入有效的日期格式", promptMessage = "请输入日期，格式：yyyy-MM-dd", showPromptBox = true)
private LocalDate hireDate;
@ExcelProperty(value = "部门", index = 5)
@ExcelValidation(type = ValidationType.LIST, options = { "技术部", "销售部", "人事部", "财务部", "运营部" }, errorMessage = "请从下拉列表中选择部门", promptMessage = "请选择部门", showPromptBox = true)
private String department;
@ExcelProperty(value = "职位", index = 6)
@ExcelValidation(type = ValidationType.LIST, options = { "初级工程师", "中级工程师", "高级工程师", "技术经理", "技术总监" }, errorMessage = "请从下拉列表中选择职位", promptMessage = "请选择职位", showPromptBox = true)
private String position;
@ExcelProperty(value = "工作年限", index = 7)
@ExcelValidation(type = ValidationType.INTEGER, min = 0, max = 50, errorMessage = "工作年限必须大于等于0", promptMessage = "请输入工作年限（年）", showPromptBox = true)
private Integer workYears;
@ExcelProperty(value = "绩效评分", index = 8)
@ExcelValidation(type = ValidationType.DECIMAL, min = 0.0, max = 10.0, errorMessage = "绩效评分必须在0-10之间", promptMessage = "请输入0-10之间的评分", showPromptBox = true)
private Double performanceScore;
@ExcelProperty(value = "邮箱", index = 9)
@ExcelValidation(type = ValidationType.ANY, promptMessage = "请输入有效的邮箱地址，例如：example@company.com", showPromptBox = true, showErrorBox = false)
private String email;
}
|
实际的效果如图：
条件样式
根据单元格值自动应用不同样式，不同的颜色、不同字体等，比如以学生分数90分以上一个颜色，60-90一个颜色，60以下一个颜色。注意下方的注解ConditionalStyle的用法。
代码示例：
1
2
3
4
5
6
7
8
9
10
11
| @GetMapping("/conditional-style")
@ExportExcel(
name = "条件样式示例",
sheets = @Sheet(sheetName = "条件样式示例"),
writeHandler = {ConditionalStyleWriteHandler.class}
)
public List<ConditionalStyleDTO> conditionalStyleExport(
@RequestParam(defaultValue = "20") int count
) {
return testDataService.generateConditionalStyleData(count);
}
|
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
| @Data
@NoArgsConstructor
@AllArgsConstructor
public class ConditionalStyleDTO {
@ExcelProperty("学生姓名")
private String studentName;
@ExcelProperty("考试分数")
@ConditionalStyle(conditions = { @Condition(value = ">=90", style = @CellStyleDef(backgroundColor = "#00FF00", // 绿色
bold = true)), @Condition(value = ">=60", style = @CellStyleDef(backgroundColor = "#FFFF00" // 黄色
)), @Condition(value = "<60", style = @CellStyleDef(backgroundColor = "#FF0000", // 红色
fontColor = "#FFFFFF")) })
private Integer score;
@ExcelProperty("任务状态")
@ConditionalStyle(conditions = {
@Condition(value = "已完成", style = @CellStyleDef(backgroundColor = "#00FF00", fontColor = "#FFFFFF")),
@Condition(value = "进行中", style = @CellStyleDef(backgroundColor = "#FFFF00")),
@Condition(value = "已取消", style = @CellStyleDef(backgroundColor = "#808080", fontColor = "#FFFFFF")) })
private String status;
@ExcelProperty("销售额")
@ConditionalStyle(conditions = {
@Condition(value = ">=10000", style = @CellStyleDef(backgroundColor = "#FFD700", // 金色
bold = true)),
@Condition(value = ">=5000", style = @CellStyleDef(backgroundColor = "#87CEEB" // 天蓝色
)) })
private BigDecimal salesAmount;
@ExcelProperty("等级")
@ConditionalStyle(conditions = {
@Condition(value = "regex:^A.*", style = @CellStyleDef(backgroundColor = "#00FF00", bold = true)),
@Condition(value = "regex:^B.*", style = @CellStyleDef(backgroundColor = "#FFFF00")),
@Condition(value = "regex:^C.*", style = @CellStyleDef(backgroundColor = "#FFA500")) // 橙色
})
private String grade;
}
|
导出示例：
导出数据字典转换
实际业务开发过程中肯定无法避免字段以tinyint储存或者有枚举的情况，那么如何处理这种情况？
如下是直接塞入sex和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
| @GetMapping("/export")
@ExportExcel(
name = "字典转换示例",
sheets = @Sheet(sheetName = "用户信息")
)
public List<DictExampleDTO> exportDictExample(@RequestParam(defaultValue = "10") int count) {
List<DictExampleDTO> list = new ArrayList<>();
Random random = new Random();
String[] departments = {"技术部", "市场部", "人事部", "财务部", "运营部"};
String[] usernames = {"张三", "李四", "王五", "赵六", "钱七", "孙八", "周九", "吴十"};
for (int i = 1; i <= count; i++) {
DictExampleDTO dto = new DictExampleDTO();
dto.setId((long) i);
dto.setUsername(usernames[random.nextInt(usernames.length)] + i);
dto.setSex(String.valueOf(random.nextInt(3)));
dto.setStatus(String.valueOf(random.nextInt(3)));
dto.setDepartment(departments[random.nextInt(departments.length)]);
dto.setCreateTime(LocalDateTime.now().minusDays(random.nextInt(365)));
dto.setRemark("这是测试数据 " + i);
list.add(dto);
}
return list;
}
|
处理方式为在ExcelProperty中添加属性converter = DictConverter.class并额外添加注解@ExcelDict(dictType = "sys_user_sex")标明用的是哪个字典。
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
| @Data
public class DictExampleDTO {
@ExcelProperty(value = "用户ID", index = 0)
private Long id;
@ExcelProperty(value = "用户名", index = 1)
private String username;
@ExcelProperty(value = "性别", index = 2, converter = DictConverter.class)
@ExcelDict(dictType = "sys_user_sex")
private String sex;
@ExcelProperty(value = "状态", index = 3, converter = DictConverter.class)
@ExcelDict(dictType = "sys_user_status")
private String status;
@ExcelProperty(value = "部门", index = 4)
private String department;
@ExcelProperty(value = "创建时间", index = 5)
private LocalDateTime createTime;
@ExcelProperty(value = "备注", index = 6)
private String remark;
}
|
这个DictConverter为封装的转换器，同时需要有对应的service实现。示例代码：
static中就是你所需实际项目中的字典值，需要将注解的dictType字段和下面key进行对应。
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
| @Service
public class DictServiceImpl implements DictService {
private static final Map<String, Map<String, String>> DICT_DATA = new HashMap<>();
static {
Map<String, String> sexDict = new HashMap<>();
sexDict.put("0", "女");
sexDict.put("1", "男");
sexDict.put("2", "未知");
DICT_DATA.put("sys_user_sex", sexDict);
Map<String, String> statusDict = new HashMap<>();
statusDict.put("0", "正常");
statusDict.put("1", "禁用");
statusDict.put("2", "锁定");
DICT_DATA.put("sys_user_status", statusDict);
}
@Override
public String getLabel(String dictType, String dictValue) {
Map<String, String> dict = DICT_DATA.get(dictType);
if (dict != null) {
return dict.get(dictValue);
}
return dictValue;
}
@Override
public String getValue(String dictType, String dictLabel) {
Map<String, String> dict = DICT_DATA.get(dictType);
if (dict != null) {
for (Map.Entry<String, String> entry : dict.entrySet()) {
if (entry.getValue().equals(dictLabel)) {
return entry.getKey();
}
}
}
return dictLabel;
}
}
|
带图片的导出
导出肯定会碰到有图片导出的情况，这种情况也是支持的。主要通过@ExcelImage注解实现。如下代码使用的是生成的base64图片，当然本地图片和图片链接也是支持的。支持给图片设置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
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
| @GetMapping("/export")
@ExportExcel(name = "商品图片列表", sheets = @Sheet(sheetName = "商品信息", clazz = ProductImageDTO.class))
public List<ProductImageDTO> exportWithImages() {
List<ProductImageDTO> products = new ArrayList<>();
byte[] redImage = generateColorSquare(120, 120, 255, 0, 0);
byte[] greenImage = generateColorSquare(80, 80, 0, 255, 0);
byte[] blueImage = generateColorSquare(100, 100, 0, 0, 255);
ProductImageDTO product1 = new ProductImageDTO();
product1.setId(1L);
product1.setName("iPhone 15 Pro");
product1.setPrice(new BigDecimal("7999.00"));
product1.setMainImage("data:image/png;base64," + Base64.getEncoder().encodeToString(redImage));
product1.setThumbnail(greenImage);
product1.setImageList(Arrays.asList("data:image/png;base64," + Base64.getEncoder().encodeToString(blueImage),
"data:image/png;base64," + Base64.getEncoder().encodeToString(redImage)));
product1.setStock(100);
product1.setDescription("最新款 iPhone 15 Pro，A17 Pro 芯片");
products.add(product1);
ProductImageDTO product2 = new ProductImageDTO();
product2.setId(2L);
product2.setName("MacBook Pro 16");
product2.setPrice(new BigDecimal("19999.00"));
product2.setMainImage("data:image/png;base64," + Base64.getEncoder().encodeToString(blueImage));
product2.setThumbnail(redImage);
product2.setImageList(Arrays.asList("data:image/png;base64," + Base64.getEncoder().encodeToString(greenImage),
"data:image/png;base64," + Base64.getEncoder().encodeToString(blueImage)));
product2.setStock(50);
product2.setDescription("16 英寸 MacBook Pro，M3 Max 芯片");
products.add(product2);
byte[] largeImage = generateColorSquare(150, 150, 255, 165, 0);
ProductImageDTO product3 = new ProductImageDTO();
product3.setId(3L);
product3.setName("iPad Air");
product3.setPrice(new BigDecimal("4799.00"));
product3.setMainImage("data:image/png;base64," + Base64.getEncoder().encodeToString(largeImage));
product3.setThumbnail(greenImage);
product3.setImageList(Collections.singletonList(
"data:image/png;base64," + Base64.getEncoder().encodeToString(redImage)));
product3.setStock(200);
product3.setDescription("11 英寸 iPad Air，M2 芯片");
products.add(product3);
log.info("Exporting {} products with images", products.size());
return products;
}
|
话不多说，都在代码里：
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
| @Data
@NoArgsConstructor
@AllArgsConstructor
public class ProductImageDTO {
@ExcelProperty("商品ID")
private Long id;
@ExcelProperty("商品名称")
private String name;
@ExcelProperty("商品价格")
private BigDecimal price;
@ExcelProperty("商品主图")
@ExcelImage(width = 120, height = 120)
private String mainImage;
@ExcelProperty("商品缩略图")
@ExcelImage(width = 80, height = 80, type = ExcelImage.ImageType.BYTES)
private byte[] thumbnail;
@ExcelProperty(value = "商品图集", converter = cn.allbs.excel.convert.ImageListConverter.class)
@ExcelImage(width = 100, height = 100)
private List<String> imageList;
@ExcelProperty("库存数量")
private Integer stock;
@ExcelProperty("商品描述")
private String description;
}
|
篇幅有限，有兴趣可以继续看下一篇中更多的应用方式，有些还是挺实用的。下下篇就没啥用处了，下下下篇可以看看。
