EasyExcel動態(tài)表頭基礎(chǔ)用法及最佳實踐詳解

更新時間：2026年03月12日 10:21:10 作者：Zhu_S?W

EasyExcel是阿里巴巴開源的一個基于Java的簡單、省內(nèi)存的讀寫Excel工具,在處理大量數(shù)據(jù)時,它能極大地減少內(nèi)存占用提高性能,這篇文章主要介紹了EasyExcel動態(tài)表頭基礎(chǔ)用法及最佳實踐的相關(guān)資料,需要的朋友可以參考下

什么是動態(tài)表頭？

動態(tài)表頭是指在運行時根據(jù)業(yè)務(wù)需求動態(tài)生成Excel表格的列標(biāo)題，而不是在代碼中預(yù)先定義固定的表頭結(jié)構(gòu)。這在以下場景中非常有用：

根據(jù)用戶選擇的字段生成不同的報表
處理數(shù)據(jù)庫中動態(tài)字段的導(dǎo)出
生成多級復(fù)雜表頭
實現(xiàn)可配置的數(shù)據(jù)導(dǎo)出功能

基礎(chǔ)用法

1. 簡單動態(tài)表頭

public class DynamicHeaderExample {
    
    public void writeWithDynamicHeader() {
        String fileName = "dynamic_header.xlsx";
        
        // 動態(tài)構(gòu)建表頭
        List<List<String>> head = new ArrayList<>();
        head.add(Arrays.asList("姓名"));
        head.add(Arrays.asList("年齡"));
        head.add(Arrays.asList("郵箱"));
        head.add(Arrays.asList("部門"));
        
        // 準(zhǔn)備數(shù)據(jù)
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(Arrays.asList("張三", 25, "zhangsan@example.com", "技術(shù)部"));
        dataList.add(Arrays.asList("李四", 30, "lisi@example.com", "產(chǎn)品部"));
        dataList.add(Arrays.asList("王五", 28, "wangwu@example.com", "設(shè)計部"));
        
        // 寫入Excel
        EasyExcel.write(fileName)
                .head(head)
                .sheet("員工信息")
                .doWrite(dataList);
    }
}

2. 多級表頭

public void writeWithMultiLevelHeader() {
    String fileName = "multi_level_header.xlsx";
    
    // 構(gòu)建多級表頭
    List<List<String>> head = new ArrayList<>();
    // 第一列：基本信息->個人信息->姓名
    head.add(Arrays.asList("基本信息", "個人信息", "姓名"));
    // 第二列：基本信息->個人信息->年齡
    head.add(Arrays.asList("基本信息", "個人信息", "年齡"));
    // 第三列：基本信息->聯(lián)系方式->郵箱
    head.add(Arrays.asList("基本信息", "聯(lián)系方式", "郵箱"));
    // 第四列：基本信息->聯(lián)系方式->電話
    head.add(Arrays.asList("基本信息", "聯(lián)系方式", "電話"));
    // 第五列：工作信息->部門
    head.add(Arrays.asList("工作信息", "部門"));
    // 第六列：工作信息->職位
    head.add(Arrays.asList("工作信息", "職位"));
    
    // 準(zhǔn)備數(shù)據(jù)
    List<List<Object>> dataList = new ArrayList<>();
    dataList.add(Arrays.asList("張三", 25, "zhangsan@example.com", "13800138000", "技術(shù)部", "Java工程師"));
    dataList.add(Arrays.asList("李四", 30, "lisi@example.com", "13900139000", "產(chǎn)品部", "產(chǎn)品經(jīng)理"));
    
    EasyExcel.write(fileName)
            .head(head)
            .sheet("員工詳細信息")
            .doWrite(dataList);
}

高級應(yīng)用

1. 根據(jù)數(shù)據(jù)庫字段動態(tài)生成表頭

@Service
public class DynamicExportService {
    
    @Autowired
    private FieldConfigService fieldConfigService;
    
    public void exportUserData(List<String> selectedFields) {
        String fileName = "user_export_" + System.currentTimeMillis() + ".xlsx";
        
        // 根據(jù)選中字段構(gòu)建表頭
        List<List<String>> head = buildDynamicHeader(selectedFields);
        
        // 獲取數(shù)據(jù)
        List<List<Object>> dataList = buildDataList(selectedFields);
        
        EasyExcel.write(fileName)
                .head(head)
                .sheet("用戶數(shù)據(jù)")
                .doWrite(dataList);
    }
    
    private List<List<String>> buildDynamicHeader(List<String> selectedFields) {
        List<List<String>> head = new ArrayList<>();
        
        for (String fieldCode : selectedFields) {
            FieldConfig config = fieldConfigService.getByCode(fieldCode);
            if (config != null) {
                // 支持分組表頭
                if (config.getGroupName() != null) {
                    head.add(Arrays.asList(config.getGroupName(), config.getFieldName()));
                } else {
                    head.add(Arrays.asList(config.getFieldName()));
                }
            }
        }
        
        return head;
    }
    
    private List<List<Object>> buildDataList(List<String> selectedFields) {
        List<User> users = userService.getAllUsers();
        List<List<Object>> dataList = new ArrayList<>();
        
        for (User user : users) {
            List<Object> row = new ArrayList<>();
            for (String fieldCode : selectedFields) {
                Object value = getFieldValue(user, fieldCode);
                row.add(value);
            }
            dataList.add(row);
        }
        
        return dataList;
    }
    
    private Object getFieldValue(User user, String fieldCode) {
        // 使用反射或Map方式獲取字段值
        switch (fieldCode) {
            case "name": return user.getName();
            case "age": return user.getAge();
            case "email": return user.getEmail();
            case "department": return user.getDepartment();
            case "createTime": return user.getCreateTime();
            default: return "";
        }
    }
}
?
// 字段配置實體
@Data
public class FieldConfig {
    private String fieldCode;
    private String fieldName;
    private String groupName;
    private Integer sortOrder;
}

2. 配置化動態(tài)表頭

@Component
public class ConfigurableDynamicHeader {
    
    public void exportWithConfig(ExportConfig config) {
        String fileName = config.getFileName() + ".xlsx";
        
        // 根據(jù)配置構(gòu)建表頭
        List<List<String>> head = buildHeaderFromConfig(config);
        
        // 根據(jù)配置獲取數(shù)據(jù)
        List<List<Object>> dataList = buildDataFromConfig(config);
        
        // 應(yīng)用樣式
        HorizontalCellStyleStrategy styleStrategy = buildStyleFromConfig(config);
        
        EasyExcel.write(fileName)
                .head(head)
                .registerWriteHandler(styleStrategy)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet(config.getSheetName())
                .doWrite(dataList);
    }
    
    private List<List<String>> buildHeaderFromConfig(ExportConfig config) {
        List<List<String>> head = new ArrayList<>();
        
        for (HeaderConfig headerConfig : config.getHeaders()) {
            List<String> headerPath = new ArrayList<>();
            
            // 支持多級表頭
            if (headerConfig.getLevel1() != null) {
                headerPath.add(headerConfig.getLevel1());
            }
            if (headerConfig.getLevel2() != null) {
                headerPath.add(headerConfig.getLevel2());
            }
            if (headerConfig.getLevel3() != null) {
                headerPath.add(headerConfig.getLevel3());
            }
            
            head.add(headerPath);
        }
        
        return head;
    }
}
?
// 導(dǎo)出配置類
@Data
public class ExportConfig {
    private String fileName;
    private String sheetName;
    private List<HeaderConfig> headers;
    private StyleConfig styleConfig;
}
?
@Data
public class HeaderConfig {
    private String fieldCode;
    private String level1;
    private String level2;
    private String level3;
    private Integer width;
}

3. 動態(tài)表頭與數(shù)據(jù)驗證結(jié)合

public class DynamicHeaderWithValidation {
    
    public void writeWithValidation() {
        String fileName = "validated_dynamic.xlsx";
        
        // 構(gòu)建表頭
        List<List<String>> head = Arrays.asList(
            Arrays.asList("姓名"),
            Arrays.asList("年齡"),
            Arrays.asList("郵箱"),
            Arrays.asList("手機號")
        );
        
        // 準(zhǔn)備數(shù)據(jù)
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(Arrays.asList("張三", 25, "zhangsan@example.com", "13800138000"));
        dataList.add(Arrays.asList("李四", 30, "lisi@example.com", "13900139000"));
        
        // 創(chuàng)建下拉選擇處理器
        DropDownWriteHandler dropDownHandler = new DropDownWriteHandler();
        
        EasyExcel.write(fileName)
                .head(head)
                .registerWriteHandler(dropDownHandler)
                .sheet("帶驗證的數(shù)據(jù)")
                .doWrite(dataList);
    }
}
?
// 自定義下拉選擇處理器
public class DropDownWriteHandler implements SheetWriteHandler {
    
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, 
                                WriteSheetHolder writeSheetHolder) {
        
        Sheet sheet = writeSheetHolder.getSheet();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        
        // 創(chuàng)建數(shù)據(jù)驗證規(guī)則
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();
        
        // 為年齡列添加數(shù)字驗證（假設(shè)年齡在B列）
        CellRangeAddressList ageRange = new CellRangeAddressList(1, 1000, 1, 1);
        DataValidationConstraint ageConstraint = validationHelper
            .createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, "0", "150");
        DataValidation ageValidation = validationHelper.createValidation(ageConstraint, ageRange);
        ageValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        ageValidation.createErrorBox("年齡錯誤", "年齡必須在0-150之間");
        sheet.addValidationData(ageValidation);
    }
}

實際應(yīng)用場景

1. 報表系統(tǒng)中的可配置導(dǎo)出

@RestController
@RequestMapping("/api/export")
public class ExportController {
    
    @Autowired
    private DynamicExportService exportService;
    
    @PostMapping("/users")
    public ResponseEntity<String> exportUsers(@RequestBody ExportRequest request) {
        try {
            String fileName = exportService.exportUserData(
                request.getSelectedFields(),
                request.getStartDate(),
                request.getEndDate()
            );
            
            return ResponseEntity.ok(fileName);
        } catch (Exception e) {
            return ResponseEntity.badRequest().body("導(dǎo)出失?。? + e.getMessage());
        }
    }
}
?
@Data
public class ExportRequest {
    private List<String> selectedFields;
    private LocalDate startDate;
    private LocalDate endDate;
    private String groupBy;
}

2. 動態(tài)報表生成

public class DynamicReportGenerator {
    
    public void generateReport(ReportTemplate template, Map<String, Object> params) {
        String fileName = template.getName() + "_" + 
                         LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss")) + 
                         ".xlsx";
        
        // 根據(jù)模板構(gòu)建表頭
        List<List<String>> head = buildHeaderFromTemplate(template);
        
        // 根據(jù)參數(shù)查詢數(shù)據(jù)
        List<List<Object>> dataList = queryDataByTemplate(template, params);
        
        // 應(yīng)用模板樣式
        List<WriteHandler> handlers = buildWriteHandlers(template);
        
        ExcelWriterBuilder builder = EasyExcel.write(fileName).head(head);
        
        // 注冊所有處理器
        for (WriteHandler handler : handlers) {
            builder.registerWriteHandler(handler);
        }
        
        builder.sheet(template.getSheetName()).doWrite(dataList);
    }
}

最佳實踐

1. 性能優(yōu)化

public class OptimizedDynamicExport {
    
    public void exportLargeData(List<String> fields) {
        String fileName = "large_data_export.xlsx";
        
        // 構(gòu)建表頭
        List<List<String>> head = buildHeader(fields);
        
        // 使用ExcelWriter進行流式寫入
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).head(head).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("數(shù)據(jù)").build();
            
            // 分批處理大量數(shù)據(jù)
            int pageSize = 10000;
            int pageNum = 1;
            List<List<Object>> batchData;
            
            do {
                batchData = queryDataByPage(fields, pageNum, pageSize);
                if (!batchData.isEmpty()) {
                    excelWriter.write(batchData, writeSheet);
                }
                pageNum++;
            } while (batchData.size() == pageSize);
        }
    }
}

2. 錯誤處理

public class SafeDynamicExport {
    
    public void safeExport(List<String> fields) {
        try {
            validateFields(fields);
            
            List<List<String>> head = buildHeader(fields);
            List<List<Object>> dataList = buildData(fields);
            
            EasyExcel.write("safe_export.xlsx")
                    .head(head)
                    .sheet("數(shù)據(jù)")
                    .doWrite(dataList);
                    
        } catch (IllegalArgumentException e) {
            log.error("字段驗證失敗：{}", e.getMessage());
            throw new BusinessException("導(dǎo)出字段配置錯誤");
        } catch (Exception e) {
            log.error("導(dǎo)出過程中發(fā)生錯誤：", e);
            throw new BusinessException("數(shù)據(jù)導(dǎo)出失敗");
        }
    }
    
    private void validateFields(List<String> fields) {
        if (fields == null || fields.isEmpty()) {
            throw new IllegalArgumentException("導(dǎo)出字段不能為空");
        }
        
        List<String> validFields = getValidFields();
        for (String field : fields) {
            if (!validFields.contains(field)) {
                throw new IllegalArgumentException("無效的字段：" + field);
            }
        }
    }
}