easyExcel多行表頭設定不同樣式和特定單元格設定樣式的實現

John-zh發表於2024-05-06
Excel

前言

有個需求，需要設定Excel匯出的樣式，樣式如下圖所示，有三個表頭行，第一個表頭行需要加粗和灰色背景，另外兩個表頭行使用另外的樣式，並且當測試結果單元格出現x或者未透過的時候，設定其為紅色字型。

image-20240506162318563

實現步驟

寫入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並填入相關的標題，到此第一行標題頭設定完畢。

image-20240506171306644

這裡其實就是相當於這行標題是獨立開來設定的，先預留了第一行的位置，然後後續把標題插入這行，後面再針對這行設定獨立的樣式。

image-20240506171607437

其他標題行的設定

可以看到每個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標題

image-20240506171506824

可以看到其實每個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樣式了。

相關文章