List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getDateCellValue
public Date getDateCellValue()
From source file:com.ms.commons.test.datareader.impl.ExcelReadUtil.java
License:Open Source License
/** * @param name column name of table in which this cell locates. */// w w w .ja v a 2 s .co m @SuppressWarnings("deprecation") protected static MemoryField readCellValue(String name, HSSFCell cell) { MemoryField field = null; if (cell == null) { field = new MemoryField(name, MemoryFieldType.Null); } else { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: case HSSFCell.CELL_TYPE_FORMULA: if (getCellValueForFormula(cell)) { field = new MemoryField(name, MemoryFieldType.Date, cell.getDateCellValue().toString()); } else { field = new MemoryField(name, MemoryFieldType.Number, cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: field = new MemoryField(name, MemoryFieldType.String, cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: field = new MemoryField(name, MemoryFieldType.Null); break; default: field = new MemoryField(name, MemoryFieldType.Unknow, cell.getStringCellValue()); break; } } return field; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
/** * This is a helper method to retrieve the value of a cell regardles of its * type, which will be converted into a String. * /* ww w .j av a 2 s . c o m*/ * @param cell * @return */ private Object getCellValue(HSSFCell cell) { if (cell == null) { return null; } Object result = null; int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; break; case HSSFCell.CELL_TYPE_ERROR: result = "ERROR: " + cell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle cellStyle = cell.getCellStyle(); short dataFormat = cellStyle.getDataFormat(); // assumption is made that dataFormat = 14, // when cellType is HSSFCell.CELL_TYPE_NUMERIC // is equal to a DATE format. if (dataFormat == 164) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } break; case HSSFCell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; default: break; } if (result instanceof Double) { return String.valueOf(((Double) result).longValue()); } if (result instanceof Date) { return result; } return result.toString(); }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
private Object getCellValue(HSSFCell cell, boolean resolveFormula) { if (cell == null) { return null; }//from ww w . ja va2 s . c o m Object result = null; int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; break; case HSSFCell.CELL_TYPE_ERROR: result = "ERROR: " + cell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case HSSFCell.CELL_TYPE_NUMERIC: /*System.out.println("Last evaluated as: " + cell.getNumericCellValue()); result = cell.getNumericCellValue(); break;*/ if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } System.out.println("Numeric cell value == " + result); break; case HSSFCell.CELL_TYPE_STRING: System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\""); result = cell.getRichStringCellValue(); break; } //result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle cellStyle = cell.getCellStyle(); short dataFormat = cellStyle.getDataFormat(); System.out.println("Data format for " + cell.getColumnIndex() + " = " + dataFormat); // assumption is made that dataFormat = 14, // when cellType is HSSFCell.CELL_TYPE_NUMERIC // is equal to a DATE format. //if (dataFormat == 165 || dataFormat == 164 || dataFormat == 14) { if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else { result = cell.getNumericCellValue(); } if (dataFormat == 0) { // alternative way of getting value : can this be replaced for the entire block result = new HSSFDataFormatter().formatCellValue(cell); } System.out.println("Numeric cell value == " + result); break; case HSSFCell.CELL_TYPE_STRING: //result = cell.getStringCellValue(); result = cell.getRichStringCellValue(); System.out.println("String -> " + result); break; default: break; } if (result instanceof Integer) { return String.valueOf((Integer) result); } else if (result instanceof Double) { return String.valueOf(((Double) result)); //.longValue()); } if (result instanceof Date) { return result; } return result.toString(); }
From source file:com.pureinfo.studio.db.xls2srm.impl.XlsObjectsImpl.java
License:Open Source License
/** * @see com.pureinfo.dolphin.model.IObjects#next() */// w ww . j a v a 2 s. co m public DolphinObject next() throws PureException { HSSFRow row = m_sheet.getRow(m_nCurrent++); if (row == null) return null; //else DolphinObject obj = new DolphinObject(); Object oValue; HSSFCell cell; int nCellNum = row.getLastCellNum(); if (nCellNum > m_heads.length) { nCellNum = m_heads.length; } for (int i = 0; i < nCellNum; i++) { cell = row.getCell((short) i); if (cell == null) { oValue = null; } else { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: oValue = new Double(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: oValue = cell.getStringCellValue(); if (oValue != null) oValue = ((String) oValue).trim(); break; case HSSFCell.CELL_TYPE_FORMULA: oValue = new Double(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: oValue = new Boolean(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: throw new PureException(PureException.INVALID_VALUE, "error value in cell[" + i + "]-" + m_heads[i] + ": " + String.valueOf(cell.getErrorCellValue())); //case HSSFCell.CELL_TYPE_BLANK: default: oValue = null; }//endcase if (oValue instanceof Number) { int nFormat = cell.getCellStyle().getDataFormat(); if (nFormat >= 0xe && nFormat <= 0x16) { oValue = cell.getDateCellValue(); } else if (nFormat == 1) { oValue = new Long(((Number) oValue).intValue()); } } } obj.setProperty(m_heads[i], oValue); } return obj; }
From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java
License:Open Source License
private String getCellData(HSSFCell cell, int j) throws Exception { // String format = loginBean.getUserPreferences().getDateFormat(); String s = ""; ESupplyDateUtility fomater = null;/*from w w w.j a v a 2 s . c o m*/ try { fomater = new ESupplyDateUtility(); if (cell != null) { if (cell != null) { if (cell.CELL_TYPE_STRING == cell.getCellType()) { s = cell.getStringCellValue(); } else if (cell.CELL_TYPE_NUMERIC == cell.getCellType()) { // if( HSSFDateUtil.isCellDateFormatted(cell)) if (j == 6 || j == 7) { SimpleDateFormat format = new SimpleDateFormat(); format.applyPattern("dd/MMM/yy"); s = format.format(cell.getDateCellValue()); // s = cell.getDateCellValue(); } else { s = String.valueOf(cell.getNumericCellValue()).trim(); } } // else if (cell.CELL_TYPE_BLANK == cell.getCellType()) s = ""; else if (cell.CELL_TYPE_ERROR == cell.getCellType()) s = ""; else s = ""; } } } catch (Exception e) { e.printStackTrace(); throw e; } return s; }
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 . java 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 www . j a v a 2 s .c om*/ * @param requestParameters Servlet Request Parameter */ public Object cSVUpload(Interview interviewObj, HttpServletRequest requestParameters) { LocaleObject localeObj = (LocaleObject) requestParameters.getSession() .getAttribute(SessionAttributes.LOCALE_OBJ); if (interviewObj == null) { interviewObj = new Interview(); return interviewObj; } // ResourceBundle msgProps = ResourceBundle.getBundle("configurations"); // String interviewPath = msgProps.getString("InterviewPath"); Map<String, String> configurationMap = (Map<String, String>) requestParameters.getSession() .getAttribute(ApplicationAttribute.CONFIGURATION_PROPERTIES_MAP); //String interviewPath = configurationMap.get("InterviewPath"); String csv_file_name = ""; if (requestParameters.getSession().getAttribute("csv_file_name") != null) { csv_file_name = (String) requestParameters.getSession().getAttribute("csv_file_name"); } requestParameters.getSession().removeAttribute("csv_file_name"); String tempDir = System.getProperty("java.io.tmpdir"); Session session = null; int record_created = 0; StringBuffer strbuf = new StringBuffer(); int sucessCnt = 0; int m = 0; int l = 0; int rowCount = 0; try { String serverFilename = "resources/upload/Excel" + "/" + "INT_" + LMSUtil.getRendomToken(); File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } if (csv_file_name != null && !csv_file_name.equals("")) { byte[] bytearray = (byte[]) requestParameters.getSession().getAttribute("csv_byte_session"); if (csv_file_name.contains(".xlsx") || csv_file_name.contains(".XLSX") || csv_file_name.contains(".xls") || csv_file_name.contains(".XLS")) { try { FileOutputStream stream = new FileOutputStream(serverFilename + "/" + csv_file_name); stream.write(bytearray); stream.close(); } catch (Exception e) { e.printStackTrace(); LogsMaintenance logsMain = new LogsMaintenance(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("InterViewMaintenance", Level.SEVERE + "", errors.toString()); } } //delete temp directory File temp_file = new File(tempDir + "/" + csv_file_name); FileUtils.deleteFileNFolder(temp_file); // Retrieve datas from CSV /*FileInputStream fileInputStream = new FileInputStream(new File(serverFilename+"/"+csv_file_name)); BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream));*/ BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(new FileInputStream(serverFilename + "/" + csv_file_name), "UTF-8")); String records = null; String interviewType = "", interviewSessionName = "", interviewDate = "", startTime = "", endTime = "", location = "", interviewMaterial = "", estimatedCondidates = "", buName = "", distName = "", cityName = "", sscName = "", branchName = "", officeName = ""; int buCode = 0, distCode = 0, branchCode = 0; String cityCode = "", sscCode = "", officeCode = ""; session = HibernateFactory.openSession(); ImoUtilityData imoUtilityData = new ImoUtilityData(); User userObj = (User) requestParameters.getSession().getAttribute("currUserObj"); interviewObj.setCreationDate(new Date()); interviewObj.setCreatedBy(userObj.getStaffLoginId()); ; interviewObj.setModificationDate(new Date()); interviewObj.setModifiedBy(userObj.getStaffLoginId()); ; interviewObj.setStatus(true); interviewObj.setToken(LMSUtil.getRendomToken()); AuditTrailMaintenance auditTrailMaintenance = new AuditTrailMaintenance(); boolean flag = false; LMSUtil lmsUtil = new LMSUtil(); FileInputStream fis = null; fis = new FileInputStream(serverFilename + "/" + csv_file_name); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { rowCount++; HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { if (cellCount == 0) { break; } HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell); if (cellCount == 1) { interviewType = cell.toString(); if (interviewType.equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required Interview Type at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setInterviewType(interviewType); } } if (cellCount == 2) { interviewSessionName = cell.toString(); interviewObj.setInterviewSessionName(interviewSessionName); if (interviewSessionName.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required Interview Session Name at row number") + rowCount); strbuf.append("\n"); break; } /*else if(checkDuplicateInterview(interviewObj)){ flag=true; strbuf.append("Duplicate Interview Name at row number "+rowCount); break; }*/ else { interviewObj.setInterviewSessionName(interviewSessionName); } } if (cellCount == 3) { //System.out.println("date --> "+cell.getDateCellValue()+""); interviewDate = cell.getDateCellValue() + ""; interviewDate = LMSUtil.convertExcelDateToString(interviewDate); Date intDate = LMSUtil.convertExcelDateToDate(interviewDate); if (interviewType.equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required Interview Date at row number") + rowCount); strbuf.append("\n"); break; } else { Date today = new Date(); String toDayStr = LMSUtil.convertDateToString(today); today = LMSUtil.convertStringToDate(toDayStr); if (lmsUtil.validateDDMMYYYY(interviewDate)) { interviewObj.setInterviewDate(intDate); if (intDate.before(today)) { flag = true; strbuf.append(localeObj.getTranslatedText( "Interview Date should not be before Today at row number") + rowCount); strbuf.append("\n"); break; } } else { flag = true; strbuf.append(localeObj.getTranslatedText( "Date Format Invalid.Should Be DD/MM/YYYY at line number") + l); strbuf.append("\n"); break; } } } if (cellCount == 4) { startTime = cell.getDateCellValue() + ""; if (interviewType.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required StartTime at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setStartTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime))); } } if (cellCount == 5) { endTime = cell.getDateCellValue() + ""; Date e1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime); Date s1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime); if (interviewType.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required EndTime at row number") + rowCount); strbuf.append("\n"); break; } else if (e1.before(s1) || e1.equals(s1)) { flag = true; strbuf.append(localeObj.getTranslatedText( "End Time should be earlier than Start Time at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setEndTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime))); } } if (cellCount == 6) { location = cell.toString(); if (location.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required LOCATION at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setLocation(location); } } if (cellCount == 7) { interviewMaterial = cell.toString(); if (interviewMaterial.equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required Interview Material at row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setInterviewMaterial(interviewMaterial); } } if (cellCount == 8) { // estimatedCondidates = cell.getNumericCellValue()+""; int estimate = (int) cell.getNumericCellValue(); System.out.println("esimate time " + estimate); // if(estimatedCondidates.equals("")){ // interviewObj.setEstimatedCondidates(0); // } // else if(!LMSUtil.validInt(estimatedCondidates)){ // flag=true; // strbuf.append("Estimated Candidates Should be a Number at row number "+rowCount); // break; // } interviewObj.setEstimatedCondidates(estimate); } if (cellCount == 9) { buName = cell.toString(); if (buName.equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required BU at row number") + rowCount); strbuf.append("\n"); break; } else { buCode = imoUtilityData.getBuCodeBasedOnBuName(buName); if (buCode == 0) { flag = true; strbuf.append( localeObj.getTranslatedText("Invalid BU Name row number") + rowCount); strbuf.append("\n"); break; } else { interviewObj.setBuCode(buCode); } } } if (cellCount == 10) { distName = cell.toString(); if (distName.equals("")) { interviewObj.setDistrict(0); } else { distCode = imoUtilityData.getDistrictCodeBasedOnDistrictName(distName); if (distCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid District Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, 0, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "District Name Not available In this Bu at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setDistrict(distCode); } } } if (cellCount == 11) { branchName = cell.toString(); if (branchName.equals("")) { interviewObj.setBranchCode(0); } else { branchCode = imoUtilityData.getBranchCodeBasedOnBranchName(branchName); if (branchCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Branch Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Branch Name Not available In this District at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setBranchCode(branchCode); } } } if (cellCount == 12) { cityName = cell.toString(); if (cityName.equals("")) { interviewObj.setCityCode("0"); } else { cityCode = imoUtilityData.getCityCodeBasedOnDistrictName(cityName); if (cityCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid City Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "City Name Not available In this Branch at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setCityCode(cityCode); } } } if (cellCount == 13) { sscName = cell.toString(); if (sscName.equals("")) { interviewObj.setSscCode("0"); } else { sscCode = imoUtilityData.getSSCCodeBasedOnSSCName(sscName); if (sscCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid SSC Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Ssc Name Not available In this City at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setSscCode(sscCode); } } } if (cellCount == 14) { officeName = cell.toString(); if (officeName.equals("")) { interviewObj.setOfficeCode("0"); } else { officeCode = imoUtilityData.getOfficeCodeBasedOnSSCName(officeName); if (officeCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Office Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, officeCode); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Office Name Not available In this SSC at row number") + rowCount); strbuf.append("\n"); break; } interviewObj.setOfficeCode(officeCode); } } } cellCount++; } // cell while loop if (cellCount == 0) { cellCount++; } else { if (flag == true) { cellCount = 1; flag = false; strbuf.append("\n"); continue; } else { //insertion done here if (userObj.getUserType().equals("AD")) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } else { if (userObj.isOfficeLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0 && !officeCode.equals("0")) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isSscLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0") && branchCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isCityLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && branchCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isBranchLevel()) { if (buCode > 0 && distCode > 0 && branchCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isDistrictLevel()) { if (buCode > 0 && distCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } else if (userObj.isBuLevel()) { if (buCode > 0) { record_created = insertInterview(interviewObj, requestParameters); sucessCnt++; cellCount = 1; } } } if (record_created > 0) { auditTrailMaintenance.insertAuditTrail( new AuditTrail(userObj.getStaffLoginId() + "", AuditTrail.MODULE_INTERVIEW, AuditTrail.FUNCTION_CREATE, interviewObj.toString())); } } flag = false; } } // row whicle loop if (strbuf.length() > 0) { ImoUtilityData imoutill = new ImoUtilityData(); imoutill.summaryReport(strbuf, requestParameters); } } } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } finally { try { //HibernateFactory.close(session); } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); e.printStackTrace(); } } int failedCnt = rowCount - sucessCnt; requestParameters.getSession().setAttribute("strbuf", strbuf); requestParameters.getSession().setAttribute("formObj", new PathDetail().getFormObj("InterviewUploadCSV")); requestParameters.setAttribute("CacheName", "Interview"); //if(sucessCnt!=0){ String uploadSucessString = localeObj.getTranslatedText("Number of records uploaded successfully"); String recoredFailString = localeObj.getTranslatedText("Number of records fail"); return new ErrorObject( uploadSucessString + ":" + sucessCnt + " <br> " + recoredFailString + " :" + (failedCnt - 2) + " ", "", localeObj); //}else{ //return new ErrorObject("The new Holiday csv file uploaded Successfully", ""); //} }
From source file:com.verticon.treatment.poi.handlers.PoiUtils.java
License:Open Source License
private static Date getDateValue(HSSFRow row, EAttribute feature, int index) { Date result = null;// w w w . java 2s . c om if (index != -1) { try { HSSFCell cellContents = row.getCell(index); if (cellContents != null) { switch (cellContents.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: result = cellContents.getDateCellValue(); break; default: System.err.printf("Wrong type for date %s%n", cellContents.getCellType()); break; } } } catch (RuntimeException e) { // just fall through and return a null } } return result; }
From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java
License:Open Source License
private Date manageDate(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException { Date result = null;//from ww w. j av a 2 s . c o m if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { if (cell.getStringCellValue().trim().length() > 0) { DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); // 2012-06-01 hh:mm:ss java.util.Date parsedUtilDate; try { parsedUtilDate = formater.parse(cell.getStringCellValue()); result = new java.sql.Date(parsedUtilDate.getTime()); } catch (ParseException e) { e.printStackTrace(); } if (result != null) { if (ps != null) ps.setDate(lfdCol, result); if (psUpdate != null) psUpdate.setDate(lfdCol, result); return result; } } } else { result = new Date(cell.getDateCellValue().getTime()); if (ps != null) ps.setDate(lfdCol, result); if (psUpdate != null) psUpdate.setDate(lfdCol, result); return result; } if (ps != null) ps.setNull(lfdCol, java.sql.Types.DATE); if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.DATE); return result; }
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private Object getValue(HSSFCell cell, CellType cellType) { switch (cellType) { case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else {/*from w w w . j av a 2s . c o m*/ return cell.getNumericCellValue(); } case FORMULA: return getValue(cell, cell.getCachedFormulaResultTypeEnum()); case BOOLEAN: return cell.getBooleanCellValue(); case STRING: return cell.getStringCellValue(); case ERROR: return cell.getErrorCellValue(); default: return null; // do not handle Formular, Error, Blank, ... } }