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

ops/2024/10/21 14:27:41/

一、准备环境包

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 {@Beanpublic 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);}@PostConstructprivate 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();}@Overridepublic 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);}@PostConstructprivate 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();}@Overridepublic 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 {@Overridepublic 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*/@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** Called after the sheet is created*/@Overridepublic 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 + kString sheetName = "hidden" + k;Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet hiddenSheet = workbook.createSheet(sheetName);for (int i = 0, length = v.getSource().length; i < length; i++) {// 开始的行数i，列数khiddenSheet.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;}@Overridepublic void onException(Exception e, AnalysisContext analysisContext) {}@Overridepublic void invokeHead(Map<Integer, CellData> map, AnalysisContext analysisContext) {}/*** 这个每一条数据解析都会来调用** @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}* @param context*/@Overridepublic void invoke(PurchaseOrderBaseInfoResp data, AnalysisContext context) {Integer rowIndex = context.readRowHolder().getRowIndex();if (rowIndex < 6) {log.info("解析到一条数据:{}", JSON.toJSONString(data));cachedDataList.add(data);}}@Overridepublic void extra(CellExtra cellExtra, AnalysisContext analysisContext) {}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info("所有数据解析完成！");}@Overridepublic 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;}@Overridepublic void onException(Exception e, AnalysisContext analysisContext) throws Exception {}@Overridepublic 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*/@Overridepublic void invoke(PurchaseOrderGoodsResp data, AnalysisContext context) {log.info("解析到一条数据:{}", JSON.toJSONString(data));cachedDataList.add(data);}@Overridepublic void extra(CellExtra cellExtra, AnalysisContext analysisContext) {}/*** 所有数据解析完成了 都会来调用** @param context*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库log.info("所有数据解析完成！");}@Overridepublic 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;}

http://www.ppmy.cn/ops/36465.html

相关文章

Portforge：一款功能强大的轻量级端口混淆工具

Portforge：一款功能强大的轻量级端口混淆工具

关于Portforge Portforge是一款功能强大的轻量级端口混淆工具&#xff0c;该工具使用Crystal语言开发&#xff0c;可以帮助广大研究人员防止网络映射&#xff0c;这样一来&#xff0c;他人就无法查看到你设备正在运行&#xff08;或没有运行&#xff09;的服务和程序了。简而言…
阅读更多...
Linux第二节--常见的指令介绍集合（持续更新中）

Linux第二节--常见的指令介绍集合（持续更新中）

点赞关注不迷路&#xff01;&#xff0c;本节涉及初识Linux第二节&#xff0c;主要为常见的几条指令介绍。 Linux下基本指令 1. ls 指令 语法&#xff1a; ls [选项][目录或文件] 功能&#xff1a;对于目录&#xff0c;该命令列出该目录下的所有子目录与文件。对于文件&#…
阅读更多...
IPO压力应变桥信号处理系列隔离放大器 差分信号隔离转换0-10mV/0-20mV/0-±10mV/0-±20mV转4-20mA/0-5V/0-10V

IPO压力应变桥信号处理系列隔离放大器 差分信号隔离转换0-10mV/0-20mV/0-±10mV/0-±20mV转4-20mA/0-5V/0-10V

概述&#xff1a; IPO压力应变桥信号处理系列隔离放大器是一种将差分输入信号隔离放大、转换成按比例输出的直流信号混合集成厚模电路。产品广泛应用在电力、远程监控、仪器仪表、医疗设备、工业自控等行业。该模块内部嵌入了一个高效微功率的电源&#xff0c;向输入端和输出端…
阅读更多...
Vue学习笔记

Vue学习笔记

Vue可以提升开发体验&#xff0c;因为它是组件化开发&#xff0c;复用性比较高 Vue 可以列表渲染&#xff0c;可以一次性渲染出来 Vue>react>Angular Vue是渐进式框架&#xff08;逐渐增强的&#xff09;&#xff0c;简单复杂的页面都可以胜任 Vue是一个框架&#xf…
阅读更多...
Flutter开发Dart中的队列（Queue）

Flutter开发Dart中的队列（Queue）

文章目录 Dart中的队列&#xff08;Queue&#xff09;基本操作示例队列的类型队列的应用总结 Dart中的队列&#xff08;Queue&#xff09; 队列是一种抽象的数据结构&#xff0c;遵循“先进先出”&#xff08;FIFO&#xff09;的原则。这意味着最早添加的元素将首先被移除。队…
阅读更多...
FME学习之旅---day26

FME学习之旅---day26

我们付出一些成本&#xff0c;时间的或者其他&#xff0c;最终总能收获一些什么。 【由于上周&#xff0c;上班状态不是很好&#xff0c;事情多又杂&#xff0c;没有学习的劲头&#xff0c;就短暂的休息了一下下。双休爬山&#xff0c;给自己上了强度&#xff0c;今天才缓过来…
阅读更多...
基于springboot+vue+Mysql的影城管理系统

基于springboot+vue+Mysql的影城管理系统

开发语言&#xff1a;Java框架&#xff1a;springbootJDK版本&#xff1a;JDK1.8服务器&#xff1a;tomcat7数据库&#xff1a;mysql 5.7&#xff08;一定要5.7版本&#xff09;数据库工具&#xff1a;Navicat11开发软件&#xff1a;eclipse/myeclipse/ideaMaven包&#xff1a;…
阅读更多...
C++ | Leetcode C++题解之第71题简化路径

C++ | Leetcode C++题解之第71题简化路径

题目&#xff1a; 题解&#xff1a; class Solution { public:string simplifyPath(string path) {auto split [](const string& s, char delim) -> vector<string> {vector<string> ans;string cur;for (char ch: s) {if (ch delim) {ans.push_back(mov…
阅读更多...
最新文章

Copyright @ 2022~2023