Unable to read excel if cell/column has drop down list enabled for Selenium webdriver TestNG

Question

I am using following function to read data from excel for selenium webdriver test. As the data sheet have hundrend of rows to be executed, it needs dropdowns to select values for specific cell which makes it easier for user to fill the data in data sheet. I have used Data validation List to enable dropdown for cell in excel sheet. But after saving it if I try to execute the test, I get error given below. It works fine if I remove the dropdown list option from data sheet. Please help as I must have dropdowns for multiple cell values in excel to make data entry easy.

    package  functional_libraries;  
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.testng.annotations.*; 
    public class excelNew {  @Test
    public static String[][] excelRead(String filename, String Sheetname) throws 

IOException  {
        File excel = new File(filename);
        FileInputStream fis = new FileInputStream(excel);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet ws = wb.getSheet(Sheetname) ;

        int rowNum = ws.getLastRowNum() + 1 ;
        int colNum = ws.getRow(0).getLastCellNum() ;
        String[][] data = new String[rowNum][colNum] ;


        for  ( int i = 0 ; i < rowNum ; i++) {
            XSSFRow row = ws.getRow(i) ;
                for ( short j = 0 ; j < colNum ; j++) {
                    XSSFCell cell = row.getCell(j) ;
                    String value = cellToString(cell);
                    data[i][j] = value ;
                    }
            }

        return data ;

        }

    @Test
    public static String cellToString(XSSFCell cell) {

    int type;
    Object result ;
    type = cell.getCellType();

    switch (type) {

        case 0 : // numeric value in Excel
            result = cell.getNumericCellValue() ;
            break ;
        case 1 : // String Value in Excel 
            result = cell.getStringCellValue() ;
            break ;
        case 3 : // Blank Cell 
          result = "";
          break;

        default :  
            throw new RuntimeException("There are no support for this type of cell") ;                      
    }

    return result.toString() ;
    }

}

[TestNG] Running: C:\Users\suhail\AppData\Local\Temp\testng-eclipse-1517048838\testng-customsuite.xml

FAILED: TestClaimEntry
java.lang.NullPointerException
    at functional_libraries.excelNew.excelRead(excelNew.java:31)
    at testSet.TestClaimEntry.TestClaimEntry(TestClaimEntry.java:42)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:84)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:714)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:901)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1231)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:127)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:111)
    at org.testng.TestRunner.privateRun(TestRunner.java:767)
    at org.testng.TestRunner.run(TestRunner.java:617)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:334)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:329)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:291)
    at org.testng.SuiteRunner.run(SuiteRunner.java:240)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1224)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1149)
    at org.testng.TestNG.run(TestNG.java:1057)
    at org.testng.remote.RemoteTestNG.run(RemoteTestNG.java:111)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:204)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:175)

Answer 1

I found the solution for this issue. Below is the code if anyone want to use.

package functional_libraries;

import java.io.File;
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;
import org.testng.annotations.Test;

public class excelitr {

  @Test
  public static String[][] excelRead(String filename, String Sheetname) throws IOException  {
        File excel = new File(filename);
            FileInputStream fis = new FileInputStream(excel);
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet ws = wb.getSheet(Sheetname) ;
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = ws.iterator();
            int rowNum = ws.getLastRowNum() + 1 ;
            //int colNum = ws.getRow(0).getLastCellNum() ;
            int colNum = ws.getRow(0).getLastCellNum() +1 ;
            String[][] data = new String[rowNum][colNum] ;

            int i=0;
            while (rowIterator.hasNext())
            {
                   Row row = rowIterator.next();
                   //For each row, iterate through all the columns
                   Iterator<Cell> cellIterator = row.cellIterator();
                   int j = 0;

                   while (cellIterator.hasNext())
                   {

                       Cell cell = cellIterator.next();
                          //Check the cell type and format accordingly
                        String value = cellToString(cell);
                       // System.out.println(value +i +j);
                        data[i][j] = value ;
                    j++;

                   }
                   i++;
            }
      return data;
  }

  @Test
            public static String cellToString(Cell cell) {

            int type;
            Object result ;
            type = cell.getCellType();

            switch (type) {

                case 0 : // numeric value in Excel
                    result = cell.getNumericCellValue() ;
                    break ;
                case 1 : // String Value in Excel 
                    result = cell.getStringCellValue() ;
                    break ;
                case 3 : // Blank Cell 
                  result = "";
                  break;

                default :  
                    throw new RuntimeException("There are no support for this type of cell") ;                      
            }

            return result.toString() ;
            }
}