页面中做到：导入excel信息至数据表中。
页面：
代码：
<div id="dd" class="easyui-dialog" style="width: 400px; height: 120px;" data-options="iconCls:'icon-save',resizable:true,modal:true,closed:true"> <img src="${pageContext.request.contextPath}/images/expand.gif" /> <a href="${pageContext.request.contextPath}/admin/templates/importEmployee.xls" target="_blank">下载员工导入模板</a> <br> <form id="form" method="post" enctype="multipart/form-data"> <span class='ftitle'>员工导入文件：</span> <input type="file" class="uploadFile" name="upload" id="upload" /> <input type="hidden" class="id" name="id" id="id" /> </form> </div>
后台处理：
Controller中：
/** * 导入员工 * * @return */ @SuppressWarnings("unchecked") @RequestMapping(params = { "method=upload" }) @ResponseBody public Map<String, Object> upload(HttpServletRequest request, @RequestParam(value = "upload", required = false) MultipartFile upload, String id) { Map<String, Object> rsMap = new HashMap<String, Object>(); try { LoginUserVo user = getLoginUserVo(request); if (user == null) { rsMap.put("success", "false"); rsMap.put("msg", "未查询到当前用户信息"); return rsMap; } String code = ""; String name = ""; Map<String, Object> mp = cspEmployeesBO.queryUsersBranchCfg(user.getId() == null ? "" : user.getId());// 根据用户id查询用户所处分公司编码和分公司名称 if (mp != null) { code = mp.get("code").toString(); name = mp.get("name").toString(); } else { rsMap.put("success", "false"); rsMap.put("msg", "未查询到当前用户所属分公司"); return rsMap; } System.out.println("开始导入========>" + DateUtils.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss")); System.out.println("开始导入SqlServer========>" + DateUtils.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss")); Map<String, Object> map = cspEmployeesBO.saveEmployeesFile(upload, name, code, user.getUserAlias()); System.out.println("结束导入SqlServer========>" + DateUtils.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss")); if (StringUtils.equals("true", map.get("success").toString())) { List<CspEmployees> ems = (List<CspEmployees>) map.get("data"); System.out.println("开始导入Sysbase========>" + DateUtils.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss")); accountKfBO.saveEmployees(ems); System.out.println("结束导入Sysbase========>" + DateUtils.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss")); } rsMap.put("success", map.get("success")); rsMap.put("msg", map.get("msg")); System.out.println("结束导入========>" + DateUtils.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss")); } catch (Exception e) { rsMap.put("success", "false"); rsMap.put("msg", "导入失败,请检查文件"); logger.error("error", e); } return rsMap; }
BOImpl中：
@Override public Map<String, Object> saveEmployeesFile(MultipartFile importFile, String cityName, String cityCode, String userAlias) throws Exception { Map<String, Object> map = new HashMap<String, Object>(); try { Date nowTime = new Date(); CommonsMultipartFile cf = (CommonsMultipartFile) importFile; DiskFileItem fi = (DiskFileItem) cf.getFileItem(); File file = fi.getStoreLocation(); int sheetNum = ExcelHelper.getSheetNum(file, importFile.getOriginalFilename()); List<String> exclist = new ArrayList<String>(); for (int i = 0; i < sheetNum; i++) { List<String> sheetList = ExcelHelper.exportListFromExcel(file, i, importFile.getOriginalFilename()); for (String s : sheetList) { exclist.add(s); } } List<CspEmployees> employeesList = new ArrayList<CspEmployees>(); CspEmployees employees = null; Map<String, Object> mobileMap = new HashMap<String, Object>(); // List<CspEmployees> cspEmployeesList = new ArrayList<CspEmployees>(); // cspEmployeesList = cspEmployeesJDBCDAO.queryAllCspEmployees(); // 已存在的员工数据 // Map<String, Object> cspEmployeesMap = new HashMap<String, Object>(); // for (CspEmployees cspEmployees : cspEmployeesList) { // // cspEmployeesMap.put(cspEmployees.getMobile(), cspEmployees.getRealName()); // } for (int i = 0; i < exclist.size(); i++) { String[] codes = exclist.get(i).split("\\|"); int a = i + 2; if (codes.length >= 5) { EncryptionAndDecryptUtil encryptionAndDecryptUtil = new EncryptionAndDecryptUtil(); if (StringUtils.isBlank(codes[1])) { map.put("success", "false"); map.put("msg", "第" + a + "行数据为空"); return map; } if (codes[1].trim().length() != 11) { map.put("success", "false"); map.put("msg", "第" + a + "行手机号格式错误，请检查手机号长度或者数据类型"); return map; } if (mobileMap.containsKey(codes[1].trim())) { map.put("success", "false"); map.put("msg", "手机号" + codes[1] + "重复"); return map; } // if (cspEmployeesMap.containsKey(codes[1].trim())) { // map.put("success", "false"); // map.put("msg", "手机号" + codes[1] + "与现有员工" + cspEmployeesMap.get(codes[1].trim()) + "重复"); // return map; // } // 判断编码是否正确 queryBranchInfoByNodeCode(String node_code) String city_temp_code = cspEmployeesJDBCDAO.queryBranchInfoByNodeCode(codes[3].trim()); if (StringUtils.isNotBlank(city_temp_code)) { if(!city_temp_code.equals(cityCode)) { map.put("success", "false"); map.put("msg", "第" + a + "行 网点号" + codes[3].trim() + "和用户所属分公司不匹配！"); return map; } } else { map.put("success", "false"); map.put("msg", "第" + a + "行 网点号" + codes[3].trim() + "不正确"); return map; } employees = new CspEmployees(); employees.setMobile(codes[1].trim()); mobileMap.put(codes[1].trim(), ""); employees.setMobileMi(encryptionAndDecryptUtil.getMi(codes[1].trim())); employees.setRealName(codes[2].trim()); employees.setNodeCode(codes[3].trim()); employees.setNodeName(codes[4].trim()); employees.setCityName(cityName); employees.setCityCode(cityCode); employees.setCreateTime(nowTime); employees.setIsOn("0");// 0 ：在职 1：离职 employees.setUpdateTime(nowTime); employees.setCreateUser(userAlias); employees.setUpdateUser(userAlias); employees.setOutTime(DateUtils.stringToDate("2100-01-01 0:00:00", DateUtils.getTimePattern())); employeesList.add(employees); } else { map.put("success", "false"); map.put("msg", "Excel数据错误"); return map; } } List<CspEmployees> list = new ArrayList<CspEmployees>(); CspEmployees param = new CspEmployees(); param.setCityCode(cityCode); list = cspEmployeesJDBCDAO.queryAllCspEmployees(param); // 已存在的员工数据 for (int a = 0; a < list.size(); a++) { if (!mobileMap.containsKey(list.get(a).getMobile())) { CspEmployees vo = new CspEmployees(); CspEmployees employee = list.get(a); employee.setOutTime(new Date()); employee.setIsOn("1"); //excel表中没有的数据设置为离职 employee.setCreateTime(new Date()); vo = employee; employeesList.add(vo); employee = null; } } cspEmployeesDAO.saveOrUpdateCspEmployees(employeesList); map.put("success", "true"); map.put("msg", "Excel导入成功,共导入" + employeesList.size() + "条数据"); map.put("data", employeesList); } catch (Exception e) { map.put("success", "false"); map.put("msg", "处理数据失败,请检查文件"); logger.error("error", e); } return map; }
