前言
有個需求，需要設定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樣式了。