List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static void populateSFDataStrColValue(SalesForceLicenseData sfld, Cell dataCell, Cell headerCell) { //TODO:: Set sf data object from string column value String cellVal = dataCell.getStringCellValue().trim(); if (headerCell.getStringCellValue().equals(Constants.CUSTOMER_ID)) sfld.setCustomerId(Integer.parseInt(cellVal)); else if ((headerCell.getStringCellValue().equals(Constants.OAS_IMPLEMENTATION_ID))) sfld.setOasImplementationId(getStrValWithDesiredLen(cellVal, Constants.OAS_IMPLEMENTATION_ID_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.IMPL_RECORD_TYPE))) sfld.setImplRecordType(getStrValWithDesiredLen(cellVal, Constants.IMPL_RECORD_TYPE_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CUSTOMER_ACCOUNT_NAME))) sfld.setCustomerAccountName(getStrValWithDesiredLen(cellVal, Constants.CUSTOMER_ACCOUNT_NAME_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.ACCOUNT_STATE))) sfld.setAccountState(getStrValWithDesiredLen(cellVal, Constants.ACCOUNT_STATE_SIZE)); // Need to restrict value to 2 characters only else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_ID))) sfld.setOrgNodeId(Integer.parseInt(cellVal)); else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_NAME))) sfld.setOrgNodeName(getStrValWithDesiredLen(cellVal, Constants.ORG_NODE_NAME_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_PHONE))) sfld.setContactPhone(getStrValWithDesiredLen(cellVal, Constants.CONTACT_PHONE_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT))) sfld.setContact(getStrValWithDesiredLen(cellVal, Constants.CONTACT_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_EMAIL))) sfld.setContactEmail(getStrValWithDesiredLen(cellVal, Constants.CONTACT_EMAIL_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_NAME))) sfld.setCategoryName(getStrValWithDesiredLen(cellVal, Constants.CATEGORY_NAME_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_LEVEL))) sfld.setCategoryLevel(Integer.parseInt(cellVal)); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_MODEL))) sfld.setLicenseModel(getStrValWithDesiredLen(cellVal, Constants.LICENSE_MODEL_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_COUNT))) sfld.setLicenseCount(Integer.parseInt(cellVal)); else if ((headerCell.getStringCellValue().equals(Constants.ORDER_QUANTITY))) sfld.setOrderQuantity(Integer.parseInt(cellVal)); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_DISTRIBUTED_TO))) sfld.setLicenseDistributedTo(getStrValWithDesiredLen(cellVal, Constants.LICENSE_DISTRIBUTED_TO_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CREATED_DATE))) sfld.setCreatedDate(dataCell.getDateCellValue()); else if ((headerCell.getStringCellValue().equals(Constants.INTERVAL_NAME))) sfld.setIntervalName(getStrValWithDesiredLen(cellVal, Constants.INTERVAL_NAME_SIZE)); }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static void populateSFDataNumericColValue(SalesForceLicenseData sfld, Cell dataCell, Cell headerCell) { //TODO:: Set sf data object from numeric column value Double cellVal = dataCell.getNumericCellValue(); if (headerCell.getStringCellValue().equals(Constants.CUSTOMER_ID)) sfld.setCustomerId(cellVal.intValue()); else if ((headerCell.getStringCellValue().equals(Constants.OAS_IMPLEMENTATION_ID))) sfld.setOasImplementationId(/*from ww w .j a va 2 s . com*/ getStrValWithDesiredLen(cellVal.toString(), Constants.OAS_IMPLEMENTATION_ID_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.IMPL_RECORD_TYPE))) sfld.setImplRecordType(getStrValWithDesiredLen(cellVal.toString(), Constants.IMPL_RECORD_TYPE_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CUSTOMER_ACCOUNT_NAME))) sfld.setCustomerAccountName( getStrValWithDesiredLen(cellVal.toString(), Constants.CUSTOMER_ACCOUNT_NAME_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.ACCOUNT_STATE))) sfld.setAccountState(getStrValWithDesiredLen(cellVal.toString(), Constants.ACCOUNT_STATE_SIZE));// Need to restrict value to 2 characters only else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_ID))) sfld.setOrgNodeId(cellVal.intValue()); else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_NAME))) sfld.setOrgNodeName(getStrValWithDesiredLen(cellVal.toString(), Constants.ORG_NODE_NAME_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_PHONE))) sfld.setContactPhone(getStrValWithDesiredLen(cellVal.toString(), Constants.CONTACT_PHONE_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT))) sfld.setContact(getStrValWithDesiredLen(cellVal.toString(), Constants.CONTACT_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_EMAIL))) sfld.setContactEmail(getStrValWithDesiredLen(cellVal.toString(), Constants.CONTACT_EMAIL_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_NAME))) sfld.setCategoryName(getStrValWithDesiredLen(cellVal.toString(), Constants.CATEGORY_NAME_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_LEVEL))) sfld.setCategoryLevel(cellVal.intValue()); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_MODEL))) sfld.setLicenseModel(getStrValWithDesiredLen(cellVal.toString(), Constants.LICENSE_MODEL_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_COUNT))) sfld.setLicenseCount(cellVal.intValue()); else if ((headerCell.getStringCellValue().equals(Constants.ORDER_QUANTITY))) sfld.setOrderQuantity(cellVal.intValue()); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_DISTRIBUTED_TO))) sfld.setLicenseDistributedTo( getStrValWithDesiredLen(cellVal.toString(), Constants.LICENSE_DISTRIBUTED_TO_SIZE)); else if ((headerCell.getStringCellValue().equals(Constants.CREATED_DATE))) sfld.setCreatedDate(dataCell.getDateCellValue()); else if ((headerCell.getStringCellValue().equals(Constants.INTERVAL_NAME))) sfld.setIntervalName(getStrValWithDesiredLen(cellVal.toString(), Constants.INTERVAL_NAME_SIZE)); }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static void populateSFDataBlankColValue(SalesForceLicenseData sfld, Cell dataCell, Cell headerCell) { //TODO:: Set sf data object with blank column value String cellVal = " "; if (headerCell.getStringCellValue().equals(Constants.CUSTOMER_ID)) sfld.setCustomerId(new Integer(0)); else if ((headerCell.getStringCellValue().equals(Constants.OAS_IMPLEMENTATION_ID))) sfld.setOasImplementationId(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.IMPL_RECORD_TYPE))) sfld.setImplRecordType(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CUSTOMER_ACCOUNT_NAME))) sfld.setCustomerAccountName(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.ACCOUNT_STATE))) sfld.setAccountState(cellVal);/*from w w w . java 2 s. c o m*/ else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_ID))) sfld.setOrgNodeId(new Integer(0)); else if ((headerCell.getStringCellValue().equals(Constants.ORG_NODE_NAME))) sfld.setOrgNodeName(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_PHONE))) sfld.setContactPhone(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT))) sfld.setContact(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CONTACT_EMAIL))) sfld.setContactEmail(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_NAME))) sfld.setCategoryName(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CATEGORY_LEVEL))) sfld.setCategoryLevel(null); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_MODEL))) sfld.setLicenseModel(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_COUNT))) sfld.setLicenseCount(null); else if ((headerCell.getStringCellValue().equals(Constants.ORDER_QUANTITY))) sfld.setOrderQuantity(null); else if ((headerCell.getStringCellValue().equals(Constants.LICENSE_DISTRIBUTED_TO))) sfld.setLicenseDistributedTo(cellVal); else if ((headerCell.getStringCellValue().equals(Constants.CREATED_DATE))) sfld.setCreatedDate(dataCell.getDateCellValue()); else if ((headerCell.getStringCellValue().equals(Constants.INTERVAL_NAME))) sfld.setIntervalName(cellVal); }
From source file:com.cx.test.FromHowTo.java
License:Apache License
public static Object getCellValue(Cell cell, Class clazz) { String name = clazz.getSimpleName(); if ("String".equals(name)) { return cell.getStringCellValue(); }//from www . ja v a2 s . c o m if ("Integer".equals(name)) { return cell.getNumericCellValue(); } return null; }
From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java
License:Apache License
/** Returns the new {@link CellValue} from provided {@link Cell}. */ public static ICellValue resolveCellValue(Cell c) { if (c == null) { return CellValue.BLANK; }//from ww w. ja v a2 s . co m switch (c.getCellType()) { case CELL_TYPE_NUMERIC: { return CellValue.from(c.getNumericCellValue()); } case CELL_TYPE_STRING: { return CellValue.from(c.getStringCellValue()); } case CELL_TYPE_BOOLEAN: { return CellValue.from(c.getBooleanCellValue()); } case CELL_TYPE_ERROR: { return CellValue.from(forInt(c.getErrorCellValue()).getString()); } case CELL_TYPE_BLANK: { return CellValue.BLANK; } case CELL_TYPE_FORMULA: { return CellValue.from(String.format("%s%s", FORMULA_PREFIX, c.getCellFormula())); } default: { throw new CalculationEngineException( String.format("Cell's type %s is not supported.", c.getCellType())); } } }
From source file:com.ddt.driver.EXCELDriver.java
private void readOlderVersionExcelFile(String path, String sheetName) { try {/* w w w .j a va 2 s .com*/ HSSFWorkbook workbook; try (FileInputStream file = new FileInputStream(new File(path))) { workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String line = ""; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: line += cell.getBooleanCellValue() + ";"; break; case Cell.CELL_TYPE_NUMERIC: line += String.valueOf(cell.getNumericCellValue()).split("\\.")[0] + ";"; break; case Cell.CELL_TYPE_STRING: line += cell.getStringCellValue() + ";"; break; } } linesOfDriver.add(line); } } } catch (FileNotFoundException e) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, e); } catch (IOException e) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, e); } }
From source file:com.ddt.driver.EXCELDriver.java
private void readNewerVersionExcelFile(String path, String sheetName) { try {// w w w . j a va2 s . c o m try (final FileInputStream file = new FileInputStream(new File(path))) { XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = getSheetName(sheetName, workbook); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String line = ""; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: line += cell.getBooleanCellValue() + ";"; break; case Cell.CELL_TYPE_NUMERIC: line += String.valueOf(cell.getNumericCellValue()).split("\\.")[0] + ";"; break; case Cell.CELL_TYPE_STRING: line += cell.getStringCellValue() + ";"; break; } } linesOfDriver.add(line); } } } catch (FileNotFoundException ex) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java
License:Apache License
@RequestMapping(value = "/s/admin/{eventKey}/uploadRegistration", method = RequestMethod.POST) public ModelAndView uploadGroupRegistration(ModelAndView model, HttpServletRequest request, @PathVariable(value = "eventKey") String eventKey, @Valid @ModelAttribute("registerForm") UploadGroupRegistrationForm uploadForm, BindingResult bindingResult) throws FileNotFoundException, IOException, InvalidFormatException { EventSignup signUp = eventSignupRepository.getByEventKey(eventKey); model.getModelMap().addAttribute("event", signUp.getEvent()); if (bindingResult.hasErrors()) { model.getModelMap().addAttribute("registerForm", uploadForm); model.setViewName("/admin/upload-registration"); } else {/*from ww w .j a va2 s . c o m*/ boolean hasErrors = false; try { Workbook wb = WorkbookFactory.create(uploadForm.getRegistrationFile().getInputStream()); Sheet sheet = wb.getSheetAt(0); Cell contactNameCell = sheet.getRow(0).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell contactEmailCell = sheet.getRow(1).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell contactPhoneCell = sheet.getRow(2).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell registrationReferenceCell = sheet.getRow(3).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); if (contactNameCell.getStringCellValue().isEmpty()) { hasErrors = true; bindingResult.addError(new ObjectError("registrationFile", "Contact Name Empty")); } if (contactEmailCell.getStringCellValue().isEmpty()) { hasErrors = true; bindingResult.addError(new ObjectError("registrationFile", "Contact Email Empty")); } if (contactPhoneCell.getStringCellValue().isEmpty()) { hasErrors = true; bindingResult.addError(new ObjectError("registrationFile", "Contact Phone Empty")); } if (registrationReferenceCell.getStringCellValue().isEmpty()) { hasErrors = true; bindingResult.addError(new ObjectError("registrationFile", "Registration Reference Empty")); } if (!hasErrors) { RegistrationDetails details = new RegistrationDetails(); details.setContactEmailAddress(contactEmailCell.getStringCellValue()); details.setContactName(contactNameCell.getStringCellValue()); details.setContactPhoneNumber(contactPhoneCell.getStringCellValue()); details.setRegistrationFormKey(registrationReferenceCell.getStringCellValue()); details.setEvent(signUp.getEvent()); details.setFinalCost(BigDecimal.ZERO); details.setInvoice("Invoiced"); details.setPaymentState(RegistrationDetails.PaymentState.PAID); int attendeeRowIndex = 7; Row attendeeRow = sheet.getRow(attendeeRowIndex); while (attendeeRow != null) { attendeeRow = sheet.getRow(attendeeRowIndex); if (attendeeRow != null) { Cell firstName = attendeeRow.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell lastName = attendeeRow.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell emailAddress = attendeeRow.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell city = attendeeRow.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell state = attendeeRow.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell country = attendeeRow.getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell ticketType = attendeeRow.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell company = attendeeRow.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell jobTitle = attendeeRow.getCell(8, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell tshirtSize = attendeeRow.getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell vegetarian = attendeeRow.getCell(10, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); Cell sponsorMessages = attendeeRow.getCell(11, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); if (firstName.getStringCellValue().isEmpty()) { break; } if (lastName.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : lastName")); hasErrors = true; break; } if (emailAddress.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : emailAddress")); hasErrors = true; break; } if (city.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : city")); hasErrors = true; break; } if (state.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : state ")); hasErrors = true; break; } if (country.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : country")); hasErrors = true; break; } if (company.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : company")); hasErrors = true; break; } if (jobTitle.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information : jobTitle")); hasErrors = true; break; } if (ticketType.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information: ticket type")); hasErrors = true; break; } if (tshirtSize.getStringCellValue().isEmpty()) { bindingResult.addError(new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information: t shirt ")); hasErrors = true; break; } if (vegetarian.getStringCellValue().isEmpty() || !(vegetarian.getStringCellValue().equalsIgnoreCase("no") || vegetarian.getStringCellValue().equalsIgnoreCase("yes"))) { bindingResult.addError( new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information. Vegetarian option should be yes or no ")); hasErrors = true; break; } if (sponsorMessages.getStringCellValue().isEmpty() || !(sponsorMessages.getStringCellValue().equalsIgnoreCase("no") || sponsorMessages.getStringCellValue().equalsIgnoreCase("yes"))) { bindingResult.addError( new ObjectError("registrationFile", " row " + (attendeeRowIndex + 1) + " missing information. Sponsor message should be yes or no ")); hasErrors = true; break; } TicketOrderDetail detail = new TicketOrderDetail(); detail.setCity(city.getStringCellValue()); detail.setCompany(company.getStringCellValue()); detail.setCouponCode(""); detail.setCountry(country.getStringCellValue()); detail.setEmailAddress(emailAddress.getStringCellValue()); detail.setFirstName(firstName.getStringCellValue()); detail.setJobTitle(jobTitle.getStringCellValue()); detail.setLastName(lastName.getStringCellValue()); detail.setSponsorMayContact( sponsorMessages.getStringCellValue().equalsIgnoreCase("no") ? "false" : "true"); detail.setState(state.getStringCellValue()); detail.setTicketGroup( Long.parseLong(ticketType.getStringCellValue().split("-\\|-")[1].trim())); detail.setLabel(businessService.getTicketGroup(detail.getTicketGroup()).getLabel()); detail.settShirtSize(tshirtSize.getStringCellValue()); detail.setVegetarian( vegetarian.getStringCellValue().equalsIgnoreCase("no") ? "false" : "true"); detail.setRegistration(details); details.getOrderDetails().add(detail); attendeeRowIndex++; } } if (uploadForm.getOverrideRegistration()) { try { RegistrationDetails tempRegistration = businessService .getRegistrationForm(registrationReferenceCell.getStringCellValue()); tempRegistration.getOrderDetails().forEach((oldDetail) -> { oldDetail.setRegistration(null); }); tempRegistration.getOrderDetails().clear(); tempRegistration.getOrderDetails().addAll(details.getOrderDetails()); tempRegistration.getOrderDetails().forEach((detail) -> { detail.setRegistration(tempRegistration); }); details = tempRegistration; businessService.updateRegistration(details, uploadForm.getSendEmail()); } catch (EmptyResultDataAccessException ignore) { businessService.updateRegistration(details, uploadForm.getSendEmail()); } } else { try { RegistrationDetails tempRegistration = businessService .getRegistrationForm(registrationReferenceCell.getStringCellValue()); hasErrors = true; bindingResult.addError(new ObjectError("registrationFile", "Registration with this key exists, please check \"Replace Registrations\".")); } catch (EmptyResultDataAccessException ignore) { businessService.updateRegistration(details, uploadForm.getSendEmail()); } } } } catch (Exception ex) { hasErrors = true; Logger.getAnonymousLogger().log(Level.SEVERE, ex.getMessage(), ex); bindingResult.addError(new ObjectError("registrationFile", ex.getMessage())); } if (hasErrors) { model.setViewName("/admin/upload-registration"); } else { model.setViewName("/admin/index"); } } return model; }
From source file:com.dexter.fms.mbean.FleetMBean.java
License:Open Source License
@SuppressWarnings("unchecked") public void UploadZonMaintenance() { if (getMaintenanceExcel() != null) { GeneralDAO gDAO = new GeneralDAO(); try {// w w w.ja va2 s .c o m ByteArrayInputStream byteIn = new ByteArrayInputStream(getMaintenanceExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { String timestamp = null, unit = null, comment1 = null, comment2 = null, comment3 = null, value = null, updateTime = null; Row row = rowIterator.next(); if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: timestamp = val; break; case 1: unit = val; break; case 2: comment1 = val; break; case 3: comment2 = val; break; case 4: comment3 = val; break; case 5: value = val; break; case 7: updateTime = val; break; } String regNo = null; Vehicle v = null; Vendor vendor = null; try { if (unit != null && unit.contains("-")) { regNo = unit.substring(0, unit.indexOf("-")); } else if (unit != null) regNo = unit; if (regNo != null) regNo = regNo.trim(); } catch (Exception ex) { System.out.println("Reg No parsing exception on row index: " + pos); ex.printStackTrace(); } if (regNo != null) { Query q = gDAO.createQuery( "Select e from Vehicle e where e.registrationNo=:regNo and e.partner.id=:partner_id"); q.setParameter("regNo", regNo); q.setParameter("partner_id", partner_id); Object vobj = gDAO.search(q, 1); if (vobj != null) { v = (Vehicle) vobj; } } if (v != null) { if (comment2 != null && comment2.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Vendor e where e.name=:vname and e.partner.id=:partner_id"); q.setParameter("vname", comment2); q.setParameter("partner_id", partner_id); Object venobjs = gDAO.search(q, 0); if (venobjs != null) { List<Vendor> venlist = (List<Vendor>) venobjs; for (Vendor e : venlist) vendor = e; } if (vendor == null) { vendor = new Vendor(); vendor.setCreatedBy(dashBean.getUser()); vendor.setCrt_dt(new Date()); vendor.setName(comment2); vendor.setPartner(getPartner()); gDAO.save(vendor); } } Date start_dt = null, end_dt = null; if (comment3 != null && comment3.trim().length() > 0) { } if (comment1 != null && comment1.trim().length() > 0 && comment1.startsWith("Routine Service")) { VehicleRoutineMaintenance vrm = new VehicleRoutineMaintenance(); } else if (comment1 != null && comment1.trim().length() > 0) { VehicleAdHocMaintenance vahm = new VehicleAdHocMaintenance(); } } } } pos += 1; } } catch (Exception ex) { ex.printStackTrace(); } gDAO.destroy(); } }
From source file:com.dexter.fms.mbean.FleetMBean.java
License:Open Source License
@SuppressWarnings("unchecked") public void BatchLoadVehicles() { if (getVehiclesBatchExcel() != null) { String naration = "Batch load vehicles: "; GeneralDAO gDAO = new GeneralDAO(); try {/*w ww . j a va2s . c om*/ ByteArrayInputStream byteIn = new ByteArrayInputStream(getVehiclesBatchExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { if (!isLicenseAvailable()) { msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "You have used up your license. Please purchase more license to add more vehicles!"); FacesContext.getCurrentInstance().addMessage(null, msg); break; } Row row = rowIterator.next(); String fleet_nm = "", vehicleType = "", vehicleMaker = "", vehicleModel = "", modelYr = "", trackerID = "", regNo = "", engineNo = "", chassisNo = ""; String department = "", region = ""; String purchase_date = "", purchased_amount = "", purchased_from = "", fuel_type = "", tyre_size = "", tank_capacity = ""; String calibrated_capacity = "", color = "", sim_no = "", unit_of_measurement = "", card_no = ""; if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: fleet_nm = val; break; case 1: vehicleType = val; break; case 2: vehicleMaker = val; break; case 3: vehicleModel = val; break; case 4: modelYr = val; break; case 5: trackerID = val; break; case 6: regNo = val; break; case 7: engineNo = val; break; case 8: chassisNo = val; break; case 9: department = val; break; case 10: region = val; break; case 11: purchase_date = val; break; case 12: purchased_amount = val; break; case 13: purchased_from = val; break; case 14: fuel_type = val; break; case 15: tyre_size = val; break; case 16: tank_capacity = val; break; case 17: calibrated_capacity = val; break; case 18: color = val; break; case 19: sim_no = val; break; case 20: unit_of_measurement = val; break; case 21: card_no = val; break; } } boolean createModel = false; VehicleType vt = null; VehicleMake vm = null; VehicleModel vmd = null; Fleet fleet = null; Department deptObj = null; Region regionObj = null; boolean conti = true; if (regNo != null && regNo.trim().length() > 0) { Vehicle v = null; Query q = gDAO.createQuery( "Select e from Vehicle e where e.registrationNo = :registrationNo"); q.setParameter("registrationNo", regNo); Object vObj = gDAO.search(q, 0); if (vObj != null) { Vector<Vehicle> vList = (Vector<Vehicle>) vObj; for (Vehicle e : vList) v = e; } if (v != null) conti = false; } if (conti) { if (fleet_nm != null && fleet_nm.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Fleet e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", fleet_nm); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Fleet> objsList = (Vector<Fleet>) objs; for (Fleet e : objsList) fleet = e; } if (fleet == null && isAutoCreate()) { fleet = new Fleet(); fleet.setCreatedBy(dashBean.getUser()); fleet.setCrt_dt(new Date()); fleet.setName(fleet_nm); fleet.setPartner(getPartner()); fleet.setDefaultFleet(false); ret = gDAO.save(fleet); if (!ret) break; } else if (fleet == null && !isAutoCreate()) { ret = false; gDAO.setMessage( "Fleet: '" + fleet_nm + "' does not exist for vehicle: " + regNo); break; } } if (department != null && department.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Department e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", department); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Department> objsList = (Vector<Department>) objs; for (Department e : objsList) deptObj = e; } if (deptObj == null && isAutoCreate()) { deptObj = new Department(); deptObj.setCreatedBy(dashBean.getUser()); deptObj.setCrt_dt(new Date()); deptObj.setName(department); deptObj.setPartner(getPartner()); ret = gDAO.save(deptObj); if (!ret) break; } else if (deptObj == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Department: '" + department + "' does not exist for vehicle: " + regNo); break; } } if (region != null && region.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Region e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", region); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Region> objsList = (Vector<Region>) objs; for (Region e : objsList) regionObj = e; } if (regionObj == null && isAutoCreate()) { regionObj = new Region(); regionObj.setCreatedBy(dashBean.getUser()); regionObj.setCrt_dt(new Date()); regionObj.setName(region); regionObj.setPartner(getPartner()); ret = gDAO.save(regionObj); if (!ret) break; } else if (regionObj == null && !isAutoCreate()) { ret = false; gDAO.setMessage( "Region: '" + region + "' does not exist for vehicle: " + regNo); break; } } if (vehicleType != null && vehicleType.trim().length() > 0) { // search for existing vehicle type and maker Query q = gDAO.createQuery("Select e from VehicleType e where e.name = :name"); q.setParameter("name", vehicleType.trim()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleType> vtList = (Vector<VehicleType>) qObj; for (VehicleType e : vtList) vt = e; } if (vt == null && isAutoCreate()) { vt = new VehicleType(); vt.setCreatedBy(dashBean.getUser()); vt.setCrt_dt(new Date()); vt.setName(vehicleType.trim()); vt.setPartner(getPartner()); ret = gDAO.save(vt); if (!ret) break; createModel = true; } else if (vt == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Vehicle Type: '" + vehicleType + "' does not exist for vehicle: " + regNo); break; } } if (vehicleMaker != null && vehicleMaker.trim().length() > 0) { Query q = gDAO.createQuery("Select e from VehicleMake e where e.name = :name"); q.setParameter("name", vehicleMaker.trim()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleMake> vmList = (Vector<VehicleMake>) qObj; for (VehicleMake e : vmList) vm = e; } if (vm == null && isAutoCreate()) { vm = new VehicleMake(); vm.setCreatedBy(dashBean.getUser()); vm.setCrt_dt(new Date()); vm.setName(vehicleMaker.trim()); vm.setPartner(getPartner()); ret = gDAO.save(vm); if (!ret) break; createModel = true; } else if (vm == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Vehicle Make: '" + vehicleMaker + "' does not exist for vehicle: " + regNo); break; } } if (vehicleModel != null && vehicleModel.trim().length() > 0) { if (createModel) { vmd = new VehicleModel(); vmd.setCreatedBy(dashBean.getUser()); vmd.setCrt_dt(new Date()); vmd.setMaker(vm); vmd.setType(vt); vmd.setName(vehicleModel); vmd.setYear(modelYr); vmd.setPartner(getPartner()); ret = gDAO.save(vmd); if (!ret) break; } else { Query q = gDAO.createQuery( "Select e from VehicleModel e where e.name = :name and e.year=:year and e.maker=:maker and e.type=:type"); q.setParameter("name", vehicleModel.trim()); q.setParameter("year", modelYr); q.setParameter("maker", vm); q.setParameter("type", vt); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleModel> vmList = (Vector<VehicleModel>) qObj; for (VehicleModel e : vmList) vmd = e; } if (vmd == null && isAutoCreate()) { vmd = new VehicleModel(); vmd.setCreatedBy(dashBean.getUser()); vmd.setCrt_dt(new Date()); vmd.setMaker(vm); vmd.setType(vt); vmd.setName(vehicleModel); vmd.setYear(modelYr); vmd.setPartner(getPartner()); ret = gDAO.save(vmd); if (!ret) break; } else if (vmd == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Vehicle Model: '" + vehicleModel + "' does not exist for vehicle: " + regNo); break; } } } Vendor ven = null; if (purchased_from != null && purchased_from.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Vendor e where e.name = :name and e.partner=:partner"); q.setParameter("name", vehicleModel.trim()); q.setParameter("partner", getPartner()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<Vendor> vmList = (Vector<Vendor>) qObj; for (Vendor e : vmList) ven = e; } if (ven == null && isAutoCreate()) { ven = new Vendor(); ven.setName(purchased_from); ven.setPartner(getPartner()); ven.setCreatedBy(dashBean.getUser()); ven.setCrt_dt(new Date()); ret = gDAO.save(ven); if (!ret) break; else { ServiceType st = null; q = gDAO.createQuery("Select e from ServiceType e where e.name = :name"); q.setParameter("name", "Vehicle Sales"); qObj = gDAO.search(q, 0); if (qObj != null) { Vector<ServiceType> vmList = (Vector<ServiceType>) qObj; for (ServiceType e : vmList) st = e; } if (st != null) { VendorServices vs = new VendorServices(); vs.setCreatedBy(dashBean.getUser()); vs.setCrt_dt(new Date()); vs.setServiceType(st); vs.setVendor(ven); ret = gDAO.save(vs); if (!ret) break; } } } } if (fleet != null && vt != null && vm != null && vmd != null) { Vehicle v = new Vehicle(); v.setFleet(fleet); v.setActive(true); v.setActiveStatus(VehicleStatusEnum.ACTIVE.getStatus()); v.setChasisNo(chassisNo); v.setCreatedBy(dashBean.getUser()); v.setCrt_dt(new Date()); v.setEngineNo(engineNo); v.setModel(vmd); v.setPartner(getPartner()); v.setRegistrationNo(regNo); v.setZonControlId(trackerID); /*try { v.setZonControlId(Integer.parseInt(trackerID)); } catch(Exception ex){}*/ try { v.setPurchaseAmt(new BigDecimal(Double.parseDouble(purchased_amount))); } catch (Exception ex) { } try { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); v.setPurchaseDate(sdf.parse(purchase_date)); } catch (Exception ex) { } v.setVendor(ven); ret = gDAO.save(v); if (!ret) break; else if (regionObj != null || deptObj != null) { VehicleParameters vpm = new VehicleParameters(); vpm.setRegion(regionObj); vpm.setDept(deptObj); try { vpm.setCalibratedcapacity(Double.parseDouble(calibrated_capacity)); } catch (Exception ex) { } vpm.setCardno(card_no); vpm.setColor(color); vpm.setSimno(sim_no); try { vpm.setTankcapacity(Double.parseDouble(tank_capacity)); } catch (Exception ex) { } vpm.setTyresize(tyre_size); vpm.setUnitofmeasure(unit_of_measurement); if (fuel_type != null && fuel_type.trim().length() > 0) { Query q = gDAO.createQuery("Select e from FuelType e where e.name = :name"); q.setParameter("name", fuel_type); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<FuelType> vmList = (Vector<FuelType>) qObj; for (FuelType e : vmList) vpm.setFuelType(e); } } vpm.setCreatedBy(dashBean.getUser()); vpm.setCrt_dt(new Date()); vpm.setVehicle(v); ret = gDAO.save(vpm); if (!ret) break; } } } } else pos += 1; } if (ret) { gDAO.commit(); naration += ", Status: Success"; msg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Success: ", "All vehicles created successfully."); FacesContext.getCurrentInstance().addMessage(null, msg); setVehicleMakes(null); setVehicleTypes(null); setVmodels(null); } else { gDAO.rollback(); naration += ", Status: Failed: " + gDAO.getMessage(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "Failed to create all vehicles: " + gDAO.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } catch (Exception ex) { ex.printStackTrace(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Severe error occured. " + ex.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } finally { gDAO.destroy(); dashBean.saveAudit(naration, "", null); } } }