注解
@ColumnWidth
1
2
3
4
5
6
7
8
9
10
11
12
13
| @Data
public class WidthAndHeightData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ColumnWidth(50)
@ExcelProperty("数字标题")
private Double doubleData;
}
|
注解使用时表头长度无法做到动态调整，只能固定设置，每次调整表头长度时只能重新修改代码
注意：@ColumnWidth最大值只能为255，超过255*256长度时会报错
查看XSSFSheet源码
类方法
AbstractHeadColumnWidthStyleStrategy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| public abstract class AbstractHeadColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head,
Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = relativeRowIndex != null && (isHead || relativeRowIndex == 0);
if (!needSetWidth) {
return;
}
Integer width = columnWidth(head, cell.getColumnIndex());
if (width != null) {
width = width * 256;
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width);
}
}
protected abstract Integer columnWidth(Head head, Integer columnIndex);
}
|
通过继承AbstractHeadColumnWidthStyleStrategy类，实现columnWidth方法获取其对应列的宽度
SimpleColumnWidthStyleStrategy
源码查看
1
2
3
4
5
6
7
8
9
10
11
12
| public class SimpleColumnWidthStyleStrategy extends AbstractHeadColumnWidthStyleStrategy {
private final Integer columnWidth;
public SimpleColumnWidthStyleStrategy(Integer columnWidth) {
this.columnWidth = columnWidth;
}
@Override
protected Integer columnWidth(Head head, Integer columnIndex) {
return columnWidth;
}
}
|
基本使用
通过registerWriteHandler设置策略方法调整每列的固定宽度
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| @Data
public class User {
@ExcelProperty(value = "用户Id")
private Integer userId;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "手机")
private String phone;
@ExcelProperty(value = "邮箱")
private String email;
@ExcelProperty(value = "创建时间")
private Date createTime;
}
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| @GetMapping("/download2")
public void download2(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user = new User();
user.setUserId(123);
user.setName("asplplplplpplplplplpl");
user.setPhone("15245413");
user.setEmail("54565454@qq.com");
user.setCreateTime(new Date());
EasyExcel.write(response.getOutputStream(), User.class)
.sheet("模板")
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
.doWrite(Arrays.asList(user));
} catch (Exception e) {
e.printStackTrace();
}
}
|
LongestMatchColumnWidthStyleStrategy
源码查看
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
| public class LongestMatchColumnWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private final Map<Integer, Map<Integer, Integer>> cache = MapUtils.newHashMapWithExpectedSize(8);
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
Head head,
Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (!needSetWidth) {
return;
}
Map<Integer, Integer> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), key -> new HashMap<>(16));
Integer columnWidth = dataLength(cellDataList, cell, isHead);
if (columnWidth < 0) {
return;
}
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
}
WriteCellData<?> cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
}
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
|
LongestMatchColumnWidthStyleStrategy是一个列宽自适应策略。当我们在写入Excel数据时，如果希望根据数据的实际长度来自适应调整列宽，就可以使用这个策略。它会遍历指定列的所有数据（包括表头），找出最长的数据，然后根据这个最长数据的长度来设定该列的宽度，确保数据在单元格内不会被截断。
根据官网介绍：这个目前不是很好用，比如有数字就会导致换行。而且长度也不是刚好和实际长度一致。 所以需要精确到刚好列宽的慎用。
基本使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| @GetMapping("/download1")
public void download1(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user = new User();
user.setUserId(123);
user.setName("asplplplplpplplplplpl");
user.setPhone("15245413");
user.setEmail("54565454@qq.com");
user.setCreateTime(new Date());
EasyExcel.write(response.getOutputStream(), User.class)
.sheet("模板")
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.doWrite(Arrays.asList(user));
} catch (Exception e) {
e.printStackTrace();
}
}
|
表头宽度工具类
仿照LongestMatchColumnWidthStyleStrategy源码自定义工具类
使用构造器传参的方式，用户可以自定义通过表头或者单元格内容长度来设置列宽，通过修改常数值和比例可以自己设置想调整的列宽
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
|
public class CustomWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
private Integer type;
private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>();
public CustomWidthStyleStrategy(Integer type) {
this.type = type;
}
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (type == 1) {
if (isHead) {
int columnWidth = cell.getStringCellValue().length();
columnWidth = Math.max(columnWidth * 2, 20);
if (columnWidth > 255) {
columnWidth = 255;
}
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
return;
}
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
WriteCellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length * 2;
case DATE:
return cellData.getDateValue().toString().length() + 1;
default:
return -1;
}
}
}
}
}
|
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
| @GetMapping("/download3")
public void download3(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user = new User();
user.setUserId(123);
user.setName("asplplplplpplplplplpl");
user.setPhone("15245413");
user.setEmail("54565454@qq.com");
user.setCreateTime(new Date());
EasyExcel.write(response.getOutputStream(), User.class)
.sheet("模板")
.registerWriteHandler(new CustomWidthStyleStrategy(1))
.doWrite(Arrays.asList(user));
} catch (Exception e) {
e.printStackTrace();
}
}
@GetMapping("/download4")
public void download4(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
User user = new User();
user.setUserId(123);
user.setName("asplplplplpplplplplpl");
user.setPhone("15245413");
user.setEmail("54565454@qq.com");
user.setCreateTime(new Date());
EasyExcel.write(response.getOutputStream(), User.class)
.sheet("模板")
.registerWriteHandler(new CustomWidthStyleStrategy(2))
.doWrite(Arrays.asList(user));
} catch (Exception e) {
e.printStackTrace();
}
}
|
运行结果
- 使用表头设置的列宽
- 使用单元格内容设置的列宽
