#优化 Excel表格，保存优化

skyouc

2025-03-13 e86cd6325395c784d1c116a035987a5cf19bbe69

#优化
Excel表格，保存优化

 rsf-common/pom.xml

@@ -44,6 +44,16 @@
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>
        <dependency>
            <groupId>javax.validation</groupId>
            <artifactId>validation-api</artifactId>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>30.1.1-jre</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

    <build>

 rsf-common/src/main/java/com/vincent/rsf/common/domain/BeanValidators.java

New file
@@ -0,0 +1,138 @@
package com.vincent.rsf.common.domain;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;

import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import javax.validation.Validator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * ${DESCRIPTION}
 *
 * @author Super-Admin
 * @CreateUser Ryan
 * @CreateTime 2018/6/14 16:12
 * @ModifyUser YunTao.Zhou
 * @ModifiedTime 2018/6/14 16:12
 * @Version 1.0
 */
public class BeanValidators {

    /**
     *
     * @param validator
     * @param list
     * @param groups
     * @throws ConstraintViolationException
     */
    public static void validateListWithException(Validator validator, List<?> list, Class<?>... groups)
            throws ConstraintViolationException {
        if(list.isEmpty()){
            return;
        }
        Set constraintViolations = Sets.newHashSet();
        for (Object o : list) {
            constraintViolations.addAll( validator.validate(o, groups));
            if (!constraintViolations.isEmpty()) {
                throw new ConstraintViolationException(constraintViolations);
            }
        }
    }

    public static void validateArrayWithException(Validator validator, Object[] array, Class<?>... groups){
        BeanValidators.validateListWithException(validator, Lists.newArrayList(array),groups);
    }


    /**
     * 调用JSR303的validate方法, 验证失败时抛出ConstraintViolationException.
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public static void validateWithException(Validator validator, Object object, Class<?>... groups)
            throws ConstraintViolationException {
        Set constraintViolations = validator.validate(object, groups);
        if (!constraintViolations.isEmpty()) {
            throw new ConstraintViolationException(constraintViolations);
        }
    }

    /**
     * 辅助方法, 转换ConstraintViolationException中的Set<ConstraintViolations>中为List<message>.
     */
    public static List<String> extractMessage(ConstraintViolationException e) {
        return extractMessage(e.getConstraintViolations());
    }

    /**
     * 辅助方法, 转换Set<ConstraintViolation>为List<message>
     */
    @SuppressWarnings("rawtypes")
    public static List<String> extractMessage(Set<? extends ConstraintViolation> constraintViolations) {
        List<String> errorMessages = Lists.newArrayList();
        for (ConstraintViolation violation : constraintViolations) {
            errorMessages.add(violation.getMessage());
        }
        return errorMessages;
    }

    /**
     * 辅助方法, 转换ConstraintViolationException中的Set<ConstraintViolations>为Map<property, message>.
     */
    public static Map<String, String> extractPropertyAndMessage(ConstraintViolationException e) {
        return extractPropertyAndMessage(e.getConstraintViolations());
    }

    /**
     * 辅助方法, 转换Set<ConstraintViolation>为Map<property, message>.
     */
    @SuppressWarnings("rawtypes")
    public static Map<String, String> extractPropertyAndMessage(Set<? extends ConstraintViolation> constraintViolations) {
        Map<String, String> errorMessages = Maps.newHashMap();
        for (ConstraintViolation violation : constraintViolations) {
            errorMessages.put(violation.getPropertyPath().toString(), violation.getMessage());
        }
        return errorMessages;
    }

    /**
     * 辅助方法, 转换ConstraintViolationException中的Set<ConstraintViolations>为List<propertyPath message>.
     */
    public static List<String> extractPropertyAndMessageAsList(ConstraintViolationException e) {
        return extractPropertyAndMessageAsList(e.getConstraintViolations(), " ");
    }

    /**
     * 辅助方法, 转换Set<ConstraintViolations>为List<propertyPath message>.
     */
    @SuppressWarnings("rawtypes")
    public static List<String> extractPropertyAndMessageAsList(Set<? extends ConstraintViolation> constraintViolations) {
        return extractPropertyAndMessageAsList(constraintViolations, " ");
    }

    /**
     * 辅助方法, 转换ConstraintViolationException中的Set<ConstraintViolations>为List<propertyPath +separator+ message>.
     */
    public static List<String> extractPropertyAndMessageAsList(ConstraintViolationException e, String separator) {
        return extractPropertyAndMessageAsList(e.getConstraintViolations(), separator);
    }

    /**
     * 辅助方法, 转换Set<ConstraintViolation>为List<propertyPath +separator+ message>.
     */
    @SuppressWarnings("rawtypes")
    public static List<String> extractPropertyAndMessageAsList(Set<? extends ConstraintViolation> constraintViolations,
                                                               String separator) {
        List<String> errorMessages = Lists.newArrayList();
        for (ConstraintViolation violation : constraintViolations) {
            errorMessages.add(violation.getPropertyPath() + separator + violation.getMessage());
        }
        return errorMessages;
    }

}


 rsf-server/src/main/java/com/vincent/rsf/server/common/handler/AggregationDataHandler.java

New file
@@ -0,0 +1,69 @@
package com.vincent.rsf.server.common.handler;

import cn.afterturn.easypoi.handler.impl.ExcelDataHandlerDefaultImpl;
import cn.afterturn.easypoi.handler.inter.IExcelDataHandler;
import com.google.common.collect.Lists;

import java.util.List;

/**
 * @Description: 用户导入聚合数据处理器
 * @Author: Ryan
 * @CreateDate: 2019/11/1 9:42
 * @Version: 1.0
 */
public class AggregationDataHandler extends ExcelDataHandlerDefaultImpl {

    private List<IExcelDataHandler> handlers = Lists.newArrayList();

    public AggregationDataHandler(List<IExcelDataHandler> handlers) {

        this.handlers = handlers;
    }

    public AggregationDataHandler(IExcelDataHandler... handlers) {
        this(handlers == null ? Lists.newArrayList() : Lists.newArrayList(handlers));
    }

    @Override
    public String[] getNeedHandlerFields() {
        List<String> fields = Lists.newArrayList();
        for (IExcelDataHandler handler : handlers) {
            String[] needHandlerFields = handler.getNeedHandlerFields();
            if (needHandlerFields == null) {
                continue;
            }
            for (String needHandlerField : needHandlerFields) {
                fields.add(needHandlerField);
            }
        }
        return fields.toArray(new String[fields.size()]);
    }

    @Override
    public Object importHandler(Object obj, String name, Object value) {
        for (IExcelDataHandler handler : handlers) {
            String[] needHandlerFields = handler.getNeedHandlerFields();
            List<String> fieldList = Lists.newArrayList(needHandlerFields);
            if (fieldList.contains(name)) {
                handler.importHandler(obj, name, value);
                break;
            }
        }
        return super.importHandler(obj, name, value);
    }

    @Override
    public Object exportHandler(Object obj, String name, Object value) {
        for (IExcelDataHandler handler : handlers) {
            String[] needHandlerFields = handler.getNeedHandlerFields();
            List<String> fieldList = Lists.newArrayList(needHandlerFields);
            if (fieldList.contains(name)) {
                handler.exportHandler(obj, name, value);
                break;
            }
        }
        return super.exportHandler(obj, name, value);
    }
}


 rsf-server/src/main/java/com/vincent/rsf/server/common/handler/ExcelDictHandlerImpl.java

New file
@@ -0,0 +1,49 @@
package com.vincent.rsf.server.common.handler;

import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import com.vincent.rsf.framework.common.SpringUtils;
import com.vincent.rsf.server.common.handler.global.GlobalDictService;

/**
 * @Description: excel 字典处理类
 * @Author: Ryan
 * @CreateDate: 2019/10/29 17:42
 * @Version: 1.0
 */
public class ExcelDictHandlerImpl implements IExcelDictHandler {

    private ExcelDictHandlerImpl() {
    }

    private volatile static ExcelDictHandlerImpl INSTANCE;

    public static ExcelDictHandlerImpl getInstance() {
        if (INSTANCE == null) {
            synchronized (ExcelDictHandlerImpl.class) {
                INSTANCE = new ExcelDictHandlerImpl();
            }
        }
        return INSTANCE;
    }

    @Override
    public String toName(String dict, Object obj, String name, Object value) {
        return getGlobalDictService().getDictLabel(dict, (value != null ? value.toString() : null), (value != null ? value.toString() : null));
    }

    @Override
    public String toValue(String dict, Object obj, String name, Object value) {
        return getGlobalDictService().getDictValue(dict, (value != null ? value.toString() : null), (value != null ? value.toString() : null));
    }

    private GlobalDictService getGlobalDictService() {
        try {
            return SpringUtils.getBean(GlobalDictService.class);
        } catch (Exception e) {
            return null;
        }
    }


}


 rsf-server/src/main/java/com/vincent/rsf/server/common/handler/global/GlobalDictService.java

New file
@@ -0,0 +1,136 @@
package com.vincent.rsf.server.common.handler.global;

import com.google.common.collect.Lists;
import com.vincent.rsf.framework.common.SpringUtils;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.stereotype.Service;

import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @Description: 字典工具类，提供给全局使用
 * @Author: Ryan
 * @CreateDate: 2019/8/13 11:11
 * @Version: 1.0
 */
@Slf4j
@Service
public class GlobalDictService implements InitializingBean {

    private final ConcurrentHashMap<String, String> LOCAL_LABEL_CACHE = new ConcurrentHashMap();
    private final ConcurrentHashMap<String, Set<Dict>> LOCAL_CACHE = new ConcurrentHashMap();

    public void putDict(String type, String value, String label) {
        LOCAL_LABEL_CACHE.put(type + "-" + value, label);
        putToLocalCache(type, value, label);
    }

    private void putToLocalCache(String type, String value, String label) {
        Dict dict = new Dict(value, label);
        Set<Dict> dicts = LOCAL_CACHE.get(type);
        if (dicts == null) {
            dicts = new HashSet<>();
        }
        dicts.add(dict);
        LOCAL_CACHE.put(type, dicts);
    }

    public String getDictLabel(String type, String value, String defaultLabel) {
        String label = LOCAL_LABEL_CACHE.get(type + "-" + value);
        if (label != null) {
            return label;
        }
        if (StringUtils.isNotBlank(type) && StringUtils.isNotBlank(value)) {
            synchronized (type.intern()) {
                label = LOCAL_LABEL_CACHE.get(type + "-" + value);
                if (label != null) {
                    return label;
                }
            }

        }
        return defaultLabel;
    }

    public String getDictLabel(String type, String value) {
        return this.getDictLabel(type, value, null);
    }

    public String getDictLabels(String type, String values, String defaultValue) {
        if (StringUtils.isNotBlank(type) && StringUtils.isNotBlank(values)) {
            List<String> valueList = Lists.newArrayList();
            for (String value : StringUtils.split(values, ",")) {
                valueList.add(getDictLabel(value, type, defaultValue));
            }
            return StringUtils.join(valueList, ",");
        }
        return defaultValue;
    }

    public String getDictLabels(String type, String defaultValue) {
        List<String> valueList = Lists.newArrayList();
        Set<Dict> dicts = LOCAL_CACHE.get(type);
        if (dicts == null) {
            return defaultValue;
        }
        for (Dict dict : dicts) {
            valueList.add(dict.getLabel());
        }

        return StringUtils.join(valueList, ",");
    }


    public String getDictValue(String type, String label, String defaultValue) {
        if (StringUtils.isBlank(type) || StringUtils.isBlank(label)) {
            return defaultValue;
        }
        Set<Dict> dicts = LOCAL_CACHE.get(type);
        if (dicts != null) {
            for (Dict dict : dicts) {
                if (label.equalsIgnoreCase(dict.getLabel())) {
                    return dict.getValue();
                }
            }
        }
        return defaultValue;
    }

    public void clearAll() {
        LOCAL_LABEL_CACHE.clear();
        LOCAL_CACHE.clear();
    }


    @Override
    public void afterPropertiesSet() throws Exception {


    }

    @Data
    @AllArgsConstructor
    @EqualsAndHashCode
    private class Dict {
        private String value;
        private String label;
    }

    public static GlobalDictService getInstance() {
        try {
            GlobalDictService instance = SpringUtils.getBean(GlobalDictService.class);
            return instance;
        } catch (Exception e) {
            return null;
        }
    }
}


 rsf-server/src/main/java/com/vincent/rsf/server/common/utils/ExcelUtil.java

@@ -2,7 +2,13 @@

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelDataHandler;
import com.google.common.collect.Lists;
import com.vincent.rsf.common.domain.BeanValidators;
import com.vincent.rsf.framework.common.Cools;
import com.vincent.rsf.server.common.handler.AggregationDataHandler;
import com.vincent.rsf.server.common.handler.ExcelDictHandlerImpl;
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelComment;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
@@ -14,6 +20,10 @@
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolationException;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
@@ -26,7 +36,6 @@
 */
@Slf4j
public class ExcelUtil {

    public static void build(Workbook workbook, HttpServletResponse response) {
        response.reset();
        Http.cors(response);
@@ -116,8 +125,11 @@
     * @return
     */
    public static ImportParams getDefaultImportParams() {
        ImportParams params = new ImportParams();
        return params;
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(0);
        importParams.setHeadRows(1);
        importParams.setSheetNum(1);
        return importParams;
    }

    /**

 rsf-server/src/main/java/com/vincent/rsf/server/manager/controller/AsnOrderController.java

@@ -139,4 +139,14 @@
            return R.error("一键上报失败！！");
        }
    }

    @PostMapping("/asnOrder/generate/barcode")
    @ApiOperation("生成ASN标签")
    @PreAuthorize("hasAuthority('manager:asnOrder:list')")
    public R generateBarcode(@RequestBody List<AsnOrder> orders) {
        if (orders.isEmpty()) {
            return R.error("单据不能为空!!");
        }
        return asnOrderService.generateBarcode(orders);
    }
}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/controller/AsnOrderItemController.java

@@ -76,15 +76,12 @@
            throw new CoolException("信息不能为空！！");
        }
        params.put("createBy", getLoginUserId());
        params.put("updateBy", getLoginUser());
        params.put("updateBy", getLoginUserId());

        if (!asnOrderItemService.fieldsSave(params)) {
            return R.error("Save Fail");
        }
        return R.ok("Save Success");




    }


 rsf-server/src/main/java/com/vincent/rsf/server/manager/entity/excel/AsnOrderTemplate.java

@@ -5,8 +5,6 @@
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelAutoColumnSize;
import com.vincent.rsf.server.manager.entity.excel.annotation.ExcelComment;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;
@@ -24,8 +22,8 @@
@Data
@ExcelAutoColumnSize
@Accessors(chain = true)
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
//@ToString(callSuper = true)
//@EqualsAndHashCode(callSuper = false)
public class AsnOrderTemplate implements IExcelModel, IExcelDataModel, Serializable {

    @Excel(name = "行号")

 rsf-server/src/main/java/com/vincent/rsf/server/manager/schedules/ScheduleJobs.java

@@ -98,8 +98,8 @@
                if (Objects.isNull(matnr)) {
                    throw new CoolException("数据错误：当前物料不存在!!");
                }
                //TODO barcode生成策略
                String barcode = code + matnr.getCode();
//                //TODO barcode生成策略
//                String barcode = code + matnr.getCode();
                orderItem.setAnfme(item.getAnfme())
                        .setAsnId(order.getId())
                        .setQty(item.getQty())
@@ -109,7 +109,7 @@
                        .setMatnk(item.getMatnrName())
                        .setPoDetlId(item.getId())
                        .setPlatItemId(item.getPlatItemId())
                        .setBarcode(barcode)
//                        .setBarcode(barcode)
                        .setPoCode(purchase.getCode())
                        .setPurQty(item.getAnfme())
                        .setPurUnit(item.getUnit())

 rsf-server/src/main/java/com/vincent/rsf/server/manager/service/AsnOrderService.java

@@ -1,6 +1,7 @@
package com.vincent.rsf.server.manager.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.vincent.rsf.framework.common.R;
import com.vincent.rsf.server.manager.entity.AsnOrder;

import java.util.List;
@@ -8,4 +9,6 @@
public interface AsnOrderService extends IService<AsnOrder> {

    boolean notifyInspect(List<AsnOrder> orders);

    R generateBarcode(List<AsnOrder> orders);
}

 rsf-server/src/main/java/com/vincent/rsf/server/manager/service/impl/AsnOrderItemServiceImpl.java

@@ -10,7 +10,6 @@
import com.vincent.rsf.server.common.utils.ExcelUtil;
import com.vincent.rsf.server.manager.entity.AsnOrder;
import com.vincent.rsf.server.manager.entity.excel.AsnOrderTemplate;
import com.vincent.rsf.server.manager.entity.excel.MatnrsTemplate;
import com.vincent.rsf.server.manager.mapper.AsnOrderItemMapper;
import com.vincent.rsf.server.manager.entity.AsnOrderItem;
import com.vincent.rsf.server.manager.mapper.AsnOrderMapper;
@@ -27,7 +26,6 @@
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.*;

@Service("asnOrderItemService")
@@ -44,7 +42,7 @@
    @Override
    @Transactional(rollbackFor = Exception.class)
    public R excelImport(MultipartFile file, HashMap<String, Object> hashMap) throws Exception {
        ExcelImportResult<AsnOrderTemplate> result = ExcelImportUtil.importExcelMore(file.getInputStream(), MatnrsTemplate.class, ExcelUtil.getDefaultImportParams());
        ExcelImportResult result = ExcelImportUtil.importExcelMore(file.getInputStream(), AsnOrderTemplate.class, ExcelUtil.getDefaultImportParams());
        if (result.getList().isEmpty()) {
            throw new CoolException("物料导入失败！！");
        }

 rsf-server/src/main/java/com/vincent/rsf/server/manager/service/impl/AsnOrderServiceImpl.java

@@ -1,14 +1,21 @@
package com.vincent.rsf.server.manager.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.vincent.rsf.framework.common.R;
import com.vincent.rsf.framework.exception.CoolException;
import com.vincent.rsf.server.api.entity.dto.PoItemsDto;
import com.vincent.rsf.server.api.service.ReceiveMsgService;
import com.vincent.rsf.server.manager.entity.AsnOrderItem;
import com.vincent.rsf.server.manager.mapper.AsnOrderItemMapper;
import com.vincent.rsf.server.manager.mapper.AsnOrderMapper;
import com.vincent.rsf.server.manager.entity.AsnOrder;
import com.vincent.rsf.server.manager.mapper.PurchaseMapper;
import com.vincent.rsf.server.manager.service.AsnOrderService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.vincent.rsf.server.system.constant.SerialRuleCode;
import com.vincent.rsf.server.system.entity.SerialRule;
import com.vincent.rsf.server.system.mapper.SerialRuleMapper;
import com.vincent.rsf.server.system.utils.SerialRuleUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@@ -35,6 +42,9 @@
    @Resource
    private AsnOrderItemMapper asnOrderItemMapper;

    @Resource
    private SerialRuleMapper serialRuleMapper;

    @Override
    public boolean notifyInspect(List<AsnOrder> orders) {
        if (orders.isEmpty()) {
@@ -51,4 +61,16 @@
            return false;
        }
    }

    @Override
    public R generateBarcode(List<AsnOrder> orders) {
        orders.forEach(order -> {
            List<AsnOrderItem> items = asnOrderItemMapper.selectList(new LambdaQueryWrapper<AsnOrderItem>().eq(AsnOrderItem::getAsnId, order.getId()));
            items.forEach(item -> {
                String ruleCode = SerialRuleUtils.generateRuleCode(SerialRuleCode.SYS_LABEL_CODE, item);
                //TODO asnorderitem  添加单独的字段，保存编码
            });
        });
        return R.ok();
    }
}

 rsf-server/src/main/java/com/vincent/rsf/server/system/constant/SerialRuleCode.java

@@ -23,4 +23,9 @@
     * 收货批次规则
     */
    public final static String SYS_RECEIPT_BATCH = "sys_receipt_batch";

    /**
     * ASN标签生成规则
     */
    public final static String SYS_LABEL_CODE = "sys_label_code";
}