To create an Excel file with a multi-row header and merge cells vertically based on data consistency, configure an ExcelWriter with custom strategies. Define a method to generate the header structure and apply a merge strategy for rows with identical values in a specific column.

OutputStream outputStream = ExcelUtils.getOutputStream(response, filename);
Map<String, List<String>> mergeRanges = computeMergeRanges(dataList);
ExcelWriter excelWriter = EasyExcel.write(outputStream)
    .registerWriteHandler(new CustomCellStyleStrategy(new WriteCellStyle(), new WriteCellStyle()))
    .registerWriteHandler(new VerticalMergeStrategy(mergeRanges))
    .excelType(ExcelTypeEnum.XLSX)
    .build();
String clientName = "Client Corp";
String reportDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
List<List<String>> header = assembleHeader(clientName, reportDate, dataClass);
ExcelUtils.writeSheet(excelWriter, dataList, DataDTO.class, 1, "Report", header);

Construct the header as a list of string lists, where each inner list represents a cell in the header rows. Use reflection to extract field names annotated with @ExcelProperty for dynamic header generation.

private List<List<String>> assembleHeader(String client, String date, Class<?> clazz) {
    List<List<String>> headerRows = new ArrayList<>();
    List<String> fields = extractAnnotatedFields(clazz);
    for (String field : fields) {
        List<String> row = Arrays.asList(client, date, field);
        headerRows.add(row);
    }
    return headerRows;
}

private List<String> extractAnnotatedFields(Class<?> clazz) {
    List<String> fields = new ArrayList<>();
    for (Field field : clazz.getDeclaredFields()) {
        if (field.isAnnotationPresent(ExcelProperty.class)) {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            fields.add(annotation.value()[0]);
        }
    }
    return fields;
}

Implement a helper method to write data with a custom header to a sheet.

public static <T> void writeSheet(ExcelWriter writer, List<T> data, Class<T> clazz, 
                                  int sheetIndex, String sheetName, List<List<String>> header) {
    WriteSheet sheet = new WriteSheet();
    sheet.setSheetNo(sheetIndex);
    sheet.setSheetName(sheetName);
    sheet.setClazz(clazz);
    sheet.setHead(header);
    writer.write(data, sheet);
}

Calculate merge ranges by iterating through the data list and identifying consecutive rows where a specified field (e.g., supervisor) has the same value.

private Map<String, List<String>> computeMergeRanges(List<DataDTO> items) {
    Map<String, List<String>> ranges = new HashMap<>();
    List<String> rowPairs = new ArrayList<>();
    int startRow = 1;
    DataDTO previous = null;
    for (int i = 0; i < items.size(); i++) {
        DataDTO current = items.get(i);
        if (previous != null && !previous.getSupervisor().equals(current.getSupervisor())) {
            rowPairs.add(startRow + "," + i);
            startRow = i + 1;
        }
        if (i == items.size() - 1 && startRow <= i) {
            rowPairs.add(startRow + "," + (i + 1));
        }
        previous = current;
    }
    ranges.put("0", rowPairs);
    return ranges;
}

Create a custom merge strategy by extending AbstractMergeStrategy. Merge cells in a specifeid column based on the computed row ranges, ensuring each range is applied only once.

public class VerticalMergeStrategy extends AbstractMergeStrategy {
    private Map<String, List<String>> mergeRanges;
    private Sheet sheet;

    public VerticalMergeStrategy(Map<String, List<String>> mergeRanges) {
        this.mergeRanges = mergeRanges;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            for (Map.Entry<String, List<String>> entry : mergeRanges.entrySet()) {
                int col = Integer.parseInt(entry.getKey());
                for (String range : entry.getValue()) {
                    String[] bounds = range.split(",");
                    int startRow = Integer.parseInt(bounds[0]);
                    int endRow = Integer.parseInt(bounds[1]);
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, endRow, col, col));
                }
            }
        }
    }
}

For more complex layouts, such as multi-line headers with merged cells across columns, implement a SheetWriteHandler to customize the sheet after creation. This allows setting titles, dates, and styles directly on the workbook.

public class CustomHeaderHandler implements SheetWriteHandler {
    private String period;

    public CustomHeaderHandler(String period) {
        this.period = period;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder workbookHolder, WriteSheetHolder sheetHolder) {
        Workbook workbook = workbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row titleRow = sheet.createRow(1);
        titleRow.setHeight((short) 800);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("Report Summary");
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 18);
        titleStyle.setFont(titleFont);
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 5));
    }
}

Apply cell styling using a HorizontalCellStyleStrategy to define separate styles for header and content cells, including borders, alignment, and fonts.

public static HorizontalCellStyleStrategy createStyle(short headerSize, short contentSize) {
    WriteCellStyle headerStyle = new WriteCellStyle();
    WriteFont headerFont = new WriteFont();
    headerFont.setFontHeightInPoints(headerSize);
    headerStyle.setWriteFont(headerFont);
    headerStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    headerStyle.setBorderBottom(BorderStyle.THIN);

    WriteCellStyle contentStyle = new WriteCellStyle();
    WriteFont contentFont = new WriteFont();
    contentFont.setFontHeightInPoints(contentSize);
    contentStyle.setWriteFont(contentFont);
    contentStyle.setWrapped(true);
    return new HorizontalCellStyleStrategy(headerStyle, contentStyle);
}

To merge cells horizontally within a row, adjust the merge strategy to target specific rows and column ranges. Compute these ranges based on data attributes, such as grouping indicators.

private Map<String, String> computeHorizontalMerges(List<DataDTO> items, int lastColumn) {
    Map<String, String> merges = new HashMap<>();
    int dataStartRow = 3;
    for (int i = 0; i < items.size(); i++) {
        DataDTO item = items.get(i);
        if (item.getGroupMarker() != null && !merges.containsKey(String.valueOf(i))) {
            merges.put((dataStartRow + i) + "", "0," + lastColumn);
        }
    }
    return merges;
}