前言
有个需求，需要设置Excel导出的样式，样式如下图所示，有三个表头行，第一个表头行需要加粗和灰色背景，另外两个表头行使用另外的样式，并且当测试结果单元格出现x或者未通过的时候，设置其为红色字体。
实现步骤
写入ExcelSheet的部分代码
for (Map.Entry<String, List<ExcelDTO>> entry : entries) {
String excelFileName = entry.getKey() + ".xlsx";
File tempFile = File.createTempFile(excelTempFolderPath + entry.getKey(), ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(tempFile).build();
WriteSheet writeSheet = EasyExcel.
writerSheet("sheet")
.head(ReceiveCardExcelDTO.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new CustomWriteHandler("测试报告"))
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(getHeadStyleStrategy())
.build();
writeSheet.setRelativeHeadRowIndex(1);
List<List<String>> headTitles = Lists.newArrayList();
// 固定title
headTitles.add(Lists.newArrayList("工单号", "卡信息"));
headTitles.add(Lists.newArrayList(entry.getKey(), "升级固件"));
headTitles.add(Lists.newArrayList(entry.getKey(), "SDRAM"));
headTitles.add(Lists.newArrayList("型号", "HUB"));
headTitles.add(Lists.newArrayList(receiveCardOrderMap.get(entry.getValue().get(0).getMark()).getReceiveCardType(), "网口"));
headTitles.add(Lists.newArrayList("测试时间", "测试结果"));
headTitles.add(Lists.newArrayList(cn.hutool.core.date.DateUtil.format(entry.getValue().get(0).getTestTime(), "yyyy-MM-dd HH:mm:ss"), "详情"));
writeSheet.setHead(headTitles);
excelWriter.write(entry.getValue(), writeSheet);
excelWriter.finish();
重点是如下几个handler，下面会进行讲解
WriteSheet writeSheet = EasyExcel.
writerSheet("sheet")
.head(ReceiveCardExcelDTO.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new CustomWriteHandler("测试报告"))
.registerWriteHandler(new CustomCellWriteHandler())
.registerWriteHandler(getHeadStyleStrategy())
.build();
测试报告标题行的实现
这里对应的是CustomWriteHandler类
/********************************
* @interface : CustomWriteHandler
* @function : 添加excel head额外内容
* @date : 2024.04.23 11:13
* @author : zh
********************************/
public class CustomWriteHandler implements SheetWriteHandler {
private String title;
public CustomWriteHandler(String title) {
this.title = title;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheet(writeSheetHolder.getSheetName());
Row row1 = sheet.getRow(0);
if (row1 == null) {
row1 = sheet.createRow(0);
}
row1.setHeight((short) 500);
Cell cell1 = row1.getCell(0);
if (cell1 == null) {
cell1 = row1.createCell(0);
}
cell1.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 220);
font.setFontName("宋体");
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 6));
CellStyle headStyle = workbook.createCellStyle();
headStyle.setFont(font);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置表头背景色为灰色
headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
CellStyle contentStyle = workbook.createCellStyle();
contentStyle.setAlignment(HorizontalAlignment.CENTER);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setBorderTop(BorderStyle.THIN);
contentStyle.setBorderBottom(BorderStyle.THIN);
contentStyle.setBorderLeft(BorderStyle.THIN);
contentStyle.setBorderRight(BorderStyle.THIN);
for (Row row : sheet) {
if (row.getRowNum() == 0) {
for (Cell cell : row) {
// 表头
cell.setCellStyle(headStyle);
}
break;
}
}
}
}
在这里register并填入相关的标题，到此第一行标题头设置完毕。
这里其实就是相当于这行标题是独立开来设置的，先预留了第一行的位置，然后后续把标题插入这行，后面再针对这行设置独立的样式。
其他标题行的设置
可以看到每个list都有两个元素
List<List<String>> headTitles = Lists.newArrayList();
// 固定title
headTitles.add(Lists.newArrayList("工单号", "卡信息"));
headTitles.add(Lists.newArrayList(entry.getKey(), "升级固件"));
headTitles.add(Lists.newArrayList(entry.getKey(), "SDRAM"));
headTitles.add(Lists.newArrayList("型号", "HUB"));
对比excel标题
可以看到其实每个list都是两行标题竖着的两个内容，第一个列标题两行就分别是工单号，卡信息；第二列因为涉及到合并，所以出现了两个entry.getKey()对应不同的两个内容（升级固件和SDRAM)，依次类推；
标题行样式的设定
对应这个registerWriteHandler(getHeadStyleStrategy())
调getHeadStyleStrategy()方法，返回了一个策略，这里只设置了header的样式
public static HorizontalCellStyleStrategy getHeadStyleStrategy() {
// 头的策略 样式调整
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(false);
headWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return new HorizontalCellStyleStrategy(headWriteCellStyle, (List<WriteCellStyle>) null);
}
特定单元格样式的修改
对应了这个.registerWriteHandler(new CustomCellWriteHandler())
/********************************
* @interface : CustomCellWriteHandler
* @function : 处理excel特定内容格式
* @date : 2024.04.23 11:13
* @author : zh
********************************/
public class CustomCellWriteHandler implements CellWriteHandler {
Map<String,CellStyle> cellStyleMap = new HashMap<>();
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
int rowIndex = cell.getRowIndex();
int cellIndex = cell.getColumnIndex();
// 自定义样式处理
// 当前事件会在 数据设置到poi的cell里面才会回调
// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
if (BooleanUtils.isNotTrue(context.getHead())) {
if (cell.getStringCellValue().contains("×") || cell.getStringCellValue().contains("未通过")) {
CellStyle cellStyle = null;
if (cellStyleMap.get("red") != null) {
cellStyle = cellStyleMap.get("red");
} else {
// 拿到poi的workbook
Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
// 这里能复用的地方缓存起来 一个表格最多创建6W个样式
// 不同单元格传同一个cellStyle
cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
cellStyle.setFont(font);
cellStyleMap.put("red", cellStyle);
}
cell.setCellStyle(cellStyle);
// 由于这里没有指定dataformat 最后展示的数据 格式可能会不太正确
// 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到
// cell里面去 会导致自己设置的不一样
context.getFirstCellData().setWriteCellStyle(null);
}
}
}
}
到此就可以实现上述excel样式了。