poi导入导出工具类及调用方法

/** * Excel导入调用方法 */ public String readExcel(){ try { InputStream in = new FileInputStream(excelFile); List<TeacherView> lists=null; final Map<String,String> telMap=new HashMap<String, String>(); //in是一个输入流，3表示从Excel的第几列开始读取数据，后面的是个回调函数 //此导入支持多个sheet，以及下拉框 //resultMap是里面包含了一个list表示验证正确的对泛型集合对象，errorFileName，如果Excel中的数据在在验证的时候出现问题，则会将错误信息写入到导入的 //Excel中,errorFileName为返回是Excel导入错误信息的Excel地址 Map<String, Object> resultMap=LoadXLS.load(in,3,new LoadXLSCallBack<TeacherView>(){ @Override //读取的数据保存到对应的对象属性中 public TeacherView getObject(List<String> list) { //依次从list中读取值，进行验证 TeacherView tv=new TeacherView(); String teacherName=list.get(1).trim();//老师姓名 String mobile=list.get(2).trim();//电话 String roleName=list.get(3).trim();//角色名称 String sex=list.get(4).trim();//性别 String address=list.get(5); if(address!=null && address!=""){ address=address.trim(); } String classInfo=list.get(6); if(classInfo!=null && classInfo!=""){ classInfo=classInfo.trim(); } String errorMsg=""; if(teacherName.length()<1){ errorMsg+="老师名称不能为空,"; }else{ if(telMap.get(teacherName)==null){ telMap.put(teacherName, teacherName); Map<String,Object> map=new HashMap<String, Object>(); map.put("teacherName", teacherName); int teacherrow=teacherService.teacherNameIsExist(map); if(teacherrow==-1){ errorMsg+="老师姓名已存在,"; }else{ tv.setTeacherName(teacherName); } }else{ errorMsg+="导入数据中老师姓名已存在,"; } } if(mobile.length()<1){ errorMsg+="电话不能为空,"; }else{ //获取电话号码 判断电话号码是否合法 Pattern p = Pattern.compile("^((13[0-9])|(15[^4,\\D])|(18[0,5-9]))\\d{8}$"); Matcher m = p.matcher(mobile); if(!m.matches()){ errorMsg+="电话号码格式错误,"; } } if(roleName.length()<1){ errorMsg+="角色名称不能为空,"; }else{ int roleID = teacherService.getRoleIdByName(roleName); if (roleID == 0) { errorMsg+="角色名称错误,"; }else{ tv.setRoleId(roleID+""); } } if(sex.length()<1){ errorMsg+="性别不能为空,"; }else{ //获取性别 判断性别是否合法 if(!("男".equals(sex) || "女".equals(sex))){ errorMsg+="性别错误,"; } } //判断班级是否存在 String classIds=""; if(!classInfo.isEmpty()){ //String className=teacherView int index = classInfo.replace("，", ",").indexOf(","); // 如果在导入的Excel中一个老师管辖多个班级，“三(1),三(2)”这样的情况，对班级进行分割判断，然后添加 if (index > 0) { String[] str =classInfo.replace("，", ",").split(","); for (int j = 0; j < str.length; j++) { Map<String,Object> classMap=new HashMap<String, Object>(); String className=str[j]; System.out.println(className); className=className.replace("（", "("); className=className.replace("）", ")"); classMap.put("className", className); classMap.put("schId", schID); // 根据班级名称和学校ID,去查找班级信息 try { ClassBaseInfo cbs = classBaseService.selectClassBaseByName(classMap); if(cbs==null){ errorMsg+="班级名称错误,"; }else{ if(cbs.getId()!=null){ classIds+=cbs.getId()+","; } } } catch (Exception e) { } } } // 当一个老师只管理一个班级的时候 if (index == -1) { try { Map<String,Object> classMap=new HashMap<String, Object>(); classMap.put("className",classInfo); classMap.put("schId",schID); //查找班级的名称是否存在 ClassBaseInfo cbs = classBaseService.selectClassBaseByName(classMap); if (cbs== null) { errorMsg+="班级名称错误,"; }else{ if(cbs.getId()!=null){ classIds+=cbs.getId()+","; } } } catch (Exception e) { } } } //去除最后， if(classIds!="" && classIds!=null){ classIds=classIds.substring(0,classIds.length()-1); } //数据库老师名存在性判断 //去除最后， if(errorMsg!="" && errorMsg!=null){ errorMsg=errorMsg.substring(0,errorMsg.length()-1); } tv.setMobile(mobile); tv.setRoleName(roleName); tv.setSex(sex); tv.setAddress(address); tv.setOwnerClass(classInfo); tv.setSchId(Integer.parseInt(schID)); tv.setErrMsg(errorMsg);//在实体中需要添加一个errMsg属性，保存错误信息，必须存在，否则就会认为数据是正确的，封装的类中是根据此 //判断该条数据是否正确的 tv.setClassIds(classIds); return tv; } @Override public int getColumnSize() { return 7; } } ); lists= (List<TeacherView>)resultMap.get("list");//取出集合，集合中就是Excel中的数据，此集合就可以直接传入到service中，进行业务逻辑操作，然后插库 if(lists.size()>0){ if(lists.get(0).getMobile().matches("^\\d{2,13}$")){ Map<String,Object> map=teacherService.readExcelToBatch(lists,user.getId()); this.getRequest().setAttribute("rightUser", map.get("rightUser")); this.getRequest().setAttribute("rightRelate", map.get("rightRelate")); this.getRequest().setAttribute("scucessteacher", map.get("scucessteacher")); }else{ this.getRequest().setAttribute("errorExcel", "Excel格式不正确"); return "resultexcelinfo"; } } if(resultMap.get("errorFileName")!=null && resultMap.get("errorFileName").toString().length()>0){//设置错误文件地址 this.setErrorFileName((String)resultMap.get("errorFileName")); }else{ this.setErrorFileName("ok"); } return "resultexcelinfo"; } catch (Exception e) { e.printStackTrace(); } return null; } //导出 public String writeExcel(){ try { String schName=URLDecoder.decode(schoolName, "utf-8"); String outName=schName+"-老师信息汇总.xls"; Map<String,Object> map=new HashMap<String,Object>(); map.put("proId", proID); map.put("cityId", cityID); map.put("countyId", countyID); map.put("schId", schID); //map.put("schoolId", list); if(null!=nameAndMobile && !("").equals(nameAndMobile)){ if(nameAndMobile.matches("^\\d{2,13}$")){ map.put("mobile", nameAndMobile); }else{ map.put("name", nameAndMobile); } } if("-1".equals(schID) && user.getRoleGroup()==7){ List<String> schList=loadSchoolIds(); map.put("schoolId", schList); } //设置文件名，防止在火狐等浏览器下乱码 this.getResponse().setCharacterEncoding("UTF-8"); HttpServletResponse hs = this.getResponse(); String agent = this.getRequest().getHeader("USER-AGENT"); if(agent != null && agent.indexOf("MSIE") != -1){// Miscrosoft IE outName = URLEncoder.encode(outName, "UTF-8"); }else if(agent != null && agent.indexOf("Firefox") != -1){// Mozilla Firefox outName = MimeUtility.encodeText(outName, "UTF-8", "B"); } hs.setContentType("application/x-msdownload"); hs.setHeader("Content-Disposition","attachment;filename="+outName); //读取导出数据 List<TeacherView> tvList=teacherService.downloadTeacherInfo(map); //System.out.println(tvList.size()); DeriveXLS.dervieXLS(hs.getOutputStream(), tvList, new DeriveXLSCallBack<TeacherView>(){ @Override public Object[] getValues(TeacherView tv) { // TODO Auto-generated method stub Object[] obj = new Object[6]; if(tv.getTeacherName()!=null){ obj[0] = tv.getTeacherName(); }else{obj[0] = "";} if(t