利用类的反射导入excel示例
直接上代码
1、页面展示
<div class="panel-heading font-bold text-center"> <i class="fa fa-edit"></i><strong>用户导入功能</strong> </div> <div class="panel-body"> <form action="#" class="form-horizontal" method="post"> <div class="form-group" style="display:none;"> <label class="col-sm-2 control-label"> 会议Id <span class="text-danger">*</span> </label> <div class="col-sm-8"> <input type="text" class="form-control w-300 iblock" id="conId" name="conId" value="<%=conId %>" datatype="*" nullmsg="请填写会议Id"> </div> </div> <div class="form-group" style="display:none;"> <label class="col-sm-2 control-label"> 会议简称 <span class="text-danger">*</span> </label> <div class="col-sm-8"> <input type="text" class="form-control w-300 iblock" id="fromWhere" name="fromWhere" value="<%=fromWhere %>" datatype="*" nullmsg="请填写会议简称"> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label"> 注册类型 <span class="text-danger">*</span> </label> <div class="col-sm-8"> <select id="seanceId" class="form-control"> <option value="-1">请选择注册类型</option> </select> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label"> 团队名称（用于导入团队） <span class="text-danger"></span> </label> <div class="col-sm-8"> <input type="text" class="form-control w-300 iblock" id="teamName" name="teamName" datatype="*" nullmsg="请填写会议简称"> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label"> 上传Excel文件 <span class="text-danger">*</span> </label> <div class="col-sm-8"> <input type="file" class="form-control w-300 iblock" id="importFile" style="display:none"/> <button class="btn btn-lg btn-primary" id="selectFile" type="button">选择文件</button> </div> </div> <div class="form-group"> <label class="col-sm-2 control-label"> <span class="text-danger"></span> </label> <div id="daoru" class="col-sm-8 text-center"> <button class="btn btn-lg btn-primary" type="button" id="importBtn">导入</button> </div> </div> </form> </div>
2、js数据提交
$("#importBtn").click(function(){ if($("#conId").val() == ""){ alert("请填写会议Id"); return; } if($("#fromWhere").val() == ""){ alert("请填写会议的简称"); return; } if($("#importFile").val() == ''){ alert("请选择上传的文件") return; } if($("#seanceId").val() == -1){ alert("请选择注册类型"); return; }else{$("#daoru").html("导入中...");} setTimeout(function(){ var formData = new FormData(); var teamName = encodeURIComponent($("#teamName").val()); var conId = $("#conId").val(); formData.append("importFile", $("#importFile")[0].files[0]); formData.append("fromWhere", $("#fromWhere").val()); formData.append("teamName",teamName); formData.append("conId",conId); formData.append("seanceId",$("#seanceId").val()); $.ajax({ url: "/import.do?importUser", type: 'POST', data: formData, dataType:"json", // 告诉jQuery不要去处理发送的数据 processData: false, // 告诉jQuery不要去设置Content-Type请求头 contentType: false, cache:false, ifModified:true, async:false, beforeSend: function () { console.log("正在进行，请稍候"); }, success: function (json) { if (json.state == 1) { alert("导入完成"); window.location.reload(); //$("#selectFile").html("选择文件"); } else { alert(json.msg); return; } }, error: function (json) { console.log("error"); } }); },100) })
3、后台数据处理
@RequestMapping(params = "importUser",method = RequestMethod.POST) public void importUser(Integer conId,Integer seanceId,HttpServletRequest request,HttpServletResponse response){ try { MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) request; MultipartFile mFile = mRequest.getFile("importFile"); String fromWhere = request.getParameter("fromWhere"); String teamName = request.getParameter("teamName"); teamName = URLDecoder.decode(teamName,"UTF-8"); UserInfo teamUser = null; if(teamName.length() > 0){//要创建团队 System.out.println(teamName); teamUser = userinfoService.getTeamUser(fromWhere,teamName); if(teamUser == null){ teamUser = new UserInfo(); teamUser.setTrueName(teamName); teamUser.setDanwei(teamName); teamUser.setHospitalName(teamName); teamUser.setIsTeam(1); teamUser.setIsTeamLeader(1); teamUser.setFromWhere(fromWhere); teamUser.setConferencesId(conId); userinfoService.saveOrUpdate(teamUser); teamUser.setTeamId(teamUser.getUserInfoId()); userinfoService.saveOrUpdate(teamUser); } } InputStream is = mFile.getInputStream(); if(is!=null) { Workbook rwb = WorkbookFactory.create(is); int rowCount = 0; Sheet st = rwb.getSheetAt(0); Iterator<?> it= st.rowIterator(); String [] tableHead = null; for(; it !=null && it.hasNext();){ Row row = (Row)it.next(); if(row==null) { it = null; continue; } if(row.getCell(0)==null || row.getCell(0).getStringCellValue() == null ||row.getCell(0).getStringCellValue().length() == 0) { it = null; continue; } if(rowCount == 0){ int columnNum = row.getPhysicalNumberOfCells(); tableHead = new String[columnNum]; for(int i = 0; i< tableHead.length;i++){ Cell cell = row.getCell(i); String headStr = cell.getStringCellValue(); tableHead[i] = headStr; } }else{ UserInfo userInfo = new UserInfo(); userInfo.setFromWhere(fromWhere); for(int j = 0;j < tableHead.length;j++){ Cell cell = row.getCell(j); String value = ""; if(cell == null || cell.equals("") || cell.getCellType() ==HSSFCell.CELL_TYPE_BLANK){ }else{ value = getCellValue(cell); } if (value!="") { String tablePropertyName = tableHead[j].substring(3,tableHead[j].length()); tablePropertyName = tablePropertyName.substring(0, 1).toLowerCase()+tablePropertyName.substring(1,tablePropertyName.length()); String type = userInfo.getClass().getDeclaredField(tablePropertyName).getGenericType().toString(); if(type.equals("class java.lang.String")){ Method method = userInfo.getClass().getMethod(tableHead[j],new Class[] { String.class }); method.invoke(userInfo,new Object[]{new String(value)}); } if(type.equals("class java.lang.Integer")){ value = value==""?"0":value; Method method = userInfo.getClass().getMethod(tableHead[j],new Class[] { Integer.class }); method.invoke(userInfo,new Object[]{new Integer(value)}); } } } /*if(teamName.length() == 0){//个人导入 UserInfo saveUserInfo = userinfoService.getUserInfoByNameAndMobile(fromWhere,userInfo.getTrueName(),userInfo.getMobilePhone()); if(saveUserInfo == null){ saveUserInfo = userinfoService.getUserINfoByNameAndEmail(fromWhere,userInfo.getTrueName(),userInfo.getEmail()); if(saveUserInfo == null){ Hospital hospital = mainService.getProvinceByName(userInfo.getProvince()); if(hospital != null){ userInfo.setProvince(String.valueOf(hospital.getProvinceId())); userInfo.setCity(String.valueOf(hospital.getCityId())); } userInfo.setConferencesId(conId); userinfoService.saveOrUpdate(userInfo); } } }else if(teamName.length() > 0){//团队导入 UserInfo saveUserInfo = userinfoService.getUserInfoByName(fromWhere,userInfo.getTrueName()); Hospital province = mainService.getProvinceByName(userInfo.getProvinceName()); if(province != null){ userInfo.setProvince(String.valueOf(province.getHospitalId())); } Hospital city = mainService.getCityByName(userInfo.getCityName()); if(city != null){ userInfo.setCity(String.valueOf(city.getHospitalId())); } if(saveUserInfo != null && saveUserInfo.getIsVip() != null && saveUserInfo.getIsVip() == 1){ userInfo.setIsVip(-1); } userInfo.setTeamId(teamUser.getUserInfoId()); userInfo.setIsTeam(1); userInfo.setIsTeamMember(1); userInfo.setConferencesId(conId); userinfoService.saveOrUpdate(userInfo); }*/ if(userInfo != null){ if(userInfo.getProvinceName() != null && userInfo.getProvinceName().length() >0){ String provinceName = userInfo.getProvinceName().replace("自治区", "").replace("省", "").replace("市", ""); Hospital province = mainService.getProvinceByLikeName(provinceName); if(province != null){ userInfo.setProvince(String.valueOf(province.getHospitalId())); } } if(userInfo.getCityName() != null && userInfo.getCityName().length() >0){ String cityName = userInfo.getCityName().replace("市", ""); Hospital city = mainService.getCityByLikeName(cityName); if(city != null){ userInfo.setCity(String.valueOf(city.getHospitalId())); } } } if(teamName.length() > 0){//团队导入 UserInfo saveUserInfo = userinfoService.getUserInfoByName(fromWhere,userInfo.getTrueName()); if(saveUserInfo != null && saveUserInfo.getIsVip() != null && saveUserInfo.getIsVip() == 1){ userInfo.setIsVip(-1); } userInfo.setTeamId(teamUser.getUserInfoId()); userInfo.setIsTeam(1); userInfo.setIsTeamMember(1); } userInfo.setConferencesId(conId); userinfoService.saveOrUpdate(userInfo); /*注册类型*/ if(seanceId != null && seanceId + 1 !=0){ ChcRegUser chcRegUser = webService.getChcRegUser(userInfo.getUserInfoId()); if(chcRegUser == null){ chcRegUser = new ChcRegUser(); chcRegUser.setCreateTime(new Date()); } chcRegUser.setPaymentMethod(1); chcRegUser.setConferencesId(Integer.valueOf(conId)); Seance seance = webService.getSeanceById(seanceId); chcRegUser.setPay(seance.getPrice().intValue()); //应缴费 chcRegUser.setHasPay(0); //已缴费 chcRegUser.setQianPay(seance.getPrice().intValue()); //欠费 chcRegUser.setSeanceId(seanceId); chcRegUser.setSeance(seance.getSeanceContent()); chcRegUser.setEnSeance(seance.getEnSeanceContent()==null?"":seance.getEnSeanceContent()); chcRegUser.setUserId(userInfo.getUserInfoId()); chcRegUser.setStateType(0); chcRegUser.setPayType(2); webService.saveObject(chcRegUser); userInfo.setJobNumber(chcRegUser.getChcRegUserId()); userInfo.setPayState(0); webService.saveObject(userInfo); } } rowCount++; System.out.println(rowCount); } } writeJson(response, new JSONObject().accumulate("state", 1).toString()); } catch (Exception e) { e.printStackTrace(); } }
