使用注解动态映射:根据实体List列表动态生成Excel文件

我们一般通过POI来生成对应的Excel文件，绝大多数情况是需要手动编写单元格内容，然后顺序填充值，今天我们将动态根据实体来生成Excel表头，同时自动填充内容。

1. 定义注解

import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelColumn {
    String name();          // Excel列名
    int order();            // 列顺序(从0开始)
    String format() default ""; // 格式(如日期、数字格式)
}

2. 实体类应用注解

public class Employee {
    @ExcelColumn(name = "员工ID", order = 0)
    private Integer id;
    
    @ExcelColumn(name = "姓名", order = 1)
    private String name;
    
    @ExcelColumn(name = "部门", order = 2)
    private String department;
    
    @ExcelColumn(name = "薪资", order = 3, format = "#,##0.00")
    private Double salary;
    
    @ExcelColumn(name = "入职日期", order = 4, format = "yyyy-MM-dd")
    private Date hireDate;
    
    // getter/setter省略...
}

3. 动态导出工具类

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

public class DynamicExcelExporter {
    
    public static <T> void export(List<T> dataList, OutputStream outputStream) throws IOException {
        if (dataList == null || dataList.isEmpty()) {
            throw new IllegalArgumentException("数据列表不能为空");
        }
        
        Class<?> clazz = dataList.get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        
        // 获取带注解的字段并按order排序
        List<Field> annotatedFields = Arrays.stream(fields)
                .filter(f -> f.isAnnotationPresent(ExcelColumn.class))
                .sorted(Comparator.comparingInt(f -> f.getAnnotation(ExcelColumn.class).order()))
                .toList();
        
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");
            
            // 1. 动态生成表头
            createDynamicHeader(workbook, sheet, annotatedFields);
            
            // 2. 动态填充数据
            fillDynamicData(workbook, sheet, dataList, annotatedFields);
            
            // 3. 自动调整列宽
            for (int i = 0; i < annotatedFields.size(); i++) {
                sheet.autoSizeColumn(i);
            }
            
            workbook.write(outputStream);
        }
    }
    
    private static void createDynamicHeader(Workbook workbook, Sheet sheet, List<Field> fields) {
        Row headerRow = sheet.createRow(0);
        CellStyle headerStyle = createHeaderStyle(workbook);
        
        for (int i = 0; i < fields.size(); i++) {
            ExcelColumn annotation = fields.get(i).getAnnotation(ExcelColumn.class);
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(annotation.name());
            cell.setCellStyle(headerStyle);
        }
    }
    
    private static <T> void fillDynamicData(Workbook workbook, Sheet sheet, 
                                          List<T> dataList, List<Field> fields) {
        for (int rowIdx = 0; rowIdx < dataList.size(); rowIdx++) {
            T item = dataList.get(rowIdx);
            Row row = sheet.createRow(rowIdx + 1);
            
            for (int colIdx = 0; colIdx < fields.size(); colIdx++) {
                Field field = fields.get(colIdx);
                field.setAccessible(true);
                
                try {
                    Object value = field.get(item);
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    createCell(workbook, row, colIdx, value, annotation.format());
                } catch (IllegalAccessException e) {
                    createCell(workbook, row, colIdx, "", null);
                }
            }
        }
    }
    
    private static void createCell(Workbook workbook, Row row, int column, 
                                 Object value, String format) {
        Cell cell = row.createCell(column);
        
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
            if (format != null && !format.isEmpty()) {
                CellStyle style = workbook.createCellStyle();
                style.setDataFormat(workbook.createDataFormat().getFormat(format));
                cell.setCellStyle(style);
            }
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
            CellStyle style = workbook.createCellStyle();
            String dateFormat = format != null ? format : "yyyy-MM-dd";
            style.setDataFormat(workbook.createDataFormat().getFormat(dateFormat));
            cell.setCellStyle(style);
        } else {
            cell.setCellValue(value.toString());
        }
    }
    
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setAlignment(HorizontalAlignment.CENTER);
        
        // 设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        
        return style;
    }
}