【excl批导&校验】实现批量导入销售视图excl文件，并实现校验excl列字段。

实现批量导入销售视图excl文件，并校验excl列字段

1.导入依赖

    <properties>    
		<poi.version>4.1.2</poi.version>
        <hibernate-validator.version>6.0.16.Final</hibernate-validator.version>
        <validation-api.version>2.0.1.Final</validation-api.version>
     </properties>   

    <!-- 依赖声明 -->
    <dependencyManagement>
        <dependencies>	
			<!-- excel工具 -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.version}</version>
            </dependency>

			<!-- 验证工具-->
			<dependency>
                <groupId>javax.validation</groupId>
                <artifactId>validation-api</artifactId>
            </dependency>
            <!-- hibernate validator-->
            <dependency>
                <groupId>org.hibernate.validator</groupId>
                <artifactId>hibernate-validator</artifactId>
                <version>${validator}</version>
            </dependency>
            
        </dependencies>
    </dependencyManagement>
            

2.controller

    @PostMapping("/importMvke")
    public AjaxResult importExcel(MultipartFile file) throws Exception {
        // 如果文件不存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        // 校验文件类型
        String originalFilename = file.getOriginalFilename();
        String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
        if (!".xls".equals(fileType) && !".xlsx".equals(fileType)) {
            return  AjaxResult.error("导入的文件类型有误");
        }
        // 将文件流转为List<T>
        List<TnMdMvkeTemp> detailList = null;
        try {
            ExcelUtil<TnMdMvkeTemp> util = new ExcelUtil<>(TnMdMvkeTemp.class);
            detailList = util.importExcel(file.getInputStream());
        } catch (Exception e) {
           e.printStackTrace();
           return  AjaxResult.error("解析失败");
        }
        // 校验文件列属性-对象属性
        String message = tnMdMvkeTempService.importData(detailList);
        // 如果校验无误，返回List<T>,否则，返回校验的错误信息
        if (StringUtils.isNull(message)) {
            return AjaxResult.success(detailList);
        } else {
            return AjaxResult.error(message);
        }
    }

3.service

    /**
     * 批量导入销售视图
     * @param detailList
     * @return
     */
    public  String importData(List<TnMdMvkeTemp> detailList);

4.serviceImpl

    /**
     * 批量导入销售视图
     * @param detailList
     * @return
     */
    @Override
    public String importData(List<TnMdMvkeTemp> detailList) {
        StringBuffer failureMsg = new StringBuffer();
        // 校验List<T>为null
        if (StringUtils.isNull(detailList)||detailList.size() == 0){
            return  failureMsg.append("导入的数据不能为空！").toString();
        }
        // 遍历List<T>
        for (int i = 0; i < detailList.size(); i++) {
            TnMdMvkeTemp detail = detailList.get(i);
            if (StringUtils.isNull(detail.getVkorg())){
                return  failureMsg.append("导入的数据错误！").toString();
            }
            // 开启Validation验证
            Set<ConstraintViolation<TnMdMvkeTemp>> result = Validation.buildDefaultValidatorFactory().getValidator().validate(detail);
            // 如果验证结果为null,说明没有问题，结束方法，返回null
            if (result.isEmpty()){
                return  null;
            }
            String message = result.stream().map(ConstraintViolation::getMessage).collect(Collectors.joining(";","[第"+(i+1)+"行","]"));
            failureMsg.append(message);
        }
        return  failureMsg.toString();
    }

5.实体类属性上加校验条件

package com.tn.mdm.mdm.domain;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.tn.mdm.common.annotation.Excel;
import com.tn.mdm.common.core.domain.BaseEntity;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;

/**
 * 物料主数据销售视图临时对象 tn_md_mvke_temp
 *
 * @author yongda
 * @date 2022-10-26
 */
public class TnMdMvkeTemp extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** 自增编号 */
    public Long id;

    /** 任务关联外键 */
    @Excel(name = "任务关联外键")
    public String taskId;

    /** 任务单中物料序号 （物料虚拟号）*/
    @Excel(name = "物料虚拟号")
    public Long matIndex;

    /** 同步标识 Y/N */
    @Excel(name = "同步标识 Y/N")
    public String syncFlag;

    /** 物料编码 */
    @Excel(name = "物料编码")
    @NotBlank(message = "物料编码不可为空")
    public String matnr;

    /** 物料描述（短文本) */
    @Excel(name = "物料描述", readConverterExp = "物料描述（短文本)")
    public String maktx;

    /** 物料类型 */
    @Excel(name = "物料类型")
    public String mtart;

    /** 基本计量单位（物料基本单位） */
    @Excel(name = "物料基本单位")
    public String meins;

    /** 销售组织 */
    @Excel(name = "销售组织")
    @NotBlank(message = "销售组织不可为空")
    public String vkorg;

    /** 分销渠道 */
    @Excel(name = "分销渠道")
    @NotBlank(message = "分销渠道不可为空")
    public String vtweg;

    /** 销售单位 */
    @Excel(name = "销售单位")
    public String vrkme;

    /** 该物料的科目设置组 */
    @Excel(name = "科目设置组")
   @NotBlank(message = "该物料的科目设置组不可为空")
    public String ktgrm;

    /** 来自物料主文件的项目类别组 */
    @Excel(name = "普通项目类别组")
    @NotBlank(message = "来自物料主文件的项目类别组不可为空")
    public String mtpos;

    /** 物料的税分类 */
    @Excel(name = "税分类")
    public String taxm1;

    /** 物料组 1 */
    @Excel(name = "物料组1")
    public String mvgr1;

    /** 物料组 2 */
    @Excel(name = "物料组2")
    public String mvgr2;

    /** 物料组 3 */
    @Excel(name = "物料组3")
    @Length(max = 3,message = "物料组3长度不可超过3个字符")
    @Pattern(regexp = "^$|^([A-Z]|\\d){1,3}$",message = "物料组3大写英文字母数字")
    public String mvgr3;

    /** 物料组 4 */
    @Excel(name = "物料组4")
    public String mvgr4;

    /** 物料组 5 */
    @Excel(name = "物料组5")
    @Length(max = 3,message = "物料组5长度不可超过3个字符")
    public String mvgr5;

    /** 消息类型 */
    @Excel(name = "消息类型")
    public String type;

    /** 消息文本 */
    @Excel(name = "消息文本")
    public String message;

    public void setId(Long id)
    {
        this.id = id;
    }

    public Long getId()
    {
        return id;
    }
    public void setTaskId(String taskId)
    {
        this.taskId = taskId;
    }

    public String getTaskId()
    {
        return taskId;
    }
    public void setMatIndex(Long matIndex)
    {
        this.matIndex = matIndex;
    }

    public Long getMatIndex()
    {
        return matIndex;
    }
    public void setSyncFlag(String syncFlag)
    {
        this.syncFlag = syncFlag;
    }

    public String getSyncFlag()
    {
        return syncFlag;
    }
    public void setMatnr(String matnr)
    {
        this.matnr = matnr;
    }

    public String getMatnr()
    {
        return matnr;
    }
    public void setMaktx(String maktx)
    {
        this.maktx = maktx;
    }

    public String getMaktx()
    {
        return maktx;
    }
    public void setMtart(String mtart)
    {
        this.mtart = mtart;
    }

    public String getMtart()
    {
        return mtart;
    }
    public void setMeins(String meins)
    {
        this.meins = meins;
    }

    public String getMeins()
    {
        return meins;
    }
    public void setVkorg(String vkorg)
    {
        this.vkorg = vkorg;
    }

    public String getVkorg()
    {
        return vkorg;
    }
    public void setVtweg(String vtweg)
    {
        this.vtweg = vtweg;
    }

    public String getVtweg()
    {
        return vtweg;
    }
    public void setVrkme(String vrkme)
    {
        this.vrkme = vrkme;
    }

    public String getVrkme()
    {
        return vrkme;
    }
    public void setKtgrm(String ktgrm)
    {
        this.ktgrm = ktgrm;
    }

    public String getKtgrm()
    {
        return ktgrm;
    }
    public void setMtpos(String mtpos)
    {
        this.mtpos = mtpos;
    }

    public String getMtpos()
    {
        return mtpos;
    }
    public void setTaxm1(String taxm1)
    {
        this.taxm1 = taxm1;
    }

    public String getTaxm1()
    {
        return taxm1;
    }
    public void setMvgr1(String mvgr1)
    {
        this.mvgr1 = mvgr1;
    }

    public String getMvgr1()
    {
        return mvgr1;
    }
    public void setMvgr2(String mvgr2)
    {
        this.mvgr2 = mvgr2;
    }

    public String getMvgr2()
    {
        return mvgr2;
    }
    public void setMvgr3(String mvgr3)
    {
        this.mvgr3 = mvgr3;
    }

    public String getMvgr3()
    {
        return mvgr3;
    }
    public void setMvgr4(String mvgr4)
    {
        this.mvgr4 = mvgr4;
    }

    public String getMvgr4()
    {
        return mvgr4;
    }
    public void setMvgr5(String mvgr5)
    {
        this.mvgr5 = mvgr5;
    }

    public String getMvgr5()
    {
        return mvgr5;
    }
    public void setType(String type)
    {
        this.type = type;
    }

    public String getType()
    {
        return type;
    }
    public void setMessage(String message)
    {
        this.message = message;
    }

    public String getMessage()
    {
        return message;
    }

    @Override
    public String toString() {
        return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
            .append("id", getId())
            .append("taskId", getTaskId())
            .append("matIndex", getMatIndex())
            .append("syncFlag", getSyncFlag())
            .append("matnr", getMatnr())
            .append("maktx", getMaktx())
            .append("mtart", getMtart())
            .append("meins", getMeins())
            .append("vkorg", getVkorg())
            .append("vtweg", getVtweg())
            .append("vrkme", getVrkme())
            .append("ktgrm", getKtgrm())
            .append("mtpos", getMtpos())
            .append("taxm1", getTaxm1())
            .append("mvgr1", getMvgr1())
            .append("mvgr2", getMvgr2())
            .append("mvgr3", getMvgr3())
            .append("mvgr4", getMvgr4())
            .append("mvgr5", getMvgr5())
            .append("type", getType())
            .append("message", getMessage())
            .append("remark", getRemark())
            .toString();
    }
}