Mybatis简单存储过程实例

java批量导入excell数据

常用功能，写下备忘：

• public String addUserExcelfile ()
  {
  HttpServletRequest request = ServletActionContext.getRequest ();
  String failString = “”;
  if (“”.equals (failString))
  {
  if (excelFile != null)
  {

          List <UserInfo> hlist = new ArrayList <UserInfo> ();
          String name = excelFile.getName ();
          try
          {
              InputStream is = new FileInputStream (excelFile);
              Workbook wb = Workbook.getWorkbook (is); // 得到工作薄
              Sheet st = wb.getSheet (0);// 得到工作薄中的第一个工作表
              int rowNum = st.getRows ();
  
              String createTime = DateUtil.currentDateString ("yyyy-MM-dd hh:mm:ss");
              for (int i = 1; i < rowNum; i++)
              {// 得到第i行
                  UserInfo h = new UserInfo ();
                  Cell userCell = st.getCell (0, i);// 姓名
                  Cell cardCell = st.getCell (1, i);// 身份证
                  Cell phoneCell = st.getCell (2, i);// 电话
                  Cell addressCell = st.getCell (3, i);// 地址
                  Cell doctorCell = st.getCell (4, i);// 主治医生
                  Cell doctorphoneCell = st.getCell (5, i);// 主治医生手机号码
                  Cell authorCell = st.getCell (6, i);// 操作者
                  String username = userCell.getContents ().trim ();
                  String papersNum = cardCell.getContents ().trim ();
                  String phone = phoneCell.getContents ().trim ();
                  String address = addressCell.getContents ().trim ();//地址
                  String doctor = doctorCell.getContents ().trim ();//主治医生
                  String doctorphone = doctorphoneCell.getContents ().trim ();//主治医生手机
                  String author= authorCell.getContents ().trim ();//操作者
                  String sex="1";
                  String userBdstr="19940421";
                  if (username.equals ("") && username.equals (""))
                  {
                      failString = failString + "第" + i + "行姓名不能为空！";
                      break;
                  }
                  if (phone.equals (""))
                  {
                      failString = failString + "第" + i + "行电话号码不能为空！";
                      break;
                  }
                  if (author.equals (""))
                  {
                      failString = failString + "第" + i + "行录入者不能为空！";
                      break;
                  }
                  if (!doctor.equals (""))
                  {
                      String regex = "^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(18[0,5-9]))\\d{8}$"; 
                      if(!doctorphone.matches(regex)){
                          failString = failString + "第" + i + "行医生手机号码格式不正确！";
                          break;
                      }
  
                  }
                  if (papersNum.equals (""))
                  {
                       failString = failString + "第" + i + "行身份证不能为空！";                           
                  }
                  else
                  {
                      // 判断性别
                      String sexNum = "1";
  
                      if (papersNum.length () == 18)
                      {
                          userBdstr=papersNum.substring(6, 14);
                          sexNum = papersNum.substring (16, 17);
                          if (Integer.parseInt (sexNum) % 2 == 0)
                          {
                              sex = "2";
                          }
                          else
                          {
                              sex = "1";
                          }
                      }
                      else if (papersNum.length () == 15)
                      {
                          userBdstr=papersNum.substring(6, 14);
                          sexNum = papersNum.substring (14, 15);
                          if (Integer.parseInt (sexNum) % 2 == 0)
                          {
                              sex = "2";
                          }
                          else
                          {
                              sex = "1";
                          }
                      }
                      else
                      {
                          failString = failString + "第" + i + "行身份证格式错误！";
                          break;
                      }
  
                  }
                  h.setUserBd(DateUtil.formatStringToDate(userBdstr, "yyyyMMdd"));;
                  h.setUserSex (sex);
                  h.setUserName (username);
                  h.setLoginName(phone);
                  h.setUserPhone (phone);
                  h.setPapersNum (papersNum);
                  h.setPapersType ("1");// 身份证
                  h.setCreateTime (createTime);
                  h.setUserPassword (EncrypterMD5.getMD5ofStr ("123456"));
                  h.setUserIdentifier ("1");// 固定用户
                  h.setUserAdress(address);
                  h.setTemp_doctor(doctor);
                  h.setTemp_doctorphone(doctorphone);
                  //操作人还没弄
                  h.setUserAuthor(author);
  
                  //若需要从session中拿到录入者
  

  // NhSysUser nhsysuser=(NhSysUser) request.getSession().getAttribute (“nhsysuser”);
  // h.setUserAuthor(nhsysuser.getShowname());
  h.setOperaTime(createTime);

                  hlist.add (h);
              }
              excelFile.delete ();
              // f.delete();
          }
          catch (Exception e)
          {
              request.setAttribute ("excelimportmsg", "读取导入文件出错！");
              excelFile.delete ();
              return SUCCESS;
          }
  
          if (failString == null || failString.equals (""))
          {
              // 先获取最大的用户id
              int maxUserId = 0;
              for (int i = 0; i < hlist.size (); i++)
              {
                  UserInfo tmp = hlist.get (i);
                  // 验证手机号码唯一性
                  Map <String, String> params = new HashMap <String, String> ();
                  params.put ("userPhoneCheck", tmp.getUserPhone ());
                  int countUser = m_nhUserInfoService.countUserInfo (params);
                  if (countUser > 0)
                  {
                      failString = failString + tmp.getUserName () + "的手机号码与其他用户重复！";
                      continue;
                  }
                  // 验证身份证的惟一性
                  if (!"".equals (tmp.getPapersNum ()))
                  {
                      params.put ("userPhoneCheck", "");
                      params.put ("papersNum", tmp.getPapersNum ());
                      countUser = m_nhUserInfoService.countUserInfo (params);
                      if (countUser > 0)
                      {
                          failString = failString + tmp.getUserName () + "的身份证号码与其他用户重复！";
                          continue;
                      }
                  }
                  //验证主治医生是否存在
                  if (!"".equals (tmp.getTemp_doctor()))
                  {
                      String did=m_nhUserInfoService.getDoctorNameById(tmp.getTemp_doctor(),tmp.getTemp_doctorphone());
                      if (did==null)
                      {
                          failString = failString + tmp.getUserName () + "无法找到他的主治医生，请仔细核对姓名和手机号码！";
                          continue;
                      }
                  }
              }
              if (!failString.equals (""))
              {
                  request.setAttribute ("excelimportmsg", failString);
                  return ERROR;
              }
              for (int i = 0; i < hlist.size (); i++)
              {
                  UserInfo tmp = hlist.get (i);
                  tmp.setIsActivate ("0");
  
                  int flag =m_nhUserInfoService.addUserReturnId (tmp);
                  if (i == 0)
                  {
  
                      maxUserId = m_nhUserInfoService.getMaxUserId ();
                  }
                  else
                  {
                      maxUserId += 1;
                  }   
                  //如果插入成功
                  if(flag>0){
                      if(tmp.getTemp_doctor()!=null&&!"".equals(tmp.getTemp_doctor())){
                          //根据医生姓名查询医生id
                          String doctorid=m_nhUserInfoService.getDoctorNameById(tmp.getTemp_doctor(),tmp.getTemp_doctorphone());
                           //绑定医生
                          if(doctorid!=null){
                              m_nhUserInfoService.userBindDoctor(Long.parseLong(maxUserId+""),Long.parseLong(doctorid));
                          }
  
                      }
                  }
              }
          }
      }
  
  }
  if (!"".equals (failString))
  {
      request.setAttribute ("excelimportmsg", failString);
      return ERROR;
  }
  return SUCCESS;
  

  }