While reading data from Excel Sheet got NullpointerException

Question

I am trying to read data from a excel sheet but getting NullPointerException every time when reading data at the cell index =6. Put while(value != null) to avoid null values but still got the exception without any output. I am putting the screen shot of excel sheet from where i am trying to get data. enter image description here

Code-
package com.selenium;

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
import java.io.IOException;

public class Exxcel {
  public static void main(String[] args) throws Exception,NullPointerException{
    //WebDriver driver= new FirefoxDriver();
    //WebElement wb;
    try{
    FileInputStream file= new FileInputStream("C:\\Documents and Settings\\OMEGA\\Desktop\\Test Planning And Documents\\Automation Data.xlsx");
    Workbook data=WorkbookFactory.create(file);
    Sheet sheet=data.getSheet("Sheet1");
    for(int i=1;i<=sheet.getLastRowNum();i++){
        Row row= sheet.getRow(i);
        int j=0;
        String value=row.getCell(j).getStringCellValue();
        while(value != null){ 
        System.out.println(value);
        }//while
        while(value == null){
            j++; 
         }  
    }//for

         /*while(j1==9){
             String value=row.getCell(j1).getStringCellValue();
             System.out.println(value);
             }//while2
             */
    }catch(NullPointerException n){n.printStackTrace();
        System.out.println("Null");
        }// catch
    }//main
 }//class


StackTrace-
Null
java.lang.NullPointerException
at com.selenium.Exxcel.main(Exxcel.java:22)

Eran · Accepted Answer · 2014-07-05 19:05:20Z

3

It's not enough to check that row.getCell(j).getStringCellValue() != null. You should check that row.getCell(j) != null.

In addition, your while loops makes no sense :

The first one will either do nothing or print value forever (since you are not changing value inside the loop).

    while(value != null) { 
        System.out.println(value);
    }//while

The second one will either do nothing or increment j forever (since you are not changing value inside the loop).

    while(value == null) {
        j++; 
    }

I suggest you replace them with the following code :

Row row = sheet.getRow(i);
if (row != null) {
    for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j) != null) {
            if (row.getCell(j).getCellType() == CELL_TYPE_STRING) {
                String value=row.getCell(j).getStringCellValue();
                if(value != null) { 
                    System.out.println(value);
                }
            }
        }   
    }
}

edited Jul 5, 2014 at 19:05

answered Jul 5, 2014 at 18:40

Eran

395k57 gold badges726 silver badges793 bronze badges

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

13 Comments

Shantanu Nandan Over a year ago

Did the code changes as said by you but still getting the exception without any value/output

Eran Over a year ago

@Shantanu In which row do you get the exception? Perhaps you should verify that row != null too.

Shantanu Nandan Over a year ago

@EranSir it worked when i put row != null.... But i am getting the output in a single line. Is it possible to get value of each cell independently. For example String s1 = value of cell 1, String s2= value of second cell

Eran Over a year ago

@Shantanu You gain nothing by creating the array inside the loop, since the values read in previous columns would be overriden. In addition, using an array is not recommended in your case, since you don't know in advance the number of Strings you would have to store in this array. Initializing the array to have i elements makes no sense, since i is just the index of the current row.

Eran Over a year ago

@Shantanu at the beginning (before you start reading the rows), do List<String> list = new ArrayList<String>(); Then do list.add(value) each time you read a new value from the file.

|

Collectives™ on Stack Overflow

While reading data from Excel Sheet got NullpointerException

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related