react 虚拟列表

使用Java EasyExcel导出树形结构数据到Excel

下面我将提供一个完整的解决方案，使用EasyExcel实现将包含子列表的对象集合导出为Excel，并保持树形结构。

实现思路

1. 创建主对象和子对象的数据结构
2. 使用EasyExcel的注解配置列映射
3. 实现自定义单元格合并策略
4. 构建数据并导出Excel

完整代码实现

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;

// 主对象类
@HeadRowHeight(20)
@ContentRowHeight(15)
public class MainObject {
    @ExcelProperty(value = "主ID", index = 0)
    private String mainId;
    
    @ExcelProperty(value = "主名称", index = 1)
    private String mainName;
    
    @ExcelProperty(value = "主描述", index = 2)
    private String mainDescription;
    
    // 子对象列表
    private List<SubObject> subList;

    // 构造方法、getter和setter
    public MainObject() {}
    
    public MainObject(String mainId, String mainName, String mainDescription) {
        this.mainId = mainId;
        this.mainName = mainName;
        this.mainDescription = mainDescription;
        this.subList = new ArrayList<>();
    }
    
    // 省略getter和setter方法
}

// 子对象类
public class SubObject {
    @ExcelProperty(value = "子ID", index = 3)
    private String id;
    
    @ExcelProperty(value = "父ID", index = 4)
    private String parentId;
    
    @ExcelProperty(value = "名称", index = 5)
    private String name;
    
    @ExcelProperty(value = "大小", index = 6)
    private Integer size;

    // 构造方法、getter和setter
    public SubObject() {}
    
    public SubObject(String id, String parentId, String name, Integer size) {
        this.id = id;
        this.parentId = parentId;
        this.name = name;
        this.size = size;
    }
    
    // 省略getter和setter方法
}

// 导出服务类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.util.ArrayList;
import java.util.List;

public class ExcelExportService {
    
    // 自定义合并策略
    public static class MergeStrategy extends AbstractCellWriteHandler {
        private List<Integer> mergeRowIndexes = new ArrayList<>();
        private int mergeColumnCount;
        
        public MergeStrategy(List<Integer> mergeRowIndexes, int mergeColumnCount) {
            this.mergeRowIndexes = mergeRowIndexes;
            this.mergeColumnCount = mergeColumnCount;
        }
        
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                                   List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            
            if (isHead || cell.getRowIndex() == 0) {
                return;
            }
            
            int rowIndex = cell.getRowIndex();
            int colIndex = cell.getColumnIndex();
            
            // 只处理前mergeColumnCount列
            if (colIndex < mergeColumnCount) {
                // 检查是否需要合并
                if (mergeRowIndexes.contains(rowIndex)) {
                    // 找到相同主ID的起始行和结束行
                    int startRow = rowIndex;
                    int endRow = rowIndex;
                    
                    for (int i = rowIndex + 1; i <= writeSheetHolder.getSheet().getLastRowNum(); i++) {
                        if (mergeRowIndexes.contains(i)) {
                            break;
                        }
                        endRow = i;
                    }
                    
                    // 合并单元格
                    if (startRow < endRow) {
                        CellRangeAddress region = new CellRangeAddress(startRow, endRow, colIndex, colIndex);
                        writeSheetHolder.getSheet().addMergedRegion(region);
                        
                        // 设置合并后单元格的样式
                        CellStyle cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
                        cellStyle.cloneStyleFrom(cell.getCellStyle());
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cell.getSheet().getRow(startRow).getCell(colIndex).setCellStyle(cellStyle);
                    }
                }
            }
        }
    }
    
    // 导出方法
    public static void exportToExcel(List<MainObject> dataList, String filePath) {
        // 准备数据
        List<Object> exportData = new ArrayList<>();
        List<Integer> mergeRowIndexes = new ArrayList<>();
        int rowIndex = 0;
        
        for (MainObject mainObj : dataList) {
            // 记录需要合并的行索引
            mergeRowIndexes.add(rowIndex);
            
            if (mainObj.getSubList() != null && !mainObj.getSubList().isEmpty()) {
                boolean isFirst = true;
                for (SubObject subObj : mainObj.getSubList()) {
                    // 创建一个包含主对象和子对象数据的复合对象
                    CompositeObject composite = new CompositeObject();
                    if (isFirst) {
                        composite.setMainId(mainObj.getMainId());
                        composite.setMainName(mainObj.getMainName());
                        composite.setMainDescription(mainObj.getMainDescription());
                        isFirst = false;
                    }
                    composite.setId(subObj.getId());
                    composite.setParentId(subObj.getParentId());
                    composite.setName(subObj.getName());
                    composite.setSize(subObj.getSize());
                    
                    exportData.add(composite);
                    rowIndex++;
                }
            } else {
                // 即使没有子对象，也要添加一行空数据
                CompositeObject composite = new CompositeObject();
                composite.setMainId(mainObj.getMainId());
                composite.setMainName(mainObj.getMainName());
                composite.setMainDescription(mainObj.getMainDescription());
                exportData.add(composite);
                rowIndex++;
            }
        }
        
        // 设置样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headWriteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        
        HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(
            headWriteCellStyle, contentWriteCellStyle);
        
        // 创建写入器
        EasyExcel.write(filePath, CompositeObject.class)
                .registerWriteHandler(styleStrategy)
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(15)) // 设置列宽
                .registerWriteHandler(new MergeStrategy(mergeRowIndexes, 3)) // 合并前3列
                .sheet("数据导出")
                .doWrite(exportData);
    }
    
    // 复合对象，用于导出
    public static class CompositeObject {
        @ExcelProperty(value = "主ID", index = 0)
        private String mainId;
        
        @ExcelProperty(value = "主名称", index = 1)
        private String mainName;
        
        @ExcelProperty(value = "主描述", index = 2)
        private String mainDescription;
        
        @ExcelProperty(value = "子ID", index = 3)
        private String id;
        
        @ExcelProperty(value = "父ID", index = 4)
        private String parentId;
        
        @ExcelProperty(value = "名称", index = 5)
        private String name;
        
        @ExcelProperty(value = "大小", index = 6)
        private Integer size;
        
        // 省略getter和setter方法
    }
    
    // 示例用法
    public static void main(String[] args) {
        List<MainObject> dataList = new ArrayList<>();
        
        // 创建示例数据
        MainObject main1 = new MainObject("M001", "主对象1", "这是第一个主对象");
        main1.getSubList().add(new SubObject("S001", "M001", "子对象1", 100));
        main1.getSubList().add(new SubObject("S002", "M001", "子对象2", 200));
        dataList.add(main1);
        
        MainObject main2 = new MainObject("M002", "主对象2", "这是第二个主对象");
        main2.getSubList().add(new SubObject("S003", "M002", "子对象3", 150));
        dataList.add(main2);
        
        MainObject main3 = new MainObject("M003", "主对象3", "这是第三个主对象");
        // 这个主对象没有子对象
        dataList.add(main3);
        
        // 导出到Excel
        exportToExcel(dataList, "树形结构数据.xlsx");
    }
}

关键点说明

1. 数据结构设计： · MainObject 表示主对象，包含主表属性和子对象列表 · SubObject 表示子对象，包含子表属性 · CompositeObject 用于导出的复合对象，包含主表和子表的所有字段
2. 合并策略： · 自定义 MergeStrategy 类处理单元格合并 · 根据主对象的起始行索引合并前几列（主表字段）
3. 样式配置： · 使用 HorizontalCellStyleStrategy 设置表头和内容样式 · 使用 SimpleColumnWidthStyleStrategy 设置列宽
4. 数据准备： · 将主对象和子对象数据转换为扁平化的导出数据 · 记录每个主对象的起始行索引，用于后续合并

使用说明

1. 添加EasyExcel依赖到你的项目中（Maven）：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

1. 根据你的实际业务需求调整对象属性和注解
2. 调用 ExcelExportService.exportToExcel() 方法导出数据

这个实现会生成一个Excel文件，其中主对象的数据在前几列合并显示，子对象的数据在后续列分开显示，形成树形结构。