excel导入数据

/**
* 批量导入
*/
public void uploadExcel() {
String fmt = "yyyy-MM-dd";
int failNum=0; //用来保存导入失败的数据
int successNum=0;
StringBuffer sbBuffer = new StringBuffer();
try {
Workbook workbook = Workbook.getWorkbook(myFile);
Sheet sheet = workbook.getSheet(0);  
int rowCount = sheet.getRows();  
for (int i = 1; i < rowCount; i++) {  
            String staffName = sheet.getCell(0, i).getContents();
            if("".equals(staffName)||staffName==null){
            break;
            }else {
            StaffFile staffFile = new StaffFile();
            staffFile.setName(staffName);
            staffFile.setPositionId(positionService.getPositionIdByName(sheet.getCell(1, i).getContents()));
            staffFile.setOrgId(deptService.getDepIdByName(sheet.getCell(2, i).getContents()));
            staffFile.setContactPhone(sheet.getCell(3, i).getContents());
            staffFile.setBirthday(DateUtil.parseDate(sheet.getCell(4, i).getContents()));
            staffFile.setGender(dataDictionaryService.getValueByName(sheet.getCell(5, i).getContents()));
            staffFile.setNation(dataDictionaryService.getValueByName(sheet.getCell(6, i).getContents()));
            staffFile.setEducation(dataDictionaryService.getValueByName(sheet.getCell(7, i).getContents()));
            staffFile.setPolitics(dataDictionaryService.getValueByName(sheet.getCell(8, i).getContents()));
            staffFile.setSecNum(sheet.getCell(9, i).getContents());
            staffFile.setIdCardNum(sheet.getCell(10, i).getContents());
            staffFile.setMaritalStatus(dataDictionaryService.getValueByName(sheet.getCell(11, i).getContents()));
            staffFile.setGunLisenceVd(DateUtil.parseDate(sheet.getCell(12, i).getContents()));
            staffFile.setGunLisenceNum(sheet.getCell(13, i).getContents());
            staffFile.setMail(sheet.getCell(14, i).getContents());
            staffFile.setHomeAdd(sheet.getCell(15, i).getContents());
            staffFile.setHomeTel(sheet.getCell(16, i).getContents());
            staffFile.setEntryDate(DateUtil.parseDate(sheet.getCell(17, i).getContents()));
            staffFile.setIsReg(dataDictionaryService.getValueByName(sheet.getCell(18, i).getContents()));
            staffFile.setRegDate(DateUtil.parseDate(sheet.getCell(19, i).getContents()));
            staffFile.setOnJob(dataDictionaryService.getValueByName(sheet.getCell(20, i).getContents()));
            staffFile.setDimissionDate(DateUtil.parseDate(sheet.getCell(21, i).getContents()));
            staffFile.setCommission(dataDictionaryService.getValueByName(sheet.getCell(22, i).getContents()));
            staffFile.setSecStatus(dataDictionaryService.getValueByName(sheet.getCell(235, i).getContents()));
            staffFile.setSecPassDate(DateUtil.parseDate(sheet.getCell(24, i).getContents()));
            staffFile.setIntern(dataDictionaryService.getValueByName(sheet.getCell(25, i).getContents()));
            staffFile.setCreateDate(new Date());
            staffFile.setCreateUserId(userBean.getId());
            staffFile.setCreateUserName(userBean.getName());
            staffFile.setDeleted("N");
            try {
            HqlHelper h1 = new HqlHelper(StaffFile.class).addEqual("deleted", "N").addEqual("name", staffName);
            HqlHelper h2 = new HqlHelper(StaffFile.class).addEqual("deleted", "N").addEqual("idCardNum", sheet.getCell(10, i).getContents()); 
            HqlHelper h3 = new HqlHelper(StaffFile.class).addEqual("deleted", "N").addEqual("contactPhone", sheet.getCell(3, i).getContents());
           
            if(Tools.isValid(positionService.getPositionIdByName(sheet.getCell(1, i).getContents())) 
            && Tools.isValid(deptService.getDepIdByName(sheet.getCell(2, i).getContents()))
            && !Tools.isValid(staffFileService.getByHql(h1))
            && !Tools.isValid(staffFileService.getByHql(h2))
            && !Tools.isValid(staffFileService.getByHql(h3))) {
            staffFileService.doAdd(staffFile, userBean);
            successNum++;
            }else {
            failNum++;
           
            sbBuffer.append("【"+staffName+"】");
            if(!Tools.isValid(sheet.getCell(0, i).getContents())){
          sbBuffer.append("姓名为空,");
              }
            if(!Tools.isValid(positionService.getPositionIdByName(sheet.getCell(1, i).getContents()))){
          sbBuffer.append("岗位为空,");
              }
              if(!Tools.isValid(deptService.getDepIdByName(sheet.getCell(2, i).getContents()))){
          sbBuffer.append("部门为空,");
              }
              if(Tools.isValid(staffFileService.getByHql(h1))){
          sbBuffer.append("名字已存在,");
              }
              if(Tools.isValid(staffFileService.getByHql(h2))){
          sbBuffer.append("身份证已存在,");
              }
              if(Tools.isValid(staffFileService.getByHql(h3))){
          sbBuffer.append("联系电话已存在,");
              }
              sbBuffer.append(";");
            }
           
} catch (Exception e) {
failNum++;
sbBuffer.append("【"+staffName+"】");
sbBuffer.append("系统抛出");
sbBuffer.append(";");
}
}
            }
if (failNum==0) {
String info = "无";
response.getWriter().println("<script>parent.callback('"+successNum+"','"+failNum+"','"+info+"')</script>");
}else {
response.getWriter().println("<script>parent.callback('"+successNum+"','"+failNum+"','"+sbBuffer+"')</script>");
}
} catch (Exception e) {
e.printStackTrace();
}

}

private File myFile;
public File getMyFile() {
return myFile;
}


public void setMyFile(File myFile) {
this.myFile = myFile;
}