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 

@ApiOperation("上传")@ApiImplicitParams({@ApiImplicitParam(name = "file",value = "文件",dataTypeClass = MultipartFile.class,required = true,paramType = "")})@PostMapping ("/importS")public RestResponse 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

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")@Datapublic 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;}

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

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  {    private ISsqdService iSsqdService;    public UploadListenerBySsqd(ISsqdService iSsqdService) {        this.iSsqdService = iSsqdService;    }    private List 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 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);        }    }}

　　实现类

@Overridepublic void saveData(List list) {//出现空的数据行，只有边框没有值-处理list = tableNullLineRemove(list);String msg = "";if(list.size() > 0){if (StringUtil.isEmpty(msg)) {// 验证输入数据重复性msg = checkMxList(list);}if (StringUtil.isEmpty(msg)) {// excel数据插入数据库List 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 list) {List errMsgList = new ArrayList();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)+ "行数据记录重复
";/*errMsgList.add("导入文档第" + (i+1) + "行数据记录与第" + j+ "行数据记录重复
");*/}} 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)+ "行数据记录重复
";/*errMsgList.add("导入文档第" + (i+1) + "行数据记录与第" + j+ "行数据记录重复
")*/;}}}}}}return msg;}private Boolean importAdd(List list) {UserDTO userBean = UserUtil.getUserInfo();int index = 0 ;List <Map > addList = new ArrayList <Map >();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 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 tableNullLineRemove(List list) {List 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>