Exporting Excel with Custom Cell Styles

When working with EasyExcel, you can apply custom styling to both header and contant cells by implementing a cell style strategy. This approach allows fine-grained control over fonts, colors, aligmnent, and other visual properties.

Service Layer Implementation

@Override
public String generateTemplateFile(HttpServletResponse response) {
    OutputStream outputStream = ExcelHelper.prepareResponseStream(response, "import_template");
    ExcelWriterBuilder writerBuilder = EasyExcel.write(outputStream)
        .registerWriteHandler(new CustomCellStyleHandler())
        .excelType(ExcelTypeEnum.XLSX);
    ExcelWriter excelWriter = writerBuilder.build();

    ExcelHelper.writeToSheet(excelWriter, sampleDataList(), DataDTO.class, 1, "Sample Data");
    ExcelHelper.writeToSheet(excelWriter, descriptionList(), DescriptionDTO.class, 2, "Description");
    
    try {
        ExcelHelper.finalizeExport(outputStream, excelWriter);
    } catch (Exception e) {
        log.error("Export operation failed", e);
    }
    return "Template export finished";
}

Utility Class for Sheet Writing

public static <T> void writeToSheet(ExcelWriter excelWriter, List<T> data, 
                                    Class clazz, Integer sheetNo, String sheetName) {
    ExcelWriterSheetBuilder sheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
    sheetBuilder.sheetNo(sheetNo);
    sheetBuilder.sheetName(sheetName);
    
    WriteSheet writeSheet = new WriteSheet();
    writeSheet.setSheetNo(sheetNo);
    writeSheet.setSheetName(sheetName);
    writeSheet.setClazz(clazz);
    
    excelWriter.write(data, writeSheet);
}

public static void finalizeExport(OutputStream outputStream, ExcelWriter excelWriter) 
    throws IOException {
    outputStream.flush();
    excelWriter.finish();
    outputStream.close();
}

Custom Style Handler Implementation

The custom handler extends AbstractCellStyleStrategy to provide dynamic styling based on cell position and content:

public class CustomCellStyleHandler extends AbstractCellStyleStrategy {

    private WriteCellStyle headerStyle;
    private List<WriteCellStyle> bodyStyleList;
    private CellStyle headerCellStyle;
    private List<CellStyle> bodyCellStyleList;

    public CustomCellStyleHandler(WriteCellStyle headerStyle, 
                                  List<WriteCellStyle> bodyStyleList) {
        this.headerStyle = headerStyle;
        this.bodyStyleList = bodyStyleList;
    }

    public CustomCellStyleHandler(WriteCellStyle headerStyle, WriteCellStyle bodyStyle) {
        this.headerStyle = headerStyle;
        this.bodyStyleList = new ArrayList<>();
        this.bodyStyleList.add(bodyStyle);
    }

    @Override
    protected void initCellStyle(Workbook workbook) {
        if (this.headerStyle != null) {
            this.headerCellStyle = StyleUtils.createHeaderStyle(workbook, this.headerStyle);
        }

        if (this.bodyStyleList != null && !this.bodyStyleList.isEmpty()) {
            this.bodyCellStyleList = new ArrayList<>();
            for (WriteCellStyle cellStyle : this.bodyStyleList) {
                this.bodyCellStyleList.add(StyleUtils.createBodyStyle(workbook, cellStyle));
            }
        }
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (this.headerCellStyle != null) {
            cell.setCellStyle(this.headerCellStyle);
        }
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (this.bodyCellStyleList != null && !this.bodyCellStyleList.isEmpty()) {
            Workbook workbook = cell.getSheet().getWorkbook();
            CellStyle style = createDynamicStyle(workbook, cell);
            cell.setCellStyle(style);
        }
    }

    private static CellStyle createDynamicStyle(Workbook workbook, Cell cell) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        font.setBold(false);
        font.setItalic(true);
        font.setFontName("Arial");

        CellStyle style = workbook.createCellStyle();
        
        // Apply red color to first data row
        if (cell.getRowIndex() == 1) {
            font.setColor(IndexedColors.RED.getIndex());
        }
        
        style.setFont(font);
        style.setWrapText(true);
        style.setAlignment(HorizontalAlignment.CENTER);
        
        // Special formatting for description rows
        if (cell.getColumnIndex() == 0 && 
            cell.getStringCellValue().contains("Specification")) {
            font.setBold(true);
            style.setAlignment(HorizontalAlignment.LEFT);
        }
        
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
}

Key Implementation Details

The initCellStyle method converts WriteCellStyle objects to actual CellStyle instances using Apache POI's style utilities. This conversion happens once per workbook for performance optimization.

The setHeadCellStyle and setContentCellStyle methods are called during the write process, allowing for row-specific or cell-specific style decisions based on the relativeRowIndex parameter.

The createDynamicStyle method demonstrates conditional styling logic - applying different formatting based on cell position and content value. This pattern is useful for highlighting specific rows, creating zebra-striping effects, or emphasizing certain data categories.