List of usage examples for org.apache.poi.hssf.usermodel HSSFCell toString
public String toString()
From source file:com.quix.aia.cn.imo.mapper.EopMaintenance.java
License:Open Source License
private boolean checkSpecialGroupAgentId(String eopPath, String tempDir, String fileName, HttpServletRequest requestParameters, String byteSession) { // TODO Auto-generated method stub log.log(Level.INFO, "---EopMaintenance checkSpecialGroupAgentId---"); String path = ""; FileOutputStream stream = null; String rendomStr = LMSUtil.getRendomToken(); boolean flag = false; try {/*from w ww .j av a 2 s . c o m*/ String serverFilename = eopPath + "/" + "EOP_" + rendomStr; File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } byte[] bytearray = (byte[]) requestParameters.getSession().getAttribute(byteSession); stream = new FileOutputStream(serverFilename + "/" + fileName); stream.write(bytearray); path = "EOP_" + rendomStr + "/" + fileName; log.log(Level.INFO, "---EopMaintenance deleting temp folder---"); FileInputStream fileInputStream = null; BufferedReader bufferedReader = null; String records = null; SpecialGroup sg = null; fileInputStream = new FileInputStream(new File(eopPath + "/" + path)); bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream)); if (fileName.contains("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell.toString()); if (cell.toString().trim().length() != 0 && cell.toString() != null) { flag = checkAgentCodeInAamData(cell.toString(), requestParameters); } else { flag = true; } if (flag == false) { break; } } if (flag == false) { break; } } } else { while ((records = bufferedReader.readLine()) != null) { flag = checkAgentCodeInAamData(records, requestParameters); } } } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); LogsMaintenance logsMain = new LogsMaintenance(); e.printStackTrace(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("EopMaintenance", Level.SEVERE + "", errors.toString()); } finally { if (stream != null) try { stream.close(); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); } } return flag; }
From source file:com.quix.aia.cn.imo.mapper.EopMaintenance.java
License:Open Source License
/** * <p>Method takes care for inserting special users agency unit.</p> * @param session hibernate session object * @param eventCode event code of current Event * @param filePath file path// www . j av a 2 s . co m * @param op represents from where this method is called(during insert or update) * @param fileExtension */ public void insertSpecialGroup(Session session, int eventCode, String filePath, String op, String fileExtension) { // Retrieve datas from CSV FileInputStream fileInputStream = null; BufferedReader bufferedReader = null; try { String records = null; SpecialGroup sg = null; fileInputStream = new FileInputStream(new File(filePath)); bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream)); if (op.equals("UPDATE")) { Query deleteQ = session.createQuery("delete SpecialGroup where eventCode = " + eventCode + ""); deleteQ.executeUpdate(); } if (fileExtension.equals("xls")) { HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell.toString()); sg = new SpecialGroup(); sg.setEventCode(eventCode); sg.setAgencyUnit(cell.toString()); session.save(sg); } } } else { while ((records = bufferedReader.readLine()) != null) { sg = new SpecialGroup(); sg.setEventCode(eventCode); sg.setAgencyUnit(records); session.save(sg); } } log.log(Level.INFO, "---EopMaintenance special user inserted---"); } 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()); } finally { try { if (fileInputStream != null) fileInputStream.close(); if (bufferedReader != null) bufferedReader.close(); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } } }
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/*from www . j av a2 s. com*/ * @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 ww w.j av a 2 s. c o m * @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 a va 2s . co m*/ * & 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.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 a v a2 s.co m*/ 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 w ww. j a v a 2 s . c om 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/*from w w w . ja va 2s . c o m*/ 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; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<DictStudent> getDictStudents(InputStream inputStream, DictSchool dictSchool, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<DictStudent> dictStudents = new ArrayList<DictStudent>(); // start--//from www. j av a2s. c o m 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 studentInfo = hssfSheet.getRow(rowNum); if (studentInfo == null) { continue; } DictStudent dictStudent = new DictStudent(); dictStudent.setCreateDate(new Date()); dictStudent.setModifyDate(new Date()); dictStudent.setStudentStatus(StudentStatus.active); String studentName = null; String studentNo = null; DictClass dictClass = new DictClass(); // start_?? for (int i = 0; i < studentInfo.getLastCellNum(); i++) { HSSFCell studentCell = studentInfo.getCell(i); if (studentCell == null) { continue; } if (i == 0) { // ? studentNo = studentCell.toString(); studentNo = studentNo.trim(); dictStudent.setStudentNo(studentNo); } if (i == 1) { // ?? studentName = studentCell.toString(); studentName = studentName.trim(); dictStudent.setStudentName(studentName); } if (i == 2) { // ? String className = studentCell.toString(); className = className.trim(); List<DictClass> classes = dictClassDao.getClassByName(className, dictSchool); if (classes != null) { if (classes.size() > 0) { dictClass = classes.get(0); dictStudent.setDictClass(dictClass); } } } if (i == 3) { // ? String memberString = studentCell.toString(); memberString = memberString.trim(); memberString = memberString.replaceAll("", ","); dictStudent.setStuRmark(memberString); } } // end___?? dictStudents.add(dictStudent); } } // end return dictStudents; }
From source file:com.seer.datacruncher.fileupload.Excel_97_FileReadObject.java
License:Open Source License
@Override public String parseStream(long schemaId, InputStream ios) { List<SchemaFieldEntity> listSchemaFields = schemaFieldsDao.listSchemaFields(schemaId); StringBuffer sb = new StringBuffer(); try {/*from w ww. j a va2 s.c om*/ HSSFWorkbook myWorkBook = new HSSFWorkbook(ios); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIter = mySheet.rowIterator(); sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"); int i = 0; while (rowIter.hasNext()) { if (i++ == 0) { //skip table's header rowIter.next(); continue; } int j = 0; sb.append("<" + Tag.TAG_ROOT + ">"); HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator<Cell> cellIter = myRow.cellIterator(); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); String fieldValue = myCell.toString().trim(); if (!listSchemaFields.get(j).getNillable() || fieldValue.length() > 0) { sb.append("<" + listSchemaFields.get(j).getName() + ">") .append(myCell.toString().replaceAll("&", "&")) .append("</" + listSchemaFields.get(j).getName() + ">"); } j++; if (j == listSchemaFields.size() && cellIter.hasNext()) { return I18n.getMessage("error.numberFieldsNoMatch"); } else if (!cellIter.hasNext() && j != listSchemaFields.size()) { return I18n.getMessage("error.numberFieldsNoMatch"); } } sb.append("</" + Tag.TAG_ROOT + ">\n"); } } catch (IOException e) { logger.error("Error occured during fetch records from excel file.", e); return "Could not able to parse Excel file. " + e.getMessage(); } return sb.toString(); }