List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.quix.aia.cn.imo.mapper.EopMaintenance.java
License:Open Source License
/** * <p>Method is used to upload multiple Schedules.</p> * @param event// w w w . j av a 2s . c om * @param req * @return */ public Object eopCSVUpload(Event event, HttpServletRequest req) { log.log(Level.INFO, "EOPMaintenance --> eopCSVUpload"); if (event == null) { event = new Event(); return event; } LocaleObject localeObj = (LocaleObject) req.getSession().getAttribute(SessionAttributes.LOCALE_OBJ); // ResourceBundle msgProps = ResourceBundle.getBundle("configurations"); // String eopPath = msgProps.getString("EopPath"); Map<String, String> configurationMap = (Map<String, String>) req.getSession() .getAttribute(ApplicationAttribute.CONFIGURATION_PROPERTIES_MAP); //String eopPath = configurationMap.get("EopPath"); String csv_file_name = ""; int record_created = 0; String tempDir = System.getProperty("java.io.tmpdir"); if (req.getSession().getAttribute("csv_file_name") != null) { csv_file_name = (String) req.getSession().getAttribute("csv_file_name"); } MsgObject msgObj = null; StringBuffer strbuf = new StringBuffer(); LMSUtil lmsUtil = new LMSUtil(); int sucessCnt = 0; int m = 0; int l = 0; int rowCount = 0; try { String serverFilename = "resources/upload/Excel" + "/" + "EOP_" + LMSUtil.getRendomToken(); File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } if (csv_file_name != null && !csv_file_name.equals("")) { byte[] bytearray = (byte[]) req.getSession().getAttribute("csv_byte_session"); if (csv_file_name.contains(".xlsx") || csv_file_name.contains(".XLSX") || csv_file_name.contains(".xls") || csv_file_name.contains(".XLS")) { try { FileOutputStream stream = new FileOutputStream(serverFilename + "/" + csv_file_name); stream.write(bytearray); stream.close(); } catch (Exception e) { e.printStackTrace(); } } File temp_file = new File(tempDir + "/" + csv_file_name); FileUtils.deleteFileNFolder(temp_file); // Retrieve datas from CSV /*FileInputStream fileInputStream = new FileInputStream(new File(serverFilename+"/"+csv_file_name)); BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream));*/ BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(new FileInputStream(serverFilename + "/" + csv_file_name), "UTF-8")); String records = null; String interviewDate = "", startTime = ""; int buCode = 0, distCode = 0, branchCode = 0; String cityCode = 0 + "", sscCode = 0 + "", officeCode = 0 + ""; ImoUtilityData imoUtilityData = new ImoUtilityData(); String officeName = ""; User userObj = (User) req.getSession().getAttribute("currUserObj"); event.setCreationDate(new Date()); event.setCreatedBy(userObj.getStaffLoginId()); ; event.setModificationDate(new Date()); event.setModifiedBy(userObj.getStaffLoginId()); ; event.setStatus(true); AuditTrailMaintenance auditTrailMaintenance = new AuditTrailMaintenance(); ArrayList<Channel> channelList = ImoUtilityData.getActiveChannels(req); boolean flag = false; FileInputStream fis = null; fis = new FileInputStream(serverFilename + "/" + csv_file_name); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { rowCount++; HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { if (cellCount == 0) { break; } HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell); if (cellCount == 1) { if ("eop".equals(cell.toString())) event.setEventType(cell.toString()); else if ("companyevent".equals(cell.toString())) event.setEventType(cell.toString()); else if ("networking".equals(cell.toString())) event.setEventType(cell.toString()); else if ("training".equals(cell.toString())) event.setEventType(cell.toString()); else { flag = true; strbuf.append(localeObj.getTranslatedText("Event Type Value is Wrong") + rowCount); strbuf.append("\n"); break; } } /* if(cellCount==2){ event.setTopic(cell.toString()); }*/ if (cellCount == 2) { event.setEventName(cell.toString()); event.setTopic(cell.toString()); } if (cellCount == 3) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required DATE at row number") + rowCount); strbuf.append("\n"); break; } else { Date today = new Date(); String toDayStr = LMSUtil.convertDateToString(today); today = LMSUtil.convertStringToDate(toDayStr); interviewDate = cell.getDateCellValue() + ""; interviewDate = LMSUtil.convertExcelDateToString(interviewDate); Date intDate = LMSUtil.convertExcelDateToDate(interviewDate); if (lmsUtil.validateDDMMYYYY(interviewDate)) { event.setEventDate(intDate); if (intDate.before(today)) { flag = true; strbuf.append(localeObj.getTranslatedText( "Event Date Should Not Be Before Today at line number") + rowCount); strbuf.append("\n"); break; } /*if(checkDuplicateEvent(event)){ flag=true; strbuf.append("Schedule already Exists.Duplicate Schedule Not Allowed at line Number "+l); strbuf.append("\n"); break; }*/ } else { flag = true; strbuf.append(localeObj.getTranslatedText( "Date Format Invalid.Should Be DD/MM/YYYY at line number") + l); strbuf.append("\n"); } } } if (cellCount == 4) { startTime = cell.getDateCellValue() + ""; event.setStartTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime))); } if (cellCount == 5) { String endTime = cell.getDateCellValue() + ""; Date e1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime); Date s1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime); if (e1.before(s1) || e1.equals(s1)) { flag = true; strbuf.append(localeObj.getTranslatedText( "End Time should be earlier than Start Time at row number") + rowCount); break; } else { event.setEndTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime))); } } if (cellCount == 6) { event.setEopDescription(cell.toString()); } if (cellCount == 7) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required LOCATION at row number") + rowCount); strbuf.append("\n"); break; } else { event.setLocation(cell.toString()); } } if (cellCount == 8) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required SPEAKER at row number") + rowCount); strbuf.append("\n"); break; } else { event.setSpeaker(cell.toString()); } } if (cellCount == 9) { int estimate = (int) cell.getNumericCellValue(); event.setEstimatedCandidates(estimate); if (estimate == 0) { event.setEstimatedCandidates(0); } } if (cellCount == 10) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required OPEN TO at row number") + rowCount); strbuf.append("\n"); break; } else { if (cell.toString().equalsIgnoreCase("Y")) { event.setOpenTo("Y"); } else if (cell.toString().equalsIgnoreCase("N")) { event.setOpenTo("N"); } else { flag = true; strbuf.append(localeObj.getTranslatedText( "Required Valid (Y/N) OPEN TO at row number") + rowCount); strbuf.append("\n"); break; } } } if (cellCount == 11) { if (cell.toString().equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText( "Required OPEN TO PUBLIC REGISTRATION at row number") + rowCount); strbuf.append("\n"); break; } else { event.setOpenToRegistration(cell.toString()); } } if (cellCount == 12) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required ORGANIZER at row number") + rowCount); strbuf.append("\n"); break; } else { if (!(cell.toString().equalsIgnoreCase("CHO") || cell.toString().equalsIgnoreCase("BU") || cell.toString().equalsIgnoreCase("District") || cell.toString().equalsIgnoreCase("City") || cell.toString().equalsIgnoreCase("SSC") || cell.toString().equalsIgnoreCase("Agent Team"))) { flag = true; strbuf.append(localeObj.getTranslatedText( "ORGANIZER value is incorrect at line number") + rowCount); strbuf.append("\n"); break; } else { if (cell.toString().equalsIgnoreCase("CHO")) event.setOrganizer(1); else if (cell.toString().equalsIgnoreCase("BU")) event.setOrganizer(2); else if (cell.toString().equalsIgnoreCase("District")) event.setOrganizer(3); else if (cell.toString().equalsIgnoreCase("Branch")) event.setOrganizer(7); else if (cell.toString().equalsIgnoreCase("City")) event.setOrganizer(4); else if (cell.toString().equalsIgnoreCase("SSC")) event.setOrganizer(5); else if (cell.toString().equalsIgnoreCase("Office")) event.setOrganizer(8); else if (cell.toString().equalsIgnoreCase("Agent Team")) event.setOrganizer(6); } } } if (cellCount == 13) { if (cell.toString().equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required BU at row number") + rowCount); strbuf.append("\n"); break; } else { buCode = imoUtilityData.getBuCodeBasedOnBuName(cell.toString()); if (buCode == 0) { flag = true; strbuf.append( localeObj.getTranslatedText("Invalid BU Name row number") + rowCount); strbuf.append("\n"); break; } else { event.setBuCode(buCode); event.setBuName(cell.toString()); } } } if (cellCount == 14) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setDistrict(0); event.setDistName(localeObj.getTranslatedText("ALL")); } else { distCode = imoUtilityData.getDistrictCodeBasedOnDistrictName(cell.toString()); if (distCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid District Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, 0, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "District Name Not available In this Bu at row number") + rowCount); break; } event.setDistrict(distCode); event.setDistName(cell.toString()); } } } if (cellCount == 15) { //cell.setCellType(1); if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setBranchCode(0); event.setBranchName(localeObj.getTranslatedText("ALL")); } else { branchCode = imoUtilityData.getBranchCodeBasedOnBranchName(cell.toString()); if (branchCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Branch Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Branch Name Not available In this District at row number") + rowCount); break; } event.setBranchCode(branchCode); event.setBranchName(cell.toString()); } } } if (cellCount == 16) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setCityCode("0"); event.setCityName(localeObj.getTranslatedText("ALL")); } else { cityCode = imoUtilityData.getCityCodeBasedOnDistrictName(cell.toString()); if (cityCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid City Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "City Name Not available In this Branch at row number") + rowCount); break; } event.setCityCode(cityCode); event.setCityName(cell.toString()); } } } if (cellCount == 17) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setSscCode("0"); event.setSscName(localeObj.getTranslatedText("ALL")); } else { sscCode = imoUtilityData.getSSCCodeBasedOnSSCName(cell.toString()); if (sscCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid SSC Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Ssc Name Not available In this City at row number") + rowCount); break; } event.setSscCode(sscCode); event.setSscName(cell.toString()); } } } if (cellCount == 18) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setOfficeCode("0"); event.setOfficeName(localeObj.getTranslatedText("ALL")); } else { officeCode = imoUtilityData.getOfficeCodeBasedOnSSCName(cell.toString()); if (officeCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Office Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, officeCode); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Office Name Not available In this SSC at row number") + rowCount); break; } event.setOfficeCode(officeCode); event.setOfficeName(cell.toString()); } } } if (cellCount == 19) { if (cell.toString().equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required AGENT TEAM at row number") + rowCount); strbuf.append("\n"); break; } else { event.setAgentTeam(cell.toString()); } } cellCount++; } //cell while loop if (cellCount == 0) { cellCount++; } else { if (flag == true) { cellCount = 1; flag = false; strbuf.append("\n"); continue; } else { if (userObj.getUserType().equals("AD")) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } else { if (userObj.isSscLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0")) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } else if (userObj.isCityLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0")) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } else if (userObj.isDistrictLevel()) { if (buCode > 0 && distCode > 0) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } else if (userObj.isBuLevel()) { if (buCode > 0) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } } if (record_created > 0) { auditTrailMaintenance.insertAuditTrail( new AuditTrail(userObj.getStaffLoginId() + "", AuditTrail.MODULE_EOP, AuditTrail.FUNCTION_CREATE, event.toString())); } } flag = false; } } //end while loop } if (strbuf.length() > 0) { ImoUtilityData imoutill = new ImoUtilityData(); imoutill.summaryReport(strbuf, req); } } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); LogsMaintenance logsMain = new LogsMaintenance(); e.printStackTrace(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("EopMaintenance", Level.SEVERE + "", errors.toString()); } int failedCnt = rowCount - sucessCnt; req.getSession().removeAttribute("csv_file_name"); req.getSession().setAttribute("strbuf", strbuf); req.getSession().setAttribute("formObj", new PathDetail().getFormObj("EopUploadCSV")); req.setAttribute("CacheName", "EOP"); //if(sucessCnt!=0){ String uploadSucessString = localeObj.getTranslatedText("Number of records uploaded successfully"); String recoredFailString = localeObj.getTranslatedText("Number of records fail"); return new ErrorObject(uploadSucessString + " :" + sucessCnt + " <br> " + recoredFailString + " :" + (failedCnt - 2) + " ", "", localeObj); //}else{ //return new ErrorObject("The new schedule csv file uploaded Successfully", ""); //} }
From source file:com.quix.aia.cn.imo.mapper.InterviewMaintenance.java
License:Open Source License
/** * <p>This method performs multiple Interviews Upload through CSV File</p> * @param interviewObj Interview object//from w ww . j av a 2 s. c om * @param requestParameters Servlet Request Parameter */ public Object cSVUpload(Interview interviewObj, HttpServletRequest requestParameters) { LocaleObject localeObj = (LocaleObject) requestParameters.getSession() .getAttribute(SessionAttributes.LOCALE_OBJ); if (interviewObj == null) { interviewObj = new Interview(); return interviewObj; } // ResourceBundle msgProps = ResourceBundle.getBundle("configurations"); // String interviewPath = msgProps.getString("InterviewPath"); Map<String, String> configurationMap = (Map<String, String>) requestParameters.getSession() .getAttribute(ApplicationAttribute.CONFIGURATION_PROPERTIES_MAP); //String interviewPath = configurationMap.get("InterviewPath"); String csv_file_name = ""; if (requestParameters.getSession().getAttribute("csv_file_name") != null) { csv_file_name = (String) requestParameters.getSession().getAttribute("csv_file_name"); } requestParameters.getSession().removeAttribute("csv_file_name"); String tempDir = System.getProperty("java.io.tmpdir"); Session session = null; int record_created = 0; StringBuffer strbuf = new StringBuffer(); int sucessCnt = 0; int m = 0; int l = 0; int rowCount = 0; try { String serverFilename = "resources/upload/Excel" + "/" + "INT_" + LMSUtil.getRendomToken(); File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } if (csv_file_name != null && !csv_file_name.equals("")) { byte[] bytearray = (byte[]) requestParameters.getSession().getAttribute("csv_byte_session"); if (csv_file_name.contains(".xlsx") || csv_file_name.contains(".XLSX") || csv_file_name.contains(".xls") || csv_file_name.contains(".XLS")) { try { FileOutputStream stream = new FileOutputStream(serverFilename + "/" + csv_file_name); stream.write(bytearray); stream.close(); } catch (Exception e) { e.printStackTrace(); LogsMaintenance logsMain = new LogsMaintenance(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("InterViewMaintenance", Level.SEVERE + "", errors.toString()); } } //delete temp directory File temp_file = new File(tempDir + "/" + csv_file_name); FileUtils.deleteFileNFolder(temp_file); // Retrieve datas from CSV /*FileInputStream fileInputStream = new FileInputStream(new File(serverFilename+"/"+csv_file_name)); BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream));*/ BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(new FileInputStream(serverFilename + "/" + csv_file_name), "UTF-8")); String records = null; String interviewType = "", interviewSessionName = "", interviewDate = "", startTime = "", endTime = "", location = "", interviewMaterial = "", estimatedCondidates = "", buName = "", distName = "", cityName = "", sscName = "", branchName = "", officeName = ""; int buCode = 0, distCode = 0, branchCode = 0; String cityCode = "", sscCode = "", officeCode = ""; session = HibernateFactory.openSession(); ImoUtilityData imoUtilityData = new ImoUtilityData(); User userObj = (User) requestParameters.getSession().getAttribute("currUserObj"); interviewObj.setCreationDate(new Date()); interviewObj.setCreatedBy(userObj.getStaffLoginId()); ; interviewObj.setModificationDate(new Date()); interviewObj.setModifiedBy(userObj.getStaffLoginId()); ; interviewObj.setStatus(true); interviewObj.setToken(LMSUtil.getRendomToken()); AuditTrailMaintenance auditTrailMaintenance = new AuditTrailMaintenance(); boolean flag = false; LMSUtil lmsUtil = new LMSUtil(); FileInputStream fis = null; fis = new FileInputStream(serverFilename + "/" + csv_file_name); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { rowCount++; HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { if (cellCount == 0) { break; } HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell); if (cellCount == 1) { interviewType = cell.toString(); if (interviewType.equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required Interview Type at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setInterviewType(interviewType); } } if (cellCount == 2) { interviewSessionName = cell.toString(); interviewObj.setInterviewSessionName(interviewSessionName); if (interviewSessionName.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required Interview Session Name at row number") + rowCount); strbuf.append("\n"); break; } /*else if(checkDuplicateInterview(interviewObj)){ flag=true; strbuf.append("Duplicate Interview Name at row number "+rowCount); break; }*/ else { interviewObj.setInterviewSessionName(interviewSessionName); } } if (cellCount == 3) { //System.out.println("date --> "+cell.getDateCellValue()+""); interviewDate = cell.getDateCellValue() + ""; interviewDate = LMSUtil.convertExcelDateToString(interviewDate); Date intDate = LMSUtil.convertExcelDateToDate(interviewDate); if (interviewType.equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required Interview Date at row number") + rowCount); strbuf.append("\n"); break; } else { Date today = new Date(); String toDayStr = LMSUtil.convertDateToString(today); today = LMSUtil.convertStringToDate(toDayStr); if (lmsUtil.validateDDMMYYYY(interviewDate)) { interviewObj.setInterviewDate(intDate); if (intDate.before(today)) { flag = true; strbuf.append(localeObj.getTranslatedText( "Interview Date should not be before Today at row number") + rowCount); strbuf.append("\n"); break; } } else { flag = true; strbuf.append(localeObj.getTranslatedText( "Date Format Invalid.Should Be DD/MM/YYYY at line number") + l); strbuf.append("\n"); break; } } } if (cellCount == 4) { startTime = cell.getDateCellValue() + ""; if (interviewType.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required StartTime at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setStartTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime))); } } if (cellCount == 5) { endTime = cell.getDateCellValue() + ""; Date e1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime); Date s1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime); if (interviewType.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required EndTime at row number") + rowCount); strbuf.append("\n"); break; } else if (e1.before(s1) || e1.equals(s1)) { flag = true; strbuf.append(localeObj.getTranslatedText( "End Time should be earlier than Start Time at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setEndTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime))); } } if (cellCount == 6) { location = cell.toString(); if (location.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required LOCATION at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setLocation(location); } } if (cellCount == 7) { interviewMaterial = cell.toString(); if (interviewMaterial.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required Interview Material at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setInterviewMaterial(interviewMaterial); } } if (cellCount == 8) { // estimatedCondidates = cell.getNumericCellValue()+""; int estimate = (int) cell.getNumericCellValue(); System.out.println("esimate time " + estimate); // if(estimatedCondidates.equals("")){ // interviewObj.setEstimatedCondidates(0); // } // else if(!LMSUtil.validInt(estimatedCondidates)){ // flag=true; // strbuf.append("Estimated Candidates Should be a Number at row number "+rowCount); // break; // } interviewObj.setEstimatedCondidates(estimate); } if (cellCount == 9) { buName = cell.toString(); if (buName.equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required BU at row number") + rowCount); strbuf.append("\n"); break; } else { buCode = imoUtilityData.getBuCodeBasedOnBuName(buName); if (buCode == 0) { flag = true; strbuf.append( localeObj.getTranslatedText("Invalid BU Name row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setBuCode(buCode); } } } if (cellCount == 10) { distName = cell.toString(); if (distName.equals("")) { interviewObj.setDistrict(0); } else { distCode = imoUtilityData.getDistrictCodeBasedOnDistrictName(distName); if (distCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid District Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, 0, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "District Name Not available In this Bu at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setDistrict(distCode); } } } if (cellCount == 11) { branchName = cell.toString(); if (branchName.equals("")) { interviewObj.setBranchCode(0); } else { branchCode = imoUtilityData.getBranchCodeBasedOnBranchName(branchName); if (branchCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Branch Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Branch Name Not available In this District at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setBranchCode(branchCode); } } } if (cellCount == 12) { cityName = cell.toString(); if (cityName.equals("")) { interviewObj.setCityCode("0"); } else { cityCode = imoUtilityData.getCityCodeBasedOnDistrictName(cityName); if (cityCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid City Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "City Name Not available In this Branch at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setCityCode(cityCode); } } } if (cellCount == 13) { sscName = cell.toString(); if (sscName.equals("")) { interviewObj.setSscCode("0"); } else { sscCode = imoUtilityData.getSSCCodeBasedOnSSCName(sscName); if (sscCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid SSC Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Ssc Name Not available In this City at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setSscCode(sscCode); } } } if (cellCount == 14) { officeName = cell.toString(); if (officeName.equals("")) { interviewObj.setOfficeCode("0"); } else { officeCode = imoUtilityData.getOfficeCodeBasedOnSSCName(officeName); if (officeCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Office Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, officeCode); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Office Name Not available In this SSC at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setOfficeCode(officeCode); } } } cellCount++; } // cell while loop if (cellCount == 0) { cellCount++; } else { if (flag == true) { cellCount = 1; flag = false; strbuf.append("\n"); continue; } else { //insertion done here if (userObj.getUserType().equals("AD")) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } else { if (userObj.isOfficeLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0 && !officeCode.equals("0")) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isSscLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isCityLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && branchCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isBranchLevel()) { if (buCode > 0 && distCode > 0 && branchCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isDistrictLevel()) { if (buCode > 0 && distCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isBuLevel()) { if (buCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } } if (record_created > 0) { auditTrailMaintenance.insertAuditTrail( new AuditTrail(userObj.getStaffLoginId() + "", AuditTrail.MODULE_INTERVIEW, AuditTrail.FUNCTION_CREATE, interviewObj.toString())); } } flag = false; } } // row whicle loop if (strbuf.length() > 0) { ImoUtilityData imoutill = new ImoUtilityData(); imoutill.summaryReport(strbuf, requestParameters); } } } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } finally { try { //HibernateFactory.close(session); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } } int failedCnt = rowCount - sucessCnt; requestParameters.getSession().setAttribute("strbuf", strbuf); requestParameters.getSession().setAttribute("formObj", new PathDetail().getFormObj("InterviewUploadCSV")); requestParameters.setAttribute("CacheName", "Interview"); //if(sucessCnt!=0){ String uploadSucessString = localeObj.getTranslatedText("Number of records uploaded successfully"); String recoredFailString = localeObj.getTranslatedText("Number of records fail"); return new ErrorObject( uploadSucessString + ":" + sucessCnt + " <br> " + recoredFailString + " :" + (failedCnt - 2) + " ", "", localeObj); //}else{ //return new ErrorObject("The new Holiday csv file uploaded Successfully", ""); //} }
From source file:com.quix.aia.cn.imo.mapper.UserMaintenance.java
License:Open Source License
/** * <p>This method performs call for validation of upload .csv file data * and insertion method /*w w w . j av a2 s . c om*/ * & audit trail for holiday update * & sets message object in request</p> * @param Userid * @param req Servlet Request Parameter * @return User object */ public Object mapFormCSVUpload(User objToBeMapped, HttpServletRequest req) { // TODO Auto-generated method stub log.log(Level.INFO, "UserMaintenance --> mapFormCSVUpload"); LocaleObject localeObj = (LocaleObject) req.getSession().getAttribute(SessionAttributes.LOCALE_OBJ); if (objToBeMapped == null) { objToBeMapped = new User(); return objToBeMapped; } //ResourceBundle msgProps = ResourceBundle.getBundle("configurations"); // String userPath = //msgProps.getString("UserPath"); String csv_file_name = ""; if (req.getSession().getAttribute("csv_file_name") != null) { csv_file_name = (String) req.getSession().getAttribute("csv_file_name"); req.getSession().removeAttribute("csv_file_name"); String tempDir = System.getProperty("java.io.tmpdir"); Session session = null; int record_created = 0; MsgObject msgObj = null; User user = new User(); StringBuffer strbuf = new StringBuffer(); try { int sucessCnt = 0; int m = 0; int l = 0; int rowCount = 0; String serverFilename = "resources/upload/userExcel" + "USER_" + LMSUtil.getRendomToken(); File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } if (csv_file_name != null && !csv_file_name.equals("")) { byte[] bytearray = (byte[]) req.getSession().getAttribute("csv_byte_session"); if (csv_file_name.contains(".xlsx") || csv_file_name.contains(".XLSX") || csv_file_name.contains(".xls") || csv_file_name.contains(".XLS")) { try { FileOutputStream stream = new FileOutputStream(serverFilename + "/" + csv_file_name); stream.write(bytearray); stream.close(); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); LogsMaintenance logsMain = new LogsMaintenance(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("UserMaintenance", Level.SEVERE + "", errors.toString()); } } File temp_file = new File(tempDir + "/" + csv_file_name); FileUtils.deleteFileNFolder(temp_file); String records = null; int buCode = 0, distCode = 0, channelCode = 0, branchCode = 0; String cityCode = "0", sscCode = "0", officeCode = "0"; session = HibernateFactory.openSession(); ImoUtilityData imoUtilityData = new ImoUtilityData(); User userObj = (User) req.getSession().getAttribute("currUserObj"); user.setCreationDate(new Date()); user.setCreatedBy(userObj.getStaffLoginId()); ; user.setModificationDate(new Date()); user.setModifiedBy(userObj.getStaffLoginId()); ; user.setStatus(true); AuditTrailMaintenance auditTrailMaintenance = new AuditTrailMaintenance(); boolean flag = false; FileInputStream fis = null; fis = new FileInputStream(serverFilename + "/" + csv_file_name); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { rowCount++; HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { if (cellCount == 0) { break; } // cellCount++; HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell.toString()); if (cellCount == 1) { if (cell.toString().equals("Y".toUpperCase())) { user.setCho(true); user.setUserType("AD"); } else if (cell.toString().equals("N".toUpperCase())) { user.setCho(false); user.setUserType("ST"); } else { strbuf.append(localeObj.getTranslatedText("Required Y OR N CHO at row number") + rowCount + "\n"); } } if (cellCount == 2) { if (user.isCho()) { user.setBuCode(0); } else { buCode = imoUtilityData.getBuCodeBasedOnBuName(cell.toString()); if (buCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid BU Name row number") + rowCount + "\n"); break; } else { user.setBuCode(buCode); user.setBuName(cell.toString()); } } } if (cellCount == 3) { if (user.isCho()) { user.setDistrict(0); } else { distCode = imoUtilityData.getDistrictCodeBasedOnDistrictName(cell.toString()); user.setDistrict(distCode); user.setDistName(cell.toString()); } } if (cellCount == 4) { if (user.isCho()) { user.setBranchCode(9986); } else { branchCode = imoUtilityData.getBranchCodeBasedOnBranchName(cell.toString()); user.setBranchCode(branchCode); user.setBranchName(cell.toString()); } } if (cellCount == 5) { if (user.isCho()) { user.setCityCode("0"); } else { cityCode = imoUtilityData.getCityCodeBasedOnDistrictName(cell.toString()); user.setCityCode(cityCode); user.setCityName(cell.toString()); } } if (cellCount == 6) { if (user.isCho()) { user.setSscCode("0"); } else { sscCode = imoUtilityData.getSSCCodeBasedOnSSCName(cell.toString()); user.setSscCode(sscCode); user.setSscName(cell.toString()); } } if (cellCount == 7) { if (user.isCho()) { user.setOfficeCode("0"); } else { officeCode = imoUtilityData.getOfficeCodeBasedOnSSCName(cell.toString()); user.setOfficeCode(officeCode); user.setOfficeName(cell.toString()); } } if (cellCount == 8) { int dept = imoUtilityData.getdeptCodeBasedOndeptName(cell.toString()); user.setDepartment(dept); user.setDeptName(cell.toString()); } if (cellCount == 9) { if (checkLoginID(cell.toString().trim(), 0)) { flag = true; strbuf.append( localeObj.getTranslatedText("Dublicate STAFF LOGIN ID at row number") + rowCount + "\n"); break; } else { user.setStaffLoginId(cell.toString().toUpperCase()); } } if (cellCount == 10) { user.setStaffName(cell.toString()); } if (cellCount == 11) { if (checkemilID(cell.toString(), 0)) { flag = true; strbuf.append(localeObj.getTranslatedText("Dublicate Email at row number") + rowCount + "\n"); break; } else { user.setEmail(cell.toString()); } } if (cellCount == 12) { user.setContactNo(cell.toString()); } if (cellCount == 13) { user.setExtensionNo(cell.toString()); } cellCount++; } if (cellCount == 0) { cellCount++; } else { if (flag == true) { cellCount = 1; flag = false; strbuf.append("\n"); continue; } else { if (userObj.getUserType().equals("AD")) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } else { if (userObj.isOfficeLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0 && !officeCode.equals("0")) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } } else if (userObj.isSscLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } } else if (userObj.isBranchLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } } else if (userObj.isCityLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0")) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } } else if (userObj.isDistrictLevel()) { if (buCode > 0 && distCode > 0) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } } else if (userObj.isBuLevel()) { if (buCode > 0) { msgObj = insertUser(user, req, msgObj); sucessCnt++; cellCount = 1; } } } User userAudi = new User(user); log.log(Level.INFO, userAudi.toString()); if (!msgObj.equals("0")) { auditTrailMaintenance.insertAuditTrail(new AuditTrail( String.valueOf(user.getUser_no()), AuditTrail.MODULE_USER, AuditTrail.FUNCTION_CREATE, "Action on:INSERT T_USER CREATED,USER_NO:" + user.getUser_no())); } else { auditTrailMaintenance.insertAuditTrail(new AuditTrail( String.valueOf(user.getUser_no()), AuditTrail.MODULE_USER, AuditTrail.FUNCTION_FAILED, "Action on:INSERT T_USER CREATED,USER_NO:" + user.getUser_no())); } flag = false; } } } } if (strbuf.length() > 0) { ImoUtilityData imoutill = new ImoUtilityData(); imoutill.summaryReport(strbuf, req); } req.getSession().setAttribute("strbuf", strbuf); msgObj = new MsgObject("The new User has been created."); req.getSession().setAttribute("msgObject", msgObj); req.getSession().setAttribute("formObj", new PathDetail().getFormObj("userUploadCsv")); int failedCnt = rowCount - sucessCnt; //if(sucessCnt!=0){ String uploadSucessString = localeObj.getTranslatedText("Number of records uploaded successfully"); String recoredFailString = localeObj.getTranslatedText("Number of records fail"); return new ErrorObject(uploadSucessString + " :" + sucessCnt + " <br> " + recoredFailString + " :" + (failedCnt - 1) + " ", "", localeObj); //}else{ //return new ErrorObject("The new Holiday csv file uploaded Successfully", ""); //} } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); LogsMaintenance logsMain = new LogsMaintenance(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("UserMaintenance", Level.SEVERE + "", errors.toString()); } finally { try { //HibernateFactory.close(session); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } } } req.getSession().setAttribute("formObj", new PathDetail().getFormObj("userUploadCsv")); return new ErrorObject("Please choose the document", " ", localeObj); }
From source file:com.raisepartner.chartfusion.generator.XLSParser.java
License:Open Source License
public MetaNode parse(File file) throws Exception { debug("Parsing '" + file.getAbsolutePath() + "' ..."); InputStream in = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(in)); Map<String, MetaNode> nodeName2MetaNode = new HashMap<String, MetaNode>(); try {//from w w w.j a v a2 s . co m parseAttribute(wb.getSheetAt(0), nodeName2MetaNode); return parseNode(wb.getSheetAt(1), nodeName2MetaNode); } catch (Exception e) { System.err.println("Error during the parsing of the xls file: '" + file.getAbsolutePath() + "':"); throw e; } finally { if (in != null) { in.close(); } } }
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
public void processWorkbook(HSSFWorkbook workbook) { evaluator = workbook.getCreationHelper().createFormulaEvaluator(); for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { HSSFSheet sheet = workbook.getSheetAt(sheetNum); for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == Cell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(c); }//from www. ja va2 s .c om } } } final SummaryInformation summaryInformation = workbook.getSummaryInformation(); if (summaryInformation != null) { processDocumentInformation(summaryInformation); } if (isUseDivsToSpan()) { // prepare CSS classes for later usage this.cssClassContainerCell = htmlDocumentFacade.getOrCreateCssClass(cssClassPrefixCell, "padding:0;margin:0;align:left;vertical-align:top;"); this.cssClassContainerDiv = htmlDocumentFacade.getOrCreateCssClass(cssClassPrefixDiv, "position:relative;"); } for (int s = 0; s < workbook.getNumberOfSheets(); s++) { HSSFSheet sheet = workbook.getSheetAt(s); processSheet(sheet); } htmlDocumentFacade.updateStylesheet(); }
From source file:com.ro.ssc.app.client.utils.ExcelReader.java
/** * * @param file// ww w . j a v a2 s. c om * @return */ public static Map<String, User> readExcel(File file) { Map<String, User> result = new HashMap<>(); List<Event> events; try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } DateTimeFormatter dtf = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss EEEE"); for (int r = 1; r < rows; r++) { row = sheet.getRow(r); if (row != null) { try { String user = WordUtils .capitalizeFully(row.getCell(ExcelEnum.USER_NAME.getAsInteger()).toString().trim()); if (row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim().equals("1.0")) { if (result.containsKey(user)) { events = result.get(user).getEvents(); events.add(new Event( DateTime.parse(row.getCell(ExcelEnum.TIMESTAMP.getAsInteger()).toString(), dtf), row.getCell(ExcelEnum.DESCRIPTION.getAsInteger()).toString(), row.getCell(ExcelEnum.ADDRESS.getAsInteger()).toString().trim(), row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim() .equals("1.0"))); result.get(user).setEvents(events); } else { events = new ArrayList(); events.add(new Event( DateTime.parse(row.getCell(ExcelEnum.TIMESTAMP.getAsInteger()).toString(), dtf), row.getCell(ExcelEnum.DESCRIPTION.getAsInteger()).toString().trim(), row.getCell(ExcelEnum.ADDRESS.getAsInteger()).toString().trim(), row.getCell(ExcelEnum.PASSED.getAsInteger()).toString().trim() .equals("1.0"))); String id = row.getCell(ExcelEnum.USER_ID.getAsInteger()).toString().trim(); result.put(user, new User(user, id.contains(".") ? id.split("\\.")[0] : id, row.getCell(ExcelEnum.CARD_NO.getAsInteger()).toString().trim(), WordUtils.capitalizeFully( row.getCell(ExcelEnum.DEPARTMENT.getAsInteger()).toString().trim()), events)); } } } catch (Exception e) { log.error("Exception" + e.getMessage()); } } } } catch (Exception ioe) { log.error("Exception" + ioe.getMessage()); } return result; }
From source file:com.sadakhata.spamsmsblocker.SKSpamBlocker.java
License:Mozilla Public License
private void readExcelFile(String excelFileName, double x[][], double t[][], int numRows) { try {//from w w w. j av a2 s .co m HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(excelFileName)); HSSFSheet worksheet = workbook.getSheetAt(0); for (int rowIdx = 0; rowIdx < numRows; rowIdx++) { HSSFRow row = worksheet.getRow(rowIdx); String msgCellVal = row.getCell(4).getStringCellValue(); int spamCellVal = (int) row.getCell(5).getNumericCellValue(); hashFullString(x[rowIdx], msgCellVal); t[rowIdx][spamCellVal] = 1; //System.out.println(rowIdx); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembersByExcel(InputStream is) throws Exception { // TODO Auto-generated method stub HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<Member> members = new ArrayList<Member>(); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; }/* w w w .j ava 2 s.com*/ int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); Member member = new Member(); String username = null; String mobile = null; String realName = null; for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (i == 0) { // ?? username = memberCell.toString(); } if (i == 1) { // mobile = memberCell.toString(); } if (i == 2) { // ?? realName = memberCell.toString(); } } // ? if (username != null) { member.setUsername(username); } else { String errorString = "" + rowNum + "????"; return null; } if (mobile != null) { member.setMobile(mobile); } else { String errorString = "" + rowNum + "???"; return null; } if (realName != null) { member.setRealName(realName); } else { String errorString = "" + rowNum + "????"; return null; } members.add(member); } } return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start//from www . j a va 2 s . c o m for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.setIsUpdate(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getTeachers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start/* w ww . j ava 2s. com*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.s(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } if (i == 2) { // ??-? String classTeacherMap = memberCell.toString(); classTeacherMap = classTeacherMap.trim(); member.setAddress(classTeacherMap); } } // end?? members.add(member); } // endrow } // end return members; }