List of usage examples for org.apache.poi.hssf.usermodel HSSFRow cellIterator
@Override
public Iterator<Cell> cellIterator()
From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter.java
License:Open Source License
private boolean isEmptyRow(HSSFRow row) { if (row == null) { return true; }//from w ww . ja v a 2 s . co m final Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { final Cell cell = cellIterator.next(); final String value = cell.getStringCellValue(); if (value != null && value.trim().length() > 0) { return false; } } return true; }
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 ww w . j a v a2 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//from w ww . j a v a 2 s . c om * @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/* w w w. j ava 2 s . 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 w w . java 2 s . co 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 /*from ww w. j av a 2 s . c o 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.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 ww w. j a v a2 s.com*/ 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(); }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to read the data's from an excel file. * @param fileName - Name of the excel file. *///ww w. j a v a 2 s .c o m private List readExcelFile() { List cellDataList = new ArrayList(); try { FileInputStream fileInputStream = new FileInputStream(this.fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet hssfSheet = workBook.getSheetAt(0); Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); cellTempList.add(hssfCell); } cellDataList.add(cellTempList); } } catch (Exception e) { WmLog.printMessage("Can not read XLs file=" + this.fileName); e.printStackTrace(); } //printToConsole(cellDataList); return cellDataList; }
From source file:Compras.ComparaCotizacion.java
void importaDatos(Workbook wb, int col) { Sheet hoja = wb.getSheetAt(0);//w ww . j a va 2s .com Iterator rowIterator = hoja.rowIterator(); List renglones = new ArrayList(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); List celdas = new ArrayList(); Iterator iterator = hssfRow.cellIterator(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); celdas.add(hssfCell); } renglones.add(celdas); } for (int r = 8; r < renglones.size(); r++) { List aux = (List) renglones.get(r); HSSFCell auxCell = (HSSFCell) aux.get(0); HSSFCell auxCell1 = (HSSFCell) aux.get(1); int res = busca(auxCell.getNumericCellValue(), auxCell1.getNumericCellValue()); if (res != -1) { for (int c = 0; c < aux.size(); c++) { HSSFCell valor = (HSSFCell) aux.get(c); switch (valor.getColumnIndex()) { case 4: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: t_datos.setValueAt("", res, col); break; default: t_datos.setValueAt("", res, col); break; } break; case 7: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt(valor.getNumericCellValue(), res, col + 1); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 1); break; default: t_datos.setValueAt("", res, col + 1); break; } if (t_datos.getValueAt(res, col + 1).toString().compareTo("") != 0) { model.setCeldaEditable(res, col + 3, true); model.setCeldaEditable(res, col + 4, true); } break; case 10: switch (valor.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: t_datos.setValueAt((int) valor.getNumericCellValue(), res, col + 2); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: t_datos.setValueAt(valor.getStringCellValue(), res, col + 2); break; default: t_datos.setValueAt("", res, col + 2); break; } break; } t_datos.setValueAt(false, res, col + 3); t_datos.setValueAt(false, res, col + 4); } } } }
From source file:Control.CtrlCredencial.java
/** * This method is used to read the data's from an excel file. * * @param fileName - Name of the excel file. *///w ww. ja v a 2s.co m public ArrayList readExcelFile(String fileName) { /** * Create a new instance for cellDataList */ List cellDataList = new ArrayList(); try { /** * Create a new instance for FileInputStream class */ FileInputStream fileInputStream = new FileInputStream(fileName); /** * Create a new instance for POIFSFileSystem class */ //POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); /* * Create a new instance for HSSFWorkBook Class */ HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet hssfSheet = workbook.getSheet("Pregrado - 2013-02"); /** * Iterate the rows and cells of the spreadsheet to get all the * datas. */ Iterator rowIterator = hssfSheet.rowIterator(); while (rowIterator.hasNext()) { HSSFRow hssfRow = (HSSFRow) rowIterator.next(); Iterator iterator = hssfRow.cellIterator(); List cellTempList = new ArrayList(); while (iterator.hasNext()) { HSSFCell hssfCell = (HSSFCell) iterator.next(); cellTempList.add(hssfCell); // } cellDataList.add(cellTempList); } } catch (Exception e) { e.printStackTrace(); } /** * Call the printToConsole method to print the cell data in the console. */ return credencialesToArray(cellDataList); }