SSH导入读取导入的Excel

痛定思痛。 2022-05-08 08:42 223阅读 0赞

一.前端关键代码

![70][]

二.js提交脚本

//关闭
    function closep(){
    	parent.layer.closeAll();
    }
    //导入文件
    function doImport() {
    	$('#thisform').submit(function(){
    	    var fName = $("#importFile").val();
    	 	var i = fName.lastIndexOf("\\");
    	 	if(i!=-1)
    	 	{
    	 		fName = fName.substring(i+1);
    	 	}
    					
    		if(fName=='')
    		{
    			alert("请选择文件（excel文件）");
    			return false;
    		}
    		if(fName.substring(fName.length - 4, fName.length).toLowerCase() != ".xls") {
    			alert("只能导入excel文件！");
    			return false;
    		}
    		var options = {
    			url : basePath+"smp/smp/SmpProduct-doImport.action?upFile="+fName,
    			beforeSubmit:  showRequest,   
    			success:       showResponse,
    			dataType:		'json' 
    			};
    		$(this).ajaxSubmit(options); 
    			return false;//阻止表单提交  		
    		});
    	}
    			 
    function showRequest() {   
    	$.blockUI({message: '<div style="margin: 5px;"> ' +
    		'<img src="'+basePath+'/smp/images/background/ajax_loader.gif"/>' +
    		 '<div style="margin: 5px; font-size: 14px;">系统正在导入数据，请稍等...</div>        </div>'
    	 });  
    	 return true;    
    } 
    		    
     function showResponse(msg) {  
    	$.unblockUI();
    	if(msg.result == "ok") {
    		var supNumSuc = msg.supNumSuc;
    		var userNumSuc = msg.userNumSuc;
    		var supArrayFai = msg.supArrayFai.length;
    		var userArrayFai = msg.userArrayFai.length;
    		var h = '<font color="#3366FF" style="line-height: 24px;">一共导入商品：</font>'+supNumSuc+' 个，<font color="#3366FF" style="line-height: 24px;">成功导入商品：</font>'+userNumSuc+'个。<br/>';
    		h += '<font color="#808000" style="line-height: 24px;">未成功导入商品：</font>'+supArrayFai+' 个。';
    		if(supArrayFai > 0) {
    			h += '<a href="javascript:void(0);" onclick="displayOO(\'faOO\',this)">查看</a><br />';
    			h += '<span style="display:none;" id="faOO">';
    		$.each(msg.supArrayFai, function(i, n){
    				h += '<span style="padding-left: 20px; line-height: 20px;">'+n.message+' </span><br/>';
    		});
    		h += '</span>'
    		} else {
    			h += '<br />';
    		}
    		$("#resultTr").show();
    		$("#resultTd").html(h);
    		}else if(msg.result == "errmodle") {
    			$("#resultTr").show();
    			$("#resultTd").html('<font color="red">上传文件格式应为Excel文件，后缀名为【xls】！</font>');
    		}else if(msg.result == "fileModeErr") {
    			$("#resultTr").show();
    			$("#resultTd").html('<font color="red">'+msg.errInfo+'</font>');
    		}else if(msg.result == "readFileErr"){
    			alert("读取Excel出错!请检查所填数据格式是否合法再提交！");
    		}else if(msg.result == "noProduct"){
    			alert("本次无商品导入!请检查所填数据！");
    		}
    				
    }
    		    
    function displayOO(id, thi) {
    	$("#"+id).toggle();
    	if(thi.innerHTML=="查看"){
    		thi.innerHTML = "收起";
    	} else {
    		thi.innerHTML = "查看";
    	}
    }

三.后端关键代码

private File importFile;//上传文件
    private List<SmpProduct> smpProductlist;//参数
    /*
     * 保存Excel导入的商品信息
     */
    public String doImport(){
        JSONObject json = new JSONObject();
    	JSONArray supArrayFai = new JSONArray();
    	JSONArray userArrayFai = new JSONArray();
    	int userNumSuc=0;
    	try {
    	    smpProductlist = readProductExcel();
    	} catch (Exception e) {
    		System.out.println("-------->>读取Excel出错!");
    		e.printStackTrace();
    		json.put("result", "readFileErr");
    		ajaxResultText(json.toString());
    		return NONE;
    	}
    	if(smpProductlist.size()==0){
    		System.out.println("-------->>本次无商品导入!");
    		json.put("result", "noProduct");
    		ajaxResultText(json.toString());
    		return NONE;
    	}else{
    		//获取该用户一级商品类别
    		List<Map<String,Object>> commList = wechatService.getCommTypeByTypeNo(smpProductlist.get(0).getSupId(),1,"");
    		for(int i=0; i<smpProductlist.size(); i++){
    		    try {
    			    JSONObject jo = null;
    				String typeNo=smpProductlist.get(i).getTypeNo();
    				if(typeNo.equals("")){
    				    jo = new JSONObject();
    					jo.put("message", "第"+(i+2)+"行，商品类别编码不能为空！");
    					supArrayFai.put(jo);
    					continue;
    				}else{
    					if(typeNo.length()>50){
    					jo = new JSONObject();
    					jo.put("message", "第"+(i+2)+"行，商品类别编码不能大于50长度！");
    					supArrayFai.put(jo);
    					continue;
    				}else{
    					List<Map<String,Object>> childList = wechatService.getSmpProductTypeByFirstLevelAndTypeNo(commList, typeNo);
    					if(childList.size()==0){
    					    jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行，商品类别编码不正确！");
    						supArrayFai.put(jo);
    						continue;
    					}
    				}
    		    }
    		    String productName=smpProductlist.get(i).getProductName();
    		    if(productName.equals("")){
    		        jo = new JSONObject();
    			    jo.put("message", "第"+(i+2)+"行，商品名称不能为空！");
    			    supArrayFai.put(jo);
    			    continue;
    		    }else{
    			    if(productName.length()>200){
    			    jo = new JSONObject();
    			    jo.put("message", "第"+(i+2)+"行，商品名称不能大于200长度！");
    			    supArrayFai.put(jo);
    				continue;
    		    }
    		    }
    			String productAlias=smpProductlist.get(i).getProductAlias();
    			if(productAlias.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行，商品通用名称不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(productAlias.length()>200){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行，商品通用名称不能大于200长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String factory=smpProductlist.get(i).getFactory();
    					if(factory.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行，生产厂家不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(factory.length()>200){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行，生产厂家不能大于200长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String registerCardNo=smpProductlist.get(i).getRegisterCardNo();
    					if(registerCardNo.length()>100){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行， 注册证号不能大于100长度！");
    						supArrayFai.put(jo);
    						continue;
    					}
    					String productCertificate=smpProductlist.get(i).getProductCertificate();
    					if(productCertificate.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行，生产许可证不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(productCertificate.length()>100){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行，生产许可证不能大于100长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String brand=smpProductlist.get(i).getBrand();
    					if(brand.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行， 品牌不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(brand.length()>100){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行， 品牌不能大于100长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String specification=smpProductlist.get(i).getSpecification();
    					if(specification.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行， 规格不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(specification.length()>300){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行， 规格不能大于300长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String model=smpProductlist.get(i).getModel();
    					if(model.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行， 型号不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(model.length()>300){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行， 型号不能大于300长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String unit=smpProductlist.get(i).getUnit();
    					if(unit.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行， 单位不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(unit.length()>20){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行， 单位不能大于20长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					String pack=smpProductlist.get(i).getPack();
    					if(pack.equals("")){
    						jo = new JSONObject();
    						jo.put("message", "第"+(i+2)+"行， 包装不能为空！");
    						supArrayFai.put(jo);
    						continue;
    					}else{
    						if(pack.length()>100){
    							jo = new JSONObject();
    							jo.put("message", "第"+(i+2)+"行， 包装不能大于100长度！");
    							supArrayFai.put(jo);
    							continue;
    						}
    					}
    					jo=wechatService.addSmpProductByExcel(smpProductlist.get(i));
    					if(!jo.getBoolean("result")){
    						//商品未添加成功
    						userArrayFai.put(jo);
    					}else{
    						userNumSuc++;
    					}
    				} catch (Exception e) {
    					System.out.println("第"+(i+2)+"行["+smpProductlist.get(i).getProductName()+"]商品导入时系统出现异常！");
    					e.printStackTrace();
    					JSONObject jo = new JSONObject();
    					jo.put("message", "第"+(i+2)+"行["+smpProductlist.get(i).getProductName()+"]商品导入时系统出现异常！");
    					supArrayFai.put(jo);
    				}
    			}
    		}
    		json.put("supNumSuc", smpProductlist.size());
    		json.put("supArrayFai", supArrayFai);
    		json.put("userNumSuc", userNumSuc);
    		json.put("userArrayFai", userArrayFai);
    		json.put("result", "ok");
    		ajaxResultText(json.toString());
    		return NONE;
    	}
    
    /*
    	 * 读取上传的Excel信息
    	 */
    	private List<SmpProduct> readProductExcel() throws BiffException, IOException, JSONException{
    		JSONObject jo = new JSONObject();
    		//上传Excel
    		String fileName = (request.getParameter("upFile"));
    		if (!fileName.substring(fileName.lastIndexOf(".")).equalsIgnoreCase(".xls")) {
    			jo.put("result", "errmodle");
    			super.ajaxResultText(jo.toString());
    			return null;
    		}
    		String path =application.getRealPath("/smp/tmp")+ File.separator;
    		File uploadPath = new File(path);
    		if (!uploadPath.isDirectory())
    			uploadPath.mkdirs();
    		long time = new Date().getTime();
    		String fileSaveName = time + ".xls";
    		OutputStream bos = new FileOutputStream(uploadPath + File.separator + fileSaveName);
    		FileInputStream fileInputStream=new FileInputStream(this.getImportFile());
    		IOUtils.copy(fileInputStream, bos);
    		fileInputStream.close();
    		bos.close();
    		//获取当前用户
    		PreSupplier ps = supplierRegService.getRegisterInfoByUserCode(super.getUserCode());
    		//创建工作薄
    		InputStream is=new FileInputStream(new   File(path + fileSaveName));
    		Workbook rwb = Workbook.getWorkbook(is);
    		//获取第一张Sheet表 
    		Sheet rs=rwb.getSheet(0);
    		List<SmpProduct> list=new ArrayList<SmpProduct>();
    		int row=1;
    		//读取Excel信息
    		while(row<rs.getRows()){
    			SmpProduct sp=new SmpProduct();
    			sp.setSupId(ps.getPreSupId());
    			sp.setTypeNo(rs.getCell(0, row).getContents().trim());
    			sp.setProductName(rs.getCell(1, row).getContents().trim());
    			sp.setProductAlias(rs.getCell(2, row).getContents().trim());
    			sp.setStat(0);
    			sp.setFactory(rs.getCell(3, row).getContents().trim());
    			sp.setRegisterCardNo(rs.getCell(4, row).getContents().trim());
    			sp.setRegisterCardNoAttrPath("");
    			sp.setProductCertificate(rs.getCell(5, row).getContents().trim());
    			sp.setProductCertificateAttrPath("");
    			sp.setBrand(rs.getCell(6, row).getContents().trim());
    			sp.setSpecification(rs.getCell(7, row).getContents().trim());
    			sp.setModel(rs.getCell(8, row).getContents().trim());
    			sp.setUnit(rs.getCell(9, row).getContents().trim());
    			sp.setPack(rs.getCell(10, row).getContents().trim());
    			sp.setRemark("");
    			sp.setOptDate(new Date());
    			sp.setRegFrom(0);
    			sp.setPinYinCode("");
    			sp.setProduceArea("");
    			sp.setBarCode("");
    			list.add(sp);
    			row++;
    		}
    		 rwb.close(); 
    		return list;
    	}

[70]: /images/20220508/f131edff45ce4d97b7e7aad4841caef0.png