EasyExcel多行表头带动态下拉框导入导出具体实现

最新推荐文章于 2024-05-22 12:20:36 发布

巨人IIT

最新推荐文章于 2024-05-22 12:20:36 发布

阅读量539

点赞数 6

文章标签： spring boot excel

本文链接：https://blog.csdn.net/qq_31217363/article/details/138541392

版权

一、准备环境包



maven:

<!-- guava本地缓存-->
<dependency>
	<groupId>com.google.guava</groupId>
	<artifactId>guava</artifactId>
	<version>32.1.2-jre</version>
</dependency>


<!--easyexcel依赖-->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>2.2.10</version>
</dependency>

二、多行表头的excel导出带有设置下拉框


设计代码：

import java.lang.annotation.*;

/**
 * 标注导出的列为下拉框类型，并为下拉框设置内容
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;
}

public interface ExcelDynamicSelect {
    /**
     * 获取动态生成的下拉框可选数据
     * @return 动态生成的下拉框可选数据
     */
    String[] getSource();
}

import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.concurrent.TimeUnit;

@Configuration
public class CacheConfig {

    @Bean
    public Cache<String, Object> myCache() {
        return CacheBuilder.newBuilder()
                .expireAfterWrite(10, TimeUnit.MINUTES)
                .maximumSize(100)
                .build();
    }
}


import cn.hutool.extra.spring.SpringUtil;
import com.google.common.cache.Cache;
import com.youqian.common.rest.RestResponse;
import com.youqian.pms.api.feign.warehouse.client.WarehouseClient;
import com.youqian.pms.api.feign.warehouse.dto.DropdownQuery;
import com.youqian.pms.api.feign.warehouse.dto.SupplierBasicInfo;
import com.youqian.pms.boot.utils.easyexcelutil.ExcelDynamicSelect;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.List;

/**
 * 获取所有供应商名称列表
 */
@Slf4j
@Component
public class SupplierNameServiceImpl implements ExcelDynamicSelect {


    private static final WarehouseClient warehouseClient;
    private static final Cache<String, Object> myCache;

    static {
        warehouseClient = SpringUtil.getBean(WarehouseClient.class);
        myCache = SpringUtil.getBean(Cache.class);
    }

    @PostConstruct
    private void init() {
        myCache.put("all-supplier-name", this.getDataList());
    }

    private String getDataList() {
        DropdownQuery query = new DropdownQuery();
        RestResponse<List<SupplierBasicInfo>> listRestResponse = warehouseClient.queryAllSuppliers(query);
        StringBuilder stringBuilder = new StringBuilder();
        if (listRestResponse.isSuccess()) {
            List<SupplierBasicInfo> data = listRestResponse.getData();
            for (SupplierBasicInfo datum : data) {
                String supplierName = datum.getSupplierName();
                if (StringUtils.isNotBlank(supplierName)) {
                    stringBuilder.append(supplierName).append(",");
                }
            }
        }
        log.info("guava cache queryAllSuppliers");
        return stringBuilder.toString();
    }

    @Override
    public String[] getSource() {
        String value = "";
        try {
            value = String.valueOf(myCache.get("all-supplier-name", this::getDataList));
        } catch (Exception e) {
            log.warn("guava cache queryAllSuppliers warn :", e);
        }
        return value.split(",");
    }

}


import cn.hutool.extra.spring.SpringUtil;
import com.google.common.cache.Cache;
import com.youqian.common.rest.RestResponse;
import com.youqian.pms.api.feign.warehouse.client.WarehouseClient;
import com.youqian.pms.api.feign.warehouse.dto.WarehouseManagementListReqDto;
import com.youqian.pms.api.feign.warehouse.dto.WarehouseManagementListRespDto;
import com.youqian.pms.boot.utils.easyexcelutil.ExcelDynamicSelect;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.List;

@Slf4j
@Component
public class WarehouseNameServiceImpl implements ExcelDynamicSelect {


    private static final WarehouseClient warehouseClient;
    private static final Cache<String, Object> myCache;

    static {
        warehouseClient = SpringUtil.getBean(WarehouseClient.class);
        myCache = SpringUtil.getBean(Cache.class);
    }

    @PostConstruct
    private void init() {
        myCache.put("all-warehouse-name", this.getDataList());
    }

    private String getDataList() {
        WarehouseManagementListReqDto query = new WarehouseManagementListReqDto();
        RestResponse<List<WarehouseManagementListRespDto>> listRestResponse = warehouseClient.findList(query);
        StringBuilder stringBuilder = new StringBuilder();
        if (listRestResponse.isSuccess()) {
            List<WarehouseManagementListRespDto> data = listRestResponse.getData();
            for (WarehouseManagementListRespDto datum : data) {
                String warehouseName = datum.getWarehouseName();
                if (StringUtils.isNotBlank(warehouseName)) {
                    stringBuilder.append(warehouseName).append(",");
                }
            }
        }
        log.info("guava cache warehouseName");
        return stringBuilder.toString();
    }

    @Override
    public String[] getSource() {
        String value = "";
        try {
            value = String.valueOf(myCache.get("all-warehouse-name", this::getDataList));
        } catch (Exception e) {
            log.warn("guava cache warehouseName warn :", e);
        }
        return value.split(",");
    }

}

import com.youqian.pms.boot.utils.easyexcelutil.ExcelDynamicSelect;
import com.youqian.pms.common.enums.buyorder.BuyTypeEnum;
import lombok.extern.slf4j.Slf4j;

/**
 * 获取采购类型列表
 */
@Slf4j
public class BuyTypeStrServiceImpl implements ExcelDynamicSelect {


    @Override
    public String[] getSource() {
        StringBuilder value = new StringBuilder();
        for (BuyTypeEnum typeEnum : BuyTypeEnum.values()) {
            value.append(typeEnum.getDesc()).append(",");
        }
        return value.toString().split(",");
    }

}

/**
 * 采购订单模板导出
 */
@ApiOperation("采购订单导入模板导出")
@PostMapping("/buyOrder/exportPurchasingOrderTemplate")
public void exportPurchasingOrderTemplate(HttpServletResponse response) {
	log.info("exportPurchasingOrderTemplate start");
	this.buyOrderOperateService.exportPurchasingOrderTemplate(response);
}

导出模板对象：多个表头对应多个对象

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.youqian.pms.boot.utils.easyexcelutil.ExcelSelected;
import lombok.Data;

import java.io.Serializable;

/**
 * 采购订单基本信息
 */
@Data
@HeadRowHeight(20)
@ContentRowHeight(18)
public class PurchaseOrderBaseInfoResp implements Serializable {

    private static final long serialVersionUID = -2121379439910317195L;

    /**
     * 供应商名称
     */
    @ExcelSelected(sourceClass = SupplierNameServiceImpl.class)
    @ExcelProperty(index = 0,value = "供应商名称")
    @ColumnWidth(30)
    private String supplierName;
    /**
     * 仓库名称
     */
    @ExcelSelected(sourceClass = WarehouseNameServiceImpl.class)
    @ExcelProperty(index = 1,value = "仓库名称")
    @ColumnWidth(30)
    private String warehouseName;
    /**
     * 采购类型
     */
    @ExcelSelected(sourceClass = BuyTypeStrServiceImpl.class)
    @ExcelProperty(index = 2,value = "采购类型")
    @ColumnWidth(30)
    private String buyTypeStr;
    /**
     * 货主
     */
    @ExcelSelected(sourceClass = BuyerNameServiceImpl.class)
    @ExcelProperty(index = 3,value = "货主")
    @ColumnWidth(30)
    private String buyerName;
}


import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

/**
 * 采购订单商品信息
 */
@Data
@HeadRowHeight(20)
@ContentRowHeight(18)
public class PurchaseOrderGoodsResp {

    /**
     * sku编码
     */
    @ExcelProperty(index = 0,value = "SKU 编码")
    @ColumnWidth(30)
    private String skuCode;
    /**
     * 数量
     */
    @ExcelProperty(index = 1,value = "数量")
    @ColumnWidth(30)
    private String count;
    /**
     * 单价
     */
    @ExcelProperty(index = 2,value = "单价")
    @ColumnWidth(30)
    private String price;
}


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


@Slf4j
public class EasyExcelUtil {

    /**
     * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
     * @param head 导出的表头信息和配置
     * @param sheetNo sheet索引
     * @param sheetName sheet名称
     * @param <T> 泛型
     * @return sheet页
     */
    public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);

        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                .build();
    }

    /**
     * 解析表头类中的下拉注解
     * @param head 表头类
     * @param <T> 泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();

        // getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = head.getDeclaredFields();
        for (int i = 0; i < fields.length; i++){
            Field field = fields[i];
            // 解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            ExcelProperty property = field.getAnnotation(ExcelProperty.class);
            if (selected != null) {
                ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                String[] source = excelSelectedResolve.resolveSelectedSource(selected);
                if (source != null && source.length > 0){
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property != null && property.index() >= 0){
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }
        return selectedMap;
    }

    public static boolean isIDNumber(String IDNumber) {
        if (IDNumber == null || "".equals(IDNumber)) {
            return false;
        }
        // 定义判别用户身份证号的正则表达式（15位或者18位，最后一位可以为字母）
        String regularExpression = "(^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$)|" +
                "(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";
        //假设18位身份证号码:41000119910101123X  410001 19910101 123X
        //^开头
        //[1-9] 第一位1-9中的一个      4
        //\\d{5} 五位数字           10001（前六位省市县地区）
        //(18|19|20)                19（现阶段可能取值范围18xx-20xx年）
        //\\d{2}                    91（年份）
        //((0[1-9])|(10|11|12))     01（月份）
        //(([0-2][1-9])|10|20|30|31)01（日期）
        //\\d{3} 三位数字            123（第十七位奇数代表男，偶数代表女）
        //[0-9Xx] 0123456789Xx其中的一个 X（第十八位为校验值）
        //$结尾

        //假设15位身份证号码:410001910101123  410001 910101 123
        //^开头
        //[1-9] 第一位1-9中的一个      4
        //\\d{5} 五位数字           10001（前六位省市县地区）
        //\\d{2}                    91（年份）
        //((0[1-9])|(10|11|12))     01（月份）
        //(([0-2][1-9])|10|20|30|31)01（日期）
        //\\d{3} 三位数字            123（第十五位奇数代表男，偶数代表女），15位身份证不含X
        //$结尾
        boolean matches = IDNumber.matches(regularExpression);

        //判断第18位校验值
        if (matches) {

            if (IDNumber.length() == 18) {
                try {
                    char[] charArray = IDNumber.toCharArray();
                    //前十七位加权因子
                    int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
                    //这是除以11后，可能产生的11位余数对应的验证码
                    String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
                    int sum = 0;
                    for (int i = 0; i < idCardWi.length; i++) {
                        int current = Integer.parseInt(String.valueOf(charArray[i]));
                        int count = current * idCardWi[i];
                        sum += count;
                    }
                    char idCardLast = charArray[17];
                    int idCardMod = sum % 11;
                    if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {
                        return true;
                    } else {
                        return false;
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                    return false;
                }
            }
            return false;
        }
        return matches;
    }


    public static  boolean isMobile(String phone){
        Pattern p = null;
        Matcher m = null;
        boolean b = false;
        // 验证手机号
        String s2="^[1](([3|5|6|7|8|9][\\d])|([4][4,5,6,7,8,9])|([6][2,5,6,7])|([7][^9])|([9][1,8,9]))[\\d]{8}$";
        if(StringUtils.isNotBlank(phone)){
            p = Pattern.compile(s2);
            m = p.matcher(phone);
            b = m.matches();
        }
        return b;
    }

}


import lombok.Data;
import lombok.extern.slf4j.Slf4j;

@Data
@Slf4j
public class ExcelSelectedResolve {
    /**
     * 下拉内容
     */
    private String[] source;

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;

    public String[] resolveSelectedSource(ExcelSelected excelSelected) {
        if (excelSelected == null) {
            return null;
        }

        // 获取固定下拉框的内容
        String[] source = excelSelected.source();
        if (source.length > 0) {
            return source;
        }

        // 获取动态下拉框的内容
        Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
        if (classes.length > 0) {
            try {
                ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
                String[] dynamicSelectSource = excelDynamicSelect.getSource();
                if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
                    return dynamicSelectSource;
                }
            } catch (InstantiationException | IllegalAccessException e) {
                log.error("解析动态下拉框数据异常", e);
            }
        }
        return null;
    }

}


import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Map;

@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    /**
     * 设置阈值，避免生成的导入模板下拉值获取不到，可自行设置数量大小
     */
    private static final Integer LIMIT_NUMBER = 25;

    /**
     * Called before create the sheet
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * Called after the sheet is created
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        selectedMap.forEach((k, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
//            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), 5, k, k);
            // 如果下拉值总数大于25，则使用一个新sheet存储，避免生成的导入模板下拉值获取不到
            if (v.getSource().length > LIMIT_NUMBER) {
                //定义sheet的名称
                //1.创建一个隐藏的sheet 名称为 hidden + k
                String sheetName = "hidden" + k;
                Workbook workbook = writeWorkbookHolder.getWorkbook();
                Sheet hiddenSheet = workbook.createSheet(sheetName);
                for (int i = 0, length = v.getSource().length; i < length; i++) {
                    // 开始的行数i，列数k
                    hiddenSheet.createRow(i).createCell(k).setCellValue(v.getSource()[i]);
                }
                Name category1Name = workbook.createName();
                category1Name.setNameName(sheetName);
                String excelLine = getExcelLine(k);
                // =hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
                String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.getSource().length + 1);
                // 将刚才设置的sheet引用到你的下拉列表中
                DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
                DataValidation dataValidation = helper.createValidation(constraint, rangeList);
                writeSheetHolder.getSheet().addValidationData(dataValidation);
                // 设置存储下拉列值得sheet为隐藏
                int hiddenIndex = workbook.getSheetIndex(sheetName);
                if (!workbook.isSheetHidden(hiddenIndex)) {
                    workbook.setSheetHidden(hiddenIndex, true);
                }
            }
            // 设置下拉列表的值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
//			validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
    }


    /**
     * 返回excel列标A-Z-AA-ZZ
     *
     * @param num 列数
     * @return java.lang.String
     */
    private String getExcelLine(int num) {
        String line = "";
        int first = num / 26;
        int second = num % 26;
        if (first > 0) {
            line = (char) ('A' + first - 1) + "";
        }
        line += (char) ('A' + second) + "";
        return line;
    }
}

@Override
public void exportPurchasingOrderTemplate(HttpServletResponse response) {
	String filename = "采购订单导入模板";
	try {
		// 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
		filename = URLEncoder.encode(filename, "UTF-8");
		response.setContentType("application/octet-stream;charset=ISO8859-1");
		response.setHeader("Content-Disposition", "attachment;filename=" + filename);
		response.addHeader("Pragma", "no-cache");
		response.addHeader("Cache-Control", "no-cache");


		// 设置隔行表头
		List<List<String>> headList = Lists.newArrayList();
		headList.add(Lists.newArrayList());
		headList.add(Lists.newArrayList());
		headList.add(Lists.newArrayList());
		headList.add(Lists.newArrayList());
		headList.add(Lists.newArrayList());
		ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
		WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(PurchaseOrderBaseInfoResp.class, 0, filename);
		excelWriter.write(headList, writeSheet);

		WriteTable writeTable2 = EasyExcel.writerTable(2).needHead(Boolean.TRUE).head(PurchaseOrderGoodsResp.class).build();
		excelWriter.write(new ArrayList<String>(), writeSheet, writeTable2);
		excelWriter.finish();
	} catch (Exception e) {
		log.error("导出【采购订单导入模板】 error ：",e);
	}
}

三、多行表头的excel导入

 /**
 * 采购商品批量导入
 * @return 采购商品列表
 */
@ApiOperation("采购订单导入")
@PostMapping("/buyOrder/importPurchasingOrder")
public ResponseResult<AddOrModifyBuyOrderDto> importPurchasingOrder(MultipartFile file) {
	log.info("importPurchasingOrder start");
	try {
		return ResponseResult.buildSuccessResponse(this.buyOrderOperateService.importPurchasingOrder(file));
	} catch (Exception e) {
		log.info("采购订单导入失败", e);
		return ResponseResult.build(-1, e.getMessage(),null);
	}
}

@Override
public AddOrModifyBuyOrderDto importPurchasingOrder(MultipartFile file) {
	try {
		// 读取excel内容
		List<PurchaseOrderBaseInfoResp> baseInfoRespList = new ArrayList<>();
		List<PurchaseOrderGoodsResp> goodsRespList = new ArrayList<>();
		PurchaseOrderBaseInfoRespListener purchaseOrderBaseInfoRespListener = new PurchaseOrderBaseInfoRespListener(baseInfoRespList);
		PurchaseOrderGoodsRespListener purchaseOrderGoodsRespListener = new PurchaseOrderGoodsRespListener(goodsRespList);
		EasyExcel.read(file.getInputStream(), PurchaseOrderBaseInfoResp.class, purchaseOrderBaseInfoRespListener).head(PurchaseOrderBaseInfoResp.class).sheet().doRead();
		EasyExcel.read(file.getInputStream(), PurchaseOrderGoodsResp.class, purchaseOrderGoodsRespListener).headRowNumber(7).sheet().doRead();
		// 组装数据
		return this.assemblyObject(baseInfoRespList, goodsRespList);
	} catch (Exception e){
		log.error("导出【采购订单导入模板】 error ：",e);
		throw new Exception(e.getMessage());
	}
}

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.youqian.pms.boot.controller.internal.buyorder.dto.PurchaseOrderBaseInfoResp;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

@Getter
@Slf4j
public class PurchaseOrderBaseInfoRespListener implements ReadListener<PurchaseOrderBaseInfoResp> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private final List<PurchaseOrderBaseInfoResp> cachedDataList;

    public PurchaseOrderBaseInfoRespListener(List<PurchaseOrderBaseInfoResp> cachedDataList) {
        this.cachedDataList = cachedDataList;
    }

    @Override
    public void onException(Exception e, AnalysisContext analysisContext) {
    }

    @Override
    public void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(PurchaseOrderBaseInfoResp data, AnalysisContext context) {
        Integer rowIndex = context.readRowHolder().getRowIndex();
        if (rowIndex < 6) {
            log.info("解析到一条数据:{}", JSON.toJSONString(data));
            cachedDataList.add(data);
        }
    }

    @Override
    public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        Integer rowIndex = analysisContext.readRowHolder().getRowIndex();
        return rowIndex < 6;
    }


}



import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.youqian.pms.boot.controller.internal.buyorder.dto.PurchaseOrderGoodsResp;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

@Getter
@Slf4j
public class PurchaseOrderGoodsRespListener implements ReadListener<PurchaseOrderGoodsResp> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private final List<PurchaseOrderGoodsResp> cachedDataList;


    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     */
    public PurchaseOrderGoodsRespListener(List<PurchaseOrderGoodsResp> cachedDataList) {
        this.cachedDataList = cachedDataList;
    }

    @Override
    public void onException(Exception e, AnalysisContext analysisContext) throws Exception {

    }

    @Override
    public void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {
        System.out.println();
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(PurchaseOrderGoodsResp data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
    }

    @Override
    public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {

    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        log.info("所有数据解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext analysisContext) {
        return true;
    }


}

private AddOrModifyBuyOrderDto assemblyObject(List<PurchaseOrderBaseInfoResp> baseInfoRespList, List<PurchaseOrderGoodsResp> goodsRespList) {
        // 校验数据
        if (CollectionUtils.isEmpty(baseInfoRespList) || CollectionUtils.isEmpty(goodsRespList)) {
            throw new Exception("导入的数据为空，请检查后重新操作");
        }
        PurchaseOrderBaseInfoResp baseInfoResp = baseInfoRespList.get(0);
        String buyerName = baseInfoResp.getBuyerName();
        if (StringUtils.isEmpty(buyerName)) {
            throw new Exception("[货主]不可为空");
        }
        String buyTypeStr = baseInfoResp.getBuyTypeStr();
        if (StringUtils.isEmpty(buyTypeStr)) {
            throw new Exception("[采购类型]不可为空");
        }
        String supplierName = baseInfoResp.getSupplierName();
        if (StringUtils.isEmpty(supplierName)) {
            throw new Exception("[供应商]不可为空");
        }
        String warehouseName = baseInfoResp.getWarehouseName();
        if (StringUtils.isEmpty(warehouseName)) {
            throw new Exception("[仓库]不可为空");
        }
        List<String> skuCodeList = new ArrayList<>();
        for (PurchaseOrderGoodsResp orderGoodsResp : goodsRespList) {
            String count = orderGoodsResp.getCount();
            if (StringUtils.isEmpty(count)) {
                throw new Exception("[SKU 数量]不可为空");
            }
            String price = orderGoodsResp.getPrice();
            if (StringUtils.isEmpty(price)) {
                throw new Exception("[SKU 单价]不可为空");
            }
            String skuCode = orderGoodsResp.getSkuCode();
            if (StringUtils.isEmpty(skuCode)) {
                throw new Exception("[SKU 编码]不可为空");
            }
            skuCodeList.add(skuCode);
        }
        Map<String, PurchaseOrderGoodsResp> skuCode2IdentityMap = goodsRespList.stream().collect(
                Collectors.toMap(PurchaseOrderGoodsResp::getSkuCode, Function.identity(), (oldOne, newOne) -> newOne)
        );
        AddOrModifyBuyOrderDto addDto = new AddOrModifyBuyOrderDto();
        CompletableFuture<Void> f1 = CompletableFuture.runAsync(()->{
            RestResponse<BuySupplierMainResp> buySupplierMainRespRestResponse = supplierBuyerUserService.querySupplierMainListByBuyerName(buyerName);
            BuySupplierMainResp data = buySupplierMainRespRestResponse.getData();
            if (buySupplierMainRespRestResponse.isSuccess() && null != data) {
                Long buyerId = data.getId();
                addDto.setBuyerId(buyerId);
            } else {
                throw new Exception("[货主]： "+buyerName+" 不存在，请检查后重新填写");
            }
            addDto.setBuyerName(buyerName);
            int buyType = BuyTypeEnum.getCodeByDesc(buyTypeStr);
            if (buyType == -1) {
                throw new Exception("[采购类型]： "+buyTypeStr+" 不存在，请检查后重新填写");
            }
            addDto.setBuyType(buyType);
        });
        CompletableFuture<Void> f2 = CompletableFuture.runAsync(()->{
            DropdownQuery query = new DropdownQuery();
            query.setText(supplierName);
            RestResponse<List<SupplierBasicInfo>> listRestResponse = warehouseClient.queryAllSuppliers(query);
            log.info("AssemblyObject queryAllSuppliers req = {}, result = {}", JsonUtils.toJsonString(query), JsonUtils.toJsonString(listRestResponse));
            if (listRestResponse.isSuccess()) {
                List<SupplierBasicInfo> supplierBasicInfos = listRestResponse.getData();
                if (CollectionUtils.isNotEmpty(supplierBasicInfos)) {
                    Integer supplierId = supplierBasicInfos.get(0).getId();
                    if (null != supplierId) {
                        addDto.setSupplierId(Long.valueOf(supplierId));
                    } else {
                        throw new Exception("[供应商]： "+supplierName+" 不存在，请检查后重新填写");
                    }
                } else {
                    throw new Exception("[供应商]： "+supplierName+" 不存在，请检查后重新填写");
                }
            }
        });
        addDto.setSupplierName(supplierName);
        CompletableFuture<Void> f3 = CompletableFuture.runAsync(()->{
            WarehouseManagementListReqDto warehouseManagementListReqDto = new WarehouseManagementListReqDto();
            warehouseManagementListReqDto.setWarehouseName(warehouseName);
            RestResponse<List<WarehouseManagementListRespDto>> warehouseManagementListResp = warehouseClient.findList(warehouseManagementListReqDto);
            log.info("AssemblyObject findList req = {}, result = {}", JsonUtils.toJsonString(warehouseManagementListReqDto), JsonUtils.toJsonString(warehouseManagementListResp));
            if (warehouseManagementListResp.isSuccess()) {
                List<WarehouseManagementListRespDto> warehouseManagementListRespDtos = warehouseManagementListResp.getData();
                if (CollectionUtils.isNotEmpty(warehouseManagementListRespDtos)) {
                    Long warehouseId = warehouseManagementListRespDtos.get(0).getId();
                    if (null != warehouseId) {
                        addDto.setWarehouseId(warehouseId);
                    } else {
                        throw new Exception("[仓库]： "+warehouseName+" 不存在，请检查后重新填写");
                    }
                } else {
                    throw new Exception("[仓库]： "+warehouseName+" 不存在，请检查后重新填写");
                }
            }
        });
        CompletableFuture<Void> f4 = CompletableFuture.runAsync(() -> {
            List<BuyOrderItemDto> itemList = new ArrayList<>();
            BigDecimal postAmount = new BigDecimal("0");
            MaterielSpuInfoQueryReqDto materielSpuInfoQueryReqDto = new MaterielSpuInfoQueryReqDto();
            materielSpuInfoQueryReqDto.setSkuCodeList(skuCodeList);
            RestResponse<PageInfo<MaterielSpuInfoRespDto>> pageInfoRestResponse = warehouseClient.warehouseoutboundorderitemInfo(materielSpuInfoQueryReqDto);
            log.info("AssemblyObject warehouseoutboundorderitemInfo req = {}, result = {}", skuCodeList, JsonUtils.toJsonString(pageInfoRestResponse));
            if (pageInfoRestResponse.isSuccess()) {
                PageInfo<MaterielSpuInfoRespDto> materielSpuInfoResp = pageInfoRestResponse.getData();
                if (null != materielSpuInfoResp) {
                    List<MaterielSpuInfoRespDto> list = materielSpuInfoResp.getList();
                    if (CollectionUtils.isEmpty(list))  {
                        throw new Exception("[SKU]： "+skuCodeList+" 不存在，请检查后重新填写");
                    }
                    for (MaterielSpuInfoRespDto dto : list) {
                        String spuCode = dto.getSpuCode();
                        String spuName = dto.getSpuName();
                        List<MaterielSkuBySpuRespDto> skuListInfo = dto.getSkuListInfo();
                        for (MaterielSkuBySpuRespDto materielSkuBySpuRespDto : skuListInfo) {
                            String skuCode = materielSkuBySpuRespDto.getSkuCode();
                            PurchaseOrderGoodsResp purchaseOrderGoodsResp = skuCode2IdentityMap.get(skuCode);
                            String count = purchaseOrderGoodsResp.getCount();
                            String price = purchaseOrderGoodsResp.getPrice();
                            String skuName = materielSkuBySpuRespDto.getSkuName();
                            Long materielModelId = materielSkuBySpuRespDto.getMaterielModelId();
                            Long materielBrandId = materielSkuBySpuRespDto.getMaterielBrandId();
                            Long materielClassId = materielSkuBySpuRespDto.getMaterielClassId();
                            String materielBrandName = materielSkuBySpuRespDto.getMaterielBrandName();
                            String materielClassName = materielSkuBySpuRespDto.getMaterielClassName();
                            String materielModelName = materielSkuBySpuRespDto.getMaterielModelName();
                            String thumbnailUrl = materielSkuBySpuRespDto.getThumbnailUrl();
                            Long materielId = materielSkuBySpuRespDto.getId();
                            String specValues = materielSkuBySpuRespDto.getSpecValues();
                            Integer enableUniqueCodeFlag = materielSkuBySpuRespDto.getEnableUniqueCodeFlag();
                            String materielNewConfigName = materielSkuBySpuRespDto.getMaterielNewConfigName();
                            String materielBrandCode = materielSkuBySpuRespDto.getMaterielBrandCode();
                            String materielClassCode = materielSkuBySpuRespDto.getMaterielClassCode();

                            BuyOrderItemDto buyOrderItemDto = new BuyOrderItemDto();
                            buyOrderItemDto.setSkuCode(skuCode);
                            buyOrderItemDto.setSkuName(skuName);
                            buyOrderItemDto.setMaterielModelId(materielModelId);
                            buyOrderItemDto.setMaterielBrandId(materielBrandId);
                            buyOrderItemDto.setMaterielClassId(materielClassId);
                            buyOrderItemDto.setMaterielModelName(materielModelName);
                            buyOrderItemDto.setMaterielBrandName(materielBrandName);
                            buyOrderItemDto.setMaterielClassName(materielClassName);
                            buyOrderItemDto.setSkuImg(thumbnailUrl);
                            buyOrderItemDto.setMaterielId(materielId);
                            buyOrderItemDto.setMaterielName(skuName);
                            buyOrderItemDto.setSpec(specValues);
                            buyOrderItemDto.setEnableUniqueCodeFlag(enableUniqueCodeFlag);
                            buyOrderItemDto.setMaterielNewConfigName(materielNewConfigName);
                            buyOrderItemDto.setSpuCode(spuCode);
                            buyOrderItemDto.setSpuName(spuName);
                            buyOrderItemDto.setCount(Integer.valueOf(count));
                            buyOrderItemDto.setSkuAmount(new BigDecimal(price));
                            buyOrderItemDto.setRebateAmount(new BigDecimal("0"));
                            itemList.add(buyOrderItemDto);
                            postAmount = new BigDecimal(price).multiply(new BigDecimal(count)).add(postAmount);
                        }
                    }
                }
                addDto.setItemList(itemList);
                addDto.setRebate(0);
                BuyOrderPaymentDto buyOrderPayment = new BuyOrderPaymentDto();
                buyOrderPayment.setAdvanceAmount(new BigDecimal("0"));
                buyOrderPayment.setBalancePaymentDay(1);
                buyOrderPayment.setPostAmount(postAmount);
                buyOrderPayment.setSettlementCycle(1);
                buyOrderPayment.setTotalAmount(postAmount);
                addDto.setBuyOrderPayment(buyOrderPayment);
            }
        });
        try {
            CompletableFuture<Void> future = CompletableFuture.allOf(f1, f2, f3, f4);
            future.get();
        } catch (Exception e){
            throw new Exception(e.getMessage().split("=",4)[3].replace(")",""));
        }
        return addDto;
    }