CODEWITHSUNDEEP
javaexcelapache-poi
Ambika
Ambika

Reputation: 61

Cannot invoke "org.apache.poi.ss.usermodel.Cell.getStringCellValue()" because the return value of org.apache.poi.ss.usermodel.Row.getCell(int) is null

I am accessing my Excel sheet data and I am able to get my output but along with that am getting an error:

Cannot invoke "org.apache.poi.ss.usermodel.Cell.getStringCellValue()" because the return value of org.apache.poi.ss.usermodel.Row.getCell(int) is null

I get this error on this line of code:

if(r.getCell(column).getStringCellValue().equalsIgnoreCase("Login ID")

This is my whole code:

package Exceltwo.guruExcel;
import java.io.FileInputStream;

import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class excelDataProvider {

//Identify Testcases column by scanning the entire 1st row
//once column is identified then scan entire testcase column to identify purchase testcase row
//after you grab purchase testcase row = pull all the data of that row and feed into test

public static void main(String[] args) throws IOException {
    FileInputStream fis = new FileInputStream("C:\\Users\\mimo\\Desktop\\workbookexample.xlsx");
    XSSFWorkbook workbook=new XSSFWorkbook(fis);

    int sheets = workbook.getNumberOfSheets();

    for (int i = 0; i < sheets; i++)
    {
        if (workbook.getSheetName(i).equalsIgnoreCase("Sheet1"))
        {
            XSSFSheet sheet = workbook.getSheetAt(i);
            //Identify Testcases column by scanning the entire 1st row
            Iterator<Row> rows = sheet.iterator();  // sheet is collection of rows
            Row firstrow = rows.next();

            Iterator<Cell> ce = firstrow.cellIterator();  // row is collection of cells
            int k = 0;
            int column = 0;

            while (ce.hasNext())
            {
                 Cell value = ce.next();
 
                 if (value.getStringCellValue().equalsIgnoreCase("Testcase"))
                 {
                     column = k;
                 }

                 k++;
             }

             System.out.println(column);

             // once column is identified then scan entire testcase column to identify purchase testcase row
             while (rows.hasNext())
             {
                 Row r = rows.next();

                 if (r.getCell(column).getStringCellValue().equalsIgnoreCase("Login ID"))
                 { 
                     // after you grab purchase testcase row = pull all the data of that row and feed into test
                     Iterator<Cell> cv = r.cellIterator();

                     while (cv.hasNext())
                     {
                          Cell c = cv.next();
                          System.out.println(c);
                     }
                 }
             }
         }
     }

    // TODO Auto-generated method stub
}

Excel Screenshot

Console Screenshot

Upvotes: 1

Views: 16582

Answers (3)

Swanand Yadav
Swanand Yadav

Reputation: 21

    **Add one more if block for checking whether the cell is null or not then it will execute without any error...**

while(rows.hasNext())
{
    Row r=rows.next();
    if(r.getCell(column)!=null)
    {                    
       if(r.getCell(column).getStringCellValue().equalsIgnoreCase("Login ID"))
       {
          Iterator<Cell> cv=r.cellIterator();
          while(cv.hasNext())
          {
                Cell c = cv.next();
                System.out.println(c);
          }
       }
    }
}

Upvotes: 1

Soundhar
Soundhar

Reputation: 1

Change this

while (cv.hasNext())

to this

while (cv.hasNext() == true)

It because, there is no available value after the next / down cells of the given cell.

Upvotes: 0

gordon613
gordon613

Reputation: 2952

As pointed out in the comments r.getCell(column) can return null.

Another option (besides checking for null) is to use CellUtil.getCell(r, column) which will not return null, as it will create the cell if it doesn't exist.

See https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/CellUtil.html#getCell-org.apache.poi.ss.usermodel.Row-int-

Upvotes: 1

Related Questions