EasyExcel 导出 xlsx 自动列宽
参考 https://blog.csdn.net/weixin_44811578/article/details/107101248
/**
* 表头样式
*/
private static class XHorizontalCellStyleStrategy extends HorizontalCellStyleStrategy {
// 表头的策略
private static final WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 内容的策略
private static final WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
static {
// 背景设置为灰色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
headWriteFont.setFontName("Frozen");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(false);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
contentWriteFont.setFontName("Calibri");
contentWriteCellStyle.setWriteFont(contentWriteFont);
}
public XHorizontalCellStyleStrategy() {
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
super(headWriteCellStyle, contentWriteCellStyle);
}
}
/**
* 宽度自适应
*/
private static class AutoColumnWidthWriteHandler extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
// 因为在自动列宽的过程中,有些设置地方让列宽显得紧凑,所以做出了个判断
private static final int COLUMN_WIDTH = 20;
private final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(@NonNull WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, @NonNull Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
} else {
if (columnWidth < COLUMN_WIDTH) {
columnWidth = columnWidth * 2;
}
}
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, @NonNull 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;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
使用方法:
public byte[] test() {
// 使用 EasyExcel 生成 xlsx 文件
ByteArrayOutputStream ous = new ByteArrayOutputStream();
EasyExcel.write(ous)
.head(tableHead)
.excelType(ExcelTypeEnum.XLSX)
.sheet("sheet1")
.registerWriteHandler(new AutoColumnWidthWriteHandler())
.registerWriteHandler(new XHorizontalCellStyleStrategy())
.doWrite(tableData);
return ous.toByteArray();
}