Easyexcel 导出数据一对多关系导出数据集合

淡淡的烟草味﹌ 2024-03-17 15:16 75阅读 0赞

### 客户要求 ###

要求导出的表格如图  
![在这里插入图片描述][5fbfc3e95e984ddfaa1d87c61d657566.png]  
实现这样表格 很多人会想到动态表头，easypoi可以直接实现，但是我用的是easyexcel,而easyexcel自身并没有提供自动合并的功能所以还是需要自己来合并。

### 代码如下 ###

#### 首先我们来看下将嵌套数据平铺，不进行合并导出的Excel ####

![在这里插入图片描述][cfb3c0864b594283a2fb91d5c091aaf2.png]

#### 实现思路 ####

首先我们得把原来嵌套的订单商品信息给平铺了，创建一个专门的导出对象OrderData，包含订单和商品信息，二级表头可以通过设置@ExcelProperty的value为数组来实现

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class OrderData {
        
    
    
        @ExcelProperty(value = "订单编码")
        @ColumnWidth(20)
        @CollectCustomMerge(needMerge = true, isPk = true)
        private String orderSn;
        @ExcelProperty(value = "创建时间")
        @ColumnWidth(20)
        @DateTimeFormat("yyyy-MM-dd")
        @CollectCustomMerge(needMerge = true)
        private Date createTime;
        @ExcelProperty(value = "收货地址")
        @CollectCustomMerge(needMerge = true)
        @ColumnWidth(20)
        private String receiverAddress;
        @ExcelProperty(value = {
        "商品信息", "商品编码"})
        @ColumnWidth(20)
        private String productSn;
        @ExcelProperty(value = {
        "商品信息", "商品名称"})
        @ColumnWidth(20)
        private String name;
        @ExcelProperty(value = {
        "商品信息", "商品标题"})
        @ColumnWidth(30)
        private String subTitle;
    
    }

#### 创建一个自定义注解CollectCustomMerge，用于标记哪些属性需要合并，哪个是主键 ####

/**
     * 自定义注解，用于判断是否需要合并以及合并的主键
     * 标记哪些属性需要合并，哪个是主键
     */
    @Target({
        ElementType.FIELD})
    @Retention(RetentionPolicy.RUNTIME)
    @Inherited
    public @interface CollectCustomMerge {
        
    
        /**
         * 是否需要合并单元格
         */
        boolean needMerge() default false;
    
        /**
         * 是否是主键,即该字段相同的行合并
         */
        boolean isPk() default false;
    }

#### 创建自定义单元格合并策略类CustomMergeStrategy，当Excel中两列主键相同时，合并被标记需要合并的列 ####

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.write.handler.RowWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import com.easyexcle.demo.easyExcel.annotion.CollectCustomMerge;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.lang.reflect.Field;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @Author: lkz
     * @Title: CustomMergeStrategy
     * @Description: 自定义单元格合并策略类CustomMergeStrategy，当Excel中两列主键相同时，合并被标记需要合并的列
     * @Date: 2023/7/5 9:16
     */
    public class CustomMergeStrategy implements RowWriteHandler {
        
        /**
         * 主键下标
         */
        private Integer pkIndex;
    
        /**
         * 需要合并的列的下标集合
         */
        private List<Integer> needMergeColumnIndex = new ArrayList<>();
    
        /**
         * DTO数据类型
         */
        private Class<?> elementType;
    
        public CustomMergeStrategy(Class<?> elementType) {
        
            this.elementType = elementType;
        }
    
        @Override
        public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
        
    
        }
    
        @Override
        public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        
    
        }
    
        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        
            // 如果是标题,则直接返回
            if (isHead) {
        
                return;
            }
    
            // 获取当前sheet
            Sheet sheet = writeSheetHolder.getSheet();
    
            // 获取标题行
            Row titleRow = sheet.getRow(0);
    
            if (null == pkIndex) {
        
                this.lazyInit(writeSheetHolder);
            }
    
            // 判断是否需要和上一行进行合并
            // 不能和标题合并，只能数据行之间合并
            if (row.getRowNum() <= 1) {
        
                return;
            }
            // 获取上一行数据
            Row lastRow = sheet.getRow(row.getRowNum() - 1);
            // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
            if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
        
                for (Integer needMerIndex : needMergeColumnIndex) {
        
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                            needMerIndex, needMerIndex);
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                }
            }
        }
    
        /**
         * 初始化主键下标和需要合并字段的下标
         */
        private void lazyInit(WriteSheetHolder writeSheetHolder) {
        
    
            // 获取当前sheet
            Sheet sheet = writeSheetHolder.getSheet();
    
            // 获取标题行
            Row titleRow = sheet.getRow(0);
            // 获取DTO的类型
            Class<?> eleType = this.elementType;
    
            // 获取DTO所有的属性
            Field[] fields = eleType.getDeclaredFields();
    
            // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
            for (Field theField : fields) {
        
                // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
                ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
                // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
                if (null == easyExcelAnno) {
        
                    continue;
                }
                // 获取自定义的注解，用于合并单元格
                CollectCustomMerge collectCustomMerge = theField.getAnnotation(CollectCustomMerge.class);
    
                // 没有@CustomMerge注解的默认不合并
                if (null == collectCustomMerge) {
        
                    continue;
                }
    
                for (int index = 0; index < fields.length; index++) {
        
                    Cell theCell = titleRow.getCell(index);
                    // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                    if (null == theCell) {
        
                        continue;
                    }
                    // 将字段和excel的表头匹配上
                    if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
        
                        if (collectCustomMerge.isPk()) {
        
                            pkIndex = index;
                        }
    
                        if (collectCustomMerge.needMerge()) {
        
                            needMergeColumnIndex.add(index);
                        }
                    }
                }
            }
    
            // 没有指定主键，则异常
            if (null == this.pkIndex) {
        
                throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
            }
    
        }
    }

#### 测试最终效果 ####

public class CollectTest {
        
    
    
        public static void main(String[] args) {
        
        
            List<OrderData> orderDataList=new ArrayList<>();
            orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称1","标题1"));
            orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称2","标题2"));
            orderDataList.add(new OrderData("2",new Date(),"北京","002","商品名称02","标题02"));
            orderDataList.add(new OrderData("2",new Date(),"北京","003","商品名称03","标题1"));
            orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称3","标题3"));
            orderDataList.add(new OrderData("3",new Date(),"上海","001","商品名称3","标题3"));
            orderDataList.add(new OrderData("3",new Date(),"天津","001","商品名称302","标题302"));
            orderDataList.add(new OrderData("3",new Date(),"上海","001","商品名称303","标题303"));
            orderDataList.add(new OrderData("4",new Date(),"杭州","001","商品名称4","标题4"));
            orderDataList.add(new OrderData("5",new Date(),"杭州","001","商品名称5","标题5"));
    
    
            EasyExcel.write("D:/pdf/CollectExport.xlsx").head(OrderData.class)
                    .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
                    .sheet().doWrite(orderDataList);
    
        }
    
    }

#### 注意事项 ####

![在这里插入图片描述][001c4374249045859ae81304c3cc1cae.png]

#### 效果 ####

![在这里插入图片描述][53dc2643d3974797ab0d8256f576a9a3.png]

#### 很多人说只能合并两行，下面是我测试的代码 ####

public class CollectTest {
        
    
    
        public static void main(String[] args) {
        
            /**
             * 这里的数据要注意一下.主键相同的要紧挨着放入，不然不放咋一起还是不能合并
             */
            List<OrderData> orderDataList=new ArrayList<>();
            orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称1","标题1"));
            orderDataList.add(new OrderData("1",new Date(),"南京2","001","商品名称2","标题2"));
            orderDataList.add(new OrderData("1",new Date(),"南京3","001","商品名称2","标题3"));
            orderDataList.add(new OrderData("2",new Date(),"北京","002","商品名称02","标题02"));
            orderDataList.add(new OrderData("2",new Date(),"北京","003","商品名称03","标题1"));
            orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称3","标题3"));
            orderDataList.add(new OrderData("3",new Date(),"上海","001","商品名称3","标题3"));
            orderDataList.add(new OrderData("3",new Date(),"天津","001","商品名称302","标题302"));
            orderDataList.add(new OrderData("3",new Date(),"上海2","0012","商品名称3032","标题303"));
            orderDataList.add(new OrderData("3",new Date(),"上海3","0013","商品名称3033","标题303"));
            orderDataList.add(new OrderData("3",new Date(),"上海4","0014","商品名称3034","标题303"));
            orderDataList.add(new OrderData("3",new Date(),"上海5","0015","商品名称3035","标题303"));
            orderDataList.add(new OrderData("4",new Date(),"杭州","001","商品名称4","标题4"));
            orderDataList.add(new OrderData("5",new Date(),"杭州","001","商品名称5","标题5"));
    
    
            EasyExcel.write("D:/pdf/CollectExport.xlsx").head(OrderData.class)
                    .registerWriteHandler(new CustomMergeStrategy(OrderData.class))
                    .sheet().doWrite(orderDataList);
    
        }

![在这里插入图片描述][54a3d2861f8c4c78b1bbc1839f965859.png]

[5fbfc3e95e984ddfaa1d87c61d657566.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/da8f3e402d85459f9d512019ea3697ba.png
[cfb3c0864b594283a2fb91d5c091aaf2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/9c297ca569bb471ebeb0d416494511f0.png
[001c4374249045859ae81304c3cc1cae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/0b95d393a541445d970953b34412a216.png
[53dc2643d3974797ab0d8256f576a9a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/399185a6e02a47a8a97a3d22720bbb2b.png
[54a3d2861f8c4c78b1bbc1839f965859.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f5fe2b33ef1e46f3aefb72e671e7a71a.png