List of usage examples for org.apache.poi.ss.usermodel Row cellIterator
Iterator<Cell> cellIterator();
From source file:com.asakusafw.testdriver.excel.ExcelSheetDataModelSource.java
License:Apache License
private Map<PropertyName, Integer> extractProperties() throws IOException { // first row must be property names Row row = sheet.getRow(0); if (row == null) { throw new IOException( MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.errorInvalidHeader"), //$NON-NLS-1$ id));/*from w ww.jav a 2s. c om*/ } nextRowNumber = 1; Map<PropertyName, Integer> results = new LinkedHashMap<>(); for (Iterator<Cell> iter = row.cellIterator(); iter.hasNext();) { Cell cell = iter.next(); int type = cell.getCellType(); if (type == Cell.CELL_TYPE_BLANK) { continue; } if (type != Cell.CELL_TYPE_STRING || cell.getStringCellValue().isEmpty()) { throw new IOException( MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.errorInvalidHeaderCell"), //$NON-NLS-1$ id, cell.getColumnIndex() + 1)); } String name = cell.getStringCellValue(); PropertyName property = toPropertyName(cell, name); if (definition.getType(property) == null) { throw new IOException( MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.errorMissingProperty"), //$NON-NLS-1$ definition.getModelClass().getName(), property, id, cell.getColumnIndex() + 1)); } results.put(property, cell.getColumnIndex()); } if (results.isEmpty()) { throw new IOException( MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.errorEmptyProperty"), //$NON-NLS-1$ id)); } return results; }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception { System.out.println("hiiii heree......."); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);// www . j a v a 2s . c o m fout.flush(); fout.close(); try { String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = ""; String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "", locEmail = ""; String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = ""; int isLicense = 0; int isactive = 1; int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; String[] data = null; List<String> dataStatus = new ArrayList<String>(); int val = 0; //Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { EmpName = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { EmpName = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); val++; } try { DepartmentID = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { DepartmentID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); val++; } try { LocationID = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { LocationID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Location -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); val++; } try { locCoun = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { locCoun = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Country - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country- " + e.getMessage()); val++; } try { locstate = hsrow.getCell((short) 4).getStringCellValue(); } catch (Exception e) { locstate = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location State - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State- " + e.getMessage()); val++; } try { loccity = hsrow.getCell((short) 5).getStringCellValue(); } catch (Exception e) { loccity = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location City -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City- " + e.getMessage()); val++; } try { locContact = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { locContact = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Contact Name -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name- " + e.getMessage()); val++; } try { locEmail = hsrow.getCell((short) 7).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and validated the supplied // address which is this case is an email address. InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { locEmail = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Email -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); val++; } try { divisions = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { divisions = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); val++; } try { address = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { address = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); val++; } try { city = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); val++; } try { zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue()); zip = String.valueOf(zip).split("\\.")[0]; } catch (Exception e) { zip = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 12)); phone1 = df2; phone1 = String.valueOf(phone1).split("\\.")[0]; } catch (Exception e) { phone1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 13)); phone2 = df2; System.out.println("check phone1" + phone2); phone2 = String.valueOf(phone2).split("\\.")[0]; } catch (Exception e) { phone2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 - " + e.getMessage()); val++; } try { boolean isValid = false; Email1 = hsrow.getCell((short) 14).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); val++; } try { boolean isValid = false; Email2 = hsrow.getCell((short) 15).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); val++; } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + hsrow.getRowNum() + "Not Inserted Row : " + EmpName); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } //Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { EmpName = c.toString(); } catch (Exception e) { EmpName = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": EmpName - " + e.getMessage()); } } if (c.getColumnIndex() == 1) { try { DepartmentID = c.toString(); } catch (Exception e) { DepartmentID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Department - " + e.getMessage()); } } if (c.getColumnIndex() == 2) { try { LocationID = c.toString(); } catch (Exception e) { LocationID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Location -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { locCoun = c.toString(); } catch (Exception e) { locCoun = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Country - " + e.getMessage()); } } if (c.getColumnIndex() == 4) { try { locstate = c.toString(); } catch (Exception e) { locstate = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location State - " + e.getMessage()); } } if (c.getColumnIndex() == 5) { try { loccity = c.toString(); } catch (Exception e) { loccity = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location City -" + e.getMessage()); } } if (c.getColumnIndex() == 6) { try { locContact = c.toString(); } catch (Exception e) { locContact = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Contact Name -" + e.getMessage()); } } if (c.getColumnIndex() == 7) { try { locEmail = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": locEmail - " + e.getMessage()); } } catch (Exception e) { locEmail = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Email -" + e.getMessage()); } } if (c.getColumnIndex() == 8) { try { divisions = c.toString(); } catch (Exception e) { divisions = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Divisions - " + e.getMessage()); } } if (c.getColumnIndex() == 9) { try { address = c.toString(); } catch (Exception e) { address = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": address - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { city = c.toString(); } catch (Exception e) { city = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": city - " + e.getMessage()); } } if (c.getColumnIndex() == 11) { try { zip = c.toString(); } catch (Exception e) { zip = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": zip - " + e.getMessage()); } } if (c.getColumnIndex() == 12) { try { DataFormatter formatter = new DataFormatter(); phone1 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone1 = df2; } catch (Exception e) { phone1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone1 - " + e.getMessage()); } } if (c.getColumnIndex() == 13) { try { DataFormatter formatter = new DataFormatter(); phone2 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone2 = df2; } catch (Exception e) { phone2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone2 -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { Email1 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { Email2 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName); notcount = notcount + 1; } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { dataStatus.add(notcount + " Rows are not inserted"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("organization/employees", map); }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importAssets(HttpServletRequest req, HttpServletResponse res) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);/*ww w . java 2 s. co m*/ fout.flush(); fout.close(); try { String Assets = "", category = "", location = "", application = "", business = ""; String initial = "", quantity = "", country = "", state = "", city = "", contact = "", email = ""; List<String> dataStatus = new ArrayList<String>(); int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; // Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); System.out.println("check this...hsrow." + hsrow.getRowNum()); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { Assets = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": AssetName - " + e.getMessage()); } } try { category = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Category - " + e.getMessage()); } } try { application = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Application - " + e.getMessage()); } } try { business = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } try { initial = String.valueOf(hsrow.getCell((short) 4).getNumericCellValue()); initial = String.valueOf(initial).split("\\.")[0]; } catch (Exception e) { initial = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": InitialValue - " + e.getMessage()); } } try { quantity = String.valueOf(hsrow.getCell((short) 5).getNumericCellValue()); quantity = String.valueOf(quantity).split("\\.")[0]; } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Quantity - " + e.getMessage()); } } try { location = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); } } try { country = hsrow.getCell((short) 7).getStringCellValue(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country - " + e.getMessage()); } } try { state = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State - " + e.getMessage()); } } try { city = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City - " + e.getMessage()); } } try { contact = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } try { email = hsrow.getCell((short) 11).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an email // address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { email = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email - " + e.getMessage()); } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println("Error Row : " + hsrow.getRowNum() + "Not Inserted Row Asset Name : " + Assets); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } // Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { Assets = c.toString(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": AssetName - " + e.getMessage()); } } } if (c.getColumnIndex() == 1) { try { category = c.toString(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Category - " + e.getMessage()); } } } if (c.getColumnIndex() == 2) { try { application = c.toString(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Application - " + e.getMessage()); } } } if (c.getColumnIndex() == 3) { try { business = c.toString(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } } if (c.getColumnIndex() == 4) { try { initial = c.toString(); } catch (Exception e) { initial = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": InitialValue - " + e.getMessage()); } } } if (c.getColumnIndex() == 5) { try { quantity = c.toString(); } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Quantity - " + e.getMessage()); } } } if (c.getColumnIndex() == 6) { try { location = c.toString(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location - " + e.getMessage()); } } } if (c.getColumnIndex() == 7) { try { country = c.toString(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Country - " + e.getMessage()); } } } if (c.getColumnIndex() == 8) { try { state = c.toString(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location State - " + e.getMessage()); } } } if (c.getColumnIndex() == 9) { try { city = c.toString(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location City - " + e.getMessage()); } } } if (c.getColumnIndex() == 10) { try { contact = c.toString(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } } if (c.getColumnIndex() == 11) { try { email = c.toString(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an // email address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } catch (Exception e) { email = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row Asset Name : " + Assets); } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("assests/assets", map); }
From source file:com.aurel.track.lucene.util.poi.XLSTextStripper.java
License:Open Source License
public XLSTextStripper(FileInputStream fis, String fileExtension) { try {//from w w w. java2 s .c o m StringBuffer sb = new StringBuffer(); Workbook workbook = null; if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLS.equalsIgnoreCase(fileExtension)) { workbook = new HSSFWorkbook(fis); } else { if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLSX.equalsIgnoreCase(fileExtension)) { workbook = new XSSFWorkbook(fis); } } if (workbook != null) { int numOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellStringValue = null; if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { boolean booleanValue = cell.getBooleanCellValue(); cellStringValue = Boolean.toString(booleanValue); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { double doubleValue = cell.getNumericCellValue(); cellStringValue = Double.toString(doubleValue); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStringValue = cell.getStringCellValue(); } if (cellStringValue != null) { sb.append(cellStringValue); sb.append("\t"); } } sb.append("\n"); } } } _text = sb.toString(); } catch (Exception e) { LOGGER.error(ExceptionUtils.getStackTrace(e)); } }
From source file:com.avaya.plds.excel.ExcelRead.java
public List<String> getPoeticProductFamily() { System.out.println(" Inside of getPoeticProductFamily "); sheet = xssfWorkbook.getSheetAt(3);// w w w . j av a2s.c o m List<String> family = new ArrayList<String>(); List<String> familyCodes = new ArrayList<String>(); List<String> pld = new ArrayList<String>(); // pld product line description List<String> plc = new ArrayList<String>(); // plc product line code rowIterator = sheet.iterator(); int count = 0; int appCodeCount = 0; int appNameCount = 0; boolean status = false; while (rowIterator.hasNext()) { Row row = rowIterator.next(); cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().equals("NEW FAMILY Configurations")) { status = true; count++; } else if (cell.getStringCellValue().equals("NPI Pre-Configuration Data only")) { status = true; count += 2; appCodeCount = 0; appNameCount = 0; System.out.println("cell value : tetsing " + cell.getStringCellValue()); } else if (count > 3 && count < 6) { if (count == 4) { family.add(cell.getStringCellValue().trim()); } else { familyCodes.add(cell.getStringCellValue().trim()); } System.out.print(cell.getStringCellValue() + "\t"); } else if (count == 6) { status = false; } else if (count > 9 && count < 12) { if (count == 10) { if (appNameCount < 2) { pld.add(cell.getStringCellValue()); appNameCount++; } } else { if (appCodeCount < 2) { plc.add(cell.getStringCellValue()); appCodeCount++; } } } } } if (status) { count++; } } this.storeMap(family, familyCodes, 0); this.storeMap(pld, plc, 1); for (String key : getProductFamilyListValueMap.keySet()) { System.out.println("key = " + key + " and value =" + getProductFamilyListValueMap.get(key)); } for (String key : getApplicationListValueMap.keySet()) { System.out.println("key = " + key + " and value =" + getApplicationListValueMap.get(key)); } return null; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private static boolean match(Row row1, Row row2) { if (row1.getLastCellNum() != row2.getLastCellNum() || row1.getFirstCellNum() != row2.getFirstCellNum()) { return false; }//from w ww .j a v a 2 s . com //p("rows " + row1.getRowNum() + "/" + row2.getRowNum() + " MIGHT be a match"); Iterator cs1 = row1.cellIterator(); Iterator cs2 = row2.cellIterator(); while (cs1.hasNext()) { HSSFCell c1 = (HSSFCell) cs1.next(); if (!cs2.hasNext()) { return false; } HSSFCell c2 = (HSSFCell) cs2.next(); if (c1.getCellNum() != c2.getCellNum()) return false; if (c1.getCellType() != c2.getCellType()) return false; } return true; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private boolean hasNumericCell(Row row) { //eval.setCurrentRow(row); // Workaround for forumula evaluator bug Iterator<Cell> ci = row.cellIterator(); while (ci.hasNext()) { switch (getType(ci.next())) { case INTEGER: case DOUBLE: return true; }/*from w w w. j a va 2 s. c o m*/ } return false; }
From source file:com.beans.AddressBookMBean.java
private ArrayList<Addressbook> readExcel(UploadedFile file) { ArrayList<Addressbook> adbooks = new ArrayList<Addressbook>(); InputStreamReader reader = null; try {//from ww w . ja va2 s.c om File myFile = new File(file.getFileName()); FileUtils.copyInputStreamToFile(file.getInputstream(), myFile); FileInputStream fis = new FileInputStream(myFile); // Finds the workbook instance for XLSX file XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); // Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file while (rowIterator.hasNext()) { Addressbook add = new Addressbook(); Row row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int i = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cell.setCellType(Cell.CELL_TYPE_STRING); System.out.print("Cell:" + i + ":" + cell.getStringCellValue() + "\t"); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (i == 0) { add.setPhonenumber(cell.getStringCellValue()); } else { add.setName(cell.getStringCellValue()); } break; case Cell.CELL_TYPE_NUMERIC: if (i == 0) { cell.setCellType(Cell.CELL_TYPE_STRING); add.setPhonenumber(cell.getStringCellValue()); } System.out.print(cell.getStringCellValue() + "\t"); break; default: } i++; } if (add.getPhonenumber() != null) { adbooks.add(add); } } System.out.println(""); } catch (IOException ex) { Logger.getLogger(AddressBookMBean.class.getName()).log(Level.SEVERE, null, ex); } finally { } return adbooks; }
From source file:com.binlist.binlistreader.BinlistReader.java
public static void main(String[] args) { String result[] = null;/*from w w w . j a v a 2 s . c om*/ String folder = "/opt/"; String sourceFileName = "binlist.xls"; FileInputStream fis = null; try { fis = new FileInputStream(folder + sourceFileName); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator = null; Row row = null; Cell cell = null; int cellNo = 0; String binno = ""; String longUrl = ""; String shortUrl = ""; int rownum = 0; while (rowIterator.hasNext()) { rownum++; if (rownum == 1) continue; row = rowIterator.next(); cellIterator = row.cellIterator(); cellNo = 0; binno = ""; longUrl = ""; shortUrl = ""; cell = row.getCell(4); if (cell != null && cell.getStringCellValue() != null) { binno = cell.getStringCellValue(); } if (binno != null && binno.length() > 5) { result = postRequestV3(binno); System.out.println("rownum..:" + rownum + " binno..:" + binno + " result..:" + result.length); if (result != null) { row.getCell(5).setCellValue(checkNull(result[0], "")); row.getCell(6).setCellValue(checkNull(result[1], "")); row.getCell(7).setCellValue(checkNull(result[2], "")); } } } System.out.println("rownum..:" + rownum); fis.close(); FileOutputStream out = new FileOutputStream(folder + "newfile/" + sourceFileName); workbook.write(out); out.close(); workbook.close(); workbook = null; } catch (Exception e) { e.printStackTrace(); } finally { if (fis != null) try { fis.close(); } catch (IOException ex) { Logger.getLogger(BinlistReader.class.getName()).log(Level.SEVERE, null, ex); } fis = null; } }
From source file:com.bizosys.dataservice.dao.ReadXLS.java
License:Apache License
@Override protected List<String> populate() throws SQLException { checkCondition();/* w w w .j a va 2 s . c o m*/ Workbook workbook = getWorkbook(); Sheet sheet = workbook.createSheet(); ResultSetMetaData md = rs.getMetaData(); int totalCol = md.getColumnCount(); String[] cols = createLabels(md, totalCol); try { if (null != templateFile) { File templateFileObject = new File(templateFile); if (templateFileObject.exists()) { Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject)); Sheet templatesheet = templateWorkbook.getSheetAt(0); Iterator<Row> rowIterator = templatesheet.iterator(); while (rowIterator.hasNext()) { Row templateRow = rowIterator.next(); Row row = sheet.createRow(startRowIndex++); Iterator<Cell> cellIterator = templateRow.cellIterator(); while (cellIterator.hasNext()) { Cell templateCell = cellIterator.next(); Cell cell = row.createCell(templateCell.getColumnIndex()); cell.setCellType(templateCell.getCellType()); switch (templateCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: cell.setCellValue(templateCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: cell.setCellValue(templateCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setCellValue(templateCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(templateCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(templateCell.getStringCellValue()); break; } } } } else { System.err.println("Can not read " + templateFileObject.getAbsolutePath()); } } while (this.rs.next()) { createRecord(totalCol, cols, sheet); } workbook.write(out); } catch (IOException ex) { throw new SQLException(ex); } return null; }