List of usage examples for org.apache.poi.ss.usermodel Sheet getPhysicalNumberOfRows
int getPhysicalNumberOfRows();
From source file:com.ifeng.vdn.ip.repository.service.impl.AliBatchIPAddressCheckerTest.java
License:Apache License
@Test public void wrap() { Workbook wb = null;/*from w w w .java 2 s . com*/ try { BatchIPAddressChecker<AliIPBean> checker = new AliBatchIPAddressChecker(); wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_18.xlsx")); Sheet sheet = wb.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); Cell ipCell = null; Cell locCell = null; List<String> ips = new ArrayList<String>(); for (int i = 1; i < totalRows; i++) { Row row = sheet.getRow(i); ipCell = row.getCell(1); ips.add(ipCell.getStringCellValue()); } List<AliIPBean> locations = checker.check(ips); String location = ""; for (int i = 1; i < totalRows; i++) { Row row = sheet.getRow(i); locCell = row.getCell(3); try { location = locations.get(i).getIpString(); } catch (Exception e) { location = "ERROR"; } locCell.setCellValue(location); } /*if(location.length() == (totalRows - 1)){ }else { log.error("Batch executed error"); throw new RuntimeException("Batch executed error. Some one IP location not be checked."); }*/ wb.write(new FileOutputStream("src/test/resources/data/ip_18_Alibaba.xlsx")); } catch (InvalidFormatException | IOException e) { e.printStackTrace(); } finally { if (wb != null) try { wb.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:com.ifeng.vdn.ip.repository.service.impl.AliDataFactoryTest.java
License:Apache License
@Test public void importDataFromExcel() { Workbook wb = null;/*from w w w . j a v a 2s . c om*/ String input = "src/test/resources/data/ip_18_Alibaba.xlsx"; try { List<IPModel> ipList = new ArrayList<IPModel>(); wb = WorkbookFactory.create(new FileInputStream(input)); Sheet sheet = wb.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); Cell ipCell = null; Cell totalCell = null; Cell locCell = null; String location = ""; int total; String ipString = ""; String[] items = null; for (int i = 1; i < totalRows; i++) { Row row = sheet.getRow(i); ipCell = row.getCell(1); totalCell = row.getCell(2); locCell = row.getCell(3); try { String country = ""; String area = ""; String region = ""; String city = ""; String isp = ""; IPModel model = new IPModel(); ipString = ipCell.getStringCellValue(); total = (int) totalCell.getNumericCellValue(); location = locCell.getStringCellValue(); items = location.split(" "); if (items != null) { if (items[0] != null) { country = items[0]; } if (items[1] != null) { area = items[1]; } if (items[2] != null) { region = items[2]; } if (items[3] != null) { city = items[3]; } if (items[4] != null) { isp = items[4]; } model.setIp(ipString); model.setCountry(country.trim()); model.setArea(area.trim()); model.setRegion(region.trim()); model.setCity(city.trim()); model.setIsp(isp.trim()); model.setTotal(total); ipList.add(model); } log.info("IP: {}, Total{}, location: {}", ipString, total, location); if ((i % 1000) == 0) { aliDataFactory.importData(ipList); ipList = new ArrayList<IPModel>(); } } catch (Exception e) { e.getMessage(); //log.error(e.getMessage(), e); } } if (ipList.size() > 0) { aliDataFactory.importData(ipList); } } catch (InvalidFormatException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.ifeng.vdn.ip.repository.service.impl.AliIPAddressCheckerTest.java
License:Apache License
public void wrap(String input, String output) { Workbook wb = null;//from ww w.j a v a 2s .co m PrintWriter pw = null; try { pw = new PrintWriter(output, "UTF-8"); IPAddressChecker checker = new AliIPAddressChecker(); wb = WorkbookFactory.create(new FileInputStream(input)); Sheet sheet = wb.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); Cell ipCell = null; Cell locCell = null; String location = ""; String ipString = ""; for (int i = 1; i < totalRows; i++) { Row row = sheet.getRow(i); ipCell = row.getCell(1); locCell = row.getCell(3); try { ipString = ipCell.getStringCellValue(); AliIPBean ip = (AliIPBean) checker.ipcheck(ipString); location = ip.getIpString(); } catch (Exception e) { log.error(e.getMessage(), e); location = "ERROR"; } finally { //locCell.setCellValue(location); pw.append(ipString + " " + location); pw.println(); } } wb.write(new FileOutputStream(output)); } catch (InvalidFormatException | IOException e) { log.error(e.getMessage(), e); } finally { if (wb != null) try { wb.close(); } catch (IOException e) { e.printStackTrace(); } pw.flush(); pw.close(); } }
From source file:com.ifeng.vdn.ip.repository.service.impl.IPAddressBaiduCheckerTest.java
License:Apache License
@Test public void wrap() { Workbook wb = null;//from ww w .jav a 2 s . c o m try { IPAddressBaiduChecker checker = new IPAddressBaiduChecker(); wb = WorkbookFactory.create(new FileInputStream("src/test/resources/data/ip_16.xlsx")); Sheet sheet = wb.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); Cell ipCell = null; Cell locCell = null; for (int i = 1; i < totalRows; i++) { Row row = sheet.getRow(i); ipCell = row.getCell(1); locCell = row.getCell(3); BaiduIPBean ip = (BaiduIPBean) checker.ipcheck(ipCell.getStringCellValue()); List<IPLocation> locations = ip.getData(); if (locations != null && locations.size() > 0) { try { locCell.setCellValue(locations.get(0).getLocation()); } catch (Exception e) { e.printStackTrace(); } } } wb.write(new FileOutputStream("src/test/resources/data/ip_16_loc.xlsx")); } catch (InvalidFormatException | IOException e) { e.printStackTrace(); } finally { if (wb != null) try { wb.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:com.impetus.kvapps.runner.UserBroker.java
License:Apache License
public UserBroker(Sheet sheet) { m_sheet = sheet; m_iNbRows = sheet.getPhysicalNumberOfRows(); }
From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java
public List getAccountListFromExcel() throws FileNotFoundException { List accountList = new ArrayList(); FileInputStream fis = null;/*from ww w . j a v a 2s . co m*/ try { fis = new FileInputStream(FILE_PATH); Workbook workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { AccountDetails account = new AccountDetails(); Row row = (Row) sheet.getRow(j); Iterator cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); if (Cell.CELL_TYPE_STRING == cell.getCellType()) { if (cell.getColumnIndex() == 1) { account.setLastName(cell.getStringCellValue()); } if (cell.getColumnIndex() == 2) { account.setAccountNo(cell.getStringCellValue()); } if (cell.getColumnIndex() == 0) { account.setFirstName(cell.getStringCellValue()); } } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (cell.getColumnIndex() == 3) { account.setAccountBalance((int) cell.getNumericCellValue()); } } } accountList.add(account); } } fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return accountList; }
From source file:com.ncc.excel.ExcelUtil.java
License:Apache License
public List<Row> readExcel(Workbook wb) { Sheet sheet = null; if (onlyReadOneSheet) {//??sheet System.out.println("selectedSheetName:" + selectedSheetName); // ??sheet(?????) sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName); System.out.println(sheet.getSheetName()); } else {/*ww w . j av a2s . c o m*/ for (int i = 0; i < wb.getNumberOfSheets(); i++) {//??Sheet sheet = wb.getSheetAt(i); logger.info(sheet.getSheetName()); for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//?? Row row = sheet.getRow(j); for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//??? System.out.print(row.getCell(k) + "\t"); } System.out.println("---Sheet" + i + "?---"); } } } return null; }
From source file:com.oleke.facebookcrawler.App.java
License:Apache License
public static void main(String[] args) { ExcelAPI xcel = new ExcelAPI(); Sheet stats = xcel.initExcel(statFile, "Facebook Statistics"); Sheet posts = xcel.initExcel(postFile, "Facebook Posts"); if (stats.getPhysicalNumberOfRows() == 0) { xcel.F_Stats_Header(stats);/*from w w w.j av a 2 s . c om*/ } if (posts.getPhysicalNumberOfRows() == 0) { xcel.F_Posts_Header(posts); } FcbkCrawler fb = new FcbkCrawler(accessToken, xcel.readFromFile(dbFile), xcel.readFromFile(crawledFile)); fb.getFriends("me"); System.out.println("Building Crawler Database..."); while (fb.getTGroup().activeCount() > 0) { } System.out.println("Finished"); xcel.writeToFile(dbFile, fb.getFriendsList()); fb.getFriendsList().remove(0); //fb.getCrawled().remove(0); System.out.println("Crawling User Data in Progress..."); int i = 0; for (String id : fb.getFriendsList()) { if (!fb.getCrawled().contains(id)) { System.out.println(i + 1 + " Crawling user " + id); try { User userDetails = fb.getUserDetails(id); int age = fb.getAge(userDetails.getBirthdayAsDate()); String ageRange = fb.getAgeRange(age); String educationLevel = fb.getEducationLevel(userDetails.getEducation()); int noAlbums = fb.getNoAlbums(id); int noGroups = fb.getNoGroups(id); int noPages = fb.getNoPages(id); int noPictures = fb.getNoPictures(id); int noPosts = fb.getNoPosts(id); int noTaggedPosts = fb.getNoTaggedPosts(id); List<Post> user_posts = fb.getPosts(id); Row u_stats = xcel.createRow(stats); xcel.addCell(u_stats, ExcelAPI.about, userDetails.getAbout()); xcel.addCell(u_stats, ExcelAPI.age_range, ageRange); xcel.addCell(u_stats, ExcelAPI.album_no, Integer.toString(noAlbums)); xcel.addCell(u_stats, ExcelAPI.bio, userDetails.getBio()); xcel.addCell(u_stats, ExcelAPI.education_level, educationLevel); xcel.addCell(u_stats, ExcelAPI.name, userDetails.getName()); xcel.addCell(u_stats, ExcelAPI.no_of_groups, Integer.toString(noGroups)); xcel.addCell(u_stats, ExcelAPI.no_pages, Integer.toString(noPages)); xcel.addCell(u_stats, ExcelAPI.no_pictures, Integer.toString(noPictures)); xcel.addCell(u_stats, ExcelAPI.no_posts, Integer.toString(noPosts)); xcel.addCell(u_stats, ExcelAPI.no_tagged_posts, Integer.toString(noTaggedPosts)); xcel.addCell(u_stats, ExcelAPI.political_view, userDetails.getPolitical()); xcel.addCell(u_stats, ExcelAPI.quotes, userDetails.getQuotes()); xcel.addCell(u_stats, ExcelAPI.relationship_status, userDetails.getRelationshipStatus()); xcel.addCell(u_stats, ExcelAPI.religion, userDetails.getReligion()); xcel.addCell(u_stats, ExcelAPI.s_gender, userDetails.getGender()); xcel.addCell(u_stats, ExcelAPI.s_id, userDetails.getId()); for (Post p : user_posts) { Row u_posts = xcel.createRow(posts); xcel.addCell(u_posts, ExcelAPI.p_id, userDetails.getId()); xcel.addCell(u_posts, ExcelAPI.post, p.getMessage()); if (!"status".equals(p.getType())) { xcel.addCell(u_posts, ExcelAPI.post, p.getDescription()); } xcel.addCell(u_posts, ExcelAPI.post_type, p.getType()); xcel.addCell(u_posts, ExcelAPI.p_gender, userDetails.getGender()); } fb.getCrawled().add(id); } catch (Exception ex) { } i++; } } System.out.println("Writing to File"); xcel.commitChanges(statFile, stats.getWorkbook()); xcel.commitChanges(postFile, posts.getWorkbook()); xcel.writeToFile(crawledFile, fb.getCrawled()); System.out.println("Completed!"); }
From source file:com.oleke.facebookcrawler.ExcelAPI.java
License:Apache License
/** * This method creates a new row on a sheet * * @param sh the sheet to write on/*from ww w. j a v a 2 s .c o m*/ * @return Returns a new row */ public Row createRow(Sheet sh) { int rowNo = sh.getPhysicalNumberOfRows(); Row rw = sh.createRow(rowNo); return rw; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importVendorSpecificFuelLog(InputStream is, LinkedHashMap<String, String> vendorSpecificColumns, Long vendor, HashMap<String, Object> additionalVendorData) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {// w ww . j a v a2 s . co m POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, vendorSpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", vendor); FuelVendor fuelVendor = genericDAO.findByCriteria(FuelVendor.class, criterias, "name", false).get(0); boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; !stopParsing && i <= sheet.getPhysicalNumberOfRows() - 1; i++) { LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(fuelVendor.getName()); rowObjects.add(fuelVendor.getCompany().getName()); Row row = sheet.getRow(i); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); // corresponding column not found in actual column list, find in additionalVendorData if (entry.getValue() == -1) { System.out.println("Additional vendor data = " + additionalVendorData); System.out.println("Column " + entry.getKey() + " not found in Vendor Excel, checking in additionalVendorData"); Object cellValueObj = additionalVendorData.get(entry.getKey()); if (cellValueObj != null) { rowObjects.add(cellValueObj); } else { rowObjects.add(StringUtils.EMPTY); } continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null && cellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); stopParsing = true; rowObjects.clear(); break; } rowObjects.add(cellValueObj); } if (!stopParsing) { data.add(rowObjects); } } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }