elment UI + EasyExcel 实现 导入

前端组件
复制代码
<hd-flex>
                <el-dialog v-model="isUploadDialog" width="50%" lock-scroll=false>
                    <el-upload
                            class="upload-demo"
                            drag
                            :action="url"
                            :on-success="success"
                            :on-error="error"
                            :headers="uploadHeaders"
                            :limit="1"
                            :on-exceed="handleExceed"
                            :file-list="fileList"
                            accept=".xlsx,.xls">
                        <i class="el-icon-upload"></i>
                        <div class="el-upload__text"><em>点击上传</em></div>
                        <div class="el-upload__tip" slot="tip">只能上传xlsx/xls文件，且不超过500kb</div>
                    </el-upload>
                </el-dialog>
            </hd-flex>

//变量
url: `${conf.BASE_URL}/core/ssqd/importS`,
isUploadDialog: false,
fileList: [],


// 方法
//导入
async importS() {
    this.fileList=[];
    this.isUploadDialog=true;
},
success(response, file, fileList){
    if(response.code=='500'){
        this.$hd.message.error(response.errorBody.errorMessage);
    }
    if(response.code=='200'){
        this.$hd.message.ok('导入成功！');
        this.isUploadDialog=false;
        this.$refs.table.onSearch();
    }


},
error(err, file, fileList){
    this.$hd.message.error(err);

},
handleRemove(file, fileList) {
    console.log(file, fileList)
},
handlePreview(file) {
    console.log(file)
},
handleExceed(files, fileList) {
    this.$message.warning(
        `当前限制选择 1 个文件，本次选择了 ${files.length} 个文件，共选择了 ${
            files.length + fileList.length
        } 个文件`
    )
},
beforeRemove(file, fileList) {
    return this.$confirm(`确定移除 ${file.name}？`)
},
复制代码

Java  

Controller
?
1
2
3
4
5
6
7
8
9
10
11
12
13
@ApiOperation("上传")
@ApiImplicitParams({
        @ApiImplicitParam(name = "file",value = "文件",dataTypeClass = MultipartFile.class,required = true,paramType = "")
})
@PostMapping ("/importS")
public RestResponse<String> uploadExcel(MultipartFile file)throws IOException {
    String HZ = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
    if(".xlsx.xls".indexOf(HZ) < 0){
        throw new BaseException("500","导入的文件类型不正确，只能导入Excel文件");
    }
    EasyExcel.read(file.getInputStream(), SsqdVO.class,new UploadListenerBySsqd(iSsqdService)).sheet() .doRead();;
    return new RestResponse<> ("ok");
}　

javaBean

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
package com.hopedove.coreserver.vo.sygl;
 
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
 
import com.hopedove.commons.vo.BaseModel;
import lombok.Data;
 
/**
 * 璇曠罕娓呭崟
 * @TableName T_JS_SYGL_SSQD
 */
@TableName(value ="T_JS_SYGL_SSQD")
@Data
public class SsqdVO  extends BaseModel implements Serializable {
    /**
     * 璇曠罕娓呭崟ID
     */
    @TableId
    private String SSQDID;
    /**
     * 坯布类型
     */
    @ExcelProperty(value ="试纱类型", index = 0)
    private String PBLX;
 
    /**
     * 布号
     */
    @ExcelProperty(value ="布号", index = 1)
    private String BH;
 
    /**
     * 支数
     */
    @ExcelProperty(value ="支数", index = 2)
    private String ZS;
 
    /**
     * 产地
     */
    @ExcelProperty(value ="产地", index = 3)
    private String CD;
 
    /**
     * 批号
     */
    @ExcelProperty(value ="批号", index = 4)
    private String PH;
 
 
    /**
     * 重量
     */
    @ExcelProperty(value ="重量", index = 5)
    private BigDecimal ZL;
 
    /**
     * 备注
     */
    @ExcelProperty(value ="备注", index = 6)
    private String REMARK;
 
 
 
 
    @TableField(exist = false)
    private String GY;
 
 
}

　　监听器: 判断上传表格是否符合要求

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
package com.hopedove.coreserver.service.impl.sygl;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.hedu.sweet.starter.utils.exception.BusinException;
import com.hopedove.coreserver.service.sygl.ISsqdService;
import com.hopedove.coreserver.vo.sygl.SsqdVO;
 
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
public class UploadListenerBySsqd extends AnalysisEventListener<SsqdVO>  {
 
 
    private ISsqdService iSsqdService;
 
    public UploadListenerBySsqd(ISsqdService iSsqdService) {
        this.iSsqdService = iSsqdService;
    }
 
 
    private List<SsqdVO> list = new ArrayList<>(100);
 
 
    @Override
    public void invoke(SsqdVO ssqdVO, AnalysisContext analysisContext) {
        //业务判断
        System.out.println("***"+ssqdVO+"***");
        list.add(ssqdVO);
//        if (list.size() > 100) {
//            wjgbpclService.saveData(list);//保存到数据库
//            list = ListUtils.newArrayListWithExpectedSize(100);
//        }
    }
 
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if (CollectionUtils.isNotEmpty(list)) {
            System.out.println("***结束***");
            System.out.println(list);
            iSsqdService.saveData(list);
        }else{
            throw new BusinException("500","导入的文件为空,请填写信息后重新导入。");
        }
    }
    /**
     * 在这里进行模板的判断
     * @param headMap 存放着导入表格的表头，键是索引，值是名称
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        String isNull = "";
        if (context.readRowHolder().getRowIndex() == 0) {
            String[] headList = {"试纱类型","布号","支数","产地","批号","重量","备注"};
            for (int i = 0; i < headList.length; i++) {
                try {
                    if (!headMap.get(i).equals(headList[i])) {
                        isNull = "导入模板不正确，请重新导入";
                        break;
                    }
                } catch (Exception e) {
                    isNull = "导入模板不正确，请重新导入";
                    break;
                }
            }
        }
        if(isNull!=""){
            throw new BusinException("500",isNull);
        }
 
    }
 
}

　　实现类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
@Override
public void saveData(List<SsqdVO> list) {
    //出现空的数据行，只有边框没有值-处理
    list = tableNullLineRemove(list);
    String msg = "";
    if(list.size() > 0){
 
        if (StringUtil.isEmpty(msg)) {
            // 验证输入数据重复性
            msg = checkMxList(list);
        }
 
        if (StringUtil.isEmpty(msg)) {
            // excel数据插入数据库
            List<SsqdVO> arr = new ArrayList<>(100);
            for (int i = 0; i < list.size(); i++) {
                SsqdVO bean= list.get(i);
                bean = nullToString(bean);
                arr.add(bean);
            }
            if (CollectionUtils.isNotEmpty(list)) {
                if(!importAdd(list)){//导入
                    throw new BusinException("500","导入的文件有效记录数超过1000条，请分批次多次导入");
                }
            }
        }
    }
    if(!StringUtil.isEmpty(msg)){
        throw new BusinException("500",msg);
    }
}
 
/**
 * 验证输入数据重复性
 * @param list
 * @return
 */
private String checkMxList(List<SsqdVO> list) {
    List<String> errMsgList = new ArrayList<String>();
    String msg = "";
    if (list.size() > 0) {
        // 把页面的数据进行重复性检验
        for (int i = 0; i < list.size(); i++) {
            SsqdVO mxModel = list.get(i);
            String PBLX = StringUtil.nullToSring(mxModel.getPBLX());
            String BH = StringUtil.nullToSring(mxModel.getBH());
            String ZS = StringUtil.nullToSring(mxModel.getZS());
            String ZL = StringUtil.nullToSring(mxModel.getZL());
            //当纱织类型，布号，支数和重量都为空，那么这条记录既不交验，也不添加
            if(StringUtil.isEmpty(PBLX) && StringUtil.isEmpty(BH) && StringUtil.isEmpty(ZS) && StringUtil.isEmpty(ZL)){
                continue;
            }
            for (int j = 1; j < list.size(); j++) {
 
                if (i != j) {
                    SsqdVO mxModelSec =  list.get(j);
                    String PBLXsec = mxModelSec.getPBLX();
                    String BHsec = StringUtil.nullToSring(mxModelSec.getBH());
                    String ZSsec = StringUtil.nullToSring(mxModelSec.getZS());
                    String ZLsec = StringUtil.nullToSring(mxModelSec.getZL());
                    //当纱织类型，布号，支数和重量都为空，那么这条记录既不交验，也不添加
                    if(StringUtil.isEmpty(PBLXsec) && StringUtil.isEmpty(BHsec) && StringUtil.isEmpty(ZSsec) && StringUtil.isEmpty(ZLsec)){
                        continue;
                    }
                    if(PBLX.equals(PBLXsec) && "2".equals(PBLX)){
                        if ((StringUtil.nullToSring(mxModelSec.getBH()))
                                .equals(StringUtil.nullToSring(mxModel.getBH()))
                                && (StringUtil.nullToSring(mxModelSec.getZS()))
                                .equals(StringUtil.nullToSring(mxModel.getZS()))
                                && (StringUtil.nullToSring(mxModelSec.getZL()))
                                .equals(StringUtil.nullToSring(mxModel.getZL()))
                                && (StringUtil.nullToSring(mxModelSec.getSC()))
                                .equals(StringUtil.nullToSring(mxModel.getSC()))
                                && (StringUtil.nullToSring(mxModelSec.getSH()))
                                .equals(StringUtil.nullToSring(mxModel.getSH()))
                                && (StringUtil.nullToSring(mxModelSec.getGY()))
                                .equals(StringUtil.nullToSring(mxModel.getGY()))) {
                            msg="导入文档第" + (i+1) + "行数据记录与第" + (j+1)
                                    + "行数据记录重复</br>";
                            /*errMsgList.add("导入文档第" + (i+1) + "行数据记录与第" + j
                                    + "行数据记录重复</br>");*/
                        }
                    } else {
                        if ((StringUtil.nullToSring(mxModelSec.getBH()))
                                .equals(StringUtil.nullToSring(mxModel.getBH()))
                                && (StringUtil.nullToSring(mxModelSec.getZS()))
                                .equals(StringUtil.nullToSring(mxModel.getZS()))
                                && (StringUtil.nullToSring(mxModelSec.getZL()))
                                .equals(StringUtil.nullToSring(mxModel.getZL()))) {
                            msg="导入文档第" + (i+1) + "行数据记录与第" + (j+1)
                                    + "行数据记录重复</br>";
                            /*errMsgList.add("导入文档第" + (i+1) + "行数据记录与第" + j
                                    + "行数据记录重复</br>")*/;
                        }
                    }
                }
            }
        }
    }
 
    return msg;
}
 
private Boolean importAdd(List<SsqdVO> list) {
    UserDTO userBean = UserUtil.getUserInfo();
    int index = 0 ;
    List <Map <String, String>> addList = new ArrayList <Map <String, String>>();
    for (int i = 0; i < list.size(); i++) {
        SsqdVO entry = list.get(i);
    //保存的处理逻辑
     
    }
    iSsqdDao.insertSSQD(addList);
    iSsqdDao.insertSSRZGY(addList);
    return true;
}
private SsqdVO nullToString(SsqdVO params) {
    Map<String,Object> map = new HashMap<>();
    Field[] fields = params.getClass().getDeclaredFields();
    try {
        for (Field field : fields
        ) {
            //设置允许通过反射访问私有变量
            field.setAccessible(true);
            map.put(field.getName(),field.get(params)==null? "":field.get(params));
        }
 
    }catch (Exception e){
        e.printStackTrace();
    }
    return MapUntil.mapToBean(map,new SsqdVO());
}
private List<SsqdVO> tableNullLineRemove(List<SsqdVO> list) {
    List<SsqdVO> l = new ArrayList<>();
    //当纱织类型，布号，支数和重量都为空，那么这条记录既不交验，也不添加
    for (SsqdVO model:list
    ) {
        if(model.getPBLX()==null && model.getBH() ==null&&model.getZS()==null &&model.getZL()==null){
            continue;
        }else{
            l.add(model);
        }
    }
    return l;
}

　　SQL  oracle数据库批量新增

复制代码
    <insert id="insertSSQD" parameterType="list">
        insert all
        <foreach collection="list" item="item">
        <![CDATA[
        into T_JS_SYGL_SSQD
        (
        SSQDID,
        RSQDBH,
        BH,
        PBLX,
        PBMC,
        ZS,
        CREATER,
        CRENAME,
        UPDATER,
        BMXXID,
        BMMC,
        JGXXID,
        JGMC,
        ZTXXID,
        ZTMC
        ]]>
        <if test=" item.ZL != null and item.ZL != '' ">,ZL </if>
        <if test=" item.SH != null and item.SH != '' ">,SH </if>
        <if test=" item.ZFMYQ != null and item.ZFMYQ != '' ">,ZFMYQ </if>
        <if test=" item.XSYQ != null and item.XSYQ != '' ">,XSYQ </if>
        <if test=" item.SG != null and item.SG != '' ">,SG </if>
        <if test=" item.REMARK != null and item.REMARK != '' ">,REMARK </if>
        <if test=" item.CD != null and item.CD != '' ">,CD </if>
        <if test=" item.PH != null and item.PH != '' ">,PH </if>
        <if test=" item.SC != null and item.SC != '' ">,SC </if>
        ) VALUES(
        <![CDATA[
        #{item.SSQDID},
        #{item.RSQDBH},
        #{item.BH},
        #{item.PBLX},
        #{item.PBMC},
        #{item.ZS},
        #{item.CREATER},
        #{item.CRENAME},
        #{item.UPDATER},
        #{item.BMXXID},
        #{item.BMMC},
        #{item.JGXXID},
        #{item.JGMC},
        #{item.ZTXXID},
        #{item.ZTMC}
        ]]>
        <if test=" item.ZL != null and item.ZL != '' ">,#{item.ZL} </if>
        <if test=" item.SH != null and item.SH != '' ">,#{item.SH} </if>
        <if test=" item.ZFMYQ != null and item.ZFMYQ != '' ">,#{item.ZFMYQ} </if>
        <if test=" item.XSYQ != null and item.XSYQ != '' ">,#{item.XSYQ} </if>
        <if test=" item.SG != null and item.SG != '' ">,#{item.SG} </if>
        <if test=" item.REMARK != null and item.REMARK != '' ">,#{item.REMARK} </if>
        <if test=" item.CD != null and item.CD != '' ">,#{item.CD} </if>
        <if test=" item.PH != null and item.PH != '' ">,#{item.PH} </if>
        <if test=" item.SC != null and item.SC != '' ">,#{item.SC} </if>
        )

        </foreach>
        select * from dual
    </insert>
复制代码

 

posted @   Li_ll  Views(147)  Comments(0)  Edit  收藏  举报
相关博文：
· EasyExcel导出
· ExcelUtil
· springboot使用EasyExcel进行导入导出
· 阿里Excel工具的简单使用
· “easyExcel”导入的代码实现
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通
点击右上角即可分享
微信分享提示