How can i write test result of Excel based login script with Selenium webdriver

Question

I am completely new to selenium as well as Java coding and I have executed excel based login script now I want to write test result into same excel : My Excel has 3 columns : Username, Password, Result

My Code trail :

public class Excel_Read_Write {


    public static void main(String[] args)throws Exception  
    {
        // TODO Auto-generated method stub
         System.setProperty("webdriver.chrome.driver","C:\\chromedriver.exe");
         WebDriver driver = new ChromeDriver();
         // To Maximize browser screen      
//          driver.manage().window().maximize();    
            String baseUrl = "http://openspace.website/vpms/public/";
            driver.get(baseUrl);
            driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
            String FilePath = "F:\\\\LoginDetails.xlsx";
            XSSFWorkbook wb = new XSSFWorkbook(FilePath);
            XSSFSheet s1 = wb.getSheetAt(0);
            int totalNoOfRows  = s1.getLastRowNum();
            int noOfColumns = s1.getRow(0).getPhysicalNumberOfCells();
            System.out.println(noOfColumns);
            System.out.println(noOfColumns);
            XSSFCell username,password,resultCell ;
             for(int i = 1; i <= totalNoOfRows ; i++) {

                resultCell= s1.getRow(i).getCell(2);
                System.out.println("Reading Cell : "+resultCell);
                        username= s1.getRow(i).getCell(0);
                        password = s1.getRow(i).getCell(1);
                        System.out.println("Username : "+username);
                        System.out.println("Password : "+password);
                        driver.findElement(By.id("email")).clear();
                        driver.findElement(By.id("password")).clear();
                        driver.manage().timeouts().implicitlyWait(3, TimeUnit.SECONDS);
                        driver.findElement(By.id("email")).sendKeys(username.toString());
                        driver.findElement(By.id("password")).sendKeys(password.toString());
                        driver.findElement(By.xpath("/html/body/div/div/div[3]/section/form/div[3]/button")).click();
                        driver.manage().timeouts().implicitlyWait(15, TimeUnit.SECONDS);
                         if ((driver.getPageSource().contains("Check In"))){
                                System.out.println("Successful Login verified for " +username.toString());
                                driver.findElement(By.xpath("//*[@id=\"bs-example-navbar-collapse-1\"]/ul/li[4]/a")).click();
//                              resultCell.setCellValue("PASS");
                                driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
                            } else {
                                System.out.println("Successful Login not verified for " +username.toString());
//                              resultCell.setCellValue("FAIL");
                            }
             }

              FileOutputStream outFile =new FileOutputStream(FilePath);
              wb.write(outFile);
    }
}

Error message dismayed in :

Reading Cell : null
Username : QA Master
Password : 123456
Successful Login verified for QA Master
Reading Cell : null
Username : Vidya
Password : 123456
Successful Login verified for Vidya
Reading Cell : null
Username : Swati
Password : admin123
Successful Login not verified for Swati
Reading Cell : null
Username : swati123
Password : admin123
Successful Login verified for swati123
Reading Cell : null
Username : swati123
Password : admin123
Successful Login verified for swati123
Exception in thread "main" org.apache.poi.POIXMLException: java.lang.NullPointerException
    at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:147)
    at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:225)
    at excel_read_write.Excel_Read_Write.main(Excel_Read_Write.java:65)
Caused by: java.lang.NullPointerException
    at org.apache.poi.openxml4j.util.ZipSecureFile$ThresholdInputStream.read(ZipSecureFile.java:211)
    at com.sun.org.apache.xerces.internal.impl.XMLEntityManager$RewindableInputStream.read(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLEntityManager.setupCurrentEntity(Unknown Source)
    at com.sun.org.apache.xerces.internal.impl.XMLVersionDetector.determineDocVersion(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(Unknown Source)
    at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(Unknown Source)
    at javax.xml.parsers.DocumentBuilder.parse(Unknown Source)
    at org.apache.poi.util.DocumentHelper.readDocument(DocumentHelper.java:140)
    at org.apache.poi.POIXMLTypeLoader.parse(POIXMLTypeLoader.java:163)
    at org.openxmlformats.schemas.officeDocument.x2006.extendedProperties.PropertiesDocument$Factory.parse(Unknown Source)
    at org.apache.poi.POIXMLProperties.<init>(POIXMLProperties.java:78)
    at org.apache.poi.POIXMLDocument.getProperties(POIXMLDocument.java:145)
    ... 2 more

This row "resultCell= s1.getRow(i).getCell(2);" is not getting any data.

Kindly help me with this, TIA. "Just adding this text for getting this question post, please ignore this, test data test data because I have already explained my issue in short so i dont this so i need to add any more details"

Murthi · Accepted Answer · 2018-01-10 10:04:20Z

1

Here, There are two issues.

The resultCell is null because it is not created and you are trying to read the cell before creating it. So change/replace the below line.

resultCell= s1.getRow(i).getCell(2); with resultCell= s1.getRow(i).createCell(2);

Also, you are trying to write on cell with null reference.
Null pointer exception because the output stream is trying to write with file which is opened already.

The change/replace the following line,

  XSSFWorkbook wb = new XSSFWorkbook(FilePath);

with

  File myFile = new File(FilePath);
  FileInputStream fis = new FileInputStream(myFile);
  XSSFWorkbook wb = new XSSFWorkbook(fis);

answered Jan 10, 2018 at 10:04

Murthi

5,3471 gold badge12 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can i write test result of Excel based login script with Selenium webdriver

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related