相关文章链接
注解
@ColumnWidth
@Data
public class WidthAndHeightData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ColumnWidth(50)
@ExcelProperty("数字标题")
private Double doubleData;
}
注解使用时表头长度无法做到动态调整，只能固定设置，每次调整表头长度时只能重新修改代码
注意：@ColumnWidth最大值只能为255，超过255*256长度时会报错
查看XSSFSheet源码
类方法
AbstractHeadColumnWidthStyleStrategy
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
源码查看
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设置策略方法调整每列的固定宽度
@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;
}
@GetMapping("/download2")
public void download2(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
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("[email protected]");
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
源码查看
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;
}
// 超过最大值255时则设置为255
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数据时，如果希望根据数据的实际长度来自适应调整列宽，就可以使用这个策略。它会遍历指定列的所有数据（包括表头），找出最长的数据，然后根据这个最长数据的长度来设定该列的宽度，确保数据在单元格内不会被截断。
根据官网介绍：这个目前不是很好用，比如有数字就会导致换行。而且长度也不是刚好和实际长度一致。 所以需要精确到刚好列宽的慎用。
基本使用
@GetMapping("/download1")
public void download1(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
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("[email protected]");
user.setCreateTime(new Date());
EasyExcel.write(response.getOutputStream(), User.class)
.sheet("模板")
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.doWrite(Arrays.asList(user));
} catch (Exception e) {
e.printStackTrace();
}
}
表头宽度工具类
仿照LongestMatchColumnWidthStyleStrategy源码自定义工具类
使用构造器传参的方式，用户可以自定义通过表头或者单元格内容长度来设置列宽，通过修改常数值和比例可以自己设置想调整的列宽
/**
* 表头宽度根据表头或数据内容自适应
*/
public class CustomWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
/**
* 1-根据表头宽度，2-根据单元格内容
*/
private Integer type;
private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>();
public CustomWidthStyleStrategy(Integer type) {
this.type = type;
}
/**
* 设置列宽
*
* @param writeSheetHolder 写入Sheet的持有者
* @param cellDataList 当前列的单元格数据列表
* @param cell 当前单元格
* @param head 表头
* @param relativeRowIndex 当前行的相对索引
* @param isHead 是否为表头
*/
@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);
}
}
}
}
/**
* 数据长度
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
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;
}
}
}
}
}
@GetMapping("/download3")
public void download3(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
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("[email protected]");
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");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
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("[email protected]");
user.setCreateTime(new Date());
EasyExcel.write(response.getOutputStream(), User.class)
.sheet("模板")
.registerWriteHandler(new CustomWidthStyleStrategy(2))
.doWrite(Arrays.asList(user));
} catch (Exception e) {
e.printStackTrace();
}
}
运行结果
- 使用表头设置的列宽
- 使用单元格内容设置的列宽
