EasyPoi一对多excel表格导出

效果如下图：

1、引入pom文件

<!--easypoi 一对多导入导出 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.2.0</version>
</dependency>

2、Controller

   /**
     * 导出
     */
    @Log(title = "导出", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(WmsReturnOrderCreateBo bo, HttpServletResponse response) {
        if (!getLoginUser().isAdmin()) {
            bo.setWarehouseId(getLoginUser().getWarehouseId());
        }
        List<ExportReturnOrderCreate> list = iWmsReturnOrderCreateService.getExport(bo);
        EasyPoiUtil.exportExcel(list, ExportReturnOrderCreate.class, "导出", true, response);
    }

3、模板类

package com.xxx.domain.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * 导出销退入库单
 *
 * @author bgq
 * @date 2024-03-15
 */
@Data
@ExcelIgnoreUnannotated
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 13)
@HeadFontStyle(color = 8)
public class ExportReturnOrderCreate implements Serializable {

    private static final long serialVersionUID = 1L;

    @Excel(name = "销退单号", needMerge = true, width = 20, height = 10)
    @HeadFontStyle(color = 10)
    private String returnOrderCode;

    @Excel(name = "出库单号", needMerge = true, width = 20, height = 10)
    private String deliveryOrderNo;

    @Excel(name = "发货单号", needMerge = true, width = 20, height = 10)
    private String deliveryOrderCode;

    @Excel(name = "质检单号", needMerge = true, width = 20, height = 10)
    private String returnCheckOrderNo;


    @Excel(name = "创建时间", needMerge = true, exportFormat = "yyyy-MM-dd HH:mm:ss" , width = 20, height = 10)
    private Date createTime;

    @ExcelCollection(name = "商品明细")
    private List<ExportReturnOrderCreateLine> orderCreateLines;

    private String itemCode;

    private String itemName;

    private Long planQty;

    private Long realQty;

    @Data
    public static class ExportReturnOrderCreateLine {

        @Excel(name = "商品名称", needMerge = true, width = 20, height = 10)
        private String itemName;

        @Excel(name = "应收数量", needMerge = true, width = 20, height = 10)
        private Long planQty;

        @Excel(name = "实收数量", needMerge = true, width = 20, height = 10)
        private Long realQty;
        
    }
}

4、Service

@Override
    public List<ExportReturnOrderCreate> getExport(WmsReturnOrderCreateBo bo) {
        Long exportCount = getExportCount(bo);
        if (exportCount > 10000){
            throw new IllegalArgumentException("导出数据量过大，请缩小查询范围");
        }
        List<ExportReturnOrderCreate> export = baseMapper.export(buildExportQueryWrapper(bo));
        List<ExportReturnOrderCreate> exportList = new ArrayList<>();
        Map<String,String> exportMap = new HashMap<>();
        Map<String,List<ExportReturnOrderCreate.ExportReturnOrderCreateLine>> lineMap = new HashMap<>();
        for (ExportReturnOrderCreate exportReturnOrderCreate : export) {
            String key = exportReturnOrderCreate.getReturnOrderCode();
            if (exportMap.get(key) == null) {
                exportList.add(exportReturnOrderCreate);
            }
            exportMap.put(key,"1");
            ExportReturnOrderCreate.ExportReturnOrderCreateLine line = getExportReturnOrderCreateLine(exportReturnOrderCreate);
            if (CollUtil.isEmpty(lineMap.get(key))){
                List<ExportReturnOrderCreate.ExportReturnOrderCreateLine> lineList = new ArrayList<>();
                lineList.add(line);
                lineMap.put(key,lineList);
            }else{
                List<ExportReturnOrderCreate.ExportReturnOrderCreateLine> lineList2 = lineMap.get(key);
                lineList2.add(line);
                lineMap.put(key,lineList2);
            }
        }
        for (ExportReturnOrderCreate orderCreate : exportList) {
            if (lineMap.get(orderCreate.getReturnOrderCode()) != null){
                orderCreate.setOrderCreateLines(lineMap.get(orderCreate.getReturnOrderCode()));
            }
        }
        return exportList;
    }

    @NotNull
    private static ExportReturnOrderCreate.ExportReturnOrderCreateLine getExportReturnOrderCreateLine(ExportReturnOrderCreate exportReturnOrderCreate) {
        ExportReturnOrderCreate.ExportReturnOrderCreateLine line = new ExportReturnOrderCreate.ExportReturnOrderCreateLine();
        line.setItemCode(exportReturnOrderCreate.getItemCode());
        line.setBarCode(exportReturnOrderCreate.getItemBarCode());
        line.setItemName(exportReturnOrderCreate.getItemName());
        line.setPlanQty(exportReturnOrderCreate.getPlanQty());
        line.setRealQty(exportReturnOrderCreate.getRealQty());
        line.setInventoryType(exportReturnOrderCreate.getInventoryType());
        return line;
    }

    @Override
    public Long getExportCount(WmsReturnOrderCreateBo bo) {
        return baseMapper.exportCount(buildExportQueryWrapper(bo));
    }
    private QueryWrapper<WmsReturnOrderCreateBo> buildExportQueryWrapper(WmsReturnOrderCreateBo bo) {
        QueryWrapper<WmsReturnOrderCreateBo> lqw = Wrappers.query();
        lqw.eq(bo.getWarehouseId() != null, "wroc.warehouse_id", bo.getWarehouseId());
        lqw.eq(bo.getExpressCode() != null, "wroc.express_code", bo.getExpressCode());
        lqw.eq(bo.getPreDeliveryOrderCode() != null,"wso.delivery_order_code", bo.getPreDeliveryOrderCode());
        lqw.eq(bo.getHandoverCode() != null,"wroc.handover_code", bo.getHandoverCode());
        lqw.groupBy("wrocl.return_order_id,wrcpr.inventory_type,wrocl.item_code");
        return lqw;
    }