0
import java.io.FileInputStream;
import java.io.FileOutputStream;    
import java.io.IOException;    
import java.util.List;    
import java.util.concurrent.TimeUnit;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.Select;

public class CityBusRoutes {

    static String cityName;
    static String routeNo;
    static String routeDetails;

    static String routeList;

    public static void main(String[] args) throws IOException {

        // TODO Auto-generated method stub

        System.setProperty("webdriver.chrome.driver",
                "D://Selenium//Selenium Drivers//chromedriver_win32//chromedriver.exe");
        WebDriver driver = new ChromeDriver();

        driver.get("http://www.onefivenine.com/busRoute.dont?method=findBusRoute");

        driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);

        FileInputStream fis = new FileInputStream("D://City bus routes task//routes-list.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sh = wb.getSheetAt(0);

        int totalNoOfRows = sh.getLastRowNum();
        System.out.println("Row count :"+totalNoOfRows);

        for (int i = 1; i < totalNoOfRows; i++) {

            XSSFRow row = sh.getRow(i);
            cityName = new DataFormatter().formatCellValue(row.getCell(0));
            routeNo = new DataFormatter().formatCellValue(row.getCell(1));

            System.out.println("route opened");

            Select ddl1 = new Select(driver.findElement(By.id("cityId")));

            ddl1.selectByVisibleText(cityName);

            Select ddl2 = new Select(driver.findElement(By.xpath(".//*[@id='routeId']")));
            ddl2.selectByVisibleText(routeNo);

            System.out.println("route selected");

            routeDetails=driver.findElement(By.xpath("html/body/table/tbody/tr[3]/td[2]/div[2]/table[2]")).getText();
            routeList=driver.findElement(By.xpath("html/body/table/tbody/tr[3]/td[2]/div[2]/table[3]/tbody/tr/td[1]/table")).getText();

                row.getCell(2).setCellValue(routeDetails);
                row.getCell(3).setCellValue(routeList);

                FileOutputStream fos = new FileOutputStream("D://City bus routes task//routes-list.xlsx");

                wb.write(fos);
                fos.close();
        }
    }   
}

Below is my StackTrace:

Row count :2668
route opened
route selected
route opened
route selected
Exception in thread "main" java.lang.NullPointerException
at CityBusRoutes.main(CityBusRoutes.java:100)

Reading first two cells in first row and writing next two cell in first row,
Reading first two cells in second row also happening but writing into second row is not happening and showing the above error.

OneCricketeer
  • 179,855
  • 19
  • 132
  • 245
Krishna
  • 21
  • 1
  • 6
  • I am not found proper solution for my problem in this link – Krishna Aug 18 '17 at 05:28
  • 1
    Hint: What happens when `driver.findElement` finds nothing? – OneCricketeer Aug 18 '17 at 05:29
  • 3
    Hint 2: `sh.getRow(i)` can return null. Read the JavaDoc to learn why – OneCricketeer Aug 18 '17 at 05:30
  • 1
    getCell too if I remember well –  Aug 18 '17 at 05:32
  • Some background: Modern spreadsheets have 2^14 possible columns and 2^20 possible rows, so 2^34 possible cells. If always all those cells would be present in the files, all files would be very big even without content. So they are not always present. So if the `get...` methods return `null`, the `create...` methods have to be used. – Axel Richter Aug 18 '17 at 05:42
  • Thank you all, i solved my problem by replacing row.createCell() instead of using row.getCell() – Krishna Aug 18 '17 at 06:35

0 Answers0