easyExcel嵌套子集合导出Excel
我想要的Excel效果
说明:
1.创建两个自定义注解:@ExcelMerge(表示主对象内的单个属性,后续会根据子集合的大小合并下面的单元格),@ExcelNestedList(表示嵌套的子集合)
2.NestedDataConverter.java 会把查询到的数据转换为一行一行的,相当于主表 left join 子表 ON 主.id=子.主id的形式
SmartMergeStrategy.java 在使用EasyExcel时使用的策略类,会计算每组数据需要合并的row层数
3. public void t3()执行的Main方法.
package xxx.annotation;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** 标记主实体中需要合并的字段*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelMerge {// 可以添加其他属性，如合并策略等
}
package xxx.annotation;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** 标记包含子列表的字段*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelNestedList {Class<?> value(); // 指定子元素的类型
}package xxx.common;import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;import org.springframework.stereotype.Component;import com.alibaba.excel.annotation.ExcelProperty;import xxx.annotation.ExcelNestedList;public class NestedDataConverter {public static List<List<Object>> convertToNestedListWithNesting(List<?> dataList) {List<List<Object>> result = new ArrayList<>();if (dataList.isEmpty()) {return result;}// Extract parameter names to maintain the orderClass<?> clazz = dataList.get(0).getClass();List<String> parameterNames = extractParameterNames(clazz);for (Object data : dataList) {List<Field> nestedFields = Arrays.stream(clazz.getDeclaredFields()).filter(f -> f.isAnnotationPresent(ExcelNestedList.class)).collect(Collectors.toList());if (nestedFields.isEmpty()) {// No nested fields, convert the object directlyresult.add(convertSingleToList(data, parameterNames));} else {Map<String, Object> fieldValues = new HashMap<>();populateFieldValues(data, fieldValues);for (Field nestedField : nestedFields) {try {nestedField.setAccessible(true);List<?> nestedList = (List<?>) nestedField.get(data);Class<?> nestedClass = nestedField.getAnnotation(ExcelNestedList.class).value();if (nestedList == null || nestedList.isEmpty()) {// Add a row with empty strings for nested fieldsList<Object> row = buildRowFromFieldValues(fieldValues, parameterNames, nestedClass, true);result.add(row);} else {for (Object nestedItem : nestedList) {Map<String, Object> nestedFieldValues = new HashMap<>(fieldValues);populateFieldValues(nestedItem, nestedFieldValues);List<Object> row = buildRowFromFieldValues(nestedFieldValues, parameterNames, nestedClass, false);result.add(row);}}} catch (IllegalAccessException e) {throw new RuntimeException("Failed to access nested data", e);}}}}return result;}private static void populateFieldValues(Object data, Map<String, Object> fieldValues) {for (Field field : data.getClass().getDeclaredFields()) {ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);if (excelProperty != null) {try {field.setAccessible(true);Object value = field.get(data);fieldValues.put(excelProperty.value()[0], value != null ? value : ""); // Replace null with empty string} catch (IllegalAccessException e) {fieldValues.put(excelProperty.value()[0], ""); // Add empty string if inaccessible}}}}private static List<Object> buildRowFromFieldValues(Map<String, Object> fieldValues, List<String> parameterNames, Class<?> nestedClass, boolean isEmptyNested) {List<Object> row = new ArrayList<>();for (String paramName : parameterNames) {if (fieldValues.containsKey(paramName)) {row.add(fieldValues.get(paramName));} else if (isEmptyNested && isNestedField(paramName, nestedClass)) {row.add(""); // Add empty string for empty nested fields}}return row;}private static boolean isNestedField(String paramName, Class<?> nestedClass) {return Arrays.stream(nestedClass.getDeclaredFields()).anyMatch(f -> f.isAnnotationPresent(ExcelProperty.class) && f.getAnnotation(ExcelProperty.class).value()[0].equals(paramName));}private static List<Object> convertSingleToList(Object data, List<String> parameterNames) {List<Object> row = new ArrayList<>();Map<String, Object> fieldValues = new HashMap<>();for (Field field : data.getClass().getDeclaredFields()) {ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);if (excelProperty != null) {try {field.setAccessible(true);fieldValues.put(excelProperty.value()[0], field.get(data));} catch (IllegalAccessException e) {fieldValues.put(excelProperty.value()[0], ""); // Add empty string if inaccessible}}}// Add values in the order of parameterNamesfor (String paramName : parameterNames) {row.add(fieldValues.getOrDefault(paramName, ""));}return row;}public static List<String> extractParameterNames(Class<?> clazz) {List<String> parameterNames = new ArrayList<>();for (Field field : clazz.getDeclaredFields()) {// Check if the field is annotated with @ExcelPropertyExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);// Check if the field is annotated with @ExcelNestedListExcelNestedList nestedList = field.getAnnotation(ExcelNestedList.class);if (excelProperty != null && nestedList == null) {parameterNames.add(excelProperty.value()[0]); // Add the parameter name}// Check if the field is annotated with @ExcelNestedList
// ExcelNestedList nestedList = field.getAnnotation(ExcelNestedList.class);if (nestedList != null) {// Recursively extract parameter names from the nested classClass<?> nestedClass = nestedList.value();parameterNames.addAll(extractParameterNames(nestedClass));}}return parameterNames;}
}package xxx.common;import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;import xxx.annotation.ExcelMerge;public class SmartMergeStrategy extends AbstractMergeStrategy {private final List<?> dataList;private final Class<?> clazz;private final Map<Integer, List<int[]>> mergeInfo = new HashMap<>(); public SmartMergeStrategy(List<?> dataList, Class<?> clazz) {this.dataList = dataList;this.clazz = clazz;prepareMergeInfo();}private void prepareMergeInfo() {// 获取所有带有@ExcelMerge注释的字段List<Field> mergeFields = Arrays.stream(clazz.getDeclaredFields()).filter(field -> field.isAnnotationPresent(ExcelMerge.class)).collect(Collectors.toList());int currentRow = 1; // Start from row 1 (after the header)从第 1 行开始（标题之后）for (Object data : dataList) {try {// Get the `id` field value
// Field idField = clazz.getDeclaredField("id");
// idField.setAccessible(true);
// Object idValue = idField.get(data);// 获取嵌套的 `forwards` 列表
// Field nestedField = clazz.getDeclaredField("forwards");Field nestedField = Arrays.stream(clazz.getDeclaredFields()).filter(f -> List.class.isAssignableFrom(f.getType())).findFirst().orElseThrow(() -> new RuntimeException("未找到 List 类型字段"));nestedField.setAccessible(true);List<?> nestedList = (List<?>) nestedField.get(data);int nestedSize = (nestedList != null) ? nestedList.size() : 0;int startRow = currentRow;int endRow = (nestedSize > 0) ? (currentRow + nestedSize - 1) : currentRow;// 计算每个“@ExcelMerge”列的合并范围for (Field field : mergeFields) {int colIndex = getColumnIndex(field);if (colIndex >= 0 && startRow != endRow) {mergeInfo.computeIfAbsent(colIndex, k -> new ArrayList<>()).add(new int[]{startRow, endRow});}}// Update the current row pointer更新当前行指针currentRow = endRow + 1;} catch (IllegalAccessException e) {e.printStackTrace();}}}private int getColumnIndex(Field field) {ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);if (excelProperty != null) {String[] values = excelProperty.value();if (values.length > 0) {String columnName = values[0];List<Field> allFields = Arrays.stream(clazz.getDeclaredFields()).filter(f -> f.isAnnotationPresent(ExcelProperty.class)).collect(Collectors.toList());for (int i = 0; i < allFields.size(); i++) {Field currentField = allFields.get(i);ExcelProperty property = currentField.getAnnotation(ExcelProperty.class);if (property != null && property.value().length > 0 && property.value()[0].equals(columnName)) {return i;}}}}return -1;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {int colIndex = cell.getColumnIndex();if (mergeInfo.containsKey(colIndex)) {List<int[]> ranges = mergeInfo.get(colIndex);for (int[] range : ranges) {int startRow = range[0];int endRow = range[1];if (cell.getRowIndex() == startRow) {CellRangeAddress region = new CellRangeAddress(startRow, endRow, colIndex, colIndex);sheet.addMergedRegion(region);}}}}}
package xxx.entity;import java.util.List;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;
import xxx.annotation.ExcelMerge;
import xxx.annotation.ExcelNestedList;@Data
@TableName(value = "bkdb3.actions")
public class Action {@TableId(type = IdType.AUTO)@ExcelProperty("ID")@ExcelMergeprivate Long id;@ExcelProperty("Struts-Conf")@ExcelMergeprivate String strutsconfName;@ExcelIgnoreprivate String attribute;@ExcelIgnoreprivate String name;@ExcelIgnoreprivate String parameter;@ExcelProperty("Path")@ExcelMergeprivate String path;@ExcelProperty("ActionClassPath")@ExcelMergeprivate String type;// 非数据库字段，用于关联 forward 列表@TableField(exist = false)@ExcelProperty("forwards")@ExcelNestedList(Forward.class)private List<Forward> forwards;}
package xxx.entity;import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;@Data
@TableName(value = "bkdb3.forwards")
public class Forward {@TableId(type = IdType.AUTO)private Long id;private Long actionId; // 外键，关联 actions 表private String name;@ExcelProperty("forwardPath")private String path;}
/**把Action Forward输出为以下这种形式| Action ID | Action Name | Action Path | Forward Name | Forward Path || --------- | ----------- | ----------- | ------------ | ------------ || 1 | doLogin | /login | success | /main.jsp || | | | error | /login.jsp || 2 | doSearch | /search | next | /result.jsp |**/@Disabled@Testpublic void t3() {QueryWrapper<Action> wrapper = new QueryWrapper<>();wrapper.orderByAsc("id");List<Action> actions = actionMapper.selectList(wrapper);// 第二步：查询所有 forwards 一次性（推荐，一次查库，避免N+1）List<Long> actionIds = actions.stream().map(Action::getId).collect(Collectors.toList());if (!actionIds.isEmpty()) {List<Forward> allForwards = forwardMapper.selectList(new QueryWrapper<Forward>().in("action_id", actionIds));// 第三步：将 forwards 按照 actionId 分组Map<Long, List<Forward>> forwardMap = allForwards.stream().collect(Collectors.groupingBy(Forward::getActionId));// 第四步：将 forward 分别赋值到每个 Action 上for (Action action : actions) {List<Forward> childForwards = forwardMap.getOrDefault(action.getId(), new ArrayList<>());action.setForwards(childForwards);}}log.info("actions size={}",actions.size());List<List<Object>> lists = NestedDataConverter.convertToNestedListWithNesting(actions);// 4. 创建并注册合并策略SmartMergeStrategy mergeStrategy = new SmartMergeStrategy(actions, Action.class);// 获取所有可能的表头字段List<String> heads = NestedDataConverter.extractParameterNames(Action.class);// 5. 导出ExcelEasyExcel.write(analyzeProperties.getExcel1(),Action.class).registerWriteHandler(mergeStrategy) // 注册合并策略
// .head(createHead(heads)) // 动态生成表头.sheet("Action").doWrite(lists);log.info("导出历史记录成功");}