How to Build a Powerful Annotation‑Driven Excel Export Tool in Java

Introduction

This article introduces a Java Excel export plugin that leverages custom annotations, reflection, and Apache POI to generate Excel files with complex headers, multi‑sheet support, styling, and dynamic content.

Feature Overview

Export tree‑structured data with unlimited header depth.

Support one‑to‑one and one‑to‑many relationships.

Customizable sheet name, password protection, and title height.

Annotations for cells ( @Cell), sheets ( @Sheet), titles ( @Title), styles ( @Style) and fonts ( @Font).

Group‑based column selection using marker interfaces.

Dynamic title and header generation at runtime.

Automatic column width adjustment with a custom algorithm.

Feature Demonstration

Preparation

Add the Maven dependency to your project:

<dependency>
  <groupId>com.gitee.lwpwork</groupId>
  <artifactId>excel</artifactId>
  <version>0.0.2-RELEASE</version>
</dependency>

1. One‑to‑many relationship with multiple sheets and sheet password

Entity Shop with a list of Clerk objects:

@Data
@Sheet(name = "Blizzard Group")
@Title(value = "Blizzard Group Report", heightInPoints = 30)
public class Shop implements ExcelAble {
    @Cell(value = "Store Name")
    private String name;
    @Cell(value = "Revenue")
    private Integer income;
    @Cell(value = "Manager")
    private Manager manager;
    @Cell(value = "Employees")
    private List<Clerk> clerks;
    // getters & setters
}

Entity Clerk:

@Data
public class Clerk implements ExcelAble {
    @Cell(value = "Name")
    private String name;
    @Cell(value = "Age")
    private String age;
    @Cell(value = "Income")
    private Integer income;
    // getters & setters
}

Export code:

public static void main(String[] args) throws Exception {
    List<Shop> shops = init(); // initialize data
    HSSFWorkbook wb = ExcelUtil.exportExcel(shops);
    ExcelUtil.createExcelFile(wb, "/Shop.xls");
}

2. One‑to‑one relationship with complex headers

Entity Shop now contains a Manager object:

@Data
@Sheet(name = "Blizzard Group")
@Title(value = "Blizzard Group Report", heightInPoints = 30)
public class Shop implements ExcelAble {
    @Cell(value = "Store Name")
    private String name;
    @Cell(value = "Revenue")
    private Integer income;
    @Cell(value = "Manager")
    private Manager manager;
    // getters & setters
}

@Data
public class Manager implements ExcelAble {
    @Cell(value = "Manager Name")
    private String name;
    @Cell(value = "Gender")
    private String sex;
    @Cell(value = "Experience")
    private String jobExperience;
    // getters & setters
}

3. Grouped headers

Define marker interfaces GroupA and GroupB to control column groups:

@Data
public class Clerk implements ExcelAble {
    @Cell(value = "Name", groups = {GroupA.class, GroupB.class})
    private String name;
    @Cell(value = "Age", groups = GroupB.class)
    private String age;
    @Cell(value = "Income", groups = GroupA.class)
    private Integer income;
    // marker interfaces
    public interface GroupA {}
    public interface GroupB {}
}

Export with a specific group:

HSSFWorkbook wb = ExcelUtil.exportExcel(shops, Clerk.GroupA.class);

4. Cell formatting (date, value conversion, default value)

@Cell(value = "Create Time", format = "yyyy-MM-dd HH:mm:ss")
private Date createDate;

@Cell(value = "Shop Type", readConverterExp = "1=Supermarket,2=Mall,3=Restaurant")
private Integer shopType;

5. Styling (alignment, borders, colors)

@Style(border = BorderStyles.BORDER_DASH_DOT, color = ExcelColors.AQUA)
@Cell(value = "Store Name")
private String name;

@Style(backgroundColor = ExcelColors.YELLOW, fillPattern = FillPatternStyles.THIN_HORZ_BANDS)
@Cell(value = "Revenue")
private Integer income;

6. Font customization (size, name, color)

@Font(fontHeightInPoints = 12, fontColor = ExcelColors.RED, fontName = "华文琥珀")
@Cell(value = "Store Name")
private String name;

@Font(fontHeightInPoints = 14, fontColor = ExcelColors.BLUE, fontName = "Bradley Hand ITC")
@Cell(value = "Revenue")
private Integer income;

7. Dynamic title and headers

Pass custom headers and title at runtime:

String[] headers = {"name", "income", "manager", "clerks", "createDate", "shopType", "manager.name", "manager.jobExperience", "clerks.name", "clerks.income"};
String title = "Blizzard Group Report [Dynamic Title]";
HSSFWorkbook wb = ExcelUtil.exportExcel(shops, headers, title);

Source Code Analysis

The core export process resides in ExcelUtil.exportExcel. It creates a HSSFWorkbook, resolves sheets via DataExcelResolver.sheetResolver, writes the title, headers, and data, and finally adjusts column widths.

public static <T> HSSFWorkbook exportExcel(List<T> data, Class<?> group, String[] headers) {
    DataExcelResolver excelResolver = new DataExcelResolver(group, headers);
    excelResolver.checkExcel(data);
    HSSFWorkbook wb = new HSSFWorkbook();
    Map<HSSFSheet, List> sheetMap = excelResolver.sheetResolver(wb, data, null);
    for (HSSFSheet sheet : sheetMap.keySet()) {
        excelResolver.titleResolver(sheet, sheetMap.get(sheet), wb, headers);
        excelResolver.headerResolver(sheet, sheetMap.get(sheet), wb);
        excelResolver.dataResolver(sheet, sheetMap.get(sheet), wb);
        int index = excelResolver.countParticleCell(sheetMap.get(sheet).get(0).getClass(), headers);
        setSizeColumn(sheet, index);
    }
    return wb;
}

The resolver methods use reflection to read annotations, compute required column/row spans, merge cells, and apply styles. The custom setSizeColumn method iterates over rows to calculate the maximum string width for each column, handling merged cells, and sets the column width without the performance penalty of POI's built‑in auto‑size.

Source Repository

Git repository: https://gitee.com/lwpwork/excel