List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getNumericCellValue
public double getNumericCellValue()
From source file:com.netxforge.netxstudio.data.importer.HSSFMetricValuesImporter.java
License:Open Source License
@Override protected Date getDateCellValue(int rowNum, int column) { Date value = null;//from w w w . j a v a 2 s .co m HSSFCell cell = currentSheet.getRow(rowNum).getCell(column); int cellType = cell.getCellType(); if (cellType == HSSFCell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) { double cellValue = cell.getNumericCellValue(); value = HSSFDateUtil.getJavaDate(cellValue); } } return value; }
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. * // www . ja v a 2s . 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; }//w w w . j ava 2 s.c om 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.proem.exm.service.wholesaleGroupPurchase.customer.impl.CustomerInfoServiceImpl.java
@SuppressWarnings("static-access") private String getValue(HSSFCell hssfCell) { if (hssfCell != null) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { // //from w ww . ja va2 s .c o m return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { // return String.valueOf(hssfCell.getNumericCellValue()); } else { // return String.valueOf(hssfCell.getStringCellValue()); } } else { return null; } }
From source file:com.pureinfo.studio.db.xls2srm.impl.XlsObjectsImpl.java
License:Open Source License
/** * @see com.pureinfo.dolphin.model.IObjects#next() *//*from w ww .j ava 2 s . c o 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 ww w .ja va2 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/*from w w w.ja va2s . co m*/ * @param req * @return */ public Object eopCSVUpload(Event event, HttpServletRequest req) { log.log(Level.INFO, "EOPMaintenance --> eopCSVUpload"); if (event == null) { event = new Event(); return event; } LocaleObject localeObj = (LocaleObject) req.getSession().getAttribute(SessionAttributes.LOCALE_OBJ); // ResourceBundle msgProps = ResourceBundle.getBundle("configurations"); // String eopPath = msgProps.getString("EopPath"); Map<String, String> configurationMap = (Map<String, String>) req.getSession() .getAttribute(ApplicationAttribute.CONFIGURATION_PROPERTIES_MAP); //String eopPath = configurationMap.get("EopPath"); String csv_file_name = ""; int record_created = 0; String tempDir = System.getProperty("java.io.tmpdir"); if (req.getSession().getAttribute("csv_file_name") != null) { csv_file_name = (String) req.getSession().getAttribute("csv_file_name"); } MsgObject msgObj = null; StringBuffer strbuf = new StringBuffer(); LMSUtil lmsUtil = new LMSUtil(); int sucessCnt = 0; int m = 0; int l = 0; int rowCount = 0; try { String serverFilename = "resources/upload/Excel" + "/" + "EOP_" + LMSUtil.getRendomToken(); File uploadedFolder = new File(serverFilename); if (!uploadedFolder.exists()) { uploadedFolder.mkdirs(); } if (csv_file_name != null && !csv_file_name.equals("")) { byte[] bytearray = (byte[]) req.getSession().getAttribute("csv_byte_session"); if (csv_file_name.contains(".xlsx") || csv_file_name.contains(".XLSX") || csv_file_name.contains(".xls") || csv_file_name.contains(".XLS")) { try { FileOutputStream stream = new FileOutputStream(serverFilename + "/" + csv_file_name); stream.write(bytearray); stream.close(); } catch (Exception e) { e.printStackTrace(); } } File temp_file = new File(tempDir + "/" + csv_file_name); FileUtils.deleteFileNFolder(temp_file); // Retrieve datas from CSV /*FileInputStream fileInputStream = new FileInputStream(new File(serverFilename+"/"+csv_file_name)); BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(fileInputStream));*/ BufferedReader bufferedReader = new BufferedReader( new InputStreamReader(new FileInputStream(serverFilename + "/" + csv_file_name), "UTF-8")); String records = null; String interviewDate = "", startTime = ""; int buCode = 0, distCode = 0, branchCode = 0; String cityCode = 0 + "", sscCode = 0 + "", officeCode = 0 + ""; ImoUtilityData imoUtilityData = new ImoUtilityData(); String officeName = ""; User userObj = (User) req.getSession().getAttribute("currUserObj"); event.setCreationDate(new Date()); event.setCreatedBy(userObj.getStaffLoginId()); ; event.setModificationDate(new Date()); event.setModifiedBy(userObj.getStaffLoginId()); ; event.setStatus(true); AuditTrailMaintenance auditTrailMaintenance = new AuditTrailMaintenance(); ArrayList<Channel> channelList = ImoUtilityData.getActiveChannels(req); boolean flag = false; FileInputStream fis = null; fis = new FileInputStream(serverFilename + "/" + csv_file_name); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator rows = sheet.rowIterator(); int cellCount = 0; while (rows.hasNext()) { rowCount++; HSSFRow row = (HSSFRow) rows.next(); Iterator cells = row.cellIterator(); List data = new ArrayList(); while (cells.hasNext()) { if (cellCount == 0) { break; } HSSFCell cell = (HSSFCell) cells.next(); System.out.println("cell contenct " + cell); if (cellCount == 1) { if ("eop".equals(cell.toString())) event.setEventType(cell.toString()); else if ("companyevent".equals(cell.toString())) event.setEventType(cell.toString()); else if ("networking".equals(cell.toString())) event.setEventType(cell.toString()); else if ("training".equals(cell.toString())) event.setEventType(cell.toString()); else { flag = true; strbuf.append(localeObj.getTranslatedText("Event Type Value is Wrong") + rowCount); strbuf.append("\n"); break; } } /* if(cellCount==2){ event.setTopic(cell.toString()); }*/ if (cellCount == 2) { event.setEventName(cell.toString()); event.setTopic(cell.toString()); } if (cellCount == 3) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required DATE at row number") + rowCount); strbuf.append("\n"); break; } else { Date today = new Date(); String toDayStr = LMSUtil.convertDateToString(today); today = LMSUtil.convertStringToDate(toDayStr); interviewDate = cell.getDateCellValue() + ""; interviewDate = LMSUtil.convertExcelDateToString(interviewDate); Date intDate = LMSUtil.convertExcelDateToDate(interviewDate); if (lmsUtil.validateDDMMYYYY(interviewDate)) { event.setEventDate(intDate); if (intDate.before(today)) { flag = true; strbuf.append(localeObj.getTranslatedText( "Event Date Should Not Be Before Today at line number") + rowCount); strbuf.append("\n"); break; } /*if(checkDuplicateEvent(event)){ flag=true; strbuf.append("Schedule already Exists.Duplicate Schedule Not Allowed at line Number "+l); strbuf.append("\n"); break; }*/ } else { flag = true; strbuf.append(localeObj.getTranslatedText( "Date Format Invalid.Should Be DD/MM/YYYY at line number") + l); strbuf.append("\n"); } } } if (cellCount == 4) { startTime = cell.getDateCellValue() + ""; event.setStartTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime))); } if (cellCount == 5) { String endTime = cell.getDateCellValue() + ""; Date e1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime); Date s1 = LMSUtil.converExcelTimetoDateHHMMAMPM1(startTime); if (e1.before(s1) || e1.equals(s1)) { flag = true; strbuf.append(localeObj.getTranslatedText( "End Time should be earlier than Start Time at row number") + rowCount); break; } else { event.setEndTime((LMSUtil.converExcelTimetoDateHHMMAMPM1(endTime))); } } if (cellCount == 6) { event.setEopDescription(cell.toString()); } if (cellCount == 7) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required LOCATION at row number") + rowCount); strbuf.append("\n"); break; } else { event.setLocation(cell.toString()); } } if (cellCount == 8) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required SPEAKER at row number") + rowCount); strbuf.append("\n"); break; } else { event.setSpeaker(cell.toString()); } } if (cellCount == 9) { int estimate = (int) cell.getNumericCellValue(); event.setEstimatedCandidates(estimate); if (estimate == 0) { event.setEstimatedCandidates(0); } } if (cellCount == 10) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required OPEN TO at row number") + rowCount); strbuf.append("\n"); break; } else { if (cell.toString().equalsIgnoreCase("Y")) { event.setOpenTo("Y"); } else if (cell.toString().equalsIgnoreCase("N")) { event.setOpenTo("N"); } else { flag = true; strbuf.append(localeObj.getTranslatedText( "Required Valid (Y/N) OPEN TO at row number") + rowCount); strbuf.append("\n"); break; } } } if (cellCount == 11) { if (cell.toString().equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText( "Required OPEN TO PUBLIC REGISTRATION at row number") + rowCount); strbuf.append("\n"); break; } else { event.setOpenToRegistration(cell.toString()); } } if (cellCount == 12) { if (cell.toString().equals("")) { flag = true; strbuf.append( localeObj.getTranslatedText("Required ORGANIZER at row number") + rowCount); strbuf.append("\n"); break; } else { if (!(cell.toString().equalsIgnoreCase("CHO") || cell.toString().equalsIgnoreCase("BU") || cell.toString().equalsIgnoreCase("District") || cell.toString().equalsIgnoreCase("City") || cell.toString().equalsIgnoreCase("SSC") || cell.toString().equalsIgnoreCase("Agent Team"))) { flag = true; strbuf.append(localeObj.getTranslatedText( "ORGANIZER value is incorrect at line number") + rowCount); strbuf.append("\n"); break; } else { if (cell.toString().equalsIgnoreCase("CHO")) event.setOrganizer(1); else if (cell.toString().equalsIgnoreCase("BU")) event.setOrganizer(2); else if (cell.toString().equalsIgnoreCase("District")) event.setOrganizer(3); else if (cell.toString().equalsIgnoreCase("Branch")) event.setOrganizer(7); else if (cell.toString().equalsIgnoreCase("City")) event.setOrganizer(4); else if (cell.toString().equalsIgnoreCase("SSC")) event.setOrganizer(5); else if (cell.toString().equalsIgnoreCase("Office")) event.setOrganizer(8); else if (cell.toString().equalsIgnoreCase("Agent Team")) event.setOrganizer(6); } } } if (cellCount == 13) { if (cell.toString().equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required BU at row number") + rowCount); strbuf.append("\n"); break; } else { buCode = imoUtilityData.getBuCodeBasedOnBuName(cell.toString()); if (buCode == 0) { flag = true; strbuf.append( localeObj.getTranslatedText("Invalid BU Name row number") + rowCount); strbuf.append("\n"); break; } else { event.setBuCode(buCode); event.setBuName(cell.toString()); } } } if (cellCount == 14) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setDistrict(0); event.setDistName(localeObj.getTranslatedText("ALL")); } else { distCode = imoUtilityData.getDistrictCodeBasedOnDistrictName(cell.toString()); if (distCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid District Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, 0, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "District Name Not available In this Bu at row number") + rowCount); break; } event.setDistrict(distCode); event.setDistName(cell.toString()); } } } if (cellCount == 15) { //cell.setCellType(1); if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setBranchCode(0); event.setBranchName(localeObj.getTranslatedText("ALL")); } else { branchCode = imoUtilityData.getBranchCodeBasedOnBranchName(cell.toString()); if (branchCode == 0) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Branch Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, "0", "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Branch Name Not available In this District at row number") + rowCount); break; } event.setBranchCode(branchCode); event.setBranchName(cell.toString()); } } } if (cellCount == 16) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setCityCode("0"); event.setCityName(localeObj.getTranslatedText("ALL")); } else { cityCode = imoUtilityData.getCityCodeBasedOnDistrictName(cell.toString()); if (cityCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid City Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, "0", "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "City Name Not available In this Branch at row number") + rowCount); break; } event.setCityCode(cityCode); event.setCityName(cell.toString()); } } } if (cellCount == 17) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setSscCode("0"); event.setSscName(localeObj.getTranslatedText("ALL")); } else { sscCode = imoUtilityData.getSSCCodeBasedOnSSCName(cell.toString()); if (sscCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid SSC Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, "0"); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Ssc Name Not available In this City at row number") + rowCount); break; } event.setSscCode(sscCode); event.setSscName(cell.toString()); } } } if (cellCount == 18) { if (cell.toString().equals("0") || cell.toString().equals("0.0")) { event.setOfficeCode("0"); event.setOfficeName(localeObj.getTranslatedText("ALL")); } else { officeCode = imoUtilityData.getOfficeCodeBasedOnSSCName(cell.toString()); if (officeCode.equals("0")) { flag = true; strbuf.append(localeObj.getTranslatedText("Invalid Office Name at row number") + rowCount); strbuf.append("\n"); break; } else { flag = imoUtilityData.checkLevelOfCode(buCode, distCode, branchCode, cityCode, sscCode, officeCode); if (flag == true) { strbuf.append(localeObj.getTranslatedText( "Office Name Not available In this SSC at row number") + rowCount); break; } event.setOfficeCode(officeCode); event.setOfficeName(cell.toString()); } } } if (cellCount == 19) { if (cell.toString().equals("")) { flag = true; strbuf.append(localeObj.getTranslatedText("Required AGENT TEAM at row number") + rowCount); strbuf.append("\n"); break; } else { event.setAgentTeam(cell.toString()); } } cellCount++; } //cell while loop if (cellCount == 0) { cellCount++; } else { if (flag == true) { cellCount = 1; flag = false; strbuf.append("\n"); continue; } else { if (userObj.getUserType().equals("AD")) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } else { if (userObj.isSscLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0") && !sscCode.equals("0")) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } else if (userObj.isCityLevel()) { if (buCode > 0 && distCode > 0 && !cityCode.equals("0")) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } else if (userObj.isDistrictLevel()) { if (buCode > 0 && distCode > 0) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } else if (userObj.isBuLevel()) { if (buCode > 0) { record_created = insertEventCsv(event); sucessCnt++; cellCount = 1; } } } if (record_created > 0) { auditTrailMaintenance.insertAuditTrail( new AuditTrail(userObj.getStaffLoginId() + "", AuditTrail.MODULE_EOP, AuditTrail.FUNCTION_CREATE, event.toString())); } } flag = false; } } //end while loop } if (strbuf.length() > 0) { ImoUtilityData imoutill = new ImoUtilityData(); imoutill.summaryReport(strbuf, req); } } catch (Exception e) { log.log(Level.SEVERE, e.getMessage()); LogsMaintenance logsMain = new LogsMaintenance(); e.printStackTrace(); StringWriter errors = new StringWriter(); e.printStackTrace(new PrintWriter(errors)); logsMain.insertLogs("EopMaintenance", Level.SEVERE + "", errors.toString()); } int failedCnt = rowCount - sucessCnt; req.getSession().removeAttribute("csv_file_name"); req.getSession().setAttribute("strbuf", strbuf); req.getSession().setAttribute("formObj", new PathDetail().getFormObj("EopUploadCSV")); req.setAttribute("CacheName", "EOP"); //if(sucessCnt!=0){ String uploadSucessString = localeObj.getTranslatedText("Number of records uploaded successfully"); String recoredFailString = localeObj.getTranslatedText("Number of records fail"); return new ErrorObject(uploadSucessString + " :" + sucessCnt + " <br> " + recoredFailString + " :" + (failedCnt - 2) + " ", "", localeObj); //}else{ //return new ErrorObject("The new schedule csv file uploaded Successfully", ""); //} }
From source file:com.quix.aia.cn.imo.mapper.InterviewMaintenance.java
License:Open Source License
/** * <p>This method performs multiple Interviews Upload through CSV File</p> * @param interviewObj Interview object/*from w ww . j a va 2s. 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.raisepartner.chartfusion.generator.XLSParser.java
License:Open Source License
/** * Extractes a String value from a cell/*from w ww. j ava 2 s . com*/ * @param cell is an XLS cell * @param defaultValue is the default value returned if cell does not * contains data (null or empty String). * @return a String instance or the default value */ public String getStringValue(HSSFCell cell, String defaultValue) { if (cell == null) { return defaultValue; } else { String s; try { s = cell.getStringCellValue(); } catch (RuntimeException e) { return "" + cell.getNumericCellValue(); } if (s == null || s.length() == 0) { return defaultValue; } else { return s; } } }
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
protected boolean processCell(HSSFCell cell, Element tableCellElement, int normalWidthPx, int maxSpannedWidthPx, float normalHeightPt) { final HSSFCellStyle cellStyle = cell.getCellStyle(); String value;/*w w w . j ava2 s . c om*/ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: // XXX: enrich value = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_FORMULA: /*switch (evaluator.evaluateFormulaCell(cell)) { case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: value = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: System.out.println(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: break; }*/ switch (cell.getCachedFormulaResultType()) { case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString str = cell.getRichStringCellValue(); if (str != null && str.length() > 0) { value = (str.toString()); } else { value = ExcelToHtmlUtils.EMPTY; } break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle style = cellStyle; if (style == null) { value = String.valueOf(cell.getNumericCellValue()); } else { value = (_formatter.formatRawCellContents(cell.getNumericCellValue(), style.getDataFormat(), style.getDataFormatString())); } break; case HSSFCell.CELL_TYPE_BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: value = ErrorEval.getText(cell.getErrorCellValue()); break; default: logger.log(POILogger.WARN, "Unexpected cell cachedFormulaResultType (" + cell.getCachedFormulaResultType() + ")"); value = ExcelToHtmlUtils.EMPTY; break; } break; case HSSFCell.CELL_TYPE_BLANK: value = ExcelToHtmlUtils.EMPTY; break; case HSSFCell.CELL_TYPE_NUMERIC: value = _formatter.formatCellValue(cell); break; case HSSFCell.CELL_TYPE_BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: value = ErrorEval.getText(cell.getErrorCellValue()); break; default: logger.log(POILogger.WARN, "Unexpected cell type (" + cell.getCellType() + ")"); return true; } final boolean noText = ExcelToHtmlUtils.isEmpty(value); final boolean wrapInDivs = !noText && isUseDivsToSpan() && !cellStyle.getWrapText(); final short cellStyleIndex = cellStyle.getIndex(); if (cellStyleIndex != 0) { HSSFWorkbook workbook = cell.getRow().getSheet().getWorkbook(); String mainCssClass = getStyleClassName(workbook, cellStyle); if (wrapInDivs) { tableCellElement.setAttribute("class", mainCssClass + " " + cssClassContainerCell); } else { tableCellElement.setAttribute("class", mainCssClass); } if (noText) { /* * if cell style is defined (like borders, etc.) but cell text * is empty, add " " to output, so browser won't collapse * and ignore cell */ value = "\u00A0"; } } if (isOutputLeadingSpacesAsNonBreaking() && value.startsWith(" ")) { StringBuilder builder = new StringBuilder(); for (int c = 0; c < value.length(); c++) { if (value.charAt(c) != ' ') break; builder.append('\u00a0'); } if (value.length() != builder.length()) builder.append(value.substring(builder.length())); value = builder.toString(); } Text text = htmlDocumentFacade.createText(value); if (wrapInDivs) { Element outerDiv = htmlDocumentFacade.createBlock(); outerDiv.setAttribute("class", this.cssClassContainerDiv); Element innerDiv = htmlDocumentFacade.createBlock(); StringBuilder innerDivStyle = new StringBuilder(); innerDivStyle.append("position:absolute;min-width:"); innerDivStyle.append(normalWidthPx); innerDivStyle.append("px;"); if (maxSpannedWidthPx != Integer.MAX_VALUE) { innerDivStyle.append("max-width:"); innerDivStyle.append(maxSpannedWidthPx); innerDivStyle.append("px;"); } innerDivStyle.append("overflow:hidden;max-height:"); innerDivStyle.append(normalHeightPt); innerDivStyle.append("pt;white-space:nowrap;"); ExcelToHtmlUtils.appendAlign(innerDivStyle, cellStyle.getAlignment()); htmlDocumentFacade.addStyleClass(outerDiv, cssClassPrefixDiv, innerDivStyle.toString()); innerDiv.appendChild(text); outerDiv.appendChild(innerDiv); tableCellElement.appendChild(outerDiv); } else { tableCellElement.appendChild(text); } return ExcelToHtmlUtils.isEmpty(value) && cellStyleIndex == 0; }