Excel导入功能的实现

业务分析：1.能够导入excel文件类型

                  2.读取该上传文件内容

                  3.内容展示在页面上，可修改，可勾选保存，保存时对内容进行校验。

代码实现：jsp前台页面

<form id="mainForm" action="list.action" method="post">
		<!--用户编辑 -->
		<div class="container dw_xd">
			<div class="radius_top">
				<span></span>
			</div>
			<div class="radius_mid zsy" id="content">
				<div class="pd0_30">
					<div class="edit_tit">
						<span class="drift_lt">导入三统一用户</span>
						<div class="xddw_0213 dq_r">
							<input
								onchange="document.getElementById('textfield').value=this.value"
								class="file_rt_0213" name="filePath" id="filePath" type="file" />
							<input id='textfield' name="" type="text" class="percent_w168" />
							<input class="btn_wt63" type="button"
								onclick="document.getElementById('logoImage').focus();"
								value="浏览" /> <input id="btn_sc" class="btn_wt63" type="button"
								value="上传" />
						</div>
					</div>
				</div>

				<div class="pd0_30" id="dr_account_info" style="display: none;">
					<script language="javascript"
						src="${ctx}/trust/js/plugins/myajax.js"></script>
					<table class="dr_ta">
						<tr>
                          <!-- 显示表头内容 -->      
							<th width="20"><input id="choseAll" type="checkbox" /></th>
							<th>序号</th>
							<th>账号</th>
							<th>登录名称</th>
							<th>人员姓名</th>
							<th>认证标识</th>
							<th>人员标识</th>
							<th>用户类型</th>
						</tr>
						<tfoot id="tab_account_info">
							<!-- 显示导入文件的内容 -->
						</tfoot>
					</table>
				</div>
			</div>
			<div class="btm">
				<span class="btm_rt"></span> <input name="" type="button"
					class="page_w68_glay page_f4 drift_rt" value="返回" id="history_skip" />
				<input id="save_import_resource" name="" type="button"
					class="page_w68 drift_rt" value="保存" />
				<div class="clean"></div>
			</div>
		</div>

	</form>

js   显示上传的文件内容   验证    保存

  $("#btn_sc").click(function() {
        if($("#filePath").val()=="" || $("#filePath").val()==null){
            jAlert("请选择上传文件");
            return;
        }
        if($("#filePath").val().indexOf(".raot")>1){
            uploadContainPasswordFile();
            $("#filePath").val("");
            $("#textfield").val("");
            return ;
        }
}

  function displayImportInfo(data){
      $("#tab_account_info").html("");
//    	alert(data);
    	console.log("----"+data)
        var index=0;
        var length = data.length;
//    	alert(length);
        //回显用户选择上传的数据
        for(var i=0;i<length;i++){
        		 var error="";
        		  var tr=" <tr trFirst='"+index+"_show' name='resourceData'><td name='index_num' align='center'  width='5%'><input type='checkbox' class='choice' flag='1'/></td>"+"<td align='center' name='user_count' width='5%'>"+(index+1)+"</td>";
        		  //不加密时
        		   if(data[i][0] != null) {
       		          tr=tr+" <td name='fort_user_account'><input class='addData' name='fortUser"+i+"' value='"+(data[i][0])+"'></input></td>";
     		      }
        		   if(data[i][1]!= null) {
     		          tr=tr+" <td name='fort_logon_name'><input class='addData' name='fortUser"+i+"' value='"+(data[i][1])+"'></input></td>";
     		      }
     		      if(data[i][2]!= null) {
     		          tr=tr+" <td id='fortUserName' name='fort_user_name'><input class='addData' name='fortUser"+i+"' value='"+(data[i][2])+"'></input></td>";
     		      }
     		      if(data[i][3]!= null) {
    		          tr=tr+" <td name='fort_guid'><input class='addData' id='fort_guid' name='fortUser"+i+"' value='"+(data[i][3])+"'></input></td>";
     		      }
     		      if(data[i][4]!= null) {
     		          tr=tr+" <td name='fort_user_guid'><input class='addData' name='fortUser"+i+"' value='"+(data[i][4])+"'></input></td>";
     		      }
						if(data[i][5]=="新发现"){
							tr=tr+"<td name='fort_user_type' align='center'><select name='fortUser"+i+"'>"+
							"<option value='0' selected='selected'>新发现</option>"+
							"<option value='1'>已导入</option>" +
							"<option value='2'>排除</option></select>";
						}else if(data[i][5]=="已导入"){
								tr=tr+"<td name='fort_user_type' align='center'><select name='fortUser"+i+"'>"+
								"<option value='0'>新发现</option>"+
								"<option value='1' selected='selected'>已导入</option>" +
								"<option value='2'>排除</option></select>";
						}else if(data[i][5]=="排除"){
								tr=tr+"<td name='fort_user_type' align='center'><select name='fortUser"+i+"'>"+
								"<option value='0'>新发现</option>"+
								"<option value='1' >已导入</option>" +
								"<option value='2' selected='selected'>排除</option></select>";
						}else{
							tr=tr+"<td name='fort_user_type' align='center'><select name='fortUser"+i+"'>"+
							"<option value='-1'selected='selected'>请选择</option>"+
							"<option value='0'>新发现</option>"+
							"<option value='1'>已导入</option>" +
							"<option value='2'>排除</option></select>";
						}
						tr+="</td>";
		     tr=tr+"</tr>";
          $("#tab_account_info").append(tr);
            
            if(data[i].errorInfo != null){
                var dataAccount = data[i].errorInfo;
                var error = "";
                for(var j=0; j<dataAccount.length; j++){
                    var accountInfo = dataAccount[j];
//                    if( !( data[i].fortParentIp == "" || data[i].fortParentIp == null ||  
//                    		typeof(data[i].fortParentIp) == undefined ) ){
//                    	
//                    	continue;
//                    }
                    if(endWith(accountInfo,"添加成功")){
                        error +="<span succ='succ' >"+dataAccount[j] +"</span></br>";  
                    }else{
                        error +="<span class='red' succ='fail' >"+dataAccount[j] +"</span></br>";  
                    }
                }
                
                tr=tr+"</tr>";
                $("#tab_account_info").append(tr);
                $("tr[trFirst='"+index+"_show']").css('background-color','#ECEDEF');
                $("#tab_account_info").append("<tr trSecond='"+index+"_show' ><td colspan='6' >导入警告信息：<br/>"+error+"</td></tr>");
            }     
             index++;
        }
        $("#dr_account_info").show();
//        $("tr[trSecond]").hide();
        $("tr[trSecond]").css('background-color','#F8F8F8');
        $("#tab_account_info").show();
}

//保存按钮
$("#save_import_resource").click(function() {	
    	save_ajax();
    });

//进行信息验证
function save_ajax(){
		var flag=true;
		indexs="";
		var reg = /[^\x00-\xff]/;
		$("input[class='choice']:checked").each(function(){
			var obj=$(this).parent();
			if(!validateUserAccount(obj.nextAll("td[name='fort_user_account']").find("input:eq(0)").val())){
				if(flag){
					flag=false;
					return false;
				}
			}
			if( !validateUserName(obj.nextAll("td[name='fort_logon_name']").find("input:eq(0)").val())){
				if(flag){
					flag=false;
					return false;
				}
			}
			if( (obj.nextAll("td[name='fort_user_name']").find("input:eq(0)").val()) == "" ||(obj.nextAll("td[name='fort_user_name']").find("input:eq(0)").val()) == undefined){
				if(flag){
					jAlert("姓名不能为空");
					flag=false;
				}
			}

			indexs+=obj.next("td[name='user_count']").html()+",";
		});
		indexs=indexs.substring(0,indexs.length-1);
//		alert(indexs+"indexs");
		if(indexs==""&&flag){
			jAlert("请选择要保存的用户");
			return;
		}
		if(flag){
			$().showOverlay();

			//保存方法
			save_ajax_impl("user_import_form","/excel/three/userImportExport",indexs);
		}
	}

/*
	*批量处理，提交表单中的多个用户数据
	*formId   表单名称
	*requestName 请求名称
	*indexs  参数
	*/
function save_ajax_impl(formId,requestName,indexs){
	var data =  $("#"+formId).serialize();
	var data1=getData();
	$.ajax({
		cache : false,
		type : 'POST',
		url : window.top.ctx+requestName+'/save_user'+'?indexs='+indexs+'&version=normal',
		dataType : 'text',
		data:data1,
		async : true, 
		success : function(data) {
			var dataObj = eval("(" + data + ")");
			var index=dataObj.index;//改变属性
			var indextemp=dataObj.indextemp;//去除checkbox
			if(dataObj.message!=undefined){
				$().hideOverlay();
				uncheckbox(indexs,indextemp);
				jAlert(dataObj.message,'提示');
				return;
			}
			if(dataObj.mess!=undefined){
				//找出已经添加好的用户，将多选框去除
				if(index!=undefined){
					uncheckbox(indexs,indextemp);
					changecheck(indexs,index);
					//改变
//					if(getData()!=null ||getData()!=""){
					indexs="";
					$("input[falg='0']").each(function(){
						var obj=$(this).parent();
						indexs+=obj.next("td[name='user_count']").html()+",";
					});
					indexs=indexs.substring(0,indexs.length-1);
//					save_ajax();
					if(indexs!=""){
					save_ajax_impl(formId,requestName,indexs);
//					alert(indexs);
					return;
				}

				}
				var str="";
				var mess=dataObj.mess.split("\!");
				for(i=0;i<mess.length;i++){
					str+=mess[i]+"<br>";
				}
				jAlert(str,'提示');
			}
			else{
				uncheckbox(indexs,index);
				changecheck(indexs,index);
//				if(getData()!=null ||getData()!=""){
					indexs="";
					$("input[falg='0']").each(function(){
						var obj=$(this).parent();
						indexs+=obj.next("td[name='user_count']").html()+",";
						
					});
					indexs=indexs.substring(0,indexs.length-1);
//					save_ajax();
					if(indexs!=""){
					save_ajax_impl(formId,requestName,indexs);
					return;
				}
				jAlert('批量保存成功！', '','',function(){
					checkIfAllImported();});
			}
			$().hideOverlay();
		},
		error : function() {
			jAlert("批量保存失败！",'警告',"warn");
		}
	});
}

//传到后台需要保存的内容 
function getData(){
	var data1="";
	$("input[class='choice']:checked").each(function(){
		var eq03 = $(this).parent().parent().children('td').eq(3).find("input").val();
		var eq04 = $(this).parent().parent().children('td').eq(4).find("input").val();
		var eq05 = $(this).parent().parent().children('td').eq(5).find("input").val();
		var eq06 = $(this).parent().parent().children('td').eq(6).find("input").val();

		var eq3 = Base64.encode(eq03);
		var eq4 = Base64.encode(eq04);
		var eq5 = Base64.encode(eq05);
		var eq6 = Base64.encode(eq06);
		
		eq3 = $(this).parent().parent().children('td').eq(3).find("input").val(eq3);
		eq4 = $(this).parent().parent().children('td').eq(4).find("input").val(eq4);
		eq5 = $(this).parent().parent().children('td').eq(5).find("input").val(eq5);
		eq6 = $(this).parent().parent().children('td').eq(6).find("input").val(eq6);
		
		data1=data1+$(this).parent().parent().children('td').eq(2).find("input").serialize()+"&";
		data1=data1+decodeURIComponent($(this).parent().parent().children('td').eq(3).find("input").serialize(),true)+"&";
		data1=data1+decodeURIComponent($(this).parent().parent().children('td').eq(4).find("input").serialize(),true)+"&";
		data1=data1+decodeURIComponent($(this).parent().parent().children('td').eq(5).find("input").serialize(),true)+"&";
		data1=data1+decodeURIComponent($(this).parent().parent().children('td').eq(6).find("input").serialize(),true)+"&";
		data1=data1+$(this).parent().parent().children('td').eq(7).find("select").serialize()+"&";//登录修改密码
		
		$(this).parent().parent().children('td').eq(3).find("input").val(eq03);
		$(this).parent().parent().children('td').eq(4).find("input").val(eq04);
		$(this).parent().parent().children('td').eq(5).find("input").val(eq05);
		$(this).parent().parent().children('td').eq(6).find("input").val(eq06);

	});
	return data1;
}

Contrller  读取上传的文件内容返回前台处理

//接收上传的三统一用户文件
	@RequestMapping(value = "/upload_account_password")
	@ResponseBody
	public void uploadAccountPassword(@RequestParam(value = "filePath") MultipartFile file,HttpServletResponse response) {
		log.info("接收上传的文件");
		response.setHeader("Content-type", "text/html");
		PrintWriter out = null;
		JSONObject json = new JSONObject();
		String tempDirPath = makeRestorPath();
		try {
			out = response.getWriter();
			String fileName = file.getOriginalFilename();
			if (!fileName.trim().toLowerCase().endsWith(".xls") && !fileName.trim().toLowerCase().endsWith(".xlsx")) {
				json.put("datas", "");
				return;
			}

			String uploadFilePath = tempDirPath + fileName;
			log.info("uploadFilePath------------------"+uploadFilePath);

			// 文件类型正确，在指定路径下创建文件
			File targetFile = new File(uploadFilePath);
			FileUtil.createFile(targetFile);

			try {

				// 复制传输的文件到指定文件
				file.transferTo(targetFile);

				List<List<String>> data = readExcel(targetFile);
				json.put("datas", data);
				for (List<String> list : data) {
						log.info("list------------------"+list);
						
				}		

			} catch (Exception e) {
				json.put("datas", "");
				e.printStackTrace();
			}

		} catch (IOException e) {

			e.printStackTrace();
		} catch (JSONException e) {

			e.printStackTrace();
		} finally {
			try {
				out.println(json);
				out.flush();
				FileUtil.delete(tempDirPath);
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

/**
     * 获取数据
     * @param file
     * @return
     * @throws Exception
     */
    private static List<List<String>> readExcel(File file) throws Exception {

        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(file.getAbsolutePath());
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(is);
        // 只有一个sheet,直接处理
        //创建一个Sheet对象
        Sheet sheet = wb.getSheet(0);
        // 得到所有的行数
        int rows = sheet.getRows();
        // 所有的数据
        List<List<String>> allData = new ArrayList<List<String>>();
        // 越过第一行 它是列名称
        for (int j = 1; j < rows; j++) {

            List<String> oneData = new ArrayList<String>();
            // 得到每一行的单元格的数据
            Cell[] cells = sheet.getRow(j);
            for (int k = 0; k < cells.length; k++) {

                oneData.add(cells[k].getContents().trim());
            }
            // 存储每一条数据
            allData.add(oneData);
            // 打印出每一条数据
            log.info(allData+"allData============");
            //System.out.println(oneData);

        }
        return allData;

    }

private String makeRestorPath() {
		String restorePath = getProgramPath("upload") + File.separator +         
        ParseUtil.dateToString10(new Date())
				+ File.separator;
		return restorePath;
	}
	

/**
	 * 批量存储多个用户
	 * @throws Throwable 
	 */
	@RequestMapping(value = "/save_user")
	@ResponseBody
	public Object saveUser() throws Throwable {
		List<String> indexArrays = new ArrayList<String>();
		List<String> indexArrayTemp=new ArrayList<String>();
		try {
		  log.info("批量添加用户");
		  String[] indexArray = getParameter("indexs").split(",");
		  System.out.println(indexArray+"");
		  FortSystemLogFactory.getInnerHandleSystemLog().getSystemLogType("用户","批量导入用户", true);
		  Thread.sleep(1000);
		
			  for (int i = 0; i<indexArray.length; i++) {
				  log.info(indexArray[i]+"===========================indexArray[i]");
				  String[] obj = getParameterValues("fortUser" + (Integer.parseInt(indexArray[i])-1));
//				  BASE64Decoder decoder = new BASE64Decoder();
//		  
				  log.info(indexArray.length+"===========================length");
				
//					String fortLogonName = new String(decoder.decodeBuffer(obj[1]));
				  if(obj.length!=0){
				  //调用验证批量用户的方法
				  if (validate_import(obj)) {
					  indexArrays.add(indexArray[i]);
					  continue;
				  }
				  FortThreeUniformUser01 fortThreeUniformUser = createFortUser(obj);
//				  this.fortUserService.saveEntity(fortThreeUniformUser);
				  //添加用户
				int num = this.fortThreeUniformUserService01.saveFortThreeUniformUser(fortThreeUniformUser);
//				log.info(num+"===========================num");
				  indexArrayTemp.add(indexArray[i]);
				  indexArrays.add(indexArray[i]);
			  }
			  }	  
			  //删除服务器端临时文件d://poi.xls
			  File file = new File(getProgramPath("upload", FileImportUtil.EXCEL_FULL_NAME));
			  if (file.exists()) {
				  file.delete();
			  }
		} catch (Exception e) {
			result = Constant.AJAX_RESULT_FAILURE;
			log.error(e.getMessage(), e);
		} finally {
			resultData.put("indextemp", indexArrayTemp);
			resultData.put("index", indexArrays);
			resultData.put(Constant.AJAX_RESULT_DEFAULT_MAP_KEY, result);
		}
		return resultData;
	}


/**
	 * 验证批量添加的用户
	 * @param obj
	 * @return
	 * @throws Throwable
	 */
	private boolean validate_import(String[] obj) throws Throwable {
		Example example = new Example();
		boolean flag;
		String mess = "";
		// 判断账号、口令及用户组是否为空
		 BASE64Decoder decoder = new BASE64Decoder();
			log.info(obj[0]+"flag");
		obj[1] = new String(decoder.decodeBuffer(obj[1]));
		obj[2] = new String(decoder.decodeBuffer(obj[2]));
		log.info(obj[0]+obj[1]+obj[2]);
		if ("".equals(obj[0]) || "".equals(obj[1]) || "".equals(obj[2])){
			mess += "请输入账号、名称、姓名!";
		}
		
		example.createCriteria().andFieldEqualTo("fort_user_account",obj[0]);
//		flag = this.fortUserService.hasEntityByExample(example);
		flag = this.fortThreeUniformUserService01.hasEntityByExample(example);
		log.info(flag+"flag");
		// 判断账号是否重复
		if (flag) {
			mess += "部分用户账号已存在!";
		}
		if (!"".equals(mess)) {
			resultData.put("mess", mess);
			return true;
		}
		return false;
	}

/**
	 * 批量添加用户时，设置用户对象
	 * @param obj
	 * @return
	 * @throws Exception 
	 */
	private FortThreeUniformUser01 createFortUser(String[] obj) throws Exception {
		log.info("obj=========="+obj[0]+","+obj[1]+","+obj[2]+","+obj[3]+","+obj[4]+","+obj[5]);
		FortThreeUniformUser01 fortThreeUniformUser = new FortThreeUniformUser01();
		BASE64Decoder decoder = new BASE64Decoder();
		fortThreeUniformUser.setFortThreeUniformUserId(UniqId.getId());
		fortThreeUniformUser.setFortUserAccount(obj[0]);
		fortThreeUniformUser.setFortLogonName(obj[1]);
		fortThreeUniformUser.setFortUserName(new String(obj[2]));
		fortThreeUniformUser.setFortGuid(new String(decoder.decodeBuffer(obj[3])));
		fortThreeUniformUser.setFortUserGuid(new String(decoder.decodeBuffer(obj[4])));
		if(obj[5].equals("-1")){
			 fortThreeUniformUser.setFortUserType(null);
			log.info(fortThreeUniformUser.getFortUserType()+"FortUserType01");
		 }else {
			 fortThreeUniformUser.setFortUserType(obj[5]);
			 log.info(fortThreeUniformUser.getFortUserType()+"FortUserType");
		 }
		
		if(fortThreeUniformUser.getFortCreateDate() == "" || fortThreeUniformUser.getFortCreateDate() == null ) {
			fortThreeUniformUser.setFortCreateDate(getCurrentDate());
		}
		
		return fortThreeUniformUser;
	}