Null Pointer Exception when using Apache POI

Question

I am trying to input the values to excel from web, having 2 columns and 9 rows, so that I will get the price and the description of 10 products from the web. but Iam getting the Null pointer exception. Please any one help me out in clearing this error.

package samples;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

//import org.apache.commons.io.FileUtils;

//import org.apache.poi.hssf.usermodel.HSSFRow;


import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;

import java.text.ParseException;
import java.util.List;
import java.util.concurrent.TimeUnit;

import org.openqa.selenium.By;
//import org.openqa.selenium.OutputType;
//import org.openqa.selenium.TakesScreenshot;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.interactions.Actions;






public class A1  {



      public static  void main(String[] args) throws InterruptedException, ParseException, IOException, EncryptedDocumentException, InvalidFormatException
      {

                System.out.println("selenium");

            WebDriver webdriver = new FirefoxDriver();
            webdriver.manage().window().maximize();
            webdriver.get("http://www.snapdeal.com");
            webdriver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
            List<WebElement> alllinks = webdriver.findElements(By.tagName("a"));
            int linkcnt = alllinks.size();
            System.out.println("total links=" +linkcnt);

            Actions action = new Actions(webdriver);
            WebElement COG = webdriver.findElement(By.xpath("//span[text()='Computers, Office & Gaming']"));
            WebElement EHD = webdriver.findElement(By.xpath("//span[text()='External Hard Drives']"));
            action.moveToElement(COG).build().perform();
            Thread.sleep(5000);
            EHD.click();

            webdriver.findElement(By.xpath("//label[@for='Capacity_s-1 TB']")).click();
            Thread.sleep(5000);
            webdriver.findElement(By.xpath("//a[contains(text(),'500 GB')]/..")).click();
            Thread.sleep(5000);
            webdriver.findElement(By.xpath("(//span[@class='price-collapse-arrow'])[1]/..")).click();

            WebElement totalitems = webdriver.findElement(By.xpath("//span[@class='category-count']"));
            String totalitemsvalue=totalitems.getText();
            System.out.println(totalitemsvalue);
            String value=totalitemsvalue.replaceAll(" Items","");
            System.out.println(value);

            try{

            List<WebElement> productprice = webdriver.findElements(By.xpath("//div[@class='product-tuple-description']/div[2]"));
            List<WebElement> productTitle = webdriver.findElements(By.xpath("//div[@class='product-desc-rating title-section-collapse']"));
            int count=productprice.size();
            int count1=productTitle.size();
            System.out.println(count);
            System.out.println(count1);

          //int i=9;
        //  int j=9;
           Thread.sleep(2000); 
         for (count=0;count<10;count++)
            {
             productprice = webdriver.findElements(By.xpath("//div[@class='product-tuple-description']/div[2]"));
        //   File srcfile=((TakesScreenshot)webdriver).getScreenshotAs(OutputType.FILE);
        //   FileUtils.copyFile(srcfile, new File("c:\\screenshot.png"));
             Thread.sleep(2000);
             String RupeesValue= productprice.get(count).getText();
             System.out.println(RupeesValue);
             Thread.sleep(2000);
              productTitle = webdriver.findElements(By.xpath("//div[@class='product-tuple-description']/div[1]"));
             Thread.sleep(2000);
              String TitleValue= productTitle.get(count1).getText();
              System.out.println(TitleValue);
              Thread.sleep(2000);

             FileInputStream fis = new FileInputStream("C:\\Users\\aa74231\\Desktop\\abc.xlsx");
            Workbook wb = WorkbookFactory.create(fis);
               Sheet sheet = wb.getSheet("Sheet1");
       //        Sheet sheet1 = wb.getSheet("Sheet1");

               for (int i=0;i<2;i++)
               {
               Row row=sheet.getRow(count);
              // Row row1=sheet.getRow(j);
               Cell cell = row.createCell(count);
              // Cell cell1 = row1.createCell(count1);
            cell.setCellType(cell.CELL_TYPE_STRING);
            //cell1.setCellType(cell1.CELL_TYPE_STRING);
            if(i==0){
            cell.setCellValue(productprice.get(count).getText());
            }
            else
            {
                cell.setCellValue(productTitle.get(count).getText());
            }
            //cell1.setCellValue(productTitle.get(count1).getText());
            FileOutputStream fos=new FileOutputStream("C:\\Users\\aa74231\\Desktop\\abc.xlsx");
            wb.write(fos);
            fos.close();

            wb.close();
            }
          } 




          } catch(Exception e){
                e.printStackTrace();
        }

 }
}

exception which am getting is :

Exception in thread "main" java.lang.NullPointerException at samples.learningold.main(learningold.java:97)

Please post your exception that will help to solve your problem. — Abdul Momen Khan, Jul 19 '16 at 06:07
Possible duplicate of [What is a NullPointerException, and how do I fix it?](http://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) — whitesite, Jul 19 '16 at 06:27
Edit your answer to include the complete exception messages..... — Punkaaj Chavaan, Jul 19 '16 at 07:53

score 0 · Answer 1 · answered Jul 20 '16 at 09:19

First of all your ProductTitle Xpath is not fetching all the product names from the list. I have updated that too.

Following code works well.

package samples;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.List;
import java.util.concurrent.TimeUnit;
import org.apache.poi.EncryptedDocumentException; 
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.interactions.Actions;

public class X
{
@SuppressWarnings("resource")
public static  void main(String[] args) throws InterruptedException,    ParseException, IOException, EncryptedDocumentException, InvalidFormatException
  {
        System.out.println("selenium");

        WebDriver webdriver = new FirefoxDriver();
        webdriver.manage().window().maximize();
        webdriver.get("http://www.snapdeal.com");
        webdriver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
        List<WebElement> alllinks = webdriver.findElements(By.tagName("a"));
        int linkcnt = alllinks.size();
        System.out.println("total links=" +linkcnt);

        Actions action = new Actions(webdriver);
        WebElement COG = webdriver.findElement(By.xpath("//span[text()='Computers, Office & Gaming']"));
        WebElement EHD = webdriver.findElement(By.xpath("//span[text()='External Hard Drives']"));
        action.moveToElement(COG).build().perform();
        Thread.sleep(5000);
        EHD.click();

        webdriver.findElement(By.xpath("//label[@for='Capacity_s-1 TB']")).click();
        Thread.sleep(5000);
        webdriver.findElement(By.xpath("//a[contains(text(),'500 GB')]/..")).click();
        Thread.sleep(5000);
        webdriver.findElement(By.xpath("(//span[@class='price-collapse-arrow'])[1]/..")).click();
        Thread.sleep(5000);
        WebElement totalitems = webdriver.findElement(By.xpath("//span[@class='category-count']"));

        String totalitemsvalue=totalitems.getText();
        System.out.println(totalitemsvalue);
        String value=totalitemsvalue.replaceAll(" Items","");
        System.out.println(value);


        try
        {

            List<WebElement> productprice = webdriver.findElements(By.xpath("//div[@class='product-tuple-description']/div[2]"));
            List<WebElement> productTitle = webdriver.findElements(By.xpath("//div[@class='product-tuple-description']/div[1]"));
            int count=productprice.size();
            int count1=productTitle.size();
            System.out.println(count);
            System.out.println(count1);

            String[] productPriceList = new String[count];
            String[] productTitleList = new String[count];


            Thread.sleep(2000);
            for(int k =0; k<count; k++)
            {
                System.out.println(productprice.get(k).getText());
                productPriceList[k]=productprice.get(k).getText();

                System.out.println(productTitle.get(k).getText());
                productTitleList[k]=productTitle.get(k).getText();

           }

            File file= new File("C:\\Users\\XX\\Downloads\\snapdeal.xlsx");  // give your file path
            FileInputStream inputStream = new FileInputStream(file);
            Workbook sampleWorkbook=null;
            sampleWorkbook=new XSSFWorkbook(inputStream);
            Sheet sheet = sampleWorkbook.getSheet("Sheet1");

           for(int t=0;t<count;t++)
           {
               Row row = sheet.createRow(t);
               Cell cell = row.createCell(0);    // create column 1
               cell.setCellValue(productPriceList[t].toString());

               Cell cell1 = row.createCell(1);   // create column 2
               cell1.setCellValue(productTitleList[t].toString());

               inputStream.close();
               FileOutputStream outputStream = new FileOutputStream(file);
               sampleWorkbook.write(outputStream);
               System.out.println("Data written to Excel successful");   
               outputStream.close();
        }
   }
        catch(Exception e)
        {
            System.out.println(e);
        }

  }

}

Null Pointer Exception when using Apache POI

1 Answers1