做的比较漂亮的网站,徐州市云龙区建设局网站,运维工程师累吗,微楼书网站建设Excel多级数据结构导入导出工具
这是一个功能强大的Excel导入导出工具库#xff0c;专门用于处理复杂的多级嵌套数据结构。通过自定义注解配置#xff0c;可以轻松实现Java对象与Excel文件之间的双向转换。
核心功能特性
1. 多级数据结构支持
嵌套对象处理: 支持任意层级的对…Excel多级数据结构导入导出工具
这是一个功能强大的Excel导入导出工具库专门用于处理复杂的多级嵌套数据结构。通过自定义注解配置可以轻松实现Java对象与Excel文件之间的双向转换。
核心功能特性
1. 多级数据结构支持
嵌套对象处理: 支持任意层级的对象嵌套如集团→公司→部门→团队→员工自动合并单元格: 上级数据自动合并对应的行数数据清晰层次分明智能填充: 合并单元格区域自动填充相同的上级数据层级识别: 自动识别和处理不同层级的同名字段避免数据覆盖
2. 强大的注解系统
ExcelColumn: Excel列配置注解控制字段与Excel列的映射关系ExcelObject: 对象关系注解定义嵌套对象和集合的处理方式ExcelConvert: 数据转换注解实现字段值的自定义转换逻辑
3. 灵活的数据转换
内置转换器: 性别、状态、布尔值等常用数据类型转换自定义转换器: 支持实现自定义的双向数据转换逻辑日期格式化: 支持多种日期格式的导入导出数据验证: 支持必填字段验证和数据类型检查
注解详细说明
1. ExcelColumn 注解
用于标识Java字段与Excel列的映射关系。
ExcelColumn(name 列名称, // Excel表头显示的列名index 0, // 列的位置索引可选width 20, // 列宽度required true, // 是否必填isKey true, // 是否为关键字段用于数据关联dateFormat yyyy-MM-dd, // 日期格式仅日期字段defaultValue 默认值 // 默认值可选
)参数说明
name: Excel列标题导出时作为表头导入时用于匹配列index: 列的顺序位置影响Excel中的列顺序width: Excel列宽度字符数required: 导入时是否验证该字段必填isKey: 标识关键字段用于多级数据的层级关联dateFormat: 日期字段的格式化模式defaultValue: 字段为空时的默认值
2. ExcelObject 注解
用于定义嵌套对象和集合的处理方式。
ExcelObject(isCollection true, // 是否为集合类型elementType Company.class, // 集合元素类型order 1 // 处理顺序
)
private ListCompany companies;参数说明
isCollection: 是否为集合List、Set等elementType: 集合中元素的具体类型order: 处理顺序决定在Excel中的层级位置
多级结构示例
Data
public class School {ExcelColumn(name 学校名称, isKey true)private String schoolName;ExcelObject(isCollection true, elementType ClassInfo.class, order 1)private ListClassInfo classes; // 一级嵌套班级列表
}Data
public class ClassInfo {ExcelColumn(name 班级名称, isKey true)private String className;ExcelObject(isCollection true, elementType Student.class, order 2)private ListStudent students; // 二级嵌套学生列表
}Data
public class Student {ExcelColumn(name 学生姓名)private String studentName;ExcelColumn(name 年龄)private Integer age;
}3. ExcelConvert 注解
用于实现字段值的自定义转换。
ExcelColumn(name 性别)
ExcelConvert(converter GenderConverter.class)
private Integer gender; // 存储1-男2-女 显示男/女内置转换器
GenderConverter: 性别转换1↔男2↔女StatusConverter: 状态转换0↔禁用1↔启用BooleanConverter: 布尔转换true↔是false↔否
使用示例
1. 单层数据结构
Data
public class Employee {ExcelColumn(name 员工编号, required true, width 15)private String empCode;ExcelColumn(name 姓名, required true, width 20)private String name;ExcelColumn(name 性别, width 10)ExcelConvert(converter GenderConverter.class)private Integer gender;ExcelColumn(name 入职日期, dateFormat yyyy-MM-dd, width 15)private Date joinDate;
}2. 多级数据结构
Data
public class Company {ExcelColumn(name 公司名称, isKey true, width 25)private String companyName;ExcelColumn(name 公司地址, width 40)private String address;// 部门列表 - 第一层嵌套ExcelObject(isCollection true, elementType Department.class, order 1)private ListDepartment departments;
}Data
public class Department {ExcelColumn(name 部门名称, isKey true, width 20)private String deptName;ExcelColumn(name 部门经理, width 15)private String manager;// 员工列表 - 第二层嵌套ExcelObject(isCollection true, elementType Employee.class, order 2)private ListEmployee employees;
}3. 导入导出代码
// 导出Excel
GetMapping(/export)
public void exportData(HttpServletResponse response) {ListCompany companies dataService.getCompanies();ExcelUtils.exportExcel(companies, Company.class, 公司组织架构, response);
}// 导入Excel
PostMapping(/import)
public ListCompany importData(RequestParam(file) MultipartFile file) {return ExcelUtils.importExcel(file.getInputStream(), Company.class);
}Excel效果展示数据导入原理
1. 表头映射
根据ExcelColumn的name属性匹配Excel表头支持不同层级的同名字段自动识别按层级优先级进行字段映射
2. 数据填充
合并单元格区域内的空值自动填充上级数据根据isKey字段进行数据分组和层级关联自动构建嵌套对象结构
3. 类型转换
根据ExcelConvert注解进行数据转换支持日期格式化和自定义转换逻辑
API接口示例
核心方法
// 导出Excel
public static void exportExcel(ListT dataList, ClassT clazz, String fileName, HttpServletResponse response)// 导入Excel
public static ListT importExcel(InputStream inputStream, ClassT clazz)Controller使用示例
RestController
public class ExcelController {// 导出数据到ExcelGetMapping(/export/{entityType})public void exportData(PathVariable String entityType, HttpServletResponse response) {List? dataList dataService.getData(entityType);Class? clazz getEntityClass(entityType);ExcelUtils.exportExcel(dataList, clazz, entityType 数据导出, response);}// 从Excel导入数据PostMapping(/import/{entityType})public List? importData(PathVariable String entityType,RequestParam(file) MultipartFile file) {Class? clazz getEntityClass(entityType);return ExcelUtils.importExcel(file.getInputStream(), clazz);}
}自定义转换器详解
转换器接口
public interface ExcelConverterT, E {// 导出时将Java对象字段值转换为Excel显示值E convertToExcel(T value, String[] params);// 导入时将Excel单元格值转换为Java对象字段值 T convertFromExcel(E value, String[] params);
}内置转换器实现
1. StatusConverter状态转换器
public class StatusConverter implements ExcelConverterInteger, String {Overridepublic String convertToExcel(Integer value, String[] params) {if (value null) return ;return value 1 ? 启用 : 禁用;}Override public Integer convertFromExcel(String value, String[] params) {if (StringUtils.isBlank(value)) return null;return 启用.equals(value) ? 1 : 0;}
}2. GenderConverter性别转换器
public class GenderConverter implements ExcelConverterInteger, String {Overridepublic String convertToExcel(Integer value, String[] params) {if (value null) return ;return value 1 ? 男 : 女;}Overridepublic Integer convertFromExcel(String value, String[] params) {if (StringUtils.isBlank(value)) return null;return 男.equals(value) ? 1 : 2;}
}自定义转换器示例
等级转换器
public class LevelConverter implements ExcelConverterInteger, String {Overridepublic String convertToExcel(Integer value, String[] params) {if (value null) return ;switch (value) {case 1: return 初级;case 2: return 中级; case 3: return 高级;case 4: return 专家;default: return 未知;}}Overridepublic Integer convertFromExcel(String value, String[] params) {if (StringUtils.isBlank(value)) return null;switch (value) {case 初级: return 1;case 中级: return 2;case 高级: return 3;case 专家: return 4;default: return 0;}}
}带参数的转换器
public class ScoreConverter implements ExcelConverterDouble, String {Overridepublic String convertToExcel(Double value, String[] params) {if (value null) return ;// params[0] 可以传递精度参数int precision params.length 0 ? Integer.parseInt(params[0]) : 2;String format %. precision f;if (value 90) return String.format(format (优秀), value);if (value 80) return String.format(format (良好), value); if (value 70) return String.format(format (中等), value);if (value 60) return String.format(format (及格), value);return String.format(format (不及格), value);}Overridepublic Double convertFromExcel(String value, String[] params) {if (StringUtils.isBlank(value)) return null;// 提取数字部分 85.5 (良好) - 85.5String numStr value.replaceAll([^\\d.], );return Double.parseDouble(numStr);}
}// 使用时传递参数
ExcelColumn(name 考试成绩)
ExcelConvert(converter ScoreConverter.class, params {1}) // 1位小数
private Double score;高级特性
1. 同名字段处理
当不同层级存在同名字段时系统会自动按层级优先级进行映射
// 学校实体
public class School {ExcelColumn(name 地址) // L0_addressprivate String address;
}// 学生实体
public class Student {ExcelColumn(name 地址) // L2_address private String address;
}系统会为每个字段生成唯一标识如 L0_address、L2_address避免数据覆盖。
2. 智能数据填充
对于合并单元格系统会智能填充空值
// 导入时如果某行的学校名称为空会自动填充同一合并区域内的有效值
Row1: [学校A] [班级1] [学生1]
Row2: [ ] [ ] [学生2] // 空值会被填充为学校A和班级1
Row3: [ ] [ ] [学生3]3. 数据验证与错误处理
ExcelColumn(name 年龄, required true)
private Integer age;// 导入时会验证
// 1. 必填字段不能为空
// 2. 数据类型必须匹配
// 3. 转换器处理异常时记录警告但不中断处理最佳实践
1. 实体类设计建议
Data
public class OptimalEntity {// 1. 关键字段使用 isKey trueExcelColumn(name 编号, isKey true, required true)private String code;// 2. 合理设置列宽ExcelColumn(name 详细描述, width 50) private String description;// 3. 日期字段指定格式ExcelColumn(name 创建时间, dateFormat yyyy-MM-dd HH:mm:ss)private Date createTime;// 4. 使用转换器处理枚举或状态ExcelColumn(name 状态)ExcelConvert(converter StatusConverter.class)private Integer status;// 5. 嵌套集合按order排序ExcelObject(isCollection true, elementType SubEntity.class, order 1)private ListSubEntity children;
}2. 性能优化建议
大数据量: 对于超过10万行的数据建议分批处理内存控制: 导入时设置合适的JVM内存参数字段简化: 非必要字段不加ExcelColumn注解
运行项目
环境要求
Java: 17Maven: 3.6Spring Boot: 3.x
技术规范
支持的数据类型
✅ 基础类型: String, Integer, Long, Double, Boolean✅ 日期类型: Date, LocalDate, LocalDateTime✅ 集合类型: List, Set配合ExcelObject使用✅ 自定义对象: 支持任意嵌套深度
Excel格式要求
文件格式: .xlsx (Excel 2007)表头位置: 第一行数据位置: 从第二行开始编码: UTF-8支持中文单元格类型: 自动识别文本、数字、日期
限制说明
单个Excel文件建议不超过 100MB单次导入数据量建议不超过 10万行嵌套层级建议不超过 5层列数量建议不超过 100列
依赖项
!-- 核心Excel处理 --
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion5.3.0/version
/dependency!-- Spring Boot Web --
dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactIdversion3.5.4/version
/dependency!-- 工具类库 --
dependencygroupIdorg.apache.commons/groupIdartifactIdcommons-lang3/artifactIdversion3.14.0/version
/dependency!-- 代码简化 --
dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdscopeprovided/scope
/dependency!-- JSON处理 --
dependencygroupIdcom.fasterxml.jackson.core/groupIdartifactIdjackson-annotations/artifactId
/dependency总结
这个Excel多级数据结构导入导出工具提供了
✅ 强大的注解系统 - 简单配置即可实现复杂功能
✅ 多级数据支持 - 处理任意层级的嵌套结构
✅ 智能数据处理 - 自动合并单元格和数据填充
✅ 灵活的转换机制 - 支持自定义数据转换逻辑
✅ 完善的错误处理 - 友好的异常处理和日志记录
通过合理使用 ExcelColumn、ExcelObject 和 ExcelConvert 注解您可以轻松实现复杂业务数据的Excel导入导出功能。
源码
package org.whh.excel.annotation;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** Excel列注解* 用于标识实体类字段对应的Excel列* * author whh*/
Target(ElementType.FIELD)
Retention(RetentionPolicy.RUNTIME)
public interface ExcelColumn {/*** 列名称*/String name() default ;/*** 列索引从0开始如果不指定则按字段声明顺序*/int index() default -1;/*** 列宽度*/int width() default 20;/*** 是否必填*/boolean required() default false;/*** 日期格式当字段为日期类型时使用*/String dateFormat() default yyyy-MM-dd;/*** 数字格式当字段为数字类型时使用*/String numberFormat() default ;/*** 默认值*/String defaultValue() default ;/*** 说明*/String description() default ;/*** 是否为关键字段用于多级数据导入时的分组标识* 当多行数据的所有关键字段值都相同时这些行会被归为同一个对象* * 使用示例* - 如果只需要按学校编码分组在schoolCode字段上设置 isKey true* - 如果需要按姓名年龄性别组合分组在name、age、gender三个字段上都设置 isKey true*/boolean isKey() default false;
}package org.whh.excel.annotation;import org.whh.excel.converter.ExcelConverter;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** Excel字段转换注解* 用于标识字段需要进行值转换比如数字转文字等* * author whh*/
Target(ElementType.FIELD)
Retention(RetentionPolicy.RUNTIME)
public interface ExcelConvert {/*** 转换器类*/Class? extends ExcelConverter?, ? converter();/*** 转换参数可选*/String[] params() default {};/*** 说明*/String description() default ;
}package org.whh.excel.annotation;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** Excel对象注解* 用于标识字段是一个对象或对象集合需要按多级结构处理* * author whh*/
Target(ElementType.FIELD)
Retention(RetentionPolicy.RUNTIME)
public interface ExcelObject {/*** 对象类型*/Class? type() default Object.class;/*** 是否是集合类型*/boolean isCollection() default false;/*** 集合元素类型当isCollection为true时使用*/Class? elementType() default Object.class;/*** 层级顺序数字越小层级越高*/int order() default 0;/*** 说明*/String description() default ;
}package org.whh.excel.converter;/*** Excel字段转换器接口* 用于实现字段值的双向转换* * param T 实体类字段类型* param E Excel中显示的类型* author whh*/
public interface ExcelConverterT, E {/*** 导出时的转换将实体类字段值转换为Excel中显示的值* * param value 实体类字段值* param params 转换参数* return Excel中显示的值*/E convertToExcel(T value, String[] params);/*** 导入时的转换将Excel中的值转换为实体类字段值* * param value Excel中的值* param params 转换参数* return 实体类字段值*/T convertFromExcel(E value, String[] params);
}package org.whh.excel.util;import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.whh.excel.annotation.ExcelColumn;
import org.whh.excel.annotation.ExcelConvert;
import org.whh.excel.annotation.ExcelObject;
import org.whh.excel.converter.ExcelConverter;import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;/*** 通用Excel导入导出工具类* 基于注解自动处理多级数据结构的导入导出* 支持ExcelColumn、ExcelObject、ExcelConvert注解** author whh*/
Slf4j
public class ExcelUtils {/*** 导出数据到Excel通用方法*/public static T void exportExcel(ListT data, ClassT clazz, String fileName, HttpServletResponse response) {try (Workbook workbook new XSSFWorkbook()) {Sheet sheet workbook.createSheet();// 解析实体类结构ListFieldInfo allFields parseEntityStructure(clazz);// 创建表头createHeader(sheet, allFields);// 填充数据int currentRow 1;for (T item : data) {currentRow fillMultiLevelData(sheet, item, allFields, currentRow);}// 设置响应头response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setHeader(Content-Disposition, attachment; filename URLEncoder.encode(fileName .xlsx, UTF-8));workbook.write(response.getOutputStream());log.info(Excel导出成功);} catch (Exception e) {log.error(Excel导出失败, e);throw new RuntimeException(Excel导出失败, e);}}/*** 导入Excel数据通用方法*/public static T ListT importExcel(InputStream inputStream, ClassT clazz) {ListT result new ArrayList();try (Workbook workbook WorkbookFactory.create(inputStream)) {Sheet sheet workbook.getSheetAt(0);// 解析实体类结构ListFieldInfo allFields parseEntityStructure(clazz);// 读取数据并构建对象result parseMultiLevelData(sheet, clazz, allFields);log.info(Excel导入成功共导入 {} 条记录, result.size());} catch (Exception e) {log.error(Excel导入失败, e);throw new RuntimeException(Excel导入失败, e);}return result;}/*** 解析实体类结构获取所有层级的字段信息*/private static T ListFieldInfo parseEntityStructure(ClassT clazz) {ListFieldInfo allFields new ArrayList();collectAllFields(clazz, allFields, 0);// 按层级和索引排序allFields.sort((a, b) - {if (a.level ! b.level) {return Integer.compare(a.level, b.level);}return Integer.compare(a.columnIndex, b.columnIndex);});// 重新分配列索引int currentIndex 0;for (FieldInfo fieldInfo : allFields) {if (!fieldInfo.isObjectField) {fieldInfo.columnIndex currentIndex;}}return allFields;}/*** 递归收集所有字段信息*/private static void collectAllFields(Class? clazz, ListFieldInfo allFields, int level) {Field[] fields clazz.getDeclaredFields();for (Field field : fields) {field.setAccessible(true);ExcelColumn excelColumn field.getAnnotation(ExcelColumn.class);ExcelObject excelObject field.getAnnotation(ExcelObject.class);if (excelColumn ! null || excelObject ! null) {FieldInfo fieldInfo new FieldInfo();fieldInfo.field field;fieldInfo.excelColumn excelColumn;fieldInfo.excelObject excelObject;fieldInfo.excelConvert field.getAnnotation(ExcelConvert.class);fieldInfo.level level;fieldInfo.isObjectField (excelObject ! null);fieldInfo.isCollectionField (excelObject ! null excelObject.isCollection());if (excelColumn ! null) {fieldInfo.columnIndex excelColumn.index() ! -1 ? excelColumn.index() : Integer.MAX_VALUE;}// 初始化转换器if (fieldInfo.excelConvert ! null) {try {SuppressWarnings(unchecked)ExcelConverterObject, Object converter (ExcelConverterObject, Object)fieldInfo.excelConvert.converter().getDeclaredConstructor().newInstance();fieldInfo.converter converter;} catch (Exception e) {log.warn(初始化转换器失败: {}, fieldInfo.excelConvert.converter().getName());}}allFields.add(fieldInfo);// 如果是对象类型递归收集子字段if (fieldInfo.isObjectField) {Class? actualType getActualType(field);if (actualType ! Object.class) {collectAllFields(actualType, allFields, level 1);}}}}}/*** 获取字段的实际类型*/private static Class? getActualType(Field field) {ExcelObject excelObject field.getAnnotation(ExcelObject.class);if (excelObject ! null) {if (excelObject.isCollection() excelObject.elementType() ! Object.class) {return excelObject.elementType();} else if (!excelObject.isCollection() excelObject.type() ! Object.class) {return excelObject.type();}}Type genericType field.getGenericType();if (genericType instanceof ParameterizedType) {ParameterizedType paramType (ParameterizedType) genericType;Type[] actualTypeArguments paramType.getActualTypeArguments();if (actualTypeArguments.length 0) {return (Class?) actualTypeArguments[0];}}return field.getType();}/*** 创建表头*/private static void createHeader(Sheet sheet, ListFieldInfo allFields) {Row headerRow sheet.createRow(0);for (FieldInfo fieldInfo : allFields) {if (!fieldInfo.isObjectField) {Cell cell headerRow.createCell(fieldInfo.columnIndex);String headerName ;if (fieldInfo.excelColumn ! null StringUtils.isNotBlank(fieldInfo.excelColumn.name())) {headerName fieldInfo.excelColumn.name();} else {headerName fieldInfo.field.getName();}cell.setCellValue(headerName);// 设置列宽if (fieldInfo.excelColumn ! null fieldInfo.excelColumn.width() 0) {sheet.setColumnWidth(fieldInfo.columnIndex, fieldInfo.excelColumn.width() * 256);} else {sheet.setColumnWidth(fieldInfo.columnIndex, 20 * 256);}}}}/*** 填充多级数据*/private static int fillMultiLevelData(Sheet sheet, Object rootItem, ListFieldInfo allFields, int startRow) {try {// 展开为平面数据行ListMapString, Object dataRows expandToFlatRows(rootItem, allFields);// 填充每行数据for (int i 0; i dataRows.size(); i) {Row row sheet.createRow(startRow i);MapString, Object rowData dataRows.get(i);for (FieldInfo fieldInfo : allFields) {if (!fieldInfo.isObjectField) {Cell cell row.createCell(fieldInfo.columnIndex);// 使用层级特定的唯一键获取值String uniqueKey L fieldInfo.level _ fieldInfo.field.getName();Object value rowData.get(uniqueKey);String cellValue convertToExcelValue(value, fieldInfo);cell.setCellValue(cellValue);}}}// 创建合并单元格if (dataRows.size() 1) {createMergeRegions(sheet, rootItem, allFields, startRow, dataRows.size());}return startRow dataRows.size();} catch (Exception e) {log.error(填充多级数据失败, e);return startRow 1;}}/*** 展开对象为平面数据行*/private static ListMapString, Object expandToFlatRows(Object item, ListFieldInfo allFields) {ListMapString, Object result new ArrayList();try {expandObjectToRows(item, new HashMap(), allFields, result);} catch (Exception e) {log.warn(展开对象失败, e);}return result.isEmpty() ? Arrays.asList(new HashMap()) : result;}/*** 递归展开对象到行数据*/private static void expandObjectToRows(Object item, MapString, Object parentData,ListFieldInfo allFields, ListMapString, Object result) throws Exception {MapString, Object currentData new HashMap(parentData);ListObject childItems new ArrayList();// 收集当前层级的字段值for (FieldInfo fieldInfo : allFields) {if (fieldInfo.field.getDeclaringClass().isAssignableFrom(item.getClass())) {Object value fieldInfo.field.get(item);if (fieldInfo.isCollectionField value instanceof Collection) {childItems.addAll((Collection?) value);} else if (!fieldInfo.isObjectField) {// 使用层级字段名作为唯一键避免不同层级的同名字段相互覆盖String uniqueKey L fieldInfo.level _ fieldInfo.field.getName();currentData.put(uniqueKey, value); // 使用唯一键存储避免覆盖}}}// 如果有子对象递归处理if (!childItems.isEmpty()) {for (Object childItem : childItems) {expandObjectToRows(childItem, currentData, allFields, result);}} else {result.add(currentData);}}/*** 创建合并单元格支持任意多级数据结构*/private static void createMergeRegions(Sheet sheet, Object rootItem, ListFieldInfo allFields,int startRow, int totalRows) {try {// 构建层级结构树HierarchyNode rootNode buildHierarchyTree(rootItem, allFields, startRow);// 递归创建所有层级的合并区域createMergeRegionsRecursively(sheet, rootNode, allFields);} catch (Exception e) {log.warn(创建合并单元格失败, e);}}/*** 构建层级结构树支持任意层级*/private static HierarchyNode buildHierarchyTree(Object rootItem, ListFieldInfo allFields, int startRow) {HierarchyNode rootNode new HierarchyNode();rootNode.item rootItem;rootNode.level 0;rootNode.startRow startRow;// 递归构建层级树buildHierarchyTreeRecursively(rootNode, allFields);return rootNode;}/*** 递归构建层级结构树*/private static void buildHierarchyTreeRecursively(HierarchyNode node, ListFieldInfo allFields) {try {ListObject childItems new ArrayList();// 找到当前对象的子对象集合for (FieldInfo fieldInfo : allFields) {if (fieldInfo.field.getDeclaringClass().isAssignableFrom(node.item.getClass()) fieldInfo.isCollectionField) {Object value fieldInfo.field.get(node.item);if (value instanceof Collection) {childItems.addAll((Collection?) value);}break; // 每个对象只处理一个子集合}}// 为每个子对象创建节点int currentRow node.startRow;for (Object childItem : childItems) {HierarchyNode childNode new HierarchyNode();childNode.item childItem;childNode.level node.level 1;childNode.startRow currentRow;childNode.parent node;node.children.add(childNode);// 递归构建子节点buildHierarchyTreeRecursively(childNode, allFields);// 计算当前子节点占用的行数childNode.rowCount calculateNodeRowCount(childNode);currentRow childNode.rowCount;}// 如果没有子节点则为叶子节点占用1行if (childItems.isEmpty()) {node.rowCount 1;} else {// 有子节点时行数为所有子节点行数之和node.rowCount node.children.stream().mapToInt(child - child.rowCount).sum();}} catch (Exception e) {log.warn(构建层级树失败, e);node.rowCount 1;}}/*** 计算节点占用的行数*/private static int calculateNodeRowCount(HierarchyNode node) {if (node.children.isEmpty()) {return 1;}return node.children.stream().mapToInt(child - child.rowCount).sum();}/*** 递归创建所有层级的合并区域*/private static void createMergeRegionsRecursively(Sheet sheet, HierarchyNode node, ListFieldInfo allFields) {try {// 为当前层级的字段创建合并区域if (node.rowCount 1) {ListFieldInfo currentLevelFields allFields.stream().filter(f - !f.isObjectField f.level node.level).toList();for (FieldInfo fieldInfo : currentLevelFields) {CellRangeAddress mergeRegion new CellRangeAddress(node.startRow, node.startRow node.rowCount - 1,fieldInfo.columnIndex, fieldInfo.columnIndex);sheet.addMergedRegion(mergeRegion);// 设置居中样式setCellStyle(sheet, node.startRow, fieldInfo.columnIndex);}}// 递归处理子节点for (HierarchyNode child : node.children) {createMergeRegionsRecursively(sheet, child, allFields);}} catch (Exception e) {log.warn(递归创建合并区域失败, e);}}/*** 层级节点类支持任意层级的数据结构*/private static class HierarchyNode {Object item; // 当前对象int level; // 层级深度 (0根层级)int startRow; // 起始行号int rowCount; // 占用行数HierarchyNode parent; // 父节点ListHierarchyNode children new ArrayList(); // 子节点列表}/*** 设置单元格样式*/private static void setCellStyle(Sheet sheet, int row, int col) {try {Row sheetRow sheet.getRow(row);if (sheetRow ! null) {Cell cell sheetRow.getCell(col);if (cell ! null) {CellStyle style sheet.getWorkbook().createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);cell.setCellStyle(style);}}} catch (Exception e) {log.warn(设置单元格样式失败, e);}}/*** 转换值为Excel显示格式*/private static String convertToExcelValue(Object value, FieldInfo fieldInfo) {if (value null) {return fieldInfo.excelColumn ! null ? fieldInfo.excelColumn.defaultValue() : ;}// 使用转换器if (fieldInfo.converter ! null) {try {Object converted fieldInfo.converter.convertToExcel(value, fieldInfo.excelConvert.params());return String.valueOf(converted);} catch (Exception e) {log.warn(字段转换失败: {}, fieldInfo.field.getName());}}// 日期格式化if (value instanceof Date fieldInfo.excelColumn ! null StringUtils.isNotBlank(fieldInfo.excelColumn.dateFormat())) {SimpleDateFormat sdf new SimpleDateFormat(fieldInfo.excelColumn.dateFormat());return sdf.format((Date) value);}return String.valueOf(value);}/*** 解析Excel数据为对象列表*/private static T ListT parseMultiLevelData(Sheet sheet, ClassT clazz, ListFieldInfo allFields) {ListT result new ArrayList();try {if (sheet.getPhysicalNumberOfRows() 1) {log.warn(Excel文件没有数据行);return result;}// 读取表头Row headerRow sheet.getRow(0);MapString, Integer headerMap parseHeader(headerRow, allFields);// 读取所有数据行ListMapString, Object rowDataList readAllRows(sheet, headerMap, allFields);if (rowDataList.isEmpty()) {log.warn(没有读取到任何数据);return result;}// 构建多级对象结构result buildMultiLevelObjects(rowDataList, clazz, allFields);} catch (Exception e) {log.error(解析Excel数据失败, e);throw new RuntimeException(解析Excel数据失败, e);}return result;}/*** 解析Excel表头*/private static MapString, Integer parseHeader(Row headerRow, ListFieldInfo allFields) {MapString, Integer headerMap new HashMap();if (headerRow ! null) {// 按层级排序字段确保低层级的字段优先映射ListFieldInfo sortedFields allFields.stream().filter(f - !f.isObjectField f.excelColumn ! null).sorted((a, b) - Integer.compare(a.level, b.level)).toList();// 记录每个列名已经被哪些层级使用过MapString, SetInteger usedLevels new HashMap();for (int i 0; i headerRow.getPhysicalNumberOfCells(); i) {Cell cell headerRow.getCell(i);if (cell ! null) {String headerName cell.getStringCellValue();// 按层级顺序找到第一个未映射的匹配字段for (FieldInfo fieldInfo : sortedFields) {if (headerName.equals(fieldInfo.excelColumn.name())) {String uniqueKey L fieldInfo.level _ fieldInfo.field.getName();// 检查这个字段是否已经被映射过if (!headerMap.containsKey(uniqueKey)) {// 记录此列名在此层级的使用usedLevels.computeIfAbsent(headerName, k - new HashSet()).add(fieldInfo.level);headerMap.put(uniqueKey, i);break;}}}}}}log.debug(解析表头完成映射关系: {}, headerMap);return headerMap;}/*** 读取所有数据行*/private static ListMapString, Object readAllRows(Sheet sheet, MapString, Integer headerMap, ListFieldInfo allFields) {ListMapString, Object rowDataList new ArrayList();for (int i 1; i sheet.getLastRowNum(); i) {Row row sheet.getRow(i);if (row null) continue;MapString, Object rowData new HashMap();boolean hasData false;// 读取每个字段的值for (FieldInfo fieldInfo : allFields) {if (!fieldInfo.isObjectField fieldInfo.excelColumn ! null) {String fieldName fieldInfo.field.getName();// 使用层级特定的唯一键查找列索引String uniqueKey L fieldInfo.level _ fieldName;Integer colIndex headerMap.get(uniqueKey);if (colIndex ! null) {Cell cell row.getCell(colIndex);Object value getCellValue(cell, fieldInfo);// 使用层级特定的唯一键存储避免同名字段覆盖rowData.put(uniqueKey, value);if (value ! null !value.toString().trim().isEmpty()) {hasData true;}}}}if (hasData) {rowData.put(_rowIndex, i);rowDataList.add(rowData);}}// 填补合并单元格的空值问题fillMergedCellValues(rowDataList, allFields);log.debug(读取到 {} 行数据, rowDataList.size());return rowDataList;}/*** 填补合并单元格的空值问题* 严格按层级和子对象组隔离只在同层级同对象组内填补空值*/private static void fillMergedCellValues(ListMapString, Object rowDataList, ListFieldInfo allFields) {if (rowDataList.isEmpty()) return;// 按层级分组字段MapInteger, ListString levelFields new HashMap();for (FieldInfo fieldInfo : allFields) {if (!fieldInfo.isObjectField fieldInfo.excelColumn ! null) {String uniqueKey L fieldInfo.level _ fieldInfo.field.getName();levelFields.computeIfAbsent(fieldInfo.level, k - new ArrayList()).add(uniqueKey);}}// 找到最大层级叶子节点层级int maxLevel levelFields.keySet().stream().mapToInt(Integer::intValue).max().orElse(0);// 按层级逐级处理严格隔离for (int currentLevel 0; currentLevel maxLevel; currentLevel) {ListString currentLevelFieldKeys levelFields.get(currentLevel);if (currentLevelFieldKeys null || currentLevelFieldKeys.isEmpty()) continue;// 获取当前层级的关键字段ListString currentLevelKeyFields getKeyFieldNames(allFields, currentLevel);// 按当前层级的对象分组MapString, ListMapString, Object currentLevelGroups new LinkedHashMap();for (MapString, Object rowData : rowDataList) {String levelKey buildObjectKey(rowData, currentLevelKeyFields, currentLevel);currentLevelGroups.computeIfAbsent(levelKey, k - new ArrayList()).add(rowData);}// 为当前层级的每个对象组分别填补空值for (Map.EntryString, ListMapString, Object levelGroup : currentLevelGroups.entrySet()) {String levelKey levelGroup.getKey();ListMapString, Object levelRowData levelGroup.getValue();for (String fieldKey : currentLevelFieldKeys) {// 检查当前层级对象组内是否有空值需要填补boolean hasEmptyValue false;Object masterValue null;// 先找到第一个非空值作为主值for (MapString, Object rowData : levelRowData) {Object currentValue rowData.get(fieldKey);if (currentValue ! null !currentValue.toString().trim().isEmpty()) {if (masterValue null) {masterValue currentValue;}} else {hasEmptyValue true;}}// 只有同时满足有主值和有空值的条件才进行填充if (masterValue ! null hasEmptyValue) {int filledCount 0;for (MapString, Object rowData : levelRowData) {Object currentValue rowData.get(fieldKey);if (currentValue null || currentValue.toString().trim().isEmpty()) {rowData.put(fieldKey, masterValue);filledCount;}}}}}}}/*** 获取单元格值支持合并单元格*/private static Object getCellValue(Cell cell, FieldInfo fieldInfo) {if (cell null) return null;try {// 检查是否为合并单元格Sheet sheet cell.getSheet();int rowIndex cell.getRowIndex();int colIndex cell.getColumnIndex();// 查找合并区域for (CellRangeAddress range : sheet.getMergedRegions()) {if (range.isInRange(rowIndex, colIndex)) {// 如果是合并单元格获取合并区域的第一个单元格的值Row firstRow sheet.getRow(range.getFirstRow());if (firstRow ! null) {Cell firstCell firstRow.getCell(range.getFirstColumn());if (firstCell ! null firstCell ! cell) {return getCellValue(firstCell, fieldInfo);}}}}// 普通单元格处理switch (cell.getCellType()) {case STRING:String stringValue cell.getStringCellValue();return convertCellValue(stringValue, fieldInfo);case NUMERIC:double numericValue cell.getNumericCellValue();// 如果字段类型是Date尝试转换为日期if (fieldInfo.field.getType() Date.class) {return cell.getDateCellValue();} else if (fieldInfo.field.getType() Integer.class || fieldInfo.field.getType() int.class) {return (int) numericValue;}return numericValue;case BOOLEAN:return cell.getBooleanCellValue();default:return null;}} catch (Exception e) {log.warn(读取单元格值失败: {}, e.getMessage());return null;}}/*** 转换单元格值*/private static Object convertCellValue(String value, FieldInfo fieldInfo) {if (value null || value.trim().isEmpty()) return null;Class? fieldType fieldInfo.field.getType();try {// 如果有自定义转换器先尝试转换if (fieldInfo.converter ! null) {return fieldInfo.converter.convertFromExcel(value, new String[0]);}// 根据字段类型转换if (fieldType String.class) {return value;} else if (fieldType Integer.class || fieldType int.class) {return Integer.parseInt(value);} else if (fieldType Long.class || fieldType long.class) {return Long.parseLong(value);} else if (fieldType Double.class || fieldType double.class) {return Double.parseDouble(value);} else if (fieldType Boolean.class || fieldType boolean.class) {return Boolean.parseBoolean(value);} else if (fieldType Date.class) {// 尝试解析日期SimpleDateFormat[] formats {new SimpleDateFormat(yyyy-MM-dd HH:mm:ss),new SimpleDateFormat(yyyy-MM-dd),new SimpleDateFormat(yyyy/MM/dd)};for (SimpleDateFormat format : formats) {try {return format.parse(value);} catch (Exception ignored) {}}}return value;} catch (Exception e) {log.warn(转换单元格值失败: {} - {}, value, fieldType.getSimpleName());return value;}}/*** 构建多级对象结构*/private static T ListT buildMultiLevelObjects(ListMapString, Object rowDataList, ClassT clazz, ListFieldInfo allFields) {ListT result new ArrayList();try {// 找到最大层级深度int maxLevel allFields.stream().filter(f - !f.isObjectField).mapToInt(f - f.level).max().orElse(0);log.debug(最大层级深度: {}, maxLevel);// 按层级分组数据MapString, ListMapString, Object groupedData groupDataByHierarchy(rowDataList, allFields);// 构建根对象for (Map.EntryString, ListMapString, Object entry : groupedData.entrySet()) {T rootObject buildSingleObject(entry.getValue(), clazz, allFields, 0);if (rootObject ! null) {result.add(rootObject);}}} catch (Exception e) {log.error(构建多级对象失败, e);}return result;}/*** 按层级分组数据支持任意级别*/private static MapString, ListMapString, Object groupDataByHierarchy(ListMapString, Object rowDataList, ListFieldInfo allFields) {MapString, ListMapString, Object groupedData new LinkedHashMap();// 获取根层级的关键字段组合ListString rootKeyFieldNames getKeyFieldNames(allFields, 0);for (MapString, Object rowData : rowDataList) {// 构建根对象的唯一标识String key buildObjectKey(rowData, rootKeyFieldNames, 0);groupedData.computeIfAbsent(key, k - new ArrayList()).add(rowData);}return groupedData;}/*** 获取指定层级的关键字段名称列表*/private static ListString getKeyFieldNames(ListFieldInfo allFields, int level) {ListString keyFieldNames new ArrayList();// 获取当前层级的所有字段ListFieldInfo currentLevelFields allFields.stream().filter(f - !f.isObjectField f.level level).toList();// 收集所有标记为isKeytrue的字段for (FieldInfo field : currentLevelFields) {ExcelColumn column field.excelColumn;if (column ! null column.isKey()) {keyFieldNames.add(field.field.getName());}}// 如果没有找到任何关键字段使用第一个字段作为默认关键字段if (keyFieldNames.isEmpty() !currentLevelFields.isEmpty()) {keyFieldNames.add(currentLevelFields.get(0).field.getName());}return keyFieldNames;}/*** 构建对象的唯一标识键*/private static String buildObjectKey(MapString, Object rowData, ListString keyFieldNames, int level) {StringBuilder key new StringBuilder();for (String fieldName : keyFieldNames) {// 使用层级特定的唯一键获取值String uniqueKey L level _ fieldName;Object value rowData.get(uniqueKey);key.append(value ! null ? value.toString() : null).append(|);}return key.toString();}/*** 构建单个对象*/private static T T buildSingleObject(ListMapString, Object rowDataList, ClassT clazz, ListFieldInfo allFields, int level) {try {T object clazz.getDeclaredConstructor().newInstance();// 设置当前层级的字段值MapString, Object representativeRow findRepresentativeRow(rowDataList, allFields, level);ListFieldInfo currentLevelFields allFields.stream().filter(f - !f.isObjectField f.level level).toList();for (FieldInfo fieldInfo : currentLevelFields) {// 使用层级特定的唯一键避免同名字段覆盖问题String uniqueKey L fieldInfo.level _ fieldInfo.field.getName();Object value representativeRow.get(uniqueKey);if (value ! null) {fieldInfo.field.set(object, value);}}ListFieldInfo childCollectionFields allFields.stream().filter(f - f.isCollectionField f.level level).toList();for (FieldInfo collectionField : childCollectionFields) {Class? elementType getActualType(collectionField.field);// 按子对象分组MapString, ListMapString, Object childGroups groupChildData(rowDataList, allFields, level 1);ListObject childObjects new ArrayList();for (ListMapString, Object childRowData : childGroups.values()) {Object childObject buildSingleObject(childRowData, elementType, allFields, level 1);if (childObject ! null) {childObjects.add(childObject);}}collectionField.field.set(object, childObjects);}return object;} catch (Exception e) {log.error(构建对象失败: {}, e.getMessage());return null;}}/*** 找到代表性的行数据用于获取当前层级的字段值* 对于每个字段优先选择有值的行数据*/private static MapString, Object findRepresentativeRow(ListMapString, Object rowDataList, ListFieldInfo allFields, int level) {if (rowDataList.isEmpty()) {return new HashMap();}if (rowDataList.size() 1) {return rowDataList.get(0);}// 获取当前层级的所有字段ListFieldInfo currentLevelFields allFields.stream().filter(f - !f.isObjectField f.level level).toList();// 创建一个组合的代表性行数据MapString, Object representativeRow new HashMap(rowDataList.get(0));// 对于每个字段找到第一个非空值for (FieldInfo fieldInfo : currentLevelFields) {String uniqueKey L fieldInfo.level _ fieldInfo.field.getName();Object currentValue representativeRow.get(uniqueKey);// 如果当前值是空的尝试从其他行中找到非空值if (currentValue null || currentValue.toString().trim().isEmpty()) {for (MapString, Object rowData : rowDataList) {Object value rowData.get(uniqueKey);if (value ! null !value.toString().trim().isEmpty()) {representativeRow.put(uniqueKey, value);break;}}}}return representativeRow;}/*** 分组子数据支持任意级别*/private static MapString, ListMapString, Object groupChildData(ListMapString, Object rowDataList, ListFieldInfo allFields, int childLevel) {MapString, ListMapString, Object childGroups new LinkedHashMap();// 获取子层级的关键字段组合ListString childKeyFieldNames getKeyFieldNames(allFields, childLevel);for (MapString, Object rowData : rowDataList) {// 构建子对象的唯一标识String key buildObjectKey(rowData, childKeyFieldNames, childLevel);childGroups.computeIfAbsent(key, k - new ArrayList()).add(rowData);}return childGroups;}/*** 字段信息类*/private static class FieldInfo {Field field;ExcelColumn excelColumn;ExcelObject excelObject;ExcelConvert excelConvert;ExcelConverterObject, Object converter;int level;int columnIndex;boolean isObjectField;boolean isCollectionField;}
}
