1 导入依赖
<properties>
<poi.version>4.1.2</poi.version>
<validator>6.0.16.Final</validator>
</properties>
<dependencies>
<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>
<dependency>
<groupId>org.hibernate.validator</groupId>
<artifactId>hibernate-validator</artifactId>
<version>${validator}</version>
</dependency>
</dependencies>
2 代码实现
contorller.java
@PostMapping("/importDescRule")
public AjaxResult importExcel(MultipartFile file) throws Exception {
try {
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<TnMdMaraDescRule> detailList = null;
try {
ExcelUtil<TnMdMaraDescRule> util = new ExcelUtil<>(TnMdMaraDescRule.class);
detailList = util.importExcel("物料描述规则",file.getInputStream(),0);
} catch (Exception e) {
e.printStackTrace();
return AjaxResult.error("解析失败");
}
String message = tnMdMaraDescRuleService.importData(detailList);
if (StringUtils.isNull(message)) {
return AjaxResult.success(detailList);
} else {
return AjaxResult.error(message);
}
}catch (Exception e){
e.printStackTrace();
return AjaxResult.error(e.getMessage()==null?"系统异常":e.getMessage());
}
}
@PreAuthorize("@ss.hasPermi('masterdata:matrule:add')")
@Log(title = "大中小类物料描述规则", businessType = BusinessType.INSERT)
@PostMapping("/saveRules")
public AjaxResult insertTnMdMaraDescRuleList(@RequestBody List<TnMdMaraDescRule> tnMdMaraDescRules)
{
try {
if (tnMdMaraDescRules.size() == 0){
return AjaxResult.error("保存数据为空");
}
int count = tnMdMaraDescRuleService.insertTnMdMaraDescRuleList(tnMdMaraDescRules);
if (count > 0){
return AjaxResult.success("保存成功");
}
return AjaxResult.error("保存失败");
}catch (Exception e){
e.printStackTrace();
return AjaxResult.error(e.getMessage()==null?"系统异常":e.getMessage());
}
}
service.java
public String importData(List<TnMdMaraDescRule> detailList);
public int insertTnMdMaraDescRuleList(List<TnMdMaraDescRule> tnMdMaraDescRules);
serviceImpl.java
@Override
public String importData(List<TnMdMaraDescRule> detailList) {
if (StringUtils.isNull(detailList) || detailList.size() == 0){
throw new SecurityException("导入的数据不能为空");
}
StringBuffer failureMsg = new StringBuffer();
for (int index = 0; index < detailList.size(); index++) {
String msgPre = "第" + (index + 1) + "项";
TnMdMaraDescRule detail = detailList.get(index);
if (StringUtils.isNull(detail.getZwldl())){
return failureMsg.append(msgPre + "大类必填").toString();
}
Set<ConstraintViolation<TnMdMaraDescRule>> result = Validation.buildDefaultValidatorFactory().getValidator().validate(detail);
if (!result.isEmpty()) {
String message = result.stream().map(ConstraintViolation::getMessage).collect(Collectors.joining(",", msgPre,";"));
failureMsg.append(message);
}
for (int i = 0; i < index; i++) {
TnMdMaraDescRule mdMaraDescRule = detailList.get(i);
if (detail.getZwldl().equals(mdMaraDescRule.getZwldl()) && (detail.getZwlzl().equals(mdMaraDescRule.getZwlzl())) && (detail.getZwlxl().equals(mdMaraDescRule.getZwlxl())) && (detail.getZwlsl().equals(mdMaraDescRule.getZwlsl()))) {
return failureMsg.append(msgPre + "相同大类-中类-小类时,存在相同细类,或细类都为空").toString();
}
if (detail.getMatCodePre().equals(mdMaraDescRule.getMatCodePre())){
return failureMsg.append(msgPre + "存在相同 物料编码前缀").toString();
}
}
if (StringUtils.isBlank(failureMsg)){
return null;
}
}
return failureMsg.toString();
}
@Override
public int insertTnMdMaraDescRuleList(List<TnMdMaraDescRule> tnMdMaraDescRules) {
int count = 0;
for (int i = 0; i < tnMdMaraDescRules.size(); i++) {
TnMdMaraDescRule detail = tnMdMaraDescRules.get(i);
List<TnMdMaraDescRule> existsDetails = tnMdMaraDescRuleMapper.selectTnMdMaraDescRuleList(detail);
if (existsDetails.size() == 0) {
count += tnMdMaraDescRuleMapper.insertTnMdMaraDescRule(detail);
} else {
detail.setId(existsDetails.get(0).getId());
count += tnMdMaraDescRuleMapper.updateTnMdMaraDescRule(detail);
}
}
return count;
}
mapper.java
public List<TnMdMaraDescRule> selectTnMdMaraDescRuleList(TnMdMaraDescRule tnMdMaraDescRule);
public int insertTnMdMaraDescRule(TnMdMaraDescRule tnMdMaraDescRule);
public int updateTnMdMaraDescRule(TnMdMaraDescRule tnMdMaraDescRule);
mapper.xml
<select id="selectTnMdMaraDescRuleList" parameterType="TnMdMaraDescRule" resultMap="TnMdMaraDescRuleResult">
<include refid="selectTnMdMaraDescRuleVo"/>
<where>
<if test="zwldl != null and zwldl != ''"> and ZWLDL like concat('%', #{zwldl}, '%') </if>
<if test="zwlzl != null and zwlzl != ''"> and ZWLZL like concat('%', #{zwlzl}, '%')</if>
<if test="zwlxl != null and zwlxl != ''"> and ZWLXL like concat('%', #{zwlxl}, '%')</if>
<if test="zwlsl != null and zwlsl != ''"> and ZWLSL like concat('%', #{zwlsl}, '%')</if>
<if test="matCodePre != null and matCodePre != ''"> and mat_code_pre = #{matCodePre}</if>
<if test="matDescRule != null and matDescRule != ''"> and mat_desc_rule = #{matDescRule}</if>
<if test="maraName != null and maraName != ''"> and MARA_NAME like concat('%', #{maraName}, '%')</if>
<if test="zsx1 != null and zsx1 != ''"> and ZSX1 = #{zsx1}</if>
<if test="zsx2 != null and zsx2 != ''"> and ZSX2 = #{zsx2}</if>
<if test="zsx3 != null and zsx3 != ''"> and ZSX3 = #{zsx3}</if>
<if test="zsx4 != null and zsx4 != ''"> and ZSX4 = #{zsx4}</if>
<if test="zsx5 != null and zsx5 != ''"> and ZSX5 = #{zsx5}</if>
<if test="zsx6 != null and zsx6 != ''"> and ZSX6 = #{zsx6}</if>
<if test="zsx7 != null and zsx7 != ''"> and ZSX7 = #{zsx7}</if>
<if test="zsx8 != null and zsx8 != ''"> and ZSX8 = #{zsx8}</if>
<if test="zsx9 != null and zsx9 != ''"> and ZSX9 = #{zsx9}</if>
<if test="zsx10 != null and zsx10 != ''"> and ZSX10 = #{zsx10}</if>
<if test="zsx11 != null and zsx11 != ''"> and ZSX11 = #{zsx11}</if>
<if test="zsx12 != null and zsx12 != ''"> and ZSX12 = #{zsx12}</if>
</where>
</select>
<insert id="insertTnMdMaraDescRule" parameterType="TnMdMaraDescRule" useGeneratedKeys="true" keyProperty="id">
insert into tn_md_mara_desc_rule
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="zwldl != null and zwldl != ''">ZWLDL,</if>
<if test="zwlzl != null">ZWLZL,</if>
<if test="zwlxl != null">ZWLXL,</if>
<if test="zwlsl != null">ZWLSL,</if>
<if test="matCodePre != null">mat_code_pre,</if>
<if test="matDescRule != null">mat_desc_rule,</if>
<if test="maraName != null">MARA_NAME,</if>
<if test="zsx1 != null">ZSX1,</if>
<if test="zsx2 != null">ZSX2,</if>
<if test="zsx3 != null">ZSX3,</if>
<if test="zsx4 != null">ZSX4,</if>
<if test="zsx5 != null">ZSX5,</if>
<if test="zsx6 != null">ZSX6,</if>
<if test="zsx7 != null">ZSX7,</if>
<if test="zsx8 != null">ZSX8,</if>
<if test="zsx9 != null">ZSX9,</if>
<if test="zsx10 != null">ZSX10,</if>
<if test="zsx11 != null">ZSX11,</if>
<if test="zsx12 != null">ZSX12,</if>
<if test="createBy != null">create_by,</if>
<if test="createTime != null">create_time,</if>
<if test="updateBy != null">update_by,</if>
<if test="updateTime != null">update_time,</if>
<if test="remark != null">remark,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="zwldl != null and zwldl != ''">#{zwldl},</if>
<if test="zwlzl != null">#{zwlzl},</if>
<if test="zwlxl != null">#{zwlxl},</if>
<if test="zwlsl != null">#{zwlsl},</if>
<if test="matCodePre != null">#{matCodePre},</if>
<if test="matDescRule != null">#{matDescRule},</if>
<if test="maraName != null">#{maraName},</if>
<if test="zsx1 != null">#{zsx1},</if>
<if test="zsx2 != null">#{zsx2},</if>
<if test="zsx3 != null">#{zsx3},</if>
<if test="zsx4 != null">#{zsx4},</if>
<if test="zsx5 != null">#{zsx5},</if>
<if test="zsx6 != null">#{zsx6},</if>
<if test="zsx7 != null">#{zsx7},</if>
<if test="zsx8 != null">#{zsx8},</if>
<if test="zsx9 != null">#{zsx9},</if>
<if test="zsx10 != null">#{zsx10},</if>
<if test="zsx11 != null">#{zsx11},</if>
<if test="zsx12 != null">#{zsx12},</if>
<if test="createBy != null">#{createBy},</if>
<if test="createTime != null">#{createTime},</if>
<if test="updateBy != null">#{updateBy},</if>
<if test="updateTime != null">#{updateTime},</if>
<if test="remark != null">#{remark},</if>
</trim>
</insert>
<update id="updateTnMdMaraDescRule" parameterType="TnMdMaraDescRule">
update tn_md_mara_desc_rule
<trim prefix="SET" suffixOverrides=",">
<if test="zwldl != null and zwldl != ''">ZWLDL = #{zwldl},</if>
<if test="zwlzl != null">ZWLZL = #{zwlzl},</if>
<if test="zwlxl != null">ZWLXL = #{zwlxl},</if>
<if test="zwlsl != null">ZWLSL = #{zwlsl},</if>
<if test="matCodePre != null">mat_code_pre = #{matCodePre},</if>
<if test="matDescRule != null">mat_desc_rule = #{matDescRule},</if>
<if test="maraName != null">MARA_NAME = #{maraName},</if>
<if test="zsx1 != null">ZSX1 = #{zsx1},</if>
<if test="zsx2 != null">ZSX2 = #{zsx2},</if>
<if test="zsx3 != null">ZSX3 = #{zsx3},</if>
<if test="zsx4 != null">ZSX4 = #{zsx4},</if>
<if test="zsx5 != null">ZSX5 = #{zsx5},</if>
<if test="zsx6 != null">ZSX6 = #{zsx6},</if>
<if test="zsx7 != null">ZSX7 = #{zsx7},</if>
<if test="zsx8 != null">ZSX8 = #{zsx8},</if>
<if test="zsx9 != null">ZSX9 = #{zsx9},</if>
<if test="zsx10 != null">ZSX10 = #{zsx10},</if>
<if test="zsx11 != null">ZSX11 = #{zsx11},</if>
<if test="zsx12 != null">ZSX12 = #{zsx12},</if>
<if test="createBy != null">create_by = #{createBy},</if>
<if test="createTime != null">create_time = #{createTime},</if>
<if test="updateBy != null">update_by = #{updateBy},</if>
<if test="updateTime != null">update_time = #{updateTime},</if>
<if test="remark != null">remark = #{remark},</if>
</trim>
where id = #{id}
</update>
TnMdMaraDescRule.java
package com.tn.mdm.masterdata.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 org.springframework.util.ObjectUtils;
import javax.validation.constraints.NotBlank;
import java.util.ArrayList;
import java.util.List;
public class TnMdMaraDescRule extends BaseEntity
{
private static final long serialVersionUID = 1L;
private Long id;
@NotBlank(message = "物料大类不能为空")
@Length(max = 15, message = "大类不能超过15个字符")
@Excel(name = "大类")
private String zwldl;
@Length(max = 15, message = "中类不能超过15个字符")
@Excel(name = "中类")
private String zwlzl;
@Length(max = 15, message = "小类不能超过15个字符")
@Excel(name = "小类")
private String zwlxl;
@Length(max = 15, message = "细类不能超过15个字符")
@Excel(name = "细类")
private String zwlsl;
@Length(max = 5, message = "物料编码前5位不能超过5个字符" )
@Excel(name = "物料编码前5位")
private String matCodePre;
@Length(max = 200, message = "物料描述明细规则不能超过200个字符" )
@Excel(name = "物料描述明细规则")
private String matDescRule;
private List<String> matDescRuleDetail;
@Length(max = 30, message = "物料名不能超过30个字符" )
@Excel(name = "物料名")
private String maraName;
@Length(max = 15, message = "属性名称1不能超过15个字符" )
@Excel(name = "属性名称1")
private String zsx1;
@Length(max = 15, message = "属性名称2不能超过15个字符" )
@Excel(name = "属性名称2")
private String zsx2;
@Length(max = 15, message = "属性名称3不能超过15个字符" )
@Excel(name = "属性名称3")
private String zsx3;
@Length(max = 15, message = "属性名称4不能超过15个字符" )
@Excel(name = "属性名称4")
private String zsx4;
@Length(max = 15, message = "属性名称5不能超过15个字符" )
@Excel(name = "属性名称5")
private String zsx5;
@Length(max = 15, message = "属性名称6不能超过15个字符" )
@Excel(name = "属性名称6")
private String zsx6;
@Length(max = 15, message = "属性名称7不能超过15个字符" )
@Excel(name = "属性名称7")
private String zsx7;
@Length(max = 15, message = "属性名称8不能超过15个字符" )
@Excel(name = "属性名称8")
private String zsx8;
@Length(max = 15, message = "属性名称9不能超过15个字符" )
@Excel(name = "属性名称9")
private String zsx9;
@Length(max = 15, message = "属性名称10不能超过15个字符" )
@Excel(name = "属性名称10")
private String zsx10;
@Length(max = 15, message = "属性名称11不能超过15个字符" )
@Excel(name = "属性名称11")
private String zsx11;
@Length(max = 15, message = "属性名称12不能超过15个字符" )
@Excel(name = "属性名称12")
private String zsx12;
public void initMatDescRuleDetail(){
matDescRuleDetail = new ArrayList<>();
optContainDes();
}
public List<String> getMatDescRuleDetail() {
return matDescRuleDetail;
}
public void setMatDescRuleDetail(List<String> matDescRuleDetail) {
this.matDescRuleDetail = matDescRuleDetail;
}
private void optContainDes(){
if(!ObjectUtils.isEmpty(matDescRule)) {
if (!ObjectUtils.isEmpty(maraName) && matDescRule.contains(maraName)) {
matDescRuleDetail.add("maraName");
}
if (!ObjectUtils.isEmpty(zsx1) && matDescRule.contains(zsx1)) {
matDescRuleDetail.add("zsx1");
}
if (!ObjectUtils.isEmpty(zsx2) && matDescRule.contains(zsx2)) {
matDescRuleDetail.add("zsx2");
}
if (!ObjectUtils.isEmpty(zsx3) && matDescRule.contains(zsx3)) {
matDescRuleDetail.add("zsx3");
}
if (!ObjectUtils.isEmpty(zsx4) && matDescRule.contains(zsx4)) {
matDescRuleDetail.add("zsx4");
}
if (!ObjectUtils.isEmpty(zsx5) && matDescRule.contains(zsx5)) {
matDescRuleDetail.add("zsx5");
}
if (!ObjectUtils.isEmpty(zsx6) && matDescRule.contains(zsx6)) {
matDescRuleDetail.add("zsx6");
}
if (!ObjectUtils.isEmpty(zsx7) && matDescRule.contains(zsx7)) {
matDescRuleDetail.add("zsx7");
}
if (!ObjectUtils.isEmpty(zsx8) && matDescRule.contains(zsx8)) {
matDescRuleDetail.add("zsx8");
}
if (!ObjectUtils.isEmpty(zsx9) && matDescRule.contains(zsx9)) {
matDescRuleDetail.add("zsx9");
}
if (!ObjectUtils.isEmpty(zsx10) && matDescRule.contains(zsx10)) {
matDescRuleDetail.add("zsx10");
}
if (!ObjectUtils.isEmpty(zsx11) && matDescRule.contains(zsx11)) {
matDescRuleDetail.add("zsx11");
}
if (!ObjectUtils.isEmpty(zsx12) && matDescRule.contains(zsx12)) {
matDescRuleDetail.add("zsx12");
}
}
}
private String optDes(String name){
if (name.equals(maraName)) {
return "maraName";
}
if (name.equals(zsx1)) {
return "zsx1";
}
if (name.equals(zsx2)) {
return "zsx2";
}
if (name.equals(zsx3)) {
return "zsx3";
}
if (name.equals(zsx4)) {
return "zsx4";
}
if (name.equals(zsx5)) {
return "zsx5";
}
if (name.equals(zsx6)) {
return "zsx6";
}
if (name.equals(zsx7)) {
return "zsx7";
}
if (name.equals(zsx8)) {
return "zsx8";
}
if (name.equals(zsx9)) {
return "zsx9";
}
if (name.equals(zsx10)) {
return "zsx10";
}
if (name.equals(zsx11)) {
return "zsx11";
}
if (name.equals(zsx12)) {
return "zsx12";
}
return null;
}
public void setId(Long id)
{
this.id = id;
}
public Long getId()
{
return id;
}
public void setZwldl(String zwldl)
{
this.zwldl = zwldl;
}
public String getZwldl()
{
return zwldl;
}
public void setZwlzl(String zwlzl)
{
this.zwlzl = zwlzl;
}
public String getZwlzl()
{
return zwlzl;
}
public void setZwlxl(String zwlxl)
{
this.zwlxl = zwlxl;
}
public String getZwlxl()
{
return zwlxl;
}
public void setZwlsl(String zwlsl)
{
this.zwlsl = zwlsl;
}
public String getZwlsl()
{
return zwlsl;
}
public void setMatCodePre(String matCodePre)
{
this.matCodePre = matCodePre;
}
public String getMatCodePre()
{
return matCodePre;
}
public void setMatDescRule(String matDescRule)
{
this.matDescRule = matDescRule;
}
public String getMatDescRule()
{
return matDescRule;
}
public void setMaraName(String maraName)
{
this.maraName = maraName;
}
public String getMaraName()
{
return maraName;
}
public void setZsx1(String zsx1)
{
this.zsx1 = zsx1;
}
public String getZsx1()
{
return zsx1;
}
public void setZsx2(String zsx2)
{
this.zsx2 = zsx2;
}
public String getZsx2()
{
return zsx2;
}
public void setZsx3(String zsx3)
{
this.zsx3 = zsx3;
}
public String getZsx3()
{
return zsx3;
}
public void setZsx4(String zsx4)
{
this.zsx4 = zsx4;
}
public String getZsx4()
{
return zsx4;
}
public void setZsx5(String zsx5)
{
this.zsx5 = zsx5;
}
public String getZsx5()
{
return zsx5;
}
public void setZsx6(String zsx6)
{
this.zsx6 = zsx6;
}
public String getZsx6()
{
return zsx6;
}
public void setZsx7(String zsx7)
{
this.zsx7 = zsx7;
}
public String getZsx7()
{
return zsx7;
}
public void setZsx8(String zsx8)
{
this.zsx8 = zsx8;
}
public String getZsx8()
{
return zsx8;
}
public void setZsx9(String zsx9)
{
this.zsx9 = zsx9;
}
public String getZsx9()
{
return zsx9;
}
public void setZsx10(String zsx10)
{
this.zsx10 = zsx10;
}
public String getZsx10()
{
return zsx10;
}
public void setZsx11(String zsx11)
{
this.zsx11 = zsx11;
}
public String getZsx11()
{
return zsx11;
}
public void setZsx12(String zsx12)
{
this.zsx12 = zsx12;
}
public String getZsx12()
{
return zsx12;
}
@Override
public String toString() {
return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
.append("id", getId())
.append("zwldl", getZwldl())
.append("zwlzl", getZwlzl())
.append("zwlxl", getZwlxl())
.append("zwlsl", getZwlsl())
.append("matCodePre", getMatCodePre())
.append("matDescRule", getMatDescRule())
.append("maraName", getMaraName())
.append("zsx1", getZsx1())
.append("zsx2", getZsx2())
.append("zsx3", getZsx3())
.append("zsx4", getZsx4())
.append("zsx5", getZsx5())
.append("zsx6", getZsx6())
.append("zsx7", getZsx7())
.append("zsx8", getZsx8())
.append("zsx9", getZsx9())
.append("zsx10", getZsx10())
.append("zsx11", getZsx11())
.append("zsx12", getZsx12())
.append("createBy", getCreateBy())
.append("createTime", getCreateTime())
.append("updateBy", getUpdateBy())
.append("updateTime", getUpdateTime())
.append("remark", getRemark())
.toString();
}
}